MHA切换思路学习

最新推荐文章于 2021-09-27 16:47:02 发布
最新推荐文章于 2021-09-27 16:47:02 发布
阅读量175 收藏
点赞数
文章标签： mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-NC-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/knightnibo/article/details/112636608
版权

MHA作为MySQL经典的集群，一些思路值得学习，在此作对实验结论进行记录

拓扑
192.168.50.10 node1
192.168.50.11 node2
192.168.50.12 node3
192.168.50.13 master（manager）

  • 脑裂测试
Wed Jan 13 23:06:03 2021 - [info] Set master ping interval 1 seconds.
Wed Jan 13 23:06:03 2021 - [info] Set secondary check script: /usr/bin/masterha_secondary_check -s 192.168.50.10 -s 192.168.56.11 -s 192.168.50.10              
Wed Jan 13 23:06:03 2021 - [info] Starting ping health check on 192.168.50.10(192.168.50.10:3308)..
Wed Jan 13 23:06:03 2021 - [info] Ping(SELECT) succeeded, waiting until MySQL doesn't respond..

发现节点异常 开始进行检查
Wed Jan 13 23:09:24 2021 - [warning] Got error on MySQL select ping: 2006 (MySQL server has gone away)  
Wed Jan 13 23:09:24 2021 - [info] Executing SSH check script: exit 0

