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=33092、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.gz3、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的数据是同步的，以此类推，可以解决主宕机后数据不同步的问题。