Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Slave can not h

最新推荐文章于 2024-04-25 23:08:06 发布

雅冰石

最新推荐文章于 2024-04-25 23:08:06 发布

阅读量4.2k

点赞数 2

分类专栏： 2 MySql 学习 # Mysql 主从复制 # MySql 报错集锦

本文链接：https://blog.csdn.net/yabingshi_tech/article/details/53319904

版权

2 MySql 学习同时被 3 个专栏收录

332 篇文章 5 订阅

订阅专栏

MySql 报错集锦

82 篇文章 0 订阅

订阅专栏

Mysql 主从复制

39 篇文章 0 订阅

订阅专栏

一：问题描述

在做mysql版本升级实验时，将5.5.19的从库指向5.6.33的主库，slave slave后，查看slave状态，发现报错：

mysql> show slave status \G;
*************************** 1. row ***************************
               Slave_IO_State: 
                  Master_Host: 10.192.203.202
                  Master_User: RepUser
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000005
          Read_Master_Log_Pos: 120
               Relay_Log_File: master-relay-bin.000007
                Relay_Log_Pos: 150
        Relay_Master_Log_File: mysql-bin.000005
             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: 1
          Exec_Master_Log_Pos: 120
              Relay_Log_Space: 350
              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: 'Slave can not handle replication events with the checksum that master is configured to log; the first event 'mysql-bin.000005' at 120, the last event read from './mysql-bin.000005' at 120, the last byte read from './mysql-bin.000005' at 120.'
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 2
1 row in set (0.00 sec)

主库错误日志也有警告：

[Warning] Master is configured to logreplication events with checksum, but will not send such events to slaves thatcannot process them

二：出错原因

这是由于mysql5.6 的 binlog_checksum 默认设置的是 CRC32。 5.5 或者更早的版本没有这个变量binlog_checksum。

mysql 5.6:

mysql> show variables like'binlog_checksum';

+-----------------+-------+

| Variable_name | Value |

+-----------------+-------+

| binlog_checksum | CRC32 |

+-----------------+-------+

Mysql 5.5或者更早版本：

mysql> show variables like'binlog_checksum';

Empty set (0.00 sec)

三：解决办法

修改主库配置文件：添加binlog_checksum=none，然后重启主库master。

#注意：我发现set global binlog_checksum= none这种方式虽然看起来修改成功了，可是重启完数据库，又变成了之前的CRC32。

然后在主库show master status;

在从库指定这个新的位置即可。

如：

主库：
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB |Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000009 |      120 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
从库：
mysql> STOP SLAVE;
Query OK, 0 rows affected (0.00 sec) 
mysql> change master tomaster_log_file='mysql-bin.000009' ,master_log_pos=120,master_host='10.192.203.202',master_user='RepUser',master_password='beijing',master_port=3306;
Query OK, 0 rows affected (0.03 sec)
 mysql> start slave;
Query OK, 0 rows affected (0.00 sec) 
mysql> show slave status \G;
*************************** 1. row***************************
               Slave_IO_State: Waiting formaster to send event
                  Master_Host: 10.192.203.202
                  Master_User: RepUser
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000009
         Read_Master_Log_Pos: 120
               Relay_Log_File:master-relay-bin.000002
                Relay_Log_Pos: 266
       Relay_Master_Log_File: mysql-bin.000009
            Slave_IO_Running: Yes
           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: 120
              Relay_Log_Space: 423
              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: 0
Master_SSL_Verify_Server_Cert: No
               Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
 Replicate_Ignore_Server_Ids:
            Master_Server_Id: 2
1 row in set (0.00 sec)
 
ERROR:
No query specified
 
mysql>

--本篇文章参考自： https://my.oschina.net/congqian/blog/138485