当slave意外宕机,有可能会损坏中继日志relay-log,再次开启同步复制时,报错信息如下:
Last_SQL_Error: Error initializing relay log position: I/O error reading the header from the binary log
Last_SQL_Error: Error initializing relay log position: Binlog has had magic number;
It's not a binary log file that can be used by this version of mysql
解决方法:找到同步的binlog日志的POS,然后重新进行同步,这样就可以有新的中继日志了。
下面看个例子:
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.31.110
Master_User: root
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: bin-log.000006
Read_Master_Log_Pos: 8517413
Relay_Log_File: relay-bin.000009
Relay_Log_Pos: 281
Relay_Master_Log_File: bin-log.000006
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: abc
Replicate_Ignore_DB: information_schema,performance_schema,mysql,test
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 1593
Last_Error: Last_SQL_Error: Error initializing relay log position: I/O error reading the header from the binary log
Skip_Counter: 1
Exec_Master_Log_Pos: 8517121
主要关注的参数是:
Master_Log_File: bin-log.000006
Exec_Master_Log_Pos: 8517121
恢复命令如下:
mysql> change master to master_LOG_FILE='bin-log.000006', master_LOG_POS=8517121;
通过这种方法我们已经修复了中继日志。其实MySQL5.5已经考虑到了这个问题,新特性已经加入了自修复特性,在my.cnf
加入 relay_log-recovery=1