Mycat主从模式下的读写分离与自动切换

1. 机器环境

192.168.2.136   mycat1
192.168.2.134   mydb1
192.168.2.135   mydb2

2在mysql1、mysql2上安装mysql 更改root用户密码

安装mysql过程略

#两台服务器都要
use mysql
GRANT ALL PRIVILEGES ON *.* TO root@"%" IDENTIFIED BY "root123";
update user set Password = password('root123') where User='root';
GRANT replication slave , replication client ON *.* TO 'repl'@'%' identified by 'root123';
flush privileges;

3 配置mysql复制ip

mydb1 上：

mysql> show master status;
+---------------+----------+--------------+------------------+-------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000004 |      607 |              |                  |                   |
+---------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

在mysql2：向主库做同步操作，开启复制

change master to master_host='192.168.2.134',
master_port=3306,
master_user='repl', 
master_password='root123', 
master_log_file='binlog.000004',
master_log_pos=607;

start slave;

show slave statusG

在mysql1创建测试库

create database db1;
create database db2;
create database db3;
 
可以看到已经 同步到 mysql2上

4 安装、配置mycat

安装mycat(见 上一篇 基本部署 ,略)

配置mycat

cd /usr/local/mycat/conf
vi 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" dataNode="dn1">
   </schema>       
   <schema name="db2" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn2">
   </schema>
   <schema name="db3" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn3">
   </schema>
    <dataNode name="dn1" dataHost="mysqlserver" database="db1" />
    <dataNode name="dn2" dataHost="mysqlserver" database="db2" />
    <dataNode name="dn3" dataHost="mysqlserver" database="db3" />


    <!--######### TESTDB  ########-->
    <dataHost name="mysqlserver" maxCon="1000" minCon="10" balance="1"
            writeType="0" dbType="mysql" dbDriver="native" switchType="-1"  slaveThreshold="100">
            <heartbeat>select user()</heartbeat>
            <writeHost host="hostM1" url="192.168.2.134:3306" user="root" password="root123">
            </writeHost>


            <writeHost host="hostS1" url="192.168.2.135:3306" user="root" password="root123">
            </writeHost>
    </dataHost>
</mycat:schema>


#vi server.xml
<user name="root">
            <property name="password">root123</property>
            <property name="schemas">db1,db2,db3</property>
</user>

vi log4j.xml

  <root>
    <level value="debug" />
    <appender-ref ref="FILE" />
    <!--<appender-ref ref="FILE" />-->
  </root>

</log4j:configuration>

几个关键配置的解释:

writeType="0" : 写操作只写入一个节点

balance=0,1,2,3 --负载均衡类型。

0：不开启读写分离机制，所有读操作都发送到当前可用的writeHost上
1：启用读写分离，全部的readHost与stand by writeHost参与select语句的负载均衡，简单的说，当双主双从模式
  (M1->S1，M2->S2，并且M1与 M2互为主备)，正常情况下，M2,S1,S2都参与select语句的负载均衡。 (常用配置)；
2：所有读操作都随机的在writeHost、readhost上分发。
3：所有读请求随机的分发到wiriterHost对应的readhost执行，writerHost不负担读压力

switchType="-1,1,2,3" --是否启用主从切换

 -1：表示不启用主从切换；
  1：为默认值，自动切换；
  2：基于主从同步的状态，决定是否切换，与show slave status心跳对应；
  3：基于多住galary集群切换，与show status like 'wsrep%'对应；

因此，该配置文件中的balance="1"意味着作为stand by writeHost的hostS1 将参与select语句的负载均衡，这就实现了主从的读写分离，switchType='-1'意味着当主挂掉的时候，不会进行切换,此时无法写入,但是可以查询

5 启动 mycat

[root@localhost conf]# mycat start

[root@localhost conf]# mycat status

[root@localhost conf]# tail -800  /usr/local/mycat/logs/wrapper.log

[root@localhost conf]# netstat -an|grep 9066

6.验证读写分离模式：

[root@mycat ~]#  mysql -uroot -proot123  -h192.168.2.136 -P8066

mysql> show databases;

#主库上设置复制模式为STATEMENT
set session binlog_format='STATEMENT';

#插入数据时带入主机名 这样可以很直观的看出读写分离,由于刚才改成了 statement模式,所以这个方法可行,但如果是默认的 row模式,则不可行

use db1;
create table users (id int not null primary key,hostname varchar(100) ,name varchar(100));
insert into  users(id,hostname,name) values(1,@@hostname，'fish');


#查询mydb

mysql> select * from users;

#查询mycat

mysql> select * from users;

对比可以看出,查询出来的 hostname 全是mydb2 说明目前读写分离了！！

当然也可以通过日志来查看

可以看到读取都是读的从库

7.验证自动切换的模式

# 修改 mycat 配置 switch_type=1
cd /usr/local/mycat/conf
vi 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" dataNode="dn1">
   </schema>       
   <schema name="db2" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn2">
   </schema>
   <schema name="db3" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn3">
   </schema>
    <dataNode name="dn1" dataHost="mysqlserver" database="db1" />
    <dataNode name="dn2" dataHost="mysqlserver" database="db2" />
    <dataNode name="dn3" dataHost="mysqlserver" database="db3" />


    <!--######### TESTDB  ########-->
    <dataHost name="mysqlserver" maxCon="1000" minCon="10" balance="1"
            writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
            <heartbeat>show slave status</heartbeat>
            <writeHost host="hostM1" url="192.168.2.134:3306" user="root" password="root123">
            </writeHost>
            <writeHost host="hostS1" url="192.168.2.135:3306" user="root" password="root123">
            </writeHost>
    </dataHost>
</mycat:schema>

#重启 mycat

#查看管理信息 检查心跳

[root@mycat ~]# mysql -uroot -proot123 -h192.168.2.136 -P9066

mysql> show @@heartbeat;

mysql> show @@backend;

#关闭主库     
[mysql@mydb1 ~]$ mysqladmin shutdown --socket=/MySQL/my3306/run/mysql.sock  --port=3306

#通过检查心跳发现主库的RS_CODE=-1

#测试插入数据是否成功
mysql>  insert into  users(id,name) values(2,'fish2');  

#查看日志

可以看到 已经切换到 mydb2上执行insert

#查询数据
mysql> select * from users where id =2 ;

#查看日志:
10/19 06:40:56.375  DEBUG [$_NIOREACTOR-0-RW] (PhysicalDBPool.java:452) -select read source
hostS1 for dataHost:mysqlserver
10/19 06:40:56.381  DEBUG [$_NIOREACTOR-0-RW] (NonBlockingSession.java:229) -release connection
MySQLConnection [id=14, lastTime=1508409656368, user=root, schema=db1, old shema=db1, 
borrowed=true, fromSlaveDB=false, threadId=9, charset=utf8, txIsolation=3, autocommit=true,
 attachment=dn1{select * from users where id =2}, respHandler=SingleNodeHandler [node=dn1{select
  * from users where id =2}, packetId=6], host=192.168.2.135, port=3306, statusSync=null, 
 writeQueue=0, modifiedSQLExecuted=false]

#查看管理信息
mysql> show @@backend;

可以看到 自动切换已经成功 只剩下 mydb2