利用MyCAT实现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/" >
        <!--定义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="debug" />
    <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) -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尚未提及。

 <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 表示该节点心跳检测出错。




评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值