MySQL同步复制报错处理总结

最新推荐文章于 2023-04-29 14:42:03 发布

Cosmos_Zhang

最新推荐文章于 2023-04-29 14:42:03 发布

阅读量2.2k

点赞数 4

分类专栏： mysql 文章标签： MySQL主从复制

本文链接：https://blog.csdn.net/Z13580480392/article/details/90116740

版权

mysql 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

一. 常见的3种故障

A.在master上删除一条记录时出现故障现象在master上删除一条记录后,slave上因找不到该记录而报错，报错信息如下：
Could not execute Delete_rows events on table <database.table_name>; Can't find record in <table_name>, Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log mysql-bin.000002, end_log_pos 84718
出现这种情况是因为主机上已经将其删除了，对此，可采取从机直接跳过的方式解决,命令如下：
分为GTID和binlog两种

A1. GTID复制的跳过:
1，停止slave进程
STOP SLAVE;
2，设置事务号，事务号从Retrieved_Gtid_Set获取
SET @@SESSION.GTID_NEXT= '1ffbb886-e3a6-11e2-89fd-18a905565190:7'
3，设置空事务
BEGIN; COMMIT;
4，恢复事务号
SET SESSION GTID_NEXT = AUTOMATIC;
5，启动slave进程
START SLAVE;

A2. binlog复制的跳过:

stop slave;
set global sql_salve_skip_count=1;start slave;

针对这种情况，个人写了一个skip_deleterows_replication.sh来处理该种情况，脚本具体如下（我以前是直接放在cron里面用定时任务去检测）:

#!/bin/bash
#######################################################################
##
##此脚本是参考maakit脚本编写的，默认跳过10次
##
##只有Could not execute Delete_rows events on table <database.table_name>; Can't find record in <table_name>, Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log mysql-bin.000002, end_log_pos 84718
##这种情况才会跳过，其他情况，需要自行处理，一面丢失数据
##
## author zxb
######################################################################
export LANG=zh_CN
v_dir=/usr/bin/
v_user=root
v_passwd=binbin
v_log=/tmp/logs
v_times=10

##创建和初始化各类日志目录
if [ -d "${v_log}" ]; then
   echo "${v_log} has existed."
else
   mkdir ${v_log}
fi

echo "">${v_log}/slave_status.log
echo "">${v_log}/slave_status_error.log
count=1
while true
do
   Seconds_Behind_Master=$(${v_dir}mysql -u${v_user} -p${v_passwd} -e "show slave status\G;"|grep Seconds_Behind_Master|awk -F: '{print $2}')

if[ ${Seconds_Behind_Master} !="NULL" ];then
   echo "slave is ok!"
   ${v_dir}mysql -u${v_user} -p${v_passwd} -e "show slave status\G;">>${v_log}/slave_status.log
   break
else
   echo "">>${v_log}/slave_status_error.log
   date>>${v_log}/slave_status_error.log
   ${v_dir}mysql -u${v_user} -p${v_passwd} -e "show slave status\G;">>${v_log}/slave_status_error.log
   ${v_dir}mysql -u${v_user} -p${v_passwd} -e "show slave status\G;"|egrep 'Delete_rows'>/dev/null 2>&1
   if[ $?=0 ]; then
       ${v_dir}mysql -u${v_user} -p${v_passwd} -e "stop slave;set global sql_salve_skip_count=1;start slave;"
   else
       ${v_dir}mysql -u${v_user} -p${v_passwd} -e "start slave;"
       Last_SQL_Error=${v_dir}mysql -u${v_user} -p${v_passwd} -e "show slave status\G;"|grep -v 'Last_SQL_Error_Timestamp'|awk -F: '/Last_SQL_Error/{print $2}'
       if [ ${Last_SQL_Error} !="NULL" ];then
           echo "replication is error and must manual handling;"||mail -s "replication alert" zhangxianbin@onething.net -f zhangxianbin330.pingan.com.cn
       else
           echo "replication is ok"
       break
   fi
   fi
   let count++
   if [ $count -gt ${v_times} ];then
       break
   else
       ${v_dir}mysql -u${v_user} -p${v_passwd} -e "show slave status\G;">>${v_log}/slave_status_error.log
       sleep 2
       continue
   fi
