Cpp五条

这个屌丝很懒，什么也没留下！

热门标签

Mycat实现水平分表及读写分离_mysql表分离

作者：Cpp五条 | 2024-03-05 15:19:38

踩

mysql表分离

Mycat出现缘由

随着互联网的发展，数据的量级也是呈指数的增长。数据量的增大导致对数据的各种操作也是愈加的困难，传统的关系性数据库已经无法满足快速查询与插入数据的需求。而NoSQL的出现暂时解决了这一危机，它通过降低数据的安全性，减少对事务的支持，减少对复杂查询的支持，来获取性能上的提升。但是，有些场合NoSQL是无法满足的，就比如有些使用场景其数据库是绝对要有事务支持。这就带来了一个如何使用关系型数据库解决海量存储的问题，答案就是做数据库集群，将数据分散到不同的数据库中存储——MyCat。

Mycat不仅支持MySQL，还支持oracle、SQLServer和mongoDB等主流的数据库

数据切分

简单来说，Mycat所做的事情就是数据切分，数据切分按模式可以分为两种：

垂直切分

垂直切分是基于数据库中的"列"进行，某个表字段较多，可以新建一张扩展表，将不经常用或字段长度较大的字段拆分出去到扩展表中。在字段很多的情况下（例如一个大表有100多个字段），通过"大表拆小表"，更便于开发与维护，也能避免跨页问题，MySQL底层是通过数据页存储的，一条记录占用空间过大会导致跨页，造成额外的性能开销。另外数据库以行为单位将数据加载到内存中，这样表中字段长度较短且访问频率较高，内存能加载更多的数据，命中率更高，减少了磁盘IO，从而提升了数据库性能。(图是网上搜来的，感觉画得挺贴切的)

在这里插入图片描述

水平切分

水平切分则是根据表内数据内在的逻辑关系，将同一个表按不同的条件分散到多个数据库或多个表中，每个表中只包含一部分数据，从而使得单个表的数据量变小，达到分布式的效果。(图是网上搜来的，感觉画得挺贴切的)

在这里插入图片描述

Mycat简介

Mycat是一个开源的分布式数据库系统，是一个实现了MySQL协议的服务器，前端用户可以把它看作是一个数据库代理，用MySQL客户端工具和命令行访问，而其后端可以用MySQL原生协议与多个MySQL服务器通信，也可以用JDBC协议与大多数主流数据库服务器通信，其核心功能是分表分库，即将一个大表水平分割为N个小表，存储在后端MySQL服务器里或者其他数据库里，它提供了高可用的数据分片集群。你可以像使用MySQL一样使用Mycat。对于开发人员来说根本感觉不到Mycat的存在。
在这里插入图片描述

在Mycat中有以下几个概念需要明确一下的

Schema：逻辑库，与MySQL中的Database（数据库）对应，一个逻辑库中定义了所包括的Table。
Table：逻辑表，即逻辑数据库中存储的某一张表，与传统数据库不同，这里的表格需要声明其所存储的逻辑数据节点DataNode。在此可以指定表的分片规则。
DataNode：数据切分后，一个大表被分到不同的分片数据库上面，每个表分片所在的数据库就是分片节点（dataNode）。
DataHost：数据切分后，每个分片节点（DataNode）不一定都会独占一台机器，同一机器上面可以有多个分片数据库，这样一个或多个分片节点（dataNode）所在的机器就是节点主机（dataHost）,为了规避单节点主机并发数限制，尽量将读写压力高的分片节点（dataNode）均衡的放在不同的节点主机（dataHost），DataHost直接定义了具体的数据库实例、读写分离配置和心跳语句。
Rule：分片规则，前面讲了数据切分，一个大表被分成若干个分片表，就需要一定的规则，这样按照某种业务规则把数据分到某个分片的规则就是分片规则。

Mycat的下载及安装

JDK：要求JDK必须是1.7及以上版本

MySQL：推荐MySQL是5.5以上版本

Mycat下载地址：https://github.com/MyCATApache/Mycat-download

注意：若Mycat的版本不一致，其配置文件的配置也有些许的不一样，我所用的Mycat版本是Mycat1.6.7

Mycat安装步骤：

下载Mycat安装压缩包
将压缩包解压缩到某个目录。

