原 【DB宝19】在Docker中使用MySQL高可用之MHA
3.2.4 启动131,恢复131为备库
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | # 启动131 docker start MHA-LHR-Master1-ip131 # 在134的日志文件中找到恢复的语句 grep "All other slaves should start replication from here" /usr/local/mha/manager_running.log # 在131上执行恢复 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; # 在134上检查 masterha_check_repl --conf=/etc/mha/mha.cnf |
执行过程:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 | [root@MHA-LHR-Monitor-ip134 /]# grep "All other slaves should start replication from here" /usr/local/mha/manager_running.log Mon Jun 15 14:16:31 2020 - [info] All other slaves should start replication from here. Statement should be: CHANGE MASTER TO MASTER_HOST='192.168.68.132', MASTER_PORT=3306, MASTER_AUTO_POSITION=1, MASTER_USER='repl', MASTER_PASSWORD='xxx'; Sat Aug 8 11:01:30 2020 - [info] All other slaves should start replication from here. Statement should be: CHANGE MASTER TO MASTER_HOST='192.168.68.132', MASTER_PORT=3306, MASTER_AUTO_POSITION=1, MASTER_USER='repl', MASTER_PASSWORD='xxx'; [root@MHA-LHR-Monitor-ip134 /]# [root@MHA-LHR-Monitor-ip134 /]# masterha_check_repl --conf=/etc/mha/mha.cnf Sat Aug 8 11:23:30 2020 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping. Sat Aug 8 11:23:30 2020 - [info] Reading application default configuration from /etc/mha/mha.cnf.. Sat Aug 8 11:23:30 2020 - [info] Reading server configuration from /etc/mha/mha.cnf.. Sat Aug 8 11:23:30 2020 - [info] MHA::MasterMonitor version 0.58. Sat Aug 8 11:23:32 2020 - [info] GTID failover mode = 1 Sat Aug 8 11:23:32 2020 - [info] Dead Servers: Sat Aug 8 11:23:32 2020 - [info] Alive Servers: Sat Aug 8 11:23:32 2020 - [info] 192.168.68.131(192.168.68.131:3306) Sat Aug 8 11:23:32 2020 - [info] 192.168.68.132(192.168.68.132:3306) Sat Aug 8 11:23:32 2020 - [info] 192.168.68.133(192.168.68.133:3306) Sat Aug 8 11:23:32 2020 - [info] Alive Slaves: Sat Aug 8 11:23:32 2020 - [info] 192.168.68.131(192.168.68.131:3306) Version=5.7.30-log (oldest major version between slaves) log-bin:enabled Sat Aug 8 11:23:32 2020 - [info] GTID ON Sat Aug 8 11:23:32 2020 - [info] Replicating from 192.168.68.132(192.168.68.132:3306) Sat Aug 8 11:23:32 2020 - [info] 192.168.68.133(192.168.68.133:3306) Version=5.7.30-log (oldest major version between slaves) log-bin:enabled Sat Aug 8 11:23:32 2020 - [info] GTID ON Sat Aug 8 11:23:32 2020 - [info] Replicating from 192.168.68.132(192.168.68.132:3306) Sat Aug 8 11:23:32 2020 - [info] Current Alive Master: 192.168.68.132(192.168.68.132:3306) Sat Aug 8 11:23:32 2020 - [info] Checking slave configurations.. Sat Aug 8 11:23:32 2020 - [info] read_only=1 is not set on slave 192.168.68.131(192.168.68.131:3306). Sat Aug 8 11:23:32 2020 - [info] read_only=1 is not set on slave 192.168.68.133(192.168.68.133:3306). Sat Aug 8 11:23:32 2020 - [info] Checking replication filtering settings.. Sat Aug 8 11:23:32 2020 - [info] binlog_do_db= , binlog_ignore_db= information_schema,mysql,performance_schema,sys Sat Aug 8 11:23:32 2020 - [info] Replication filtering check ok. Sat Aug 8 11:23:32 2020 - [info] GTID (with auto-pos) is supported. Skipping all SSH and Node package checking. Sat Aug 8 11:23:32 2020 - [info] Checking SSH publickey authentication settings on the current master.. Sat Aug 8 11:23:32 2020 - [info] HealthCheck: SSH to 192.168.68.132 is reachable. Sat Aug 8 11:23:32 2020 - [info] 192.168.68.132(192.168.68.132:3306) (current master) +--192.168.68.131(192.168.68.131:3306) +--192.168.68.133(192.168.68.133:3306) Sat Aug 8 11:23:32 2020 - [info] Checking replication health on 192.168.68.131.. Sat Aug 8 11:23:32 2020 - [info] ok. Sat Aug 8 11:23:32 2020 - [info] Checking replication health on 192.168.68.133.. Sat Aug 8 11:23:32 2020 - [info] ok. Sat Aug 8 11:23:32 2020 - [info] Checking master_ip_failover_script status: Sat Aug 8 11:23:32 2020 - [info] /usr/local/mha/scripts/master_ip_failover --command=status --ssh_user=root --orig_master_host=192.168.68.132 --orig_master_ip=192.168.68.132 --orig_master_port=3306 IN SCRIPT TEST====/sbin/ip addr del 192.168.68.135/24 dev eth0==/sbin/ifconfig eth0:1 192.168.68.135/24=== Checking the Status of the script.. OK Sat Aug 8 11:23:32 2020 - [info] OK. Sat Aug 8 11:23:32 2020 - [warning] shutdown_script is not defined. Sat Aug 8 11:23:32 2020 - [info] Got exit code 0 (Not master dead). MySQL Replication Health is OK. |
3.2.5 Switchover切换:手动切换131为主库,132为备库
类似Oracle DG中的switchover。在该场景下,主库并没有宕机。在主库活着的时候,将主库降级为备库,将备用主库提升为主库,并且重新配置主从关系。此时,MHA进程不能启动。
1 2 3 | masterha_master_switch --conf=/etc/mha/mha.cnf --master_state=alive \ --orig_master_is_new_slave --running_updates_limit=10000 --interactive=0 \ --new_master_host=192.168.68.131 --new_master_port=3306 |
参数解释:
--interactive 为是否交互,即你要输入yes或no
--running_updates_limit 如果在切换过程中不指定running_updates_limit,那么默认情况下running_updates_limit为1秒。故障切换时,候选master如果有延迟的话,mha切换不能成功,加上此参数表示延迟在此时间范围内都可切换(单位为s),但是切换的时间长短是由recover时relay日志的大小决定
--orig_master_is_new_slave 将原来的主降低为从并重新加入主从关系
--new_master_host 指定新的主库的主机名,建议写IP地址
--new_master_port 指定新的主库上mysql服务的端口
本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务,私聊QQ646634621或微信dbaup66,谢谢!