mariadb(mysql)从库relaylog损坏无法同步的处理方法

故障说明

晚上备用服务器自动重启，收到报警，备用服务器上的mariadb从库无法去同步主库。启动mariadb后，报如下错误（重点看红色字体） 

mariadb_1              | 2018-09-15 17:04:28 140262750803840 [Note] Plugin 'FEEDBACK' is disabled.
mariadb_1              | 2018-09-15 17:04:28 140262750803840 [Note] Recovering after a crash using mysql-bin
mariadb_1              | 2018-09-15 17:04:28 140262750803840 [Note] Starting crash recovery...
mariadb_1              | 2018-09-15 17:04:28 140262750803840 [Note] Crash recovery finished.
mariadb_1              | 2018-09-15 17:04:28 140262750803840 [Note] Server socket created on IP: '0.0.0.0'.
mariadb_1              | 2018-09-15 17:04:28 140262750803840 [Warning] 'proxies_priv' entry '@% xxx@xxx' ignored in --skip-name-resolve mode.
mariadb_1              | 2018-09-15 17:04:28 140262750803840 [Note] Reading of all Master_info entries succeded
mariadb_1              | 2018-09-15 17:04:28 140262750803840 [Note] Added new Master_info '' to hash table
mariadb_1              | 2018-09-15 17:04:28 140262750803840 [Warning] Neither --relay-log nor --relay-log-index were used; so replication may break when this MySQL server acts as a slave and has his hostname changed!! Please use '--log-basename=#' or '--relay-log=mysqld-relay-bin' to avoid this problem.
mariadb_1              | 2018-09-15 17:04:28 140262750803840 [Note] mysqld: ready for connections.
mariadb_1              | Version: '10.2.14-MariaDB-10.2.14+maria~jessie-log'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  mariadb.org binary distribution
mariadb_1              | 2018-09-15 17:04:28 140261962921728 [Note] Slave SQL thread initialized, starting replication in log 'mysql-bin.000239' at position 13999540, relay log './mysqld-relay-bin.000261' position: 13999839
mariadb_1              | 2018-09-15 17:04:28 140261962921728 [ERROR] Error in Log_event::read_log_event(): 'Event truncated', data_len: 489, event_type: 2
mariadb_1              | 2018-09-15 17:04:28 140261962921728 [ERROR] Error reading relay log event: slave SQL thread aborted because of I/O error
mariadb_1              | 2018-09-15 17:04:28 140261962921728 [ERROR] Slave SQL: 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. Internal MariaDB error code: 1594
mariadb_1              | 2018-09-15 17:04:28 140261962921728 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'mysql-bin.000239' position 13999540
mariadb_1              | 2018-09-15 17:04:28 140261962921728 [Note] Slave SQL thread exiting, replication stopped in log 'mysql-bin.000239' at position 13999540
mariadb_1              | 2018-09-15 17:04:28 140261963224832 [Note] Slave I/O thread: connected to master 'xxx@xxx.xxx.xxx.xxx:3306',replication started in log 'mysql-bin.000239' at position 13999582
mariadb_1              | 2018-09-15 17:04:28 140261711652608 [Note] InnoDB: Buffer pool(s) load completed at 180915 17:04:28

原因分析

 

mariadb_1              | 2018-09-15 17:04:28 140261962921728 [ERROR] Error in Log_event::read_log_event(): 'Event truncated', data_len: 489, event_type: 2

这个错误表示relay-log存在错误，导致从库想把mysqld-relay-bin.000261的13999839恢复到mysql-bin.000239的13999540失败。relay-log出错，可能是从库relay-log自身出错，也可能是主库的binlog有问题。本次从机自动重启，而其他从机仍然与主服务器在正常同步，于是判定为从库relay-log自身出错。

如果relay-log(binlog)文件有错误，通过mysqlbinlog <relay log路径> > /dev/null测试，也会打出上面一样的错误。

 

解决方法

一、从机清除掉出错位置之后的relay-log，在SQL客户端执行：

> STOP SLAVE;
> RESET SLAVE;

二、从出错位置重新同步，本次出错位置为：'mysql-bin.000239' position 13999540