首页 > 代码库 > slave中继日志损坏处理办法
slave中继日志损坏处理办法
1.slave中继日志损坏
当slave意外宕机时，有可能损坏中继日志relay-log，再次开启同步复制时，就会报错：
Last_SQL_Error:Relay log read failure: Could not parse relay log event entry. The possiblereasons are: the master‘s binary log is corrupted (you can check this byrunning ‘mysqlbinlog‘ on the binary log), the slave‘s relay log is corrupted(you can check this by running ‘mysqlbinlog‘ on the relay log), a networkproblem, or a bug in the master‘s or slave‘s MySQL code. If you want to checkthe master‘s binary log or slave‘s relay log, you will be able to know theirnames by issuing ‘SHOW SLAVE STATUS‘ on this slave.
1.1. 模拟中继日志损坏
[root@gflinux103log]# rm relay-bin.00001*
把中继日志全部删除。
1.2. 查看主从数据一致性
master：
mysql>delete from t1 where id=7;
Query OK,1 row affected (0.00 sec)
mysql>select * from t1;
+----+
| id |
+----+
| 1 |
| 2 |
| 3 |
+----+
3 rows inset (0.00 sec)
slave：
mysql>select * from t1;
+----+
| id |
+----+
| 1 |
| 2 |
| 3 |
| 7 |
+----+
4 rows inset (0.00 sec)
mysql>stop slave;
Query OK,0 rows affected (0.01 sec)
mysql>start slave;
Query OK,0 rows affected (0.01 sec)
mysql>show slave status\G;
***************************1. row ***************************
Slave_IO_State: Waiting formaster to send event
Master_Host: 192.168.6.100
Master_User: repl_user
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: bin-log.000001
Read_Master_Log_Pos: 7335
Relay_Log_File: relay-bin.000012
Relay_Log_Pos: 492
Relay_Master_Log_File: bin-log.000001
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 readfailure: Could not parse relay log event entry. The possible reasons are: themaster‘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 byrunning ‘mysqlbinlog‘ on the relay log), a network problem, or a bug in themaster‘s or slave‘s MySQL code. If you want to check the master‘s binary log orslave‘s relay log, you will be able to know their names by issuing ‘SHOW SLAVESTATUS‘ on this slave.
Skip_Counter: 0
Exec_Master_Log_Pos: 7335
Relay_Log_Space: 2211
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 readfailure: Could not parse relay log event entry. The possible reasons are: themaster‘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 byrunning ‘mysqlbinlog‘ on the relay log), a network problem, or a bug in themaster‘s or slave‘s MySQL code. If you want to check the master‘s binary log orslave‘s relay log, you will be able to know their names by issuing ‘SHOW SLAVESTATUS‘ on this slave.
Replicate_Ignore_Server_Ids:
Master_Server_Id: 10
Master_UUID:014ede19-f822-11e3-94e4-080027be9228
Master_Info_File:/opt/mysql/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State:
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp: 140730 13:52:58
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
1 row inset (0.00 sec)
查看中继日志及索引信息：
[root@gflinux103log]# ll
total1178316
-rw-rw----1 mysql mysql 143 Jul 8 13:59 bin-log.000049
-rw-rw----1 mysql mysql 390 Jul 8 08:48 bin-log.index
-rw-r-----1 mysql root 2169794 Jul 30 13:52gflinux.err
-rw-rw----1 mysql mysql 120 Jun 24 11:05gflinux.index
-rw-rw----1 mysql mysql 645656511 Jul 29 17:26 gfsunny-slow.logs
-rw-rw----1 mysql mysql 281 Jul 30 13:52relay-bin.000001
-rw-rw----1 mysql mysql 96 Jul 30 13:52relay-bin.index
[root@gflinux103log]#
[root@gflinux103log]# more relay-bin.index
/opt/mysql/log/relay-bin.000012
/opt/mysql/log/relay-bin.000012
/opt/mysql/log/relay-bin.000001
[root@gflinux103log]#
1.3.解决办法
1.3.1.手工处理
找到同步的binlog日志和pos点，然后重新同步，这样就可以有新的同步日志了。
参数解读：
Slave_IO_Running: Yes | 接收master的binlog信息 |
Master_Log_File: bin-log.000001 | 正在读取master上binlog日志名 |
Read_Master_Log_Pos: 7335 | 正在读取master上binlog日志POS点 |
Slave_SQL_Running: No | 执行写操作 |
Relay_Master_Log_File: bin-log.000001 | 正在同步master上的binlog日志名 |
Exec_Master_Log_Pos: 7335 | 正在同步当前binlog日志的Pos点 |
（1）以Relay_Master_Log_File和Exec_Master_Log_Pos: 7335的参数值为基准，重置主从复制。
mysql>change master to master_log_file=‘bin-log.000001‘,master_log_pos=7335;
Query OK,0 rows affected, 2 warnings (0.01 sec)
（2）重启从复制进程，查看其状态
mysql>start slave;
Query OK,0 rows affected (0.01 sec)
mysql>show slave status\G;
***************************1. row ***************************
Slave_IO_State: Waiting formaster to send event
Master_Host: 192.168.6.100
Master_User: repl_user
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: bin-log.000001
Read_Master_Log_Pos: 7335
Relay_Log_File: relay-bin.000002
Relay_Log_Pos: 281
Relay_Master_Log_File: bin-log.000001
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: 7335
Relay_Log_Space: 448
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: 0
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: 10
Master_UUID:014ede19-f822-11e3-94e4-080027be9228
Master_Info_File:/opt/mysql/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has readall relay log; waiting for the slave I/O thread to update it
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
1 row inset (0.00 sec)
（3）查看中继日志及索引信息
[root@gflinux103log]# ll
mysql 143 Jul 8 13:59 bin-log.000049
-rw-rw----1 mysql mysql 390 Jul 8 08:48 bin-log.index
-rw-r-----1 mysql root 2171426 Jul 30 14:21gflinux.err
-rw-rw----1 mysql mysql 120 Jun 24 11:05 gflinux.index
-rw-rw----1 mysql mysql 645656511 Jul 29 17:26 gfsunny-slow.logs
-rw-rw----1 mysql mysql 167 Jul 30 14:21relay-bin.000001
-rw-rw----1 mysql mysql 281 Jul 30 14:21relay-bin.000002
-rw-rw----1 mysql mysql 64 Jul 30 14:21relay-bin.index
[root@gflinux103log]# more relay-bin.index
/opt/mysql/log/relay-bin.000001
/opt/mysql/log/relay-bin.000002
[root@gflinux103log]#
索引信息自动更新。
1.3.2.自动处理
mysql5.5考虑到slave宕机中继日志损坏这一问题，只要在slave的的配置文件my.cnf里增加一个参数relay_log_recovery=1即可。
salve的重要参数：relay_log_recovery=1
mysql>show variables like ‘relay%‘;
+---------------------------+--------------------------------+
|Variable_name | Value |
+---------------------------+--------------------------------+
|relay_log |/opt/mysql/log/relay-bin |
|relay_log_basename |/opt/mysql/log/relay-bin |
|relay_log_index | /opt/mysql/log/relay-bin.index |
|relay_log_info_file | relay-log.info |
|relay_log_info_repository | FILE |
|relay_log_purge | ON |
|relay_log_recovery |OFF |
|relay_log_space_limit | 0 |
+---------------------------+--------------------------------+
8 rows inset (0.00 sec)
relay_log_purge：启用和禁用中继日志的自动清理，只要他们不需要的时候，默认是启用。全局变量，可以自动修改。
relay_log_recovery={0|1}：紧随服务器启动立即启用自动中继日志恢复。默认值是0，禁用。
relay_log_space_limit：默认为0，没有限制。
slave中继日志损坏处理办法