个人博客原文地址:http://www.ltang.me/2017/05/15/mysql-slave-rebuilding/
Mysql从库恢复失败
推断是因为一句修改表结构的语句在从库执行失败(或者未同步?)导致mysql从库同步异常,使用命令show slave status;
可以看到
Slave_IO_Running: Yes
Slave_SQL_Running: No
然后通过Relay_Master_Log_File
和Exec_Master_Log_Pos
定位到具体的sql语句,发现是条更新语句,更新某个表的19个字段,然而从库该表只有18个字段。
- 找到历史更新记录,然后手动在从库添加该字段
- 暂停同步
stop slave;
- 跳过当前执行的同步sql
set global sql_slave_skip_counter =1;
- 继续同步
start slave;
查看同步状态,发现开始正常地执行历史sql了。然而,过了大约半个小时,我再次查看状态时,发现另一个sql又执行报错,重复几次后发现又有不同的sql执行报错,而且由于前面跳过了一些重要数据没有同步,导致后面大片失败,这时候只好选择重建从库。
重建过程
mysqldump -u*** -p*** -h db-master --default-character-set=utf8 --master-data=2 --single-transaction --databases DB1 DB2 DB3 > product_data_backup_20170515.sql
这里将需要同步的数据库DB1/DB2/DB3导出到文件。注意–master-data=2 –single-transaction的配合使用,前一个参数会在开始导出时锁全表,记录当前的binlog文件和位置,然后释放锁,然后在同一个事务中导出数据,以保证一致性。
从库
stop slave;
- 从库先drop需要同步的数据库,然后
source product_data_backup_20170515.sql
导入数据; grep 'CHANGE MASTER TO MASTER_LOG_FILE' product_data_backup_20170515.sql
拿到导出时的MASTER_LOG_FILE和MASTER_LOG_POS,例:-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000068', MASTER_LOG_POS=70371793;
CHANGE MASTER TO MASTER_HOST='10.*.*.*',MASTER_USER='slaveUser',MASTER_PASSWORD='***',MASTER_LOG_FILE='mysql-bin.000068', MASTER_LOG_POS=70371793;
start slave;
show slave status;
发现从库正在同步最新的数据,一会儿之后就已经同步到最新的POS,恢复正常
其他问题
- 不要修改从库的表结构以及数据,避免同步冲突失败;
- 不要滥用
set global sql_slave_skip_counter
,这会跳过某些同步sql,可能导致数据不一致; - 附件类数据尽量不要直接存储在数据库中,备份和恢复时会特别慢