1. Mycat高可用参数
有关Mycat高可用的schema.xml
配置参数相关说明:
balance
属性
负载均衡类型,目前的取值有如下4种。
* balance="0"
: 不开启读写分离机制,所有读操作都发送到当前可用的writeHost上。
* balance="1"
: 全部的readHost与stand by writeHost都参与select语句的负载均衡,简而言之,当为双主双从模式(M1->S1,M2->S2,并且M1与M2互为主备)时,在正常情况下,M2、S1和S2都参与select语句的负载均衡。
* balance="2"
: 所有的读操作都随机地在writeHost、readHost上分发。
* balance="3"
: 所有的读请求都随机分发到writeHost对应的readHost上执行,writeHost不负担读压力。
writeType
属性
负载均衡类型目前的取值有两种。
* writeType="0"
: 所有的写操作都发送到配置的第1个writeHost上,writeHost挂了则切到writeHost2上,重新恢复writeHost1节点后,不会再切回来,还是以writeHost2为准,切换记录在配置文件dnindex.properties中。
* writeType="0"
: 所有的写操作都随机地发送到配置的writeHost上,Mycat1.5版本以后不再推荐使用该值。
switchType
属性
* switchType="-1"
: 表示不自动切换。
* switchType="1"
: 为默认值(或为0),表示自动切换。
* switchType="2"
: 表示基于MySQL主从同步的状态决定是否切换,心跳语句如下:
show slave status
switchType="3"
: 表示基于 MySQL Galary Cluster的切换机制,心跳语句如下:
show status like 'wsrep%'
2. MySQL读写分离master-slave
对于MySQL,主流的读写分离是master-slave和galera cluster,下面分别介绍如何通过Mycat来实现这两种读写分离。
2.1 MySQL主从读写分离环境
Mycat:192.168.33.11 Port: 8086/9066 MySQL-master:192.168.33.11 Port:3306 MySQL-slaveA :192.168.33.12 Port :3306 MySQL-slaveB:192.168.33.13 Port: 33
MySQL为一主两从,192.168.33.11同时为192.168.33.12和192.168.33.13的主
[root@testA ~]# mysql -uroot -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 117
Server version: 5.7.19-log Source distribution
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> grant replication slave on *.* to 'repl'@'192.168.33.12' identified by '123456';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> grant replication slave on *.* to 'repl'@'192.168.33.13' identified by '123456';
Query OK, 0 rows affected, 1 warning (0.02 sec)
mysql>
[root@testB ~]# mysql -uroot -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 61
Server version: 5.7.19-log Source distribution
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> change master to master_host='192.168.33.11',master_port=3306,master_user='repl',master_password='123456',MASTER_AUTO_POSITION=1;
Query OK, 0 rows affected, 2 warnings (2.61 sec)
mysql> start slave;
Query OK, 0 rows affected (0.23 sec)
[root@testC ~]# mysql -uroot -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 48
Server version: 5.7.19-log Source distribution
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> change master to master_host='192.168.33.11',master_port=3306,master_user='repl',master_password='123456',MASTER_AUTO_POSITION=1;
Query OK, 0 rows affected, 2 warnings (2.66 sec)
mysql> start slave;
Query OK, 0 rows affected (0.23 sec)
- 查看主从,都正常。
2.2 验证测试
vim conf/schema.xml
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
<schema name="test01" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
</schema>
<dataNode name="dn1" dataHost="dh1" database="test01"/>
<dataHost balance="0" maxCon="1000" minCon="10" name="dh1" writeType="0" switchType="2" slaveThreshold="100" dbType="mysql" dbDriver="native">
<heartbeat>show slave status</heartbeat>
<writeHost host="hostM1" url="192.168.33.11:3306" password="123456" user="root"/>
<writeHost host="hostS2" url="192.168.33.12:3306" password="123456" user="root"/>
<writeHost host="hostS3" url="192.168.33.13:3306" password="123456" user="root"/>
</dataHost>
</mycat:schema>
测试SQL:
CREATE TABLE t1 (`time` datetime NOT NULL ,`db_nm` varchar(20) NULL);
INSERT INTO t1 (time,db_nm) VALUES (now(), database());
INSERT INTO t1 (time,db_nm) VALUES (now(), database());
INSERT INTO t1 (time,db_nm) VALUES (now(), database());
[root@testA mycat]# mysql -uroot -p123456 -h127.0.0.1 -P8066 test01
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.6.29-mycat-1.6-RELEASE-20161028204710 MyCat Server (OpenCloundDB)
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> CREATE TABLE t1 (`time` datetime NOT NULL ,`db_nm` varchar(20) NULL);
INSERT INTO t1 (time,db_nm) VALUES (now(), database());
INSERT INTO t1 (time,db_nm) VALUES (now(), database());
INSERT INTO t1 (time,db_nm) VALUES (now(), database()); Query OK, 0 rows affected (0.04 sec)
mysql> INSERT INTO t1 (time,db_nm) VALUES (now(), database());
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO t1 (time,db_nm) VALUES (now(), database());
Query OK, 1 row affected (0.01 sec)
mysql> INSERT INTO t1 (time,db_nm) VALUES (now(), database());
Query OK, 1 row affected (0.00 sec)
mysql> select * from t1;
+---------------------+--------+
| time | db_nm |
+---------------------+--------+
| 2017-11-01 17:38:03 | test01 |
| 2017-11-01 17:38:03 | test01 |
| 2017-11-01 17:38:03 | test01 |
+---------------------+--------+
3 rows in set (0.01 sec)
- 开启DEBUG日志,
vim conf/log4j2.c
-
... <Configuration status="DEBUG"> ... <asyncRoot level="DEBUG" includeLocation="true"> ...
- 可以从DEBUG日志中看到,读操作发送到当前的writeHost上。
tail -f logs/wrapper.log
INFO | jvm 1 | 2017/11/01 18:39:21 | 2017-11-01 18:39:21,738 [DEBUG][$_NIOREACTOR-1-RW] select read source hostM1 for dataHost:dh1 (io.mycat.backend.datasource.PhysicalDBPool:PhysicalDBPool.java:456)
- 2.3 读写分离2种配置的区别
配置方式一
<writeHost host="hostM1" url="192.168.33.11:3306" password="123456" user="root"/>
<writeHost host="hostS2" url="192.168.33.12:3306" password="123456" user="root"/>
<writeHost host="hostS3" url="192.168.33.13:3306" password="123456" user="root"/>
配置方式二
<writeHost host="hostM1" url="192.168.33.11:3306" password="123456" user="root">
<readHost host="hostS2" url="192.168.33.12:3306" password="123456" user="root"/>
<readHost host="hostS3" url="192.168.33.13:3306" password="123456" user="root"/>
</writeHost>
在hostM1、hostS1、hostS2均正常工作时,写均在hostM1上。读会有如下区别:
balance | 方式一 | 方式二 |
---|---|---|
0 | 读–>hostM1 | 读–>hostM1 |
1 | 读–>hostS2 or hostS3 | 读–>hostS2 or hostS3 |
2 | 读–>hostM1 or hostS2 or hostS3 | 读–>hostM1 or hostS2 or hostS3 |
3 | 读–>hostM1 | 读–>hostS2 or hostS3 |
在hostM1失去心跳,宕机时,两种配置方式的读写会有如下区别:
balance | 方式一 | 方式二 | ||||
switchType=-1 | switchType=0或1 | switchType=-1 | switchType=0或1 | |||
0 | 读–>X 写–>X | 读–>hostS2 写–>hostS2 | 读–>X 写–>X | |||
1 | 读–>hostS2 写–>X | 读–>hostS2 写–>hostS2 | ||||
2 | 读–>hostS2 写–>X | 读–>hostS2 写–>hostS2 | ||||
3 | 读–>X 写–>X | 读–>hostS2 写–>hostS2 |
主从切换记录文件 cat conf/dnindex.properties
#update
#Mon Oct 23 18:44:40 CST 2017
dh1=0
- 注意
* 当发生主从切换后,要查看集群内所有mycat的此文件是否一致。
* 当主挂了以后,备接替主以后,再加入集群的DB是从的角色。
* 主从切换后可能会存在数据的丢失。
* mycat的主从切换,并不是mysql的主从切换,mysql的主从设置并不会改变。