利用MyCAT实现MySQL的读写分离和主从切换

转载

wx5caecf2ed0645 2021-08-09 16:35:25 博主文章分类：Mysql

文章标签 mysql java sql xml 数据库 文章分类 MySQL 数据库

实验环境介绍

采用的是在同一台机器上部署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/" >

<schema name="DB1" checkSQLschema="false" sqlMaxLimit="100">
<table name="travelrecord" primaryKey="id" dataNode="dn1,dn2,dn3" />
</schema>

<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>

<dataNode name="dn1" dataHost="237_15" database="testdb1" />
<dataNode name="dn2" dataHost="237_15" database="testdb2" />
<dataNode name="dn3" dataHost="237_15" database="testdb3" />

<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">

</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="debug" />
<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) -select read source MySQL_M1 for dataHost:237_15
08/17 15:37:49.714 DEBUG [$_NIOREACTOR-0-RW] (PhysicalDBPool.java:452) -select read source MySQL_M1 for dataHost:237_15
08/17 15:37:49.715 DEBUG [$_NIOREACTOR-0-RW] (PhysicalDBPool.java:452) -select read source MySQL_M1 for dataHost:237_15
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, port=3306, 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) -select read source MySQL_M2 for dataHost:237_15
08/17 15:56:10.027 DEBUG [$_NIOREACTOR-0-RW] (PhysicalDBPool.java:452) -select read source MySQL_M2 for dataHost:237_15
08/17 15:56:10.028 DEBUG [$_NIOREACTOR-0-RW] (PhysicalDBPool.java:452) -select read source MySQL_M2 for dataHost:237_15
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, port=3307, 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) -select read source MySQL_M2 for dataHost:237_15
08/17 16:03:50.226 DEBUG [$_NIOREACTOR-0-RW] (PhysicalDBPool.java:452) -select read source MySQL_S1 for dataHost:237_15
08/17 16:03:50.226 DEBUG [$_NIOREACTOR-0-RW] (PhysicalDBPool.java:452) -select read source MySQL_M2 for dataHost:237_15
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, port=3308, 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, port=3308, 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, port=3307, 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, port=3307, 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, port=3307, 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, port=3307, 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) -select read source MySQL_M1 for dataHost:237_15
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, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]
08/17 16:10:48.876 DEBUG [$_NIOREACTOR-0-RW] (PhysicalDBPool.java:452) -select read source MySQL_S1 for dataHost:237_15
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, port=3308, 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) -select read source MySQL_S1 for dataHost:237_15
08/17 16:15:54.270 DEBUG [$_NIOREACTOR-0-RW] (PhysicalDBPool.java:452) -select read source MySQL_S1 for dataHost:237_15
08/17 16:15:54.271 DEBUG [$_NIOREACTOR-0-RW] (PhysicalDBPool.java:452) -select read source MySQL_S1 for dataHost:237_15
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, port=3308, 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尚未提及。

参数解读

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 |
+----+----------+-----------+