记录一次MySQL复制问题的处理

备库：

mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: xx.xxx.xxx.xx
Master_User: replicator
Master_Port: 3015
Connect_Retry: 60
Master_Log_File: mysql-bin.006404
Read_Master_Log_Pos: 524204301
Relay_Log_File: slave-relay.016921
Relay_Log_Pos: 251
Relay_Master_Log_File: mysql-bin.006404
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: 524204301
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
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
1 row in set (0.00 sec)

 

SQL线程和IO线程都挂了，那就start slave一下吧。

mysql> start slave;
ERROR 1201 (HY000): Could not initialize master info structure; more error messages can be found in the MySQL error log

 

查看备库上的错误日志：

150916 16:29:06 [ERROR] Failed to open the relay log '/home/mysql/data3015/mysql/slave-relay.016921' (relay_log_pos 251)
150916 16:29:06 [ERROR] Could not find target log during relay log initialization
150916 16:32:09 [ERROR] Failed to open the relay log '/home/mysql/data3015/mysql/slave-relay.016921' (relay_log_pos 251)
150916 16:32:09 [ERROR] Could not find target log during relay log initialization

 

去备库的日志目录去看看：[真的没有slave-relay.016921这个文件]

[root@myworkplace /home/mysql/data3015/mysql]
#cat slave-relay-log.index
/home/mysql/data3015/mysql/slave-relay.016922
/home/mysql/data3015/mysql/slave-relay.016923
/home/mysql/data3015/mysql/slave-relay.016924
/home/mysql/data3015/mysql/slave-relay.016925

 

备库上的处理过程：

1. reset slave;

这是执行show slave status看一把。

mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: xx.xxx.xxx.xx
Master_User: test
Master_Port: 3306
Connect_Retry: 60
Master_Log_File:
Read_Master_Log_Pos: 4
Relay_Log_File: slave-relay.016921
Relay_Log_Pos: 251
Relay_Master_Log_File:
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: 0
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
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
1 row in set (0.00 sec)

2. change master to master_host='xx.xxx.xxx.xx',master_port=3015, master_user='replicator',master_password='xxxxxxxxxxxxx',master_log_file='mysql-bin.006404',master_log_pos= 524204301;

3. start slave;