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   <!--此处为mycat的家目录-->
[root@mycat mycat]# vim conf/server.xml
<!--指定client连接mycat的用户名及密码，此处的账号密码与MySQL数据库无关-->
[root@mycat mycat]# vim conf/server.xml    <!--定位到80行左右，修改如下-->
        <user name="mycat" defaultAccount="true">  <!--mycat为用户名-->
                <property name="password">pwd@123</property>   <!--此处为用户mycat的登录密码-->
                <property name="schemas">test_mycat</property>    <!--此处为逻辑库名--

                <!-- 表级 DML 权限设置 -->
                <!--            
                <privileges check="false">
                        <schema name="TESTDB" dml="0110" >
                                <table name="tb01" dml="0000"></table>
                                <table name="tb02" dml="1111"></table>
                        </schema>
                </privileges>           
                 -->
        </user>

2）修改schema.xml文件

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
<!--下面的schema必须和第一个文件中的schema name（逻辑库名）一致。-->
        <schema name="test_mycat" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
        </schema>
        <dataNode name="dn1" dataHost="host1" database="test" />    <!--这里的database是指定要连接后端的哪个数据库，这里连接的是test库-->
                <!--下面是指定后端真实的MySQL主机，关于下面的balance值，有三个可选值，将在下面写下来-->
        <dataHost name="host1" maxCon="1000" minCon="10" balance="3"
                          writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
                <heartbeat>select user()</heartbeat>
                <!-- can have multi write hosts -->
                                <!--指定后端哪台主机用来写数据-->
                <writeHost host="hostM1" url="192.168.20.2:3306" user="root"
                                   password="123.com">
                        <!-- can have multi read hosts -->
                                                <!--指定后端哪台主机用来读数据-->
                        <readHost host="hostS2" url="192.168.20.3:3306" user="root" password="123.com" />
                </writeHost>
        </dataHost>
</mycat:schema>

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

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

[root@mycat mycat]# vim conf/server.xml 

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mycat:server SYSTEM "server.dtd">
<mycat:server xmlns:mycat="http://io.mycat/">
        <system>
        <property name="useSqlStat">0</property>  <!-- 1为开启实时统计、0为关闭 -->
        <property name="useGlobleTableCheck">0</property>  <!-- 1为开启全加班一致性检测、0为关闭 -->

                <property name="sequnceHandlerType">2</property>
                <property name="processorBufferPoolType">0</property>
                <property name="handleDistributedTransactions">0</property>

                <property name="useOffHeapForMerge">1</property>
                <property name="memoryPageSize">1m</property>
                <property name="spillsFileBufferSize">1k</property>

                <property name="useStreamOutput">0</property>
                <property name="systemReserveMemorySize">384m</property>

                <property name="useZKSwitch">true</property>

        </system>
        <user name="mycat" >
                <property name="password">pwd@123</property>
                <property name="schemas">test_mycat</property>
        </user>
</mycat:server>

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

[root@mycat mycat]# vim conf/schema.xml

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

        <schema name="test_mycat" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
        </schema>
        <dataNode name="dn1" dataHost="host1" database="test" />
        <dataHost name="host1" maxCon="1000" minCon="10" balance="3"
                          writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
                <heartbeat>select user()</heartbeat>
                <!-- can have multi write hosts -->
                <writeHost host="hostM1" url="192.168.20.2:3306" user="root"
                                   password="123.com">
                        <!-- can have multi read hosts -->
                        <readHost host="hostS2" url="192.168.20.3:3306" user="root" password="123.com" />
                </writeHost>
        </dataHost>
</mycat:schema>

注：至此，后端数据库上自行创建有相关权限的用户，比如我在上面的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 
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mycat:server SYSTEM "server.dtd">
<mycat:server xmlns:mycat="http://io.mycat/">
    <system>
    <property name="useSqlStat">0</property>  <!-- 1为开启实时统计、0为关闭 -->
    <property name="useGlobleTableCheck">0</property>  <!-- 1为开启全加班一致性检测、0为关闭 -->

        <property name="sequnceHandlerType">2</property>
        <property name="processorBufferPoolType">0</property>
        <property name="handleDistributedTransactions">0</property>

        <property name="useOffHeapForMerge">1</property>
        <property name="memoryPageSize">1m</property>
        <property name="spillsFileBufferSize">1k</property>

        <property name="useStreamOutput">0</property>
        <property name="systemReserveMemorySize">384m</property>

        <property name="useZKSwitch">true</property>

    </system>
    <user name="mycat" >
        <property name="password">pwd@123</property>
        <property name="schemas">test_db</property>
    </user>
</mycat:server>

2）修改schema.xml文件

