Mycat之——读写分离与主从切换案例

转载请注明出处：http://blog.csdn.net/l1028386804/article/details/76944940

一、环境准备

软件版本：
操作系统：CentOS-6.5-x86_64-bin-DVD1.iso
JDK版本：jdk1.7.0_80
Mycat版本：Mycat-server-1.5.1-RELEASE-20170717215510-Linux.tar.gz (下载源码自行编译的)
MySQL:5.6.32

二、安装数据库并配置主从复制

1、不同主机安装数据库

不同主机安装数据库，并配置主从复制，大家可以参考博文《MySQL之——CentOS6.5 编译安装MySQL5.6.16》和《MySQL之——主从复制的配置》

2、同一主机安装多数据库

这里，再给大家介绍一种如何在同一主机上安装多个MySQL数据库，

2.1安装主库

tar -xvf mysql-5.6.32.tar.gzcd mysql-5.6.32time cmake . -DCMAKE_INSTALL_PREFIX=/usr/local/mysql3317 -DMYSQL_DATADIR=/home/data/mysql3317/data -DWITH_INNOBASE_STORAGE_ENGINE=1 -DMYSQL_UNIX_ADDR==/usr/local/mysql3317/mysql.sock -DMYSQL_USER=mysql -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_citime maketime make install chown -R mysql /home/data/chgrp -R mysql /home/data/chown -R mysql /usr/local/mysqlchown -R mysql /usr/local/mysql3317chgrp -R mysql /usr/local/mysqlchgrp -R mysql /usr/local/mysql3317/mkdir -p /home/data/mysql3317/binlog/chown -R mysql.mysql /home/data/mysql3317/binlog/mkdir -p /home/data/mysql5610/binlog/chown -R mysql.mysql /home/data/cd /usr/local/mysql3317time scripts/mysql_install_db  --user=mysql --basedir=/usr/local/mysql3317 --datadir=/home/data/mysql3317/data --defaults-file=/usr/local/mysql3317/my.cnfcp support-files/mysql.server /etc/init.d/mysql3317chmod 700 /etc/init.d/mysql3317echo "export PATH=$PATH:/usr/local/mysql3317/bin">>/etc/profile source /etc/profilechkconfig --add mysql3317service mysql3317 start

2.2安装从库

tar -xvf mysql-5.6.32.tar.gzcd mysql-5.6.32time cmake . -DCMAKE_INSTALL_PREFIX=/usr/local/mysql3327 -DMYSQL_DATADIR=/home/data/mysql3327/data -DWITH_INNOBASE_STORAGE_ENGINE=1 -DMYSQL_UNIX_ADDR==/usr/local/mysql3327/mysql.sock -DMYSQL_USER=mysql -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_citime maketime make install chown -R mysql /home/data/chgrp -R mysql /home/data/chown -R mysql /usr/local/mysqlchown -R mysql /usr/local/mysql3327chgrp -R mysql /usr/local/mysqlchgrp -R mysql /usr/local/mysql3327/mkdir -p /home/data/mysql3327/binlog/chown -R mysql.mysql /home/data/mysql3327/binlog/mkdir -p /home/data/mysql5610/binlog/chown -R mysql.mysql /home/data/cd /usr/local/mysql3327time scripts/mysql_install_db  --user=mysql --basedir=/usr/local/mysql3327 --datadir=/home/data/mysql3327/data --defaults-file=/usr/local/mysql3327/my.cnfcp support-files/mysql.server /etc/init.d/mysql3327chmod 700 /etc/init.d/mysql3327echo "export PATH=$PATH:/usr/local/mysql3327/bin">>/etc/profile source /etc/profilechkconfig --add mysql3327service mysql3327 start

主从复制参见博文《MySQL之——主从复制的配置》

三、安装Mycat

将Mycat上传到指定路径并解压。

四、数据同步测试

1、配置schema.xml

然后修改mycat的schema.xml：
balance为1：让全部的readHost及备用的writeHost参与select的负载均衡。
switchType为2：基于MySQL主从同步的状态决定是否切换。
heartbeat：主从切换的心跳语句必须为show slave status。

2、数据录入

mysql> explain create table company(id int not null primary key,name varchar(100));+-----------+---------------------------------------------------------------------+| DATA_NODE | SQL                                                                 |+-----------+---------------------------------------------------------------------+| dn1       | create table company(id int not null primary key,name varchar(100)) || dn2       | create table company(id int not null primary key,name varchar(100)) || dn3       | create table company(id int not null primary key,name varchar(100)) |+-----------+---------------------------------------------------------------------+3 rows in set (0.00 sec)mysql> create table company(id int not null primary key,name varchar(100));Query OK, 0 rows affected (0.01 sec)mysql> explain  insert into company(id,name) values(1,'liuyazhuang');+-----------+--------------------------------------------------+| DATA_NODE | SQL                                              |+-----------+--------------------------------------------------+| dn1       | insert into company(id,name) values(1,'liuyazhuang') || dn2       | insert into company(id,name) values(1,'liuyazhuang') || dn3       | insert into company(id,name) values(1,'liuyazhuang') |+-----------+--------------------------------------------------+3 rows in set (0.10 sec)mysql>  insert into company(id,name) values(1,'liuyazhuang');Query OK, 1 row affected (0.00 sec)mysql>

3、主库验证

