Mycat搭建负载均衡，读写分离的Mysql集群

Mycat搭建负载均衡，读写分离的Mysql集群

准备环境

1、mysql-5.7.24-linux-glibc2.12-x86_64.tar.gz

2、Mycat-server-1.6.7.4-test-20191113141017-linux.tar.gz

3、Centos7

运行环境

1个mycat，四个mysql实例（两个一主一从）。

下载安装配置运行

1、mysql安装配置运行

参考我的另一篇随笔：从零开始：mysql基于Amoeba的集群搭建

mysql需要创建3307,3308,3309,3310端口，其中，3307（主）和3308（从），3310（主）和3309（从）。

[root@iz2zeaf5jdjve80rjlsjgnz support-files]# ps -aux | grep mysql
root      5708  0.0  0.0 112712   964 pts/0    R+   23:25   0:00 grep --color=auto mysql
root      6218  0.0  0.0 113320  1608 pts/0    S    22:43   0:00 /bin/sh /software/mysql/mysql3310/bin/mysqld_safe --datadir=/software/mysql/mysql3310/data --pid-file=/software/mysql/mysql3310/mysqld.pid
mysql     7008  0.0 14.5 1796424 273876 pts/0  Sl   22:43   0:02 /software/mysql/mysql3310/bin/mysqld --basedir=/software/mysql/mysql3310 --datadir=/software/mysql/mysql3310/data --plugin-dir=/software/mysql/mysql3310/lib/plugin --user=mysql --log-error=iz2zeaf5jdjve80rjlsjgnz.err --open-files-limit=4161 --pid-file=/software/mysql/mysql3310/mysqld.pid --port=3310
root      7434  0.0  0.0 113320  1624 pts/0    S    23:15   0:00 /bin/sh /software/mysql/mysql3307/bin/mysqld_safe --datadir=/software/mysql/mysql3307/data --pid-file=/software/mysql/mysql3307/mysqld.pid
mysql     8214  0.1 15.3 1777900 288340 pts/0  Sl   23:15   0:00 /software/mysql/mysql3307/bin/mysqld --basedir=/software/mysql/mysql3307 --datadir=/software/mysql/mysql3307/data --plugin-dir=/software/mysql/mysql3307/lib/plugin --user=mysql --log-error=iz2zeaf5jdjve80rjlsjgnz.err --open-files-limit=4161 --pid-file=/software/mysql/mysql3307/mysqld.pid --port=3307
root     27817  0.0  0.0 113324  1300 pts/3    S    22:06   0:00 /bin/sh /software/mysql/mysql3308/bin/mysqld_safe --datadir=/software/mysql/mysql3308/data --pid-file=/software/mysql/mysql3308/mysqld.pid
mysql    28625  0.0  3.6 1795656 69588 pts/3   Sl   22:06   0:03 /software/mysql/mysql3308/bin/mysqld --basedir=/software/mysql/mysql3308 --datadir=/software/mysql/mysql3308/data --plugin-dir=/software/mysql/mysql3308/lib/plugin --user=mysql --log-error=iz2zeaf5jdjve80rjlsjgnz.err --open-files-limit=4161 --pid-file=/software/mysql/mysql3308/mysqld.pid --port=3308
root     29106  0.0  0.0 113324  1640 pts/3    S    22:07   0:00 /bin/sh /software/mysql/mysql3309/bin/mysqld_safe --datadir=/software/mysql/mysql3309/data --pid-file=/software/mysql/mysql3309/mysqld.pid
mysql    29916  0.0 15.1 1795660 284968 pts/3  Sl   22:07   0:03 /software/mysql/mysql3309/bin/mysqld --basedir=/software/mysql/mysql3309 --datadir=/software/mysql/mysql3309/data --plugin-dir=/software/mysql/mysql3309/lib/plugin --user=mysql --log-error=iz2zeaf5jdjve80rjlsjgnz.err --open-files-limit=4161 --pid-file=/software/mysql/mysql3309/mysqld.pid --port=3309

2、mycat安装

使用wget命令下载Mycat-server-1.6.7.4-test-20191113141017-linux.tar.gz

mkdir /software/mycat
cd /software/mycat
wget http://dl.mycat.io/1.6.7.4/Mycat-server-1.6.7.4-test-20191113141017-linux.tar.gz

使用tar解压

tar zxvf Mycat-server-1.6.7.4-test-20191113141017-linux.tar.gz

3、mycat配置

mycat配置文件主要有schema.xml和server.xml。

