如何利用 MHA+ProxySQL 实现读写分离和负载均衡
作者：华为云开发者社区
- 2022 年 4 月 06 日
本文字数：14018 字
阅读完需：约 46 分钟
本文分享自华为云社区《MySQL高可用架构MHA+ProxySQL实现读写分离和负载均衡》，作者：小麦苗 DB 宝。
一、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 架构如下图所示：
https://cdn.jsdelivr.net/gh/lhrbest/pic/img/20210416111756.png
二、快速搭建 MHA 环境
2.1 下载 MHA 镜像
小麦苗的 Docker Hub 的地址：https://hub.docker.com/u/lhrbest
-- 下载镜像
docker pull registry.cn-hangzhou.aliyuncs.com/lhrbest/mha-lhr-master1-ip131docker pull registry.cn-hangzhou.aliyuncs.com/lhrbest/mha-lhr-slave1-ip132docker pull registry.cn-hangzhou.aliyuncs.com/lhrbest/mha-lhr-slave2-ip133docker pull registry.cn-hangzhou.aliyuncs.com/lhrbest/mha-lhr-monitor-ip134复制代码
-- 重命名镜像
docker tag registry.cn-hangzhou.aliyuncs.com/lhrbest/mha-lhr-master1-ip131 lhrbest/mha-lhr-master1-ip131docker tag registry.cn-hangzhou.aliyuncs.com/lhrbest/mha-lhr-slave1-ip132 lhrbest/mha-lhr-slave1-ip132 docker tag registry.cn-hangzhou.aliyuncs.com/lhrbest/mha-lhr-slave2-ip133 lhrbest/mha-lhr-slave2-ip133 docker tag registry.cn-hangzhou.aliyuncs.com/lhrbest/mha-lhr-monitor-ip134 lhrbest/mha-lhr-monitor-ip134复制代码
一共 4 个镜像，3 个 MHA Node，一个 MHA Manager，压缩包大概 3G，下载完成后：
[root@lhrdocker ~]# docker images | grep mharegistry.cn-hangzhou.aliyuncs.com/lhrbest/mha-lhr-monitor-ip134 latest 7d29597dc997 14 hours ago 1.53GBregistry.cn-hangzhou.aliyuncs.com/lhrbest/mha-lhr-slave2-ip133 latest d3717794e93a 40 hours ago 4.56GBregistry.cn-hangzhou.aliyuncs.com/lhrbest/mha-lhr-slave1-ip132 latest f62ee813e487 40 hours ago 4.56GBregistry.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 文件的格式，对空格、缩进、对齐都有严格要求：
# 创建存放yml文件的路径mkdir -p /root/mha
# 编辑文件/root/mha/docker-compose.ymlcat > /root/mha/docker-compose.yml <<"EOF"version: '3.8'
services: MHA-LHR-Master1-ip131: container_name: "MHA-LHR-Master1-ip131" restart: "always" hostname: MHA-LHR-Master1-ip131 privileged: true image: lhrbest/mha-lhr-master1-ip131 ports: - "33131:3306" - "2201:22" networks: mhalhr: ipv4_address: 192.168.68.131
MHA-LHR-Slave1-ip132: container_name: "MHA-LHR-Slave1-ip132" restart: "always" hostname: MHA-LHR-Slave1-ip132 privileged: true image: lhrbest/mha-lhr-slave1-ip132 ports: - "33132:3306" - "2202:22" networks: mhalhr: ipv4_address: 192.168.68.132
MHA-LHR-Slave2-ip133: container_name: "MHA-LHR-Slave2-ip133" restart: "always" hostname: MHA-LHR-Slave2-ip133 privileged: true image: lhrbest/mha-lhr-slave2-ip133 ports: - "33133:3306" - "2203:22" networks: mhalhr: ipv4_address: 192.168.68.133
MHA-LHR-Monitor-ip134: container_name: "MHA-LHR-Monitor-ip134" restart: "always" hostname: MHA-LHR-Monitor-ip134 privileged: true image: lhrbest/mha-lhr-monitor-ip134 ports: - "33134:3306" - "2204:22" networks: mhalhr: ipv4_address: 192.168.68.134
networks: mhalhr: name: mhalhr ipam: config: - subnet: "192.168.68.0/16"
EOF复制代码
2.3 安装 docker-compose 软件（若已安装，可忽略）
安装 Docker Compose 官方文档：https://docs.docker.com/compose/
编辑 docker-compose.yml 文件官方文档：https://docs.docker.com/compose/compose-file/
[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 % Total % Received % Xferd Average Speed Time Time Time Current Dload Upload Total Spent Left Speed100 638 100 638 0 0 530 0 0:00:01 0:00:01 --:--:-- 531100 11.6M 100 11.6M 0 0 1994k 0 0:00:06 0:00:06 --:--:-- 2943k[root@lhrdocker ~]# chmod +x /usr/local/bin/docker-compose[root@lhrdocker ~]# docker-compose -vdocker-compose version 1.28.4, build cabd5cfb复制代码
2.4 创建 MHA 容器
# 启动mha环境的容器，一定要进入文件夹/root/mha/后再操作-- docker rm -f MHA-LHR-Master1-ip131 MHA-LHR-Slave1-ip132 MHA-LHR-Slave2-ip133 MHA-LHR-Monitor-ip134[root@lhrdocker ~]# cd /root/mha/[root@lhrdocker mha]#[root@lhrdocker mha]# docker-compose up -dCreating network "mhalhr" with the default driverCreating MHA-LHR-Monitor-ip134 ... doneCreating MHA-LHR-Slave2-ip133 ... doneCreating MHA-LHR-Master1-ip131 ... doneCreating MHA-LHR-Slave1-ip132 ... done[root@docker35 ~]# docker ps | grep "mha\|COMMAND" CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES2978361198b7 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-ip131a64e2e86589c 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-ip132d7d6ce34800b 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-ip134dacd22edb2f8 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
# 进入主库131docker exec -it MHA-LHR-Master1-ip131 bash
# 添加VIP135/sbin/ifconfig eth0:1 192.168.68.135/24ifconfig
# 如果删除的话ip addr del 192.168.68.135/24 dev eth1复制代码
添加完成后：
[root@MHA-LHR-Master1-ip131 /]# ifconfigeth0: flags=4163<UP,BROADCAST,RUNNING,MULTICAST> mtu 1500 inet 192.168.68.131 netmask 255.255.0.0 broadcast 192.168.255.255 ether 02:42:c0:a8:44:83 txqueuelen 0 (Ethernet) RX packets 220 bytes 15883 (15.5 KiB) RX errors 0 dropped 0 overruns 0 frame 0 TX packets 189 bytes 17524 (17.1 KiB) TX errors 0 dropped 0 overruns 0 carrier 0 collisions 0
eth0:1: flags=4163<UP,BROADCAST,RUNNING,MULTICAST> mtu 1500 inet 192.168.68.135 netmask 255.255.255.0 broadcast 192.168.68.255 ether 02:42:c0:a8:44:83 txqueuelen 0 lo: flags=73<UP,LOOPBACK,RUNNING> mtu 65536 inet 127.0.0.1 netmask 255.0.0.0 loop txqueuelen 1000 (Local Loopback) RX packets 5 bytes 400 (400.0 B) RX errors 0 dropped 0 overruns 0 frame 0 TX packets 5 bytes 400 (400.0 B) TX errors 0 dropped 0 overruns 0 carrier 0 collisions 0 # 管理节点已经可以ping通VIP了[root@MHA-LHR-Monitor-ip134 /]# ping 192.168.68.135PING 192.168.68.135 (192.168.68.135) 56(84) bytes of data.64 bytes from 192.168.68.135: icmp_seq=1 ttl=64 time=0.172 ms64 bytes from 192.168.68.135: icmp_seq=2 ttl=64 time=0.076 ms^C--- 192.168.68.135 ping statistics ---2 packets transmitted, 2 received, 0% packet loss, time 1000msrtt min/avg/max/mdev = 0.076/0.124/0.172/0.048 ms复制代码
到这一步就可以验证主从复制是否正确，若正确，则可以直接测试 MHA 了。
mysql -uroot -plhr -h192.168.68.131 -P3306show slave hosts;mysql> show slave hosts;+-----------+----------------+------+-----------+--------------------------------------+| Server_id | Host | Port | Master_id | Slave_UUID |+-----------+----------------+------+-----------+--------------------------------------+| 573306133 | 192.168.68.133 | 3306 | 573306131 | d391ce7e-aec3-11ea-94cd-0242c0a84485 || 573306132 | 192.168.68.132 | 3306 | 573306131 | d24a77d1-aec3-11ea-9399-0242c0a84484 |+-----------+----------------+------+-----------+--------------------------------------+2 rows in set (0.00 sec)复制代码
https://cdn.jsdelivr.net/gh/lhrbest/pic/img/20210416111806.png
三、配置 ProxySQL 环境
3.1 申请 ProxySQL 主机并安装 ProxySQL
docker rm -f MHA-LHR-ProxySQL-ip136docker run -d --name MHA-LHR-ProxySQL-ip136 -h MHA-LHR-ProxySQL-ip136 \ -v /sys/fs/cgroup:/sys/fs/cgroup \ --network mhalhr --ip 192.168.68.136 \ -p 26032:6032 -p 26033:6033 -p 26080:6080 \ --privileged=true lhrbest/lhrcentos76:8.0 \ /usr/sbin/init
docker network connect bridge MHA-LHR-ProxySQL-ip136docker restart MHA-LHR-ProxySQL-ip136
docker cp proxysql2-2.0.15-1.1.el7.x86_64.rpm MHA-LHR-ProxySQL-ip136:/docker exec -it MHA-LHR-ProxySQL-ip136 bashrpm -ivh proxysql2-2.0.15-1.1.el7.x86_64.rpmsystemctl start proxysqlsystemctl status proxysql复制代码
3.2 添加远程登录用户
-- 添加远程登录用户mysql -uadmin -padmin -h127.0.0.1 -P6032select @@admin-admin_credentials;set admin-admin_credentials='admin:admin;root:lhr';select @@admin-admin_credentials;load admin variables to runtime;save admin variables to disk;
-- 远程登录mysql -uroot -plhr -h192.168.66.35 -P26032复制代码
执行过程：
-- ProxySQL本地登录[root@MHA-LHR-ProxySQL-ip136 /]# mysql -uadmin -padmin -h127.0.0.1 -P6032mysql: [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 162Server version: 5.5.30 (ProxySQL Admin Module)
Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> select @@admin-admin_credentials;+---------------------------+| @@admin-admin_credentials |+---------------------------+| admin:admin;lhr:lhr |+---------------------------+1 row in set (0.05 sec)
mysql> set admin-admin_credentials='admin:admin;root:lhr';Query OK, 1 row affected (0.00 sec)
mysql> select @@admin-admin_credentials;+---------------------------+| @@admin-admin_credentials |+---------------------------+| admin:admin;root:lhr |+---------------------------+1 row in set (0.00 sec)
mysql> load admin variables to runtime;Query OK, 0 rows affected (0.00 sec)
mysql> save admin variables to disk;Query OK, 35 rows affected (0.13 sec)
mysql>
-- 远程登录C:\Users\lhrxxt>mysql -uroot -plhr -h192.168.66.35 -P26032mysql: [Warning] Using a password on the command line interface can be insecure.ERROR 1045 (28000): ProxySQL Error: Access denied for user 'root'@'172.17.0.1' (using password: YES)
C:\Users\lhrxxt>mysql -uroot -plhr -h192.168.66.35 -P26032mysql: [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 163Server version: 5.5.30 (ProxySQL Admin Module)
Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MySQL [(none)]> show databases;+-----+---------------+-------------------------------------+| seq | name | file |+-----+---------------+-------------------------------------+| 0 | main | || 2 | disk | /var/lib/proxysql/proxysql.db || 3 | stats | || 4 | monitor | || 5 | stats_history | /var/lib/proxysql/proxysql_stats.db |+-----+---------------+-------------------------------------+5 rows in set (0.05 sec)复制代码
3.3 开启 ProxySQL 的 web 监控功能
-- 开启web监控功能SET admin-web_enabled='true';LOAD ADMIN VARIABLES TO RUNTIME;SAVE ADMIN VARIABLES TO DISK;select * from global_variables where variable_name LIKE 'admin-web_enabled';select @@admin-web_enabled;
lsof -i:6080
-- 浏览器访问https://192.168.66.35:26080用户名和密码：stats:stats复制代码
https://cdn.jsdelivr.net/gh/lhrbest/pic/img/20210416111815.png
3.4 配置被监控的数据库
3.4.1 向 ProxySQL 插入被监控数据库
-- 1、向ProxySQL插入被监控数据库select * from mysql_servers;insert into main.mysql_servers(hostgroup_id,hostname,port) values(10,'192.168.68.131',3306);insert into main.mysql_servers(hostgroup_id,hostname,port) values(10,'192.168.68.132',3306);insert into main.mysql_servers(hostgroup_id,hostname,port) values(10,'192.168.68.133',3306);load mysql servers to runtime;save mysql servers to disk;select * from mysql_servers;MySQL [(none)]> select * from mysql_servers;+--------------+----------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+| hostgroup_id | hostname | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |+--------------+----------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+| 10 | 192.168.68.131 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | || 10 | 192.168.68.132 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | || 10 | 192.168.68.133 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |+--------------+----------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+3 rows in set (0.07 sec)复制代码
3.4.2 在所有被监控 MySQL 服务器上创建监控帐户
-- 2、在所有被监控MySQL服务器上创建帐户，注意：新版本中，这里的密码必须为monitor，可参考配置文件/etc/proxysql.cnfmysql -uroot -plhr -h192.168.66.35 -P33131create user 'monitor'@'%' IDENTIFIED BY 'monitor';GRANT all privileges ON *.* TO 'monitor'@'%' with grant option;select user,host from mysql.user;
mysql> select user,host from mysql.user;+---------------+--------------+| user | host |+---------------+--------------+| mha | % || monitor | % || repl | % || root | % || mysql.session | localhost || mysql.sys | localhost || root | localhost |+---------------+--------------+7 rows in set (0.00 sec)复制代码
3.4.3 在所有被监控 MySQL 服务器上创建对外访问账户
-- 3、 在所有被监控MySQL服务器上创建对外访问账户：create user 'wr'@'%' IDENTIFIED BY 'lhr';GRANT all privileges ON *.* TO 'wr'@'%' with grant option;
-- 配置到ProxySQL中insert into mysql_users(username,password,default_hostgroup) values('wr','lhr',10);update mysql_users set transaction_persistent=1 where username='wr';load mysql users to runtime;save mysql users to disk;select * from mysql_users;
MySQL [(none)]> select * from mysql_users;+----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+---------+| username | password | active | use_ssl | default_hostgroup | default_schema | schema_locked | transaction_persistent | fast_forward | backend | frontend | max_connections | comment |+----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+---------+| wr | lhr | 1 | 0 | 10 | NULL | 0 | 1 | 0 | 1 | 1 | 10000 | |+----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+---------+1 row in set (0.05 sec)复制代码
3.4.4 配置监控
-- 4、在ProxySQL端执行下列SQL语句：set mysql-monitor_username='monitor';set mysql-monitor_password='monitor';load mysql servers to runtime;save mysql servers to disk;select * from global_variables where variable_name in('mysql-monitor_username','mysql-monitor_password');+------------------------+----------------+| variable_name | variable_value |+------------------------+----------------+| mysql-monitor_password | monitor || mysql-monitor_username | monitor |+------------------------+----------------+2 rows in set (0.05 sec)-- 检查连接到MySQL的日志select * from monitor.mysql_server_ping_log order by time_start_us desc limit 6;select * from monitor.mysql_server_connect_log order by time_start_us desc limit 6;MySQL [(none)]> select * from monitor.mysql_server_ping_log order by time_start_us desc limit 6;+----------------+------+------------------+----------------------+------------+| hostname | port | time_start_us | ping_success_time_us | ping_error |+----------------+------+------------------+----------------------+------------+| 192.168.68.132 | 3306 | 1614050308827202 | 252 | NULL || 192.168.68.133 | 3306 | 1614050308716530 | 370 | NULL || 192.168.68.131 | 3306 | 1614050308605853 | 542 | NULL || 192.168.68.131 | 3306 | 1614050298778908 | 334 | NULL || 192.168.68.133 | 3306 | 1614050298690947 | 297 | NULL || 192.168.68.132 | 3306 | 1614050298605725 | 344 | NULL |+----------------+------+------------------+----------------------+------------+6 rows in set (0.06 sec)
MySQL [(none)]> select * from monitor.mysql_server_connect_log order by time_start_us desc limit 6;+----------------+------+------------------+-------------------------+---------------+| hostname | port | time_start_us | connect_success_time_us | connect_error |+----------------+------+------------------+-------------------------+---------------+| 192.168.68.131 | 3306 | 1614050285481316 | 1173 | NULL || 192.168.68.133 | 3306 | 1614050284894846 | 1008 | NULL || 192.168.68.132 | 3306 | 1614050284309124 | 970 | NULL || 192.168.68.131 | 3306 | 1614050225194575 | 1108 | NULL || 192.168.68.133 | 3306 | 1614050224751771 | 987 | NULL || 192.168.68.132 | 3306 | 1614050224309026 | 1294 | NULL |+----------------+------+------------------+-------------------------+---------------+6 rows in set (0.05 sec)复制代码
四、故障切换
在 Manager 节点检查 SSH、复制及 MHA 的状态。
docker exec -it MHA-LHR-Monitor-ip134 bashmasterha_check_ssh --conf=/etc/mha/mha.cnfmasterha_check_repl --conf=/etc/mha/mha.cnfmasterha_check_status --conf=/etc/mha/mha.cnf
-- 启动MHA监控进程nohup masterha_manager --conf=/etc/mha/mha.cnf --ignore_last_failover < /dev/null > /usr/local/mha/manager_start.log 2>&1 &
--关闭MHA监控进程masterha_stop --conf=/etc/mha/mha.cnf[root@MHA-LHR-Monitor-ip134 /]# masterha_check_status --conf=/etc/mha/mha.cnfmha (pid:3738) is running(0:PING_OK), master:192.168.68.131复制代码
接下来，宕掉主库，继续观察 ProxySQL 的情况：
-- 宕掉主库 docker stop MHA-LHR-Master1-ip131复制代码
MHA 自动执行了故障转移，主库切换为 132，并发送告警邮件：
https://cdn.jsdelivr.net/gh/lhrbest/pic/img/20210416111828.png
此时，来查看 ProxySQL 的情况：
MySQL [(none)]> select * from mysql_servers;+--------------+----------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+| hostgroup_id | hostname | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |+--------------+----------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+| 10 | 192.168.68.132 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | || 20 | 192.168.68.131 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | || 20 | 192.168.68.133 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | || 20 | 192.168.68.132 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |+--------------+----------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+4 rows in set (0.05 sec)
MySQL [(none)]> select * from runtime_mysql_servers;+--------------+----------------+------+-----------+---------+--------+-------------+-----------------+---------------------+---------+----------------+---------+| hostgroup_id | hostname | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |+--------------+----------------+------+-----------+---------+--------+-------------+-----------------+---------------------+---------+----------------+---------+| 10 | 192.168.68.132 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | || 20 | 192.168.68.131 | 3306 | 0 | SHUNNED | 1 | 0 | 1000 | 0 | 0 | 0 | || 20 | 192.168.68.133 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | || 20 | 192.168.68.132 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |+--------------+----------------+------+-----------+---------+--------+-------------+-----------------+---------------------+---------+----------------+---------+4 rows in set (1.26 sec)复制代码
可以发现 131 已经变成 SHUNNED 状态，ProxySQL 会避开这个主机。
此时再做压测等操作，所有负载会被分配到 132 和 133 上，此处不再测试。
接下来启动 131，并以从库的身份加入原主从环境：
-- 启动131docker start MHA-LHR-Master1-ip131
-- 在134的日志文件中找到恢复的语句grep "All other slaves should start replication from here" /usr/local/mha/manager_running.log
-- 在131上执行恢复mysql -uroot -plhr -h192.168.68.131 -P3306CHANGE MASTER TO MASTER_HOST='192.168.68.132', MASTER_PORT=3306, MASTER_AUTO_POSITION=1,MASTER_USER='repl',MASTER_PASSWORD='lhr';
start slave;show slave status \G
-- 设置只读set global read_only=1;复制代码
查询 ProxySQL：
MySQL [(none)]> select * from mysql_servers;+--------------+----------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+| hostgroup_id | hostname | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |+--------------+----------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+| 10 | 192.168.68.132 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | || 20 | 192.168.68.131 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | || 20 | 192.168.68.133 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | || 20 | 192.168.68.132 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |+--------------+----------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+4 rows in set (0.06 sec)复制代码
可以看到 131 为只读。若想让 132 只写，则可以删除相关记录：
MySQL [(none)]> delete from mysql_servers where hostgroup_id=20 and hostname='192.168.68.132';Query OK, 1 row affected (0.06 sec)
MySQL [(none)]> load mysql servers to runtime;Query OK, 0 rows affected (0.68 sec)
MySQL [(none)]> save mysql servers to disk;Query OK, 0 rows affected (0.10 sec)
MySQL [(none)]> select * from mysql_servers;+--------------+----------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+| hostgroup_id | hostname | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |+--------------+----------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+| 10 | 192.168.68.132 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | || 20 | 192.168.68.131 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | || 20 | 192.168.68.133 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |+--------------+----------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+3 rows in set (0.05 sec)
MySQL [(none)]> select * from runtime_mysql_servers;+--------------+----------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+| hostgroup_id | hostname | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |+--------------+----------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+| 10 | 192.168.68.132 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | || 20 | 192.168.68.133 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | || 20 | 192.168.68.131 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |+--------------+----------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+3 rows in set (0.94 sec)复制代码
可以看到 132 为主库，131 和 133 为从库。Orchestrator 界面：
https://cdn.jsdelivr.net/gh/lhrbest/pic/img/20210416111835.png
划线
评论
复制
发布于: 刚刚阅读数: 2
版权声明: 本文为 InfoQ 作者【华为云开发者社区】的原创文章。
原文链接:【http://xie.infoq.cn/article/b938a5721c0eae8008e6959f4】。文章转载请联系作者。
华为云开发者社区
关注
提供全面深入的云计算技术干货 2020.07.14 加入
华为云开发者社区，提供全面深入的云计算前景分析、丰富的技术干货、程序样例，分享华为云前沿资讯动态，方便开发者快速成长与发展，欢迎提问、互动，多方位了解云计算！ 传送门：https://bbs.huaweicloud.com/
评论