ERROR:
091104 11:12:49 [ERROR] Failed to open the relay log '/var/run/mysqld/mysqld-relay-bin.9339843' (relay_log_pos 233)
091104 11:12:49 [ERROR] Could not find target log during relay log initialization
091104 11:12:49 [ERROR] Failed to initialize the master info structure
Due to the way in which the master and slaves where stopped the relay logs got out of sync, hence replication would not restart.
Found the solution was to
a) make a note of Master_Log_File and Exec_Master_Log_Pos
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: mk-tacacs-1.auth.as9105.net
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: log-bin.000008
Read_Master_Log_Pos: 9684
Relay_Log_File: mysqld-relay-bin.9339637
Relay_Log_Pos: 233
Relay_Master_Log_File: log-bin.000008
Slave_IO_Running: No
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: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 9684
Relay_Log_Space: 0
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: NULL
b) reset the slave and bypass the relay logs by configuring the replication again.
reset slave;
CHANGE MASTER TO MASTER_HOST='mk-tacacs-1.auth.as9105.net'
CHANGE MASTER TO MASTER_LOG_FILE='log-bin.000008';
CHANGE MASTER TO MASTER_LOG_POS=9684;
start slave;
You should reset to the position of the SQL thread, not the position of the I/O Thread.
ReplyDeleteSo use Exec_Master_Log_Pos and Relay_Master_Log_File.
I/O Thread: Read_Master_Log_Pos and Master_Log_File
SQL Thread: Exec_Master_Log_Pos and Relay_Master_Log_File
Also be aware of multi threaded slave in 5.6+.