MySQL read_log_event(): 'Found invalid event in binary log'，invalidbinary

MySQL read_log_event(): 'Found invalid event in binary log'，invalidbinary

    MySQL以簡單易用著稱，在同一個伺服器上可以安裝N個不同的版本，方便測試，遷移等等。此外，對於大多數Linux系統，整合了mysql，預設會被安裝。因此多版本的問題導致一些莫名的錯誤也是時有發生。最近在提取binlog日誌時，碰到了一個read_log_event，Found invalid event in binary log。經查發現原來是mysqlbinlog版本問題引發的。如下文，供大家參考。

 

1、故障描述
[root@HKBO bak]# mysqlbinlog --start-datetime="2014-12-15 00:30:00" mysql-bin.000399bk |more
ERROR: Error in Log_event::read_log_event(): 'Found invalid event in binary log', data_len: 376, event_type: 19
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#141213 21:05:01 server id 11  end_log_pos 120  Start: binlog v 4, server v 5.6.12-log created 141213 21:05:01
# Warning: this binlog is either in use or was not closed properly.
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

 

2、故障分析與解決
#查看當前是哪一個mysqlbinlog檔案
[root@HKBO bak]# which mysqlbinlog
/usr/bin/mysqlbinlog

#查看mysqlbinlog的版本，此時為3.2
[root@HKBO bak]# mysqlbinlog --version
mysqlbinlog Ver 3.2 for redhat-linux-gnu at x86_64
#Author : Leshami
#Blog   : http://blog.csdn.net/leshami

[root@HKBO bak]# whereis mysqlbinlog
mysqlbinlog: /usr/bin/mysqlbinlog /usr/share/man/man1/mysqlbinlog.1.gz

#此伺服器的安裝路徑其實質如為/app/soft/mysql/，查看mysqlbinlog的版本為3.4版
[root@HKBO bak]# /app/soft/mysql/bin/mysqlbinlog --version
/app/soft/mysql/bin/mysqlbinlog Ver 3.4 for Linux at x86_64

#下面使用正確路徑的mysqlbinlog，此時成功
[root@HKBO bak]# /app/soft/mysql/bin/mysqlbinlog --start-datetime="2014-12-15 00:30:00" mysql-bin.000399bk |more
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#141213 21:05:01 server id 11  end_log_pos 120 CRC32 0xaafb2293         Start: binlog v 4, server v 5.6.12-log created 141213 21:05:01
# Warning: this binlog is either in use or was not closed properly.
BINLOG '
fTmMVA8LAAAAdAAAAHgAAAABAAQANS42LjEyLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXAAEGggAAAAICAgCAAAACgoKGRkAAZMi
+6o=
'/*!*/;
# at 594054457

#查看當前系統mysql的版本
# /app/soft/mysql/bin/mysql --version
/app/soft/mysql/bin/mysql  Ver 14.14 Distrib 5.6.12, for Linux (x86_64) using  EditLine wrapper

#以下的為OS安裝時預設安裝的版本
# rpm -qa |grep -i mysql
mysql-5.0.95-3.el5

 

3、小結
a、對於生產環境mysql的部署，建議在安裝OS前不安裝預設的mysql或者安裝後卸載預設的mysql
b、因需要存在多版本的情形，應考慮使用直接路徑方式來執行相應的mysql命令列工具