MyCat实现读写分离、分库、全局表的代码

环境如下：

注：主机mysql01和mysql02为主从复制关系，可以参考博文MySQL高可用方案--双主（注：只需要参考博文做出主从效果即可，并不需要双主，也不需要keepalived来做高可用）来搭建。mycat服务请参考博文MyCat部署安装来搭建，这里新增的主机mysql03为一个刚刚搭建好的mysql服务器，可以参考博文Centos部署MySQL 5.7来搭建。

1、mycat实现读写分离

这里没有用到mysql03主机，读写分离的实现比较简单，只需要修改mycat的下面两个配置文件即可。如下：

1）修改server.xml文件

[root@mycat mycat]# pwd/usr/local/mycat   [root@mycat mycat]# vim conf/server.xml[root@mycat mycat]# vim conf/server.xml                              pwd@123                   test_mycat                            

2）修改schema.xml文件

                                                                    select user()                                                                                                                                                                                        

修改后的完整配置文件如下（为避免冗余，删除了部分注释）：

server.xml配置文件内容如下：

[root@mycat mycat]# vim conf/server.xml                 0          0                  2                0                0                1                1m                1k                0                384m                true                                pwd@123                test_mycat        

schema.xml配置文件内容如下：

[root@mycat mycat]# vim conf/schema.xml                                                select user()                                                                                                        

注：至此，后端数据库上自行创建有相关权限的用户，比如我在上面的datahost字段中指定的writehost和readhost字段的user值为root，则数据库的root账号要有对应的登录权限（默认root用户只允许在localhost登录，所以要自行修改root权限或换成其他有权限的用户）。

3）启动mycat并测试读写分离效果

[root@mycat mycat]# mycat start       #启动mycat[root@mycat mycat]# mysql -umycat -ppwd@123 -h 192.168.20.4 -P 8066   #登录到mycat#192.168.20.4为mycat主机IP，mycat监听端口为8066，使用的登录用户为server.xml文件中创建的用户#创建表并插入一些测试数据mysql> use test_mycat;mysql> create table t1 (id int,name varchar(4));mysql> insert into t1 values(1,'张三'),(2,'李四'),(3,'王五');#此时，去后端数据库上查看，应该是有上面这些数据的。#现在可以暂时关闭后端数据库的主从复制，然后再mycat主机上继续插入数据，测试是否可以读到#关闭主从复制命令：stop slave;#以下操作还是在mycat主机进行mysql> insert into t1 values(4,'赵六');    #插入一条数据Query OK, 1 row affected (0.01 sec)mysql> select * from t1;      #查询不到刚插入的数据，说明读写操作没有在同一台主机上进行+------+--------+| id   | name   |+------+--------+|    1 | 张三   ||    2 | 李四   ||    3 | 王五   |+------+--------+#可以去后端两个数据库分别进行查看数据#mysql01主机的数据如下（说明写操作是在mysql01）：mysql> select * from t1;+------+--------+| id   | name   |+------+--------+|    1 | 张三   ||    2 | 李四   ||    3 | 王五   ||    4 | 赵六   |+------+--------+4 rows in set (0.00 sec)#mysql02主机的数据如下：mysql> select * from t1（说明读操作是在mysql02）;+------+--------+| id   | name   |+------+--------+|    1 | 张三   ||    2 | 李四   ||    3 | 王五   |+------+--------+3 rows in set (0.00 sec)

读写分离效果至此结束。（为不影响后面的测试进行，最好恢复mysql01和mysql02主机的主从复制）。

2、mycat实现分库

一个数据库由很多表组成，每个表对应着不同的业务，所谓分库，就是按照业务将表进行分类，分不到不同的数据库上面，这样也就将数据或者说压力分担到不同的库上面，分库的原则：有紧密关联关系的表应该在一个库里，相互没有或者关联关系不大的表可以分到不同的库里。

分库举例：

假设现在有四张表： customer， orders， orders_detail， dict_order_type，每张表都有数百万条数据，那么这四张表如若要实现分库，则可以将customer表单独分离到一个数据库，另外三张表单独在另一个数据库。

1）修改server.xml文件（就改了一下逻辑库名）

[root@mycat mycat]# cat conf/server.xml         0      0          2        0        0        1        1m        1k        0        384m        true                pwd@123        test_db    

2）修改schema.xml文件

