【MySQL】解決mysql的 1594 錯誤

對于主從架構的mysql,當發生主機斷電或者其他原因異常crash的時候, slave的容易發生讀取binlog出錯的問題，最常見的是 

show slave status \G;

Master_Log_File: mysql-bin.000029

Read_Master_Log_Pos: 3154083

Relay_Log_File:  relay-bin.000478

Relay_Log_Pos: 633

Relay_Master_Log_File: mysql-bin.000027

Slave_IO_Running: Yes

Slave_SQL_Running: No

Replicate_Do_DB: 

Replicate_Ignore_DB: 

Replicate_Do_Table: 

Replicate_Ignore_Table: 

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.

Skip_Counter: 0

Exec_Master_Log_Pos: 234663436

依據錯誤描述提示，顯然slave sql 程序讀取不到relay log。

解決該問題之前先了解幾個參數:

mysql的主從複制的原理可知,slave 的sql線程理論上來說是延遲于IO線程,show slave status 查詢時 Relay_Master_Log_File和Master_Log_File檔案顯示的不是同一個檔案。

<b>Master_Log_File</b>

The name of the master binary log file from which the I/O thread is currently reading.

slave的IO線程目前正在讀取的master二進制日志檔案名。

<b>Relay_Master_Log_File</b>

The name of the master binary log file containing the most recent event executed by the SQL thread.

slave的Sql線程最近執行的master二進制日志檔案名。（該檔案有可能是滞後于IO線程正在讀取的二進制日志檔案）

<b>Read_Master_Log_Pos</b>

The position in the current master binary log file up to which the I/O thread has read.

<b>Exec_Master_Log_Pos</b>

The position in the current master binary log file to which the SQL thread has read and executed, marking the start of the next transaction or event to be processed. You can use this value with the CHANGE MASTER TO statement's MASTER_LOG_POS option when starting a new slave from an existing slave, so that the new slave reads from this point. The coordinates given by (Relay_Master_Log_File, Exec_Master_Log_Pos) in the master's binary log correspond to the coordinates given by (Relay_Log_File, Relay_Log_Pos) in the relay log.

slave的Sql線程已經讀并且執行的master二進制日志檔案的位置,标記下一個被執行的事務或事件的開始位置。

你可以将該值應用于兩台slave演變為主從結構的應用場景中,新的slave可以在change master to語句中使用該值作為master_log_pos選項的值。master二進制日志檔案的(Relay_Master_Log_File, Exec_Master_Log_Pos) 的坐标對應于slave中繼日志(Relay_Log_File,Relay_Log_Pos) 坐标.

#!/bin/bash

#created by yangyi 

[ -z "$1" ] &amp;&amp; exit 0 || PORT=$1

repair_1594()

{

  local portlist=$1

  for my_port in $portlist 

  do

    Last_SQL_Errno=$(mysql -uroot -h127.0.0.1 -P${my_port}  -Ae"show slave status \G"  2&gt;/dev/null | grep Last_SQL_Errno | awk '{print $2}' )

    echo ${Last_SQL_Errno}

    Master_Host=`mysql -uroot -h127.0.0.1 -P${PORT} -Ae"show slave status \G" | grep Master_Host |awk '{print $2}'`

    Relay_Master_Log_File=`mysql -uroot -h127.0.0.1 -P${PORT}  -Ae"show slave status \G" | grep Relay_Master_Log_File |awk '{print $2}'`

    Exec_Master_Log_Pos=`mysql -uroot -h127.0.0.1 -P${PORT}  -Ae"show slave status \G" | grep Exec_Master_Log_Pos  |awk '{print $2}'`

    sql="change master to master_host='${Master_Host}',master_port=$PORT, master_user='rep',master_password='yangyi@rac1',master_log_file='${Relay_Master_Log_File}',master_log_pos=${Exec_Master_Log_Pos};"

    mysql -uroot -h127.0.0.1 -P$PORT -e " stop slave ; sleep 1; ${sql} ;start slave ;"

    sleep 1

    is_OK=`mysql -uroot -h127.0.0.1 -P$PORT -p123456 -e "show slave status  \G"| grep Seconds_Behind_Master | awk '{print $2}'`    

    if [[ ${is_OK} -ge 0 ]];

    then 

      echo  "instance : $my_port is recovered !!!!'"

    else

      echo  "instance : $my_port is not OK,PLS CHECK WITH MANUL !!!!'"

    fi

 done 

}

repair_1594 $PORT 

exit 0