31. MHA-故障模拟¶
1. 环境准备（略）¶
#搭建好MHA环境，见第30篇文章
2. 监控mha manager节点日志¶
#162
tail -f /var/log/mha/app1/manager
[root@162 tools]# tail -f /var/log/mha/app1/manager
192.168.178.151(192.168.178.151:3306) (current master)
+--192.168.178.161(192.168.178.161:3306)
+--192.168.178.162(192.168.178.162:3306)
Wed Apr 28 22:38:43 2021 - [warning] master_ip_failover_script is not defined.
Wed Apr 28 22:38:43 2021 - [warning] shutdown_script is not defined.
Wed Apr 28 22:38:43 2021 - [info] Set master ping interval 2 seconds.
Wed Apr 28 22:38:43 2021 - [warning] secondary_check_script is not defined. It is highly recommended setting it to check master reachability from two or more routes.
Wed Apr 28 22:38:43 2021 - [info] Starting ping health check on 192.168.178.151(192.168.178.151:3306)..
Wed Apr 28 22:38:43 2021 - [info] Ping(SELECT) succeeded, waiting until MySQL doesn't respond..
3. 模拟主库宕机¶
#151
pkill mysqld
netstat -tunlp |grep 330
[root@151 tools]# netstat -tunlp |grep 330
tcp6 0 0 :::3306 :::* LISTEN 70068/mysqld
[root@151 tools]# pkill mysqld
[root@151 tools]# netstat -tunlp |grep 330
[root@151 tools]#
4. 此时查看mha manager节点日志信息¶
#162
[root@162 tools]# tail -f /var/log/mha/app1/manager
192.168.178.151(192.168.178.151:3306) (current master)
+--192.168.178.161(192.168.178.161:3306)
+--192.168.178.162(192.168.178.162:3306)
Wed Apr 28 22:38:43 2021 - [warning] master_ip_failover_script is not defined.
Wed Apr 28 22:38:43 2021 - [warning] shutdown_script is not defined.
Wed Apr 28 22:38:43 2021 - [info] Set master ping interval 2 seconds.
Wed Apr 28 22:38:43 2021 - [warning] secondary_check_script is not defined. It is highly recommended setting it to check master reachability from two or more routes.
Wed Apr 28 22:38:43 2021 - [info] Starting ping health check on 192.168.178.151(192.168.178.151:3306)..
Wed Apr 28 22:38:43 2021 - [info] Ping(SELECT) succeeded, waiting until MySQL doesn't respond..
Wed Apr 28 22:57:42 2021 - [warning] Got error on MySQL select ping: 2006 (MySQL server has gone away)
Wed Apr 28 22:57:42 2021 - [info] Executing SSH check script: exit 0
Wed Apr 28 22:57:42 2021 - [info] HealthCheck: SSH to 192.168.178.151 is reachable.
Wed Apr 28 22:57:44 2021 - [warning] Got error on MySQL connect: 2003 (Can't connect to MySQL server on '192.168.178.151' (111))
Wed Apr 28 22:57:44 2021 - [warning] Connection failed 2 time(s)..
Wed Apr 28 22:57:46 2021 - [warning] Got error on MySQL connect: 2003 (Can't connect to MySQL server on '192.168.178.151' (111))
Wed Apr 28 22:57:46 2021 - [warning] Connection failed 3 time(s)..
Wed Apr 28 22:57:48 2021 - [warning] Got error on MySQL connect: 2003 (Can't connect to MySQL server on '192.168.178.151' (111))
Wed Apr 28 22:57:48 2021 - [warning] Connection failed 4 time(s)..
Wed Apr 28 22:57:48 2021 - [warning] Master is not reachable from health checker!
Wed Apr 28 22:57:48 2021 - [warning] Master 192.168.178.151(192.168.178.151:3306) is not reachable!
Wed Apr 28 22:57:48 2021 - [warning] SSH is reachable.
Wed Apr 28 22:57:48 2021 - [info] Connecting to a master server failed. Reading configuration file /etc/masterha_default.cnf and /etc/mha/app1.cnf again, and trying to connect to all servers to check server status..
Wed Apr 28 22:57:48 2021 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Wed Apr 28 22:57:48 2021 - [info] Reading application default configuration from /etc/mha/app1.cnf..
Wed Apr 28 22:57:48 2021 - [info] Reading server configuration from /etc/mha/app1.cnf..
Wed Apr 28 22:57:49 2021 - [info] GTID failover mode = 1
Wed Apr 28 22:57:49 2021 - [info] Dead Servers:
Wed Apr 28 22:57:49 2021 - [info] 192.168.178.151(192.168.178.151:3306)
Wed Apr 28 22:57:49 2021 - [info] Alive Servers:
Wed Apr 28 22:57:49 2021 - [info] 192.168.178.161(192.168.178.161:3306)
Wed Apr 28 22:57:49 2021 - [info] 192.168.178.162(192.168.178.162:3306)
Wed Apr 28 22:57:49 2021 - [info] Alive Slaves:
Wed Apr 28 22:57:49 2021 - [info] 192.168.178.161(192.168.178.161:3306) Version=5.7.20-log (oldest major version between slaves) log-bin:enabled
Wed Apr 28 22:57:49 2021 - [info] GTID ON
Wed Apr 28 22:57:49 2021 - [info] Replicating from 192.168.178.151(192.168.178.151:3306)
Wed Apr 28 22:57:49 2021 - [info] 192.168.178.162(192.168.178.162:3306) Version=5.7.20-log (oldest major version between slaves) log-bin:enabled
Wed Apr 28 22:57:49 2021 - [info] GTID ON
Wed Apr 28 22:57:49 2021 - [info] Replicating from 192.168.178.151(192.168.178.151:3306)
Wed Apr 28 22:57:49 2021 - [info] Checking slave configurations..
Wed Apr 28 22:57:49 2021 - [info] read_only=1 is not set on slave 192.168.178.161(192.168.178.161:3306).
Wed Apr 28 22:57:49 2021 - [info] read_only=1 is not set on slave 192.168.178.162(192.168.178.162:3306).
Wed Apr 28 22:57:49 2021 - [info] Checking replication filtering settings..
Wed Apr 28 22:57:49 2021 - [info] Replication filtering check ok.
Wed Apr 28 22:57:49 2021 - [info] Master is down!
Wed Apr 28 22:57:49 2021 - [info] Terminating monitoring script.
Wed Apr 28 22:57:49 2021 - [info] Got exit code 20 (Master dead).
Wed Apr 28 22:57:49 2021 - [info] MHA::MasterFailover version 0.58.
Wed Apr 28 22:57:49 2021 - [info] Starting master failover.
Wed Apr 28 22:57:49 2021 - [info]
Wed Apr 28 22:57:49 2021 - [info] * Phase 1: Configuration Check Phase..
Wed Apr 28 22:57:49 2021 - [info]
Wed Apr 28 22:57:50 2021 - [info] GTID failover mode = 1
Wed Apr 28 22:57:50 2021 - [info] Dead Servers:
Wed Apr 28 22:57:50 2021 - [info] 192.168.178.151(192.168.178.151:3306)
Wed Apr 28 22:57:50 2021 - [info] Checking master reachability via MySQL(double check)...
Wed Apr 28 22:57:50 2021 - [info] ok.
Wed Apr 28 22:57:50 2021 - [info] Alive Servers:
Wed Apr 28 22:57:50 2021 - [info] 192.168.178.161(192.168.178.161:3306)
Wed Apr 28 22:57:50 2021 - [info] 192.168.178.162(192.168.178.162:3306)
Wed Apr 28 22:57:50 2021 - [info] Alive Slaves:
Wed Apr 28 22:57:50 2021 - [info] 192.168.178.161(192.168.178.161:3306) Version=5.7.20-log (oldest major version between slaves) log-bin:enabled
Wed Apr 28 22:57:50 2021 - [info] GTID ON
Wed Apr 28 22:57:50 2021 - [info] Replicating from 192.168.178.151(192.168.178.151:3306)
Wed Apr 28 22:57:50 2021 - [info] 192.168.178.162(192.168.178.162:3306) Version=5.7.20-log (oldest major version between slaves) log-bin:enabled
Wed Apr 28 22:57:50 2021 - [info] GTID ON
Wed Apr 28 22:57:50 2021 - [info] Replicating from 192.168.178.151(192.168.178.151:3306)
Wed Apr 28 22:57:50 2021 - [info] Starting GTID based failover.
Wed Apr 28 22:57:50 2021 - [info]
Wed Apr 28 22:57:50 2021 - [info] ** Phase 1: Configuration Check Phase completed.
Wed Apr 28 22:57:50 2021 - [info]
Wed Apr 28 22:57:50 2021 - [info] * Phase 2: Dead Master Shutdown Phase..
Wed Apr 28 22:57:50 2021 - [info]
Wed Apr 28 22:57:50 2021 - [info] Forcing shutdown so that applications never connect to the current master..
Wed Apr 28 22:57:50 2021 - [warning] master_ip_failover_script is not set. Skipping invalidating dead master IP address.
Wed Apr 28 22:57:50 2021 - [warning] shutdown_script is not set. Skipping explicit shutting down of the dead master.
Wed Apr 28 22:57:51 2021 - [info] * Phase 2: Dead Master Shutdown Phase completed.
Wed Apr 28 22:57:51 2021 - [info]
Wed Apr 28 22:57:51 2021 - [info] * Phase 3: Master Recovery Phase..
Wed Apr 28 22:57:51 2021 - [info]
Wed Apr 28 22:57:51 2021 - [info] * Phase 3.1: Getting Latest Slaves Phase..
Wed Apr 28 22:57:51 2021 - [info]
Wed Apr 28 22:57:51 2021 - [info] The latest binary log file/position on all slaves is mysql-bin.000007:1045
Wed Apr 28 22:57:51 2021 - [info] Retrieved Gtid Set: 741453b1-a83a-11eb-acdf-000c29482705:1-4
Wed Apr 28 22:57:51 2021 - [info] Latest slaves (Slaves that received relay log files to the latest):
Wed Apr 28 22:57:51 2021 - [info] 192.168.178.161(192.168.178.161:3306) Version=5.7.20-log (oldest major version between slaves) log-bin:enabled
Wed Apr 28 22:57:51 2021 - [info] GTID ON
Wed Apr 28 22:57:51 2021 - [info] Replicating from 192.168.178.151(192.168.178.151:3306)
Wed Apr 28 22:57:51 2021 - [info] 192.168.178.162(192.168.178.162:3306) Version=5.7.20-log (oldest major version between slaves) log-bin:enabled
Wed Apr 28 22:57:51 2021 - [info] GTID ON
Wed Apr 28 22:57:51 2021 - [info] Replicating from 192.168.178.151(192.168.178.151:3306)
Wed Apr 28 22:57:51 2021 - [info] The oldest binary log file/position on all slaves is mysql-bin.000007:1045
Wed Apr 28 22:57:51 2021 - [info] Retrieved Gtid Set: 741453b1-a83a-11eb-acdf-000c29482705:1-4
Wed Apr 28 22:57:51 2021 - [info] Oldest slaves:
Wed Apr 28 22:57:51 2021 - [info] 192.168.178.161(192.168.178.161:3306) Version=5.7.20-log (oldest major version between slaves) log-bin:enabled
Wed Apr 28 22:57:51 2021 - [info] GTID ON
Wed Apr 28 22:57:51 2021 - [info] Replicating from 192.168.178.151(192.168.178.151:3306)
Wed Apr 28 22:57:51 2021 - [info] 192.168.178.162(192.168.178.162:3306) Version=5.7.20-log (oldest major version between slaves) log-bin:enabled
Wed Apr 28 22:57:51 2021 - [info] GTID ON
Wed Apr 28 22:57:51 2021 - [info] Replicating from 192.168.178.151(192.168.178.151:3306)
Wed Apr 28 22:57:51 2021 - [info]
Wed Apr 28 22:57:51 2021 - [info] * Phase 3.3: Determining New Master Phase..
Wed Apr 28 22:57:51 2021 - [info]
Wed Apr 28 22:57:51 2021 - [info] Searching new master from slaves..
Wed Apr 28 22:57:51 2021 - [info] Candidate masters from the configuration file:
Wed Apr 28 22:57:51 2021 - [info] Non-candidate masters:
Wed Apr 28 22:57:51 2021 - [info] New master is 192.168.178.161(192.168.178.161:3306)
Wed Apr 28 22:57:51 2021 - [info] Starting master failover..
Wed Apr 28 22:57:51 2021 - [info]
From:
192.168.178.151(192.168.178.151:3306) (current master)
+--192.168.178.161(192.168.178.161:3306)
+--192.168.178.162(192.168.178.162:3306)
To:
192.168.178.161(192.168.178.161:3306) (new master)
+--192.168.178.162(192.168.178.162:3306)
Wed Apr 28 22:57:51 2021 - [info]
Wed Apr 28 22:57:51 2021 - [info] * Phase 3.3: New Master Recovery Phase..
Wed Apr 28 22:57:51 2021 - [info]
Wed Apr 28 22:57:51 2021 - [info] Waiting all logs to be applied..
Wed Apr 28 22:57:51 2021 - [info] done.
Wed Apr 28 22:57:51 2021 - [info] Getting new master's binlog name and position..
Wed Apr 28 22:57:51 2021 - [info] mysql-bin.000001:1045
Wed Apr 28 22:57:51 2021 - [info] All other slaves should start replication from here. Statement should be: CHANGE MASTER TO MASTER_HOST='192.168.178.161', MASTER_PORT=3306, MASTER_AUTO_POSITION=1, MASTER_USER='repl', MASTER_PASSWORD='xxx';
Wed Apr 28 22:57:51 2021 - [info] Master Recovery succeeded. File:Pos:Exec_Gtid_Set: mysql-bin.000001, 1045, 741453b1-a83a-11eb-acdf-000c29482705:1-4
Wed Apr 28 22:57:51 2021 - [warning] master_ip_failover_script is not set. Skipping taking over new master IP address.
Wed Apr 28 22:57:51 2021 - [info] ** Finished master recovery successfully.
Wed Apr 28 22:57:51 2021 - [info] * Phase 3: Master Recovery Phase completed.
Wed Apr 28 22:57:51 2021 - [info]
Wed Apr 28 22:57:51 2021 - [info] * Phase 4: Slaves Recovery Phase..
Wed Apr 28 22:57:51 2021 - [info]
Wed Apr 28 22:57:51 2021 - [info]
Wed Apr 28 22:57:51 2021 - [info] * Phase 4.1: Starting Slaves in parallel..
Wed Apr 28 22:57:51 2021 - [info]
Wed Apr 28 22:57:51 2021 - [info] -- Slave recovery on host 192.168.178.162(192.168.178.162:3306) started, pid: 61068. Check tmp log /var/log/mha/app1/192.168.178.162_3306_20210428225749.log if it takes time..
Wed Apr 28 22:57:53 2021 - [info]
Wed Apr 28 22:57:53 2021 - [info] Log messages from 192.168.178.162 ...
Wed Apr 28 22:57:53 2021 - [info]
Wed Apr 28 22:57:51 2021 - [info] Resetting slave 192.168.178.162(192.168.178.162:3306) and starting replication from the new master 192.168.178.161(192.168.178.161:3306)..
Wed Apr 28 22:57:51 2021 - [info] Executed CHANGE MASTER.
Wed Apr 28 22:57:52 2021 - [info] Slave started.
Wed Apr 28 22:57:52 2021 - [info] gtid_wait(741453b1-a83a-11eb-acdf-000c29482705:1-4) completed on 192.168.178.162(192.168.178.162:3306). Executed 0 events.
Wed Apr 28 22:57:53 2021 - [info] End of log messages from 192.168.178.162.
Wed Apr 28 22:57:53 2021 - [info] -- Slave on host 192.168.178.162(192.168.178.162:3306) started.
Wed Apr 28 22:57:53 2021 - [info] All new slave servers recovered successfully.
Wed Apr 28 22:57:53 2021 - [info]
Wed Apr 28 22:57:53 2021 - [info] * Phase 5: New master cleanup phase..
Wed Apr 28 22:57:53 2021 - [info]
Wed Apr 28 22:57:53 2021 - [info] Resetting slave info on the new master..
Wed Apr 28 22:57:53 2021 - [info] 192.168.178.161: Resetting slave info succeeded.
Wed Apr 28 22:57:53 2021 - [info] Master failover to 192.168.178.161(192.168.178.161:3306) completed successfully.
Wed Apr 28 22:57:53 2021 - [info] Deleted server1 entry from /etc/mha/app1.cnf .
Wed Apr 28 22:57:53 2021 - [info]
----- Failover Report -----
app1: MySQL Master failover 192.168.178.151(192.168.178.151:3306) to 192.168.178.161(192.168.178.161:3306) succeeded
Master 192.168.178.151(192.168.178.151:3306) is down!
Check MHA Manager logs at 162:/var/log/mha/app1/manager for details.
Started automated(non-interactive) failover.
Selected 192.168.178.161(192.168.178.161:3306) as a new master.
192.168.178.161(192.168.178.161:3306): OK: Applying all logs succeeded.
192.168.178.162(192.168.178.162:3306): OK: Slave started, replicating from 192.168.178.161(192.168.178.161:3306)
192.168.178.161(192.168.178.161:3306): Resetting slave info succeeded.
Master failover to 192.168.178.161(192.168.178.161:3306) completed successfully.
5. 查看从库主从复制状态¶
#162
show slave status \G
[root@162 tools]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 17
Server version: 5.7.20-log Source distribution
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show slave status \G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.178.161
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 1045
Relay_Log_File: 162-relay-bin.000002
Relay_Log_Pos: 414
Relay_Master_Log_File: mysql-bin.000001
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: 1045
Relay_Log_Space: 619
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: 61
Master_UUID: 559ab858-a7c0-11eb-8c51-000c29a0a205
Master_Info_File: /application/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: 741453b1-a83a-11eb-acdf-000c29482705:1-4
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
mysql>
#可以看出：Master_Host: 192.168.178.161
#161
show slave status\G
show master status\G
[root@161 tools]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 16
Server version: 5.7.20-log Source distribution
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show slave status\G
Empty set (0.00 sec)
mysql>
mysql> show master status\G
*************************** 1. row ***************************
File: mysql-bin.000001
Position: 1045
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set: 741453b1-a83a-11eb-acdf-000c29482705:1-4
1 row in set (0.00 sec)
mysql> show master status;
+------------------+----------+--------------+------------------+------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+------------------------------------------+
| mysql-bin.000001 | 1045 | | | 741453b1-a83a-11eb-acdf-000c29482705:1-4 |
+------------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)
mysql>
#可以看出，161成为主库了
6. 查看manager配置文件¶
#162
cat /etc/mha/app1.cnf
[root@162 tools]# cat /etc/mha/app1.cnf
[server default]
manager_log=/var/log/mha/app1/manager
manager_workdir=/var/log/mha/app1
master_binlog_dir=/data/mysql #主库二进制日志目录
password=mha
ping_interval=2
repl_password=123
repl_user=repl
ssh_user=root
user=mha
[server2]
hostname=192.168.178.161
port=3306
[server3]
hostname=192.168.178.162
port=3306
[root@162 tools]#
#可以看出，151信息已被移除
7. 查看mha运行状态¶
#162
[root@162 tools]# masterha_check_status --conf=/etc/mha/app1.cnf
app1 is stopped(2:NOT_RUNNING).
[root@162 tools]#
#发现，mha未运行
一旦主库宕机，mha也就宕机了。此时就不是高可用架构了。¶
#mha不能自动把节点加回来，只能手动加，重新配置一遍.恢复过程如下
1. 启动主库¶
#151
systemctl start mysqld
netstat -tunlp |grep 330
[root@151 tools]# systemctl start mysqld
[root@151 tools]# netstat -tunlp |grep 330
tcp6 0 0 :::3306 :::* LISTEN 66127/mysqld
2. 从日志中找到恢复命令¶
#162
vim /var/log/mha/app1/manager
CHANGE MASTER_HOST='192.168.178.161', MASTER_PORT=3306, MASTER_AUTO_POSITION=1, MASTER_USER='repl', MASTER_PASSWORD='xxx';
Wed Apr 28 22:57:51 2021 - [info] Getting new master's binlog name and position..
Wed Apr 28 22:57:51 2021 - [info] mysql-bin.000001:1045
Wed Apr 28 22:57:51 2021 - [info] All other slaves should start replication from here. Statement should be: CHANGE MASTER TO MASTER_HOST='192.168.178.161', MASTER_PORT=3306, MASTER_AUTO_POSITION=1, MASTER_USER='repl', MASTER_PASSWORD='xxx';
Wed Apr 28 22:57:51 2021 - [info] Master Recovery succeeded. File:Pos:Exec_Gtid_Set: mysql-bin.000001, 1045, 741453b1-a83a-11eb-acdf-000c29482705:1-4
3. 恢复¶
#151,登录数据库
CHANGE MASTER TO MASTER_HOST='192.168.178.161', MASTER_PORT=3306, MASTER_AUTO_POSITION=1, MASTER_USER='repl', MASTER_PASSWORD='123';
start slave;
#报错
[root@151 tools]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.20-log Source distribution
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> MASTER TO MASTER_HOST='192.168.178.161', MASTER_PORT=3306, MASTER_AUTO_POSITION=1, MASTER_USER='repl', MASTER_PASSWORD='123';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'MASTER TO MASTER_HOST='192.168.178.161', MASTER_PORT=3306, MASTER_AUTO_POSITION=' at line 1
mysql> start slave;
ERROR 1200 (HY000): The server is not configured as slave; fix in config file or with CHANGE MASTER TO
mysql>
#查看得知，命令敲错了。。。再次执行，成功
mysql> CHANGE MASTER TO MASTER_HOST='192.168.178.161', MASTER_PORT=3306, MASTER_AUTO_POSITION=1, MASTER_USER='repl', MASTER_PASSWORD='123';
Query OK, 0 rows affected, 2 warnings (0.01 sec)
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)
mysql>
4. 查看主从状态¶
#151
show slave status\G
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.178.161
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 1045
Relay_Log_File: 151-relay-bin.000002
Relay_Log_Pos: 414
Relay_Master_Log_File: mysql-bin.000001
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: 1045
Relay_Log_Space: 619
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: 61
Master_UUID: 559ab858-a7c0-11eb-8c51-000c29a0a205
Master_Info_File: /application/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: 741453b1-a83a-11eb-acdf-000c29482705:1-4
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
mysql>
#一切正常
5. 修改mha配置文件，加上151信息¶
#162
vim /etc/mha/app1.cnf
#加上如下信息
[server1]
hostname=192.168.178.151
port=3306
[server default]
manager_log=/var/log/mha/app1/manager
manager_workdir=/var/log/mha/app1
master_binlog_dir=/data/mysql #主库二进制日志目录
password=mha
ping_interval=2
repl_password=123
repl_user=repl
ssh_user=root
user=mha
[server1]
hostname=192.168.178.151
port=3306
[server2]
hostname=192.168.178.161
port=3306
[server3]
hostname=192.168.178.162
port=3306
6. 启动mha¶
nohup masterha_manager --conf=/etc/mha/app1.cnf --remove_dead_master_conf --ignore_last_failover </dev/null >/var/log/mha/app1/manager.log 2>&1 &
7. 查看mha运行状态¶
[root@162 tools]# masterha_check_status --conf=/etc/mha/app1.cnf
app1 (pid:66241) is running(0:PING_OK), master:192.168.178.161
[root@162 tools]#
最后更新:
2022-02-20 08:44:07