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 '[email protected]: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