记一次MySQL Slave库恢复实战记录
状况描述:
今天登录一个MySQL数据库slave节点主机发现/var/lib/mysql下存放大量的mysql-relay-bin文件,最早的文件创建日期甚至是2018年,我记得在slave库同步完master的日志操作记录后,会删除这些文件(默认设置不会删除,我记错了),于是便查看了slave库的状态,发现如下报错:
mysql> show slave status\G;*************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: *.*.*.* Master_User: dbsync Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000095 Read_Master_Log_Pos: 869242147 Relay_Log_File: mysqld-relay-bin.000146 Relay_Log_Pos: 871280529 Relay_Master_Log_File: mysql-bin.000075 Slave_IO_Running: Yes Slave_SQL_Running: No Replicate_Do_DB: cdb,cdb_admin Replicate_Ignore_DB: mysql Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: 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. Skip_Counter: 0 Exec_Master_Log_Pos: 871280384 Relay_Log_Space: 19994786573 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: NULLMaster_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 1594 Last_SQL_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.1 row in set (0.00 sec)ERROR: No query specified
我在master节点上删除了名称为mysql-bin.00007格式的文件,其中包括mysql-bin.000075,因此,slave库找不到该文件,无法同步。
解决办法:
1、在slave库上重新指定同步位置。(不可行)
slave stop;CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000095',MASTER_LOG_POS=869242147; //mysql master节点上mysql-bin.000095的已有位置slave start;
在多次尝试指定不同的同步位置(报错的位置,master上mysql-bin-000095刚写过的位置)依然存在该错误。
实际上,表记录已经有问题,就拿描述中提出的那个表来说,slave库存放了约1200条记录,master库则有1900+的记录。除非手工将这些数据补上,否则由于记录操作数据的日志已经丢失(被我删除),是找不到最近的一致的日志操作执行位置的。
2、重做slave库。
由于数据差异太大,而且我觉得不光一张表出现了数据不一样的问题,所以干净点,把从库重做。
1)比对master、slave节点库配置信息,保证一致。(我不知道为什么设置了双主模式,实际上我只有一个实例跑在master节点上啊?)
2)在master、slave节点上查看流量情况(show processlist),保证要重做的slave库上没有业务的流量接入。
3)停止master节点上slave进程。(这个停了以后,我就没开过,不知道有没有问题,待观察)
4)记录master节点上库的日志记录位置,之后备份数据库:
mysql> show master status;+------------------+-----------+-------------------------------+------------------+| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |+------------------+-----------+-------------------------------+------------------+| mysql-bin.000095 | 871760173 | cdb,cdb_admin | mysql |+------------------+-----------+-------------------------------+------------------+1 row in set (0.01 sec) mysqldump -u root -p --databases cdb,cdb_admin > bak.master.sql
mysqldump -u root -p --databases cdb,cdb_admin > bak.slave.sql
mysql -u root -p < bak.master.sql
slave stop;CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000095',MASTER_LOG_POS=871760173; //POS为刚才记录的master节点日志记录位置slave start;
总结:
清理文件时,要注意mysql-bin文件在master、slave节点日志读取和写的位置啊!,删之前一定要确认日志位置在master和slave断已被读过,不要乱删,否则搞得slave库无法同步了,就算在slave节点上强行指定master日志读取位置或者跳过该错误,也不排除slave库上数据丢失的可能。
更多相关文章
- MySQL系列多表连接查询92及99语法示例详解教程
- Linux下MYSQL 5.7 找回root密码的问题(亲测可用)
- MySQL 什么时候使用INNER JOIN 或 LEFT JOIN
- android从服务器下载文件(php+apache+win7+MySql)
- 【有图】android通过jdbc连接mysql(附文件)
- android 通过php 连接 mysql
- android通过php连接mysql数据库!!!!
- 关于Android连接远程数据库(mysql、oracle)
- 图书馆座位管理系统(android,java后台,mysql)