server.xml

<?xml version="1.0" encoding="UTF-8"?>
<!-- - - Licensed under the Apache License, Version 2.0 (the "License");
    - you may not use this file except in compliance with the License. - You
    may obtain a copy of the License at - - http://www.apache.org/licenses/LICENSE-2.0
    - - Unless required by applicable law or agreed to in writing, software -
    distributed under the License is distributed on an "AS IS" BASIS, - WITHOUT
    WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. - See the
    License for the specific language governing permissions and - limitations
    under the License. -->
<!DOCTYPE mycat:server SYSTEM "server.dtd">
<mycat:server xmlns:mycat="http://io.mycat/">
    <system>
    <property name="nonePasswordLogin">0</property> <!-- 0为需要密码登陆、1为不需要密码登陆 ,默认为0，设置为1则需要指定默认账户-->
    <property name="useHandshakeV10">1</property>
    <property name="useSqlStat">0</property>  <!-- 1为开启实时统计、0为关闭 -->
    <property name="useGlobleTableCheck">0</property>  <!-- 1为开启全加班一致性检测、0为关闭 -->
        <property name="sqlExecuteTimeout">300</property>  <!-- SQL 执行超时 单位:秒-->
        <property name="sequnceHandlerType">5</property>
        <!--<property name="sequnceHandlerPattern">(?:(\s*next\s+value\s+for\s*MYCATSEQ_(\w+))(,|\)|\s)*)+</property>-->
        <!--必须带有MYCATSEQ_或者 mycatseq_进入序列匹配流程 注意MYCATSEQ_有空格的情况-->
        <property name="sequnceHandlerPattern">(?:(\s*next\s+value\s+for\s*MYCATSEQ_(\w+))(,|\)|\s)*)+</property>
    <property name="subqueryRelationshipCheck">false</property> <!-- 子查询中存在关联查询的情况下,检查关联字段中是否有分片字段 .默认 false -->
    <property name="sequenceHanlderClass">io.mycat.route.sequence.handler.HttpIncrSequenceHandler</property>
      <!--  <property name="useCompression">1</property>--> <!--1为开启mysql压缩协议-->
        <!--  <property name="fakeMySQLVersion">5.6.20</property>--> <!--设置模拟的MySQL版本号-->
    <!-- <property name="processorBufferChunk">40960</property> -->
    <!--
    <property name="processors">1</property>
    <property name="processorExecutor">32</property>
     -->
        <!--默认为type 0: DirectByteBufferPool | type 1 ByteBufferArena | type 2 NettyBufferPool -->
        <property name="processorBufferPoolType">0</property>
        <!--默认是65535 64K 用于sql解析时最大文本长度 -->
        <!--<property name="maxStringLiteralLength">65535</property>-->
        <!--<property name="sequnceHandlerType">0</property>-->
        <!--<property name="backSocketNoDelay">1</property>-->
        <!--<property name="frontSocketNoDelay">1</property>-->
        <!--<property name="processorExecutor">16</property>-->
        <!--
            <property name="serverPort">8066</property> <property name="managerPort">9066</property>
            <property name="idleTimeout">300000</property> <property name="bindIp">0.0.0.0</property>
            <property name="dataNodeIdleCheckPeriod">300000</property> 5 * 60 * 1000L; //连接空闲检查
            <property name="frontWriteQueueSize">4096</property> <property name="processors">32</property> -->
        <!--分布式事务开关，0为不过滤分布式事务，1为过滤分布式事务（如果分布式事务内只涉及全局表，则不过滤），2为不过滤分布式事务,但是记录分布式事务日志-->
        <property name="handleDistributedTransactions">0</property>

            <!--
            off heap for merge/order/group/limit      1开启   0关闭
        -->
        <property name="useOffHeapForMerge">0</property>

        <!--
            单位为m
        -->
        <property name="memoryPageSize">64k</property>

        <!--
            单位为k
        -->
        <property name="spillsFileBufferSize">1k</property>

        <property name="useStreamOutput">0</property>

        <!--
            单位为m
        -->
        <property name="systemReserveMemorySize">384m</property>

        <!--是否采用zookeeper协调切换  -->
        <property name="useZKSwitch">false</property>

        <!-- XA Recovery Log日志路径 -->
        <!--<property name="XARecoveryLogBaseDir">./</property>-->

        <!-- XA Recovery Log日志名称 -->
        <!--<property name="XARecoveryLogBaseName">tmlog</property>-->
        <!--如果为 true的话 严格遵守隔离级别,不会在仅仅只有select语句的时候在事务中切换连接-->
        <property name="strictTxIsolation">false</property>

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

    </system>

    <!-- 全局SQL防火墙设置 -->
    <!--白名单可以使用通配符%或着*-->
    <!--例如<host host="127.0.0.*" user="root"/>-->
    <!--例如<host host="127.0.*" user="root"/>-->
    <!--例如<host host="127.*" user="root"/>-->
    <!--例如<host host="1*7.*" user="root"/>-->
    <!--这些配置情况下对于127.0.0.1都能以root账户登录-->
    <!--
    <firewall>
       <whitehost>
          <host host="1*7.0.0.*" user="root"/>
       </whitehost>
       <blacklist check="false">
       </blacklist>
    </firewall>
    -->

    <!--使用navicat等软件登录mycat的账户和密码-->
    <user name="mycat" defaultAccount="true">
        <property name="password">mycat</property>
    <!--类似于mysql的数据库名-->
        <property name="schemas">DB1</property>
        <property name="defaultSchema">DB1</property>
        <!--No MyCAT Database selected 错误前会尝试使用该schema作为schema，不设置则为null,报错 -->

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

    <user name="user">
        <property name="password">user</property>
        <property name="schemas">DB1</property>
        <property name="readOnly">true</property>
        <property name="defaultSchema">DB1</property>
    </user>

