男人50
- 浏览: 227959 次
- 性别:
- 来自: 珠海
-
文章分类
- 全部博客 (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 6691、把conf下的配置文件 复制到 zkconf下面 2、修 ... -
Unable to register shutdown hook because JVM is shutting down
2018-07-25 17:48 3094<?xml version="1.0" ... -
mycat无法创建存储过程 ，只能调用存储过程
2018-07-11 14:10 1622mycat无法创建存储过程 ，只能调用存储过程 mycat无 ... -
mycat dn过多（2）
2018-06-27 13:18 505<dataNode name="multi ... -
mycat dn过多
2018-06-27 12:57 545<table name="customer&q ... -
Mycat事务源码分析
2018-06-21 14:33 1016Mycat的事务相关的代码逻辑，目前的实现方式如下： 用户会话 ... -
Mycat全局表
2018-06-11 11:14 1927如果你的业务中有些数据类似于数据字典，比如配置文件的配置，常用 ... -
分片规则
2018-06-11 11:13 15789.1 分片规则概述 在数据切分处理中，特别是水平切分中，中间 ... -
关系型数据库和NoSQL数据库
2018-06-07 12:59 1284针对上面两类系统有多种技术实现方案，存储部分的数据库主要分为两 ... -
创建MyCat的Docker镜像
2018-05-31 15:43 1160MyCat 要使用JDK1.7以上环境,因此基于openjdk ... -
mycat 原理分享
2018-02-10 09:05 945下载地址 mycat pdf下载 -
Mycat 多租户方案 （1）
2017-09-28 15:04 1349Mycat多租户方案 1、需求 1、1 需求图 这里 ...
相关推荐
使用MyCat实现mysql读写分离配置说明
文档中包括mysql的读写分离，与mycat的读写分离，因为mycat读写是基于mysql的，所以首先需要部署mysql读写
基于Mycat的Mysql主从复制读写分离配置详解与示例,DBA必看的
实战案例：利用Mycat实现MySQL的读写分离.md
基于mycat的mysql高可用读写分离，适合mysql进阶。比较实用的技术。
mycat+mysql读写分离集群离线安装手册，适用环境：linux，mycat配置了一主一从的配置，读者可根据自身需求增加slave节点。
一般来说，读写分离有两种实现方式。第一种是依靠中间件MyCat，也就是说应用程序连接到中间件，中间件帮我们做SQL分离，去选择指定的数据源；第二种是应用程序自己去做分离。这里我用程序自己来做，主要是利用Spring...
内容详细，亲测可用，包含图文介绍
基于Mycat的MySQL主从读写分离配置详解与示例
第三版_基于Mycat的MySQL主从读写分离配置详解与示例
linux Mysql mycat主从复制读写分离部署完成 技术：mycat mysql集群 linux mycat读写分离 说明包含： mysql数据库服务安装包 mysql+mycat主从复制读写分离部署帮助文档 mysql+mycat主从复制读写分离使用帮助...
【27】基于MyCat的MySQL高可用读写分离集群实战课程下载【No215】基于MyCat的MySQL高可用读写分离集群实战课程下载 .txt
Mycat+MySQL Galera读写分离验证安装手册
mycat实现读写分离
这是Mycat实现mysql主从读写分离时用到的的配置文件。 内容包括：schema.xml和server.xml。 请根据实际情况替换里面的数据库地址、用户、密码。 相关操作教程：...
.基于Mycat的MySQL主从复制读写分离docker实现.
mycat在mysql主从复制基础上实现读写分离,详细配置，包括server.xml，scheme配置等
Mycat+MySQL主从复制读写分离验证安装手册
oracle11g读写分离配置文档，Mycat软件及相关linux安装adg方式的rpm插件包