tar -zxvf Mycat-server-1.6.7.4-test-20191022170541-linux.tar.gz -C /usr/local
1

./mycat start
1

查看启动状态：

./mycat status
1

停止：

./mycat stop
1

Mycat的默认端口号为：8066

Mycat水平分表

下面我们走个Mycat的水平分表小例子来认识下Mycat中的几个配置文件，我们使用Mycat来实现水平分表操作。

在用Mycat实现水平分表操作之前，得先装下MySQL

在CentOS6.4里安装MySQL

用yum下载MySQL

yum install -y mysql-server mysql mysql-devel
1

启动MySQL服务

service mysqld start
1

设置MySQL的用户和密码

mysqladmin -u root password 'root'
1

登录MySQL，敲入命令后回车，输入密码

mysql -u root -p
1

设置MySQL允许远程连接

进入MySQL，设置允许所有主机ip以root用户远程连接

grant all privileges on *.* to 'root'@'%' identified by 'root' with grant option;
1

将配置项写入MySQL授权表中

flush privileges;
1

基础设施搭建好之后我们开始配置Mycat配置文件

设置schema.xml

schema.xml作为Mycat中重要的配置文件之一，定义着Mycat的逻辑库、表、分片规则、DataNode以及DataHost。弄懂这些配置，是正确使用Mycat的前提。这里就一层层对该文件进行解析。

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">


	<!--
	name：为mycat逻辑库的名字，
	checkSQLschema：自动检查逻辑库名称并拼接，true会在sql语句中的表名前拼接逻辑库名，
	sqlMaxLimit：查询保护、如果没有写limit条件，会自动拼接。只查询100条。
	-->
	<schema name="mydatabase" checkSQLschema="true" sqlMaxLimit="100">
		<!--
		name:为逻辑数据库的表名
		dataNode:为dataNode标签(<dataNode name="dn1" dataHost="dtHost1" database="db1" />)里面的name值
		dataNode里面填写的节点数量必须和rule里面的规则数量一致
		例如rule里面只定义了两个0-1M=0  1M-2M=1那么此处只可以指定两个节点
		primaryKey:表的主键，建议和rule.xml里面指定的ID一致
		rule：分片规则，对应rule.xml中<tableRule name="student_id">的name
		-->	
		<table name="t_student" dataNode="dn1,dn3" primaryKey="id" rule="student_id" />
    </schema>
		
		<!-- name：分片节点名称
		dataHost:dataHost标签name值(<dataHost name="dtHost1">)
		database:物理数据库名，需要提前创建好实际存在的-->
		<dataNode name="dn1" dataHost="Master" database="db1" />
		<dataNode name="dn2" dataHost="Slave1" database="db2" />
		<dataNode name="dn3" dataHost="Master" database="db3" />
		
	<!--
	DataHost定义了具体的数据库实例
	name：节点名称，在上方dataNode标签中调用;
	maxCon:底层数据库的链接最大数;
	minCon:底层数据库的链接最小数;
	balance:值可以为0,1,2,3,分别表示对当前datahost中维护的数据库们的读操作逻辑：0不开启读写分离;
	writeType:值可以为:0所有的写操作都在第一个writeHost标签的数据库进行,
	1：所有的写操作，都随机分配到所有的writeHost（mycat1.5完全不建议配置了）;
	dbtype：数据库类型（不同数据库配置不同名称，mysql）;
	dbDriver:数据库驱动，native,动态获取;
	switchType：切换的逻辑,-1故障不切换,1故障切换，当前写操作的writeHost故障，进行切换，切换到下一个writeHost;
	-->
 <dataHost name="Master" maxCon="1000" minCon="10" balance="0"
                          writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
                <heartbeat>select user()</heartbeat>
                <writeHost host="Master" url="192.168.100.10:3306" user="root"
                                   password="root">
                </writeHost>
        </dataHost>
        <dataHost name="Slave1" maxCon="1000" minCon="10" balance="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
                 <heartbeat>select user()</heartbeat>
                <writeHost host="Slave1" url="192.168.100.11:3306" user="root" password="root"></writeHost>
        </dataHost>
</mycat:schema>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53

设置server.xml

server.xml，最常用的是在此配置用户名、密码及权限。