</mycat:server>

schema.xml

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

    <schema name="DB1" checkSQLschema="false" sqlMaxLimit="100">
        <table name="ADMIN" dataNode="dn1,dn2,dn3" rule="auto-sharding-long" />
    </schema>
    <!-- <dataNode name="dn1$0-743" dataHost="localhost1" database="db$0-743"
        /> -->
    <dataNode name="dn1" dataHost="localhost1" database="DB1" />
    <dataNode name="dn2" dataHost="localhost1" database="DB2" />
    <dataNode name="dn3" dataHost="localhost1" database="DB3" />

    <!--<dataNode name="dn4" dataHost="sequoiadb1" database="SAMPLE" />
     <dataNode name="jdbc_dn1" dataHost="jdbchost" database="db1" />
    <dataNode   name="jdbc_dn2" dataHost="jdbchost" database="db2" />
    <dataNode name="jdbc_dn3"   dataHost="jdbchost" database="db3" /> -->
    <dataHost name="localhost1" maxCon="1000" minCon="10" balance="3"
              writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
        <heartbeat>show slave status</heartbeat>
        <!-- can have multi write hosts -->
        <writeHost host="hostM1" url="localhost:3307" user="root"
                   password="root">
            <readHost host="hosts1" url="localhost:3308" user="root"
                       password="root">
            </readHost>
        </writeHost>
        <writeHost host="hostM2" url="localhost:3310" user="root"
                    password="root">
            <readHost host="hosts2" url="localhost:3309" user="root"
                       password="root">
            </readHost>
        </writeHost>
    </dataHost>

    <!--
        <dataHost name="sequoiadb1" maxCon="1000" minCon="1" balance="0" dbType="sequoiadb" dbDriver="jdbc">
        <heartbeat>         </heartbeat>
         <writeHost host="hostM1" url="sequoiadb://1426587161.dbaas.sequoialab.net:11920/SAMPLE" user="jifeng"  password="jifeng"></writeHost>
         </dataHost>

      <dataHost name="oracle1" maxCon="1000" minCon="1" balance="0" writeType="0"   dbType="oracle" dbDriver="jdbc"> <heartbeat>select 1 from dual</heartbeat>
        <connectionInitSql>alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss'</connectionInitSql>
        <writeHost host="hostM1" url="jdbc:oracle:thin:@127.0.0.1:1521:nange" user="base"   password="123456" > </writeHost> </dataHost>

        <dataHost name="jdbchost" maxCon="1000"     minCon="1" balance="0" writeType="0" dbType="mongodb" dbDriver="jdbc">
        <heartbeat>select   user()</heartbeat>
        <writeHost host="hostM" url="mongodb://192.168.0.99/test" user="admin" password="123456" ></writeHost> </dataHost>

        <dataHost name="sparksql" maxCon="1000" minCon="1" balance="0" dbType="spark" dbDriver="jdbc">
        <heartbeat> </heartbeat>
         <writeHost host="hostM1" url="jdbc:hive2://feng01:10000" user="jifeng"     password="jifeng"></writeHost> </dataHost> -->

    <!-- <dataHost name="jdbchost" maxCon="1000" minCon="10" balance="0" dbType="mysql"
        dbDriver="jdbc"> <heartbeat>select user()</heartbeat> <writeHost host="hostM1"
        url="jdbc:mysql://localhost:3306" user="root" password="123456"> </writeHost>
        </dataHost> -->
