1. 故障现象
任务需要搭建一个一主一从的环境。本人比较菜鸡,这个任务就交给我了。从库系统centos6.8,系统自带了mysql。我熟练的配置完,ok,检查没问题。插入数据试试,发现从库报错了。内容如下:
mysql> show slave status \G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
...
Master_Log_File: mysql-bin.011889
Read_Master_Log_Pos: 1011
Relay_Log_File: mysql-relay-bin.000002
Relay_Log_Pos: 274
Relay_Master_Log_File: mysql-bin.011889
Slave_IO_Running: Yes
Slave_SQL_Running: No
...
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.
...
Seconds_Behind_Master: NULL
...
Master_Server_Id: 31
故障原因看上去是relay log读取失败。可能是主库二进制日志崩溃,可能是从库中继日志崩溃,或者是网络原因,也可能是代码原因。我觉得这个原因的范围和银河系那么大……
提取相关二进制日志,发现报错Sanity check failed:
# mysqlbinlog mysql-relay-bin.000002
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#200106 13:07:51 server id 11 end_log_pos 112 Start: binlog v 4, server v 5.1.67-ndb-7.1.26-cluster-gpl-log created 200106 13:07:51
BINLOG '
p8ASXg8LAAAAbAAAAHAAAAAAAAQANS4xLjY3LW5kYi03LjEuMjYtY2x1c3Rlci1ncGwtbG9nAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAWQAEGggAAAAICAgCAAAACgoK
'/*!*/;
# at 112
#700101 8:00:00 server id 31 end_log_pos 0 Rotate to mysql-bin.011890 pos: 1914
# at 155
#200106 10:27:56 server id 31 end_log_pos 0 Start: binlog v 4, server v 5.7.20-log created 200106 10:27:56
BINLOG '
LJsSXg8fAAAAdwAAAAAAAAAAAAQANS43LjIwLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
ABkFqsk=
'/*!*/;
ERROR: Error in Log_event::read_log_event(): 'Sanity check failed', data_len: 61, event_type: 34
ERROR: Could not read entry at offset 274: Error in log format or read error.
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
但是依然无法确认主从失败的原因。只能凭感觉搞了。
2. 故障原因
主库使用mysql5.7,从库使用系统自带的mysql5.1版本。版本不一致导致主从失败。重装从库mysql,故障修复。
3. 安装步骤
参考官方文档。确认已安装libaio库。
简洁步骤如下:
shell> groupadd mysql
shell> useradd -r -g mysql -s /bin/false mysql
shell> cd /usr/local
shell> tar zxvf /path/to/mysql-VERSION-OS.tar.gz
shell> ln -s full-path-to-mysql-VERSION-OS mysql
shell> cd mysql
shell> mkdir mysql-files
shell> chown mysql:mysql mysql-files
shell> chmod 750 mysql-files
shell> bin/mysqld --initialize --user=mysql
shell> bin/mysql_ssl_rsa_setup
shell> bin/mysqld_safe --user=mysql &
# Next command is optional
shell> cp support-files/mysql.server /etc/init.d/mysql.server
新的一年,要有新的目标!我的目标就是每天不划水,工作不摸鱼。
参考文档
https://dev.mysql.com/doc/refman/5.7/en/binary-installation.html