[root@mycat mycat]# cat conf/schema.xml                            select user()                                                                    select user()                            
3）重启mycat
[root@mycat mycat]# mycat restart
注：后端指定的真实数据库必须要有test数据库，因为在上面schema.xml文件中的dataNode字段指定的是连接后端的test数据库。
4）测试分库效果
#登录到mycat[root@mycat mycat]# mysql -umycat -ppwd@123 -h 192.168.20.4 -P 8066#创建相应的表mysql> use test_db;mysql> CREATE TABLE customer(    -> id_a INT AUTO_INCREMENT,    -> NAME VARCHAR(200),    -> PRIMARY KEY(id_a)    -> );Query OK, 0 rows affected (0.02 sec)mysql> CREATE TABLE orders(    -> id_b INT AUTO_INCREMENT,    -> order_type INT,    -> customer_id INT,    -> amount DECIMAL(10,2),    -> PRIMARY KEY(id_b)    -> );Query OK, 0 rows affected (0.01 sec)mysql> CREATE TABLE orders_detail(    -> id_c INT AUTO_INCREMENT,    -> detail VARCHAR(2000),    -> order_id INT,    -> PRIMARY KEY(id_c)    -> );Query OK, 0 rows affected (0.02 sec)mysql> CREATE TABLE dict_order_type(    -> id_d INT AUTO_INCREMENT,    -> order_type VARCHAR(200),    -> PRIMARY KEY(id_d)    -> );Query OK, 0 rows affected (0.01 sec)#登录到后端数据库上，查看表是否按照预计的效果拆分[root@mycat mycat]# mysql -uroot -p123.com -h 192.168.20.2mysql> use test;mysql> show tables;             #可以看到主机mysql01这里有三个表+-----------------+| Tables_in_test  |+-----------------+| dict_order_type || orders          || orders_detail   |+-----------------+3 rows in set (0.00 sec)#登录到mysql03上查看[root@mycat mycat]# mysql -uroot -p123.com -h 192.168.20.21mysql> use test;mysql> show tables;     #可以看到这里只有customer表+----------------+| Tables_in_test |+----------------+| customer       |+----------------+1 row in set (0.00 sec)
至此，已经实现了分库，多个表放在了不同的库中（在上面的栗子中，实现的是多个表放在了不同的主机上），但对于通过mycat登录的客户来说，还是一个库，库中有四张表。
3、mycat实现分表
这里对一个新表emp进行拆分。
1）schema.xml文件修改如下（完整配置文件）
[root@mycat mycat]# cat conf/schema.xml                            select user()                                                                select user()                            
2）rule.xml文件添加以下内容
[root@mycat mycat]# vim conf/rule.xml      #修改rule.xml文件                                                    id                            mod_long                                                  ...................                                                                                        2            
3）重启mycat并登录到mycat
[root@mycat mycat]# mycat restart
4）登录到mycat插入数据进行测试分表效果
[root@mycat mycat]# mysql -umycat -ppwd@123 -h 192.168.20.4 -P 8066mysql> use test_db;#创建emp表（后端两个数据库节点上都会有这张表）mysql> create table emp (    -> id int,    -> name varchar(10)    -> );Query OK, 0 rows affected (0.41 sec)#插入多条数据（对于这种分表，插入数据需要指定列名，否则会报错）mysql> insert into emp(id,name) values(1,'张三'),(2,'李四'),(3,'王五'),(4,'赵六');#查询新插入的数据mysql> select * from emp;    +------+--------+| id   | name   |+------+--------+|    2 | 李四   ||    4 | 赵六   ||    1 | 张三   ||    3 | 王五   |+------+--------+4 rows in set (0.04 sec)mysql> select * from emp order by id;    #对结果进行排序+------+--------+| id   | name   |+------+--------+|    1 | 张三   ||    2 | 李四   ||    3 | 王五   ||    4 | 赵六   |+------+--------+4 rows in set (0.01 sec)#后端mysql01主机查询到该表的数据如下：mysql> select * from emp;+------+--------+| id   | name   |+------+--------+|    2 | 李四   ||    4 | 赵六   |+------+--------+2 rows in set (0.00 sec)#mysql03主机查询到的数据如下：mysql> select * from emp;          +------+--------+| id   | name   |+------+--------+|    1 | 张三   ||    3 | 王五   |+------+--------+2 rows in set (0.00 sec)
可以看到数据是平均分散在不同节点上保存的。
但是经过分片的普通表，是无法直接和其他表进行join的。
要想解决多表join的问题，还需要修改schema.xml配置文件，在分表的table字段下添加childTable字段，具体怎么实现，还在研究中。但是可以将分表的类型设置为全局表，这是一种比较简单的实现方式。
3、全局表
分表后，与其他表如何join就成了比较棘手的问题，我们可以选择将分表设置为全局表，这是一个解决join的方法（但不是唯一的方法），但全局表有一定的局限性，如下：
全局表变动不频繁；
数据量总体变化不大；
数据规模不大，很少有超数十万的记录。
若想创建全局表，只需在table字段增加type="global"即可。如下：
1）定义全局表
[root@mycat conf]# cat schema.xml            
                        select user()                                                                select user()                            

2）验证全局表

mysql> drop table emp;    #需要删除之前创建的emp表Query OK, 0 rows affected (0.03 sec)#重新创建emp表mysql> create table emp(    -> id int,    -> name varchar(10)    -> );Query OK, 0 rows affected (0.38 sec)#插入数据测试mysql>  insert into emp(id,name) values(1,'张三'),(2,'李四'),(3,'王五'),(4,'赵六');#在mycat上查询插入的数据如下：mysql> select * from emp;+------+--------+| id   | name   |+------+--------+|    1 | 张三   ||    2 | 李四   ||    3 | 王五   ||    4 | 赵六   |+------+--------+4 rows in set (0.02 sec)#在后端主机上分别查看emp表中的数据：#mysql01主机上数据如下：mysql> select * from emp;+------+--------+| id   | name   |+------+--------+|    1 | 张三   ||    2 | 李四   ||    3 | 王五   ||    4 | 赵六   |+------+--------+4 rows in set (0.00 sec)#mysql03主机上数据如下：mysql> select * from emp;+------+--------+| id   | name   |+------+--------+|    1 | 张三   ||    2 | 李四   ||    3 | 王五   ||    4 | 赵六   |+------+--------+4 rows in set (0.00 sec)