<!-- 此处定义了一个root用户，可以管理的逻辑库为mydatabase，对应schema.xml中的<schema name="mydatabase" > -->
<user name="root">
	<property name="password">root</property>
	<property name="schemas">mydatabase</property>
	<property name="readOnly">false</property>
</user>
1
2
3
4
5
6

设置rule.xml

rule.xml里面就定义了我们对表进行拆分所涉及到的规则定义。我们可以灵活的对表使用不同的分片算法，或者对表使用相同的算法但具体的参数不同。这个文件里面主要有tableRule和function这两个标签。在具体使用过程中可以按照需求添加tableRule和function，也可以不添加使用自带的，其自带的分片规则可以满足我们基本的分片需求。

这里我选择添加如下tableRule和function。

<!--schema.xml中配置的rule="student_id" 所对应的分表规则，
columns为id需要与数据库的列名对应，algorithm对应下方function-->
	<tableRule name="student_id">
        <rule>
            <columns>id</columns>
            <algorithm>student_text</algorithm>
        </rule>
    </tableRule>

<!--在conf中需要添加student_text.txt规则文件-->
 <function name="student_text"
        class="io.mycat.route.function.AutoPartitionByLong">
        <property name="mapFile">student_text.txt</property>
    </function>
1
2
3
4
5
6
7
8
9
10
11
12
13
14

在conf目录下创建student_text.txt

文件内容如下

0-1M=0
1M-2M=1
1
2

在Master节点的MySQL中创建相应的数据库db1、db3

mysql -u root -P3306 -p
1

create database db1;
create database db3;
1
2

启动Mycat，连接Mycat

启动Mycat

cd /usr/local/mycat/bin
./mycat start
1
2

连接Mycat和连接MySQL的方式几乎是一样的，只不过在连接时要指定Mycat的端口号

mysql -u root -h 192.168.100.10 -P8066 -p
1

连接Mycat后出现的数据库和数据表，均是虚拟的逻辑数据库、逻辑数据表，我们在对逻辑数据库、逻辑数据表的操作，背后均被Mycat转换成对MySQL的数据库、数据表的操作，其数据被分布在背后的MySQL集群中。

连接Mycat后我们show databases可以查看到我们在schema.xml中定义的逻辑数据库

show databases;
1

在这里插入图片描述

往Mycat插入数据

我们往Mycat中插入数据，观察数据的分片情况

在Mycat的连接控制台我们键入如下命令，插入数据，其命令都是基础的sql语句

选择数据库

use mydatabase;
1

建表，所建的表要和我们在schema.xml设置的逻辑表要对应

