mycat 读写分离，balance的理解

一、环境配置

1.1 数据库配置

项目	Mycat	Mycat-master	Mycat-master-standby	Mycat-slave
ip	10.2.14.196	47.92.159.234	59.40.181.247	118.31.21.138
port	8066/9066	3306	3306	3306

 

 

 

2.2 创建表

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;

2.3 schema 配置

　　1.当balance=0 时，不开启读写分离，所有读操作都发生在当前的writeHost上

　　

<schema name="mycat01" checkSQLschema="false" sqlMaxLimit="100">
        <table name="travelrecord" primaryKey="id" dataNode="MySQL" />
    </schema>

    <dataNode name="MySQL" dataHost="MySQL_Host" database="yf" />

    <dataHost name="MySQL_Host" maxCon="1000" minCon="10" balance="0"
              writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
              <heartbeat>select user()</heartbeat>
              <writeHost host="MySQL_M1" url="47.92.159.234:3306" user="root"
                   password="*******">
                   <readHost host="MySQL_S1" url="118.31.21.138:3306" user="root" password="*******" />
              </writeHost>
              <writeHost host="MYSQL_M2" url="59.40.181.247:3306" user="root" password="******">
              </writeHost>
    </dataHost>    

登录mycat :mysql -uroot -ppassword -p9066

mysql> show @@datasource;
+----------+----------+-------+---------------+------+------+--------+------+------+---------+-----------+------------+
| DATANODE | NAME | TYPE | HOST | PORT | W/R | ACTIVE | IDLE | SIZE | EXECUTE | READ_LOAD | WRITE_LOAD |
+----------+----------+-------+---------------+------+------+--------+------+------+---------+-----------+------------+
| MySQL | MySQL_M1 | mysql | 47.92.159.234 | 3306 | W | 0 | 1 | 1000 | 71 | 0 | 0 |
| MySQL | MYSQL_M2 | mysql | 59.40.181.247 | 3306 | W | 0 | 10 | 1000 | 85 | 6 | 0 |
| MySQL | MySQL_S1 | mysql | 118.31.21.138 | 3306 | R | 0 | 7 | 1000 | 77 | 0 | 0 |
+----------+----------+-------+---------------+------+------+--------+------+------+---------+-----------+------------+

use mycat01;

select * from travelrecord;

查看mycat.logs

2018-05-17 19:15:11,975 [DEBUG][$_NIOREACTOR-2-RW] release connection MySQLConnection [id=10, lastTime=1526555711970, user=root, schema=yf, old shema=yf, borrowed=true, fromSlaveDB=false, threadId=16, charset=utf8, txIsolation=3, autocommit=true, attachment=MySQL{SELECT *
INFO | jvm 1 | 2018/05/17 19:15:11 | FROM travelrecord
INFO | jvm 1 | 2018/05/17 19:15:11 | LIMIT 100}, respHandler=SingleNodeHandler [node=MySQL{SELECT *
INFO | jvm 1 | 2018/05/17 19:15:11 | FROM travelrecord
INFO | jvm 1 | 2018/05/17 19:15:11 | LIMIT 100}, packetId=5], host=10.2.14.196, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=false] (io.mycat.server.NonBlockingSession:NonBlockingSession.java:341)

 

可以看出所以读操作都在MySQL_M1上

 

2. 当balance=1 ，所有读操作都随机发送到当前的writeHost对应的readHost和备用的writeHost

mysql> show @@datasource;
+----------+----------+-------+---------------+------+------+--------+------+------+---------+-----------+------------+
| DATANODE | NAME | TYPE | HOST | PORT | W/R | ACTIVE | IDLE | SIZE | EXECUTE | READ_LOAD | WRITE_LOAD |
+----------+----------+-------+---------------+------+------+--------+------+------+---------+-----------+------------+
| MySQL | MySQL_M1 | mysql | 10.2.14.196 | 3306 | W | 0 | 10 | 1000 | 14 | 0 | 0 |
| MySQL | MYSQL_M2 | mysql | 59.40.181.247 | 3306 | W | 0 | 1 | 1000 | 5 | 0 | 0 |
| MySQL | MySQL_S1 | mysql | 118.31.21.138 | 3306 | R | 0 | 4 | 1000 | 8 | 0 | 0 |
+----------+----------+-------+---------------+------+------+--------+------+------+---------+-----------+------------+
3 rows in set (0.01 sec)

select * from travelrecord;

[node=MySQL{SELECT *
INFO | jvm 1 | 2018/05/17 19:30:48 | FROM travelrecord
INFO | jvm 1 | 2018/05/17 19:30:48 | LIMIT 100}, packetId=5], host=59.40.181.247, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]

[node=MySQL{SELECT *
INFO | jvm 1 | 2018/05/17 19:30:48 | FROM travelrecord
INFO | jvm 1 | 2018/05/17 19:30:48 | LIMIT 100}, packetId=0], host=118.31.21.138, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=false

 

3.当balance=2，所有的读操作都随机发送到所有的writeHost,readHost上

[DEBUG][$_NIOREACTOR-0-RW] select read source MySQL_S1 for dataHost:MySQL_Host

select read source MySQL_M1 for dataHost:MySQL_Host  

 [DEBUG][$_NIOREACTOR-0-RW] select read source MYSQL_M2 for dataHost:MySQL_Host

 

4.当balance=3 ，所有的读操作都只发送到writeHost的readHost上

[DEBUG][$_NIOREACTOR-0-RW] select read source MySQL_S1 for dataHost:MySQL_Host