验证环境
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 event
Master_Host: 192.168.56.101
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 154
Relay_Log_File: relaylog.000003
Relay_Log_Pos: 367
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
...
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 1
Replicate_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 hjj
Database changed
root@localhost : hjj:12: > source /root/net_test_1.sql;
root@localhost : hjj:13: > show master status\G
*************************** 1. row ***************************
File: mysql-bin.000001
Position: 39126486
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set: 376a65d8-ec05-11e8-becd-080027f2f9fd:1-202
1 row in set (0.00 sec)
slave
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.56.101
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 39126486
Relay_Log_File: relaylog.000003
Relay_Log_Pos: 39126699
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
...
Retrieved_Gtid_Set: 376a65d8-ec05-11e8-becd-080027f2f9fd:1-202
Executed_Gtid_Set: 376a65d8-ec05-11e8-becd-080027f2f9fd:1-202
Auto_Position: 1
Replicate_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 event
Master_Host: 192.168.56.101
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 78197987
Relay_Log_File: relaylog.000004
Relay_Log_Pos: 39071814
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
...
Retrieved_Gtid_Set: 376a65d8-ec05-11e8-becd-080027f2f9fd:1-208
Executed_Gtid_Set: 376a65d8-ec05-11e8-becd-080027f2f9fd:1-208
Auto_Position: 1
Replicate_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/my.cnf
[root@yuelei3 log]# ls
error.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/my.cnf &
[root@yuelei3 log]# /opt/mysql5.7/bin/mysql -uroot -S /data/mysql/run/mysql.sock
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.56.101
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 78197987
Relay_Log_File: relaylog.000005
Relay_Log_Pos: 454
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
...
Retrieved_Gtid_Set: 376a65d8-ec05-11e8-becd-080027f2f9fd:1-202
Executed_Gtid_Set: 376a65d8-ec05-11e8-becd-080027f2f9fd:1-208
Auto_Position: 1
Replicate_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/my.cnf
[root@yuelei3 log]# ls
error.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/my.cnf &
[root@yuelei3 log]# /opt/mysql5.7/bin/mysql -uroot -S /data/mysql/run/mysql.sock
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.00 sec)
总结
relay_log_recover=0
- slave会扫描最后一个relay log文件，Retrieved_Gtid_Set显示的是当前扫描所得的GTID；io线程会通过扫描所得的最后一个GTID+1（如果Retrieved_Gtid_Set>=Executed_Gtid_Set）为依据来拉取，如果Retrieved_Gtid_Set
relay_log_recover=1
- slave中把所有relay log清除，io线程通过Executed_Gtid_Set后的+1个事务开始拉取并生成新的relay log文件；SQL线程在清除relay log时把Relay_Log_File、Relay_Log_Pos设为空，所以SQL线程从新的relay log文件的第一个事务开始应用