fi

B. 主键冲突
主从数据不一致时,slave上已经有该条记录，但我们又在master插入了同一条记录，此时就会报错，报错信息如下:

Could not execute Write_rows event on table zxb.test; Duplicate entry '29889' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log mysql-bin.000008, end_log_pos 617275634
解决方法:先查看slave的表结构 desc bbs.zxb;具体如下所示:
MySQL>desc zxb.test;
+-------+---------+------+------+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+------+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | char(5) | YES | | NULL | |
+-------+---------+------+------+---------+-------+

查看该表字段信息，得到主键的字段名。接着删除重复的主键，然后start slave;就ok了

C. 在master 更新一条记录，而slave上却找不到
解决方法:在master上，用mysqlbinlog分析一下出错的binlog日志在干什么,如果主库是更新一条数据但是数据没有找到，这种情况可能是导数或者其他操作将binlog关闭了。正常我们的做法是：
C1. 在主库找到相应的记录
C2. 跳过这个操作。set global sql_salve_skip_count=1;
C3.重启slave ;start slave;

二. slave的中继日志relay-log 损坏

当slave意外宕机时，有可能会损坏中继日志relay-log,再次开启同步复制时，报错信息如下：

Last_SQL_Error: Error initializing relay log position: I/O error reading the header from the binary log
Last_SQL_Error: Error initializing relay log position: Binlog has bad magic number;
It's not a binary log file that can be used by this version of MySQL.

处理方法(两种方法都适用)：
1.在my.cnf 加上relay_log_recovery=1
2.重新配置主从同步：

stop slave;
change master to master_log_file='' master_log_pos='';--binlog 模式的
CHANGE MASTER TO MASTER_HOST='xxx', MASTER_USER='xxx',Master_Port=xxx, MASTER_PASSWORD='xxx', MASTER_AUTO_POSITION=1; --gtid模式的
start slave;

三.主库myisam，出现数据不支持事务

报错如下：
Last_SQL_Error: Could not execute Write_rows event on table ggcond.der_report_num; Duplicate entry '119728' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log mysql-bin.008062, end_log_pos 42041

解决方法:改造相应的表(将myisam表改造为innodb)，直接忽略相应的表操作(暂时处理方法)
replicate-ignore-table=ggcond.der_report_num

四.主从复制变慢，暂时提高复制的方法(两个参数的含义)

set global innodb_flush_log_at_trx_commit=0;
set global sync_binlog=0;

a、innodb_flush_log_at_trx_commit：是 InnoDB 引擎特有的，ib_logfile的刷新方式（ ib_logfile：记录的是redo log和undo log的信息）
取值:0/1/2
innodb_flush_log_at_trx_commit=0，表示每隔一秒把log buffer刷到文件系统中(os buffer)去，并且调用文件系统的“flush”操作将缓存刷新到磁盘上去。也就是说一秒之前的日志都保存在日志缓冲区，也就是内存上，如果机器宕掉，可能丢失1秒的事务数据。
innodb_flush_log_at_trx_commit=1，表示在每次事务提交的时候，都把log buffer刷到文件系统中(os buffer)去，并且调用文件系统的“flush”操作将缓存刷新到磁盘上去。这样的话，数据库对IO的要求就非常高了，如果底层的硬件提供的IOPS比较差，那么MySQL数据库的并发很快就会由于硬件IO的问题而无法提升。
innodb_flush_log_at_trx_commit=2，表示在每次事务提交的时候会把log buffer刷到文件系统中去，但并不会立即刷写到磁盘。如果只是MySQL数据库挂掉了，由于文件系统没有问题，那么对应的事务数据并没有丢失。只有在数据库所在的主机操作系统损坏或者突然掉电的情况下，数据库的事务数据可能丢失1秒之类的事务数据。这样的好处，减少了事务数据丢失的概率，而对底层硬件的IO要求也没有那么高(log buffer写到文件系统中，一般只是从log buffer的内存转移的文件系统的内存缓存中，对底层IO没有压力)。

