线上有个端口主从复制 SQL线程中断:
具体报错如下:
localhost.(none)>show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.79.112.103
Master_User: replica
Master_Port: 3432
Connect_Retry: 60
Master_Log_File: mysql-bin.000760
Read_Master_Log_Pos: 16037528
Relay_Log_File: relay-bin.002245
Relay_Log_Pos: 5717987
Relay_Master_Log_File: mysql-bin.000759
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: 6283088
Relay_Log_Space: 160936424
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: 1594
Last_SQL_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.
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1121033432
1 row in set (0.00 sec)
ERROR:
No query specified
从上面报错看:
1、master bin-log有问题
2、从库 relay log 有问题
3、网络问题
4、代码bug
首先如果是中继日志坏掉,那只需要找到同步的时间点,然后重新同步,这样就可以有新的中继日志。如果Master上的日志坏了就麻烦了。
检测相关日志,这是中继日值出问题了。
处理方法:需要找到同步的点。
本次恢复依据点为:Relay_Master_Log_File: mysql-bin.000759,Exec_Master_Log_Pos:6283088(需要看从库执行执行点在哪里)
解决方案:
localhost.(none)>slave stop;
localhost.(none)>change master to Master_Log_File='mysql-bin.000759',Master_Log_pos=6283088;
localhost.(none)>slave start;
localhost.(none)>show slave status\G;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 524
Current database: *** NONE ***
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.79.112.103
Master_User: replica
Master_Port: 3432
Connect_Retry: 60
Master_Log_File: mysql-bin.000760
Read_Master_Log_Pos: 16411799
Relay_Log_File: relay-bin.000002
Relay_Log_Pos: 48447878
Relay_Master_Log_File: mysql-bin.000759
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
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: 54730713
Relay_Log_Space: 155026315
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: 55654
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1121033432
1 row in set (0.00 sec)
ERROR:
No query specified