Mycat之MySql 读写分离与日志分析——主从多节点

1    环境说明

接上篇环境   《Mycat学习笔记》 第一篇. MySql 读写分离与日志分析——主从单结点 http://www.cnblogs.com/kaye0110/p/5134588.html

增加一套 mysql 实例，端口为3308 ，通过Binlog方式同步主机情况

localhost : 3306 主机，    在mycat 中配置为 writehost 1

localhost : 3307 从机 a ，在mycat 中配置为 readhost

localhost : 3308 从机 b ，在mycat 中配置为 writehost 2

 

2    MyCat 配置

基本配置参考上篇，差异在于 switchType 和 balance  和 心跳监控语句

根据参数说明，其实在此处配置readhost已无意义

switchType属性

-  1 表示不自动切换-   1 默认值，自动切换

-  2 基于MySQL主从同步的状态决定是否切换

      心跳语句为 show slave status

-  3 基于MySQL galera cluster的切换机制（适合集群）（1.4.1）

      心跳语句为 show status like ‘wsrep%’

 

balance="0", 不开启读写分离机制，所有读操作都发送到当前可用的writeHost上。 

 

<dataHost name="localhost1" maxCon="1000" minCon="10" balance="0"
　　writeType="0" dbType="mysql" dbDriver="native" switchType="2" slaveThreshold="100">
　　<heartbeat>show slave status</heartbeat>

　　<!-- can have multi write hosts -->
　　<writeHost host="hostM1" url="localhost:3306" user="root" password="root123">
　　<!-- can have multi read hosts -->
　　　　<readHost host="hostS1" url="localhost:3307" user="root" password="root123" />
　　</writeHost>

　　<writeHost host="hostM2" url="localhost:3308" user="root" password="root123"></writeHost>

</dataHost>

 

3    基本SQL操作验证

 直接看下insert 操作的日志，目前数据是直接进入了3306端口的主机了。

01/17 10:53:58.596 DEBUG [$_NIOREACTOR-1-RW] (ServerQueryHandler.java:56) -ServerConnection [id=1, schema=TESTDB, host=0:0:0:0:0:0:0:1, user=test,txIsolation=3, autocommit=true, schema=TESTDB]insert into aaa (id,context) values (7,' insert by M1')

01/17 10:53:58.601 DEBUG [$_NIOREACTOR-1-RW] (NonBlockingSession.java:113) -ServerConnection [id=1, schema=TESTDB, host=0:0:0:0:0:0:0:1, user=test,txIsolation=3, autocommit=true, schema=TESTDB]insert into aaa (id,context) values (7,' insert by M1'), route={
1 -> dn1{insert into aaa (id,context) values (7,' insert by M1')}
} rrs

01/17 10:53:58.601 DEBUG [$_NIOREACTOR-1-RW] (MySQLConnection.java:445) -con need syn ,total syn cmd 1 commands SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;schema change:false con:MySQLConnection [id=1, lastTime=1452999238601, user=root, schema=mycat_sync_test, old shema=mycat_sync_test, borrowed=true, fromSlaveDB=false, threadId=43, charset=utf8, txIsolation=0, autocommit=true, attachment=dn1{insert into aaa (id,context) values (7,' insert by M1')}, respHandler=SingleNodeHandler [node=dn1{insert into aaa (id,context) values (7,' insert by M1')}, packetId=0], host=localhost, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=true]

 

 

再看下select 操作的日志 ，数据从M1 结点获取，即为3306口的Mysql主机

01/17 10:54:04.466 DEBUG [$_NIOREACTOR-1-RW] (ServerQueryHandler.java:56) -ServerConnection [id=1, schema=TESTDB, host=0:0:0:0:0:0:0:1, user=test,txIsolation=3, autocommit=true, schema=TESTDB]select * from aaa

01/17 10:54:04.467 DEBUG [$_NIOREACTOR-1-RW] (EnchachePool.java:70) -SQLRouteCache hit cache ,key:TESTDBselect * from aaa

01/17 10:54:04.467 DEBUG [$_NIOREACTOR-1-RW] (NonBlockingSession.java:113) -ServerConnection [id=1, schema=TESTDB, host=0:0:0:0:0:0:0:1, user=test,txIsolation=3, autocommit=true, schema=TESTDB]select * from aaa, route={
1 -> dn1{SELECT *
FROM aaa
LIMIT 100}
} rrs
01/17 10:54:04.467 DEBUG [$_NIOREACTOR-1-RW] (PhysicalDBPool.java:452) -select read source hostM1 for dataHost:localhost1

