转自:http://blog.csdn.net/mchdba/article/details/50616534
6,读写分离模式
6.1然后修改mycat的schema.xml:
balance为1:让全部的readHost及备用的writeHost参与select的负载均衡。
switchType为2:基于MySQL主从同步的状态决定是否切换。
heartbeat:主从切换的心跳语句必须为show slave status。
有配置读节点:
6.2 打开debug模式
[root@wgq_idc_squid_1_11 conf]# vim log4j.xml
<level value="info" /> 变成 <level value="debug" />
6.3 执行查询读操作
在mycat窗口上执行select操作:
1 row in set (0.14 sec)
1 row in set (0.01 sec)
mysql>
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
后台日志显示出只在3327上执行:
01/18 01:35:01.536 DEBUG [$_NIOREACTOR-3-RW] (PhysicalDBPool.java:452) -select read source hostS2 for dataHost:wgq_idc_mon_1_11
01/18 01:35:01.537 DEBUG [$_NIOREACTOR-3-RW] (MySQLConnection.java:445) -con need syn ,total syn cmd 2 commands SET names latin1;SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;schema change:false con:MySQLConnection [id=14, lastTime=1453052101537, user=root, schema=db1, old shema=db1, borrowed=true, fromSlaveDB=false, threadId=3326, charset=latin1, txIsolation=0, autocommit=true, attachment=dn1{SELECT *
FROM company
WHERE id = 1
LIMIT 100}, respHandler=SingleNodeHandler [node=dn1{SELECT *
FROM company
WHERE id = 1
LIMIT 100}, packetId=0], host=127.0.0.1, port=3327, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]
01/18 01:35:01.546 DEBUG [$_NIOREACTOR-2-RW] (NonBlockingSession.java:229) -release connection MySQLConnection [id=14, lastTime=1453052101529, user=root, schema=db1, old shema=db1, borrowed=true, fromSlaveDB=false, threadId=3326, charset=latin1, txIsolation=3, autocommit=true, attachment=dn1{SELECT *
FROM company
WHERE id = 1
LIMIT 100}, respHandler=SingleNodeHandler [node=dn1{SELECT *
FROM company
WHERE id = 1
LIMIT 100}, packetId=5], host=127.0.0.1, port=3327, statusSync=org.opencloudb.mysql.nio.MySQLConnection$StatusSync@7fb43f0f, writeQueue=0, modifiedSQLExecuted=false]
01/18 01:35:01.547 DEBUG [$_NIOREACTOR-2-RW] (PhysicalDatasource.java:403) -release channel MySQLConnection [id=14, lastTime=1453052101529, user=root, schema=db1, old shema=db1, borrowed=true, fromSlaveDB=false, threadId=3326, charset=latin1, txIsolation=3, autocommit=true, attachment=null, respHandler=null, host=127.0.0.1, port=3327, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]
^C
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
![这里写图片描述](https://img-blog.csdn.net/20160202215117387)
6.4 执行写操作
Mycat窗口写操作:
![这里写图片描述](https://img-blog.csdn.net/20160202215138726)
后台log显示写操作在3317上:
![这里写图片描述](https://img-blog.csdn.net/20160202215222585)
01/18 01:39:54.550 DEBUG [$_NIOREACTOR-3-RW] (NonBlockingSession.java:229) -release connection MySQLConnection [id=7, lastTime=1453052394535, user=root, schema=db3, old shema=db3, borrowed=true, fromSlaveDB=false, threadId=163, charset=latin1, txIsolation=3, autocommit=true, attachment=dn3{insert into company values(3,'baidu')}, respHandler=org.opencloudb.mysql.nio.handler.MultiNodeQueryHandler@42bed1e7, host=127.0.0.1, port=3317, statusSync=null, writeQueue=0, modifiedSQLExecuted=true]
01/18 01:39:54.550 DEBUG [$_NIOREACTOR-3-RW] (PhysicalDatasource.java:403) -release channel MySQLConnection [id=7, lastTime=1453052394535, user=root, schema=db3, old shema=db3, borrowed=true, fromSlaveDB=false, threadId=163, charset=latin1, txIsolation=3, autocommit=true, attachment=null, respHandler=null, host=127.0.0.1, port=3317, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]
01/18 01:39:54.550 DEBUG [$_NIOREACTOR-3-RW] (MultiNodeQueryHandler.java:171) -received ok response ,executeResponse:true from MySQLConnection [id=3, lastTime=1453052394535, user=root, schema=db2, old shema=db2, borrowed=true, fromSlaveDB=false, threadId=162, charset=latin1, txIsolation=3, autocommit=true, attachment=dn2{insert into company values(3,'baidu')}, respHandler=org.opencloudb.mysql.nio.handler.MultiNodeQueryHandler@42bed1e7, host=127.0.0.1, port=3317, statusSync=null, writeQueue=0, modifiedSQLExecuted=true]
01/18 01:39:54.551 DEBUG [$_NIOREACTOR-3-RW] (NonBlockingSession.java:229) -release connection MySQLConnection [id=3, lastTime=1453052394535, user=root, schema=db2, old shema=db2, borrowed=true, fromSlaveDB=false, threadId=162, charset=latin1, txIsolation=3, autocommit=true, attachment=dn2{insert into company values(3,'baidu')}, respHandler=org.opencloudb.mysql.nio.handler.MultiNodeQueryHandler@42bed1e7, host=127.0.0.1, port=3317, statusSync=null, writeQueue=0, modifiedSQLExecuted=true]
01/18 01:39:54.551 DEBUG [$_NIOREACTOR-3-RW] (PhysicalDatasource.java:403) -release channel MySQLConnection [id=3, lastTime=1453052394535, user=root, schema=db2, old shema=db2, borrowed=true, fromSlaveDB=false, threadId=162, charset=latin1, txIsolation=3, autocommit=true, attachment=null, respHandler=null, host=127.0.0.1, port=3317, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]
Check下主从数据是否都已经写入了:
主库3317端口:
![这里写图片描述](https://img-blog.csdn.net/20160202215313945)
从裤3327端口:
![这里写图片描述](https://img-blog.csdn.net/20160202215348203)
7,主从切换测试
7.1 mycat托管配置如下
<dataHost name="wgq_idc_mon_1_11" maxCon="1000" minCon="10" balance="1"
writeType="0" dbType="mysql" dbDriver="native" switchType="2" slaveThreshold="100">
<heartbeat>show slave status</heartbeat>
<writeHost host="hostM1" url="127.0.0.1:3317" user="root" password="">
<readHost host="hostR1" url="127.0.0.1:3327" user="root" password=""/>
</writeHost>
<writeHost host="hostS2" url="127.0.0.1:3327" user="root" password="" />
</dataHost>
7.2 关闭主库,写操作切换到从裤3327端口
关闭主库
![这里写图片描述](https://img-blog.csdn.net/20160202215507211)
[root@wgq_idc_squid_1_11 conf]
Shutting down MySQL.... [确定]
[root@wgq_idc_squid_1_11 conf]
Mycat后台报错:
01/18 01:50:01.037 INFO [Timer0] (PhysicalDatasource.java:373) -not ilde connection in pool,create new connection for hostM1 of schema db1
01/18 01:50:01.038 INFO [$_NIOConnector] (AbstractConnection.java:458) -close connection,reason:java.net.ConnectException: 拒绝连接 ,MySQLConnection [id=0, lastTime=1453053001035, user=root, schema=db1, old shema=db1, borrowed=false, fromSlaveDB=false, threadId=0, charset=utf8, txIsolation=0, autocommit=true, attachment=null, respHandler=null, host=127.0.0.1, port=3317, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]
01/18 01:50:01.038 INFO [$_NIOConnector] (SQLJob.java:111) -can't get connection for sql :show slave status
01/18 01:50:01.038 WARN [$_NIOREACTOR-1-RW] (MySQLDetector.java:139) -found MySQL master/slave Replication err !!! DBHostConfig [hostName=hostR1, url=127.0.0.1:3327]error reconnecting to master 'repl@127.0.0.1:3317' - retry-time: 60 retries: 1
0
看到主从失效,因为主down了。
因为我们通过mycat配置了主从切换模式,现在3317端口主库down了,那么写库应该自动切换到从裤3327上面,如果在mycat上面写入,就应该写到3327端口的从库了,验证如下:
(1)在mycat窗口录入数据:
(2)在3327端口从裤验证数据:
![这里写图片描述](https://img-blog.csdn.net/20160202215631603)
看到3327端口的数据有新录入的4,meituan的数据。表明验证成功。
(3) dnindex属性文件中writeHost已经变成了第二个了,如下所示:
![这里写图片描述](https://img-blog.csdn.net/20160202215701128)
7.3 再启动原来的主库3317端口,在mycat上做写操作,后台mysql写库还是原来的从库3327端口:
这个时候就会报错,主从连接失败,原因是从库3327会再重新从主库3317同步所有的数据,但是从库已经有了,所以就会报错,如下所示:
![这里写图片描述](https://img-blog.csdn.net/20160202215812416)
我采用
stop slave;
set global sql_slave_skip_counter=1;
start slave;
show slave status\G;
一个个忽略过后,就正常了,没有报错:
![这里写图片描述](https://img-blog.csdn.net/20160202215850385)
但是这个时候如果再在mycat窗口上录入数据,debug分析面,还是会写入到3327里面去:
![这里写图片描述](https://img-blog.csdn.net/20160202215915791)
后台debug日志:
![这里写图片描述](https://img-blog.csdn.net/20160202215956541)
OK,至此,mycat主从切换成功。
补充点:实际生产环境需要配置双主,不会切到读库