MySQL复制常见错误 -- slave中继日志relay-log损坏

当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