主服务器: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参数的定义是什么呢?
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)
// 问题不存在了