GTID切换至传统复制 异常处理

背景：

     由于某些特殊原因，需要将原有的GTID模式切换成传统的数据同步方式。

     在主库修改配置，重启之后。发现主从同步异常：

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: 
                  Master_Host: 192.168.60.151
                  Master_User: slave
                  Master_Port: 6200
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000026
          Read_Master_Log_Pos: 636005683
               Relay_Log_File: mysql-relay-bin.2987503
                Relay_Log_Pos: 283
        Relay_Master_Log_File: mysql-bin.000026
             Slave_IO_Running: No
            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: 636005683
              Relay_Log_Space: 457
              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: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 1236
                Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Found a Gtid_log_event or Previous_gtids_log_event when @@GLOBAL.GTID_MODE = OFF.; the first event 'mysql-bin.000026' at 636005683, the last event read from './mysql-bin.000026' at 636005731, the last byte read from './mysql-bin.000026' at 636005731.'
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 2
                  Master_UUID: 8e8a08c6-8163-11e6-9e94-1418774981ad
             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 the slave I/O thread to update it
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 170228 10:41:52
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 229f8f5a-800d-11e6-95dc-14187749b52e:1-8557222,
8e8a08c6-8163-11e6-9e94-1418774981ad:7140-18782652
                Auto_Position: 0
1 row in set (0.00 sec)

分析原因：

     由于先停止的从库，然后再停的主库。所以从库的relaylog存在一定时间延迟，导致从库无法及时加载主库中日志。如果先停主库，再停从库，没有发生延迟的binlog重放，那么就不会存在这个问题。

解决：

首先通过mysqlbinlog导出日志：mysql-bin.000026，定位到具体的日志位置。

SET TIMESTAMP=1488218046/*!*/;
BEGIN
/*!*/;
# at 636005448
#170228  1:54:06 server id 2  end_log_pos 636005652 CRC32 0x1c264ab0    Query   thread_id=41705729      exec_time=0     error_code=0
SET TIMESTAMP=1488218046/*!*/;
UPDATE trans_truck SET updated_at = '1488218046', bind_status = '0', carry_status = '0' WHERE truck_id='1206028132801088420'
/*!*/;
# at 636005652
#170228  1:54:06 server id 2  end_log_pos 636005683 CRC32 0x5977b0e2    Xid = 1448704361
COMMIT/*!*/;
# at 636005683
#170228  1:54:06 server id 2  end_log_pos 636005731 CRC32 0xc55897c7    GTID [commit=yes]
SET @@SESSION.GTID_NEXT= '8e8a08c6-8163-11e6-9e94-1418774981ad:18781524'/*!*/;
# at 636005731
#170228  1:54:06 server id 2  end_log_pos 636005816 CRC32 0xaece3477    Query   thread_id=41705729      exec_time=0     error_code=0
SET TIMESTAMP=1488218046/*!*/;
BEGIN
/*!*/;
# at 636005816
#170228  1:54:06 server id 2  end_log_pos 636006019 CRC32 0x0140a12a    Query   thread_id=41705729      exec_time=0     error_code=0
SET TIMESTAMP=1488218046/*!*/;
UPDATE trans_truck SET updated_at = '1488218046', bind_status = '0', carry_status = '0' WHERE truck_id='361743945761243113'
/*!*/;
# at 636006019
#170228  1:54:06 server id 2  end_log_pos 636006050 CRC32 0x1839e02e    Xid = 1448704365
COMMIT/*!*/;
# at 636006050
#170228  1:54:06 server id 2  end_log_pos 636006098 CRC32 0xb476ba65    GTID [commit=yes]
SET @@SESSION.GTID_NEXT= '8e8a08c6-8163-11e6-9e94-1418774981ad:18781525'/*!*/;
# at 636006098
#170228  1:54:06 server id 2  end_log_pos 636006183 CRC32 0xb33f96f2    Query   thread_id=41705729      exec_time=0     error_code=0
SET TIMESTAMP=1488218046/*!*/;

从库进行如下处理：

1:主库mysqlbinlog导出从库延迟的binlog，在从库重放。文中对应“ at 636005731”后此文件的所有语句。
2:stop slave;
3:change master  to  master_log_file='mysql-bin.000027',master_log_pos=4;  （在数据库重启之后，创建了新的binlog日志文件）
4:start slave;

数据恢复同步。