Mycat 03 - 读写分离

1.配置文件的内容

schema.xml
一个主库 + 两个从库 做读写分离
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">

        <schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
                <table name="travelrecord" dataNode="dn1,dn2,dn3" rule="auto-sharding-long" />
                <table name="company" primaryKey="ID" type="global" dataNode="dn1,dn2,dn3" />
                <!-- random sharding using mod sharind rule -->
                <table name="hotnews" primaryKey="ID" autoIncrement="true" dataNode="dn1,dn2,dn3"
                           rule="mod-long" />
                <table name="employee" primaryKey="ID" dataNode="dn1,dn2"
                           rule="sharding-by-intfile" />
        </schema>

        <dataNode name="dn1" dataHost="localhost1" database="db1" />
        <dataNode name="dn2" dataHost="localhost1" database="db2" />
        <dataNode name="dn3" dataHost="localhost1" database="db3" />

        <dataHost name="localhost1" maxCon="1000" minCon="10" balance="1"
                          writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
                <heartbeat>select user()</heartbeat>
                <!-- can have multi write hosts -->
                <writeHost host="hostM1" url="10.1.3.67:3606" user="mycat"
                                   password="123456">
                        <!-- can have multi read hosts -->
                        <readHost host="hostS1" url="10.1.3.68:3606" user="mycat" password="123456" />
                        <readHost host="hostS2" url="10.1.3.69:3606" user="mycat" password="123456" />
                </writeHost>
        <!--    <writeHost host="hostS1" url="10.1.3.69:3606" user="mycat"
                                   password="123456" />  -->
        </dataHost>
</mycat:schema>

读写分离参数：

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

 

2.登录到mycat 创建表并插入数据。

mysql -uroot -p123456 -P8066 -h10.1.2.140
mysql> show  tables;
+---------------+
| Tables_in_db1 |
+---------------+
| company       |
| employee      |
| hotnews       |
| travelrecord  |
| cat_1         |
| cat_2         |
| test_1        |
+---------------+
7 rows in set (0.00 sec)

mysql> select * from test_1;
+----+-------+
| id | name  |
+----+-------+
|  1 | louis |
|  2 | king  |
|  3 | fdf   |
|  5 | fd    |
+----+-------+

3.设置日志级别为debug，执行查询语句查看路由的节点。

管理端口查看节点信息。

mysql> show @@datasource;
+----------+--------+-------+-----------+------+------+--------+------+------+---------+-----------+------------+
| DATANODE | NAME   | TYPE  | HOST      | PORT | W/R  | ACTIVE | IDLE | SIZE | EXECUTE | READ_LOAD | WRITE_LOAD |
+----------+--------+-------+-----------+------+------+--------+------+------+---------+-----------+------------+
| dn1      | hostM1 | mysql | 10.1.3.67 | 3606 | W    |      0 |   11 | 1000 |     885 |         0 |          0 |
| dn1      | hostS1 | mysql | 10.1.3.68 | 3606 | R    |      0 |   11 | 1000 |     876 |         1 |          0 |
| dn1      | hostS2 | mysql | 10.1.3.69 | 3606 | R    |      0 |    6 | 1000 |     870 |         3 |          0 |
| dn3      | hostM1 | mysql | 10.1.3.67 | 3606 | W    |      0 |   11 | 1000 |     885 |         0 |          0 |
| dn3      | hostS1 | mysql | 10.1.3.68 | 3606 | R    |      0 |   11 | 1000 |     876 |         1 |          0 |
| dn3      | hostS2 | mysql | 10.1.3.69 | 3606 | R    |      0 |    6 | 1000 |     870 |         3 |          0 |
| dn2      | hostM1 | mysql | 10.1.3.67 | 3606 | W    |      0 |   11 | 1000 |     885 |         0 |          0 |
| dn2      | hostS1 | mysql | 10.1.3.68 | 3606 | R    |      0 |   11 | 1000 |     876 |         1 |          0 |
| dn2      | hostS2 | mysql | 10.1.3.69 | 3606 | R    |      0 |    6 | 1000 |     870 |         3 |          0 |
+----------+--------+-------+-----------+------+------+--------+------+------+---------+-----------+------------+

如果一个节点宕机则心跳检测会变成-1 
mysql> show @@heartbeat;

+--------+-------+-----------+------+---------+-------+--------+---------+--------------+---------------------+-------+
| NAME | TYPE | HOST | PORT | RS_CODE | RETRY | STATUS | TIMEOUT | EXECUTE_TIME | LAST_ACTIVE_TIME | STOP |
+--------+-------+-----------+------+---------+-------+--------+---------+--------------+---------------------+-------+
| hostM1 | mysql | 10.1.3.67 | 3606 | 1 | 0 | idle | 0 | 0,0,0 | 2018-03-30 07:59:22 | false |
| hostS1 | mysql | 10.1.3.68 | 3606 | 1 | 0 | idle | 0 | 0,0,0 | 2018-03-30 07:59:22 | false |
| hostS2 | mysql | 10.1.3.69 | 3606 | -1 | 0 | idle | 0 | 0,0,0 | 2018-03-30 07:59:22 | false |
+--------+-------+-----------+------+---------+-------+--------+---------+--------------+---------------------+-------+