MyCat实现读写分离+分库分表+全局表

比眉伴天荒 2023-07-04 15:44 25阅读 0赞

### 前言 ###

mycat功能强大，配置简单，作为数据库中间件，是一个非常优秀的开源产品，关于MyCat的部署安装，可以参考博文：[MyCat部署安装][MyCat]。

> 本博文将展示mycat的如下功能：
> 
>  *  读写分离；
>  *  分库分表；
>  *  全局表；

环境如下：

<table> 
 <thead> 
  <tr> 
   <th>系统</th> 
   <th>IP</th> 
   <th>主机名</th> 
   <th>服务</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>Centos 7.5</td> 
   <td>192.168.20.2</td> 
   <td>mysql01</td> 
   <td>MySQL 5.7.24</td> 
  </tr> 
  <tr> 
   <td>Centos 7.5</td> 
   <td>192.168.20.3</td> 
   <td>mysql02</td> 
   <td>MySQL 5.7.24</td> 
  </tr> 
  <tr> 
   <td>Centos 7.5</td> 
   <td>192.168.20.3</td> 
   <td>mysql03</td> 
   <td>MySQL 5.7.24</td> 
  </tr> 
  <tr> 
   <td>Centos 7.5</td> 
   <td>192.168.20.4</td> 
   <td>mycat</td> 
   <td>Mycat</td> 
  </tr> 
 </tbody> 
</table>

**注：主机mysql01和mysql02为主从复制关系**，可以参考博文[MySQL高可用方案——双主][MySQL]（注：只需要参考博文做出主从效果即可，并不需要双主，也不需要keepalived来做高可用）来搭建。mycat服务请参考博文[MyCat部署安装][MyCat]来搭建，这里新增的主机mysql03为一个刚刚搭建好的mysql服务器，可以参考博文[Centos部署MySQL 5.7][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    
            <user name="mycat" defaultAccount="true">  
                    <property name="password">pwd@123</property>   
                    <property name="schemas">test_mycat</property>    
                    
            </user>

#### 2）修改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>
                    
                                    
                    <writeHost host="hostM1" url="192.168.20.2:3306" user="root"
                                       password="123.com">
                            
                                                    
                            <readHost host="hostS2" url="192.168.20.3:3306" user="root" password="123.com" />
                    </writeHost>
            </dataHost>
    </mycat:schema>

> 注：上面提到的dataHost字段balance负载均衡类型，目前的取值有以下4 种：
> 
> 1.  balance="0", 不开启读写分离机制，所有读操作都发送到当前可用的 writeHost 上（默认值）。
> 2.  balance="1"，全部的 readHost 与 stand by writeHost 参与 select 语句的负载均衡，简单的说，当双主双从模式(M1->S1，M2->S2，并且 M1 与 M2 互为主备)，正常情况下，M2,S1,S2 都参与 select 语句的负载均衡。
> 3.  balance="2"，所有读操作都随机的在 writeHost、readhost 上分发。
> 4.  balance="3"，所有读请求随机的分发到 wiriterHost 对应的 readhost 执行，writerHost 不负担读压力，注意 balance=3 只在 1.4 及其以后版本有，1.3 没有。
> 
> writeTyep字段有以下3中取值：
> 
> 1、writeType="0", 所有写操作发送到配置的第一个 writeHost，第一个挂了切到还生存的第二个 writeHost，重新启动后已切换后的为准，切换记录在配置文件中:dnindex.properties 。  
> 2、writeType="1"，所有写操作都随机的发送到配置的 writeHost，1.5 以后废弃不推荐。switchType 属性
> 
>  *  \-1 表示不自动切换。
>  *  1 默认值，自动切换。
>  *  2 基于 MySQL 主从同步的状态决定是否切换。

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

#### 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>  
            <property name="useGlobleTableCheck">0</property>  
    
                    <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>
                    
                    <writeHost host="hostM1" url="192.168.20.2:3306" user="root"
                                       password="123.com">
                            
                            <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权限或换成其他有权限的用户）。

> 总结：  
> 1.指定的后端数据库登录用户，必须可以有相应的权限；  
> 2.在上面指定的后端真实数据库为test，所以后端必须要有这个test库；

#### 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>  
        <property name="useGlobleTableCheck">0</property>  
    
            <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"/>   
        </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>
            
            <writeHost host="hostM1" url="192.168.20.2:3306" user="root"
                       password="123.com">
                
                <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>
            
            <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"/>
            
            <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>
            
            <writeHost host="hostM1" url="192.168.20.2:3306" user="root"
                       password="123.com">
                
                <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>
            
            <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">    
                    <rule>
                            <columns>id</columns>    
                            <algorithm>mod_long</algorithm>  
                    </rule>
            </tableRule>
                            ...................
                                                    
            <function name="mod_long" class="io.mycat.route.function.PartitionByMod">
                    
                    <property name="count">2</property>    
            </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的方法（但不是唯一的方法），但全局表有一定的局限性，如下：

*  全局表变动不频繁；
 *  数据量总体变化不大；
 *  数据规模不大，很少有超数十万的记录。

> 全局表有以下特性：
> 
>  *  全局表的插入、更新操作会实时在所有节点上执行，保持各个分片的数据一致性；
>  *  全局表的查询操作，只从一个节点获取；
>  *  全局表可以跟任何一个表进行 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 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>
            
            <writeHost host="hostM1" url="192.168.20.2:3306" user="root"
                       password="123.com">
                
                <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>
            
            <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)

[MyCat]: https://blog.51cto.com/14154700/2470060
[MySQL]: https://blog.51cto.com/14154700/2469334
[Centos_MySQL 5.7]: https://blog.51cto.com/14154700/2455519