【就业班作业】【第十五周】使用MHA实现Mysql高可用。
MHA：Master High Availability
1、对主节点进行监控，可实现自动故障转移至其它从节点；
2、通过提升某一从节点为新的主节点，基于主从复制实现，还需要客户端配合实现，目前MHA主要支持一主多从的架构，要搭建MHA,要求一个复制集群中必须最少有三台数据库服务器，一主二从，即一台充当master，一台充当备用master，另外一台充当从库；
3、出于机器成本的考虑，淘宝进行了改造，目前淘宝TMHA已经支持一主一从。
MHA工作原理
1.从宕机崩溃的master保存二进制日志事件（binlog events）
2.识别含有最新更新的slave。
3.应用差异的中继日志（relay log）到其他的slave。
4.应用从master保存的二进制日志事件（binlog events）。
5.提升一个slave为新的master。
6.使其他的slave连接新的master进行复制。
官方网站：
https://code.google.com/archive/p/mysql-master-ha/
https://github.com/yoshinorim/mha4mysql-manager/wiki/Downloads
#MHA包下载（基于centos7）
链接: https://pan.baidu.com/s/1tO8mEXgpkQ1DoSzoAhWaNg
提取码: 36n3
一、节点组件准备
规划4台主机实验环境
192.168.0.105 MHAserver
192.168.0.106 Mysql master server
192.168.0.107 Mysql slave server1
192.168.0.108 Mysql slave server2
1台MHA节点安装节点、管理组件 yum -y install mha4mysql-node-0.57-0.el7.noarch.rpm mha4mysql-manager-0.57-0.el7.noarch.rpm 3台mysql服务器端安装 yum -y install mha4mysql-node-0.57-0.el7.noarch.rpm
二、所有节点都配置SSH密钥登录(4台都要配置)
ssh-keygen -t rsa ssh-copy-id 192.168.0.105 ssh-copy-id 192.168.0.106 ssh-copy-id 192.168.0.107 ssh-copy-id 192.168.0.108
三、MHA节点配置文件编辑
mkdir /etc/mastermha/ vim /etc/mastermha/app1.cnf [server default] user=mhauser password=centos manager_workdir=/data/mastermha/app1/ manager_log=/data/mastermha/app1/manager.log remote_workdir=/data/mastermha/app1/ master_binlog_dir=/data/mysql/ ssh_user=root repl_user=repluser repl_password=centos ping_interval=1
[server1] hostname=192.168.0.106 candidate_master=1
[server2] hostname=192.168.0.107 candidate_master=1
[server3] hostname=192.168.0.108
四、mysql服务器配置
master: vim /etc/my.cnf [mysqld] log-bin server_id=# skip_name_resolve=1 mysql>show master logs mysql>grant replication slave on *.* to repluser@'192.168.0.%' identified by 'centos'; mysql>grant all on *.* to mhauser@'192.168.0.%' identified by 'centos';
slave: vim /etc/my.cnf [mysqld] server_id=# log-bin #进行主备切换的时候，一般都会先对主库进行只读操作(on)，然后主备同步完成后，再把备库置为可读写(off)，可以避免切换的过程中双写引起脏数据。 read_only relay_log_purge=0 skip_name_resolve=1 mysql>grant all on *.* to mhauser@'192.168.0.%' identified by 'centos'; mysql>CHANGE MASTER TO MASTER_HOST='192.168.0.106', MASTER_USER='repluser',MASTER_PASSWORD='centos', MASTER_LOG_FILE='master-bin.000006',MASTER_LOG_POS=343; mysql>START SLAVE;
五、检查各服务器状态及配置
MHA服务器配置检查:
masterha_check_ssh --conf=/etc/mastermha/app1.cnf
masterha_check_repl --conf=/etc/mastermha/app1.cnf
[root@mha-ntp-server-105 mastermha]# masterha_check_ssh --conf=/etc/mastermha/app1.cnf Tue Dec 8 14:32:28 2020 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping. Tue Dec 8 14:32:28 2020 - [info] Reading application default configuration from /etc/mastermha/app1.cnf.. Tue Dec 8 14:32:28 2020 - [info] Reading server configuration from /etc/mastermha/app1.cnf.. Tue Dec 8 14:32:28 2020 - [info] Starting SSH connection tests.. Tue Dec 8 14:32:29 2020 - [debug] Tue Dec 8 14:32:28 2020 - [debug] Connecting via SSH from root@192.168.0.106(192.168.0.106:22) to root@192.168.0.107(192.168.0.107:22).. Tue Dec 8 14:32:28 2020 - [debug] ok. Tue Dec 8 14:32:28 2020 - [debug] Connecting via SSH from root@192.168.0.106(192.168.0.106:22) to root@192.168.0.108(192.168.0.108:22).. Tue Dec 8 14:32:29 2020 - [debug] ok. Tue Dec 8 14:32:29 2020 - [debug] Tue Dec 8 14:32:29 2020 - [debug] Connecting via SSH from root@192.168.0.107(192.168.0.107:22) to root@192.168.0.106(192.168.0.106:22).. Tue Dec 8 14:32:29 2020 - [debug] ok. Tue Dec 8 14:32:29 2020 - [debug] Connecting via SSH from root@192.168.0.107(192.168.0.107:22) to root@192.168.0.108(192.168.0.108:22).. Tue Dec 8 14:32:29 2020 - [debug] ok. Tue Dec 8 14:32:31 2020 - [debug] Tue Dec 8 14:32:29 2020 - [debug] Connecting via SSH from root@192.168.0.108(192.168.0.108:22) to root@192.168.0.106(192.168.0.106:22).. Tue Dec 8 14:32:29 2020 - [debug] ok. Tue Dec 8 14:32:29 2020 - [debug] Connecting via SSH from root@192.168.0.108(192.168.0.108:22) to root@192.168.0.107(192.168.0.107:22).. Tue Dec 8 14:32:30 2020 - [debug] ok. Tue Dec 8 14:32:31 2020 - [info] All SSH connection tests passed successfully. [root@mha-ntp-server-105 mastermha]#
[root@mha-ntp-server-105 ~]# masterha_check_repl --conf=/etc/mastermha/app1.cnf
Tue Dec 8 12:07:46 2020 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Tue Dec 8 12:07:46 2020 - [info] Reading application default configuration from /etc/mastermha/app1.cnf..
Tue Dec 8 12:07:46 2020 - [info] Reading server configuration from /etc/mastermha/app1.cnf..
Tue Dec 8 12:07:46 2020 - [info] MHA::MasterMonitor version 0.57.
Tue Dec 8 12:07:48 2020 - [warning] SQL Thread is stopped(no error) on 192.168.0.106(192.168.0.106:3306)
Tue Dec 8 12:07:48 2020 - [info] GTID failover mode = 0
Tue Dec 8 12:07:48 2020 - [info] Dead Servers:
Tue Dec 8 12:07:48 2020 - [info] Alive Servers:
Tue Dec 8 12:07:48 2020 - [info] 192.168.0.106(192.168.0.106:3306)
Tue Dec 8 12:07:48 2020 - [info] 192.168.0.107(192.168.0.107:3306)
Tue Dec 8 12:07:48 2020 - [info] 192.168.0.108(192.168.0.108:3306)
Tue Dec 8 12:07:48 2020 - [info] Alive Slaves:
Tue Dec 8 12:07:48 2020 - [info] 192.168.0.106(192.168.0.106:3306) Version=10.2.25-MariaDB-log (oldest major version between slaves) log-bin:enabled
Tue Dec 8 12:07:48 2020 - [info] Replicating from 192.168.0.106(192.168.0.106:3306)
Tue Dec 8 12:07:48 2020 - [info] Primary candidate for the new Master (candidate_master is set)
Tue Dec 8 12:07:48 2020 - [info] 192.168.0.107(192.168.0.107:3306) Version=10.2.25-MariaDB-log (oldest major version between slaves) log-bin:enabled
Tue Dec 8 12:07:48 2020 - [info] Replicating from 192.168.0.106(192.168.0.106:3306)
Tue Dec 8 12:07:48 2020 - [info] Primary candidate for the new Master (candidate_master is set)
Tue Dec 8 12:07:48 2020 - [info] 192.168.0.108(192.168.0.108:3306) Version=10.2.25-MariaDB-log (oldest major version between slaves) log-bin:enabled
Tue Dec 8 12:07:48 2020 - [info] Replicating from 192.168.0.106(192.168.0.106:3306)
Tue Dec 8 12:07:48 2020 - [info] Current Alive Master: 192.168.0.106(192.168.0.106:3306)
Tue Dec 8 12:07:48 2020 - [info] Checking slave configurations..
Tue Dec 8 12:07:48 2020 - [info] read_only=1 is not set on slave 192.168.0.106(192.168.0.106:3306).
Tue Dec 8 12:07:48 2020 - [warning] relay_log_purge=0 is not set on slave 192.168.0.106(192.168.0.106:3306).
Tue Dec 8 12:07:48 2020 - [info] Checking replication filtering settings..
Tue Dec 8 12:07:48 2020 - [info] binlog_do_db= , binlog_ignore_db=
Tue Dec 8 12:07:48 2020 - [info] Replication filtering check ok.
Tue Dec 8 12:07:48 2020 - [info] GTID (with auto-pos) is not supported
Tue Dec 8 12:07:48 2020 - [info] Starting SSH connection tests..
Tue Dec 8 12:07:49 2020 - [info] All SSH connection tests passed successfully.
Tue Dec 8 12:07:49 2020 - [info] Checking MHA Node version..
Tue Dec 8 12:07:50 2020 - [info] Version check ok.
Tue Dec 8 12:07:50 2020 - [info] Checking SSH publickey authentication settings on the current master..
Tue Dec 8 12:07:50 2020 - [info] HealthCheck: SSH to 192.168.0.106 is reachable.
Tue Dec 8 12:07:50 2020 - [info] Master MHA Node version is 0.57.
Tue Dec 8 12:07:50 2020 - [info] Checking recovery script configurations on 192.168.0.106(192.168.0.106:3306)..
Tue Dec 8 12:07:50 2020 - [info] Executing command: save_binary_logs --command=test --start_pos=4 --binlog_dir=/data/mysql/ --output_file=/data/mastermha/app1//save_binary_logs_test --manager_version=0.57 --start_file=mysql_log_bin.000001
Tue Dec 8 12:07:50 2020 - [info] Connecting to root@192.168.0.106(192.168.0.106:22)..
Creating /data/mastermha/app1 if not exists.. ok.
Checking output directory is accessible or not..
ok.
Binlog found at /data/mysql/, up to mysql_log_bin.000001
Tue Dec 8 12:07:50 2020 - [info] Binlog setting check done.
Tue Dec 8 12:07:50 2020 - [info] Checking SSH publickey authentication and checking recovery script configurations on all alive slave servers..
Tue Dec 8 12:07:50 2020 - [info] Executing command : apply_diff_relay_logs --command=test --slave_user='mhauser' --slave_host=192.168.0.106 --slave_ip=192.168.0.106 --slave_port=3306 --workdir=/data/mastermha/app1/ --target_version=10.2.25-MariaDB-log --manager_version=0.57 --relay_log_info=/data/mysql/relay-log.info --relay_dir=/data/mysql/ --slave_pass=xxx
Tue Dec 8 12:07:50 2020 - [info] Connecting to root@192.168.0.106(192.168.0.106:22)..
Checking slave recovery environment settings..
Opening /data/mysql/relay-log.info ... ok.
Relay log found at /data/mysql, up to mysql-master-server-106-relay-bin.000001
Temporary relay log file is /data/mysql/mysql-master-server-106-relay-bin.000001
Testing mysql connection and privileges.. done.
Testing mysqlbinlog output.. done.
Cleaning up test file(s).. done.
Tue Dec 8 12:07:51 2020 - [info] Executing command : apply_diff_relay_logs --command=test --slave_user='mhauser' --slave_host=192.168.0.107 --slave_ip=192.168.0.107 --slave_port=3306 --workdir=/data/mastermha/app1/ --target_version=10.2.25-MariaDB-log --manager_version=0.57 --relay_log_info=/data/mysql/relay-log.info --relay_dir=/data/mysql/ --slave_pass=xxx
Tue Dec 8 12:07:51 2020 - [info] Connecting to root@192.168.0.107(192.168.0.107:22)..
Checking slave recovery environment settings..
Opening /data/mysql/relay-log.info ... ok.
Relay log found at /data/mysql, up to mysql-slave--server-107-relay-bin.000002
Temporary relay log file is /data/mysql/mysql-slave--server-107-relay-bin.000002
Testing mysql connection and privileges.. done.
Testing mysqlbinlog output.. done.
Cleaning up test file(s).. done.
Tue Dec 8 12:07:51 2020 - [info] Executing command : apply_diff_relay_logs --command=test --slave_user='mhauser' --slave_host=192.168.0.108 --slave_ip=192.168.0.108 --slave_port=3306 --workdir=/data/mastermha/app1/ --target_version=10.2.25-MariaDB-log --manager_version=0.57 --relay_log_info=/data/mysql/relay-log.info --relay_dir=/data/mysql/ --slave_pass=xxx
Tue Dec 8 12:07:51 2020 - [info] Connecting to root@192.168.0.108(192.168.0.108:22)..
Checking slave recovery environment settings..
Opening /data/mysql/relay-log.info ... ok.
Relay log found at /data/mysql, up to mysql-slave2-server-108-relay-bin.000002
Temporary relay log file is /data/mysql/mysql-slave2-server-108-relay-bin.000002
Testing mysql connection and privileges.. done.
Testing mysqlbinlog output.. done.
Cleaning up test file(s).. done.
Tue Dec 8 12:07:51 2020 - [info] Slaves settings check done.
Tue Dec 8 12:07:51 2020 - [info]
192.168.0.106(192.168.0.106:3306) (current master)
+--192.168.0.106(192.168.0.106:3306)
+--192.168.0.107(192.168.0.107:3306)
+--192.168.0.108(192.168.0.108:3306)
Tue Dec 8 12:07:51 2020 - [info] Checking replication health on 192.168.0.106..
Tue Dec 8 12:07:51 2020 - [error][/usr/share/perl5/vendor_perl/MHA/Server.pm, ln490] Slave IO thread is not running on 192.168.0.106(192.168.0.106:3306)
Tue Dec 8 12:07:51 2020 - [error][/usr/share/perl5/vendor_perl/MHA/ServerManager.pm, ln1526] failed!
Tue Dec 8 12:07:51 2020 - [error][/usr/share/perl5/vendor_perl/MHA/MasterMonitor.pm, ln427] Error happened on checking configurations. at /usr/share/perl5/vendor_perl/MHA/MasterMonitor.pm line 420.
Tue Dec 8 12:07:51 2020 - [error][/usr/share/perl5/vendor_perl/MHA/MasterMonitor.pm, ln525] Error happened on monitoring servers.
Tue Dec 8 12:07:51 2020 - [info] Got exit code 1 (Not master dead).
MySQL Replication Health is NOT OK!
mysql主从复制服务器检查
Mysql Master Server vim /etc/my.cnf [mysqld] log-bin server_id=# skip_name_resolve=1 MariaDB [(none)]>show master logs MariaDB [(none)]>grant replication slave on *.* to repluser@'192.168.0.%' identified by 'centos'; MariaDB [(none)]>grant all on *.* to mhauser@'192.168.0.%' identified by 'centos'; MariaDB [(none)]> show master logs; +----------------------+-----------+ | Log_name | File_size | +----------------------+-----------+ | mysql_log_bin.000001 | 332 | +----------------------+-----------+ 1 row in set (0.00 sec) MariaDB [(none)]> Mysql Slave Server vim /etc/my.cnf [mysqld] server_id=# log-bin #进行主备切换的时候，一般都会先对主库进行只读操作(on)，然后主备同步完成后，再把备库置为可读写(off)。这样可以避免切换的过程中双写引起脏数据。 read_only relay_log_purge=0 skip_name_resolve=1 mysql>grant all on *.* to mhauser@'192.168.0.%' identified by 'centos'; mysql>CHANGE MASTER TO MASTER_HOST='192.168.0.106', MASTER_USER='repluser',MASTER_PASSWORD='centos', MASTER_LOG_FILE='mysql_log_bin.000001',MASTER_LOG_POS=332; mysql>START SLAVE; [root@mysql-slave--server-107 ~]# mysql -uroot -pcentos -e 'show slave status\G' *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.0.106 Master_User: repluser Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql_log_bin.000001 Read_Master_Log_Pos: 332 Relay_Log_File: mysql-slave--server-107-relay-bin.000002 Relay_Log_Pos: 559 Relay_Master_Log_File: mysql_log_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: 332 Relay_Log_Space: 886 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: 1 Master_SSL_Crl: Master_SSL_Crlpath: Using_Gtid: No Gtid_IO_Pos: Replicate_Do_Domain_Ids: Replicate_Ignore_Domain_Ids: Parallel_Mode: conservative SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it [root@mysql-slave--server-107 ~]# [root@mysql-slave2-server-108 ~]# mysql -uroot -pcentos -e 'show slave status\G' *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.0.106 Master_User: repluser Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql_log_bin.000001 Read_Master_Log_Pos: 332 Relay_Log_File: mysql-slave2-server-108-relay-bin.000002 Relay_Log_Pos: 559 Relay_Master_Log_File: mysql_log_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: 332 Relay_Log_Space: 886 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: 1 Master_SSL_Crl: Master_SSL_Crlpath: Using_Gtid: No Gtid_IO_Pos: Replicate_Do_Domain_Ids: Replicate_Ignore_Domain_Ids: Parallel_Mode: conservative SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it [root@mysql-slave2-server-108 ~]#
检查切换效果
[root@mysql-master-server-106 /]# poweroff //master mysql server shutdown Connection closing...Socket close. Connection closed by foreign host. Disconnected from remote host(192.168.0.106) at 15:26:51. [root@mha-ntp-server-105 ~]# masterha_manager --conf=/etc/mastermha/app1.cnf Tue Dec 8 12:10:15 2020 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping. Tue Dec 8 12:10:15 2020 - [info] Reading application default configuration from /etc/mastermha/app1.cnf.. Tue Dec 8 12:10:15 2020 - [info] Reading server configuration from /etc/mastermha/app1.cnf.. ssh: connect to host 192.168.0.106 port 22: Connection refused Tue Dec 8 12:11:22 2020 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping. Tue Dec 8 12:11:22 2020 - [info] Reading application default configuration from /etc/mastermha/app1.cnf.. Tue Dec 8 12:11:22 2020 - [info] Reading server configuration from /etc/mastermha/app1.cnf.. [root@mha-ntp-server-105 ~]# [root@mha-ntp-server-105 ~]# cat /data/mastermha/app1/manager.log ........ ........ Tue Dec 8 12:11:27 2020 - [info] Tue Dec 8 12:11:26 2020 - [info] Starting recovery on 192.168.0.108(192.168.0.108:3306).. Tue Dec 8 12:11:26 2020 - [info] This server has all relay logs. Waiting all logs to be applied.. Tue Dec 8 12:11:26 2020 - [info] done. Tue Dec 8 12:11:26 2020 - [info] All relay logs were successfully applied. Tue Dec 8 12:11:26 2020 - [info] Resetting slave 192.168.0.108(192.168.0.108:3306) and starting replication from the new master 192.168.0.107(192.168.0.107:3306).. Tue Dec 8 12:11:26 2020 - [info] Executed CHANGE MASTER. Tue Dec 8 12:11:26 2020 - [info] Slave started. Tue Dec 8 12:11:27 2020 - [info] End of log messages from 192.168.0.108. Tue Dec 8 12:11:27 2020 - [info] -- Slave recovery on host 192.168.0.108(192.168.0.108:3306) succeeded. Tue Dec 8 12:11:27 2020 - [info] All new slave servers recovered successfully. Tue Dec 8 12:11:27 2020 - [info] Tue Dec 8 12:11:27 2020 - [info] * Phase 5: New master cleanup phase.. Tue Dec 8 12:11:27 2020 - [info] Tue Dec 8 12:11:27 2020 - [info] Resetting slave info on the new master.. Tue Dec 8 12:11:27 2020 - [info] 192.168.0.107: Resetting slave info succeeded. Tue Dec 8 12:11:27 2020 - [info] Master failover to 192.168.0.107(192.168.0.107:3306) completed successfully. Tue Dec 8 12:11:27 2020 - [info] ----- Failover Report ----- app1: MySQL Master failover 192.168.0.106(192.168.0.106:3306) to 192.168.0.107(192.168.0.107:3306) succeeded Master 192.168.0.106(192.168.0.106:3306) is down! Check MHA Manager logs at mha-ntp-server-105:/data/mastermha/app1/manager.log for details. Started automated(non-interactive) failover. The latest slave 192.168.0.107(192.168.0.107:3306) has all relay logs for recovery. Selected 192.168.0.107(192.168.0.107:3306) as a new master. 192.168.0.107(192.168.0.107:3306): OK: Applying all logs succeeded. 192.168.0.108(192.168.0.108:3306): This host has the latest relay log events. Generating relay diff files from the latest slave succeeded. 192.168.0.108(192.168.0.108:3306): OK: Applying all logs succeeded. Slave started, replicating from 192.168.0.107(192.168.0.107:3306) 192.168.0.107(192.168.0.107:3306): Resetting slave info succeeded. Master failover to 192.168.0.107(192.168.0.107:3306) completed successfully.
切换效果显示切换成功
[root@mysql-slave--server-107 ~]# mysql -uroot -pcentos -e 'show slave status;' [root@mysql-slave--server-107 ~]# [root@mysql-slave2-server-108 ~]# mysql -uroot -pcentos -e 'show slave status\G' *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.0.107 Master_User: repluser Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql_log_bin.000001 Read_Master_Log_Pos: 332 Relay_Log_File: mysql-slave2-server-108-relay-bin.000002 Relay_Log_Pos: 559 Relay_Master_Log_File: mysql_log_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: 332 Relay_Log_Space: 886 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: 107 Master_SSL_Crl: Master_SSL_Crlpath: Using_Gtid: No Gtid_IO_Pos: Replicate_Do_Domain_Ids: Replicate_Ignore_Domain_Ids: Parallel_Mode: conservative SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it [root@mysql-slave2-server-108 ~]#
原主用开机，变成备用重新加入
[root@mysql-slave--server-107 ~]# mysql -uroot -pcentos -e 'show master logs;' +----------------------+-----------+ | Log_name | File_size | +----------------------+-----------+ | mysql_log_bin.000001 | 8950 | +----------------------+-----------+ [root@mysql-slave--server-107 ~]# MariaDB [(none)]> stop slave; Query OK, 0 rows affected (0.01 sec) MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='192.168.0.107', MASTER_USER='repluser',MASTER_PASSWORD='centos', MASTER_LOG_FILE='mysql_log_bin.000001',MASTER_LOG_POS=8950; Query OK, 0 rows affected (0.02 sec) MariaDB [(none)]> start slave; Query OK, 0 rows affected (0.01 sec) MariaDB [(none)]> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.0.107 Master_User: repluser Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql_log_bin.000001 Read_Master_Log_Pos: 8950 Relay_Log_File: mysql-master-server-106-relay-bin.000002 Relay_Log_Pos: 559 Relay_Master_Log_File: mysql_log_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: 8950 Relay_Log_Space: 886 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: 107 Master_SSL_Crl: Master_SSL_Crlpath: Using_Gtid: No Gtid_IO_Pos: Replicate_Do_Domain_Ids: Replicate_Ignore_Domain_Ids: Parallel_Mode: conservative SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it 1 row in set (0.01 sec)
（结束）
