Mysql Ralay log read fail error

由于系统突然死机，重启之后mysql报错如下：

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，然后对应的在master 上面的binlog 里面找到对应的点 ，然后再slave上面reset slave，重新配置master链接信息就好

1，关键在于在到crash之前的点，

        Relay_Log_File: mysqld-relay-bin.000384

               Relay_Log_Pos: 76947288

check this relay log and find :

# 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*/;

so the important point is 76947216 ;

2,在master上面找到这个点和对应的文件

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/*!*/;

3 ，在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显示正常

本文出自 “老鹰工作室” 博客，请务必保留此出处http://colinspace.blog.51cto.com/1043642/1364437

Mysql Ralay log read fail error,古老的榕树,5-wow.com