b、sync_binlog
sync_binlog：是MySQL 的二进制日志（binary log）同步到磁盘的频率。
取值：0-N
sync_binlog=0，当事务提交之后，MySQL不做fsync之类的磁盘同步指令刷新binlog_cache中的信息到磁盘，而让Filesystem自行决定什么时候来做同步，或者cache满了之后才同步到磁盘。这个是性能最好的。
sync_binlog=1，当每进行1次事务提交之后，MySQL将进行一次fsync之类的磁盘同步指令来将binlog_cache中的数据强制写入磁盘。
sync_binlog=n，当每进行n次事务提交之后，MySQL将进行一次fsync之类的磁盘同步指令来将binlog_cache中的数据强制写入磁盘。

五.myisam出现问题，需要修复

CHECK TABLE table_name;
SET SESSION myisam_sort_buffer_size = 256*1024*1024;
SET SESSION read_buffer_size = 64*1024*1024;
SET GLOBAL myisam_max_sort_file_size = 10*1024*1024*1024;
SET GLOBAL repair_cache.key_buffer_size = 128*1024*1024;
CACHE INDEX tbl_name IN repair_cache;
LOAD INDEX INTO CACHE tbl_name;
REPAIR TABLE tbl_name ;
SET GLOBAL repair_cache.key_buffer_size = 0;

六.避免在master上执行大事务

这个案例以前经常出现，就是一张很大的表（大约100G）,因业务需求需要删除数据或者说这个表都需要删除，然后开发竟然用delete，然后主从就悲剧了
这种问题有两种情况，如果删除整个表的数据请用truncate,如果删除部分数据类似于delete from bigtable where id=v_id;
碰到这种问题，那让开发采用批量提交,平均每2000条数据删除一次吧，具体代码实现如下：

delimiter $$
use bigtable$$
drop procedure if exists big_table_delete_2k$$
create procedure big_table_delete_5k(in v_id int)
BEGIN
del_2k:loop:
delete from bigtable where id=v_id limit 2000;
select row_count() into @count;
if @count = 0 then
   select concat('bigtable id=',v_id,'is',@count,'rows.') as bigtable_delete_finish;
   leave del_2k;
   end if;
   select sleep(1);
   end loop del_2k;
   end$$
   delimiter;

如果以后出现上面问题，请用相关存储过程，谢谢

七. 通过sql_exec_mode参数自动处理同步复制错误

sql_exec_mode正常的值为strict,但是slave_exec_mode='IDEMPOTENT',如果以前有接触的同事应该知道。
先说说这个参数使用的场景，就是第一个中的都可以强制跳过，不适用的场景有以下几个：
1.使用冥等模式时表要有主键
2.不能对DDL操作冥等,对字段长度不同导致的错误也不是冥等(譬如主机一个字段是char(20)而备机是char(10))

同样设置完冥等模式之后要重启slave,具体代码实现如下:
##############################################
##
##skip_slave_error.sh
##
##author zxb
##
##############################################

#!/bin/bash
v_user=root
v_passwd=binbin
port=3306
v_dir=/usr/bin/
v_log=/tmp/logs
##创建和初始化各类日志目录
if [ -d "${v_log}" ]; then
   echo "${v_log} has existed."
else
   mkdir ${v_log}
fi
for hostip in 'cat slaveip.txt'
do
   result=$(${v_dir}mysql -u${v_user} -p${v_passwd} -h${hostip} -P${port} -e "show slave status\G;"|awk -F: '/slave_SQL_Running/{print $2}')
   if [ "$result" != "Yes" ];then
       ${v_dir}mysql -u${v_user} -p${v_passwd} -h${hostip} -P${port} -e "set global slave_exec_mode=IDEMPOTENT;"
       ${v_dir}mysql -u${v_user} -p${v_passwd} -h${hostip} -P${port} -e "stop slave;start slave;"

       echo "replication error and skip">>${v_log}/skip.log | mail -s "replication alert " zhangxianbin330@pingan.com.cn
   fi
done

八、binlog_ignore_db引起的同步复制故障

这个问题就是以前遇到过，场景是一个同事在库执行mysql -e "create table db.test like db.test1"但是从库一直没有写入bin_log,查了下，发现有binlog_ignore_db这个参数，解决方法用replicate-ignore-db=db取代相应参数
具体参数解析，自己查查。