[root@liuyazhuang140 logs]# /usr/local/mysql3317/bin/mysql -uroot -p -P3317 --socket=/usr/local/mysql3317/mysql.sock -e "select @@port;select * from db1.company";Enter password: +--------+| @@port |+--------+|   3317 |+--------++----+---------+| id | name    |+----+---------+|  1 | liuyazhuang |+----+---------+[root@liuyazhuang140 logs]#

4、从库验证

[root@liuyazhuang140 logs]# /usr/local/mysql3327/bin/mysql -uroot -p -P3327 --socket=/usr/local/mysql3327/mysql.sock -e "select @@port;select * from db1.company";Enter password: +--------+| @@port |+--------+|   3327 |+--------++----+---------+| id | name    |+----+---------+|  1 | liuyazhuang |+----+---------+[root@liuyazhuang140 logs]#

五、读写分离

1、修改mycat的schema.xml

balance为1：让全部的readHost及备用的writeHost参与select的负载均衡。
switchType为2：基于mysql主从同步的状态决定是否切换。
heartbeat：主从切换的心跳语句必须为show slave status。
并增加读节点：

<readHost host="hostR1" url="127.0.0.1:3327" user="root" password="root"></readHost>

具体如下：

<dataHost name="localhost1" 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="root">        <readHost host="hostR1" url="127.0.0.1:3327" user="root" password="root"></readHost>    </writeHost>    <writeHost host="hostS2" url="127.0.0.1:3327" user="root" password="root"></writeHost></dataHost>

2、开启debug模式

打开mycat下的log4j.xml文件将

<level value="info" />

修改为

<level value="debug" />

3、执行查询读操作

mysql> explain select * from company where id=1;+-----------+----------------------------------------------+| DATA_NODE | SQL                                          |+-----------+----------------------------------------------+| dn3       | SELECT * FROM company WHERE id = 1 LIMIT 100 |+-----------+----------------------------------------------+1 row in set (0.14 sec)mysql> select * from company where id=1;+----+---------+| id | name    |+----+---------+|  1 | liuyazhuang |+----+---------+1 row in set (0.01 sec)mysql>

后台日志显示出只在3327上执行：

07/18 01:35:01.536  DEBUG [$_NIOREACTOR-3-RW] (PhysicalDBPool.java:452) -select read source hostS2 for dataHost:wgq_idc_mon_1_1107/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 companyWHERE id = 1LIMIT 100}, respHandler=SingleNodeHandler [node=dn1{SELECT *FROM companyWHERE id = 1LIMIT 100}, packetId=0], host=127.0.0.1, port=3327, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]07/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 companyWHERE id = 1LIMIT 100}, respHandler=SingleNodeHandler [node=dn1{SELECT *FROM companyWHERE id = 1LIMIT 100}, packetId=5], host=127.0.0.1, port=3327, statusSync=org.opencloudb.mysql.nio.MySQLConnection$StatusSync@7fb43f0f, writeQueue=0, modifiedSQLExecuted=false]07/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]

4、执行写操作

Mycat窗口写操作：

后台log显示写操作在3317上：

07/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]07/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]07/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]07/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]07/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]

六、主从切换

1、schema.xml配置如下

<dataHost name="localhost1" 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="root">        <readHost host="hostR1" url="127.0.0.1:3327" user="root" password="root"></readHost>    </writeHost>    <writeHost host="hostS2" url="127.0.0.1:3327" user="root" password="root"></writeHost></dataHost>

2、关闭主库，写操作切换到从裤3327端口

[root@liuyazhuang140 conf]# service mysql3317 stopShutting down MySQL....                                    [确定][root@liuyazhuang140 conf]#

Mycat后台报错

07/18 01:50:01.037   INFO [Timer0] (PhysicalDatasource.java:373) -not ilde connection in pool,create new connection for hostM1 of schema db107/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]07/18 01:50:01.038   INFO [$_NIOConnector] (SQLJob.java:111) -can't get connection for sql :show slave status07/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: 10

看到主从失效，因为主down了。

因为我们通过mycat配置了主从切换模式，现在3317端口主库down了，那么写库应该自动切换到从裤3327上面，如果在mycat上面写入，就应该写到3327端口的从库了，验证如下：在Mycat窗口录入数据，在3327从库查看数据，如果3327从库有在Mycat窗口录入的数据表明验证成功。

而且此时，Mycat的dnindex属性文件中writeHost已经变成了第二个了

#update#Tue Aug 08 22:13:41 CST 2017localhost1=1

注意：Mycat的dnindex属性文件中writeHost顺序是从0开始，即0代表第一个

3、启动原来的3317主库

再启动原来的主库3317端口，在mycat上做写操作，后台mysql写库还是原来的从库3327端口：
这个时候就会报错，主从连接失败，原因是从库3327会再重新从主库3317同步所有的数据，但是从库已经有了，所以就会报错，如下所示：

我们可以在命令行输入如下命令解决这个问题：

stop slave;set global sql_slave_skip_counter=1;start slave;show slave status\G

一个个忽略过后，就正常了，没有报错：

但是这个时候如果再在mycat窗口上录入数据，debug分析，还是会写入到3327里面去

Mycat后台日志如下：

07/18 01:55: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=3327, statusSync=null, writeQueue=0, modifiedSQLExecuted=true]07/18 01:55: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=3327, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]07/18 01:55: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=3327, statusSync=null, writeQueue=0, modifiedSQLExecuted=true]07/18 01:55: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=3327, statusSync=null, writeQueue=0, modifiedSQLExecuted=true]07/18 01:55: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=3327, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]

至此，mycat主从切换成功。