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

发布时间：2020-05-30 12:02:41 来源：亿速云阅读：455 作者：鸽子栏目：MySQL数据库

环境如下：

系统	IP	主机名	服务
Centos 7.5	192.168.20.2	mysql01	MySQL 5.7.24
Centos 7.5	192.168.20.3	mysql02	MySQL 5.7.24
Centos 7.5	192.168.20.3	mysql03	MySQL 5.7.24
Centos 7.5	192.168.20.4	mycat	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)

向AI问一下细节

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

1、mycat实现读写分离

1）修改server.xml文件

2）修改schema.xml文件

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

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

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

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

2、mycat实现分库

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

2）修改schema.xml文件

3）重启mycat

4）测试分库效果

3、mycat实现分表

1）schema.xml文件修改如下（完整配置文件）

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

3）重启mycat并登录到mycat

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

3、全局表

1）定义全局表

2）验证全局表

猜你喜欢

最新资讯

相关推荐

相关标签