the master has purged binary logs containing GTIDs that the slave requires的处理

分手后的思念是犯贱 2022-04-16 01:39 112阅读 0赞

从库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

从库复制恢复正常了。