MySQL的Retrieved_Gtid_Set、Executed_Gtid_Set哪里来

验证环境

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文件的第一个事务开始应用