男人50
- 浏览: 237252 次
- 性别:
- 来自: 珠海
-
文章分类
- 全部博客 (196)
- 职业感想 (66)
- hadoop (7)
- spark (5)
- mycat (13)
- Raft (2)
- nexus (3)
- Nginx (5)
- SpringBoot (5)
- Mongodb (5)
- amq (2)
- shell (3)
- netty (3)
- spring 5.0 (2)
- 响应式编程 (1)
- Spring Cloud (4)
- pdf (1)
- docker (17)
- Kubernetes (1)
- 技术总监 (1)
- 区块链 (1)
- 大数据 (1)
- Kylin (1)
- NIO (1)
- JVM (2)
- zookeeper (1)
- Python (2)
- Docker-Compose (2)
- mysql (14)
- eclipse (1)
- spring cloud config (1)
- Redis (1)
- centos (2)
- tokudb (1)
- findbugs (1)
- HikariCP (1)
- php (1)
- ES (1)
- ZigBee (1)
- 物联网 (1)
- NLP (1)
- ionic (1)
- go (4)
- node red (3)
- 树莓派 (1)
- iot (1)
- pm2 (1)
- nodejs (1)
- Supervisor (1)
- dbus (1)
- linux (1)
- vpn (1)
- arm (1)
- debian (1)
- consul (1)
- Hystrix (1)
- InheritableThreadLocal (1)
最新评论
-
男人50：
不远啊 写道难道大多程序猿都是这样过来的吗，接着后来有一部分当 ...
刚毕业的时候 -
不远啊：
难道大多程序猿都是这样过来的吗，接着后来有一部分当了老师教着新 ...
刚毕业的时候 -
男人50：
...
ES 与关系型数据库的对比 -
liaodongdakai：
精通并发与Netty网盘地址：https://pan.baid ...
精通netty框架 -
男人50：
knight_black_bob 写道这内容怎么审核的，你好， ...
我从事技术的这些年（第12年）
实验环境介绍
采用的是在同一台机器上部署MyCAT，MySQL二主一从，其中3306和3307互为主从，3308为3306的从库。具体部署情况如下：
MyCAT：192.168.237.15，Port：8066/9066
MySQL Master：192.168.237.15，Port：3306
MySQL Master（Standby）：192.168.237.15，Port：3307
MySQL Slave：192.168.237.15，Port：3308
导入测试数据
真实数据库上创建testdb1、testdb2、testdb3库下的travelrecord表。
mysql> show create table travelrecord \G
*************************** 1. row ***************************
Table: travelrecord
Create Table: CREATE TABLE `travelrecord` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`org_code` varchar(20) NOT NULL,
`test_name` varchar(20) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
***读写分离（负载均衡）***
1、首先设定MyCAT实例中的逻辑库名（schema name）为DB1。
并定义这个逻辑库下存在一个逻辑表（table name）为travelrecord，该逻辑表所属的数据分片有：dataNode（dn1）
2、定义dataNode所在的dataHost、以及dataNode对应的真实database（testdb）。
定义dataHost拥有的writeHost和readHost（3306为write host，3307为备用write host，3308为read host）
据此，在MyCAT的schema.xml文件中配置如下：
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://org.opencloudb/" >
<!--定义MyCAT实例的逻辑库和逻辑表-->
<schema name="DB1" checkSQLschema="false" sqlMaxLimit="100">
<table name="travelrecord" primaryKey="id" dataNode="dn1,dn2,dn3" />
</schema>
<!--定义MyCAT逻辑表中提到的所有dataNode所在的真实Host，以及dataNode所属的真实数据库-->
<dataNode name="dn1" dataHost="237_15" database="testdb1" />
<dataNode name="dn2" dataHost="237_15" database="testdb2" />
<dataNode name="dn3" dataHost="237_15" database="testdb3" />
<mycat:schema xmlns:mycat="http://org.opencloudb/" >
<table name="travelrecord" primaryKey="id" dataNode="dn1,dn2,dn3" />
</schema>
<!--定义MyCAT逻辑表中提到的所有dataNode所在的真实Host，以及dataNode所属的真实数据库-->
<dataNode name="dn1" dataHost="237_15" database="testdb1" />
<dataNode name="dn2" dataHost="237_15" database="testdb2" />
<dataNode name="dn3" dataHost="237_15" database="testdb3" />
<!--定义dataNode提到的dataHost的连接限制数、负载均衡取向，以及真实的读写地址(writeHost和readHost)-->
<dataHost name="237_15" maxCon="1000" minCon="3" balance="1"
writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<writeHost host="MySQL_M1" url="192.168.237.15:3306" user="root" password="123456">
<readHost host="MySQL_S1" url="192.168.237.15:3308" user="root" password="123456" />
</writeHost>
<writeHost host="MySQL_M2" url="192.168.237.15:3307" user="root" password="123456">
<!--M2无对应的Slave库，此处留白 -->
</writeHost>
</dataHost>
</mycat:schema>
参数解读
schema.xml中的balance的取值决定了负载均衡对非事务内的读操作的处理：
balance="0"：读请求仅发送到writeHost上（不开启读写分离）。
balance="1"：读请求随机分发到当前writeHost对应的readHost和standby的writeHost上。
balance="2"：读请求随机分发到当前dataHost内所有的writeHost和readHost上。
balance="3"：读请求随机分发到当前writeHost对应的readHost上。
对于事务内的SQL默认走写节点；
以 /*balance*/ 开头，可以指定SQL使用特定负载均衡方案。例如在大环境开启读写分离的情况下，特定强一致性的SQL查询需求；
slaveThreshold：近似的主从延迟时间（秒）Seconds_Behind_Master < slaveThreshold ，读请求才会分发到该Slave，确保读到的数据相对较新。
schema.xml中的writeType的取值决定了负载均衡对写操作的处理：
writeType="0"：所有的写操作都发送到配置文件中的第一个write host。（第一个write host故障切换到第二个后，即使之后修复了仍然维持第二个为写库）。推荐取0值，不建议修改.
分类演示
事先我们要打开MyCAT的debug模式。# vim log4j.xml，将info修改为debug
<root>
<level value="<strong>debug</strong>" />
<appender-ref ref="FILE" />
<!--<appender-ref ref="FILE" />-->
</root>
下面通过变换配置文件中的balance值，执行写操作：select * from travelrecord来演示各种读写分离的情况。
（1）balance=“0”，查看debug日志，走MySQL_M1
08/17 15:37:49.712 DEBUG [$_NIOREACTOR-0-RW] (MultiNodeQueryHandler.java:82) -execute mutinode query select * from travelrecord
08/17 15:37:49.712 DEBUG [$_NIOREACTOR-0-RW] (MultiNodeQueryHandler.java:97) -has data merge logic
08/17 15:37:49.713 DEBUG [$_NIOREACTOR-0-RW] (PhysicalDBPool.java:452) -<strong>select read source MySQL_M1 for dataHost:237_15</strong>
08/17 15:37:49.714 DEBUG [$_NIOREACTOR-0-RW] (PhysicalDBPool.java:452) -<strong>select read source MySQL_M1 for dataHost:237_15</strong>
08/17 15:37:49.715 DEBUG [$_NIOREACTOR-0-RW] (PhysicalDBPool.java:452) -<strong>select read source MySQL_M1 for dataHost:237_15</strong>
08/17 15:37:49.718 DEBUG [$_NIOREACTOR-0-RW] (DataMergeService.java:102) -field metadata inf:[TEST_NAME=ColMeta [colIndex=2, colType=253], ORG_CODE=ColMeta [colIndex=1, colType=253], ID=ColMeta [colIndex=0, colType=3]]
08/17 15:37:49.719 DEBUG [$_NIOREACTOR-0-RW] (MultiNodeQueryHandler.java:250) -on row end reseponse MySQLConnection [id=1, lastTime=1502955469708, user=root, schema=testdb2, old shema=testdb2, borrowed=true, fromSlaveDB=false, threadId=31, charset=utf8, txIsolation=3, autocommit=true, attachment=dn2{SELECT *
FROM travelrecord
LIMIT 100}, respHandler=org.opencloudb.mysql.nio.handler.MultiNodeQueryHandler@5e5f215f, host=192.168.237.15, <strong>port=3306</strong>, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]
（2）balance=“1”，查看debug日志，走MySQL_M2或者MySQL_S1
08/17 15:56:10.023 DEBUG [$_NIOREACTOR-0-RW] (MultiNodeQueryHandler.java:82) -execute mutinode query select * from travelrecord
08/17 15:56:10.023 DEBUG [$_NIOREACTOR-0-RW] (MultiNodeQueryHandler.java:97) -has data merge logic
08/17 15:56:10.023 DEBUG [$_NIOREACTOR-0-RW] (PhysicalDBPool.java:452) -<strong>select read source MySQL_M2 for dataHost:237_15</strong>
08/17 15:56:10.027 DEBUG [$_NIOREACTOR-0-RW] (PhysicalDBPool.java:452) -<strong>select read source MySQL_M2 for dataHost:237_15</strong>
08/17 15:56:10.028 DEBUG [$_NIOREACTOR-0-RW] (PhysicalDBPool.java:452) -<strong>select read source MySQL_M2 for dataHost:237_15</strong>
08/17 15:56:10.030 DEBUG [$_NIOREACTOR-0-RW] (DataMergeService.java:102) -field metadata inf:[TEST_NAME=ColMeta [colIndex=2, colType=253], ORG_CODE=ColMeta [colIndex=1, colType=253], ID=ColMeta [colIndex=0, colType=3]]
08/17 15:56:10.031 DEBUG [$_NIOREACTOR-0-RW] (MultiNodeQueryHandler.java:250) -on row end reseponse MySQLConnection [id=8, lastTime=1502956570027, user=root, schema=testdb3, old shema=testdb3, borrowed=true, fromSlaveDB=false, threadId=17, charset=utf8, txIsolation=3, autocommit=true, attachment=dn3{SELECT *
FROM travelrecord
LIMIT 100}, respHandler=org.opencloudb.mysql.nio.handler.MultiNodeQueryHandler@5209c6a4, host=192.168.237.15, <strong>port=3307</strong>, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]
08/17 16:03:50.225 DEBUG [$_NIOREACTOR-0-RW] (MultiNodeQueryHandler.java:82) -execute mutinode query select * from travelrecord
08/17 16:03:50.225 DEBUG [$_NIOREACTOR-0-RW] (MultiNodeQueryHandler.java:97) -has data merge logic
08/17 16:03:50.225 DEBUG [$_NIOREACTOR-0-RW] (PhysicalDBPool.java:452) -<strong>select read source MySQL_M2 for dataHost:237_15</strong>
08/17 16:03:50.226 DEBUG [$_NIOREACTOR-0-RW] (PhysicalDBPool.java:452) -<strong>select read source MySQL_S1 for dataHost:237_15</strong>
08/17 16:03:50.226 DEBUG [$_NIOREACTOR-0-RW] (PhysicalDBPool.java:452) -<strong>select read source MySQL_M2 for dataHost:237_15</strong>
08/17 16:03:50.227 DEBUG [$_NIOREACTOR-0-RW] (DataMergeService.java:102) -field metadata inf:[TEST_NAME=ColMeta [colIndex=2, colType=253], ORG_CODE=ColMeta [colIndex=1, colType=253], ID=ColMeta [colIndex=0, colType=3]]
08/17 16:03:50.227 DEBUG [$_NIOREACTOR-0-RW] (MultiNodeQueryHandler.java:250) -on row end reseponse MySQLConnection [id=6, lastTime=1502957030209, user=root, schema=testdb2, old shema=testdb2, borrowed=true, fromSlaveDB=true, threadId=18, charset=utf8, txIsolation=3, autocommit=true, attachment=dn2{SELECT *
FROM travelrecord
LIMIT 100}, respHandler=org.opencloudb.mysql.nio.handler.MultiNodeQueryHandler@5cd051b6, host=192.168.237.15, <strong>port=3308</strong>, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]
08/17 16:03:50.228 DEBUG [$_NIOREACTOR-0-RW] (NonBlockingSession.java:229) -release connection MySQLConnection [id=6, lastTime=1502957030209, user=root, schema=testdb2, old shema=testdb2, borrowed=true, fromSlaveDB=true, threadId=18, charset=utf8, txIsolation=3, autocommit=true, attachment=dn2{SELECT *
FROM travelrecord
LIMIT 100}, respHandler=org.opencloudb.mysql.nio.handler.MultiNodeQueryHandler@5cd051b6, host=192.168.237.15, <strong>port=3308</strong>, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]
08/17 16:03:50.228 DEBUG [$_NIOREACTOR-0-RW] (PhysicalDatasource.java:403) -release channel MySQLConnection [id=6, lastTime=1502957030209, user=root, schema=testdb2, old shema=testdb2, borrowed=true, fromSlaveDB=true, threadId=18, charset=utf8, txIsolation=3, autocommit=true, attachment=null, respHandler=null, host=192.168.237.15, port=3308, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]
08/17 16:03:50.231 DEBUG [$_NIOREACTOR-0-RW] (MultiNodeQueryHandler.java:250) -on row end reseponse MySQLConnection [id=8, lastTime=1502957030209, user=root, schema=testdb3, old shema=testdb3, borrowed=true, fromSlaveDB=false, threadId=17, charset=utf8, txIsolation=3, autocommit=true, attachment=dn3{SELECT *
FROM travelrecord
LIMIT 100}, respHandler=org.opencloudb.mysql.nio.handler.MultiNodeQueryHandler@5cd051b6, host=192.168.237.15, <strong>port=3307</strong>, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]
08/17 16:03:50.232 DEBUG [$_NIOREACTOR-0-RW] (NonBlockingSession.java:229) -release connection MySQLConnection [id=8, lastTime=1502957030209, user=root, schema=testdb3, old shema=testdb3, borrowed=true, fromSlaveDB=false, threadId=17, charset=utf8, txIsolation=3, autocommit=true, attachment=dn3{SELECT *
FROM travelrecord
LIMIT 100}, respHandler=org.opencloudb.mysql.nio.handler.MultiNodeQueryHandler@5cd051b6, host=192.168.237.15, <strong>port=3307</strong>, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]
08/17 16:03:50.232 DEBUG [$_NIOREACTOR-0-RW] (PhysicalDatasource.java:403) -release channel MySQLConnection [id=8, lastTime=1502957030209, user=root, schema=testdb3, old shema=testdb3, borrowed=true, fromSlaveDB=false, threadId=17, charset=utf8, txIsolation=3, autocommit=true, attachment=null, respHandler=null, host=192.168.237.15, port=3307, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]
08/17 16:03:50.239 DEBUG [$_NIOREACTOR-0-RW] (MultiNodeQueryHandler.java:250) -on row end reseponse MySQLConnection [id=5, lastTime=1502957030209, user=root, schema=testdb1, old shema=testdb1, borrowed=true, fromSlaveDB=false, threadId=15, charset=utf8, txIsolation=3, autocommit=true, attachment=dn1{SELECT *
FROM travelrecord
LIMIT 100}, respHandler=org.opencloudb.mysql.nio.handler.MultiNodeQueryHandler@5cd051b6, host=192.168.237.15, <strong>port=3307</strong>, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]
08/17 16:03:50.239 DEBUG [$_NIOREACTOR-0-RW] (NonBlockingSession.java:229) -release connection MySQLConnection [id=5, lastTime=1502957030209, user=root, schema=testdb1, old shema=testdb1, borrowed=true, fromSlaveDB=false, threadId=15, charset=utf8, txIsolation=3, autocommit=true, attachment=dn1{SELECT *
FROM travelrecord
LIMIT 100}, respHandler=org.opencloudb.mysql.nio.handler.MultiNodeQueryHandler@5cd051b6, host=192.168.237.15, <strong>port=3307</strong>, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]
（3）balance=“2”，查看debug日志，走MySQL_M1或者MySQL_S1
08/17 16:10:48.867 DEBUG [$_NIOREACTOR-0-RW] (MultiNodeQueryHandler.java:82) -execute mutinode query select * from travelrecord
08/17 16:10:48.867 DEBUG [$_NIOREACTOR-0-RW] (MultiNodeQueryHandler.java:97) -has data merge logic
08/17 16:10:48.868 DEBUG [$_NIOREACTOR-0-RW] (PhysicalDBPool.java:452) -<strong>select read source MySQL_M1 for dataHost:237_15</strong>
08/17 16:10:48.869 DEBUG [$_NIOREACTOR-0-RW] (MySQLConnection.java:459) -con need syn ,total syn cmd 1 commands SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;schema change:false con:MySQLConnection [id=1, lastTime=1502957448869, user=root, schema=testdb1, old shema=testdb1, borrowed=true, fromSlaveDB=false, threadId=38, charset=utf8, txIsolation=0, autocommit=true, attachment=dn1{SELECT *
FROM travelrecord
LIMIT 100}, respHandler=org.opencloudb.mysql.nio.handler.MultiNodeQueryHandler@17005763, host=192.168.237.15, <strong>port=3306</strong>, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]
08/17 16:10:48.876 DEBUG [$_NIOREACTOR-0-RW] (PhysicalDBPool.java:452) -<strong>select read source MySQL_S1 for dataHost:237_15</strong>
08/17 16:10:48.876 DEBUG [$_NIOREACTOR-0-RW] (MySQLConnection.java:459) -con need syn ,total syn cmd 2 commands SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;schema change:true con:MySQLConnection [id=4, lastTime=1502957448876, user=root, schema=testdb2, old shema=testdb3, borrowed=true, fromSlaveDB=true, threadId=20, charset=utf8, txIsolation=0, autocommit=true, attachment=dn2{SELECT *
FROM travelrecord
LIMIT 100}, respHandler=org.opencloudb.mysql.nio.handler.MultiNodeQueryHandler@17005763, host=192.168.237.15, <strong>port=3308</strong>, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]
此处之后日志省略...
（4）balance=“3”，查看debug日志，走MySQL_M1的从库：MySQL_S1
08/17 16:15:54.267 DEBUG [$_NIOREACTOR-0-RW] (MultiNodeQueryHandler.java:82) -execute mutinode query select * from travelrecord
08/17 16:15:54.267 DEBUG [$_NIOREACTOR-0-RW] (MultiNodeQueryHandler.java:97) -has data merge logic
08/17 16:15:54.268 DEBUG [$_NIOREACTOR-0-RW] (PhysicalDBPool.java:452) -<strong>select read source MySQL_S1 for dataHost:237_15</strong>
08/17 16:15:54.270 DEBUG [$_NIOREACTOR-0-RW] (PhysicalDBPool.java:452) -<strong>select read source MySQL_S1 for dataHost:237_15</strong>
08/17 16:15:54.271 DEBUG [$_NIOREACTOR-0-RW] (PhysicalDBPool.java:452) -<strong>select read source MySQL_S1 for dataHost:237_15</strong>
08/17 16:15:54.274 DEBUG [$_NIOREACTOR-0-RW] (DataMergeService.java:102) -field metadata inf:[TEST_NAME=ColMeta [colIndex=2, colType=253], ORG_CODE=ColMeta [colIndex=1, colType=253], ID=ColMeta [colIndex=0, colType=3]]
08/17 16:15:54.276 DEBUG [$_NIOREACTOR-0-RW] (MultiNodeQueryHandler.java:250) -on row end reseponse MySQLConnection [id=4, lastTime=1502957754253, user=root, schema=testdb1, old shema=testdb1, borrowed=true, fromSlaveDB=true, threadId=21, charset=utf8, txIsolation=3, autocommit=true, attachment=dn1{SELECT *
FROM travelrecord
LIMIT 100}, respHandler=org.opencloudb.mysql.nio.handler.MultiNodeQueryHandler@5615b9cb, host=192.168.237.15, <strong>port=3308</strong>, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]
此处之后日志省略...
MyCAT插入测试数据：mysql> insert into travelrecord values(1,"china86","mike");
查看debug日志，写操作走MySQL_M1
08/17 16:38:49.995 DEBUG [$_NIOREACTOR-0-RW] (ServerQueryHandler.java:56) -ServerConnection [id=1, schema=DB1, host=127.0.0.1, user=test,txIsolation=3, autocommit=true, schema=DB1]insert into travelrecord values(1,"china86","mike")
08/17 16:38:50.002 DEBUG [$_NIOREACTOR-0-RW] (NonBlockingSession.java:113) -ServerConnection [id=1, schema=DB1, host=127.0.0.1, user=test,txIsolation=3, autocommit=true, schema=DB1]insert into travelrecord values(1,"china86","mike"), route={
1 -> dn1{insert into travelrecord values(1,"china86","mike")}
2 -> dn2{insert into travelrecord values(1,"china86","mike")}
3 -> dn3{insert into travelrecord values(1,"china86","mike")}
} rrs
08/17 16:38:50.003 DEBUG [$_NIOREACTOR-0-RW] (MultiNodeQueryHandler.java:82) -execute mutinode query insert into travelrecord values(1,"china86","mike")
08/17 16:38:50.003 DEBUG [$_NIOREACTOR-0-RW] (MySQLConnection.java:459) -con need syn ,total syn cmd 1 commands SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;schema change:false con:MySQLConnection [id=2, lastTime=1502959130003, user=root, schema=testdb1, old shema=testdb1, borrowed=true, fromSlaveDB=false, threadId=41, charset=utf8, txIsolation=0, autocommit=true, attachment=dn1{insert into travelrecord values(1,"china86","mike")}, respHandler=org.opencloudb.mysql.nio.handler.MultiNodeQueryHandler@6b4de96c, host=192.168.237.15, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=true]
08/17 16:38:50.006 DEBUG [$_NIOREACTOR-0-RW] (MySQLConnection.java:459) -con need syn ,total syn cmd 1 commands SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;schema change:false con:MySQLConnection [id=3, lastTime=1502959130006, user=root, schema=testdb2, old shema=testdb2, borrowed=true, fromSlaveDB=false, threadId=40, charset=utf8, txIsolation=0, autocommit=true, attachment=dn2{insert into travelrecord values(1,"china86","mike")}, respHandler=org.opencloudb.mysql.nio.handler.MultiNodeQueryHandler@6b4de96c, host=192.168.237.15, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=true]
***主从切换（双主failover）***
如果我们细心观察schem.xml文件的话，会发现之前有一个参数：switchType尚未提及。
<dataHost name="237_15" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="native"switchType="1" slaveThreshold="100">
参数解读
switchType="-1"：不自动切换
switchType="1"：自动切换
switchType="2"：基于MySQL主从复制的状态来决定是否切换。需修改heartbeat语句：show slave status
switchType="3"：基于Galera（集群多节点复制）的切换机制。需修改heartbeat语句：show status like 'wsrep%'
switchType="2"的切换演示
先修改heartbeat语句
<dataHost name="237_15" maxCon="1000" minCon="3" balance="1"
writeType="0" dbType="mysql" dbDriver="native" switchType="2" slaveThreshold="100">
<heartbeat>show slave status</heartbeat>
为了区分是主库还是从库插入的数据，M1、M2设置不同自增步长，通过id的奇偶性来判断，避免通过查debug日志的方式来区分。
MySQL_M1：auto_increment_increment=2，auto_increment_offset=1
MySQL_M2：auto_increment_increment=2，auto_increment_offset=2
（1）M1、M2两个节点均正常的情况下，设想情况：MyCAT选择M1插入数据
插入两组测试数据：
mysql> insert into travelrecord(org_code,test_name) values("china86","tom");
mysql> insert into travelrecord(org_code,test_name) values("china86","jack");
mysql> select *from travelrecord;
+----+----------+-----------+
| id | org_code | test_name |
+----+----------+-----------+
| 1 | china86 | tom |
| 3 | china86 | jack |
+----+----------+-----------+
id为奇数，故写入的数据库是M1，符合预期
（2）M1宕机，设想情况：MyCAT切换至M2作为写入节点
此时，我们模拟故障，手动停止3306上的MySQL实例
[root@237_15 conf]# /etc/init.d/mysql3306 stop
Shutting down MySQL............ SUCCESS!
再往MyCAT内插入两组数据：
mysql> insert into travelrecord(org_code,test_name) values("china86","marry");
mysql> insert into travelrecord(org_code,test_name) values("china86","ellen");
mysql> select *from travelrecord;
+----+----------+-----------+
| id | org_code | test_name |
+----+----------+-----------+
| 1 | china86 | tom |
| 3 | china86 | jack |
| 4 | china86 | marry |
| 6 | china86 | ellen |
+----+----------+-----------+
id为偶数，故写入的是M2，符合预期
再截取一段debug日志，确认写入的实例端口为3307（MySQL_M2）
08/17 22:10:40.330 DEBUG [$_NIOREACTOR-0-RW] (MultiNodeQueryHandler.java:171) -received ok response ,executeResponse:true from MySQLConnection [id=7, lastTime=1502979040315, user=root, schema=testdb2, old shema=testdb2, borrowed=true, fromSlaveDB=false, threadId=5, charset=utf8, txIsolation=3, autocommit=true, attachment=dn2{insert into travelrecord(org_code,test_name) values("china86","ellen")}, respHandler=org.opencloudb.mysql.nio.handler.MultiNodeQueryHandler@7450171c, host=192.168.237.15, port=3307, statusSync=null, writeQueue=0, modifiedSQLExecuted=true]
（3）M1恢复正常，设想情况：MyCAT继续使用之前的M2作为写入节点
重启3306上的实例（MySQL_M1），再往MyCAT内插入两条数据：
mysql> insert into travelrecord(org_code,test_name) values("china86","tiger");
mysql> insert into travelrecord(org_code,test_name) values("china86","rabbit");
mysql> select *from travelrecord;
+----+----------+-----------+
| id | org_code | test_name |
+----+----------+-----------+
| 1 | china86 | tom |
| 3 | china86 | jack |
| 4 | china86 | marry |
| 6 | china86 | ellen |
| 8 | china86 | tiger |
| 10 | china86 | rabbit |
+----+----------+-----------+
id为偶数，故虽然M1已重启，但写入的数据库仍是M2，符合预期
再截取一段debug日志进行确认，写入的实例端口仍为3307（MySQL_M2）
08/17 22:17:20.549 DEBUG [$_NIOREACTOR-0-RW] (MultiNodeQueryHandler.java:171) -received ok response ,executeResponse:true from MySQLConnection [id=5, lastTime=1502979440533, user=root, schema=testdb1, old shema=testdb1, borrowed=true, fromSlaveDB=false, threadId=4, charset=utf8, txIsolation=3, autocommit=true, attachment=dn1{insert into travelrecord(org_code,test_name) values("china86","tiger")}, respHandler=org.opencloudb.mysql.nio.handler.MultiNodeQueryHandler@7cacca01, host=192.168.237.15, port=3307, statusSync=null, writeQueue=0, modifiedSQLExecuted=true]
...
08/17 22:17:31.698 DEBUG [$_NIOREACTOR-0-RW] (MultiNodeQueryHandler.java:171) -received ok response ,executeResponse:true from MySQLConnection [id=5, lastTime=1502979451682, user=root, schema=testdb1, old shema=testdb1, borrowed=true, fromSlaveDB=false, threadId=4, charset=utf8, txIsolation=3, autocommit=true, attachment=dn1{insert into travelrecord(org_code,test_name) values("china86","rabbit")}, respHandler=org.opencloudb.mysql.nio.handler.MultiNodeQueryHandler@73118f1d, host=192.168.237.15, port=3307, statusSync=null, writeQueue=0, modifiedSQLExecuted=true]
（4）M1从故障中恢复，M2可用，重启MyCAT，设想情况：MyCAT还是会继续沿用M2作为写入节点
重启MyCAT，再插入两条数据，写入的数据库还是M2，符合预期
mysql> insert into travelrecord(org_code,test_name) values("china86","orange");
mysql> insert into travelrecord(org_code,test_name) values("china86","apple");
mysql> select *from travelrecord;
+----+----------+-----------+
| id | org_code | test_name |
+----+----------+-----------+
| 1 | china86 | tom |
| 3 | china86 | jack |
| 4 | china86 | marry |
| 6 | china86 | ellen |
| 8 | china86 | tiger |
| 10 | china86 | rabbit |
| 12 | china86 | orange |
| 14 | china86 | apple |
+----+----------+-----------+
（5）修改writeHost的index信息，指定写入节点为M1，设想情况：MyCAT将写入节点切换至M1
打开conf目录下的dnindex.properties文件
#update
#Thu Aug 17 22:08:16 CST 2017
237_15=1
可以看到当前所用的writeHost的index为1（0表示使用的是配置文件schema.xml中第一个writeHost，1代表了第二个，以此类推）
我们尝试修改index为0，并重启MyCAT
再插入两组数据，发现id已经变为奇数，说明写入的数据库重新划给MySQL_M1了，符合预期
mysql> insert into travelrecord(org_code,test_name) values("china86","black");
mysql> insert into travelrecord(org_code,test_name) values("china86","white");
mysql> select *from travelrecord;
+----+----------+-----------+
| id | org_code | test_name |
+----+----------+-----------+
| 1 | china86 | tom |
| 3 | china86 | jack |
| 4 | china86 | marry |
| 6 | china86 | ellen |
| 8 | china86 | tiger |
| 10 | china86 | rabbit |
| 12 | china86 | orange |
| 14 | china86 | apple |
| 15 | china86 | black |
| 17 | china86 | white |
+----+----------+-----------+
注：可以通过管理端口连接MyCAT，执行show @@heartbeat，若 RS_CODE=-1 表示该节点心跳检测出错。
https://blog.csdn.net/leonpenn/article/details/77278360
采用的是在同一台机器上部署MyCAT，MySQL二主一从，其中3306和3307互为主从，3308为3306的从库。具体部署情况如下：
MyCAT：192.168.237.15，Port：8066/9066
MySQL Master：192.168.237.15，Port：3306
MySQL Master（Standby）：192.168.237.15，Port：3307
MySQL Slave：192.168.237.15，Port：3308
导入测试数据
真实数据库上创建testdb1、testdb2、testdb3库下的travelrecord表。
mysql> show create table travelrecord \G
*************************** 1. row ***************************
Table: travelrecord
Create Table: CREATE TABLE `travelrecord` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`org_code` varchar(20) NOT NULL,
`test_name` varchar(20) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
***读写分离（负载均衡）***
1、首先设定MyCAT实例中的逻辑库名（schema name）为DB1。
并定义这个逻辑库下存在一个逻辑表（table name）为travelrecord，该逻辑表所属的数据分片有：dataNode（dn1）
2、定义dataNode所在的dataHost、以及dataNode对应的真实database（testdb）。
定义dataHost拥有的writeHost和readHost（3306为write host，3307为备用write host，3308为read host）
据此，在MyCAT的schema.xml文件中配置如下：
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://org.opencloudb/" >
<!--定义MyCAT实例的逻辑库和逻辑表-->
<schema name="DB1" checkSQLschema="false" sqlMaxLimit="100">
<table name="travelrecord" primaryKey="id" dataNode="dn1,dn2,dn3" />
</schema>
<!--定义MyCAT逻辑表中提到的所有dataNode所在的真实Host，以及dataNode所属的真实数据库-->
<dataNode name="dn1" dataHost="237_15" database="testdb1" />
<dataNode name="dn2" dataHost="237_15" database="testdb2" />
<dataNode name="dn3" dataHost="237_15" database="testdb3" />
<mycat:schema xmlns:mycat="http://org.opencloudb/" >
<table name="travelrecord" primaryKey="id" dataNode="dn1,dn2,dn3" />
</schema>
<!--定义MyCAT逻辑表中提到的所有dataNode所在的真实Host，以及dataNode所属的真实数据库-->
<dataNode name="dn1" dataHost="237_15" database="testdb1" />
<dataNode name="dn2" dataHost="237_15" database="testdb2" />
<dataNode name="dn3" dataHost="237_15" database="testdb3" />
<!--定义dataNode提到的dataHost的连接限制数、负载均衡取向，以及真实的读写地址(writeHost和readHost)-->
<dataHost name="237_15" maxCon="1000" minCon="3" balance="1"
writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<writeHost host="MySQL_M1" url="192.168.237.15:3306" user="root" password="123456">
<readHost host="MySQL_S1" url="192.168.237.15:3308" user="root" password="123456" />
</writeHost>
<writeHost host="MySQL_M2" url="192.168.237.15:3307" user="root" password="123456">
<!--M2无对应的Slave库，此处留白 -->
</writeHost>
</dataHost>
</mycat:schema>
参数解读
schema.xml中的balance的取值决定了负载均衡对非事务内的读操作的处理：
balance="0"：读请求仅发送到writeHost上（不开启读写分离）。
balance="1"：读请求随机分发到当前writeHost对应的readHost和standby的writeHost上。
balance="2"：读请求随机分发到当前dataHost内所有的writeHost和readHost上。
balance="3"：读请求随机分发到当前writeHost对应的readHost上。
对于事务内的SQL默认走写节点；
以 /*balance*/ 开头，可以指定SQL使用特定负载均衡方案。例如在大环境开启读写分离的情况下，特定强一致性的SQL查询需求；
slaveThreshold：近似的主从延迟时间（秒）Seconds_Behind_Master < slaveThreshold ，读请求才会分发到该Slave，确保读到的数据相对较新。
schema.xml中的writeType的取值决定了负载均衡对写操作的处理：
writeType="0"：所有的写操作都发送到配置文件中的第一个write host。（第一个write host故障切换到第二个后，即使之后修复了仍然维持第二个为写库）。推荐取0值，不建议修改.
分类演示
事先我们要打开MyCAT的debug模式。# vim log4j.xml，将info修改为debug
<root>
<level value="<strong>debug</strong>" />
<appender-ref ref="FILE" />
<!--<appender-ref ref="FILE" />-->
</root>
下面通过变换配置文件中的balance值，执行写操作：select * from travelrecord来演示各种读写分离的情况。
（1）balance=“0”，查看debug日志，走MySQL_M1
08/17 15:37:49.712 DEBUG [$_NIOREACTOR-0-RW] (MultiNodeQueryHandler.java:82) -execute mutinode query select * from travelrecord
08/17 15:37:49.712 DEBUG [$_NIOREACTOR-0-RW] (MultiNodeQueryHandler.java:97) -has data merge logic
08/17 15:37:49.713 DEBUG [$_NIOREACTOR-0-RW] (PhysicalDBPool.java:452) -<strong>select read source MySQL_M1 for dataHost:237_15</strong>
08/17 15:37:49.714 DEBUG [$_NIOREACTOR-0-RW] (PhysicalDBPool.java:452) -<strong>select read source MySQL_M1 for dataHost:237_15</strong>
08/17 15:37:49.715 DEBUG [$_NIOREACTOR-0-RW] (PhysicalDBPool.java:452) -<strong>select read source MySQL_M1 for dataHost:237_15</strong>
08/17 15:37:49.718 DEBUG [$_NIOREACTOR-0-RW] (DataMergeService.java:102) -field metadata inf:[TEST_NAME=ColMeta [colIndex=2, colType=253], ORG_CODE=ColMeta [colIndex=1, colType=253], ID=ColMeta [colIndex=0, colType=3]]
08/17 15:37:49.719 DEBUG [$_NIOREACTOR-0-RW] (MultiNodeQueryHandler.java:250) -on row end reseponse MySQLConnection [id=1, lastTime=1502955469708, user=root, schema=testdb2, old shema=testdb2, borrowed=true, fromSlaveDB=false, threadId=31, charset=utf8, txIsolation=3, autocommit=true, attachment=dn2{SELECT *
FROM travelrecord
LIMIT 100}, respHandler=org.opencloudb.mysql.nio.handler.MultiNodeQueryHandler@5e5f215f, host=192.168.237.15, <strong>port=3306</strong>, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]
（2）balance=“1”，查看debug日志，走MySQL_M2或者MySQL_S1
08/17 15:56:10.023 DEBUG [$_NIOREACTOR-0-RW] (MultiNodeQueryHandler.java:82) -execute mutinode query select * from travelrecord
08/17 15:56:10.023 DEBUG [$_NIOREACTOR-0-RW] (MultiNodeQueryHandler.java:97) -has data merge logic
08/17 15:56:10.023 DEBUG [$_NIOREACTOR-0-RW] (PhysicalDBPool.java:452) -<strong>select read source MySQL_M2 for dataHost:237_15</strong>
08/17 15:56:10.027 DEBUG [$_NIOREACTOR-0-RW] (PhysicalDBPool.java:452) -<strong>select read source MySQL_M2 for dataHost:237_15</strong>
08/17 15:56:10.028 DEBUG [$_NIOREACTOR-0-RW] (PhysicalDBPool.java:452) -<strong>select read source MySQL_M2 for dataHost:237_15</strong>
08/17 15:56:10.030 DEBUG [$_NIOREACTOR-0-RW] (DataMergeService.java:102) -field metadata inf:[TEST_NAME=ColMeta [colIndex=2, colType=253], ORG_CODE=ColMeta [colIndex=1, colType=253], ID=ColMeta [colIndex=0, colType=3]]
08/17 15:56:10.031 DEBUG [$_NIOREACTOR-0-RW] (MultiNodeQueryHandler.java:250) -on row end reseponse MySQLConnection [id=8, lastTime=1502956570027, user=root, schema=testdb3, old shema=testdb3, borrowed=true, fromSlaveDB=false, threadId=17, charset=utf8, txIsolation=3, autocommit=true, attachment=dn3{SELECT *
FROM travelrecord
LIMIT 100}, respHandler=org.opencloudb.mysql.nio.handler.MultiNodeQueryHandler@5209c6a4, host=192.168.237.15, <strong>port=3307</strong>, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]
08/17 16:03:50.225 DEBUG [$_NIOREACTOR-0-RW] (MultiNodeQueryHandler.java:82) -execute mutinode query select * from travelrecord
08/17 16:03:50.225 DEBUG [$_NIOREACTOR-0-RW] (MultiNodeQueryHandler.java:97) -has data merge logic
08/17 16:03:50.225 DEBUG [$_NIOREACTOR-0-RW] (PhysicalDBPool.java:452) -<strong>select read source MySQL_M2 for dataHost:237_15</strong>
08/17 16:03:50.226 DEBUG [$_NIOREACTOR-0-RW] (PhysicalDBPool.java:452) -<strong>select read source MySQL_S1 for dataHost:237_15</strong>
08/17 16:03:50.226 DEBUG [$_NIOREACTOR-0-RW] (PhysicalDBPool.java:452) -<strong>select read source MySQL_M2 for dataHost:237_15</strong>
08/17 16:03:50.227 DEBUG [$_NIOREACTOR-0-RW] (DataMergeService.java:102) -field metadata inf:[TEST_NAME=ColMeta [colIndex=2, colType=253], ORG_CODE=ColMeta [colIndex=1, colType=253], ID=ColMeta [colIndex=0, colType=3]]
08/17 16:03:50.227 DEBUG [$_NIOREACTOR-0-RW] (MultiNodeQueryHandler.java:250) -on row end reseponse MySQLConnection [id=6, lastTime=1502957030209, user=root, schema=testdb2, old shema=testdb2, borrowed=true, fromSlaveDB=true, threadId=18, charset=utf8, txIsolation=3, autocommit=true, attachment=dn2{SELECT *
FROM travelrecord
LIMIT 100}, respHandler=org.opencloudb.mysql.nio.handler.MultiNodeQueryHandler@5cd051b6, host=192.168.237.15, <strong>port=3308</strong>, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]
08/17 16:03:50.228 DEBUG [$_NIOREACTOR-0-RW] (NonBlockingSession.java:229) -release connection MySQLConnection [id=6, lastTime=1502957030209, user=root, schema=testdb2, old shema=testdb2, borrowed=true, fromSlaveDB=true, threadId=18, charset=utf8, txIsolation=3, autocommit=true, attachment=dn2{SELECT *
FROM travelrecord
LIMIT 100}, respHandler=org.opencloudb.mysql.nio.handler.MultiNodeQueryHandler@5cd051b6, host=192.168.237.15, <strong>port=3308</strong>, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]
08/17 16:03:50.228 DEBUG [$_NIOREACTOR-0-RW] (PhysicalDatasource.java:403) -release channel MySQLConnection [id=6, lastTime=1502957030209, user=root, schema=testdb2, old shema=testdb2, borrowed=true, fromSlaveDB=true, threadId=18, charset=utf8, txIsolation=3, autocommit=true, attachment=null, respHandler=null, host=192.168.237.15, port=3308, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]
08/17 16:03:50.231 DEBUG [$_NIOREACTOR-0-RW] (MultiNodeQueryHandler.java:250) -on row end reseponse MySQLConnection [id=8, lastTime=1502957030209, user=root, schema=testdb3, old shema=testdb3, borrowed=true, fromSlaveDB=false, threadId=17, charset=utf8, txIsolation=3, autocommit=true, attachment=dn3{SELECT *
FROM travelrecord
LIMIT 100}, respHandler=org.opencloudb.mysql.nio.handler.MultiNodeQueryHandler@5cd051b6, host=192.168.237.15, <strong>port=3307</strong>, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]
08/17 16:03:50.232 DEBUG [$_NIOREACTOR-0-RW] (NonBlockingSession.java:229) -release connection MySQLConnection [id=8, lastTime=1502957030209, user=root, schema=testdb3, old shema=testdb3, borrowed=true, fromSlaveDB=false, threadId=17, charset=utf8, txIsolation=3, autocommit=true, attachment=dn3{SELECT *
FROM travelrecord
LIMIT 100}, respHandler=org.opencloudb.mysql.nio.handler.MultiNodeQueryHandler@5cd051b6, host=192.168.237.15, <strong>port=3307</strong>, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]
08/17 16:03:50.232 DEBUG [$_NIOREACTOR-0-RW] (PhysicalDatasource.java:403) -release channel MySQLConnection [id=8, lastTime=1502957030209, user=root, schema=testdb3, old shema=testdb3, borrowed=true, fromSlaveDB=false, threadId=17, charset=utf8, txIsolation=3, autocommit=true, attachment=null, respHandler=null, host=192.168.237.15, port=3307, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]
08/17 16:03:50.239 DEBUG [$_NIOREACTOR-0-RW] (MultiNodeQueryHandler.java:250) -on row end reseponse MySQLConnection [id=5, lastTime=1502957030209, user=root, schema=testdb1, old shema=testdb1, borrowed=true, fromSlaveDB=false, threadId=15, charset=utf8, txIsolation=3, autocommit=true, attachment=dn1{SELECT *
FROM travelrecord
LIMIT 100}, respHandler=org.opencloudb.mysql.nio.handler.MultiNodeQueryHandler@5cd051b6, host=192.168.237.15, <strong>port=3307</strong>, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]
08/17 16:03:50.239 DEBUG [$_NIOREACTOR-0-RW] (NonBlockingSession.java:229) -release connection MySQLConnection [id=5, lastTime=1502957030209, user=root, schema=testdb1, old shema=testdb1, borrowed=true, fromSlaveDB=false, threadId=15, charset=utf8, txIsolation=3, autocommit=true, attachment=dn1{SELECT *
FROM travelrecord
LIMIT 100}, respHandler=org.opencloudb.mysql.nio.handler.MultiNodeQueryHandler@5cd051b6, host=192.168.237.15, <strong>port=3307</strong>, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]
（3）balance=“2”，查看debug日志，走MySQL_M1或者MySQL_S1
08/17 16:10:48.867 DEBUG [$_NIOREACTOR-0-RW] (MultiNodeQueryHandler.java:82) -execute mutinode query select * from travelrecord
08/17 16:10:48.867 DEBUG [$_NIOREACTOR-0-RW] (MultiNodeQueryHandler.java:97) -has data merge logic
08/17 16:10:48.868 DEBUG [$_NIOREACTOR-0-RW] (PhysicalDBPool.java:452) -<strong>select read source MySQL_M1 for dataHost:237_15</strong>
08/17 16:10:48.869 DEBUG [$_NIOREACTOR-0-RW] (MySQLConnection.java:459) -con need syn ,total syn cmd 1 commands SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;schema change:false con:MySQLConnection [id=1, lastTime=1502957448869, user=root, schema=testdb1, old shema=testdb1, borrowed=true, fromSlaveDB=false, threadId=38, charset=utf8, txIsolation=0, autocommit=true, attachment=dn1{SELECT *
FROM travelrecord
LIMIT 100}, respHandler=org.opencloudb.mysql.nio.handler.MultiNodeQueryHandler@17005763, host=192.168.237.15, <strong>port=3306</strong>, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]
08/17 16:10:48.876 DEBUG [$_NIOREACTOR-0-RW] (PhysicalDBPool.java:452) -<strong>select read source MySQL_S1 for dataHost:237_15</strong>
08/17 16:10:48.876 DEBUG [$_NIOREACTOR-0-RW] (MySQLConnection.java:459) -con need syn ,total syn cmd 2 commands SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;schema change:true con:MySQLConnection [id=4, lastTime=1502957448876, user=root, schema=testdb2, old shema=testdb3, borrowed=true, fromSlaveDB=true, threadId=20, charset=utf8, txIsolation=0, autocommit=true, attachment=dn2{SELECT *
FROM travelrecord
LIMIT 100}, respHandler=org.opencloudb.mysql.nio.handler.MultiNodeQueryHandler@17005763, host=192.168.237.15, <strong>port=3308</strong>, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]
此处之后日志省略...
（4）balance=“3”，查看debug日志，走MySQL_M1的从库：MySQL_S1
08/17 16:15:54.267 DEBUG [$_NIOREACTOR-0-RW] (MultiNodeQueryHandler.java:82) -execute mutinode query select * from travelrecord
08/17 16:15:54.267 DEBUG [$_NIOREACTOR-0-RW] (MultiNodeQueryHandler.java:97) -has data merge logic
08/17 16:15:54.268 DEBUG [$_NIOREACTOR-0-RW] (PhysicalDBPool.java:452) -<strong>select read source MySQL_S1 for dataHost:237_15</strong>
08/17 16:15:54.270 DEBUG [$_NIOREACTOR-0-RW] (PhysicalDBPool.java:452) -<strong>select read source MySQL_S1 for dataHost:237_15</strong>
08/17 16:15:54.271 DEBUG [$_NIOREACTOR-0-RW] (PhysicalDBPool.java:452) -<strong>select read source MySQL_S1 for dataHost:237_15</strong>
08/17 16:15:54.274 DEBUG [$_NIOREACTOR-0-RW] (DataMergeService.java:102) -field metadata inf:[TEST_NAME=ColMeta [colIndex=2, colType=253], ORG_CODE=ColMeta [colIndex=1, colType=253], ID=ColMeta [colIndex=0, colType=3]]
08/17 16:15:54.276 DEBUG [$_NIOREACTOR-0-RW] (MultiNodeQueryHandler.java:250) -on row end reseponse MySQLConnection [id=4, lastTime=1502957754253, user=root, schema=testdb1, old shema=testdb1, borrowed=true, fromSlaveDB=true, threadId=21, charset=utf8, txIsolation=3, autocommit=true, attachment=dn1{SELECT *
FROM travelrecord
LIMIT 100}, respHandler=org.opencloudb.mysql.nio.handler.MultiNodeQueryHandler@5615b9cb, host=192.168.237.15, <strong>port=3308</strong>, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]
此处之后日志省略...
MyCAT插入测试数据：mysql> insert into travelrecord values(1,"china86","mike");
查看debug日志，写操作走MySQL_M1
08/17 16:38:49.995 DEBUG [$_NIOREACTOR-0-RW] (ServerQueryHandler.java:56) -ServerConnection [id=1, schema=DB1, host=127.0.0.1, user=test,txIsolation=3, autocommit=true, schema=DB1]insert into travelrecord values(1,"china86","mike")
08/17 16:38:50.002 DEBUG [$_NIOREACTOR-0-RW] (NonBlockingSession.java:113) -ServerConnection [id=1, schema=DB1, host=127.0.0.1, user=test,txIsolation=3, autocommit=true, schema=DB1]insert into travelrecord values(1,"china86","mike"), route={
1 -> dn1{insert into travelrecord values(1,"china86","mike")}
2 -> dn2{insert into travelrecord values(1,"china86","mike")}
3 -> dn3{insert into travelrecord values(1,"china86","mike")}
} rrs
08/17 16:38:50.003 DEBUG [$_NIOREACTOR-0-RW] (MultiNodeQueryHandler.java:82) -execute mutinode query insert into travelrecord values(1,"china86","mike")
08/17 16:38:50.003 DEBUG [$_NIOREACTOR-0-RW] (MySQLConnection.java:459) -con need syn ,total syn cmd 1 commands SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;schema change:false con:MySQLConnection [id=2, lastTime=1502959130003, user=root, schema=testdb1, old shema=testdb1, borrowed=true, fromSlaveDB=false, threadId=41, charset=utf8, txIsolation=0, autocommit=true, attachment=dn1{insert into travelrecord values(1,"china86","mike")}, respHandler=org.opencloudb.mysql.nio.handler.MultiNodeQueryHandler@6b4de96c, host=192.168.237.15, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=true]
08/17 16:38:50.006 DEBUG [$_NIOREACTOR-0-RW] (MySQLConnection.java:459) -con need syn ,total syn cmd 1 commands SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;schema change:false con:MySQLConnection [id=3, lastTime=1502959130006, user=root, schema=testdb2, old shema=testdb2, borrowed=true, fromSlaveDB=false, threadId=40, charset=utf8, txIsolation=0, autocommit=true, attachment=dn2{insert into travelrecord values(1,"china86","mike")}, respHandler=org.opencloudb.mysql.nio.handler.MultiNodeQueryHandler@6b4de96c, host=192.168.237.15, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=true]
***主从切换（双主failover）***
如果我们细心观察schem.xml文件的话，会发现之前有一个参数：switchType尚未提及。
<dataHost name="237_15" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="native"switchType="1" slaveThreshold="100">
参数解读
switchType="-1"：不自动切换
switchType="1"：自动切换
switchType="2"：基于MySQL主从复制的状态来决定是否切换。需修改heartbeat语句：show slave status
switchType="3"：基于Galera（集群多节点复制）的切换机制。需修改heartbeat语句：show status like 'wsrep%'
switchType="2"的切换演示
先修改heartbeat语句
<dataHost name="237_15" maxCon="1000" minCon="3" balance="1"
writeType="0" dbType="mysql" dbDriver="native" switchType="2" slaveThreshold="100">
<heartbeat>show slave status</heartbeat>
为了区分是主库还是从库插入的数据，M1、M2设置不同自增步长，通过id的奇偶性来判断，避免通过查debug日志的方式来区分。
MySQL_M1：auto_increment_increment=2，auto_increment_offset=1
MySQL_M2：auto_increment_increment=2，auto_increment_offset=2
（1）M1、M2两个节点均正常的情况下，设想情况：MyCAT选择M1插入数据
插入两组测试数据：
mysql> insert into travelrecord(org_code,test_name) values("china86","tom");
mysql> insert into travelrecord(org_code,test_name) values("china86","jack");
mysql> select *from travelrecord;
+----+----------+-----------+
| id | org_code | test_name |
+----+----------+-----------+
| 1 | china86 | tom |
| 3 | china86 | jack |
+----+----------+-----------+
id为奇数，故写入的数据库是M1，符合预期
（2）M1宕机，设想情况：MyCAT切换至M2作为写入节点
此时，我们模拟故障，手动停止3306上的MySQL实例
[root@237_15 conf]# /etc/init.d/mysql3306 stop
Shutting down MySQL............ SUCCESS!
再往MyCAT内插入两组数据：
mysql> insert into travelrecord(org_code,test_name) values("china86","marry");
mysql> insert into travelrecord(org_code,test_name) values("china86","ellen");
mysql> select *from travelrecord;
+----+----------+-----------+
| id | org_code | test_name |
+----+----------+-----------+
| 1 | china86 | tom |
| 3 | china86 | jack |
| 4 | china86 | marry |
| 6 | china86 | ellen |
+----+----------+-----------+
id为偶数，故写入的是M2，符合预期
再截取一段debug日志，确认写入的实例端口为3307（MySQL_M2）
08/17 22:10:40.330 DEBUG [$_NIOREACTOR-0-RW] (MultiNodeQueryHandler.java:171) -received ok response ,executeResponse:true from MySQLConnection [id=7, lastTime=1502979040315, user=root, schema=testdb2, old shema=testdb2, borrowed=true, fromSlaveDB=false, threadId=5, charset=utf8, txIsolation=3, autocommit=true, attachment=dn2{insert into travelrecord(org_code,test_name) values("china86","ellen")}, respHandler=org.opencloudb.mysql.nio.handler.MultiNodeQueryHandler@7450171c, host=192.168.237.15, port=3307, statusSync=null, writeQueue=0, modifiedSQLExecuted=true]
（3）M1恢复正常，设想情况：MyCAT继续使用之前的M2作为写入节点
重启3306上的实例（MySQL_M1），再往MyCAT内插入两条数据：
mysql> insert into travelrecord(org_code,test_name) values("china86","tiger");
mysql> insert into travelrecord(org_code,test_name) values("china86","rabbit");
mysql> select *from travelrecord;
+----+----------+-----------+
| id | org_code | test_name |
+----+----------+-----------+
| 1 | china86 | tom |
| 3 | china86 | jack |
| 4 | china86 | marry |
| 6 | china86 | ellen |
| 8 | china86 | tiger |
| 10 | china86 | rabbit |
+----+----------+-----------+
id为偶数，故虽然M1已重启，但写入的数据库仍是M2，符合预期
再截取一段debug日志进行确认，写入的实例端口仍为3307（MySQL_M2）
08/17 22:17:20.549 DEBUG [$_NIOREACTOR-0-RW] (MultiNodeQueryHandler.java:171) -received ok response ,executeResponse:true from MySQLConnection [id=5, lastTime=1502979440533, user=root, schema=testdb1, old shema=testdb1, borrowed=true, fromSlaveDB=false, threadId=4, charset=utf8, txIsolation=3, autocommit=true, attachment=dn1{insert into travelrecord(org_code,test_name) values("china86","tiger")}, respHandler=org.opencloudb.mysql.nio.handler.MultiNodeQueryHandler@7cacca01, host=192.168.237.15, port=3307, statusSync=null, writeQueue=0, modifiedSQLExecuted=true]
...
08/17 22:17:31.698 DEBUG [$_NIOREACTOR-0-RW] (MultiNodeQueryHandler.java:171) -received ok response ,executeResponse:true from MySQLConnection [id=5, lastTime=1502979451682, user=root, schema=testdb1, old shema=testdb1, borrowed=true, fromSlaveDB=false, threadId=4, charset=utf8, txIsolation=3, autocommit=true, attachment=dn1{insert into travelrecord(org_code,test_name) values("china86","rabbit")}, respHandler=org.opencloudb.mysql.nio.handler.MultiNodeQueryHandler@73118f1d, host=192.168.237.15, port=3307, statusSync=null, writeQueue=0, modifiedSQLExecuted=true]
（4）M1从故障中恢复，M2可用，重启MyCAT，设想情况：MyCAT还是会继续沿用M2作为写入节点
重启MyCAT，再插入两条数据，写入的数据库还是M2，符合预期
mysql> insert into travelrecord(org_code,test_name) values("china86","orange");
mysql> insert into travelrecord(org_code,test_name) values("china86","apple");
mysql> select *from travelrecord;
+----+----------+-----------+
| id | org_code | test_name |
+----+----------+-----------+
| 1 | china86 | tom |
| 3 | china86 | jack |
| 4 | china86 | marry |
| 6 | china86 | ellen |
| 8 | china86 | tiger |
| 10 | china86 | rabbit |
| 12 | china86 | orange |
| 14 | china86 | apple |
+----+----------+-----------+
（5）修改writeHost的index信息，指定写入节点为M1，设想情况：MyCAT将写入节点切换至M1
打开conf目录下的dnindex.properties文件
#update
#Thu Aug 17 22:08:16 CST 2017
237_15=1
可以看到当前所用的writeHost的index为1（0表示使用的是配置文件schema.xml中第一个writeHost，1代表了第二个，以此类推）
我们尝试修改index为0，并重启MyCAT
再插入两组数据，发现id已经变为奇数，说明写入的数据库重新划给MySQL_M1了，符合预期
mysql> insert into travelrecord(org_code,test_name) values("china86","black");
mysql> insert into travelrecord(org_code,test_name) values("china86","white");
mysql> select *from travelrecord;
+----+----------+-----------+
| id | org_code | test_name |
+----+----------+-----------+
| 1 | china86 | tom |
| 3 | china86 | jack |
| 4 | china86 | marry |
| 6 | china86 | ellen |
| 8 | china86 | tiger |
| 10 | china86 | rabbit |
| 12 | china86 | orange |
| 14 | china86 | apple |
| 15 | china86 | black |
| 17 | china86 | white |
+----+----------+-----------+
注：可以通过管理端口连接MyCAT，执行show @@heartbeat，若 RS_CODE=-1 表示该节点心跳检测出错。
https://blog.csdn.net/leonpenn/article/details/77278360
分享到：
发表评论
-
zk管理mycat
2018-07-26 11:09 7121、把conf下的配置文件 复制到 zkconf下面 2、修 ... -
Unable to register shutdown hook because JVM is shutting down
2018-07-25 17:48 3178<?xml version="1.0" ... -
mycat无法创建存储过程 ，只能调用存储过程
2018-07-11 14:10 1652mycat无法创建存储过程 ，只能调用存储过程 mycat无 ... -
mycat dn过多（2）
2018-06-27 13:18 533<dataNode name="multi ... -
mycat dn过多
2018-06-27 12:57 580<table name="customer&q ... -
Mycat事务源码分析
2018-06-21 14:33 1060Mycat的事务相关的代码逻辑，目前的实现方式如下： 用户会话 ... -
Mycat全局表
2018-06-11 11:14 1965如果你的业务中有些数据类似于数据字典，比如配置文件的配置，常用 ... -
分片规则
2018-06-11 11:13 16199.1 分片规则概述 在数据切分处理中，特别是水平切分中，中间 ... -
关系型数据库和NoSQL数据库
2018-06-07 12:59 1338针对上面两类系统有多种技术实现方案，存储部分的数据库主要分为两 ... -
创建MyCat的Docker镜像
2018-05-31 15:43 1188MyCat 要使用JDK1.7以上环境,因此基于openjdk ... -
mycat 原理分享
2018-02-10 09:05 989下载地址 mycat pdf下载 -
Mycat 多租户方案 （1）
2017-09-28 15:04 1385Mycat多租户方案 1、需求 1、1 需求图 这里 ...
相关推荐
基于Mycat实现Mysql读写分离以及分库分表详解 本文档详细介绍了基于Mycat实现Mysql读写分离以及分库分表的技术，涵盖了Mycat安装、配置、读写分离、分库分表等多方面的知识点。 一、读写分离 Mycat读写分离是指将...
使用MyCat实现mysql读写分离配置说明
Mycat是一款开源的数据库中间件，它可以实现对多个MySQL实例的连接管理和负载均衡，支持SQL路由等功能，进而实现读写分离。 ##### 启用Mycat读写分离 完成MySQL主从配置后，下一步是启用Mycat的读写分离机制。这...
实战案例：利用Mycat实现MySQL的读写分离.md
基于mycat的mysql高可用读写分离，适合mysql进阶。比较实用的技术。
一般来说，读写分离有两种实现方式。第一种是依靠中间件MyCat，也就是说应用程序连接到中间件，中间件帮我们做SQL分离，去选择指定的数据源；第二种是应用程序自己去做分离。这里我用程序自己来做，主要是利用Spring...
本篇文章将详细讲解如何在离线环境下，利用Mycat和MySQL搭建一个读写分离的集群。Mycat是一款开源的分布式数据库中间件，它能够实现数据库的水平扩展，而MySQL则是广泛使用的开源关系型数据库。 首先，我们来了解...
三、Mycat实现读写分离 1. 安装Mycat：首先，你需要在服务器上下载并安装Mycat，配置相关环境变量，确保Mycat能够正常启动。 2. 配置Mycat Server.xml：这是Mycat的核心配置文件，包括服务器端口、数据源、路由策略...
本文将详细介绍如何利用Mycat这一开源数据库中间件实现MySQL数据库的主从读写分离，并通过具体的配置步骤及示例进行说明。 #### 二、Mycat简介 Mycat是一款开源的数据库连接池产品，它位于Java应用程序和数据库...
本文将深入探讨MyCat如何实现读写分离，并结合提供的“mycat实现读写分离.pdf”文件，详细解析这一过程。 一、MyCat简介 MyCat（原名Mycat-S）是一个基于MySQL协议的数据库中间件，它将多个数据库节点透明地连接...
此外，通过MyCat还能实现读写分离、数据分片等高级功能，从而进一步提升系统的可扩展性和可用性。 #### 二、MySQL高可用性与读写分离 1. **高可用性**：在IT领域中，高可用性通常指系统能够持续提供服务的能力。...
通过以上分析，我们可以看到，利用Mycat实现MySQL的主从复制和读写分离是一项涉及多方面技术细节的任务。正确理解和处理如【心跳问题heartbeat bug #393】和【bug407：修复主从状态监控和读写分离】这类问题，对于...
linux Mysql mycat主从复制读写分离部署完成 技术：mycat mysql集群 linux mycat读写分离 说明包含： mysql数据库服务安装包 mysql+mycat主从复制读写分离部署帮助文档 mysql+mycat主从复制读写分离使用帮助...
在本配置文档中，我们将关注如何利用Mycat中间键实现Oracle 11g的读写分离，并在Linux环境下进行相关组件的安装。 Mycat是一个开源的数据库中间件，它具有分库分表、读写分离、数据切片等功能，能够帮助我们构建大...
在本文中，我们将详细介绍如何使用MYSQL和MYCAT实现读写分离架构。 MYSQL主从复制 MYSQL主从复制是指将一个MYSQL数据库服务器作为主服务器，将其它服务器作为从服务器，主服务器上的所有操作都会被复制到从服务器...
这是Mycat实现mysql主从读写分离时用到的的配置文件。 内容包括：schema.xml和server.xml。 请根据实际情况替换里面的数据库地址、用户、密码。 相关操作教程：...
本文将详细介绍如何利用mycat中间件实现MySQL5.7的读写分离。 一、读写分离与mycat简介 1.1 读写分离 读写分离是数据库架构设计中的一个重要策略，它将读取操作和写入操作分开，通常主数据库负责写操作，而从...