基于GTID的主从复制-20201020
Master
mysql> set global enforce_gtid_consistency=true;
Query OK, 0 rows affected (0.00 sec)
mysql> set global gtid_mode=off_permissive;
Query OK, 0 rows affected (0.00 sec)
mysql> set global gtid_mode=on_permissive;
Query OK, 0 rows affected (0.02 sec)
mysql> set global gtid_mode=on;
Query OK, 0 rows affected (0.02 sec)
mysql> show master status\G;
*************************** 1. row ***************************
File: log-bin.000076
Position: 154
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)
ERROR:
No query specified
slave
mysql> stop slave;
Query OK, 0 rows affected (0.00 sec)
mysql> set global enforce_gtid_consistency=true;
Query OK, 0 rows affected (0.00 sec)
mysql> set global gtid_mode=off_permissive;
Query OK, 0 rows affected (0.01 sec)
mysql> set global gtid_mode=on_permissive;
Query OK, 0 rows affected (0.00 sec)
mysql> set global gtid_mode=on;
Query OK, 0 rows affected (0.02 sec)
mysql> change master to master_host='192.168.1.123',master_port=3306,master_user='root',master_password='123456',master_auto_position = 1;
Query OK, 0 rows affected, 2 warnings (0.00 sec)
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)
检查SSH配置
[root@MHA-Manager ~]# masterha_check_ssh --conf=/etc/mha/app1.conf
Tue Aug 4 02:13:44 2020 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Tue Aug 4 02:13:44 2020 - [info] Reading application default configuration from /etc/mha/app1.conf..
Tue Aug 4 02:13:44 2020 - [info] Reading server configuration from /etc/mha/app1.conf..
Tue Aug 4 02:13:44 2020 - [info] Starting SSH connection tests..
Tue Aug 4 02:13:48 2020 - [debug]
Tue Aug 4 02:13:45 2020 - [debug] Connecting via SSH from root@192.168.124.102(192.168.124.102:22) to root@192.168.124.100(192.168.124.100:22)..
Tue Aug 4 02:13:47 2020 - [debug] ok.
Tue Aug 4 02:13:47 2020 - [debug] Connecting via SSH from root@192.168.124.102(192.168.124.102:22) to root@192.168.124.101(192.168.124.101:22)..
Tue Aug 4 02:13:48 2020 - [debug] ok.
Tue Aug 4 02:13:48 2020 - [debug]
Tue Aug 4 02:13:44 2020 - [debug] Connecting via SSH from root@192.168.124.100(192.168.124.100:22) to root@192.168.124.101(192.168.124.101:22)..
Tue Aug 4 02:13:45 2020 - [debug] ok.
Tue Aug 4 02:13:45 2020 - [debug] Connecting via SSH from root@192.168.124.100(192.168.124.100:22) to root@192.168.124.102(192.168.124.102:22)..
Tue Aug 4 02:13:47 2020 - [debug] ok.
Tue Aug 4 02:13:48 2020 - [debug]
Tue Aug 4 02:13:45 2020 - [debug] Connecting via SSH from root@192.168.124.101(192.168.124.101:22) to root@192.168.124.100(192.168.124.100:22)..
Tue Aug 4 02:13:46 2020 - [debug] ok.
Tue Aug 4 02:13:46 2020 - [debug] Connecting via SSH from root@192.168.124.101(192.168.124.101:22) to root@192.168.124.102(192.168.124.102:22)..
Tue Aug 4 02:13:47 2020 - [debug] ok.
Tue Aug 4 02:13:48 2020 - [info] All SSH connection tests passed successfully.
检查repl当前配置
[root@MHA-Manager ~]# masterha_check_repl --conf=/etc/mha/app1.conf
Tue Aug 4 02:20:12 2020 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Tue Aug 4 02:20:12 2020 - [info] Reading application default configuration from /etc/mha/app1.conf..
Tue Aug 4 02:20:12 2020 - [info] Reading server configuration from /etc/mha/app1.conf..
Tue Aug 4 02:20:12 2020 - [info] MHA::MasterMonitor version 0.58.
Tue Aug 4 02:20:14 2020 - [info] GTID failover mode = 1
Tue Aug 4 02:20:14 2020 - [info] Dead Servers:
Tue Aug 4 02:20:14 2020 - [info] Alive Servers:
Tue Aug 4 02:20:14 2020 - [info] 192.168.124.100(192.168.124.100:3306)
Tue Aug 4 02:20:14 2020 - [info] 192.168.124.101(192.168.124.101:3306)
Tue Aug 4 02:20:14 2020 - [info] 192.168.124.102(192.168.124.102:3306)
Tue Aug 4 02:20:14 2020 - [info] Alive Slaves:
Tue Aug 4 02:20:14 2020 - [info] 192.168.124.101(192.168.124.101:3306) Version=5.7.26-log (oldest major version between slaves) log-bin:enabled
Tue Aug 4 02:20:14 2020 - [info] GTID ON
Tue Aug 4 02:20:14 2020 - [info] Replicating from 192.168.124.100(192.168.124.100:3306)
Tue Aug 4 02:20:14 2020 - [info] Primary candidate for the new Master (candidate_master is set)
Tue Aug 4 02:20:14 2020 - [info] 192.168.124.102(192.168.124.102:3306) Version=5.7.26-log (oldest major version between slaves) log-bin:enabled
Tue Aug 4 02:20:14 2020 - [info] GTID ON
Tue Aug 4 02:20:14 2020 - [info] Replicating from 192.168.124.100(192.168.124.100:3306)
Tue Aug 4 02:20:14 2020 - [info] Not candidate for the new Master (no_master is set)
Tue Aug 4 02:20:14 2020 - [info] Current Alive Master: 192.168.124.100(192.168.124.100:3306)
Tue Aug 4 02:20:14 2020 - [info] Checking slave configurations..
Tue Aug 4 02:20:14 2020 - [info] read_only=1 is not set on slave 192.168.124.101(192.168.124.101:3306).
Tue Aug 4 02:20:14 2020 - [info] read_only=1 is not set on slave 192.168.124.102(192.168.124.102:3306).
Tue Aug 4 02:20:14 2020 - [info] Checking replication filtering settings..
Tue Aug 4 02:20:14 2020 - [info] binlog_do_db= , binlog_ignore_db=
Tue Aug 4 02:20:14 2020 - [info] Replication filtering check ok.
Tue Aug 4 02:20:14 2020 - [info] GTID (with auto-pos) is supported. Skipping all SSH and Node package checking.
Tue Aug 4 02:20:14 2020 - [info] Checking SSH publickey authentication settings on the current master..
Tue Aug 4 02:20:15 2020 - [info] HealthCheck: SSH to 192.168.124.100 is reachable.
Tue Aug 4 02:20:15 2020 - [info]
192.168.124.100(192.168.124.100:3306) (current master)
+--192.168.124.101(192.168.124.101:3306)
+--192.168.124.102(192.168.124.102:3306)
Tue Aug 4 02:20:15 2020 - [info] Checking replication health on 192.168.124.101..
Tue Aug 4 02:20:15 2020 - [info] ok.
Tue Aug 4 02:20:15 2020 - [info] Checking replication health on 192.168.124.102..
Tue Aug 4 02:20:15 2020 - [info] ok.
Tue Aug 4 02:20:15 2020 - [info] Checking master_ip_failover_script status:
Tue Aug 4 02:20:15 2020 - [info] /etc/mha/master_ip_failover --command=status --ssh_user=root --orig_master_host=192.168.124.100 --orig_master_ip=192.168.124.100 --orig_master_port=3306
IN SCRIPT TEST====/sbin/ifconfig eth0:3 down==/sbin/ifconfig eth0:3 192.168.124.99 netmask 255.255.255.255 ;/sbin/arping -I eth0 -c 3 -s 192.168.124.99 192.168.124.1 >/dev/null 2>&1===
Checking the Status of the script.. OK
SIOCSIFADDR: No such device
eth0:3: ERROR while getting interface flags: No such device
SIOCSIFNETMASK: No such device
Tue Aug 4 02:20:17 2020 - [info] OK.
Tue Aug 4 02:20:17 2020 - [warning] shutdown_script is not defined.
Tue Aug 4 02:20:17 2020 - [info] Got exit code 0 (Not master dead).
MySQL Replication Health is OK.
启动mha manager
[root@MHA-Manager ~]# masterha_manager --conf=/etc/mha/app1.conf
Tue Aug 4 02:23:44 2020 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Tue Aug 4 02:23:44 2020 - [info] Reading application default configuration from /etc/mha/app1.conf..
Tue Aug 4 02:23:44 2020 - [info] Reading server configuration from /etc/mha/app1.conf..
查看状态
[root@MHA-Manager ~]# masterha_check_status --conf=/etc/mha/app1.conf
app1 (pid:10481) is running(0:PING_OK), master:192.168.124.100
4.5关闭mha
masterha_stop --conf=/etc/mha/app1.conf
4.6查看日志
tail -f /etc/masterha/app1/mha_manager.log
查看mha日志
[root@MHA-Manager mha]# tail -f /home/mha/manager.log
Checking the Status of the script.. OK
SIOCSIFADDR: No such device
eth0:3: ERROR while getting interface flags: No such device
SIOCSIFNETMASK: No such device
Tue Aug 4 02:23:46 2020 - [info] OK.
Tue Aug 4 02:23:46 2020 - [warning] shutdown_script is not defined.
Tue Aug 4 02:23:46 2020 - [info] Set master ping interval 1 seconds.
Tue Aug 4 02:23:46 2020 - [info] Set secondary check script: masterha_secondary_check -s 192.168.124.101 -s 192.168.124.102
Tue Aug 4 02:23:46 2020 - [info] Starting ping health check on 192.168.124.100(192.168.124.100:3306)..
Tue Aug 4 02:23:46 2020 - [info] Ping(SELECT) succeeded, waiting until MySQL doesn't respond..
场景1：目前的master 192.168.124.100 宕机后，MHA开始切换，候选Master库（Slave）（192.168.124.101 、192.168.124.102）会自动failover为Master.
[root@MHA-Master ~]# service mysql stop
Shutting down MySQL..................... SUCCESS!
mha日志如下
|
eth0:3: ERROR while getting interface flags: No such device
SIOCSIFNETMASK: No such device
Tue Aug 4 02:53:23 2020 - [info] OK.
Tue Aug 4 02:53:23 2020 - [info] ** Finished master recovery successfully.
Tue Aug 4 02:53:23 2020 - [info] * Phase 3: Master Recovery Phase completed.
Tue Aug 4 02:53:23 2020 - [info]
Tue Aug 4 02:53:23 2020 - [info] * Phase 4: Slaves Recovery Phase..
Tue Aug 4 02:53:23 2020 - [info]
Tue Aug 4 02:53:23 2020 - [info]
Tue Aug 4 02:53:23 2020 - [info] * Phase 4.1: Starting Slaves in parallel..
Tue Aug 4 02:53:23 2020 - [info]
Tue Aug 4 02:53:23 2020 - [info] -- Slave recovery on host 192.168.124.102(192.168.124.102:3306) started, pid: 12380. Check tmp log /home/mha//192.168.124.102_3306_20200804025321.log if it takes time..
Tue Aug 4 02:53:24 2020 - [info]
Tue Aug 4 02:53:24 2020 - [info] Log messages from 192.168.124.102 ...
Tue Aug 4 02:53:24 2020 - [info]
Tue Aug 4 02:53:23 2020 - [info] Resetting slave 192.168.124.102(192.168.124.102:3306) and starting replication from the new master 192.168.124.101(192.168.124.101:3306)..
Tue Aug 4 02:53:23 2020 - [info] Executed CHANGE MASTER.
Tue Aug 4 02:53:23 2020 - [info] Slave started.
Tue Aug 4 02:53:23 2020 - [info] gtid_wait(6c26c068-a952-11ea-ac80-000c29407100:1-4) completed on 192.168.124.102(192.168.124.102:3306). Executed 0 events.
Tue Aug 4 02:53:24 2020 - [info] End of log messages from 192.168.124.102.
Tue Aug 4 02:53:24 2020 - [info] -- Slave on host 192.168.124.102(192.168.124.102:3306) started.
Tue Aug 4 02:53:24 2020 - [info] All new slave servers recovered successfully.
Tue Aug 4 02:53:24 2020 - [info]
Tue Aug 4 02:53:24 2020 - [info] * Phase 5: New master cleanup phase..
Tue Aug 4 02:53:24 2020 - [info]
Tue Aug 4 02:53:24 2020 - [info] Resetting slave info on the new master..
Tue Aug 4 02:53:24 2020 - [info] 192.168.124.101: Resetting slave info succeeded.
Tue Aug 4 02:53:24 2020 - [info] Master failover to 192.168.124.101(192.168.124.101:3306) completed successfully.
Tue Aug 4 02:53:24 2020 - [info]
----- Failover Report -----
app1: MySQL Master failover 192.168.124.100(192.168.124.100:3306) to 192.168.124.101(192.168.124.101:3306) succeeded
Master 192.168.124.100(192.168.124.100:3306) is down!
Check MHA Manager logs at MHA-Manager:/home/mha/manager.log for details.
Started automated(non-interactive) failover.
Invalidated master IP address on 192.168.124.100(192.168.124.100:3306)
Selected 192.168.124.101(192.168.124.101:3306) as a new master.
192.168.124.101(192.168.124.101:3306): OK: Applying all logs succeeded.
192.168.124.101(192.168.124.101:3306): OK: Activated master IP address.
192.168.124.102(192.168.124.102:3306): OK: Slave started, replicating from 192.168.124.101(192.168.124.101:3306)
192.168.124.101(192.168.124.101:3306): Resetting slave info succeeded.
Master failover to 192.168.124.101(192.168.124.101:3306) completed successfully.
哈哈 自动切换
|
查看目前的Master 和 Slave
MHA-slave03
|
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.124.101
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: log-bin.000032
Read_Master_Log_Pos: 731
Relay_Log_File: slave-relay-bin.000002
Relay_Log_Pos: 408
Relay_Master_Log_File: log-bin.000032
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
|
创建时间：2021-12-22 10:15
넶浏览量：0