CREATE TABLE `t_student` (
  `id` int(11) NOT NULL,
  `s_name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1
2
3
4
5

此时建表语句报出了异常：ERROR 3009 (HY000) java.lang.IllegalArgumentException Invalid DataSource0

debug思路：Mycat：ERROR 3009 (HY000) java.lang.IllegalArgumentException Invalid DataSource0

解决，建表成功

在这里插入图片描述登录mysql，

登录mycat，往Mycat插入数据

INSERT INTO `t_student` (id,s_name) VALUES ('0', '00');
INSERT INTO `t_student` (id,s_name) VALUES ('1', '111');
INSERT INTO `t_student` (id,s_name) VALUES ('10000', '10000');
INSERT INTO `t_student` (id,s_name) VALUES ('10001', '10001');
INSERT INTO `t_student` (id,s_name) VALUES ('20000', '20000');
1
2
3
4
5

我们查看Mycat的逻辑表t_student，查看到数据已经插入成功

在这里插入图片描述

查看数据是否分片成功

按照设定的分片规则，t_student的数据是被切分到Master的db1和db3的物理数据库上的（0—1M在db1，1M—2M在db3），我们连接Master的MySQL，查看对应的物理数据库，看看数据是否成功被分片。

mysql -u root -P3306 -p
1

首先我们看看db1，里面存储了id为0、1、10000的数据记录

在这里插入图片描述

再来看看db3，里面存储了id为10001、20000的数据记录

$[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-9Mzz3Sr1-1593415277511)(C:\Users\lenovo\AppData\Roaming\Typora\typora-user-images\image-20200629144236724.png)]$

Mycat读写分离

在Mycat上实现主从复制功能的时候，在MySQL上要搭建起主从复制

在搭建MySQL主从复制之前我们要明确MySQL主从复制的原理

MySQL主从复制原理

简单点说

MySQL中有一种文件叫做二进制日志（比如mysql-bin.xxxxxx），这日志文件会记录下所有修改了数据库的SQL 语句（insert,update,delete,create/alter等语句）。
主从复制的原理其实就是把主MySQL上的二进制日志拉到从MySQL上执行一遍，这样从MySQL上的数据就会和主服务器上的数据相同了。

详细点说

主MySQL必须启用二进制日志，记录任何修改了数据库数据的事件。
从MySQL开启I/O Thread把自己扮演成 mysql 的客户端，通过 mysql 协议，请求主节点的二进制日志文件中的记录
主MySQL启动一个线程dump Thread，检查自己二进制日志中的事件，跟对方请求的位置对比，如果不带请求位置参数，则主MySQL就会从第一个二进制日志文件中的第一条记录一个个发送给从MySQL（主MySQL中会有很多个二进制日志文件）。
从MySQL接收到主MySQL发送过来的记录二进制日志文件记录把它放置到Relay log（中继日志）文件中。并记录该次请求到主节点的具体哪一个二进制日志文件内的哪一个位置。
从MySQL启动sql Thread ，把 Relay log 中的事件读取出来，并在从MySQL上再执行一次。

我们用Master节点上的MySQL做主MySQL，Slave1上的MySQL做从MySQL

主MySQL的配置

配置Master上的my.cnf

vi /etc/my.cnf
1

[mysqld]
#二进制日志文件在datadir目录下可以找到
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
#设置不开启复制的数据库，此数据库的数据库将不同步到从MySQL中
binlog-ignore-db=mysql
#启用二进制日志
log-bin=mysql-bin
symbolic-links=0
#服务器唯一id，一般取ip地址最后一段
server-id=10

[mysqld_safe]
#错误日志文件
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

一般只要安装了MySQL数据库，my.cnf都配置了datadir、socket、symbolic-links、log-error和pid-file配置项，只需要添加binlog-ignore-db、log-bin和server-id配置项即可

然后Master上启动MySQL服务

sudo service mysqld start
1

进入MySQL控制台

mysql -uroot -proot
1

查看二进制日志是否开启

SHOW GLOBAL VARIABLES LIKE '%log%';
1

我们看到bin-log这一项是开启状态的
在这里插入图片描述

查看主MySQL的二进制日志列表

show master logs;
1

我这里有三份二进制日志文件（具体数量视操作MySQL次数而定）

在这里插入图片描述
查看主MySQL的server_id

show global variables like '%server%';
1

在这里插入图片描述

配置MySQL远程登录的权限，指定用户名和密码，’’%’'表示所有ip都可以远程登录此mysql实例（已配置的可以跳过）

grant all privileges  on *.* to 用户名@'%' identified by "密码"; 
1

在主MySQL上创建账户用于复制二进制日志文件，我这里创建的账户用户名为user，密码为158758qwef，192.168.100.11是我的从MySQL的ip

grant replication slave on *.* to 'user'@'192.168.100.11' identified by '158758qwef'; 
1

刷新，写入配置

flush privileges;
1

查看记录下主MySQL的状态（配置从MySQL的时候用到）

show master status;
1

在这里插入图片描述

图中显示主MySQL的事务操作记录到了

file：mysql-bin.000003

position：1710

在配置从MySQL，我们要用上这些信息。

截止到这里，主MySQL配置完成。

从MySQL配置

配置Slave1上的my.cnf

vi /etc/my.cnf
1

只需要添加

server-id=11
1

然后启动MySQL服务

sudo service mysqld start
1

登录MySQL

mysql -uroot -proot
1

配置MySQL远程登录的权限，指定用户名和密码，’’%’'表示所有ip都可以远程登录此mysql实例（已配置的可以跳过）

grant all privileges  on *.* to 用户名@'%' identified by "密码"; 
1

在从MySQL配置访问主MySQL的参数信息，master_host是主MySQL的ip地址，master_user和master_password均是刚刚在主MySQL上创建的那个账户，master_log_file和master_log_pos是我们在主MySQL上执行show master status命令看到的file和position的信息

CHANGE MASTER TO MASTER_HOST='192.168.100.10',MASTER_USER='user',MASTER_PASSWORD='158758qwef', MASTER_LOG_FILE='mysql-bin.000003',MASTER_LOG_POS=1710;
1

开启线程复制

start slave; 
1

查看下从MySQL的slave状态

show slave status \G
1

在这里插入图片描述

请注意到画黄线的slave_io_running和salve_sql_running都处于开启状态，说明我们的MySQL主从复制是搭建成功的。

Mycat配置

在MySQL上搭建了主从关系，接下来可以在Mycat中应用它

基本沿用我们在Mycat分片上配置的配置文件，只是schema.xml要稍微加点配置更改下（变动项均用红框标出）

schema.xml

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
        <schema name="mydatabase" checkSQLschema="true" sqlMaxLimit="100">
                <table name="t_student" dataNode="dn1,dn3" rule="student_id" />
                <table name="t_teacher" dataNode="dn6,dn5" rule="student_id" />
        </schema>
        <dataNode name="dn1" dataHost="Master" database="db1" />
        <dataNode name="dn2" dataHost="Slave1" database="db2" />
        <dataNode name="dn6" dataHost="Master" database="db6" />
        <dataNode name="dn5" dataHost="Master" database="db5" />
        <dataNode name="dn3" dataHost="Master" database="db3" />
        <dataHost name="Master" maxCon="1000" minCon="10" balance="1"
                          writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
                <heartbeat>select user()</heartbeat>
                <writeHost host="Master" url="192.168.100.10:3306" user="root"
                                   password="root">
                        <readHost host="Slave1" url="192.168.100.11:3306" user="root"
                        password="root" />
                </writeHost>
        </dataHost>
        <dataHost name="Slave1" maxCon="1000" minCon="10" balance="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
                 <heartbeat>select user()</heartbeat>
                <writeHost host="Slave1" url="192.168.100.11:3306" user="root" password="root"></writeHost>
        </dataHost>
</mycat:schema>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

在这里插入图片描述

我在逻辑库mydatabase里新建了一个逻辑表t_teacher，它的数据分片分布在dataHost为Master的db5和db6数据库上，沿用了之前的分片算法。在dataHost为Master的标签中添加了一个readHost，readHost指定了之前搭建的从MySQL，并且balance属性值从0变为1，表示所有读操作都随机的发送到readHost上。

然后请不要忘记在主MySQL上创建db5，db6数据库，并创建相应的t_teacher表（逻辑数据库和物理数据库所创建的表应该是一模一样的（表名、字段和数据类型）），这步省略命令操作

登录mycat，创建t_student表

mysql -h192.168.100.10 -uroot -proot -P8066 
1

use mydatabase
1

CREATE TABLE `t_teacher` (
  `id` int(11) NOT NULL,
  `s_name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1
2
3
4
5

再强调一遍：逻辑数据库和物理数据库所创建的表应该是一模一样的（表名、字段和数据类型）

插入数据

INSERT INTO `t_teacher` (id,s_name) VALUES ('0', '00');
INSERT INTO `t_teacher` (id,s_name) VALUES ('10001', '11');
1
2

然后我们在Mycat里select一下t_teacher表看一看，数据是否成功插入

在这里插入图片描述

此时我们在从MySQL上show databases;看一看数据是否同步，这时在从MySQL上都有了db5和db6数据库的，说明Mycat读写分离是搭建成功的，注意我们之前是没有对从MySQL有过任何写数据的操作的。
在这里插入图片描述

也可以分别到db5和db6数据库上select一下t_teacher表看一下插入的数据是否被成功分片，db5里只有id为10001的数据记录，说明插入的数据也是被成功分片的
在这里插入图片描述

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/Cpp五条/article/detail/191975

Mycat实现水平分表及读写分离_mysql表分离

文章目录

Mycat出现缘由

数据切分

Mycat简介

Mycat的下载及安装

Mycat水平分表

在CentOS6.4里安装MySQL

设置MySQL允许远程连接

设置schema.xml

设置server.xml

设置rule.xml

在conf目录下创建student_text.txt

在Master节点的MySQL中创建相应的数据库db1、db3

启动Mycat，连接Mycat

往Mycat插入数据

查看数据是否分片成功

Mycat读写分离

MySQL主从复制原理

主MySQL的配置

从MySQL配置

Mycat配置