</mycat:schema>

首先schema标签中，设置的名字和server.xml设置的必须一致，它的子节点table表示设置哪个表，dataNode设置的是下面dataNode标签的name，表示将这个表分片到哪些database，dataHost属性设置的是下面dataHost的name。dataHost中writeHost设置的是3307（主服务器），readHost设置的是3308（从服务器）。第二个writeHost设置的和上面的相同，将writeHost设置的是3310（主服务器），readHost设置的是3308（从服务器）。

其中dataHost的balance、writeType、switchType属性。

balance， switchType， writeType
balance="0", 不开启读写分离机制，所有读操作都发送到当前可用的writeHost上。
balance="1"，全部的readHost与stand by writeHost参与select语句的负载均衡，简单的说，当双主双从模式(M1->S1，M2->S2，并且M1与M2互为主备)，正常情况下，M2,S1,S2都参与select语句的负载均衡。
balance="2"，所有读操作都随机的在writeHost、readhost上分发。
balance="3"，所有读请求随机的分发到writeHost下的readhost执行，writeHost不负担读压力
writeType表示写模式
writeType="0"，所有的操作发送到配置的第一个writehost
writeType="1"，随机发送到配置的所有writehost
writeType="2"，不执行写操作
switchType指的是切换的模式，目前的取值也有4种：
switchType=‘-1‘ 表示不自动切换
switchType=‘1‘ 默认值，表示自动切换
switchType=‘2‘ 基于MySQL主从同步的状态决定是否切换,心跳语句为show slave status，dnindex.properties里面的localhost1的值会发生改变。
switchType=‘3‘基于MySQL galary cluster的切换机制（适合集群）（1.4.1），心跳语句为show status like ‘wsrep%‘。

由于switchType设置的2，所以heartbeat的值是show slave status。

4、mycat运行

cd /software/mycat/mycat/bin
./mycat start

使用navicat连接（默认是8066端口）

5、测试心跳

[root@iz2zeaf5jdjve80rjlsjgnz bin]# ./mysql -h127.0.0.1 -umycat -P9066 -pmycat
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 98
Server version: 5.7.24-log MySQL Community Server (GPL)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> 

使用show命令

mysql> show @@heartbeat;
+--------+-------+-----------+------+---------+-------+--------+---------+--------------+---------------------+-------+
| NAME   | TYPE  | HOST      | PORT | RS_CODE | RETRY | STATUS | TIMEOUT | EXECUTE_TIME | LAST_ACTIVE_TIME    | STOP  |
+--------+-------+-----------+------+---------+-------+--------+---------+--------------+---------------------+-------+
| hostM1 | mysql | localhost | 3307 |       1 |     0 | idle   |   30000 | 65,22,22     | 2019-11-24 23:57:12 | false |
| hostM2 | mysql | localhost | 3310 |       1 |     0 | idle   |   30000 | 99,33,33     | 2019-11-24 23:57:12 | false |
| hosts1 | mysql | localhost | 3308 |       1 |     0 | idle   |   30000 | 81,33,33     | 2019-11-24 23:57:12 | false |
| hosts2 | mysql | localhost | 3309 |       1 |     0 | idle   |   30000 | 82,30,30     | 2019-11-24 23:57:12 | false |
+--------+-------+-----------+------+---------+-------+--------+---------+--------------+---------------------+-------+
4 rows in set (0.00 sec)

mysql> 

---

rs_code：表示心跳状态

• 0：INIT_STATUS 表示初始化状态
• 1：OK_STATUS 表示正常状态
• -1：ERROR_STATUS 表示连接出错
• -2：TIMEOUT_STATUS 表示连接超时

若节点发生故障，会连续进行默认5个周期的检测，心跳连续失败后会变成 -1 。

---

其中RS_CODE都是1，表示所有mysql实例状态正常。

6、测试分片

