一、MySQL主从常见故障——主库日志丢失
这种情况常发生于主库错误执行了reset master命令或者有reset master的需求,这样会导致binlog日志全部清空,从库会因为读取日志失败产生错误。要解决这个问题,通常就是找一个业务空闲期停服,然后从库进行reset操作重新做主从配置
#主库操作
mysql > reset master #清空binlog
#从库操作
mysql > stop slave #需等待从库把当前日志内容都读取完再做该操作
mysql > reset slave all #清空从库信息
mysql > change master ... #重做从库
二、MySQL主从常见故障——主从数据库不一致
这种情况常发生于从库没有配置super_read_only=1,然后管理员错误地在从库增删了数据,导致从库与主库数据不一致。要解决这类问题,通常需要在从库执行反向操作,比如删掉这些错误新增的数据,通过手动的方式让主从数据恢复到之前一致状态。
1、登陆MySQL执行show slave status命令查看从库状态,获取当前已应用到的binlog信息,主要关注Last_Error中所提示的信息
2、根据Last_Error中的报错信息获取具体出错的SQL
#方法1——通过binlog查询
show binlog events in 'mysql-bin.032102' from 730019106 limit 10; #找到对应行,该行中Info信息就是1973位置所做操作
#方法2——通过ps表查询
select * from performance_schema.replication_applier_status_by_worker where LAST_ERROR_NUMBER=1396
3、定位出错误语句后只需要在从库执行反向操作处理这些数据即可,然后重新启动slave进程
mysql > stop slave
mysql > start slave
三、MySQL主从常见故障——主键冲突
参考主从数据不一致的修复办法
四、MySQL主从常见故障——从库日志丢失
误删除了从库上的relay log,导致还有事务没应用,发生主从故障
1、在从库上查看当前日志信息,主要观察relay_master_log_file和exec_master_log_pos的值,这两项代表了从库已经处理的中继日志文件和位置所对应的binlog文件和位置
2、在从库上使用上一步看到的位置点重新同步
stop slave
change master ...
start slave
五、MySQL主从故障通用处理方法——在从库跳过错误事务
1、首先在从库上执行命令查看出错原因
show slave status \G
2、如果确定出错的事务可以忽略不处理,可以跳过该出错事务
# 跳过指定数量的事务,通常为1跳过当前出错事务
mysql > stop slave ;
mysql > set global sql_slave_skip_counter=1
mysql > start slave ;
# 跳过指定类型的错误或者所有错误
vi /etc/my.cnf
[mysqld]
slave-skip-errors=1062,1146,2341 #跳过指定错误类型
slave-skip-errors=all #跳过所有错误,不建议使用
# GTID模式下跳过事务
stop slave;
set gtid_next='fb6d07d2-a253-1212-b2fh-29255eg3f3g:23' #show slave status信息中retrieved_gtid_set里的gtid为
fb6d07d2-a253-1212-b2fh-29255eg3f3g:18-23
begin;commit; #手动指定gtid_next,如果gtid已经存在于实例的GTID集合中,该事务会被忽略;如果没有存在于GTID集合中就将这个gtid分配给接下来要执行的事务,系统不需要给这个事务生成新的GTID
set gtid_next='AUTOMATIC'; #修改回自动获取GTID
start slave;
# 使用gtid_purged跳过事务
show slave status \G; #先查看executed_gtid_set的值,该值有两行,看下面那行
show variables like '%gtid_purged%' #查看主库purged值,假设末尾是1-33
reset master #清空从库binlog和gtid_executed状态
set global gtid_purged='xxxxxxxxxxxxxxxxxxxxxxxxx:1-33'; 跳过1-33的事务
start slave; #主从状态恢复后需手动补跳过的事务所产生的数据
六、从库延迟
由于主从复制原理问题,主从延迟是不能100%避免的。如果必须实现“所有查询都不能是过期读”,比如一些金融类业务。只能放弃读写分离,将所有读写压力都放在主库才行。出现从库延迟后,观察从库事务是否在正常在执行,同时在主从节点排查网络传输是否正常,其它也不能做过多的干涉了。
七、MySQL主从故障通用处理方法——使用Percona工具自动处理
1、安装percona工具
yum install https://repo.percona.com/yum/percona-release-latest.noarch.rpm
yum install percona-toolkit
2、使用pt-slave-restart忽略错误
mysql > stop slave
pt-slave-restart -uroot -p123456 --error-numbers=1396 #忽略该报错
mysql > start slave