40. mycat读写分离配置¶
1.前提¶
#1.mysql5.7多实例
192.168.178.161
192.168.178.162
#2.mycat
2. 环境准备¶
#161
192.168.178.161:3307 #主
192.168.178.161:3309 #从
#162
192.168.178.162:3317 #standby主
192.168.178.162:3319 #standby从
3. 创建读写分离及高可用配置文件¶
cd /application/mycat/conf
mv schema.xml schema.xml.bak.rw
vim 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">
</schema>
<dataNode name="dn1" dataHost="localhost1" database="world" />
<dataHost name="localhost1" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="native" switchType="1">
<heartbeat>select user()</heartbeat>
<writeHost host="db1" url="192.168.178.161:3307" user="root" password="123">
<readHost host="db2" url="192.168.178.161:3309" user="root" password="123" />
</writeHost>
<writeHost host="db3" url="192.168.178.162:3317" user="root" password="123">
<readHost host="db4" url="192.168.178.162:3319" user="root" password="123" />
</writeHost>
</dataHost>
</mycat:schema>
[root@161 ~]# cd /application/mycat/conf
[root@161 conf]# mv schema.xml schema.xml.bak.rw
[root@161 conf]# vim schema.xml
4. 重启mycat¶
mycat restart
[root@161 conf]# mycat restart
Stopping Mycat-server...
Stopped Mycat-server.
Starting Mycat-server...
[root@161 conf]# netstat -tunlp|grep java
tcp 0 0 127.0.0.1:32000 0.0.0.0:* LISTEN 16152/java
tcp6 0 0 :::9066 :::* LISTEN 16152/java
tcp6 0 0 :::35370 :::* LISTEN 16152/java
tcp6 0 0 :::43311 :::* LISTEN 16152/java
tcp6 0 0 :::1984 :::* LISTEN 16152/java
tcp6 0 0 :::8066 :::* LISTEN 16152/java
5. 添加主机名与IP解析(已有则忽略)¶
#161
vim /etc/hosts
192.168.178.161 161
#162
vim /etc/hosts
192.168.178.162 162
6. 登录mycat¶
mysql -uroot -p123456 -h 127.0.0.1 -P 8066
#161
[root@161 conf]# mysql -uroot -p123456 -h 127.0.0.1 -P 8066
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 1
Server version: 5.6.29-mycat-1.6.7.5-release-20200422133810 MyCat Server (OpenCloudDB)
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>
7. 读测试¶
show variables like 'server_id';
show variables like 'server_id';
show variables like 'server_id';
show variables like 'server_id';
show variables like 'server_id';
show variables like 'server_id';
#162,第一次执行失败，发现162没有启动mysqld
[root@162 ~]# systemctl start mysqld3317
[root@162 ~]# systemctl start mysqld3318
[root@162 ~]# systemctl start mysqld3319
[root@162 ~]# systemctl start mysqld3320
[root@162 ~]# netstat -tunlp
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name
tcp 0 0 0.0.0.0:22 0.0.0.0:* LISTEN 7037/sshd
tcp6 0 0 :::3317 :::* LISTEN 7683/mysqld
tcp6 0 0 :::3318 :::* LISTEN 7730/mysqld
tcp6 0 0 :::22 :::* LISTEN 7037/sshd
tcp6 0 0 :::3319 :::* LISTEN 7783/mysqld
tcp6 0 0 :::3320 :::* LISTEN 7836/mysqld
[root@162 ~]#
#再次执行测试，依旧失败。。。
mysql> show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 9 |
+---------------+-------+
1 row in set (0.00 sec)
mysql> show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 9 |
+---------------+-------+
1 row in set (0.00 sec)
mysql> show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 9 |
+---------------+-------+
1 row in set (0.00 sec)
#查找原因，发现3317上没有创建root：123用户
[root@162 ~]# mysql -S /data/3317/mysql.sock
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.20-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> select user,host from mysql.user;
+---------------+-----------+
| user | host |
+---------------+-----------+
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
+---------------+-----------+
3 rows in set (0.00 sec)
mysql>
#原来是因为没有搭建mycat主从复制状态导致的。。。
#编写38.1主从复制文档，再次测试
[root@161 conf]# mysql -uroot -p123456 -h 127.0.0.1 -P 8066
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 2
Server version: 5.6.29-mycat-1.6.7.5-release-20200422133810 MyCat Server (OpenCloudDB)
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> show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 17 |
+---------------+-------+
1 row in set (0.00 sec)
mysql> show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 19 |
+---------------+-------+
1 row in set (0.01 sec)
mysql> show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 17 |
+---------------+-------+
1 row in set (0.00 sec)
mysql> show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 19 |
+---------------+-------+
1 row in set (0.00 sec)
mysql> show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 17 |
+---------------+-------+
1 row in set (0.00 sec)
mysql> show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 19 |
+---------------+-------+
1 row in set (0.00 sec)
mysql> show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 9 |
+---------------+-------+
1 row in set (0.01 sec)
mysql> show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 17 |
+---------------+-------+
1 row in set (0.00 sec)
mysql> show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 9 |
+---------------+-------+
1 row in set (0.01 sec)
mysql> show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 17 |
+---------------+-------+
1 row in set (0.01 sec)
mysql> show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 9 |
+---------------+-------+
1 row in set (0.01 sec)
mysql> show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 17 |
+---------------+-------+
1 row in set (0.00 sec)
mysql> show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 9 |
+---------------+-------+
1 row in set (0.01 sec)
mysql> show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 17 |
+---------------+-------+
1 row in set (0.00 sec)
mysql> show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 19 |
+---------------+-------+
1 row in set (0.01 sec)
mysql> show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 9 |
+---------------+-------+
1 row in set (0.01 sec)
mysql>
#成功！！！
8. 写测试¶
begin;
show variables like 'server_id';
show variables like 'server_id';
show variables like 'server_id';
show variables like 'server_id';
commit;
show variables like 'server_id';
show variables like 'server_id';
show variables like 'server_id';
show variables like 'server_id';
show variables like 'server_id';
show variables like 'server_id';
#161
mysql> begin;
Query OK, 0 rows affected (0.01 sec)
mysql> show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 7 |
+---------------+-------+
1 row in set (0.02 sec)
mysql> show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 7 |
+---------------+-------+
1 row in set (0.01 sec)
mysql> show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 7 |
+---------------+-------+
1 row in set (0.00 sec)
mysql> show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 7 |
+---------------+-------+
1 row in set (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 19 |
+---------------+-------+
1 row in set (0.01 sec)
mysql> show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 9 |
+---------------+-------+
1 row in set (0.00 sec)
mysql>
9. 模拟主库宕机¶
#161
systemctl stop mysqld3307
[root@161 conf]# systemctl stop mysqld3307
[root@161 conf]#
[root@161 conf]# netstat -tunlp |grep 33
tcp6 0 0 :::3308 :::* LISTEN 7782/mysqld
tcp6 0 0 :::3309 :::* LISTEN 7789/mysqld
tcp6 0 0 :::3310 :::* LISTEN 7808/mysqld
tcp6 0 0 :::43311 :::* LISTEN 16152/java
[root@161 conf]#
10. 再次读测试¶
show variables like 'server_id';
show variables like 'server_id';
show variables like 'server_id';
show variables like 'server_id';
show variables like 'server_id';
show variables like 'server_id';
#161
[root@161 conf]# mysql -uroot -p123456 -h 127.0.0.1 -P 8066
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.7.5-release-20200422133810 MyCat Server (OpenCloudDB)
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> show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 19 |
+---------------+-------+
1 row in set (0.01 sec)
mysql> show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 19 |
+---------------+-------+
1 row in set (0.00 sec)
mysql> show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 19 |
+---------------+-------+
1 row in set (0.00 sec)
mysql> show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 19 |
+---------------+-------+
1 row in set (0.00 sec)
11. 再次写测试¶
begin;
show variables like 'server_id';
show variables like 'server_id';
show variables like 'server_id';
show variables like 'server_id';
commit;
show variables like 'server_id';
show variables like 'server_id';
show variables like 'server_id';
show variables like 'server_id';
show variables like 'server_id';
show variables like 'server_id';
#161
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 17 |
+---------------+-------+
1 row in set (0.00 sec)
mysql> show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 17 |
+---------------+-------+
1 row in set (0.00 sec)
mysql> show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 17 |
+---------------+-------+
1 row in set (0.00 sec)
mysql> show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 17 |
+---------------+-------+
1 row in set (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 19 |
+---------------+-------+
1 row in set (0.00 sec)
mysql> show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 19 |
+---------------+-------+
1 row in set (0.01 sec)
mysql>
12. 启动3307数据库¶
systemctl start mysqld3307
#161
[root@161 conf]# systemctl start mysqld3307
[root@161 conf]# netstat -tunlp |grep 33
tcp6 0 0 :::3307 :::* LISTEN 25709/mysqld
tcp6 0 0 :::3308 :::* LISTEN 7782/mysqld
tcp6 0 0 :::3309 :::* LISTEN 7789/mysqld
tcp6 0 0 :::3310 :::* LISTEN 7808/mysqld
tcp6 0 0 :::43311 :::* LISTEN 16152/java
13. 再次读测试¶
[root@161 conf]# mysql -uroot -p123456 -h 127.0.0.1 -P 8066
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 4
Server version: 5.6.29-mycat-1.6.7.5-release-20200422133810 MyCat Server (OpenCloudDB)
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> show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 9 |
+---------------+-------+
1 row in set (0.01 sec)
mysql> show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 19 |
+---------------+-------+
1 row in set (0.00 sec)
mysql> show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 19 |
+---------------+-------+
1 row in set (0.00 sec)
mysql> show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 19 |
+---------------+-------+
1 row in set (0.01 sec)
mysql> show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 9 |
+---------------+-------+
1 row in set (0.00 sec)
mysql> show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 19 |
+---------------+-------+
1 row in set (0.00 sec)
mysql> show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 7 |
+---------------+-------+
1 row in set (0.00 sec)
14. 再次写测试¶
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 17 |
+---------------+-------+
1 row in set (0.00 sec)
mysql> show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 17 |
+---------------+-------+
1 row in set (0.01 sec)
mysql> show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 17 |
+---------------+-------+
1 row in set (0.00 sec)
mysql> show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 17 |
+---------------+-------+
1 row in set (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 9 |
+---------------+-------+
1 row in set (0.00 sec)
mysql> show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 9 |
+---------------+-------+
1 row in set (0.02 sec)
mysql> show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 9 |
+---------------+-------+
1 row in set (0.01 sec)
mysql> show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 9 |
+---------------+-------+
1 row in set (0.00 sec)
mysql> show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 7 |
+---------------+-------+
1 row in set (0.00 sec)
mysql> show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 7 |
+---------------+-------+
1 row in set (0.00 sec)
mysql>
14. 总结¶
1.原主库宕机，standby主库成为新的主库
2.原主库恢复后，成为新的standby主库
最后更新:
2022-02-20 08:44:07