目录
一、MHA+ProxySQL架构二、快速搭建MHA环境2.1 下载MHA镜像2.2 编辑yml文件，创建MHA相关容器2.3 安装docker-compose软件（若已安装，可忽略）2.4 创建MHA容器2.5 主库131添加VIP三、配置ProxySQL环境3.1 申请ProxySQL主机并安装ProxySQL3.2 添加远程登录用户3.3 开启ProxySQL的web监控功能3.4 配置被监控的数据库3.4.1 向ProxySQL插入被监控数据库3.4.2 在所有被监控MySQL服务器上创建监控帐户3.4.3 在所有被监控MySQL服务器上创建对外访问账户3.4.4 配置监控3.4.5 配置MySQL主机组3.4.6 配置读写分离策略四、测试读负载均衡五、测试读写分离六、故障切换七、界面监控结果（ProxySQL本身+PMM监控）7.1 ProxySQL自带监控7.2 PMM监控复制
一、MHA+ProxySQL架构
之前发过一篇MHA的文章，介绍了MHA相关的知识和功能测试，连接为：【DB宝19】在Docker中使用MySQL高可用之MHA 。今天这一篇给大家分享一下“MHA+中间件ProxySQL”来实现读写分离+负载均衡的相关知识。
我们都知道，MHA（Master High Availability Manager and tools for MySQL）目前在MySQL高可用方面是一个相对成熟的解决方案，是一套作为MySQL高可用性环境下故障切换和主从提升的高可用软件。它的架构是要求一个MySQL复制集群必须最少有3台数据库服务器，一主二从，即一台充当Master，一台充当备用Master，另一台充当从库。但是，如果不连接任何外部的数据库中间件，那么就会导致所有的业务压力流向主库，从而造成主库压力过大，而2个从库除了本身的IO和SQL线程外，无任何业务压力，会严重造成资源的浪费。因此，我们可以把MHA和ProxySQL结合使用来实现读写分离和负载均衡。所有的业务通过中间件ProxySQL后，会被分配到不同的MySQL机器上。从而，前端的写操作会流向主库，而读操作会被负载均衡的转发到2个从库上。
MHA+ProxySQL架构如下图所示：
二、快速搭建MHA环境
2.1 下载MHA镜像
小麦苗的Docker Hub的地址：https://hub.docker.com/u/lhrbest
1-- 下载镜像
2docker pull registry.cn-hangzhou.aliyuncs.com/lhrbest/mha-lhr-master1-ip131
3docker pull registry.cn-hangzhou.aliyuncs.com/lhrbest/mha-lhr-slave1-ip132
4docker pull registry.cn-hangzhou.aliyuncs.com/lhrbest/mha-lhr-slave2-ip133
5docker pull registry.cn-hangzhou.aliyuncs.com/lhrbest/mha-lhr-monitor-ip134
6
7-- 重命名镜像
8docker tag registry.cn-hangzhou.aliyuncs.com/lhrbest/mha-lhr-master1-ip131 lhrbest/mha-lhr-master1-ip131
9docker tag registry.cn-hangzhou.aliyuncs.com/lhrbest/mha-lhr-slave1-ip132 lhrbest/mha-lhr-slave1-ip132
10docker tag registry.cn-hangzhou.aliyuncs.com/lhrbest/mha-lhr-slave2-ip133 lhrbest/mha-lhr-slave2-ip133
11docker tag registry.cn-hangzhou.aliyuncs.com/lhrbest/mha-lhr-monitor-ip134 lhrbest/mha-lhr-monitor-ip134复制
一共4个镜像，3个MHA Node，一个MHA Manager，压缩包大概3G，下载完成后：
1[root@lhrdocker ~]# docker images | grep mha
2registry.cn-hangzhou.aliyuncs.com/lhrbest/mha-lhr-monitor-ip134 latest 7d29597dc997 14 hours ago 1.53GB
3registry.cn-hangzhou.aliyuncs.com/lhrbest/mha-lhr-slave2-ip133 latest d3717794e93a 40 hours ago 4.56GB
4registry.cn-hangzhou.aliyuncs.com/lhrbest/mha-lhr-slave1-ip132 latest f62ee813e487 40 hours ago 4.56GB
5registry.cn-hangzhou.aliyuncs.com/lhrbest/mha-lhr-master1-ip131 latest ae7be48d83dc 40 hours ago 4.56GB复制
2.2 编辑yml文件，创建MHA相关容器
编辑yml文件，使用docker-compose来创建MHA相关容器，注意docker-compose.yml文件的格式，对空格、缩进、对齐都有严格要求：
1# 创建存放yml文件的路径
2mkdir -p /root/mha
3
4# 编辑文件/root/mha/docker-compose.yml
5cat > /root/mha/docker-compose.yml <<"EOF"
6version: '3.8'
7
8services:
9 MHA-LHR-Master1-ip131:
10 container_name: "MHA-LHR-Master1-ip131"
11 restart: "always"
12 hostname: MHA-LHR-Master1-ip131
13 privileged: true
14 image: lhrbest/mha-lhr-master1-ip131
15 ports:
16 - "33131:3306"
17 - "2201:22"
18 networks:
19 mhalhr:
20 ipv4_address: 192.168.68.131
21
22 MHA-LHR-Slave1-ip132:
23 container_name: "MHA-LHR-Slave1-ip132"
24 restart: "always"
25 hostname: MHA-LHR-Slave1-ip132
26 privileged: true
27 image: lhrbest/mha-lhr-slave1-ip132
28 ports:
29 - "33132:3306"
30 - "2202:22"
31 networks:
32 mhalhr:
33 ipv4_address: 192.168.68.132
34
35 MHA-LHR-Slave2-ip133:
36 container_name: "MHA-LHR-Slave2-ip133"
37 restart: "always"
38 hostname: MHA-LHR-Slave2-ip133
39 privileged: true
40 image: lhrbest/mha-lhr-slave2-ip133
41 ports:
42 - "33133:3306"
43 - "2203:22"
44 networks:
45 mhalhr:
46 ipv4_address: 192.168.68.133
47
48 MHA-LHR-Monitor-ip134:
49 container_name: "MHA-LHR-Monitor-ip134"
50 restart: "always"
51 hostname: MHA-LHR-Monitor-ip134
52 privileged: true
53 image: lhrbest/mha-lhr-monitor-ip134
54 ports:
55 - "33134:3306"
56 - "2204:22"
57 networks:
58 mhalhr:
59 ipv4_address: 192.168.68.134
60
61networks:
62 mhalhr:
63 name: mhalhr
64 ipam:
65 config:
66 - subnet: "192.168.68.0/16"
67
68EOF复制
2.3 安装docker-compose软件（若已安装，可忽略）
安装 Docker Compose官方文档：https://docs.docker.com/compose/
编辑docker-compose.yml文件官方文档：https://docs.docker.com/compose/compose-file/
1[root@lhrdocker ~]# curl --insecure -L https://github.com/docker/compose/releases/download/1.28.4/docker-compose-Linux-x86_64 -o /usr/local/bin/docker-compose
2 % Total % Received % Xferd Average Speed Time Time Time Current
3 Dload Upload Total Spent Left Speed
4100 638 100 638 0 0 530 0 0:00:01 0:00:01 --:--:-- 531
5100 11.6M 100 11.6M 0 0 1994k 0 0:00:06 0:00:06 --:--:-- 2943k
6[root@lhrdocker ~]# chmod +x /usr/local/bin/docker-compose
7[root@lhrdocker ~]# docker-compose -v
8docker-compose version 1.28.4, build cabd5cfb复制
2.4 创建MHA容器
1# 启动mha环境的容器，一定要进入文件夹/root/mha/后再操作
2-- docker rm -f MHA-LHR-Master1-ip131 MHA-LHR-Slave1-ip132 MHA-LHR-Slave2-ip133 MHA-LHR-Monitor-ip134
3[root@lhrdocker ~]# cd /root/mha/
4[root@lhrdocker mha]#
5[root@lhrdocker mha]# docker-compose up -d
6Creating network "mhalhr" with the default driver
7Creating MHA-LHR-Monitor-ip134 ... done
8Creating MHA-LHR-Slave2-ip133 ... done
9Creating MHA-LHR-Master1-ip131 ... done
10Creating MHA-LHR-Slave1-ip132 ... done
11[root@docker35 ~]# docker ps | grep "mha\|COMMAND"
12CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
132978361198b7 lhrbest/mha-lhr-master1-ip131 "/usr/sbin/init" 2 minutes ago Up 2 minutes 16500-16599/tcp, 0.0.0.0:2201->22/tcp, 0.0.0.0:33131->3306/tcp MHA-LHR-Master1-ip131
14a64e2e86589c lhrbest/mha-lhr-slave1-ip132 "/usr/sbin/init" 2 minutes ago Up 2 minutes 16500-16599/tcp, 0.0.0.0:2202->22/tcp, 0.0.0.0:33132->3306/tcp MHA-LHR-Slave1-ip132
15d7d6ce34800b lhrbest/mha-lhr-monitor-ip134 "/usr/sbin/init" 2 minutes ago Up 2 minutes 0.0.0.0:2204->22/tcp, 0.0.0.0:33134->3306/tcp MHA-LHR-Monitor-ip134
16dacd22edb2f8 lhrbest/mha-lhr-slave2-ip133 "/usr/sbin/init" 2 minutes ago Up 2 minutes 16500-16599/tcp, 0.0.0.0:2203->22/tcp, 0.0.0.0:33133->3306/tcp MHA-LHR-Slave2-ip133复制
2.5 主库131添加VIP
1# 进入主库131
2docker exec -it MHA-LHR-Master1-ip131 bash
3
4# 添加VIP135
5/sbin/ifconfig eth0:1 192.168.68.135/24
6ifconfig
7
8# 如果删除的话
9ip addr del 192.168.68.135/24 dev eth1复制
添加完成后：
1[root@MHA-LHR-Master1-ip131 /]# ifconfig
2eth0: flags=4163<UP,BROADCAST,RUNNING,MULTICAST> mtu 1500
3 inet 192.168.68.131 netmask 255.255.0.0 broadcast 192.168.255.255
4 ether 02:42:c0:a8:44:83 txqueuelen 0 (Ethernet)
5 RX packets 220 bytes 15883 (15.5 KiB)
6 RX errors 0 dropped 0 overruns 0 frame 0
7 TX packets 189 bytes 17524 (17.1 KiB)
8 TX errors 0 dropped 0 overruns 0 carrier 0 collisions 0
9
10eth0:1: flags=4163<UP,BROADCAST,RUNNING,MULTICAST> mtu 1500
11 inet 192.168.68.135 netmask 255.255.255.0 broadcast 192.168.68.255
12 ether 02:42:c0:a8:44:83 txqueuelen 0
13lo: flags=73<UP,LOOPBACK,RUNNING> mtu 65536
14 inet 127.0.0.1 netmask 255.0.0.0
15 loop txqueuelen 1000 (Local Loopback)
16 RX packets 5 bytes 400 (400.0 B)
17 RX errors 0 dropped 0 overruns 0 frame 0
18 TX packets 5 bytes 400 (400.0 B)
19 TX errors 0 dropped 0 overruns 0 carrier 0 collisions 0
20
21# 管理节点已经可以ping通VIP了
22[root@MHA-LHR-Monitor-ip134 /]# ping 192.168.68.135
23PING 192.168.68.135 (192.168.68.135) 56(84) bytes of data.
2464 bytes from 192.168.68.135: icmp_seq=1 ttl=64 time=0.172 ms
2564 bytes from 192.168.68.135: icmp_seq=2 ttl=64 time=0.076 ms
26^C
27--- 192.168.68.135 ping statistics ---
282 packets transmitted, 2 received, 0% packet loss, time 1000ms
29rtt min/avg/max/mdev = 0.076/0.124/0.172/0.048 ms复制
到这一步就可以验证主从复制是否正确，若正确，则可以直接测试MHA了。
1mysql -uroot -plhr -h192.168.68.131 -P3306
2show slave hosts;
3mysql> show slave hosts;
4+-----------+----------------+------+-----------+--------------------------------------+
5| Server_id | Host | Port | Master_id | Slave_UUID |
6+-----------+----------------+------+-----------+--------------------------------------+
7| 573306133 | 192.168.68.133 | 3306 | 573306131 | d391ce7e-aec3-11ea-94cd-0242c0a84485 |
8| 573306132 | 192.168.68.132 | 3306 | 573306131 | d24a77d1-aec3-11ea-9399-0242c0a84484 |
9+-----------+----------------+------+-----------+--------------------------------------+
102 rows in set (0.00 sec)复制
三、配置ProxySQL环境
3.1 申请ProxySQL主机并安装ProxySQL
1docker rm -f MHA-LHR-ProxySQL-ip136
2docker run -d --name MHA-LHR-ProxySQL-ip136 -h MHA-LHR-ProxySQL-ip136 \
3 -v /sys/fs/cgroup:/sys/fs/cgroup \
4 --network mhalhr --ip 192.168.68.136 \
5 -p 26032:6032 -p 26033:6033 -p 26080:6080 \
6 --privileged=true lhrbest/lhrcentos76:8.0 \
7 /usr/sbin/init
8
9docker network connect bridge MHA-LHR-ProxySQL-ip136
10docker restart MHA-LHR-ProxySQL-ip136
11
12docker cp proxysql2-2.0.15-1.1.el7.x86_64.rpm MHA-LHR-ProxySQL-ip136:/
13docker exec -it MHA-LHR-ProxySQL-ip136 bash
14rpm -ivh proxysql2-2.0.15-1.1.el7.x86_64.rpm
15
16
17systemctl start proxysql
18systemctl status proxysql复制
3.2 添加远程登录用户
1-- 添加远程登录用户
2mysql -uadmin -padmin -h127.0.0.1 -P6032
3select @@admin-admin_credentials;
4set admin-admin_credentials='admin:admin;root:lhr';
5select @@admin-admin_credentials;
6load admin variables to runtime;
7save admin variables to disk;
8
9-- 远程登录
10mysql -uroot -plhr -h192.168.66.35 -P26032复制
执行过程：
1-- ProxySQL本地登录
2[root@MHA-LHR-ProxySQL-ip136 /]# mysql -uadmin -padmin -h127.0.0.1 -P6032
3mysql: [Warning] Using a password on the command line interface can be insecure.
4Welcome to the MySQL monitor. Commands end with ; or \g.
5Your MySQL connection id is 162
6Server version: 5.5.30 (ProxySQL Admin Module)
7
8Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
9
10Oracle is a registered trademark of Oracle Corporation and/or its
11affiliates. Other names may be trademarks of their respective
12owners.
13
14Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
15
16mysql> select @@admin-admin_credentials;
17+---------------------------+
18| @@admin-admin_credentials |
19+---------------------------+
20| admin:admin;lhr:lhr |
21+---------------------------+
221 row in set (0.05 sec)
23
24mysql> set admin-admin_credentials='admin:admin;root:lhr';
25Query OK, 1 row affected (0.00 sec)
26
27mysql> select @@admin-admin_credentials;
28+---------------------------+
29| @@admin-admin_credentials |
30+---------------------------+
31| admin:admin;root:lhr |
32+---------------------------+
331 row in set (0.00 sec)
34
35mysql> load admin variables to runtime;
36Query OK, 0 rows affected (0.00 sec)
37
38mysql> save admin variables to disk;
39Query OK, 35 rows affected (0.13 sec)
40
41mysql>
42
43-- 远程登录
44C:\Users\lhrxxt>mysql -uroot -plhr -h192.168.66.35 -P26032
45mysql: [Warning] Using a password on the command line interface can be insecure.
46ERROR 1045 (28000): ProxySQL Error: Access denied for user 'root'@'172.17.0.1' (using password: YES)
47
48C:\Users\lhrxxt>mysql -uroot -plhr -h192.168.66.35 -P26032
49mysql: [Warning] Using a password on the command line interface can be insecure.
50Welcome to the MySQL monitor. Commands end with ; or \g.
51Your MySQL connection id is 163
52Server version: 5.5.30 (ProxySQL Admin Module)
53
54Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
55
56Oracle is a registered trademark of Oracle Corporation and/or its
57affiliates. Other names may be trademarks of their respective
58owners.
59
60Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
61
62MySQL [(none)]> show databases;
63+-----+---------------+-------------------------------------+
64| seq | name | file |
65+-----+---------------+-------------------------------------+
66| 0 | main | |
67| 2 | disk | /var/lib/proxysql/proxysql.db |
68| 3 | stats | |
69| 4 | monitor | |
70| 5 | stats_history | /var/lib/proxysql/proxysql_stats.db |
71+-----+---------------+-------------------------------------+
725 rows in set (0.05 sec)复制
3.3 开启ProxySQL的web监控功能
1-- 开启web监控功能
2SET admin-web_enabled='true';
3LOAD ADMIN VARIABLES TO RUNTIME;
4SAVE ADMIN VARIABLES TO DISK;
5select * from global_variables where variable_name LIKE 'admin-web_enabled';
6select @@admin-web_enabled;
7
8lsof -i:6080
9
10-- 浏览器访问
11https://192.168.66.35:26080
12用户名和密码：stats:stats复制
3.4 配置被监控的数据库
3.4.1 向ProxySQL插入被监控数据库
1-- 1、向ProxySQL插入被监控数据库
2select * from mysql_servers;
3insert into main.mysql_servers(hostgroup_id,hostname,port) values(10,'192.168.68.131',3306);
4insert into main.mysql_servers(hostgroup_id,hostname,port) values(10,'192.168.68.132',3306);
5insert into main.mysql_servers(hostgroup_id,hostname,port) values(10,'192.168.68.133',3306);
6load mysql servers to runtime;
7save mysql servers to disk;
8select * from mysql_servers;
9MySQL [(none)]> select * from mysql_servers;
10+--------------+----------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
11| hostgroup_id | hostname | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
12+--------------+----------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
13| 10 | 192.168.68.131 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
14| 10 | 192.168.68.132 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
15| 10 | 192.168.68.133 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
16+--------------+----------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
173 rows in set (0.07 sec)复制
3.4.2 在所有被监控MySQL服务器上创建监控帐户
1-- 2、在所有被监控MySQL服务器上创建帐户，注意：新版本中，这里的密码必须为monitor，可参考配置文件/etc/proxysql.cnf
2mysql -uroot -plhr -h192.168.66.35 -P33131
3create user 'monitor'@'%' IDENTIFIED BY 'monitor';
4GRANT all privileges ON *.* TO 'monitor'@'%' with grant option;
5select user,host from mysql.user;
6
7mysql> select user,host from mysql.user;
8+---------------+--------------+
9| user | host |
10+---------------+--------------+
11| mha | % |
12| monitor | % |
13| repl | % |
14| root | % |
15| mysql.session | localhost |
16| mysql.sys | localhost |
17| root | localhost |
18+---------------+--------------+
197 rows in set (0.00 sec)复制
3.4.3 在所有被监控MySQL服务器上创建对外访问账户
1-- 3、 在所有被监控MySQL服务器上创建对外访问账户：
2create user 'wr'@'%' IDENTIFIED BY 'lhr';
3GRANT all privileges ON *.* TO 'wr'@'%' with grant option;
4
5-- 配置到ProxySQL中
6insert into mysql_users(username,password,default_hostgroup) values('wr','lhr',10);
7update mysql_users set transaction_persistent=1 where username='wr';
8load mysql users to runtime;
9save mysql users to disk;
10select * from mysql_users;
11
12MySQL [(none)]> select * from mysql_users;
13+----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+---------+
14| username | password | active | use_ssl | default_hostgroup | default_schema | schema_locked | transaction_persistent | fast_forward | backend | frontend | max_connections | comment |
15+----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+---------+
16| wr | lhr | 1 | 0 | 10 | NULL | 0 | 1 | 0 | 1 | 1 | 10000 | |
17+----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+---------+
181 row in set (0.05 sec)复制
3.4.4 配置监控
1-- 4、在ProxySQL端执行下列SQL语句：
2set mysql-monitor_username='monitor';
3set mysql-monitor_password='monitor';
4load mysql servers to runtime;
5save mysql servers to disk;
6select * from global_variables where variable_name in('mysql-monitor_username','mysql-monitor_password');
7+------------------------+----------------+
8| variable_name | variable_value |
9+------------------------+----------------+
10| mysql-monitor_password | monitor |
11| mysql-monitor_username | monitor |
12+------------------------+----------------+
132 rows in set (0.05 sec)
14
15
16-- 检查连接到MySQL的日志
17select * from monitor.mysql_server_ping_log order by time_start_us desc limit 6;
18select * from monitor.mysql_server_connect_log order by time_start_us desc limit 6;
19MySQL [(none)]> select * from monitor.mysql_server_ping_log order by time_start_us desc limit 6;
20+----------------+------+------------------+----------------------+------------+
21| hostname | port | time_start_us | ping_success_time_us | ping_error |
22+----------------+------+------------------+----------------------+------------+
23| 192.168.68.132 | 3306 | 1614050308827202 | 252 | NULL |
24| 192.168.68.133 | 3306 | 1614050308716530 | 370 | NULL |
25| 192.168.68.131 | 3306 | 1614050308605853 | 542 | NULL |
26| 192.168.68.131 | 3306 | 1614050298778908 | 334 | NULL |
27| 192.168.68.133 | 3306 | 1614050298690947 | 297 | NULL |
28| 192.168.68.132 | 3306 | 1614050298605725 | 344 | NULL |
29+----------------+------+------------------+----------------------+------------+
306 rows in set (0.06 sec)
31
32MySQL [(none)]> select * from monitor.mysql_server_connect_log order by time_start_us desc limit 6;
33+----------------+------+------------------+-------------------------+---------------+
34| hostname | port | time_start_us | connect_success_time_us | connect_error |
35+----------------+------+------------------+-------------------------+---------------+
36| 192.168.68.131 | 3306 | 1614050285481316 | 1173 | NULL |
37| 192.168.68.133 | 3306 | 1614050284894846 | 1008 | NULL |
38| 192.168.68.132 | 3306 | 1614050284309124 | 970 | NULL |
39| 192.168.68.131 | 3306 | 1614050225194575 | 1108 | NULL |
40| 192.168.68.133 | 3306 | 1614050224751771 | 987 | NULL |
41| 192.168.68.132 | 3306 | 1614050224309026 | 1294 | NULL |
42+----------------+------+------------------+-------------------------+---------------+
436 rows in set (0.05 sec)复制
3.4.5 配置MySQL主机组
1-- 5、实验使用10作为写入组，20作为读取组。
2show create table mysql_replication_hostgroups\G;
3writer_hostgroup 写入组的编号
4reader_hostgroup 读取组的编号
5
6
7-- 注意：需要配置从库的read_only=1
8show variables like 'read_only';
9set global read_only=1;
10
11insert into mysql_replication_hostgroups(writer_hostgroup,reader_hostgroup,comment) values(10,20,'proxy');
12load mysql servers to runtime;
13save mysql servers to disk;
14select * from mysql_replication_hostgroups;
15select * from mysql_server_read_only_log order by time_start_us desc limit 3;
16select * from mysql_servers;
17
18MySQL [(none)]> select * from mysql_replication_hostgroups;
19+------------------+------------------+------------+---------+
20| writer_hostgroup | reader_hostgroup | check_type | comment |
21+------------------+------------------+------------+---------+
22| 10 | 20 | read_only | proxy |
23+------------------+------------------+------------+---------+
241 row in set (0.05 sec)
25
26MySQL [(none)]> select * from mysql_server_read_only_log order by time_start_us desc limit 3;
27+----------------+------+------------------+-----------------+-----------+-------+
28| hostname | port | time_start_us | success_time_us | read_only | error |
29+----------------+------+------------------+-----------------+-----------+-------+
30| 192.168.68.133 | 3306 | 1614050367153351 | 611 | 1 | NULL |
31| 192.168.68.131 | 3306 | 1614050367136396 | 490 | 0 | NULL |
32| 192.168.68.132 | 3306 | 1614050367119511 | 531 | 1 | NULL |
33+----------------+------+------------------+-----------------+-----------+-------+
343 rows in set (0.05 sec)
35
36MySQL [(none)]> select * from mysql_servers;
37+--------------+----------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
38| hostgroup_id | hostname | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
39+--------------+----------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
40| 10 | 192.168.68.131 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
41| 20 | 192.168.68.132 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
42| 20 | 192.168.68.133 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
43+--------------+----------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
443 rows in set (0.05 sec)复制
注意，此时mysql_servers表中的hostgroup_id值已发生变化。
3.4.6 配置读写分离策略
1-- 6、配置读写分离策略
2insert into mysql_query_rules(active,match_pattern,destination_hostgroup,apply) values(1,'^SELECT.*FOR UPDATE$',10,1);
3insert into mysql_query_rules(active,match_pattern,destination_hostgroup,apply) values(1,'^SELECT',20,1);
4LOAD MYSQL QUERY RULES TO RUNTIME;
5SAVE MYSQL QUERY RULES TO DISK;
6
7-- 配置查询select的请求转发到hostgroup_id=2组上（读组）
8-- 针对select * from table_name for update这样的修改语句，我们是需要将请求转到写组，也就是hostgroup_id=1
9-- 对于其它没有被规则匹配的请求全部转发到默认的组（mysql_users表中default_hostgroup）
10select * from mysql_query_rules;
11select username,password,default_hostgroup from mysql_users;
12MySQL [(none)]> select * from mysql_query_rules;
13+---------+--------+----------+------------+--------+-------------+------------+------------+--------+--------------+----------------------+----------------------+--------------+---------+-----------------+-----------------------+-----------+--------------------+---------------+-----------+---------+---------+-------+-------------------+----------------+------------------+-----------+--------+-------------+-----------+---------------------+-----+-------+---------+
14| rule_id | active | username | schemaname | flagIN | client_addr | proxy_addr | proxy_port | digest | match_digest | match_pattern | negate_match_pattern | re_modifiers | flagOUT | replace_pattern | destination_hostgroup | cache_ttl | cache_empty_result | cache_timeout | reconnect | timeout | retries | delay | next_query_flagIN | mirror_flagOUT | mirror_hostgroup | error_msg | OK_msg | sticky_conn | multiplex | gtid_from_hostgroup | log | apply | comment |
15+---------+--------+----------+------------+--------+-------------+------------+------------+--------+--------------+----------------------+----------------------+--------------+---------+-----------------+-----------------------+-----------+--------------------+---------------+-----------+---------+---------+-------+-------------------+----------------+------------------+-----------+--------+-------------+-----------+---------------------+-----+-------+---------+
16| 1 | 1 | NULL | NULL | 0 | NULL | NULL | NULL | NULL | NULL | ^SELECT.*FOR UPDATE$ | 0 | CASELESS | NULL | NULL | 10 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 1 | NULL |
17| 2 | 1 | NULL | NULL | 0 | NULL | NULL | NULL | NULL | NULL | ^SELECT | 0 | CASELESS | NULL | NULL | 20 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 1 | NULL |
18+---------+--------+----------+------------+--------+-------------+------------+------------+--------+--------------+----------------------+----------------------+--------------+---------+-----------------+-----------------------+-----------+--------------------+---------------+-----------+---------+---------+-------+-------------------+----------------+------------------+-----------+--------+-------------+-----------+---------------------+-----+-------+---------+
192 rows in set (0.05 sec)
20
21MySQL [(none)]> select username,password,default_hostgroup from mysql_users;
22+----------+----------+-------------------+
23| username | password | default_hostgroup |
24+----------+----------+-------------------+
25| wr | lhr | 10 |
26+----------+----------+-------------------+
271 row in set (0.05 sec)复制
至此，ProxySQL读写分离和负载均衡已配置完成，接下来我们进行测试。
四、测试读负载均衡
1[root@docker35 ~]# for i in $(seq 1 10); do mysql -uwr -plhr -h192.168.68.136 -P6033 -e 'select @@server_id;'; done | egrep '[0-9]'
2mysql: [Warning] Using a password on the command line interface can be insecure.
3573306133
4mysql: [Warning] Using a password on the command line interface can be insecure.
5573306132
6mysql: [Warning] Using a password on the command line interface can be insecure.
7573306132
8mysql: [Warning] Using a password on the command line interface can be insecure.
9573306132
10mysql: [Warning] Using a password on the command line interface can be insecure.
11573306133
12mysql: [Warning] Using a password on the command line interface can be insecure.
13573306133
14mysql: [Warning] Using a password on the command line interface can be insecure.
15573306133
16mysql: [Warning] Using a password on the command line interface can be insecure.
17573306132
18mysql: [Warning] Using a password on the command line interface can be insecure.
19573306132
20mysql: [Warning] Using a password on the command line interface can be insecure.
21573306132
22
23MySQL [(none)]> SELECT * FROM stats.stats_mysql_connection_pool;
24+-----------+----------------+----------+--------+----------+----------+--------+---------+-------------+---------+-------------------+-----------------+-----------------+------------+
25| hostgroup | srv_host | srv_port | status | ConnUsed | ConnFree | ConnOK | ConnERR | MaxConnUsed | Queries | Queries_GTID_sync | Bytes_data_sent | Bytes_data_recv | Latency_us |
26+-----------+----------------+----------+--------+----------+----------+--------+---------+-------------+---------+-------------------+-----------------+-----------------+------------+
27| 10 | 192.168.68.131 | 3306 | ONLINE | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 323 |
28| 20 | 192.168.68.132 | 3306 | ONLINE | 0 | 1 | 1 | 0 | 1 | 6 | 0 | 108 | 84 | 280 |
29| 20 | 192.168.68.133 | 3306 | ONLINE | 0 | 1 | 1 | 0 | 1 | 4 | 0 | 72 | 56 | 390 |
30+-----------+----------------+----------+--------+----------+----------+--------+---------+-------------+---------+-------------------+-----------------+-----------------+------------+
313 rows in set (0.06 sec)
32-- 可以看到Queries列分配到132和133的从库上。
33
34-- 接下来使用mysqlslap来做压测
35[root@docker35 ~]#
36[root@docker35 ~]# mysqlslap -uwr -plhr -h192.168.68.136 -P6033 --create-schema=lhrmysqlslap --auto-generate-sql --auto-generate-sql-load-type=read --number-of-queries=100000
37mysqlslap: [Warning] Using a password on the command line interface can be insecure.
38Benchmark
39 Average number of seconds to run all queries: 104.757 seconds
40 Minimum number of seconds to run all queries: 104.757 seconds
41 Maximum number of seconds to run all queries: 104.757 seconds
42 Number of clients running queries: 1
43 Average number of queries per client: 100000
44
45
46MySQL [(none)]> SELECT * FROM stats_mysql_commands_counters WHERE Total_cnt;
47+-----------------+---------------+-----------+-----------+-----------+---------+---------+----------+----------+-----------+-----------+--------+--------+---------+----------+
48| Command | Total_Time_us | Total_cnt | cnt_100us | cnt_500us | cnt_1ms | cnt_5ms | cnt_10ms | cnt_50ms | cnt_100ms | cnt_500ms | cnt_1s | cnt_5s | cnt_10s | cnt_INFs |
49+-----------------+---------------+-----------+-----------+-----------+---------+---------+----------+----------+-----------+-----------+--------+--------+---------+----------+
50| CREATE_DATABASE | 90942 | 3 | 0 | 0 | 0 | 2 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 0 |
51| CREATE_TABLE | 67954 | 3 | 0 | 0 | 0 | 0 | 0 | 3 | 0 | 0 | 0 | 0 | 0 | 0 |
52| INSERT | 11337671 | 113 | 0 | 0 | 0 | 69 | 27 | 14 | 1 | 0 | 1 | 0 | 0 | 1 |
53| SELECT | 15299552 | 22293 | 263 | 1588 | 18947 | 1453 | 29 | 10 | 2 | 1 | 0 | 0 | 0 | 0 |
54| SHOW | 56308 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 0 |
55| UNKNOWN | 131355 | 2 | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 0 |
56+-----------------+---------------+-----------+-----------+-----------+---------+---------+----------+----------+-----------+-----------+--------+--------+---------+----------+
576 rows in set (0.11 sec)
58
59MySQL [(none)]> SELECT * FROM stats_mysql_commands_counters WHERE Total_cnt;
60+-----------------+---------------+-----------+-----------+-----------+---------+---------+----------+----------+-----------+-----------+--------+--------+---------+----------+
61| Command | Total_Time_us | Total_cnt | cnt_100us | cnt_500us | cnt_1ms | cnt_5ms | cnt_10ms | cnt_50ms | cnt_100ms | cnt_500ms | cnt_1s | cnt_5s | cnt_10s | cnt_INFs |
62+-----------------+---------------+-----------+-----------+-----------+---------+---------+----------+----------+-----------+-----------+--------+--------+---------+----------+
63| CREATE_DATABASE | 90942 | 3 | 0 | 0 | 0 | 2 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 0 |
64| CREATE_TABLE | 67954 | 3 | 0 | 0 | 0 | 0 | 0 | 3 | 0 | 0 | 0 | 0 | 0 | 0 |
65| INSERT | 11337671 | 113 | 0 | 0 | 0 | 69 | 27 | 14 | 1 | 0 | 1 | 0 | 0 | 1 |
66| SELECT | 16494138 | 24050 | 263 | 1744 | 20488 | 1512 | 29 | 10 | 3 | 1 | 0 | 0 | 0 | 0 |
67| SHOW | 56308 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 0 |
68| UNKNOWN | 131355 | 2 | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 0 |
69+-----------------+---------------+-----------+-----------+-----------+---------+---------+----------+----------+-----------+-----------+--------+--------+---------+----------+
706 rows in set (0.07 sec)
71
72MySQL [(none)]> SELECT * FROM stats.stats_mysql_connection_pool;
73+-----------+----------------+----------+--------+----------+----------+--------+---------+-------------+---------+-------------------+-----------------+-----------------+------------+
74| hostgroup | srv_host | srv_port | status | ConnUsed | ConnFree | ConnOK | ConnERR | MaxConnUsed | Queries | Queries_GTID_sync | Bytes_data_sent | Bytes_data_recv | Latency_us |
75+-----------+----------------+----------+--------+----------+----------+--------+---------+-------------+---------+-------------------+-----------------+-----------------+------------+
76| 10 | 192.168.68.131 | 3306 | ONLINE | 0 | 1 | 1 | 0 | 1 | 107 | 0 | 16464 | 92 | 277 |
77| 20 | 192.168.68.132 | 3306 | ONLINE | 0 | 1 | 1 | 0 | 1 | 24052 | 0 | 768920 | 335907539 | 306 |
78| 20 | 192.168.68.133 | 3306 | ONLINE | 1 | 0 | 1 | 0 | 1 | 24061 | 0 | 769119 | 335923167 | 381 |
79+-----------+----------------+----------+--------+----------+----------+--------+---------+-------------+---------+-------------------+-----------------+-----------------+------------+
803 rows in set (0.39 sec)
81
82MySQL [(none)]> SELECT hostgroup,schemaname , username , digest , sum_time, count_star, substr(digest_text,1,60) FROM stats_mysql_query_digest where schemaname='lhrmysqlslap' ORDER BY sum_time DESC;
83+-----------+--------------+----------+--------------------+----------+------------+---------------------------------------------------------+
84| hostgroup | schemaname | username | digest | sum_time | count_star | substr(digest_text,1,60) |
85+-----------+--------------+----------+--------------------+----------+------------+---------------------------------------------------------+
86| 20 | lhrmysqlslap | wr | 0x91B125A563AED6EB | 65166273 | 100000 | SELECT intcol1,charcol1 FROM t1 |
87| 10 | lhrmysqlslap | wr | 0xBCFD962F4A5FFA4B | 1134087 | 99 | INSERT INTO t1 VALUES (?,?) |
88| 10 | lhrmysqlslap | wr | 0x50E8C33778819FCD | 23904 | 1 | CREATE TABLE `t1` (intcol1 INT(?) ,charcol1 VARCHAR(?)) |
89| 10 | lhrmysqlslap | wr | 0x41B7F05694EF426F | 21351 | 1 | DROP SCHEMA IF EXISTS `lhrmysqlslap` |
90+-----------+--------------+----------+--------------------+----------+------------+---------------------------------------------------------+
914 rows in set (0.08 sec)复制
可以看出，负载被分别被分配到132和133上。
五、测试读写分离
1-- 测试读写分离
2mysql -uwr -plhr -h192.168.66.35 -P26033
3
4create database test_proxysql;
5use test_proxysql;
6create table test_tables(name varchar(20),age int(4));
7insert into test_tables values('lhr','33');
8select * from test_tables;
9
10
11select * from stats_mysql_query_digest;
12
13MySQL [(none)]> SELECT * FROM stats.stats_mysql_connection_pool;
14+-----------+----------------+----------+--------+----------+----------+--------+---------+-------------+---------+-------------------+-----------------+-----------------+------------+
15| hostgroup | srv_host | srv_port | status | ConnUsed | ConnFree | ConnOK | ConnERR | MaxConnUsed | Queries | Queries_GTID_sync | Bytes_data_sent | Bytes_data_recv | Latency_us |
16+-----------+----------------+----------+--------+----------+----------+--------+---------+-------------+---------+-------------------+-----------------+-----------------+------------+
17| 10 | 192.168.68.131 | 3306 | ONLINE | 0 | 1 | 1 | 0 | 1 | 5 | 0 | 165 | 92 | 369 |
18| 20 | 192.168.68.132 | 3306 | ONLINE | 0 | 1 | 1 | 0 | 1 | 8 | 0 | 142 | 130 | 301 |
19| 20 | 192.168.68.133 | 3306 | ONLINE | 0 | 1 | 1 | 0 | 1 | 5 | 0 | 97 | 67 | 341 |
20+-----------+----------------+----------+--------+----------+----------+--------+---------+-------------+---------+-------------------+-----------------+-----------------+------------+
213 rows in set (0.07 sec)
22-- 可以看到写操作被分配到131主机上。
23
24
25-- 其它表的统计信息
26SELECT * FROM stats.stats_mysql_connection_pool;
27SELECT * FROM stats_mysql_commands_counters WHERE Total_cnt;
28SELECT * FROM stats_mysql_query_digest ORDER BY sum_time DESC;
29SELECT hostgroup,schemaname , username , digest , sum_time, count_star, substr(digest_text,1,60) FROM stats_mysql_query_digest where schemaname='lhrmysqlslap' ORDER BY sum_time DESC;
30
31
32-- 清空数据，查询stats_mysql_query_digest_reset表时，会自动从stats_mysql_query_digest中临时抓取数据，并truncate。
33SELECT * FROM stats_mysql_query_digest_reset LIMIT 1;
34
35
36
37-- 使用sysbench继续读写分离测试
38-- 配置MySQL最大连接数：set global max_connections=1000;
39
40sysbench /usr/share/sysbench/oltp_common.lua --time=300 --mysql-host=192.168.68.136 --mysql-port=6033 --mysql-user=wr --mysql-password=lhr --mysql-db=sbtest --table-size=100000 --tables=20 --threads=100 --events=999999999 prepare
41
42sysbench /usr/share/sysbench/oltp_read_write.lua --time=300 --mysql-host=192.168.68.136 --mysql-port=6033 --mysql-user=wr --mysql-password=lhr --mysql-db=sbtest --table-size=10000 --tables=20 --threads=100 --events=999999999 --report-interval=10 --db-ps-mode=disable --forced-shutdown=1 run
43
44sysbench /usr/share/sysbench/oltp_read_only.lua --time=300 --mysql-host=192.168.68.136 --mysql-port=6033 --mysql-user=wr --mysql-password=lhr --mysql-db=sbtest --table-size=10000 --tables=20 --threads=100 --events=999999999 --report-interval=10 --db-ps-mode=disable --forced-shutdown=1 run
45
46
47-- 查询读写分离的监控数据
48MySQL [(none)]> SELECT hostgroup,schemaname , username , digest , sum_time, count_star, substr(digest_text,1,60) FROM stats_mysql_query_digest where schemaname='sbtest' ORDER BY sum_time DESC;
49+-----------+------------+----------+--------------------+----------+------------+--------------------------------------------------------------+
50| hostgroup | schemaname | username | digest | sum_time | count_star | substr(digest_text,1,60) |
51+-----------+------------+----------+--------------------+----------+------------+--------------------------------------------------------------+
52| 10 | sbtest | wr | 0x76607360EFEAC208 | 84059739 | 37 | INSERT INTO sbtest14(k, c, pad) VALUES(?, ?, ?),(?, ?, ?),(? |
53| 10 | sbtest | wr | 0x02834C12D1767CBF | 81396186 | 37 | INSERT INTO sbtest19(k, c, pad) VALUES(?, ?, ?),(?, ?, ?),(? |
54| 10 | sbtest | wr | 0xF175422CAEB5052B | 80172428 | 37 | INSERT INTO sbtest5(k, c, pad) VALUES(?, ?, ?),(?, ?, ?),(?, |
55| 10 | sbtest | wr | 0xD264943870461B52 | 78250552 | 37 | INSERT INTO sbtest9(k, c, pad) VALUES(?, ?, ?),(?, ?, ?),(?, |
56| 10 | sbtest | wr | 0x2F9D0B4C12C50457 | 75761155 | 37 | INSERT INTO sbtest13(k, c, pad) VALUES(?, ?, ?),(?, ?, ?),(? |
57| 10 | sbtest | wr | 0x0482F61CCAD957B8 | 74841767 | 37 | INSERT INTO sbtest16(k, c, pad) VALUES(?, ?, ?),(?, ?, ?),(? |
58| 10 | sbtest | wr | 0x2BA639A0C593250B | 74744554 | 37 | INSERT INTO sbtest20(k, c, pad) VALUES(?, ?, ?),(?, ?, ?),(? |
59| 10 | sbtest | wr | 0x131C045B3F7FC633 | 74710835 | 37 | INSERT INTO sbtest12(k, c, pad) VALUES(?, ?, ?),(?, ?, ?),(? |
60| 10 | sbtest | wr | 0x9677C76C4DF88251 | 74446715 | 37 | INSERT INTO sbtest6(k, c, pad) VALUES(?, ?, ?),(?, ?, ?),(?, |
61| 10 | sbtest | wr | 0x36760936592E8530 | 74289312 | 37 | INSERT INTO sbtest2(k, c, pad) VALUES(?, ?, ?),(?, ?, ?),(?, |
62| 10 | sbtest | wr | 0xEFBA5FC0C8412297 | 72425766 | 37 | INSERT INTO sbtest18(k, c, pad) VALUES(?, ?, ?),(?, ?, ?),(? |
63| 10 | sbtest | wr | 0xDF47484FFE945EDD | 70572836 | 37 | INSERT INTO sbtest8(k, c, pad) VALUES(?, ?, ?),(?, ?, ?),(?, |
64| 10 | sbtest | wr | 0x27D376AC1710C980 | 66516942 | 37 | INSERT INTO sbtest11(k, c, pad) VALUES(?, ?, ?),(?, ?, ?),(? |
65| 10 | sbtest | wr | 0x4D433FDEA1B945C1 | 65829143 | 37 | INSERT INTO sbtest10(k, c, pad) VALUES(?, ?, ?),(?, ?, ?),(? |
66| 10 | sbtest | wr | 0xFF6D9C6F32545951 | 65739929 | 37 | INSERT INTO sbtest1(k, c, pad) VALUES(?, ?, ?),(?, ?, ?),(?, |
67| 10 | sbtest | wr | 0x772AE6B66160E91E | 65271284 | 37 | INSERT INTO sbtest7(k, c, pad) VALUES(?, ?, ?),(?, ?, ?),(?, |
68| 10 | sbtest | wr | 0x4F8D29910113CAE6 | 64822306 | 37 | INSERT INTO sbtest15(k, c, pad) VALUES(?, ?, ?),(?, ?, ?),(? |
69| 10 | sbtest | wr | 0x7502AE74F4B0113E | 64133812 | 37 | INSERT INTO sbtest4(k, c, pad) VALUES(?, ?, ?),(?, ?, ?),(?, |
70| 10 | sbtest | wr | 0x4B083B064FF4F9D9 | 61931719 | 37 | INSERT INTO sbtest17(k, c, pad) VALUES(?, ?, ?),(?, ?, ?),(? |
71| 10 | sbtest | wr | 0x3C6D29F861CD6572 | 60387247 | 37 | INSERT INTO sbtest3(k, c, pad) VALUES(?, ?, ?),(?, ?, ?),(?, |
72| 10 | sbtest | wr | 0x8965AD5701787BC0 | 33638613 | 1 | CREATE INDEX k_3 ON sbtest3(k) |
73| 10 | sbtest | wr | 0xB12065B0D79AC0DD | 33288092 | 1 | CREATE INDEX k_17 ON sbtest17(k) |
74| 10 | sbtest | wr | 0xC46D61BAA605D7D3 | 32926000 | 1 | CREATE INDEX k_1 ON sbtest1(k) |
75| 10 | sbtest | wr | 0x2BF8C8E7084502D5 | 32904517 | 1 | CREATE INDEX k_7 ON sbtest7(k) |
76| 10 | sbtest | wr | 0x6F22CFB8FF512B02 | 32625659 | 1 | CREATE INDEX k_4 ON sbtest4(k) |
77| 10 | sbtest | wr | 0x14A86D647A425E21 | 31622991 | 1 | CREATE INDEX k_15 ON sbtest15(k) |
78| 10 | sbtest | wr | 0xF9D03F580356BB68 | 31573312 | 1 | CREATE INDEX k_11 ON sbtest11(k) |
79| 10 | sbtest | wr | 0xA43F49E4ADA080FB | 31346640 | 1 | CREATE INDEX k_10 ON sbtest10(k) |
80| 10 | sbtest | wr | 0x922B9C1E888EB4C9 | 29435206 | 1 | CREATE INDEX k_8 ON sbtest8(k) |
81| 10 | sbtest | wr | 0x409A0DA0B5B6EEF9 | 28482669 | 1 | CREATE INDEX k_2 ON sbtest2(k) |
82| 10 | sbtest | wr | 0xE4300864715B3688 | 28000903 | 1 | CREATE INDEX k_20 ON sbtest20(k) |
83| 10 | sbtest | wr | 0x4DE9E56B5EF734F2 | 27918352 | 1 | CREATE INDEX k_6 ON sbtest6(k) |
84| 10 | sbtest | wr | 0x82CE0656182236D8 | 27909444 | 1 | CREATE INDEX k_12 ON sbtest12(k) |
85| 10 | sbtest | wr | 0xFE8EAD5ACC9FEEDE | 27273704 | 1 | CREATE INDEX k_18 ON sbtest18(k) |
86| 10 | sbtest | wr | 0x35A492B2AB47EB41 | 26754664 | 1 | CREATE INDEX k_16 ON sbtest16(k) |
87| 10 | sbtest | wr | 0xF5B52253F5260086 | 21401807 | 1 | CREATE INDEX k_9 ON sbtest9(k) |
88| 10 | sbtest | wr | 0xA1B769A0F4E9637C | 21271034 | 1 | CREATE INDEX k_13 ON sbtest13(k) |
89| 10 | sbtest | wr | 0xFBE6F4A5E871D069 | 21202873 | 1 | CREATE INDEX k_5 ON sbtest5(k) |
90| 10 | sbtest | wr | 0x004D21922AA0CC4C | 19075137 | 1 | CREATE INDEX k_19 ON sbtest19(k) |
91| 10 | sbtest | wr | 0x6057175824222B09 | 18110480 | 1 | CREATE INDEX k_14 ON sbtest14(k) |
92| 10 | sbtest | wr | 0x5143272478FE391F | 5837193 | 1 | INSERT INTO sbtest13(k, c, pad) VALUES(?, ?, ?),(?, ?, ?),(? |
93| 10 | sbtest | wr | 0xD9E2214392AB9E0D | 2791511 | 1 | INSERT INTO sbtest9(k, c, pad) VALUES(?, ?, ?),(?, ?, ?),(?, |
94| 10 | sbtest | wr | 0x4AC5841F976F5A1A | 1686763 | 2 | CREATE TABLE sbtest5( id INTEGER NOT NULL AUTO_INCREMENT, k |
95| 10 | sbtest | wr | 0x0E320961406063D2 | 1608309 | 1 | INSERT INTO sbtest3(k, c, pad) VALUES(?, ?, ?),(?, ?, ?),(?, |
96| 10 | sbtest | wr | 0x1886E479A84E6EF3 | 1364378 | 1 | INSERT INTO sbtest19(k, c, pad) VALUES(?, ?, ?),(?, ?, ?),(? |
97| 10 | sbtest | wr | 0xDC0701A550CF81E1 | 1315637 | 2 | CREATE TABLE sbtest14( id INTEGER NOT NULL AUTO_INCREMENT, k |
98| 10 | sbtest | wr | 0xDE42F97C93E70D2F | 1213739 | 2 | CREATE TABLE sbtest9( id INTEGER NOT NULL AUTO_INCREMENT, k |
99| 10 | sbtest | wr | 0x69426F34842FCBCB | 1167716 | 1 | INSERT INTO sbtest16(k, c, pad) VALUES(?, ?, ?),(?, ?, ?),(? |
100| 10 | sbtest | wr | 0x3455DC796FFE13FF | 1096589 | 2 | CREATE TABLE sbtest19( id INTEGER NOT NULL AUTO_INCREMENT, k |
101| 10 | sbtest | wr | 0xCB672EA01B2BCC66 | 1059624 | 2 | CREATE TABLE sbtest18( id INTEGER NOT NULL AUTO_INCREMENT, k |
102| 10 | sbtest | wr | 0x68A680665F3A4F7F | 1046881 | 1 | INSERT INTO sbtest18(k, c, pad) VALUES(?, ?, ?),(?, ?, ?),(? |
103| 10 | sbtest | wr | 0x988823E25FA87160 | 1008422 | 2 | CREATE TABLE sbtest7( id INTEGER NOT NULL AUTO_INCREMENT, k |
104| 10 | sbtest | wr | 0x630131CEA842636C | 941575 | 1 | INSERT INTO sbtest17(k, c, pad) VALUES(?, ?, ?),(?, ?, ?),(? |
105| 10 | sbtest | wr | 0xCFABCFBA3338DFE6 | 883756 | 2 | CREATE TABLE sbtest13( id INTEGER NOT NULL AUTO_INCREMENT, k |
106| 10 | sbtest | wr | 0x38C679BD1A2B850D | 800858 | 2 | CREATE TABLE sbtest20( id INTEGER NOT NULL AUTO_INCREMENT, k |
107| 10 | sbtest | wr | 0xA8C074E066D84361 | 745750 | 2 | CREATE TABLE sbtest2( id INTEGER NOT NULL AUTO_INCREMENT, k |
108| 10 | sbtest | wr | 0x644F2A01D1AEE6F0 | 618114 | 1 | INSERT INTO sbtest8(k, c, pad) VALUES(?, ?, ?),(?, ?, ?),(?, |
109| 10 | sbtest | wr | 0xA6A432C624F814B1 | 588508 | 1 | INSERT INTO sbtest7(k, c, pad) VALUES(?, ?, ?),(?, ?, ?),(?, |
110| 10 | sbtest | wr | 0xAC072AB4E74DCA04 | 587709 | 1 | INSERT INTO sbtest1(k, c, pad) VALUES(?, ?, ?),(?, ?, ?),(?, |
111| 10 | sbtest | wr | 0x6A5DF2EE9E492E4E | 544548 | 1 | INSERT INTO sbtest4(k, c, pad) VALUES(?, ?, ?),(?, ?, ?),(?, |
112| 10 | sbtest | wr | 0xB483ABDF2ACB307D | 523796 | 2 | CREATE TABLE sbtest16( id INTEGER NOT NULL AUTO_INCREMENT, k |
113| 10 | sbtest | wr | 0xDD3B26A209175EF7 | 520238 | 1 | INSERT INTO sbtest10(k, c, pad) VALUES(?, ?, ?),(?, ?, ?),(? |
114| 10 | sbtest | wr | 0x1061831367EE99C7 | 453881 | 1 | INSERT INTO sbtest14(k, c, pad) VALUES(?, ?, ?),(?, ?, ?),(? |
115| 10 | sbtest | wr | 0x7F93BDE97051D79A | 440020 | 2 | CREATE TABLE sbtest6( id INTEGER NOT NULL AUTO_INCREMENT, k |
116| 10 | sbtest | wr | 0x806A9CCB80119BE2 | 417349 | 2 | CREATE TABLE sbtest1( id INTEGER NOT NULL AUTO_INCREMENT, k |
117| 10 | sbtest | wr | 0x43CA6E3D0072BCBE | 402073 | 2 | CREATE TABLE sbtest12( id INTEGER NOT NULL AUTO_INCREMENT, k |
118| 10 | sbtest | wr | 0xA204975AD230A23B | 400655 | 2 | CREATE TABLE sbtest8( id INTEGER NOT NULL AUTO_INCREMENT, k |
119| 10 | sbtest | wr | 0x11E6BAC23207DD78 | 381162 | 1 | INSERT INTO sbtest2(k, c, pad) VALUES(?, ?, ?),(?, ?, ?),(?, |
120| 10 | sbtest | wr | 0x42B1839D8797EDCB | 379488 | 1 | INSERT INTO sbtest12(k, c, pad) VALUES(?, ?, ?),(?, ?, ?),(? |
121| 10 | sbtest | wr | 0x583CBA28271C4365 | 357866 | 2 | CREATE TABLE sbtest4( id INTEGER NOT NULL AUTO_INCREMENT, k |
122| 10 | sbtest | wr | 0x6ACC6500F6722004 | 340867 | 2 | CREATE TABLE sbtest15( id INTEGER NOT NULL AUTO_INCREMENT, k |
123| 10 | sbtest | wr | 0x5408EB0F722B3B6F | 324340 | 2 | CREATE TABLE sbtest10( id INTEGER NOT NULL AUTO_INCREMENT, k |
124| 10 | sbtest | wr | 0xCD3FA57950F3E362 | 293837 | 1 | INSERT INTO sbtest5(k, c, pad) VALUES(?, ?, ?),(?, ?, ?),(?, |
125| 10 | sbtest | wr | 0x4196561D18B78360 | 285839 | 1 | INSERT INTO sbtest20(k, c, pad) VALUES(?, ?, ?),(?, ?, ?),(? |
126| 10 | sbtest | wr | 0xE7DCF83C81EDEA8D | 265476 | 2 | CREATE TABLE sbtest11( id INTEGER NOT NULL AUTO_INCREMENT, k |
127| 10 | sbtest | wr | 0xFA3A3817BE19ABB3 | 250778 | 2 | CREATE TABLE sbtest3( id INTEGER NOT NULL AUTO_INCREMENT, k |
128| 10 | sbtest | wr | 0x6B8F384E1250D83C | 247460 | 2 | CREATE TABLE sbtest17( id INTEGER NOT NULL AUTO_INCREMENT, k |
129| 10 | sbtest | wr | 0x26E4B187688CC6BE | 218255 | 1 | INSERT INTO sbtest6(k, c, pad) VALUES(?, ?, ?),(?, ?, ?),(?, |
130| 10 | sbtest | wr | 0x9E18B2E0420BA351 | 200861 | 1 | INSERT INTO sbtest15(k, c, pad) VALUES(?, ?, ?),(?, ?, ?),(? |
131| 10 | sbtest | wr | 0xAB7D4ACFF578DC61 | 199109 | 1 | INSERT INTO sbtest11(k, c, pad) VALUES(?, ?, ?),(?, ?, ?),(? |
132+-----------+------------+----------+--------------------+----------+------------+--------------------------------------------------------------+
13380 rows in set (0.71 sec)
134
135 MySQL [(none)]> SELECT * FROM stats_mysql_commands_counters WHERE Total_cnt;
136+-----------------+---------------+-----------+-----------+-----------+---------+---------+----------+----------+-----------+-----------+--------+--------+---------+----------+
137| Command | Total_Time_us | Total_cnt | cnt_100us | cnt_500us | cnt_1ms | cnt_5ms | cnt_10ms | cnt_50ms | cnt_100ms | cnt_500ms | cnt_1s | cnt_5s | cnt_10s | cnt_INFs |
138+-----------------+---------------+-----------+-----------+-----------+---------+---------+----------+----------+-----------+-----------+--------+--------+---------+----------+
139| BEGIN | 2793013652 | 15681 | 95 | 888 | 1864 | 5806 | 2671 | 2882 | 447 | 266 | 33 | 533 | 190 | 6 |
140| COMMIT | 4360624460 | 15575 | 3 | 31 | 58 | 191 | 138 | 1031 | 2337 | 9827 | 1369 | 590 | 0 | 0 |
141| CREATE_DATABASE | 99871 | 4 | 0 | 0 | 0 | 2 | 1 | 0 | 1 | 0 | 0 | 0 | 0 | 0 |
142| CREATE_INDEX | 556762097 | 20 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 20 |
143| CREATE_TABLE | 13849772 | 43 | 0 | 0 | 1 | 0 | 0 | 21 | 1 | 11 | 4 | 5 | 0 | 0 |
144| DELETE | 603363949 | 14940 | 45 | 197 | 1637 | 5575 | 2424 | 2997 | 771 | 1081 | 136 | 77 | 0 | 0 |
145| INSERT | 1734742355 | 15812 | 38 | 339 | 1872 | 6251 | 2484 | 2887 | 586 | 590 | 138 | 605 | 21 | 1 |
146| SELECT | 3704563235 | 319925 | 965 | 12937 | 109961 | 96434 | 40564 | 44484 | 7667 | 6460 | 376 | 77 | 0 | 0 |
147| UPDATE | 1788596841 | 29900 | 45 | 166 | 2336 | 9589 | 4487 | 6847 | 2123 | 3682 | 445 | 180 | 0 | 0 |
148| SHOW | 58391 | 2 | 0 | 0 | 0 | 1 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 0 |
149| UNKNOWN | 152706 | 3 | 0 | 0 | 0 | 1 | 0 | 1 | 0 | 1 | 0 | 0 | 0 | 0 |
150+-----------------+---------------+-----------+-----------+-----------+---------+---------+----------+----------+-----------+-----------+--------+--------+---------+----------+
15111 rows in set (0.11 sec)复制
可以观察到读写分离的数据。
六、故障切换
在Manager节点检查SSH、复制及MHA的状态。
1docker exec -it MHA-LHR-Monitor-ip134 bash
2masterha_check_ssh --conf=/etc/mha/mha.cnf
3masterha_check_repl --conf=/etc/mha/mha.cnf
4masterha_check_status --conf=/etc/mha/mha.cnf
5
6-- 启动MHA监控进程
7nohup masterha_manager --conf=/etc/mha/mha.cnf --ignore_last_failover < /dev/null > /usr/local/mha/manager_start.log 2>&1 &
8
9--关闭MHA监控进程
10masterha_stop --conf=/etc/mha/mha.cnf
11
12
13[root@MHA-LHR-Monitor-ip134 /]# masterha_check_status --conf=/etc/mha/mha.cnf
14mha (pid:3738) is running(0:PING_OK), master:192.168.68.131复制
接下来，宕掉主库，继续观察ProxySQL的情况：
1 -- 宕掉主库
2 docker stop MHA-LHR-Master1-ip131复制
MHA自动执行了故障转移，主库切换为132，并发送告警邮件：
此时，来查看ProxySQL的情况：
1MySQL [(none)]> select * from mysql_servers;
2+--------------+----------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
3| hostgroup_id | hostname | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
4+--------------+----------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
5| 10 | 192.168.68.132 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
6| 20 | 192.168.68.131 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
7| 20 | 192.168.68.133 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
8| 20 | 192.168.68.132 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
9+--------------+----------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
104 rows in set (0.05 sec)
11
12MySQL [(none)]> select * from runtime_mysql_servers;
13+--------------+----------------+------+-----------+---------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
14| hostgroup_id | hostname | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
15+--------------+----------------+------+-----------+---------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
16| 10 | 192.168.68.132 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
17| 20 | 192.168.68.131 | 3306 | 0 | SHUNNED | 1 | 0 | 1000 | 0 | 0 | 0 | |
18| 20 | 192.168.68.133 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
19| 20 | 192.168.68.132 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
20+--------------+----------------+------+-----------+---------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
214 rows in set (1.26 sec)复制
可以发现131已经变成SHUNNED状态，ProxySQL会避开这个主机。
此时再做压测等操作，所有负载会被分配到132和133上，此处不再测试。
接下来启动131，并以从库的身份加入原主从环境：
1-- 启动131
2docker start MHA-LHR-Master1-ip131
3
4-- 在134的日志文件中找到恢复的语句
5grep "All other slaves should start replication from here" /usr/local/mha/manager_running.log
6
7-- 在131上执行恢复
8mysql -uroot -plhr -h192.168.68.131 -P3306
9CHANGE MASTER TO MASTER_HOST='192.168.68.132',
10MASTER_PORT=3306,
11MASTER_AUTO_POSITION=1,
12MASTER_USER='repl',
13MASTER_PASSWORD='lhr';
14
15start slave;
16show slave status \G
17
18-- 设置只读
19set global read_only=1;复制
查询ProxySQL：
1MySQL [(none)]> select * from mysql_servers;
2+--------------+----------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
3| hostgroup_id | hostname | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
4+--------------+----------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
5| 10 | 192.168.68.132 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
6| 20 | 192.168.68.131 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
7| 20 | 192.168.68.133 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
8| 20 | 192.168.68.132 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
9+--------------+----------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
104 rows in set (0.06 sec)复制
可以看到131为只读。若想让132只写，则可以删除相关记录：
1MySQL [(none)]> delete from mysql_servers where hostgroup_id=20 and hostname='192.168.68.132';
2Query OK, 1 row affected (0.06 sec)
3
4MySQL [(none)]> load mysql servers to runtime;
5Query OK, 0 rows affected (0.68 sec)
6
7MySQL [(none)]> save mysql servers to disk;
8Query OK, 0 rows affected (0.10 sec)
9
10MySQL [(none)]> select * from mysql_servers;
11+--------------+----------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
12| hostgroup_id | hostname | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
13+--------------+----------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
14| 10 | 192.168.68.132 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
15| 20 | 192.168.68.131 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
16| 20 | 192.168.68.133 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
17+--------------+----------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
183 rows in set (0.05 sec)
19
20MySQL [(none)]> select * from runtime_mysql_servers;
21+--------------+----------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
22| hostgroup_id | hostname | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
23+--------------+----------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
24| 10 | 192.168.68.132 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
25| 20 | 192.168.68.133 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
26| 20 | 192.168.68.131 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
27+--------------+----------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
283 rows in set (0.94 sec)复制
可以看到132为主库，131和133为从库。Orchestrator界面：
七、界面监控结果（ProxySQL本身+PMM监控）
7.1 ProxySQL自带监控
ProxySQL监控结果：
7.2 PMM监控
监控可以使用ProxySQL本身自动的监控，也可以使用PMM来监控。PMM（Percona Monitoring and Management）是一个免费的开源平台，用于管理和监视数据库性能，在docker环境中即可运行。它可以主动管理和监控MySQL（AWS RDS MySQL、Aurora MySQL、用户自建MySQL实例）、MariaDB、MongoDB、PostgreSQL等数据库，也可以监控ProxySQL中间件，并提供了众多指标与多样告警方式。
PMM监控ProxySQL的命令如下：
1pmm-admin add proxysql --username=root --password=lhr --host=192.168.66.35 --port=26032 --service-name=proxysql-192.168.66.35-26032复制
PMM监控结果：
本文结束。
• 微信公众号：DB宝，作者：小麦苗
• 作者博客地址：http://blog.itpub.net/26736162/
• 作者微信：db_bao• 作者QQ：646634621，QQ群：230161599、618766405
• 提供Oracle OCP、OCM、高可用（rac+dg+ogg）和MySQL DBA培训
• 版权所有，欢迎分享本文，转载请保留出处• 若有侵权请联系小麦苗删除
★DB宝分享的IT资料：https://mp.weixin.qq.com/s/Iwsy-zkzwgs8nYkcMz29ag★DB宝笔试面试详解：https://mp.weixin.qq.com/s/Vm5PqNcDcITkOr9cQg6T7w复制
长按下图识别二维码，关注小麦苗的微信公众号：DB宝，学习最实用的数据库技术。
文章转载自DB宝，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
评论
相关阅读
【专家有话说第五期】在不同年龄段，DBA应该怎样规划自己的职业发展？
墨天轮编辑部
1327次阅读
2025-03-13 11:40:53
MySQL8.0统计信息总结
闫建(Rock Yan)
500次阅读
2025-03-17 16:04:03
2月“墨力原创作者计划”获奖名单公布
墨天轮编辑部
469次阅读
2025-03-13 14:38:19
SQL优化 - explain查看SQL执行计划（一）
金同学
397次阅读
2025-03-13 16:04:22
MySQL突然崩溃？教你用gdb解剖core文件，快速锁定“元凶”!
szrsu
374次阅读
2025-03-13 00:29:43
MySQL生产实战优化（利用Index skip scan优化性能提升257倍）
chengang
330次阅读
2025-03-17 10:36:40
MySQL数据库当前和历史事务分析
听见风的声音
301次阅读
2025-04-01 08:47:17
MySQL 生产实践-Update 二级索引导致的性能问题排查
chengang
249次阅读
2025-03-28 16:28:31
一键装库脚本3分钟极速部署，传统耗时砍掉95%！
IT邦德
240次阅读
2025-03-10 07:58:44
墨天轮个人数说知识点合集
JiekeXu
234次阅读
2025-04-01 15:56:03