mysql> create table admin (id bigint not null primary key,user_id varchar(100),traveldate DATE, fee decimal,days int);
Query OK, 0 rows affected (0.22 sec)
mysql> insert into ADMIN(id,user_id,traveldate,fee,days)  values(1,@@hostname,20160101,100,10);
Query OK, 1 row affected (0.28 sec)
mysql> insert into ADMIN(id,user_id,traveldate,fee,days)  values(5000001,@@hostname,20160101,100,10);
Query OK, 1 row affected (0.18 sec)
mysql> insert into ADMIN(id,user_id,traveldate,fee,days)  values(10000001,@@hostname,20160101,100,10);
Query OK, 1 row affected (0.19 sec)

因为在schema.xml中配置了table的rule属性为auto-sharding-long。

这个属性值在rule.xml的内容为

<tableRule name="auto-sharding-long">
        <rule>
            <columns>id</columns>
            <algorithm>rang-long</algorithm>
        </rule>
    </tableRule>
<function name="rang-long"
              class="io.mycat.route.function.AutoPartitionByLong">
        <property name="mapFile">autopartition-long.txt</property>
    </function>

对应的文件是autopartition-long.txt，打开这个文件

# range start-end ,data node index
# K=1000,M=10000.
0-500M=0
500M-1000M=1
1000M-1500M=2

表示id在0-500M范围内，500M-1000M范围内，1000M-1500M范围内，分别在不同的数据库。

7、测试读写分离

查询ADMIN表。

mysql> select * from ADMIN;
+----------+-------------------------+------------+-----+------+
| ID       | USER_ID                 | TRAVELDATE | FEE | DAYS |
+----------+-------------------------+------------+-----+------+
|        1 | iz2zeaf5jdjve80rjlsjgnz | 2016-01-01 | 100 |   10 |
|  5000001 | iz2zeaf5jdjve80rjlsjgnz | 2016-01-01 | 100 |   10 |
| 10000001 | iz2zeaf5jdjve80rjlsjgnz | 2016-01-01 | 100 |   10 |
+----------+-------------------------+------------+-----+------+
3 rows in set (0.48 sec)

关闭3310主服务器。

kill -9 6218 7008

此时心跳测试出现-1。

dnindex.properties的localhost1发生改变，说明已经切换到另一个主服务器（关闭之前是0）。

#update
#Mon Nov 25 23:58:11 CST 2019
localhost1=1

mysql> show @@heartbeat;
+--------+-------+-----------+------+---------+-------+--------+---------+--------------+---------------------+-------+
| NAME   | TYPE  | HOST      | PORT | RS_CODE | RETRY | STATUS | TIMEOUT | EXECUTE_TIME | LAST_ACTIVE_TIME    | STOP  |
+--------+-------+-----------+------+---------+-------+--------+---------+--------------+---------------------+-------+
| hostM1 | mysql | localhost | 3307 |       1 |     0 | idle   |   30000 | 2,2,3        | 2019-11-25 00:23:16 | false |
| hostM2 | mysql | localhost | 3310 |      -1 |     2 | idle   |   30000 | 17,3,3       | 2019-11-25 00:23:16 | false |
| hosts1 | mysql | localhost | 3308 |       1 |     0 | idle   |   30000 | 2,2,2        | 2019-11-25 00:23:16 | false |
| hosts2 | mysql | localhost | 3309 |       1 |     0 | idle   |   30000 | 2,3,3        | 2019-11-25 00:23:16 | false |
+--------+-------+-----------+------+---------+-------+--------+---------+--------------+---------------------+-------+
4 rows in set (0.16 sec)

再次查询ADMIN表。

mysql> select * from ADMIN;
1105 - Table 'DB1.ADMIN' doesn't exist

结论：

1、设置了两个及以上的writeHost后，如果默认的writeHost出现宕机，那么会切换到第二个writeHost，相应的，dnindex.properties的localhost1值会改变，show @@heartbeat;命令查询到的HOST对应的RS_CODE会变成-1。

2、自动切换writeHost之后，第一个writeHost的值不会同步过去，所以再次查询ADMIN表会出现Table 'DB1.ADMIN' doesn't exist。

解决办法：

有四个mysql实例

mysql3307
mysql3308
mysql3309
mysql3310

在schema.xml中配置：

<writeHost Host="mysql3307">
    <readHost Host="mysql3308"></readHost>
</writeHost>
<writeHost Host="mysql3308">
    <readHost Host="mysql3309"></readHost>
</writeHost>
<writeHost Host="mysql3309">
    <readHost Host="mysql3310"></readHost>
</writeHost>

在3307宕机后，3308复制的3307，所以3308的数据和3307是一样的，切换到3308后，3309和3308的数据是同步的，以此类推，可以解决主宕机后数据不同步的问题。