原 【DB宝42】MySQL高可用架构MHA+ProxySQL实现读写分离和负载均衡
Tags： 原创MySQL高可用监控主从复制MHA读写分离负载均衡ProxySQL
👉 本文共约2585个字，系统预计阅读时间或需10分钟。
六、故障切换
在Manager节点检查SSH、复制及MHA的状态。
1 2 3 4 5 6 7 8 9 10 11 12 13 | docker exec -it MHA-LHR-Monitor-ip134 bash masterha_check_ssh --conf=/etc/mha/mha.cnf masterha_check_repl --conf=/etc/mha/mha.cnf masterha_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.cnf mha (pid:3738) is running(0:PING_OK), master:192.168.68.131 |
接下来，宕掉主库，继续观察ProxySQL的情况：
1 2 | -- 宕掉主库 docker stop MHA-LHR-Master1-ip131 |
MHA自动执行了故障转移，主库切换为132，并发送告警邮件：
此时，来查看ProxySQL的情况：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | 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，并以从库的身份加入原主从环境：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | -- 启动131 docker 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 -P3306 CHANGE 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：
本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信dbaup66，谢谢！