[root@mycat mycat]# cat conf/schema.xml
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">

    <schema name="test_db" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
        <table name="customer" dataNode="dn2"/>   <!--这里指定customer在dn2节点上-->
    </schema>
    <dataNode name="dn1" dataHost="host1" database="test" />
    <dataNode name="dn2" dataHost="host2" database="test" />
    <dataHost name="host1" maxCon="1000" minCon="10" balance="3"
              writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
        <heartbeat>select user()</heartbeat>
        <!-- can have multi write hosts -->
        <writeHost host="hostM1" url="192.168.20.2:3306" user="root"
                   password="123.com">
            <!-- can have multi read hosts -->
            <readHost host="hostS2" url="192.168.20.3:3306" user="root" password="123.com" />
        </writeHost>
    </dataHost>
    <!--下面添加host2主机字段，指定的主机就是mysql03-->
    <dataHost name="host2" maxCon="1000" minCon="10" balance="0"
              writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
        <heartbeat>select user()</heartbeat>
        <!-- can have multi write hosts -->
        <writeHost host="hostM2" url="192.168.20.21:3306" user="root"
                   password="123.com">
        </writeHost>
    </dataHost>
</mycat:schema>

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.2
mysql> 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.21
mysql> 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
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">

    <schema name="test_db" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
        <table name="customer" dataNode="dn2"/>
        <!--只需要在原来的配置文件中增加下面一行即可，表示将emp表分在了dn1和dn2这两个节点上-->
        <table name="emp" dataNode="dn1,dn2" rule="mod_rule"/>
    </schema>
    <dataNode name="dn1" dataHost="host1" database="test" />
    <dataNode name="dn2" dataHost="host2" database="test" />
    <dataHost name="host1" maxCon="1000" minCon="10" balance="3"
              writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
        <heartbeat>select user()</heartbeat>
        <!-- can have multi write hosts -->
        <writeHost host="hostM1" url="192.168.20.2:3306" user="root"
                   password="123.com">
            <!-- can have multi read hosts -->
            <readHost host="hostS2" url="192.168.20.3:3306" user="root" password="123.com" />
        </writeHost>
    </dataHost>
    <dataHost name="host2" maxCon="1000" minCon="10" balance="0"
              writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
        <heartbeat>select user()</heartbeat>
        <!-- can have multi write hosts -->
        <writeHost host="hostM2" url="192.168.20.21:3306" user="root"
                   password="123.com">
        </writeHost>
    </dataHost>
</mycat:schema>

2）rule.xml文件添加以下内容

[root@mycat mycat]# vim conf/rule.xml      #修改rule.xml文件
        <tableRule name="mod_rule">    <!--这里指定的name必须和schema.xml中指定的name一致-->
                <rule>
                        <columns>id</columns>    <!--这里指定的是以哪里列进行分表的-->
                        <algorithm>mod_long</algorithm>  <!--这里指定的是分片方法-->
                </rule>
        </tableRule>
                        ...................
                                                <!--下面的name必须和上面的分片方法一致-->
        <function name="mod_long" class="io.mycat.route.function.PartitionByMod">
                <!-- how many data nodes -->
                <property name="count">2</property>    <!--这里指定的是node节点数量-->
        </function>

3）重启mycat并登录到mycat

[root@mycat mycat]# mycat restart

4）登录到mycat插入数据进行测试分表效果

[root@mycat mycat]# mysql -umycat -ppwd@123 -h 192.168.20.4 -P 8066
mysql> 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
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">

    <schema name="test_db" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
        <table name="customer" dataNode="dn2"/>
        <!--在下面的table字段增加global属性即可-->
        <table name="emp" dataNode="dn1,dn2" type="global">
        </table>
    </schema>
    <dataNode name="dn1" dataHost="host1" database="test" />
    <dataNode name="dn2" dataHost="host2" database="test" />
    <dataHost name="host1" maxCon="1000" minCon="10" balance="3"
              writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
        <heartbeat>select user()</heartbeat>
        <!-- can have multi write hosts -->
        <writeHost host="hostM1" url="192.168.20.2:3306" user="root"
                   password="123.com">
            <!-- can have multi read hosts -->
            <readHost host="hostS2" url="192.168.20.3:3306" user="root" password="123.com" />
        </writeHost>
    </dataHost>
    <dataHost name="host2" maxCon="1000" minCon="10" balance="0"
              writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
        <heartbeat>select user()</heartbeat>
        <!-- can have multi write hosts -->
        <writeHost host="hostM2" url="192.168.20.21:3306" user="root"
                   password="123.com">
        </writeHost>
    </dataHost>
</mycat:schema>

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)