导航：首页 > 数据库 >

MySQL5.7+MHA+Keepalived failover自动切换

发表于：2024-11-20 作者：千家信息网编辑

千家信息网最后更新 2024年11月20日，数据库架构：一主两从master：192.168.8.57slave1：192.168.8.58slave2：192.168.8.59manager：192.168.8.60MHA工具包：mha4my

千家信息网最后更新 2024年11月20日MySQL5.7+MHA+Keepalived failover自动切换

数据库架构：一主两从

master：192.168.8.57

slave1：192.168.8.58

slave2：192.168.8.59

manager：192.168.8.60

MHA工具包：

mha4mysql-manager-0.58.tar.gz

mha4mysql-node-0.58.tar.gz

keepalived-1.4.5.tar.gz

一、环境配置过程如下：

http://blog.itpub.net/30135314/viewspace-2217762/

二、切换测试

1.在192.168.8.57关闭MySQL进程

1	`mysqladmin -uroot -pmysql` `shutdown`

2.查看MHA manager日志

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 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 Mon Oct 29 13:55:18 2018 - [warning] Got error on MySQL select ping : 2006 (MySQL server has gone away) Mon Oct 29 13:55:18 2018 - [info] Executing secondary network check script: /usr/local/bin/masterha_secondary_check -s 192.168.8.58 -s 192.168.8.59 --user=root --master_host=192.168.8.57 --master_ip=192.168.8.57 --master_port=3306 --master_user=root --master_password=mysql --ping_type=SELECT Mon Oct 29 13:55:18 2018 - [info] Executing SSH check script: exit Mon Oct 29 13:55:18 2018 - [info] HealthCheck: SSH to 192.168.8.57 is reachable. Monitoring server 192.168.8.58 is reachable, Master is not reachable from 192.168.8.58. OK. Mon Oct 29 13:55:19 2018 - [warning] Got error on MySQL connect: 2003 (Can 't connect to MySQL server on ' 192.168.8.57' (111)) Mon Oct 29 13:55:19 2018 - [warning] Connection failed 2 time (s).. Monitoring server 192.168.8.59 is reachable, Master is not reachable from 192.168.8.59. OK. Mon Oct 29 13:55:19 2018 - [info] Master is not reachable from all other monitoring servers. Failover should start. Mon Oct 29 13:55:20 2018 - [warning] Got error on MySQL connect: 2003 (Can 't connect to MySQL server on ' 192.168.8.57' (111)) Mon Oct 29 13:55:20 2018 - [warning] Connection failed 3 time (s).. Mon Oct 29 13:55:21 2018 - [warning] Got error on MySQL connect: 2003 (Can 't connect to MySQL server on ' 192.168.8.57' (111)) Mon Oct 29 13:55:21 2018 - [warning] Connection failed 4 time (s).. Mon Oct 29 13:55:21 2018 - [warning] Master is not reachable from health checker! Mon Oct 29 13:55:21 2018 - [warning] Master 192.168.8.57(192.168.8.57:3306) is not reachable! Mon Oct 29 13:55:21 2018 - [warning] SSH is reachable. Mon Oct 29 13:55:21 2018 - [info] Connecting to a master server failed. Reading configuration file /etc/masterha_default .cnf and /etc/masterha/app1 .cnf again, and trying to connect to all servers to check server status.. Mon Oct 29 13:55:21 2018 - [warning] Global configuration file /etc/masterha_default .cnf not found. Skipping. Mon Oct 29 13:55:21 2018 - [info] Reading application default configuration from /etc/masterha/app1 .cnf.. Mon Oct 29 13:55:21 2018 - [info] Reading server configuration from /etc/masterha/app1 .cnf.. Mon Oct 29 13:55:22 2018 - [info] GTID failover mode = 1 Mon Oct 29 13:55:22 2018 - [info] Dead Servers: Mon Oct 29 13:55:22 2018 - [info] 192.168.8.57(192.168.8.57:3306) Mon Oct 29 13:55:22 2018 - [info] Alive Servers: Mon Oct 29 13:55:22 2018 - [info] 192.168.8.58(192.168.8.58:3306) Mon Oct 29 13:55:22 2018 - [info] 192.168.8.59(192.168.8.59:3306) Mon Oct 29 13:55:22 2018 - [info] Alive Slaves: Mon Oct 29 13:55:22 2018 - [info] 192.168.8.58(192.168.8.58:3306) Version=5.7.23-log (oldest major version between slaves) log-bin:enabled Mon Oct 29 13:55:22 2018 - [info] GTID ON Mon Oct 29 13:55:22 2018 - [info] Replicating from 192.168.8.57(192.168.8.57:3306) Mon Oct 29 13:55:22 2018 - [info] Primary candidate for the new Master (candidate_master is set ) Mon Oct 29 13:55:22 2018 - [info] 192.168.8.59(192.168.8.59:3306) Version=5.7.23-log (oldest major version between slaves) log-bin:enabled Mon Oct 29 13:55:22 2018 - [info] GTID ON Mon Oct 29 13:55:22 2018 - [info] Replicating from 192.168.8.57(192.168.8.57:3306) Mon Oct 29 13:55:22 2018 - [info] Checking slave configurations.. Mon Oct 29 13:55:22 2018 - [info] read_only=1 is not set on slave 192.168.8.58(192.168.8.58:3306). Mon Oct 29 13:55:22 2018 - [info] read_only=1 is not set on slave 192.168.8.59(192.168.8.59:3306). Mon Oct 29 13:55:22 2018 - [info] Checking replication filtering settings.. Mon Oct 29 13:55:22 2018 - [info] Replication filtering check ok. Mon Oct 29 13:55:22 2018 - [info] Master is down! Mon Oct 29 13:55:22 2018 - [info] Terminating monitoring script. Mon Oct 29 13:55:22 2018 - [info] Got exit code 20 (Master dead). Mon Oct 29 13:55:22 2018 - [info] MHA::MasterFailover version 0.58. Mon Oct 29 13:55:22 2018 - [info] Starting master failover. Mon Oct 29 13:55:22 2018 - [info] Mon Oct 29 13:55:22 2018 - [info] * Phase 1: Configuration Check Phase.. Mon Oct 29 13:55:22 2018 - [info] Mon Oct 29 13:55:23 2018 - [info] GTID failover mode = 1 Mon Oct 29 13:55:23 2018 - [info] Dead Servers: Mon Oct 29 13:55:23 2018 - [info] 192.168.8.57(192.168.8.57:3306) Mon Oct 29 13:55:23 2018 - [info] Checking master reachability via MySQL(double check)... Mon Oct 29 13:55:23 2018 - [info] ok. Mon Oct 29 13:55:23 2018 - [info] Alive Servers: Mon Oct 29 13:55:23 2018 - [info] 192.168.8.58(192.168.8.58:3306) Mon Oct 29 13:55:23 2018 - [info] 192.168.8.59(192.168.8.59:3306) Mon Oct 29 13:55:23 2018 - [info] Alive Slaves: Mon Oct 29 13:55:23 2018 - [info] 192.168.8.58(192.168.8.58:3306) Version=5.7.23-log (oldest major version between slaves) log-bin:enabled Mon Oct 29 13:55:23 2018 - [info] GTID ON Mon Oct 29 13:55:23 2018 - [info] Replicating from 192.168.8.57(192.168.8.57:3306) Mon Oct 29 13:55:23 2018 - [info] Primary candidate for the new Master (candidate_master is set ) Mon Oct 29 13:55:23 2018 - [info] 192.168.8.59(192.168.8.59:3306) Version=5.7.23-log (oldest major version between slaves) log-bin:enabled Mon Oct 29 13:55:23 2018 - [info] GTID ON Mon Oct 29 13:55:23 2018 - [info] Replicating from 192.168.8.57(192.168.8.57:3306) Mon Oct 29 13:55:23 2018 - [info] Starting GTID based failover. Mon Oct 29 13:55:23 2018 - [info] Mon Oct 29 13:55:23 2018 - [info] ** Phase 1: Configuration Check Phase completed. Mon Oct 29 13:55:23 2018 - [info] Mon Oct 29 13:55:23 2018 - [info] * Phase 2: Dead Master Shutdown Phase.. Mon Oct 29 13:55:23 2018 - [info] Mon Oct 29 13:55:23 2018 - [info] Forcing shutdown so that applications never connect to the current master.. Mon Oct 29 13:55:23 2018 - [info] Executing master IP deactivation script: Mon Oct 29 13:55:23 2018 - [info] /usr/local/bin/master_ip_failover --orig_master_host=192.168.8.57 --orig_master_ip=192.168.8.57 --orig_master_port=3306 -- command =stopssh --ssh_user=root Mon Oct 29 13:55:24 2018 - [info] done . Mon Oct 29 13:55:24 2018 - [warning] shutdown_script is not set . Skipping explicit shutting down of the dead master. Mon Oct 29 13:55:24 2018 - [info] * Phase 2: Dead Master Shutdown Phase completed. Mon Oct 29 13:55:24 2018 - [info] Mon Oct 29 13:55:24 2018 - [info] * Phase 3: Master Recovery Phase.. Mon Oct 29 13:55:24 2018 - [info] Mon Oct 29 13:55:24 2018 - [info] * Phase 3.1: Getting Latest Slaves Phase.. Mon Oct 29 13:55:24 2018 - [info] Mon Oct 29 13:55:24 2018 - [info] The latest binary log file /position on all slaves is mysql-bin.000020:375 Mon Oct 29 13:55:24 2018 - [info] Retrieved Gtid Set: a92f70a4-d5ea-11e8-af28-080027c0450d:11-13 Mon Oct 29 13:55:24 2018 - [info] Latest slaves (Slaves that received relay log files to the latest): Mon Oct 29 13:55:24 2018 - [info] 192.168.8.58(192.168.8.58:3306) Version=5.7.23-log (oldest major version between slaves) log-bin:enabled Mon Oct 29 13:55:24 2018 - [info] GTID ON Mon Oct 29 13:55:24 2018 - [info] Replicating from 192.168.8.57(192.168.8.57:3306) Mon Oct 29 13:55:24 2018 - [info] Primary candidate for the new Master (candidate_master is set ) Mon Oct 29 13:55:24 2018 - [info] 192.168.8.59(192.168.8.59:3306) Version=5.7.23-log (oldest major version between slaves) log-bin:enabled Mon Oct 29 13:55:24 2018 - [info] GTID ON Mon Oct 29 13:55:24 2018 - [info] Replicating from 192.168.8.57(192.168.8.57:3306) Mon Oct 29 13:55:24 2018 - [info] The oldest binary log file /position on all slaves is mysql-bin.000020:375 Mon Oct 29 13:55:24 2018 - [info] Retrieved Gtid Set: a92f70a4-d5ea-11e8-af28-080027c0450d:11-13 Mon Oct 29 13:55:24 2018 - [info] Oldest slaves: Mon Oct 29 13:55:24 2018 - [info] 192.168.8.58(192.168.8.58:3306) Version=5.7.23-log (oldest major version between slaves) log-bin:enabled Mon Oct 29 13:55:24 2018 - [info] GTID ON Mon Oct 29 13:55:24 2018 - [info] Replicating from 192.168.8.57(192.168.8.57:3306) Mon Oct 29 13:55:24 2018 - [info] Primary candidate for the new Master (candidate_master is set ) Mon Oct 29 13:55:24 2018 - [info] 192.168.8.59(192.168.8.59:3306) Version=5.7.23-log (oldest major version between slaves) log-bin:enabled Mon Oct 29 13:55:24 2018 - [info] GTID ON Mon Oct 29 13:55:24 2018 - [info] Replicating from 192.168.8.57(192.168.8.57:3306) Mon Oct 29 13:55:24 2018 - [info] Mon Oct 29 13:55:24 2018 - [info] * Phase 3.3: Determining New Master Phase.. Mon Oct 29 13:55:24 2018 - [info] Mon Oct 29 13:55:24 2018 - [info] Searching new master from slaves.. Mon Oct 29 13:55:24 2018 - [info] Candidate masters from the configuration file : Mon Oct 29 13:55:24 2018 - [info] 192.168.8.58(192.168.8.58:3306) Version=5.7.23-log (oldest major version between slaves) log-bin:enabled Mon Oct 29 13:55:24 2018 - [info] GTID ON Mon Oct 29 13:55:24 2018 - [info] Replicating from 192.168.8.57(192.168.8.57:3306) Mon Oct 29 13:55:24 2018 - [info] Primary candidate for the new Master (candidate_master is set ) Mon Oct 29 13:55:24 2018 - [info] Non-candidate masters: Mon Oct 29 13:55:24 2018 - [info] Searching from candidate_master slaves which have received the latest relay log events.. Mon Oct 29 13:55:24 2018 - [info] New master is 192.168.8.58(192.168.8.58:3306) Mon Oct 29 13:55:24 2018 - [info] Starting master failover.. Mon Oct 29 13:55:24 2018 - [info] From: 192.168.8.57(192.168.8.57:3306) (current master) +--192.168.8.58(192.168.8.58:3306) +--192.168.8.59(192.168.8.59:3306) To: 192.168.8.58(192.168.8.58:3306) (new master) +--192.168.8.59(192.168.8.59:3306) Mon Oct 29 13:55:24 2018 - [info] Mon Oct 29 13:55:24 2018 - [info] * Phase 3.3: New Master Recovery Phase.. Mon Oct 29 13:55:24 2018 - [info] Mon Oct 29 13:55:24 2018 - [info] Waiting all logs to be applied.. Mon Oct 29 13:55:24 2018 - [info] done . Mon Oct 29 13:55:24 2018 - [info] Getting new master's binlog name and position.. Mon Oct 29 13:55:24 2018 - [info] mysql-bin.000021:415 Mon Oct 29 13:55:24 2018 - [info] All other slaves should start replication from here. Statement should be: CHANGE MASTER TO MASTER_HOST= '192.168.8.58' , MASTER_PORT=3306, MASTER_AUTO_POSITION=1, MASTER_USER= 'repl' , MASTER_PASSWORD= 'xxx' ; Mon Oct 29 13:55:24 2018 - [info] Master Recovery succeeded. File:Pos:Exec_Gtid_Set: mysql-bin.000021, 415, a92f70a4-d5ea-11e8-af28-080027c0450d:1-13, a92f70a4-d5ea-11e8-af28-080027c0450f:1-7 Mon Oct 29 13:55:24 2018 - [info] Executing master IP activate script: Mon Oct 29 13:55:24 2018 - [info] /usr/local/bin/master_ip_failover -- command =start --ssh_user=root --orig_master_host=192.168.8.57 --orig_master_ip=192.168.8.57 --orig_master_port=3306 --new_master_host=192.168.8.58 --new_master_ip=192.168.8.58 --new_master_port=3306 --new_master_user= 'root' --new_master_password=xxx Undefined subroutine &main::FIXME_xxx_create_user called at /usr/local/bin/master_ip_failover line 94. Set read_only=0 on the new master. Creating app user on the new master.. Mon Oct 29 13:55:24 2018 - [error][ /usr/lib/perl5/vendor_perl/MHA/MasterFailover .pm, ln1612] Failed to activate master IP address for 192.168.8.58(192.168.8.58:3306) with return code 10:0 Mon Oct 29 13:55:24 2018 - [warning] Proceeding. Mon Oct 29 13:55:24 2018 - [info] ** Finished master recovery successfully. Mon Oct 29 13:55:24 2018 - [info] * Phase 3: Master Recovery Phase completed. Mon Oct 29 13:55:24 2018 - [info] Mon Oct 29 13:55:24 2018 - [info] * Phase 4: Slaves Recovery Phase.. Mon Oct 29 13:55:24 2018 - [info] Mon Oct 29 13:55:24 2018 - [info] Mon Oct 29 13:55:24 2018 - [info] * Phase 4.1: Starting Slaves in parallel.. Mon Oct 29 13:55:24 2018 - [info] Mon Oct 29 13:55:24 2018 - [info] -- Slave recovery on host 192.168.8.59(192.168.8.59:3306) started, pid: 7627. Check tmp log /var/log/masterha/app1/192 .168.8.59_3306_20181029135522.log if it takes time .. Mon Oct 29 13:55:25 2018 - [info] Mon Oct 29 13:55:25 2018 - [info] Log messages from 192.168.8.59 ... Mon Oct 29 13:55:25 2018 - [info] Mon Oct 29 13:55:24 2018 - [info] Resetting slave 192.168.8.59(192.168.8.59:3306) and starting replication from the new master 192.168.8.58(192.168.8.58:3306).. Mon Oct 29 13:55:24 2018 - [info] Executed CHANGE MASTER. Mon Oct 29 13:55:24 2018 - [info] Slave started. Mon Oct 29 13:55:24 2018 - [info] gtid_wait(a92f70a4-d5ea-11e8-af28-080027c0450d:1-13, a92f70a4-d5ea-11e8-af28-080027c0450f:1-7) completed on 192.168.8.59(192.168.8.59:3306). Executed 0 events. Mon Oct 29 13:55:25 2018 - [info] End of log messages from 192.168.8.59. Mon Oct 29 13:55:25 2018 - [info] -- Slave on host 192.168.8.59(192.168.8.59:3306) started. Mon Oct 29 13:55:25 2018 - [info] All new slave servers recovered successfully. Mon Oct 29 13:55:25 2018 - [info] Mon Oct 29 13:55:25 2018 - [info] * Phase 5: New master cleanup phase.. Mon Oct 29 13:55:25 2018 - [info] Mon Oct 29 13:55:25 2018 - [info] Resetting slave info on the new master.. Mon Oct 29 13:55:25 2018 - [info] 192.168.8.58: Resetting slave info succeeded. Mon Oct 29 13:55:25 2018 - [info] Master failover to 192.168.8.58(192.168.8.58:3306) completed successfully. Mon Oct 29 13:55:25 2018 - [info] Deleted server1 entry from /etc/masterha/app1 .cnf . Mon Oct 29 13:55:25 2018 - [info] ----- Failover Report ----- app1: MySQL Master failover 192.168.8.57(192.168.8.57:3306) to 192.168.8.58(192.168.8.58:3306) succeeded Master 192.168.8.57(192.168.8.57:3306) is down! Check MHA Manager logs at manager: /var/log/masterha/app1/manager .log for details. Started automated(non-interactive) failover. Invalidated master IP address on 192.168.8.57(192.168.8.57:3306) Selected 192.168.8.58(192.168.8.58:3306) as a new master. 192.168.8.58(192.168.8.58:3306): OK: Applying all logs succeeded. Failed to activate master IP address for 192.168.8.58(192.168.8.58:3306) with return code 10:0 192.168.8.59(192.168.8.59:3306): OK: Slave started, replicating from 192.168.8.58(192.168.8.58:3306) 192.168.8.58(192.168.8.58:3306): Resetting slave info succeeded. Master failover to 192.168.8.58(192.168.8.58:3306) completed successfully. Mon Oct 29 13:55:25 2018 - [info] Sending mail.. Unknown option: conf

可以看到192.168.8.57上MySQL进程宕掉之后，新主库切至192.168.8.58，和192.168.8.59形成新的主从环境。

3.查看VIP

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 1: lo: mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1000 link /loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00 inet 127.0.0.1 /8 scope host lo valid_lft forever preferred_lft forever inet6 ::1 /128 scope host valid_lft forever preferred_lft forever 2: enp0s3: mtu 1500 qdisc pfifo_fast state UP group default qlen 1000 link /ether 08:00:27:4d:70:17 brd ff:ff:ff:ff:ff:ff inet 192.168.8.58 /24 brd 192.168.8.255 scope global noprefixroute enp0s3 valid_lft forever preferred_lft forever inet 192.168.8.88 /24 scope global secondary enp0s3 valid_lft forever preferred_lft forever inet6 fe80::6a31:3e92:8b6f:83c0 /64 scope link noprefixroute valid_lft forever preferred_lft forever inet6 fe80::5198:593b:cdc5:1f90 /64 scope link tentative noprefixroute dadfailed valid_lft forever preferred_lft forever 3: virbr0: mtu 1500 qdisc noqueue state DOWN group default qlen 1000 link /ether 52:54:00:f4:55:bb brd ff:ff:ff:ff:ff:ff inet 192.168.122.1 /24 brd 192.168.122.255 scope global virbr0 valid_lft forever preferred_lft forever 4: virbr0-nic: mtu 1500 qdisc pfifo_fast master virbr0 state DOWN group default qlen 1000 link /ether 52:54:00:f4:55:bb brd ff:ff:ff:ff:ff:ff

VIP192.168.8.88/24已经飘至192.168.8.58机器。

4.查看slave进程

192.168.8.58

1 2	`mysql> show slave status \G` `Empty` `set` `(0.00 sec)`

192.168.8.59

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 53 54 55 56 57 58 59 60 61 mysql> show slave status \G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.8.58 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000021 Read_Master_Log_Pos: 415 Relay_Log_File: slave2-relay-bin.000002 Relay_Log_Pos: 414 Relay_Master_Log_File: mysql-bin.000021 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 415 Relay_Log_Space: 622 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 58 Master_UUID: a92f70a4-d5ea-11e8-af28-080027c0450f Master_Info_File: /mysql/data/master.info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: a92f70a4-d5ea-11e8-af28-080027c0450b:1-4, a92f70a4-d5ea-11e8-af28-080027c0450d:1-13, a92f70a4-d5ea-11e8-af28-080027c0450f:1-7 Auto_Position: 1 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version:

可以看到从库192.168.8.59的主库变成192.168.8.58

5.测试复制

192.168.8.58（主库）

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 mysql> create table t10 (id int (10)); Query OK, 0 rows affected (0.19 sec) mysql> show tables; + ----------------+ | Tables_in_test | + ----------------+ | t1 | | t10 | | t2 | | t3 | | t4 | | t5 | | t6 | | t7 | | t8 | | t9 | + ----------------+

192.168.8.59（从库）

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 mysql> show tables; + ----------------+ | Tables_in_test | + ----------------+ | t1 | | t10 | | t2 | | t3 | | t4 | | t5 | | t6 | | t7 | | t8 | | t9 | + ----------------+

新的主从数据复制正常。

三、将旧主库加入复制环境

修复完192.168.8.57之后，将此节点变成从库加入到环境当中，可以直接change master to

1 2 3 4 5 6 change master to master_host= '192.168.8.58' , master_port=3306, master_user= 'repl' , master_password= 'mysql' , master_auto_position=1;

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 53 54 55 56 57 58 59 60 61 62 63 64 mysql> start slave; Query OK, 0 rows affected (0.07 sec) mysql> set global read_only=1; Query OK, 0 rows affected (0.00 sec) mysql> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.8.58 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000021 Read_Master_Log_Pos: 583 Relay_Log_File: master-relay-bin.000002 Relay_Log_Pos: 582 Relay_Master_Log_File: mysql-bin.000021 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 583 Relay_Log_Space: 830 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 58 Master_UUID: a92f70a4-d5ea-11e8-af28-080027c0450f Master_Info_File: /mysql/data/master.info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: a92f70a4-d5ea-11e8-af28-080027c0450f:8 Executed_Gtid_Set: a92f70a4-d5ea-11e8-af28-080027c0450d:1-13, a92f70a4-d5ea-11e8-af28-080027c0450f:1-8 Auto_Position: 1 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version:

查看t10有没有复制到192.168.8.57

修改/etc/masterha/app1.cnf，将[server1]添加上

四、将复制还原到原始状态（主库为192.168.8.57）

1.重置复制

192.168.8.57

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 53 54 55 56 57 58 59 60 61 62 mysql> stop slave; Query OK, 0 rows affected (0.01 sec) mysql> show slave status \G *************************** 1. row *************************** Slave_IO_State: Master_Host: 192.168.8.58 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000021 Read_Master_Log_Pos: 583 Relay_Log_File: master-relay-bin.000003 Relay_Log_Pos: 454 Relay_Master_Log_File: mysql-bin.000021 Slave_IO_Running: No Slave_SQL_Running: No Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 583 Relay_Log_Space: 1090 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: NULL Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 58 Master_UUID: a92f70a4-d5ea-11e8-af28-080027c0450f Master_Info_File: /mysql/data/master.info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: a92f70a4-d5ea-11e8-af28-080027c0450f:8 Executed_Gtid_Set: a92f70a4-d5ea-11e8-af28-080027c0450d:1-13, a92f70a4-d5ea-11e8-af28-080027c0450f:1-8 Auto_Position: 1 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version:

192.168.8.58

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 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 mysql> reset slave; Query OK, 0 rows affected (0.00 sec) mysql> set global read_only=1; Query OK, 0 rows affected (0.00 sec) mysql> change master to -> master_host= '192.168.8.57' , -> master_port=3306, -> master_user= 'repl' , -> master_password= 'mysql' , -> master_auto_position=1; Query OK, 0 rows affected, 2 warnings (0.05 sec) mysql> start slave ; Query OK, 0 rows affected (0.00 sec) mysql> show slave status \G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.8.57 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000021 Read_Master_Log_Pos: 194 Relay_Log_File: slave1-relay-bin.000002 Relay_Log_Pos: 367 Relay_Master_Log_File: mysql-bin.000021 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 194 Relay_Log_Space: 615 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 57 Master_UUID: a92f70a4-d5ea-11e8-af28-080027c0450d Master_Info_File: /mysql/data/master.info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: a92f70a4-d5ea-11e8-af28-080027c0450d:1-13, a92f70a4-d5ea-11e8-af28-080027c0450f:1-8 Auto_Position: 1 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: 1 row in set (0.00 sec)

192.168.8.59

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 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 mysql> stop slave; Query OK, 0 rows affected (0.01 sec) mysql> reset slave; Query OK, 0 rows affected (0.10 sec) mysql> change master to -> master_host= '192.168.8.57' , -> master_port=3306, -> master_user= 'repl' , -> master_password= 'mysql' , -> master_auto_position=1; Query OK, 0 rows affected, 2 warnings (0.05 sec) mysql> start slave; Query OK, 0 rows affected (0.01 sec) mysql> set global read_only=1; Query OK, 0 rows affected (0.00 sec) mysql> show slave status \G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.8.57 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000021 Read_Master_Log_Pos: 194 Relay_Log_File: slave2-relay-bin.000002 Relay_Log_Pos: 367 Relay_Master_Log_File: mysql-bin.000021 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 194 Relay_Log_Space: 575 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 57 Master_UUID: a92f70a4-d5ea-11e8-af28-080027c0450d Master_Info_File: /mysql/data/master.info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: a92f70a4-d5ea-11e8-af28-080027c0450b:1-4, a92f70a4-d5ea-11e8-af28-080027c0450d:1-13, a92f70a4-d5ea-11e8-af28-080027c0450f:1-8 Auto_Position: 1 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: 1 row in set (0.00 sec)