验证环境
MySQL 5.7.23 Centos7.4
relay_log_recover=0
验证过程
slave
mysql> start slave;shQuery OK, 0 rows affected (0.45 sec)mysql> show slave status\G*************************** 1. row ***************************Slave_IO_State: Waiting for master to send eventMaster_Host: 192.168.56.101Master_User: replMaster_Port: 3306Connect_Retry: 60Master_Log_File: mysql-bin.000001Read_Master_Log_Pos: 154Relay_Log_File: relaylog.000003Relay_Log_Pos: 367Relay_Master_Log_File: mysql-bin.000001Slave_IO_Running: YesSlave_SQL_Running: Yes...Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 1Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: 1 row in set (0.00 sec)
master
root@localhost : (none):12: > create database hjj;Query OK, 1 row affected (0.00 sec)root@localhost : (none):12: > use hjjDatabase changedroot@localhost : hjj:12: > source /root/net_test_1.sql;root@localhost : hjj:13: > show master status\G*************************** 1. row ***************************File: mysql-bin.000001Position: 39126486Binlog_Do_DB: Binlog_Ignore_DB: Executed_Gtid_Set: 376a65d8-ec05-11e8-becd-080027f2f9fd:1- row in set (0.00 sec)
slave
mysql> show slave status\G*************************** 1. row ***************************Slave_IO_State: Waiting for master to send eventMaster_Host: 192.168.56.101Master_User: replMaster_Port: 3306Connect_Retry: 60Master_Log_File: mysql-bin.000001Read_Master_Log_Pos: 39126486Relay_Log_File: relaylog.000003Relay_Log_Pos: 39126699Relay_Master_Log_File: mysql-bin.000001Slave_IO_Running: YesSlave_SQL_Running: Yes...Retrieved_Gtid_Set: 376a65d8-ec05-11e8-becd-080027f2f9fd:1-202Executed_Gtid_Set: 376a65d8-ec05-11e8-becd-080027f2f9fd:1-202Auto_Position: 1Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: 1 row in set (0.00 sec)mysql> flush logs;Query OK, 0 rows affected (0.14 sec)
master
root@localhost : hjj:13: > source /root/net_test.sql;
slave
mysql> show slave status\G*************************** 1. row ***************************Slave_IO_State: Waiting for master to send eventMaster_Host: 192.168.56.101Master_User: replMaster_Port: 3306Connect_Retry: 60Master_Log_File: mysql-bin.000001Read_Master_Log_Pos: 78197987Relay_Log_File: relaylog.000004Relay_Log_Pos: 39071814Relay_Master_Log_File: mysql-bin.000001Slave_IO_Running: YesSlave_SQL_Running: Yes...Retrieved_Gtid_Set: 376a65d8-ec05-11e8-becd-080027f2f9fd:1-208Executed_Gtid_Set: 376a65d8-ec05-11e8-becd-080027f2f9fd:1-208Auto_Position: 1Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: 1 row in set (0.00 sec)[root@yuelei3 log]# ps -ef|grep mysqld[root@yuelei3 log]# kill -9 9065 7612[1]+ Killed /opt/mysql5.7/bin/mysqld_safe --defaults-file=/data/mysql/f[root@yuelei3 log]# lserror.log mysql-bin.000001 mysql-bin.000002 mysql-bin.index relaylog.000003 relaylog.000004 relaylog.index slow-query.log[root@yuelei3 log]# rm relaylog.000004 rm: remove regular file ‘relaylog.000004’? y[root@yuelei3 log]# /opt/mysql5.7/bin/mysqld_safe --defaults-file=/data/mysql/f & [root@yuelei3 log]# /opt/mysql5.7/bin/mysql -uroot -S /data/mysql/run/mysql.sockmysql> show slave status\G*************************** 1. row ***************************Slave_IO_State: Waiting for master to send eventMaster_Host: 192.168.56.101Master_User: replMaster_Port: 3306Connect_Retry: 60Master_Log_File: mysql-bin.000001Read_Master_Log_Pos: 78197987Relay_Log_File: relaylog.000005Relay_Log_Pos: 454Relay_Master_Log_File: mysql-bin.000001Slave_IO_Running: YesSlave_SQL_Running: Yes...Retrieved_Gtid_Set: 376a65d8-ec05-11e8-becd-080027f2f9fd:1-202Executed_Gtid_Set: 376a65d8-ec05-11e8-becd-080027f2f9fd:1-208Auto_Position: 1Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: 1 row in set (0.00 sec)
验证binlog有关GTID
slave
mysql> show master status;+------------------+----------+--------------+------------------+--------------------------------------------+| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |+------------------+----------+--------------+------------------+--------------------------------------------+| mysql-bin.000003 | 194 | | | 376a65d8-ec05-11e8-becd-080027f2f9fd:1-208 |+------------------+----------+--------------+------------------+--------------------------------------------+1 row in set (0.12 sec)[root@yuelei3 log]# ps -ef|grep mysqld[root@yuelei3 log]# kill -9 11778 10325[1]+ Killed /opt/mysql5.7/bin/mysqld_safe --defaults-file=/data/mysql/f[root@yuelei3 log]# lserror.log mysql-bin.000001 mysql-bin.000002 mysql-bin.000003 mysql-bin.index relaylog.000004 relaylog.000005 relaylog.index slow-query.log[root@yuelei3 log]# rm mysql-bin.000003 rm: remove regular file ‘mysql-bin.000003’? y[root@yuelei3 log]# vim mysql-bin.index [root@yuelei3 log]# /opt/mysql5.7/bin/mysqld_safe --defaults-file=/data/mysql/f &[root@yuelei3 log]# /opt/mysql5.7/bin/mysql -uroot -S /data/mysql/run/mysql.sockmysql> show master status;+------------------+----------+--------------+------------------+--------------------------------------------+| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |+------------------+----------+--------------+------------------+--------------------------------------------+| mysql-bin.000003 | 194 | | | 376a65d8-ec05-11e8-becd-080027f2f9fd:1-208 |+------------------+----------+--------------+------------------+--------------------------------------------+1 row in set (0.00 sec)
