mysql主从同步故障后恢复（Got fatal error 1236 from master when reading data from binary log）

数据库主从故障不要慌，切莫想着把数据拷贝过来，重新做主从，

因为很难区分出差异化数据，数据量大几十亿，几百，几千亿的数据你拷贝吧，整理差异化数据吧，累死你，

还影响生产的正常使用所以今天这个笔记很重要

一.mysql死掉服务器未断电情况下可以这样试试

stop slave

set global sql_slave_skip_counter=1 ####会连续跳过若干个event，直到当前所在的事务结束

start slave      #重新开启主从

 

然后查看slave情况

show  slave status\G;

可以看到

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

Seconds_Behind_Master为延迟数量

正在努力的同步中，过一段时间Seconds_Behind_Master=0时

未同步的数据就会全部同步过来了。

二.数据库因断电出现不同步

 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 'edu-mysql-bin.000130' at 652575, the last event read from './edu-mysql-bin.000130

1.show  slave status\G;

 Slave_IO_State: 
                  Master_Host: 保密
                  Master_User: 保密
                  Master_Port: 保密
                Connect_Retry: 60
              Master_Log_File: edu-mysql-bin.000125
          Read_Master_Log_Pos: 3031525
               Relay_Log_File: edu-mysql-relay-bin.000230
                Relay_Log_Pos: 242474377
        Relay_Master_Log_File: edu-mysql-bin.000122
             Slave_IO_Running: No
            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: 1594
                   Last_Error: Relay log read failure: Could not parse relay log event entry. The possible reasons are: the master's binary log is corrupted (you can check this by running 'mysqlbinlog' on the binary log), the slave's relay log is corrupted (you can check this by running 'mysqlbinlog' on the relay log), a network problem, or a bug in the master's or slave's MySQL code. If you want to check the master's binary log or slave's relay log, you will be able to know their names by issuing 'SHOW SLAVE STATUS' on this slave.
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 552813779
              Relay_Log_Space: 254268481
              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: 'Client requested master to start replication from position > file size; the first event 'edu-mysql-bin.000125' at 3031525, the last event read from './edu-mysql-bin.000125' at 4, the last byte read from './edu-mysql-bin.000125' at 4.'
               Last_SQL_Errno: 1594
               Last_SQL_Error: Relay log read failure: Could not parse relay log event entry. The possible reasons are: the master's binary log is corrupted (you can check this by running 'mysqlbinlog' on the binary log), the slave's relay log is corrupted (you can check this by running 'mysqlbinlog' on the relay log), a network problem, or a bug in the master's or slave's MySQL code. If you want to check the master's binary log or slave's relay log, you will be able to know their names by issuing 'SHOW SLAVE STATUS' on this slave.
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 243
                  Master_UUID: 8ddd0aa9-12fb-11e9-9ea3-20040fef4f7c
             Master_Info_File: /home/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: 
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 210527 11:49:00
     Last_SQL_Error_Timestamp: 210527 11:46:54
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0

2.找到Master_Log_File对应的值 edu-mysql-bin.000125 去主库查找这个文件位置

find -name edu-mysql-bin.000125

找到之后输出到文本里方便查看

mysqlbinlog /home/mysql/edu-mysql-bin.000125 > mysqlError.txt

定位到最后一次断电时间的之后mysql重启之后 end_log_pos 的值

3.重新设置postion

stop slave;

change master to master_log_file='edu-mysql-bin.000125',master_log_pos=3025335;

start slave;

show  slave status\G;

如果不行可以再执行一下 set global sql_slave_skip_counter

stop slave

set global sql_slave_skip_counter=1 ####会连续跳过若干个event，直到当前所在的事务结束

start slave      #重新开启主从

 

注意：至此主从同步是已经成功了，一次断电按上面的步骤就可以恢复了，但中间断电四次产生的错误日志不可能随着binlog一起恢复的，需要手动查询sql定位到，

比如断电是发生8点50-12点之间，那就比对这期间数据差异进行恢复就行了

 

如果是主主从多台集群，可按照上面的方法一台一台恢复，不说了，说多了都是泪，破集团实施，装个服务器都搞断电

成功，撒花鼓掌，原创不易，如果帮到你了来波关注吧