数据库架构:一主两从
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进程
mysqladmin -uroot -pmysql shutdown
2.查看MHA manager日志
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 0
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: lo: <LOOPBACK,UP,LOWER_UP> 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: <BROADCAST,MULTICAST,UP,LOWER_UP> 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: <NO-CARRIER,BROADCAST,MULTICAST,UP> 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: <BROADCAST,MULTICAST> 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
mysql> show slave status \G
Empty set (0.00 sec)
192.168.8.59
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(主库)
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(从库)
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| t1 |
| t10 |
| t2 |
| t3 |
| t4 |
| t5 |
| t6 |
| t7 |
| t8 |
| t9 |
+----------------+
新的主从数据复制正常。
三、将旧主库加入复制环境
修复完192.168.8.57之后,将此节点变成从库加入到环境当中,可以直接change master to
change master to
master_host='192.168.8.58',
master_port=3306,
master_user='repl',
master_password='mysql',
master_auto_position=1;
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
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| t1 |
| t10 |
| t2 |
| t3 |
| t4 |
| t5 |
| t6 |
| t7 |
| t8 |
| t9 |
+----------------+
修改/etc/masterha/app1.cnf,将[server1]添加上
四、将复制还原到原始状态(主库为192.168.8.57)
1.重置复制
192.168.8.57
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
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
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)
2.重启192.168.8.57和192.168.8.58上边keepalived进程,使VIP飘至192.168.8.57。
3.重启MHA manager进程并观察复制情况。