数据库状态
主库1：192.168.1.246:3306 从库1：192.168.1.246:3307 从库2：192.168.1.246:3308 主库1 (root:127.0.0.1:3306) [db3306]> show master status; +------------------+----------+--------------+------------------+-------------------------------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------------------------------+ | mysql-bin.000002 | 2464 | | | d023f67f-4a90-11e7-9eff-0800271d97b5:1-12 | +------------------+----------+--------------+------------------+-------------------------------------------+ 从库1 (root:127.0.0.1:3307) [test]> show master status; +------------------+----------+--------------+------------------+-------------------------------------------------------------------------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------------------------------------------------------------------------+ | mysql-bin.000008 | 2449 | | | 6e71bd59-4a97-11e7-ad35-0800271d97b5:1-4, d023f67f-4a90-11e7-9eff-0800271d97b5:1-12 | +------------------+----------+--------------+------------------+-------------------------------------------------------------------------------------+ 从库2 (root:127.0.0.1:3308) [test]> show master status; +------------------+----------+--------------+------------------+-----------------------------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-----------------------------------------+ | mysql-bin.000008 | 426 | | | d023f67f-4a90-11e7-9eff-0800271d97b5:11 | +------------------+----------+--------------+------------------+-----------------------------------------+ 此时从库2与主库落后一个事物，并且3307本身做过一些操作产生了自己的GTID--6e71bd59-4a97-11e7-ad35-0800271d97b5:1-4 假设此时主库故障，从库1选为新主库，从库2复制，并不需要从库1产生的GTID--6e71bd59-4a97-11e7-ad35-0800271d97b5:1-4的事物， 操作如下： (root:127.0.0.1:3308) [test]> reset master ; Query OK, 0 rows affected (0.04 sec) (root:127.0.0.1:3308) [test]> set global gtid_purged='6e71bd59-4a97-11e7-ad35-0800271d97b5:1-4,d023f67f-4a90-11e7-9eff-0800271d97b5:1-11'; Query OK, 0 rows affected (0.02 sec) (root:127.0.0.1:3308) [test]> show master status; +------------------+----------+--------------+------------------+-------------------------------------------------------------------------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------------------------------------------------------------------------+ | mysql-bin.000008 | 154 | | | 6e71bd59-4a97-11e7-ad35-0800271d97b5:1-4, d023f67f-4a90-11e7-9eff-0800271d97b5:1-11 | +------------------+----------+--------------+------------------+-------------------------------------------------------------------------------------+ (root:127.0.0.1:3308) [test]> CHANGE MASTER TO -> MASTER_HOST = '192.168.1.246', -> MASTER_PORT = 3307, -> MASTER_USER = 'repl', -> MASTER_PASSWORD = 'repl', -> MASTER_AUTO_POSITION = 1; Query OK, 0 rows affected, 2 warnings (0.02 sec) (root:127.0.0.1:3308) [test]> start slave; Query OK, 0 rows affected (0.01 sec) (root:127.0.0.1:3308) [test]> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.1.246 Master_User: repl Master_Port: 3307 Connect_Retry: 60 Master_Log_File: mysql-bin.000008 Read_Master_Log_Pos: 2449 Relay_Log_File: relay-bin.000002 Relay_Log_Pos: 686 Relay_Master_Log_File: mysql-bin.000008 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: 2449 Relay_Log_Space: 927 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: 10153307 Master_UUID: 6e71bd59-4a97-11e7-ad35-0800271d97b5 Master_Info_File: mysql.slave_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: d023f67f-4a90-11e7-9eff-0800271d97b5:12 Executed_Gtid_Set: 6e71bd59-4a97-11e7-ad35-0800271d97b5:1-4, d023f67f-4a90-11e7-9eff-0800271d97b5:1-12 Auto_Position: 1 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: 1 row in set (0.00 sec)