01/17 10:54:04.468 DEBUG [$_NIOREACTOR-1-RW] (MySQLConnection.java:445) -con need syn ,total syn cmd 1 commands SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;schema change:false con:MySQLConnection [id=3, lastTime=1452999244468, user=root, schema=mycat_sync_test, old shema=mycat_sync_test, borrowed=true, fromSlaveDB=false, threadId=38, charset=utf8, txIsolation=0, autocommit=true, attachment=dn1{SELECT *
FROM aaa
LIMIT 100}, respHandler=SingleNodeHandler [node=dn1{SELECT *
FROM aaa
LIMIT 100}, packetId=0], host=localhost, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]

 

4    主机下线，读写验证

通过Windows 服务停止Mysql 主机 3306 的服务，开始验证Mycat 主从自动切换效果。

 

数据库SQL验证，前两次查询时直接返回异常，第三次已能正常返回数据

mysql> select * from aaa;
ERROR 1184 (HY000): Connection refused: no further information
mysql> select * from aaa;
ERROR 1184 (HY000): Connection refused: no further information
mysql> select * from aaa;
+----+----------------+
| id | context |
+----+----------------+
| 1 | hello 1 |
| 2 | hello 2 |
| 3 | hello3 |
| 4 | hello4 |
| 5 | hell world5 |
| 6 | new mysql 3308 |
| 7 | insert by M1 |
+----+----------------+
7 rows in set (0.00 sec)

 

再来看下logs/mycat.log 日志文档

还是从dn1获取数据，不过已经改从hostM2取数了。

01/17 11:09:04.975 DEBUG [$_NIOREACTOR-1-RW] (ServerQueryHandler.java:56) -ServerConnection [id=1, schema=TESTDB, host=0:0:0:0:0:0:0:1, user=test,txIsolation=3, autocommit=true, schema=TESTDB]select * from aaa
01/17 11:09:04.980 DEBUG [$_NIOREACTOR-1-RW] (EnchachePool.java:76) -SQLRouteCache miss cache ,key:TESTDBselect * from aaa
01/17 11:09:05.110 DEBUG [$_NIOREACTOR-1-RW] (EnchachePool.java:59) -SQLRouteCache add cache ,key:TESTDBselect * from aaa value:select * from aaa, route={
1 -> dn1{SELECT *
FROM aaa
LIMIT 100}
}
01/17 11:09:05.111 DEBUG [$_NIOREACTOR-1-RW] (NonBlockingSession.java:113) -ServerConnection [id=1, schema=TESTDB, host=0:0:0:0:0:0:0:1, user=test,txIsolation=3, autocommit=true, schema=TESTDB]select * from aaa, route={
1 -> dn1{SELECT *
FROM aaa
LIMIT 100}
} rrs
01/17 11:09:05.113 DEBUG [$_NIOREACTOR-1-RW] (PhysicalDBPool.java:452) -select read source hostM2 for dataHost:localhost1
01/17 11:09:05.114 DEBUG [$_NIOREACTOR-1-RW] (MySQLConnection.java:445) -con need syn ,total syn cmd 1 commands SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;schema change:false con:MySQLConnection [id=14, lastTime=1453000145114, user=root, schema=mycat_sync_test, old shema=mycat_sync_test, borrowed=true, fromSlaveDB=false, threadId=10, charset=utf8, txIsolation=0, autocommit=true, attachment=dn1{SELECT *
FROM aaa
LIMIT 100}, respHandler=SingleNodeHandler [node=dn1{SELECT *
FROM aaa
LIMIT 100}, packetId=0], host=localhost, port=3308, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]

 

同时mycat 出现警告，通知hostM2切为主机，hostS1失效。

