遷移來自51cto博客

由于系統(tǒng)突然死機，重啟之后mysql報關(guān)于 relay log 的錯誤

錯誤如下：

mysql> show slave status \G
*************************** 1. row ***************************
              Slave_IO_State: Waiting for master to send event
                 Master_Host: xx.xx.xx.xx
                 Master_User: xxxxxx
                 Master_Port: 3306
               Connect_Retry: 60
             Master_Log_File: bin-update-log.000189
         Read_Master_Log_Pos: 298793095
              Relay_Log_File: mysqld-relay-bin.000384
               Relay_Log_Pos: 76947288
       Relay_Master_Log_File: bin-update-log.000189
            Slave_IO_Running: Yes
           Slave_SQL_Running: No
             Replicate_Do_DB: xx,xx
         Replicate_Ignore_DB: local
          Replicate_Do_Table:
      Replicate_Ignore_Table: xx._tmp_so6,xx._tmp_so6
     Replicate_Wild_Do_Table:
 Replicate_Wild_Ignore_Table:
                  Last_Errno: 1594
                  Last_Error: Relay log read failure: Could not parse relay log event entry. The possible reasons are: the master's binary log is corrupted (you can check this by running 'mysqlbinlog' on the binary log), the slave's relay log is corrupted (you can check this by running 'mysqlbinlog' on the relay log), a network problem, or a bug in the master's or slave's MySQL code. If you want to check the master's binary log or slave's relay log, you will be able to know their names by issuing 'SHOW SLAVE STATUS' on this slave.

這個replication錯誤，不能用
GLOBAL sql_slave_skip_counter 跳過，而是因為relay log crashed，這個時候就需要找到slave 主機上面crash之前的那個position，然后對應(yīng)的在master 上面的binlog 里面找到對應(yīng)的點 ，然后再slave上面reset slave，重新配置master鏈接信息就好

關(guān)鍵在于在到crash之前的點

    Relay_Log_File: mysqld-relay-bin.000384
           Relay_Log_Pos: 76947288

檢查relay log，找到如下位置

# at 76947261
#140227  3:33:15 server id 259  end_log_pos 76947137    Xid = 2827563451
COMMIT/*!*/;
# at 76947288
#140227  3:33:15 server id 259  end_log_pos 76947216    Query   thread_id=21165454      exec_time=0     error_code=0
SET TIMESTAMP=1393443195/*!*/;
BEGIN
/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

找到位置： 76947216 ;

在master上面找到這個點和對應(yīng)的文件

bin-update-log.000189
# at 76947216
#140227  3:33:15 server id 259  end_log_pos 76947505    Query   thread_id=21165454      exec_time=0     error_code=0
SET TIMESTAMP=1393443195/*!*/;

在slave上面處理

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

mysql> reset slave ;
Query OK, 0 rows affected (0.12 sec)

mysql> change master to master_user ='xxxxxxx',master_host='xx.xx.xx.xx',master_password='xx.xxxx',master_log_file='bin-update-log.000189',master_log_pos=76947216;
Query OK, 0 rows affected (0.00 sec)

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

然后show slave status ,此時整個slave顯示正常