按照辅助检查规则依次从定义的节点去访问离开的主机
Wed Jan 13 23:09:25 2021 - [info] HealthCheck: SSH to 192.168.50.10 is reachable.
Monitoring server 192.168.50.10 is reachable, Master is not reachable from 192.168.50.10. OK.
Wed Jan 13 23:09:25 2021 - [warning] Got error on MySQL connect: 2003 (Can't connect to MySQL server on '192.168.50.10' (111))
Wed Jan 13 23:09:25 2021 - [warning] Connection failed 2 time(s)..
Wed Jan 13 23:09:26 2021 - [warning] Got error on MySQL connect: 2003 (Can't connect to MySQL server on '192.168.50.10' (111))
Wed Jan 13 23:09:26 2021 - [warning] Connection failed 3 time(s)..
Wed Jan 13 23:09:27 2021 - [warning] Got error on MySQL connect: 2003 (Can't connect to MySQL server on '192.168.50.10' (111))
Wed Jan 13 23:09:27 2021 - [warning] Connection failed 4 time(s)..
ssh: connect to host 192.168.56.11 port 22: Connection timed out^M
Monitoring server 192.168.56.11 is NOT reachable!

确认主机主机始终无法访问，此时无法确定离开集群的主机状态，判断为网络问题，跳过Faiilover避免脑裂
Wed Jan 13 23:09:30 2021 - [warning] At least one of monitoring servers is not reachable from this script. This is likely a network problem. Failover should not happen.
Wed Jan 13 23:09:30 2021 - [warning] Secondary network check script returned errors. Failover should not start so checking server status again. Check network settings for details.
Wed Jan 13 23:09:30 2021 - [warning] Got error on MySQL connect: 2003 (Can't connect to MySQL server on '192.168.50.10' (111))
Wed Jan 13 23:09:30 2021 - [warning] Connection failed 1 time(s)..
Wed Jan 13 23:09:30 2021 - [info] Executing SSH check script: exit 0
Wed Jan 13 23:09:31 2021 - [info] HealthCheck: SSH to 192.168.50.10 is reachable.
Monitoring server 192.168.50.10 is reachable, Master is not reachable from 192.168.50.10. OK.
Wed Jan 13 23:09:31 2021 - [warning] Got error on MySQL connect: 2003 (Can't connect to MySQL server on '192.168.50.10' (111))
Wed Jan 13 23:09:31 2021 - [warning] Connection failed 2 time(s)..
Wed Jan 13 23:09:32 2021 - [warning] Got error on MySQL connect: 2003 (Can't connect to MySQL server on '192.168.50.10' (111))
Wed Jan 13 23:09:32 2021 - [warning] Connection failed 3 time(s)..
Wed Jan 13 23:09:33 2021 - [warning] Got error on MySQL connect: 2003 (Can't connect to MySQL server on '192.168.50.10' (111))
Wed Jan 13 23:09:33 2021 - [warning] Connection failed 4 time(s)..
ssh: connect to host 192.168.56.11 port 22: Connection timed out^M

反复循环动作，尝试从辅助规则定义的主机去访问目标机器
Monitoring server 192.168.56.11 is NOT reachable!
Wed Jan 13 23:09:36 2021 - [warning] At least one of monitoring servers is not reachable from this script. This is likely a network problem. Failover should not happen.
Wed Jan 13 23:09:36 2021 - [warning] Secondary network check script returned errors. Failover should not start so checking server status again. Check network settings for details.
Wed Jan 13 23:09:36 2021 - [warning] Got error on MySQL connect: 2003 (Can't connect to MySQL server on '192.168.50.10' (111))
Wed Jan 13 23:09:36 2021 - [warning] Connection failed 1 time(s)..
Wed Jan 13 23:09:36 2021 - [info] Executing secondary network check script: /usr/bin/masterha_secondary_check -s 192.168.50.10 -s 192.168.56.11 -s 192.168.50.10  --user=root  --master_host=192.168.50.10  --master_ip=192.168.50.10  --master_port=3308 --master_user=dba_mha --master_password=123456 --ping_type=SELECT
Wed Jan 13 23:09:36 2021 - [info] Executing SSH check script: exit 0
Wed Jan 13 23:09:36 2021 - [info] HealthCheck: SSH to 192.168.50.10 is reachable.
Monitoring server 192.168.50.10 is reachable, Master is not reachable from 192.168.50.10. OK.

早期架构尚未采用的paxos/raft选举算法，理论上存在脑裂可能性，MHA在这点上以功能为牺牲，为安全性做出让步。

  • 自动切换
Thu Jan 14 04:27:42 2021 - [info] Set master ping interval 1 seconds.
Thu Jan 14 04:27:42 2021 - [info] Set secondary check script: /usr/bin/masterha_secondary_check -s 192.168.50.10 -s 192.168.50.11 -s 192.168.50.10
Thu Jan 14 04:27:42 2021 - [info] Starting ping health check on 192.168.50.10(192.168.50.10:3308)..
Thu Jan 14 04:27:42 2021 - [info] Ping(SELECT) succeeded, waiting until MySQL doesn't respond..

检测到节点异常
Thu Jan 14 04:28:04 2021 - [warning] Got error on MySQL select ping: 1053 (Server shutdown in progress)
Thu Jan 14 04:28:04 2021 - [info] Executing SSH check script: exit 0
Thu Jan 14 04:28:04 2021 - [info] HealthCheck: SSH to 192.168.50.10 is reachable.

SSH连通性正常
Monitoring server 192.168.50.10 is reachable, Master is not reachable from 192.168.50.10. OK.
Monitoring server 192.168.50.11 is reachable, Master is not reachable from 192.168.50.11. OK.

MySQL连通性异常
Thu Jan 14 04:28:05 2021 - [warning] Got error on MySQL connect: 2003 (Can't connect to MySQL server on '192.168.50.10' (111))
Thu Jan 14 04:28:05 2021 - [warning] Connection failed 2 time(s)..
Monitoring server 192.168.50.10 is reachable, Master is not reachable from 192.168.50.10. OK.
Thu Jan 14 04:28:05 2021 - [info] Master is not reachable from all other monitoring servers. Failover should start.
Thu Jan 14 04:28:06 2021 - [warning] Got error on MySQL connect: 2003 (Can't connect to MySQL server on '192.168.50.10' (111))
Thu Jan 14 04:28:06 2021 - [warning] Connection failed 3 time(s)..
Thu Jan 14 04:28:07 2021 - [warning] Got error on MySQL connect: 2003 (Can't connect to MySQL server on '192.168.50.10' (111))
Thu Jan 14 04:28:07 2021 - [warning] Connection failed 4 time(s)..
Thu Jan 14 04:28:07 2021 - [warning] Master is not reachable from health checker!
Thu Jan 14 04:28:07 2021 - [warning] Master 192.168.50.10(192.168.50.10:3308) is not reachable!
Thu Jan 14 04:28:07 2021 - [warning] SSH is reachable.
Thu Jan 14 04:28:07 2021 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Thu Jan 14 04:28:07 2021 - [info] Reading application default configuration from /home/mha/mysql-mha.conf..
Thu Jan 14 04:28:07 2021 - [info] Reading server configuration from /home/mha/mysql-mha.conf..
Thu Jan 14 04:28:08 2021 - [info] GTID failover mode = 1

判断节点死亡
Thu Jan 14 04:28:08 2021 - [info] Dead Servers:
Thu Jan 14 04:28:08 2021 - [info]   192.168.50.10(192.168.50.10:3308)
Thu Jan 14 04:28:08 2021 - [info] Alive Servers:
Thu Jan 14 04:28:08 2021 - [info]   192.168.50.11(192.168.50.11:3308)
Thu Jan 14 04:28:08 2021 - [info]   192.168.50.12(192.168.50.12:3308)

存活节点选举
Thu Jan 14 04:28:08 2021 - [info] Alive Slaves:
Thu Jan 14 04:28:08 2021 - [info]   192.168.50.11(192.168.50.11:3308)  Version=8.0.22-13 (oldest major version between slaves) log-bin:enabled
Thu Jan 14 04:28:08 2021 - [info]     GTID ON
Thu Jan 14 04:28:08 2021 - [info]     Replicating from 192.168.50.10(192.168.50.10:3308)
Thu Jan 14 04:28:08 2021 - [info]   192.168.50.12(192.168.50.12:3308)  Version=8.0.22-13 (oldest major version between slaves) log-bin:enabled
Thu Jan 14 04:28:08 2021 - [info]     GTID ON
Thu Jan 14 04:28:08 2021 - [info]     Replicating from 192.168.50.10(192.168.50.10:3308)
Thu Jan 14 04:28:08 2021 - [info] Checking slave configurations..
Thu Jan 14 04:28:08 2021 - [info]  read_only=1 is not set on slave 192.168.50.12(192.168.50.12:3308).
Thu Jan 14 04:28:08 2021 - [info] Checking replication filtering settings..
Thu Jan 14 04:28:08 2021 - [info]  Replication filtering check ok.
Thu Jan 14 04:28:08 2021 - [info] Master is down!
Thu Jan 14 04:28:08 2021 - [info] Terminating monitoring script.
Thu Jan 14 04:28:08 2021 - [info] Got exit code 20 (Master dead).
Thu Jan 14 04:28:08 2021 - [info] MHA::MasterFailover version 0.57.

开始新主切换
Thu Jan 14 04:28:08 2021 - [info] Starting master failover.
Thu Jan 14 04:28:08 2021 - [info]
Thu Jan 14 04:28:08 2021 - [info] * Phase 1: Configuration Check Phase..
Thu Jan 14 04:28:08 2021 - [info]
Thu Jan 14 04:28:09 2021 - [info] GTID failover mode = 1
Thu Jan 14 04:28:09 2021 - [info] Dead Servers:
Thu Jan 14 04:28:09 2021 - [info]   192.168.50.10(192.168.50.10:3308)
Thu Jan 14 04:28:09 2021 - [info] Checking master reachability via MySQL(double check)...
Thu Jan 14 04:28:09 2021 - [info]  ok.
Thu Jan 14 04:28:09 2021 - [info] Alive Servers:
Thu Jan 14 04:28:09 2021 - [info]   192.168.50.11(192.168.50.11:3308)
Thu Jan 14 04:28:09 2021 - [info]   192.168.50.12(192.168.50.12:3308)
Thu Jan 14 04:28:09 2021 - [info] Alive Slaves:
Thu Jan 14 04:28:09 2021 - [info]   192.168.50.11(192.168.50.11:3308)  Version=8.0.22-13 (oldest major version between slaves) log-bin:enabled
Thu Jan 14 04:28:09 2021 - [info]     GTID ON
Thu Jan 14 04:28:09 2021 - [info]     Replicating from 192.168.50.10(192.168.50.10:3308)
Thu Jan 14 04:28:09 2021 - [info]   192.168.50.12(192.168.50.12:3308)  Version=8.0.22-13 (oldest major version between slaves) log-bin:enabled
Thu Jan 14 04:28:09 2021 - [info]     GTID ON
Thu Jan 14 04:28:09 2021 - [info]     Replicating from 192.168.50.10(192.168.50.10:3308)

基于GTID进行切换
Thu Jan 14 04:28:09 2021 - [info] Starting GTID based failover.
Thu Jan 14 04:28:09 2021 - [info]
Thu Jan 14 04:28:09 2021 - [info] ** Phase 1: Configuration Check Phase completed.
Thu Jan 14 04:28:09 2021 - [info]
Thu Jan 14 04:28:09 2021 - [info] * Phase 2: Dead Master Shutdown Phase..
Thu Jan 14 04:28:09 2021 - [info]

Thu Jan 14 04:28:09 2021 - [info]   /usr/bin/master_ip_failover --orig_master_host=192.168.50.10 --orig_master_ip=192.168.50.10 --orig_master_port=3308 --command=stopssh --ssh_user=root

移除旧主上VIP
Disabling the VIP on old master: 192.168.50.10
Thu Jan 14 04:28:10 2021 - [info]  done.
Thu Jan 14 04:28:10 2021 - [warning] shutdown_script is not set. Skipping explicit shutting down of the dead master.
Thu Jan 14 04:28:10 2021 - [info] * Phase 2: Dead Master Shutdown Phase completed.
Thu Jan 14 04:28:10 2021 - [info]
Thu Jan 14 04:28:10 2021 - [info] * Phase 3: Master Recovery Phase..
Thu Jan 14 04:28:10 2021 - [info]
Thu Jan 14 04:28:10 2021 - [info] * Phase 3.1: Getting Latest Slaves Phase..
Thu Jan 14 04:28:10 2021 - [info]

比较slave中较新位置
Thu Jan 14 04:28:10 2021 - [info] The latest binary log file/position on all slaves is master-binlog.000006:948
Thu Jan 14 04:28:10 2021 - [info] Latest slaves (Slaves that received relay log files to the latest):
Thu Jan 14 04:28:10 2021 - [info]   192.168.50.11(192.168.50.11:3308)  Version=8.0.22-13 (oldest major version between slaves) log-bin:enabled
Thu Jan 14 04:28:10 2021 - [info]     GTID ON
Thu Jan 14 04:28:10 2021 - [info]     Replicating from 192.168.50.10(192.168.50.10:3308)
Thu Jan 14 04:28:10 2021 - [info]   192.168.50.12(192.168.50.12:3308)  Version=8.0.22-13 (oldest major version between slaves) log-bin:enabled
Thu Jan 14 04:28:10 2021 - [info]     GTID ON
Thu Jan 14 04:28:10 2021 - [info]     Replicating from 192.168.50.10(192.168.50.10:3308)
Thu Jan 14 04:28:10 2021 - [info] The oldest binary log file/position on all slaves is master-binlog.000006:948
Thu Jan 14 04:28:10 2021 - [info] Oldest slaves:
Thu Jan 14 04:28:10 2021 - [info]   192.168.50.11(192.168.50.11:3308)  Version=8.0.22-13 (oldest major version between slaves) log-bin:enabled
Thu Jan 14 04:28:10 2021 - [info]     GTID ON
Thu Jan 14 04:28:10 2021 - [info]     Replicating from 192.168.50.10(192.168.50.10:3308)
Thu Jan 14 04:28:10 2021 - [info]   192.168.50.12(192.168.50.12:3308)  Version=8.0.22-13 (oldest major version between slaves) log-bin:enabled
Thu Jan 14 04:28:10 2021 - [info]     GTID ON
Thu Jan 14 04:28:10 2021 - [info]     Replicating from 192.168.50.10(192.168.50.10:3308)
Thu Jan 14 04:28:10 2021 - [info]

决定新主
Thu Jan 14 04:28:10 2021 - [info] * Phase 3.3: Determining New Master Phase..
Thu Jan 14 04:28:10 2021 - [info]
Thu Jan 14 04:28:10 2021 - [info] Searching new master from slaves..
Thu Jan 14 04:28:10 2021 - [info]  Candidate masters from the configuration file:
Thu Jan 14 04:28:10 2021 - [info]  Non-candidate masters:

选举成功
Thu Jan 14 04:28:10 2021 - [info] New master is 192.168.50.11(192.168.50.11:3308)

开始切换
Thu Jan 14 04:28:10 2021 - [info] Starting master failover..
Thu Jan 14 04:28:10 2021 - [info]
From:
192.168.50.10(192.168.50.10:3308) (current master)
 +--192.168.50.11(192.168.50.11:3308)
 +--192.168.50.12(192.168.50.12:3308)

To:
192.168.50.11(192.168.50.11:3308) (new master)
 +--192.168.50.12(192.168.50.12:3308)
Thu Jan 14 04:28:10 2021 - [info]

等待新主将relaylog应用完
Thu Jan 14 04:28:10 2021 - [info] * Phase 3.3: New Master Recovery Phase..
Thu Jan 14 04:28:10 2021 - [info]
Thu Jan 14 04:28:10 2021 - [info]  Waiting all logs to be applied..
Thu Jan 14 04:28:10 2021 - [info]   done.
Thu Jan 14 04:28:10 2021 - [info] Getting new master's binlog name and position..

获取新主的binlog位置
Thu Jan 14 04:28:10 2021 - [info]  slave-binlog.000019:236
Thu Jan 14 04:28:10 2021 - [info] Master Recovery succeeded. File:Pos:Exec_Gtid_Set: slave-binlog.000019, 236, 313251f3-55ea-11eb-ac1c-080027a042d7:1-5,
86b47f1b-55ba-11eb-aeb3-0800277dfa3f:1
Thu Jan 14 04:28:10 2021 - [info] Executing master IP activate script:
Option new_master_user does not take an argument
Option new_master_password does not take an argument


IN SCRIPT TEST====root|/sbin/ifconfig enp0s3:1 down==root|/sbin/ifconfig enp0s3:1 192.168.50.110/24===

在新主上启用VIP
Enabling the VIP - 192.168.50.110/24 on the new master - 192.168.50.11
bind: Cannot assign requested address
Thu Jan 14 04:28:10 2021 - [info]  OK.

设置新主可写
Thu Jan 14 04:28:10 2021 - [info] Setting read_only=0 on 192.168.50.11(192.168.50.11:3308)..
Thu Jan 14 04:28:10 2021 - [info]  ok.
Thu Jan 14 04:28:10 2021 - [info] ** Finished master recovery successfully.
Thu Jan 14 04:28:10 2021 - [info] * Phase 3: Master Recovery Phase completed.
Thu Jan 14 04:28:10 2021 - [info]
Thu Jan 14 04:28:10 2021 - [info] * Phase 4: Slaves Recovery Phase..
Thu Jan 14 04:28:10 2021 - [info]
Thu Jan 14 04:28:10 2021 - [info]
Thu Jan 14 04:28:10 2021 - [info] * Phase 4.1: Starting Slaves in parallel..
Thu Jan 14 04:28:10 2021 - [info]

等待slave应用未完成的relaylog
Thu Jan 14 04:28:10 2021 - [info] -- Slave recovery on host 192.168.50.12(192.168.50.12:3308) started, pid: 2199. Check tmp log /home/mha/192.168.50.12_3308_20210114042808.log if it takes time..
Thu Jan 14 04:30:06 2021 - [info]
Thu Jan 14 04:30:06 2021 - [info] Log messages from 192.168.50.12 ...
Thu Jan 14 04:30:06 2021 - [info]

执行reset slave 和change master指向新主
Thu Jan 14 04:28:10 2021 - [info]  Resetting slave 192.168.50.12(192.168.50.12:3308) and starting replication from the new master 192.168.50.11(192.168.50.11:3308)..
Thu Jan 14 04:28:10 2021 - [info]  Executed CHANGE MASTER.
Thu Jan 14 04:30:06 2021 - [info]  Slave started.

通过gtid_wait确认追平位置
Thu Jan 14 04:30:06 2021 - [info]  gtid_wait(313251f3-55ea-11eb-ac1c-080027a042d7:1-5,
86b47f1b-55ba-11eb-aeb3-0800277dfa3f:1) completed on 192.168.50.12(192.168.50.12:3308). Executed 0 events.
Thu Jan 14 04:30:06 2021 - [info] End of log messages from 192.168.50.12.
Thu Jan 14 04:30:06 2021 - [info] -- Slave on host 192.168.50.12(192.168.50.12:3308) started.
Thu Jan 14 04:30:06 2021 - [info] All new slave servers recovered successfully.
Thu Jan 14 04:30:06 2021 - [info]

Thu Jan 14 04:30:06 2021 - [info] * Phase 5: New master cleanup phase..
Thu Jan 14 04:30:06 2021 - [info]

对新主reset slave清理已经无用的relaylog
Thu Jan 14 04:30:06 2021 - [info] Resetting slave info on the new master..
Thu Jan 14 04:30:06 2021 - [info]  192.168.50.11: Resetting slave info succeeded.
Thu Jan 14 04:30:06 2021 - [info] Master failover to 192.168.50.11(192.168.50.11:3308) completed successfully.
Thu Jan 14 04:30:06 2021 - [info]

切换报告信息
----- Failover Report -----

mysql-mha: MySQL Master failover 192.168.50.10(192.168.50.10:3308) to 192.168.50.11(192.168.50.11:3308) succeeded

Master 192.168.50.10(192.168.50.10:3308) is down!

Check MHA Manager logs at master.localdomain:/home/mha/manager.log for details.

Started automated(non-interactive) failover.
Invalidated master IP address on 192.168.50.10(192.168.50.10:3308)
Selected 192.168.50.11(192.168.50.11:3308) as a new master.
192.168.50.11(192.168.50.11:3308): OK: Applying all logs succeeded.
192.168.50.11(192.168.50.11:3308): OK: Activated master IP address.
192.168.50.12(192.168.50.12:3308): OK: Slave started, replicating from 192.168.50.11(192.168.50.11:3308)
192.168.50.11(192.168.50.11:3308): Resetting slave info succeeded.
Master failover to 192.168.50.11(192.168.50.11:3308) completed successfully.
  • 手工切换测试
[root@master mha]# masterha_master_switch --master_state=alive --conf=/home/mha/mysql-mha.conf --new_master_host=192.168.50.11 --new_master_port=3308 --orig_master_is_new_slave
Thu Jan 14 04:55:44 2021 - [info] MHA::MasterRotate version 0.57.
Thu Jan 14 04:55:44 2021 - [info] Starting online master switch..
Thu Jan 14 04:55:44 2021 - [info] 
Thu Jan 14 04:55:44 2021 - [info] * Phase 1: Configuration Check Phase..
Thu Jan 14 04:55:44 2021 - [info] 
Thu Jan 14 04:55:44 2021 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Thu Jan 14 04:55:44 2021 - [info] Reading application default configuration from /home/mha/mysql-mha.conf..           
Thu Jan 14 04:55:44 2021 - [info] Reading server configuration from /home/mha/mysql-mha.conf..          
Thu Jan 14 04:55:45 2021 - [warning] SQL Thread is stopped(no error) on 192.168.50.12(192.168.50.12:3308)           
Thu Jan 14 04:55:45 2021 - [info] Multi-master configuration is detected. Current primary(writable) master is 192.168.50.12(192.168.50.12:3308)  
Thu Jan 14 04:55:45 2021 - [info] Master configurations are as below: 
Master 192.168.50.11(192.168.50.11:3308), replicating from 192.168.50.12(192.168.50.12:3308), read-only      
Master 192.168.50.12(192.168.50.12:3308), replicating from 192.168.50.11(192.168.50.11:3308)

Thu Jan 14 04:55:45 2021 - [info] GTID failover mode = 1            

检测当前各节点复制信息
Thu Jan 14 04:55:45 2021 - [info] Current Alive Master: 192.168.50.12(192.168.50.12:3308)        
Thu Jan 14 04:55:45 2021 - [info] Alive Slaves:
Thu Jan 14 04:55:45 2021 - [info]   192.168.50.10(192.168.50.10:3308)  Version=8.0.22-13 (oldest major version between slaves) log-bin:enabled   
Thu Jan 14 04:55:45 2021 - [info]     GTID ON
Thu Jan 14 04:55:45 2021 - [info]     Replicating from 192.168.50.12(192.168.50.12:3308)
Thu Jan 14 04:55:45 2021 - [info]   192.168.50.11(192.168.50.11:3308)  Version=8.0.22-13 (oldest major version between slaves) log-bin:enabled  
Thu Jan 14 04:55:45 2021 - [info]     GTID ON
Thu Jan 14 04:55:45 2021 - [info]     Replicating from 192.168.50.12(192.168.50.12:3308)

在原主节点执行 FLUSH TABLES（NO_WRITE_TO_BINLOG) 刷新数据并校验复制是否正常
It is better to execute FLUSH NO_WRITE_TO_BINLOG TABLES on the master before switching. Is it ok to execute on 192.168.50.12(192.168.50.12:3308)? (YES/no): yes
Thu Jan 14 04:55:48 2021 - [info] Executing FLUSH NO_WRITE_TO_BINLOG TABLES. This may take long time..
Thu Jan 14 04:55:48 2021 - [info]  ok.

检测MHA不在执行 并且没有其他failover操作
Thu Jan 14 04:55:48 2021 - [info] Checking MHA is not monitoring or doing failover..   
Thu Jan 14 04:55:48 2021 - [info] Checking replication health on 192.168.50.10..
Thu Jan 14 04:55:48 2021 - [info]  ok.
Thu Jan 14 04:55:48 2021 - [info] Checking replication health on 192.168.50.11..
Thu Jan 14 04:55:48 2021 - [info]  ok.

复制检测正常 确认11可以成为新主
Thu Jan 14 04:55:48 2021 - [info] 192.168.50.11 can be new master.            
Thu Jan 14 04:55:48 2021 - [info] 
From:
192.168.50.12(192.168.50.12:3308) (current master)
 +--192.168.50.10(192.168.50.10:3308)
 +--192.168.50.11(192.168.50.11:3308)

To:
192.168.50.11(192.168.50.11:3308) (new master)
 +--192.168.50.10(192.168.50.10:3308)
 +--192.168.50.12(192.168.50.12:3308)

确认执行切换操作
Starting master switch from 192.168.50.12(192.168.50.12:3308) to 192.168.50.11(192.168.50.11:3308)? (yes/NO): yes
Thu Jan 14 04:55:49 2021 - [info] Checking whether 192.168.50.11(192.168.50.11:3308) is ok for the new master..
Thu Jan 14 04:55:49 2021 - [info]  ok.
Thu Jan 14 04:55:49 2021 - [info] ** Phase 1: Configuration Check Phase completed.
Thu Jan 14 04:55:49 2021 - [info] 
Thu Jan 14 04:55:49 2021 - [info] * Phase 2: Rejecting updates Phase..
Thu Jan 14 04:55:49 2021 - [info] 

通过手动脚本处理VI转移，也可以切换开始前先移除，最后完成后手动加上，同样切断应用连接避免操作
master_ip_online_change_script is not defined. If you do not disable writes on the current master manually, applications keep writing on the current master. Is it ok to proceed? (yes/NO): yes

通过FLUSH TABLES WITH READ LOCK 锁住原主库表 并且获取此时binlog 位置
Thu Jan 14 04:55:51 2021 - [info] Locking all tables on the orig master to reject updates from everybody (including root):
Thu Jan 14 04:55:51 2021 - [info] Executing FLUSH TABLES WITH READ LOCK..
Thu Jan 14 04:55:51 2021 - [info]  ok.
Thu Jan 14 04:55:51 2021 - [info] Orig master binlog:pos is slave-binlog.000009:587.

准备成为新主的slave上通过master_pos_wait()判断binlog应用到指定位置
Thu Jan 14 04:55:51 2021 - [info]  Waiting to execute all relay logs on 192.168.50.11(192.168.50.11:3308)..
Thu Jan 14 04:55:51 2021 - [info]  master_pos_wait(slave-binlog.000009:587) completed on 192.168.50.11(192.168.50.11:3308). Executed 0 events.
Thu Jan 14 04:55:51 2021 - [info]   done.

获取新主上binlog名字和位置 供其他节点change
Thu Jan 14 04:55:51 2021 - [info] Getting new master's binlog name and position..
Thu Jan 14 04:55:51 2021 - [info]  slave-binlog.000020:641

根据配置文件中信息和提取的新节点位置 组成change语句
Thu Jan 14 04:55:51 2021 - [info]  All other slaves should start replication from here. Statement should be: CHANGE MASTER TO MASTER_HOST='192.168.50.11', MASTER_PORT=3308, MASTER_AUTO_POSITION=1, MASTER_USER='repluser', MASTER_PASSWORD='xxx';

将新主节点read_only设置为0 允许写入
Thu Jan 14 04:55:51 2021 - [info] Setting read_only=0 on 192.168.50.11(192.168.50.11:3308)..
Thu Jan 14 04:55:51 2021 - [info]  ok.
Thu Jan 14 04:55:51 2021 - [info] 

并发操作旧主以外从节点  通过master_pos_wait()确认其应用完旧主上的binlog位置
Thu Jan 14 04:55:51 2021 - [info] * Switching slaves in parallel..
Thu Jan 14 04:55:51 2021 - [info] 
Thu Jan 14 04:55:51 2021 - [info] -- Slave switch on host 192.168.50.10(192.168.50.10:3308) started, pid: 3615
Thu Jan 14 04:55:51 2021 - [info] 
Thu Jan 14 04:55:52 2021 - [info] Log messages from 192.168.50.10 ...
Thu Jan 14 04:55:52 2021 - [info] 
Thu Jan 14 04:55:51 2021 - [info]  Waiting to execute all relay logs on 192.168.50.10(192.168.50.10:3308)..
Thu Jan 14 04:55:51 2021 - [info]  master_pos_wait(slave-binlog.000009:587) completed on 192.168.50.10(192.168.50.10:3308). Executed 0 events.
Thu Jan 14 04:55:51 2021 - [info]   done.

对旧主以外的从节点执行reset slave清理relaylog 并change master指向新的主
Thu Jan 14 04:55:51 2021 - [info]  Resetting slave 192.168.50.10(192.168.50.10:3308) and starting replication from the new master 192.168.50.11(192.168.50.11:3308)..
Thu Jan 14 04:55:51 2021 - [info]  Executed CHANGE MASTER.
Thu Jan 14 04:55:51 2021 - [info]  Slave started.
Thu Jan 14 04:55:52 2021 - [info] End of log messages from 192.168.50.10 ...
Thu Jan 14 04:55:52 2021 - [info] 
Thu Jan 14 04:55:52 2021 - [info] -- Slave switch on host 192.168.50.10(192.168.50.10:3308) succeeded.

释放旧主上的READ LOCK
Thu Jan 14 04:55:52 2021 - [info] Unlocking all tables on the orig master:
Thu Jan 14 04:55:52 2021 - [info] Executing UNLOCK TABLES..
Thu Jan 14 04:55:52 2021 - [info]  ok.
Thu Jan 14 04:55:52 2021 - [info] Starting orig master as a new slave..

对旧主执行reset slave清理relaylog 并change mater指向新主
Thu Jan 14 04:55:52 2021 - [info]  Resetting slave 192.168.50.12(192.168.50.12:3308) and starting replication from the new master 192.168.50.11(192.168.50.11:3308)..
Thu Jan 14 04:55:52 2021 - [info]  Executed CHANGE MASTER.
Thu Jan 14 04:55:52 2021 - [info]  Slave started.
Thu Jan 14 04:55:52 2021 - [info] All new slave servers switched successfully.
Thu Jan 14 04:55:52 2021 - [info] 
Thu Jan 14 04:55:52 2021 - [info] * Phase 5: New master cleanup phase..
Thu Jan 14 04:55:52 2021 - [info] 

清理新主slave信息
Thu Jan 14 04:55:52 2021 - [info]  192.168.50.11: Resetting slave info succeeded.
Thu Jan 14 04:55:52 2021 - [info] Switching master to 192.168.50.11(192.168.50.11:3308) completed successfully.
-守仁-
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 0
    评论
MHA实现mysql主从数据库手动切换的方法
09-10
主要介绍了MHA实现mysql主从数据库手动切换的方法,详细的讲述了主从数据库切换的步骤与方法,非常实用,需要的朋友可以参考下
mysql mha搭建以及故障切换.
07-07
mysql mha搭建以及故障切换.
mysql切换新的日志_MySQL--19 MHA切换日志分析
weixin_29802461的博客
01-27 325
MHA切换检测日志分析GTID模式[root@db03 ~]# tail -f /etc/mha/manager.log#在MySQL select ping:2006上出错(MySQL服务器已消失)Tue Nov 19 17:52:00 2019 - [warning] Got error on MySQL select ping: 2006 (MySQL server has gone awa...
MHA高可用
小胡子
07-25 125
manager 组件 masterha_manger # 启动MHA masterha_check_ssh # 检查MHA的SSH配置状况 masterha_check_repl # 检查MySQL复制状况，配置信息 masterha_master_monitor # 检测master是否宕机 masterha_check_status # 检测当前MHA运行状态 masterha_master_switch #.
MHA 在线切换过程
乐沙弥的世界
04-22 6543
MySQL MHA 在线切换是MHA除了自动监控切换换提供的另外一种方式，多用于诸如硬件升级，MySQL数据库迁移等等。该方式提供快速切换和优雅的阻塞写入，无关关闭原有服务器，整个切换过程在0.5-2s 的时间左右，大大减少了停机时间。本文演示了MHA 在线切换并给出了在线切换的基本步骤......
MySQL高可用系列之MHA（二）
dba成长之路
07-29 2651
参数说明 MHA提供了一系列配置参数，深入理解每个参数的具体含义，对优化配置、合理使用MHA非常重要，很多高可用性也都是通过合理配置一些参数而实现的。 MHA包括如下配置参数，分别说明如下： hostname/ip/port (Local Only) hostname为MySQL Server的IP地址或主机名； ip为MySQL Server的IP地址，缺省从$hostname中获取；
At least one of monitoring servers is not reachable from this script
weixin_34208283的博客
09-08 217
Fri Sep 8 12:43:01 2017 - [info] Executing SSH check script: save_binary_logs --command=test --start_pos=4 --binlog_dir=/data/binlog/,/var/lib/mysql,/var/log/mysql --output_file=/tmp/save_...
mysql-MHA高可用
10-12
mha4mysql-node-0.56-0.el6.noarch.rpm mha4mysql-manager-0.56-0.el6.noarch.rpm
mha依赖安装包.rar
06-22
mha依赖安装包.rar mha安装步骤见https://blog.csdn.net/u013091109/article/details/124561380
mysql 高可用架构 mha 之二 master_ip_failover
一名数据库爱好者的专栏
08-07 2624
os:centos 7.4 mysql: 5.7 mha: 0.58 上一篇blog介绍了 mha 的安装，这一篇blog介绍下 master_ip_failover_script 的设置。 ip 规划如下： 192.168.56.101 node1 (mha manager) 192.168.56.102 node2 (mysql master) 192.168.56.103 no...
At least one of monitoring servers is not reachable from this script.
weixin_33672109的博客
09-08 435
secondary_check_script= /usr/local/bin/masterha_secondary_check -s 172.17.0.2 -s 172.17.0.3 -s 172.17.0.4 -s 172.17.0.5Fri Sep 8 12:43:01 2017 - [info] Executing SSH check script: save_bin...
MHA高可用部署及测试
11-02 2348
MHA特性 主服务器的自动监控和故障转移 MHA监控复制架构的主服务器，一旦检测到主服务器故障，就会自动进行故障转移。即使有些从服务器没有收到最新的relay log，MHA自动从最新的从服务器上识别差异的relay log并把这些日志应用到其他从服务器上，因此所有的从服务器保持一致性了。MHA通常在几秒内完成故障转移，9-12秒可以检测出主服务器故障，7-10秒内关闭故障的主服务器以避免
MySQL:MHA测试
Super菜的专栏
08-08 3008
测试配置： [server1] hostname=db42 candidate_master=1 check_repl_delay=0 port=3307 [server2] hostname=dbo_100_241  candidate_master=1 check_repl_delay=0 port=3307 [server3] hostname=db121
MySQL高可用自动故障恢复笔记（MHA）
xiaohu的博客
09-27 777
一、MHA架构 MHA 可以在几秒钟内完成故障转移（9-12 秒检测 master 故障，可选 7-10 秒关闭 master 机器以避免裂脑，几秒钟将差分中继日志应用到新 master，因此总停机时间为一般10-30秒），只要slave不严重延迟复制。在恢复新的 master 后，MHA 并行恢复其余的 slave。即使你有几十个slave，也不影响master恢复时间，你可以很快恢复slave。 在 150 多个 {master, slaves} 环境中使用 MHA。当其中一个主节点崩溃时，MHA
mysql mha故障切换_关于MHA故障切换的问题
weixin_34984235的博客
01-19 654
老师,我根据视频,配置好了 1主2从的 3台mysql服务器,ip 为 192.168.200.1 ,192.168.200.2 ,192.168.200.3还有一台单独弄成管理服务 192.168.200.100 ,总共4台虚拟机~主从配置也配好了, 主为192.168.200.1 从为剩下的两台MHA也都按照要求所有依赖包都安装了,ssh 账号我没有使用root ,用的当前账号va...
MHA参数的理解简译
wulantian的专栏
10-09 6388
MHA参数的翻译，在网上找了很多，都没有具体的，只有原英文版的，自己就简单的翻译总结了一下，储备！ //MHA参数的理解简译 #by coco #2013-10-09 1. candidate_master     //你可以使用不同类型的机器在slaves之间,想要促进最可靠的机器到新master(即促进RAID1 + 0的slave,而不是恢复slave). 通过设置 candida
MHA（二）安装配置
一条大河
04-24 766
1， 设置免密登陆 server1，server2,server3, monitor 4个节点都执行ssh-keygen -t rsa 生成私钥； ssh-keygen -t rsa 将各自的私钥分发给其他节点；各自的机器分发给另外3台机器即可； ssh-copy-id -i ~/.ssh/id_rsa.pub [email protected] ssh-c...
MHA故障切换VIP漂移错误
最新发布
07-14
对于MHA（Master High Availability）的故障切换和VIP（Virtual IP）漂移错误，可能有几种常见原因和解决方法。以下是一些可能的原因和对应的解决方法： 1. 配置错误：请检查MHA的配置文件是否正确设置了主从关系、...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

-守仁-

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值