从库kill -9重启之后,复制出现异常:

mysql> show slave status\G 
*************************** 1. row *************************** 
               Slave_IO_State:  
                  Master_Host: xxx.xxx.xxx.xxx 
                  Master_User: xxxx 
                  Master_Port: 3306 
                Connect_Retry: 60 
              Master_Log_File: mysql-bin.000355 
          Read_Master_Log_Pos: 215225678 
               Relay_Log_File: relay-log.001016 
                Relay_Log_Pos: 4 
        Relay_Master_Log_File: mysql-bin.000355 
             Slave_IO_Running: No 
            Slave_SQL_Running: No 
              Replicate_Do_DB:  
          Replicate_Ignore_DB: mysql,information_schema,performance_schema,sys 
           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: 215225678 
              Relay_Log_Space: 215226373 
              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: 'The slave is connecting using CHANGE MASTER TO MASTER_AUTO_POSITION = 1, but the master has purged binary logs containing GTIDs that the slave requires.' 
               Last_SQL_Errno: 0 
               Last_SQL_Error:  
  Replicate_Ignore_Server_Ids:  
             Master_Server_Id: xxxxx 
                  Master_UUID: 890e600a-e0f2-11e8-826c-0050568157da 
             Master_Info_File: mysql.slave_master_info 
                    SQL_Delay: 0 
          SQL_Remaining_Delay: NULL 
      Slave_SQL_Running_State:  
           Master_Retry_Count: 86400 
                  Master_Bind:  
      Last_IO_Error_Timestamp: 181114 14:02:04 
     Last_SQL_Error_Timestamp:  
               Master_SSL_Crl:  
           Master_SSL_Crlpath:  
           Retrieved_Gtid_Set:  
            Executed_Gtid_Set: 040a3a9f-b937-11e7-a9ca-286ed488c634:1-386444612:386544650-389781411, 
3279fdcd-7069-11e6-afde-286ed488e688:912705988-916878942:916878944-918363200:918363202-918373936:929542564-931556374:931556377:931556382:931556384-931556385:931556387-931556388:931556391-931556401:931556410-931556412:931556418:931556420:931556422:931556424-931556426:931556429-931556505:931556507-931556586:931556588-931556592:931556594:931556596-931556623:931556625:931556637:931556639:931556641-931556642:931556644:931556646-931556705:931556715:931556719-931556723:931556730-931556733:931556735-931556736:931556738-931556744:931556746-931556756:931556758-931556760:931556762-931556763:931556767:931556769-931556771:931556773:931556777:931556782-931556783:931556785:931556787-931556790:931556794:931556796:931556798-931556828:931556832-931556839:931556841:931556843-931556844:931556847:931556851:931556853:931556858-931556859:931556861-931556886, 
890e600a-e0f2-11e8-826c-0050568157da:1-52297118, 
fc821904-b936-11e7-a45f-286ed488c635:15-17 
                Auto_Position: 1 
         Replicate_Rewrite_DB:  
                 Channel_Name:  
           Master_TLS_Version 

可以看到从库当前已经应用的gtid为890e600a-e0f2-11e8-826c-0050568157da:1-52297118,而主库binlog的位置则应用到了mysql-bin.000355的215225678位置。要是问我怎么那么多不同的uuid,一看就是个不正经的从库,鬼知道它经历了什么。
可以确定的是,主库的binlog没有被清除,因为从库是kill掉之后马上拉起来了,主库也没执行过purge binary log操作。
那么根据master file:mysql-bin.000355,pos:215225678到主库去看个究竟。

#181114 13:29:00 server id xxxx  end_log_pos 215225678 CRC32 0xc972a419       Xid = 605179606 
COMMIT/*!*/; 
# at 215225678 
#181114 13:29:00 server id xxxx  end_log_pos 215225743 CRC32 0x55a06777       GTID    last_committed=185251   sequence_number=185252 
SET @@SESSION.GTID_NEXT= **'890e600a-e0f2-11e8-826c-0050568157da:59553159'**/*!*/; 

发现在pos:215225678之后,GTID_NEXT= ‘890e600a-e0f2-11e8-826c-0050568157da:59553159’
与从库show slave status中的890e600a-e0f2-11e8-826c-0050568157da:1-52297118明显出现了断层,而且还隔得有点远了。
基本可以判断,由于kill -9导致从库记录的相关gtid信息出现异常了,那么如果重新指定正确的gtid呢?
尝试通过

MYSQL>stop slave; 
MYSQL>reset master; 
MYSQL>set @@global.gtid_purged='040a3a9f-b937-11e7-a9ca-286ed488c634:1-386444612:386544650-389781411, 
3279fdcd-7069-11e6-afde-286ed488e688:912705988-916878942:916878944-918363200:918363202-918373936:929542564-931556374:931556377:931556382:931556384-931556385:931556387-931556388:931556391-931556401:931556410-931556412:931556418:931556420:931556422:931556424-931556426:931556429-931556505:931556507-931556586:931556588-931556592:931556594:931556596-931556623:931556625:931556637:931556639:931556641-931556642:931556644:931556646-931556705:931556715:931556719-931556723:931556730-931556733:931556735-931556736:931556738-931556744:931556746-931556756:931556758-931556760:931556762-931556763:931556767:931556769-931556771:931556773:931556777:931556782-931556783:931556785:931556787-931556790:931556794:931556796:931556798-931556828:931556832-931556839:931556841:931556843-931556844:931556847:931556851:931556853:931556858-931556859:931556861-931556886, 
890e600a-e0f2-11e8-826c-0050568157da:1-52297118, 
fc821904-b936-11e7-a45f-286ed488c635:15-17'; 或 
MYSQL>set @@global.gtid_purged='890e600a-e0f2-11e8-826c-0050568157da:1-59553158'; 
MYSQL>start slave; 

启动后是一样的报错。
首先确认当前relay log是已经全部应用完了,那么,用不了gtid高科技,那就用回经典套路吧。
从主库的binlog内容来看,pos215225678之前的事务已经commit了,也就是从这里重新开始不需要重做也不需要回滚,那么可以潇洒地尝试一下:

MYSQL>CHANGE MASTER TO  
MASTER_HOST='xxx.xxx.xxx.xxx', 
MASTER_PORT=3306, 
MASTER_USER='xxxx', 
MASTER_PASSWORD='xxxx', 
master_auto_position=0, 
master_log_file='mysql-bin.000355', 
master_log_pos=215225678; 
MYSQL>start slave 

从库复制恢复正常了。


评论关闭
IT源码网

微信公众号号:IT虾米 (左侧二维码扫一扫)欢迎添加!

省市区三级数据-MySQL