mysql服务器断电Got fatal error 1236

 

 

机房停电，mysql启动后发现从库报错误:

Last_IO_Error: Got fatal error 1236 from master when reading data from binary log:
'Client requested master to start replication from position > file size; 
the first event 'binlog.000140' at 1009874186, 
the last event read from '/home/mysql/binlog/binlog.000140' at 4,
the last byte read from '/home/mysql/binlog/binlog.000140' at 4.'

解决办法:

mysqlbinlog /home/mysql/binlog/binlog.000140>my.log

[root@dev-env-22 binlog]# tail -f my.log
'/*!*/;
# at 1009594103
#211019 15:28:58 server id 1 end_log_pos 1009594134 CRC32 0xa990442b Xid = 17058793169
COMMIT/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog *//*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

发现end_log_pos 1009594134比报错的1009874186小很多

 

从end_log_pos 1009594134开始应用

stop slave;
change master to master_host='192.168.1.22',master_port=3306,master_user='repl',master_password='repl',master_log_file='binlog.000140',master_log_pos=1009594134;
start slave;

 

继续报错

继续报错:
mysql> show slave status G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.1.22
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: binlog.000141
          Read_Master_Log_Pos: 636751
               Relay_Log_File: relay-bin.000003
                Relay_Log_Pos: 288515
        Relay_Master_Log_File: binlog.000141
             Slave_IO_Running: Yes
            Slave_SQL_Running: No
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 1032
                   Last_Error: Could not execute Delete_rows event on table db_miao.user_login_log; Can't find record in 'user_login_log', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log binlog.000141, end_log_pos 288719
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 288351
              Relay_Log_Space: 637273
              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: 0
                Last_IO_Error: 
               Last_SQL_Errno: 1032
               Last_SQL_Error: Could not execute Delete_rows event on table db_miao.user_login_log; Can't find record in 'user_login_log', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log binlog.000141, end_log_pos 288719
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1
                  Master_UUID: c95fcd3a-98ae-11e5-b124-5820b106a87c
             Master_Info_File: /home/mysql/data/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: 
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 211019 16:42:33
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: c95fcd3a-98ae-11e5-b124-5820b106a87c:89966674-89967893
            Executed_Gtid_Set: 68a1789e-1f86-11e9-b06d-9cb654b262cc:1-2,
c95fcd3a-98ae-11e5-b124-5820b106a87c:1-26487091:26487093-89967141
                Auto_Position: 0
1 row in set (0.00 sec)

ERROR: 
No query specified

 

在从库上执行，使用工具跳过错误号
/opt/percona-toolkit-3.1.0/bin/pt-slave-restart --error-numbers=1032 -h localhost -uroot -pyeemiao3040

 

[root@dev-env23 ~]# /opt/percona-toolkit-3.1.0/bin/pt-slave-restart --error-numbers=1032 -h localhost -uroot -pyeemiao3040
2021-10-19T16:47:23 h=localhost,p=...,u=root relay-bin.000003 288515 1032
2021-10-19T16:47:26 h=localhost,p=...,u=root relay-bin.000003 329443 1032
2021-10-19T16:47:41 h=localhost,p=...,u=root relay-bin.000003 527565 1032
2021-10-19T16:47:42 h=localhost,p=...,u=root relay-bin.000003 576529 1032
一直没有停,但是查看从库状态已经是正常的,一直等