對于主從架構的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文件顯示的不是同一個文件。
Master_Log_File
The name of the master binary log file from which the I/O thread is currently reading.
slave的IO線程當前正在讀取的master二進制日志文件名。
Relay_Master_Log_File
The name of the master binary log file containing the most recent event executed by the SQL thread.
slave的Sql線程最近執行的master二進制日志文件名。(該文件有可能是滯后于IO線程正在讀取的二進制日志文件)
Read_Master_Log_Pos
The position in the current master binary log file up to which the I/O thread has read.
Exec_Master_Log_Pos
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" ] && 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>/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
