主服务器: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
Introduced5.6.2System VariableNamebinlog_checksumVariable ScopeGlobalDynamic VariableYesPermitted Values (<= 5.6.5)TypestringDefaultNONEValid ValuesNONECRC32Permitted Values (>= 5.6.6)TypestringDefaultCRC32Valid ValuesNONECRC32When 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) // 问题不存在了