Got fatal error 1236

最新推荐文章于 2023-12-27 00:54:28 发布

置顶轮子创造者

最新推荐文章于 2023-12-27 00:54:28 发布

阅读量613

点赞数

分类专栏：经验之谈

本文链接：https://blog.csdn.net/D1034391316/article/details/61196249

版权

经验之谈专栏收录该内容

3 篇文章 0 订阅

订阅专栏

主服务器：MySQL5.7.12

从服务器：MySQL5.1.73

mysql> show slave status\G
********* 1. row *********
               Slave_IO_State:
                  Master_Host: 10.0.2.2
                  Master_User: sync
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: dingjiacaideMacBook-Pro-bin.000004
          Read_Master_Log_Pos: 4
               Relay_Log_File: mysqld-relay-bin.000002
                Relay_Log_Pos: 167
        Relay_Master_Log_File: dingjiacaideMacBook-Pro-bin.000004
             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: 0
          Exec_Master_Log_Pos: 4
              Relay_Log_Space: 323
              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 'dingjiacaideMacBook'
               Last_SQL_Errno: 0
               Last_SQL_Error:
1 row in set (0.00 sec)

经过查找一些资料得知，原因是因为binlog_checksum这个配置的问题，那么binlog_checksum参数的定义是什么呢？

binlog_checksum

Introduced	5.6.2
System Variable	Name	`binlog_checksum`
Variable Scope	Global
Dynamic Variable	Yes
Permitted Values (<= 5.6.5)	Type	string
Default	`NONE`
Valid Values	`NONE`
`CRC32`
Permitted Values (>= 5.6.6)	Type	string
Default	`CRC32`
Valid Values	`NONE`
`CRC32`

When enabled, this variable causes the master to write a checksum for each event in the binary log. binlog_checksum supports the valuesNONE (disabled) and CRC32. The default is CRC32 as of MySQL 5.6.6, NONE before that.

When binlog_checksum is disabled (value NONE), the server verifies that it is writing only complete events to the binary log by writing and checking the event length (rather than a checksum) for each event.

Changing the value of this variable causes the binary log to be rotated; checksums are always written to an entire binary log file, and never to only part of one.

This variable was added in MySQL 5.6.2.

In MySQL 5.6.6 and later, setting this variable on the master to a value unrecognized by the slave causes the slave to set its own binlog_checksum value to NONE, and to stop replication with an error. (Bug #13553750, Bug #61096) If backward compatibility with older slaves is a concern, you may want to set the value explicitly to NONE.

通过官网得知，MySQL版本5.6.5之前跟5.6.5之后版本binlog_checksum默认的值不同。

所以在主服务器的my.cnf添加下面代码

[mysqld]

binlog_checksum=none

在主服务器上重启MySQL

~ $ mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.12-log Homebrew

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show master status;
+------------------------------------+----------+--------------+------------------+-------------------+
| File                               | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------------------------+----------+--------------+------------------+-------------------+
| dingjiacaideMacBook-Pro-bin.000005 |      150 |              |                  |                   |
+------------------------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

mysql>

进入从服务器

mysql> stop slave;
Query OK, 0 rows affected (0.00 sec)

mysql> change master to master_log_file='dingjiacaideMacBook-Pro-bin.000005',master_log_pos=150;
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 for master to send event
                  Master_Host: 10.0.2.2
                  Master_User: sync
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: dingjiacaideMacBook-Pro-bin.000005
          Read_Master_Log_Pos: 150
               Relay_Log_File: mysqld-relay-bin.000002
                Relay_Log_Pos: 286
        Relay_Master_Log_File: dingjiacaideMacBook-Pro-bin.000005
             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: 150
              Relay_Log_Space: 442
              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: 
1 row in set (0.00 sec)
// 问题不存在了