01/17 11:09:17.412 WARN [$_NIOREACTOR-0-RW] (MySQLDetector.java:139) -found MySQL master/slave Replication err !!! DBHostConfig [hostName=hostM2, url=localhost:3308]error reconnecting to master 'mycat_sync@192.168.1.247:3306' - retry-time: 60 retries: 86400
01/17 11:09:17.413 DEBUG [$_NIOREACTOR-1-RW] (PhysicalDatasource.java:403) -release channel MySQLConnection [id=11, lastTime=1453000157394, user=root, schema=mycat_sync_test, old shema=mycat_sync_test, borrowed=true, fromSlaveDB=true, threadId=53, charset=utf8, txIsolation=0, autocommit=true, attachment=null, respHandler=null, host=localhost, port=3307, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]
01/17 11:09:17.413 WARN [$_NIOREACTOR-1-RW] (MySQLDetector.java:139) -found MySQL master/slave Replication err !!! DBHostConfig [hostName=hostS1, url=localhost:3307]error reconnecting to master 'mycat_sync@192.168.1.247:3306' - retry-time: 60 retries: 86400
01/17 11:09:18.418 INFO [$_NIOConnector] (AbstractConnection.java:458) -close connection,reason:java.net.ConnectException: Connection refused: no further information ,MySQLConnection [id=0, lastTime=1453000157394, user=root, schema=mycat_sync_test, old shema=mycat_sync_test, borrowed=false, fromSlaveDB=false, threadId=0, charset=utf8, txIsolation=0, autocommit=true, attachment=null, respHandler=null, host=localhost, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]
01/17 11:09:18.418 INFO [$_NIOConnector] (SQLJob.java:111) -can't get connection for sql :show slave status
01/17 11:09:18.418 INFO [$_NIOConnector] (PhysicalDatasource.java:373) -not ilde connection in pool,create new connection for hostM1 of schema mycat_sync_test

 

通过Mycat数据库insert 操作验证

 

mysql> insert into aaa (id,context) values (8,'insert by M2');
Query OK, 1 row affected (0.01 sec)

 

mysql> select * from aaa;
+----+----------------+
| id | context |
+----+----------------+
| 1 | hello 1 |
| 2 | hello 2 |
| 3 | hello3 |
| 4 | hello4 |
| 5 | hell world5 |
| 6 | new mysql 3308 |
| 7 | insert by M1 |
| 8 | insert by M2 |
+----+----------------+
8 rows in set (0.00 sec)

 

再来看下物理机 3307 的情况，查询无最新数据

mysql> use mycat_sync_test
Database changed
mysql> select * from aaa;
+----+----------------+
| id | context |
+----+----------------+
| 1 | hello 1 |
| 2 | hello 2 |
| 3 | hello3 |
| 4 | hello4 |
| 5 | hell world5 |
| 6 | new mysql 3308 |
| 7 | insert by M1 |
+----+----------------+
7 rows in set (0.00 sec)

 

再来看下物理机 3308 M2 的情况，查询有最新数据，说明mycat 自动切换正常。

mysql> use mycat_sync_test
Database changed
mysql> select * from aaa;
+----+----------------+
| id | context |
+----+----------------+
| 1 | hello 1 |
| 2 | hello 2 |
| 3 | hello3 |
| 4 | hello4 |
| 5 | hell world5 |
| 6 | new mysql 3308 |
| 7 | insert by M1 |
| 8 | insert by M2 |
+----+----------------+
8 rows in set (0.00 sec)

 

5    MyCat 监控管理功能

通过9066端口登陆MyCat管理功能 

mysql -u test -ptest -P 9066

 

mysql> show @@datasource;
+----------+--------+-------+-----------+------+------+--------+------+------+--
-------+
| DATANODE | NAME | TYPE | HOST | PORT | W/R | ACTIVE | IDLE | SIZE | EXECUTE |
+----------+--------+-------+-----------+------+------+--------+------+------+---------+
| dn2 | hostM1 | mysql | localhost | 3306 | W | 0 | 0 | 1000 |0 |
| dn2 | hostM2 | mysql | localhost | 3308 | W | 0 | 8 | 1000 |161 |
| dn2 | hostS1 | mysql | localhost | 3307 | R | 0 | 8 | 1000 |137 |
| dn1 | hostM1 | mysql | localhost | 3306 | W | 0 | 0 | 1000 |0 |
| dn1 | hostM2 | mysql | localhost | 3308 | W | 0 | 8 | 1000 |161 |
| dn1 | hostS1 | mysql | localhost | 3307 | R | 0 | 8 | 1000 |137 |
+----------+--------+-------+-----------+------+------+--------+------+------+---------+
6 rows in set (0.02 sec)

 

6    补充说明

回头还要验证下主从恢复功能

另有一篇文章也有提到可以参考：http://www.2cto.com/database/201511/448344.html