MySQL主从不一致问题相关处理_mysql主从复制数据不一致怎么办

一、背景

    某业务采取mysql的主从架构，但因为存储的问题，导致备库一直无法存储，数据同步一致性问题一直也未恢复，某次安全检查要求完成主备倒换演练，必须限期恢复主备，但是在恢复过程中，同步显示正常一段时间后，便会出现sql线程异常，主备数据不一致导致的同步错误情况。

相关链接：错误代码说明、Error Message Elements、Error Information Interfaces、mysql日志配置

二、可能原因

1、网络的延迟
由于mysql主从复制是基于binlog的一种异步复制通过网络传送binlog文件，理所当然网络延迟是主从不同步的绝大多数的原因，特别是跨机房的数据同步出现这种几率非常的大，所以做读写分离，注意从业务层进行前期设计。

2、主从两台机器的负载不一致
由于mysql主从复制是主数据库上面启动1个io线程，而从上面启动1个sql线程和1个io线程，当中任何一台机器的负载很高，忙不过来，导致其中的任何一个线程出现资源不足，都将出现主从不一致的情况。

3、max_allowed_packet设置不一致
主数据库上面设置的max_allowed_packet比从数据库大，当一个大的sql语句，能在主数据库上面执行完毕，从数据库上面设置过小，无法执行，导致的主从不一致。

4、自增键不一致
key自增键开始的键值跟自增步长设置不一致引起的主从不一致。

5、同步参数设置问题
mysql异常宕机情况下，如果未设置sync_binlog=1或者innodb_flush_log_at_trx_commit=1很有可能出现binlog或者relaylog文件出现损坏，导致主从不一致。

6、自身bug
mysql本身的bug引起的主从不同步，一般不会

7、版本不一致
特别是高版本是主，低版本为从的情况下，主数据库上面支持的功能，从数据库上面不支持该功能的情况。

注意：sql_thread是根据主键匹配行记录，不会校验行数据；有没有主键的情况下，sql_thread是根据全表扫描匹配行记录，所以master的更新当在slave中执行时sql找不到需要更新的行，就会报1032错误。

Last_SQL_Error: Could not execute Update_rows event on table rsms.t_sys_file; Can’t find record in ‘t_sys_file’, Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event’s master log mysql-bin.004186, end_log_pos 269313742

1）MySQL主从同步的1032错误，一般是指在从节点侧要更改(update、delete)的数据不存在，SQL_THREAD提取的日志无法应用故报错，造成同步失败;（Update、Delete、Insert一条已经delete的数据）。1032的错误本身对数据一致性没什么影响，影响最大的是造成了同步失败、同步停止。如果主主（主从）有同步失败，要第一时间查看并着手解决。因为不同步，会造成读取数据的不一致。应在第一时间恢复同步

更改my.cnf文件，在Replication settings下添加：

slave-skip-errors＝1032; 完成后重启数据库，然后start salve；它是只读参数，不能动态修改；这个参数针对gtid和传统复制有效，并且俩者的结果都一样。报错的SQL语句会跳过，但是其余的SQL还是正常执行。设置slave_skip_errors=1062或者1032在binlog_format是ROW的情况下，整个事务只会跳过报1062或者1032错误的sql，不执行这条SQL其余的sql正常进行。

2）ERROR 1062 主键冲突的错误，无论binlog_format是ROW格式还是STATEMENT格式，从库发生主键冲突的行的值都会被主库同步过来的数据给覆盖掉，即认为在从库执行replace操作。对于1062一般跳过会造成数据更严重的不一致情况，因分析及时纠正。

针对1032行找不到的错误，无论binlog_format是ROW格式还是STATEMENT格式，从库本地都会忽略这条SQL语句，不执行，只是执行事务的其他没有错误的SQL。

在binlog_format为ROW格式的情况下，在出现1032或者1062的情况下，并且table存在自增健为主键，并且在master上面执行insert操作的时候没有指定主键，这个时候需要注意主键的键值信息，很有可能在出现1032或者1062错误跳过之后，master和slave俩者的主键下一个键值可能还不一致。所以需要注意的操作有delete,insert,truncate。1）在binlog_format的格式是ROW格式的情况下面（把改变的内容复制过去，而不是把命令在从服务器上执行一遍.），无论是1032还是1062情况下设置sql_slave_skip_counter=1,它会将整个事务跳过去。2）在binlog_format的格式是statement(语句)的情况下,sql_slave_skip_counter=1并且是1062的错误，它会将整个事务跳过去。而针对1032错误，在整个事务当中出现修改一个主库存在，但是从库不存在的row的数据的时候，在从库是不会报错的，且该事务的其他sql语句是可以成功执行的。

附1：主从计算延迟的伪代码

//The pseudo code to compute Seconds_Behind_Master:

if(SQL thread is running)

//如果SQL线程启动了

{
   

	if(SQL thread processed all the available relay log)

		//如果SQL线程已经应用完了所有的IO线程写入的Event

	{
   

		if(IO thread is running)

		//如果IO线程启动了

			print0;		//设置延迟为 0

		else

			printNULL;		//否则为空值

	}

  else
	compute Seconds_Behind_Master;
	//如果SQL线程没有应用完所有的IO线程写入的Event，那么需要计算延迟。

  }

else

	printNULL;	//如果连SQL线程也没有启动则设置为空值

*/
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40

计算延迟的公式为：服务器当前时间-Event header中的timestamp - 主从服务器时间差

long time_diff= ((long)(time( 0)-last_master_timestamp)-clock_diff_with_master);

如果SQL线程没有应用完了所有的IO线程写入的Event，也就是Read_Master_Log_Pos和Exec_Master_Log_Pos存在一定的差值。判定标准为:

(get_master_log_pos -get_group_master_log_pos) &&(get_master_log_name-get_group_master_log_name))

也就是通过 IO线程读取到主库binary log的位置(Read_Master_Log_Pos) 和 SQL线程应用到的主库binary log位置进行比较来进行判断，只要他们出现差值就会进入延迟计算环节。也就是：服务器当前时间-Event header中的timestamp - 主从服务器时间差 这个公式必然出现了偏差。如果主库的压力越大出现这种情况的可能性就会越大，因为IO线程和SQL线程在处理Read_Master_Log_Pos和Exec_Master_Log_Pos的出现时间差的可能性就会越大。

三、处理

3.1、手动执行同步+忽略错误(在业务不保证数据强一致性的情况下，可以选择忽略)

1）先进入主库，进行锁表，防止数据写入
mysql> flush tables with read lock;
mysql> show master status
2）数据导出备份然后倒入从库
mysqldump -uroot -p --lock-all-tables --flush-logs db_name > /data/master.sql
3）登录从库停止slave从节点
stop slave;
4）倒入数据
mysql -u root -p db_name < /temp/master.sql
或mysql> source /temp/master.sql
5）配置重新主从同步

#5.7及之后版本
mysql> update mysql.user set authentication_string = password (‘Password4’) where user = ‘testuser’ and host = ‘%’;
Query OK, 1 row affected, 1 warning (0.06 sec)
Rows matched: 1 Changed: 1 Warnings: 1

mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)
#5.6及之前版本
update mysql.user set password=password(‘新密码’) where user=‘用户名’ and host=‘host’;

mysql> change master to master_host=‘172.18.1.20’, master_port=3306, master_user=‘repl’,master_password=‘123456’, master_log_file=‘mysql-bin.000031’,master_log_pos=932;

6）开启slave

start slave;

7）查看slave状态
show slave status\G //正常输出如下
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
……
Seconds_Behind_Master:0
8）如果一段时间后，出现了报错，停止slave：
set global sql_slave_skip_counter =1; #这个参数只是针对传统复制有效，针对GTID复制只能使用gtid_next.
9）start slave再次验证，重复几次，把所有错误忽略掉；

3.2、手动执行同步+手动更正

前7步同上；通过第7步的报错位置，在主库执行：

mysqlbinlog -v --stop-position=xxx ./data/master-bin.xxx > ./binlog.update
cat ./binlog.update |awk ‘/end_log_pos xxx/ {print NR}’
根据上面的NR行数，查看附近的行，定位数据不一致的地方，后续手动补全
cat ./binlog.update |awk ‘NR==xxx-50,NR==xxx+50’|grep -i update -A 200|grep xxxx -B 200|less
找到数据位置，@1表第一个字段值；其中@1 @2 @3…分别对应表的列名
或：
比如，报错位置 end_log_pos 440267874。可利用mysqlbinlog工具找出440267874的事件
/usr/local/mysql-5.6.30/bin/mysqlbinlog --base64-output=decode-rows -vv mysql-bin.000003 |grep -A 20 ‘440267874’
或者/usr/local/mysql-5.6.30/bin/mysqlbinlog --base64-output=decode-rows -vv mysql-bin.000003 --stop-position=440267874 | tail -20
或者usr/local/mysql-5.6.30/bin/mysqlbinlog --base64-output=decode-rows -vv mysql-bin.000003 > decode.log
主库创建临时表：
Create table xxl_job_temp like xxl_job_log_report;
将该段数据写入临时表导出导入到备库；
start slave;
show slave status\G

结果说明：

Slave_IO_Running： 该参数可作为io_thread的监控项，Yes表示io_thread的和主库连接正常并能实施复制工作，No则说明与主库通讯异常，多数情况是由主从间网络引起的问题；

Slave_SQL_Running： 该参数代表sql_thread是否正常，具体就是语句是否执行通过，常会遇到主键重复或是某个表不存在。

Seconds_Behind_Master：是通过比较sql_thread执行的event的timestamp和io_thread复制好的event的timestamp(简写为ts)进行比较，而得到的这么一个差值；NULL—表示io_thread或是sql_thread有任何一个发生故障，也就是该线程的Running状态是No，而非Yes。0 — 该值为零，是我们极为渴望看到的情况，表示主从复制良好，可以认为lag不存在。正值 — 表示主从已经出现延时，数字越大表示从库落后主库越多。负值 — 几乎很少见，我只是听一些资深的DBA说见过，其实，这是一个BUG值，该参数是不支持负值的，也就是不应该出现。

其他：增大从库innodb_buffer_pool_size，让更多操作在Mysgl内存中完成，减少磁盘操作，减少延迟；

3.3、对于数据量不大的小型数据库执行重做从库即可

#主库只读
mysql>FLUSH TABLES WITH READ LOCK;
#从库重置
mysql> show variables like "%server_id";  #验证主从
mysql> stop slave;
mysql> reset slave;  #或reset slave all;
mysql> drop database 'dbname';  #删除不需要的数据库
mysql> source 主库备份;  #或者
mysql -uroot -h slave_ip-P 3306 -p < /opt/master-dump.sql
#重新配置主从
mysql> change master to master_host='172.18.1.20', master_port=3306, master_user='repl',master_password='123456', master_log_file='mysql-bin.000031',master_log_pos=932;
# 启动slave
mysql> start slave;
mysql> show slave status\G；

#主库解锁
unlock tables；
#验证主从
insert into 表名(列A，列B...) values (值A，值B...);

#其他
show variables like'general%';  #通用查询日志是否开启，记录数据库的操作，一般非必要关闭，多用于审计
expire_logs_days=180  #控制mysql日志保存天数，主要针对二进制日志
#对于其他日志修改名称后手动刷新生成新日志，对就日志压缩即可
mv mysql.log mysql.log.old
mv mysql-slow.log mysql-slow.log.old
mv err.log err.log.old
mysqladmin flush-logs
mysqladmin flush-logs general  #需要先改名，否则不会生产新的，只会重新打开旧的
grep -v "^--" /mysql.sql | grep -v "^/" | grep -V "^$"  #查看备份文件

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31

四、第三方工具pt-table-sync来辅助实现

    Percona Toolkit是mysql运维的一组命令的集合， 是 Percona 支持人员用来执行各种 MySQL、MongoDB 和系统任务的高级命令行工具集，它们是完全独立的，不依赖与特定的库，因此安装也很简单；该工具中最主要的三个组件分别是：

注：percona-toolkit只能应用主从复制场景。

官网：https://docs.percona.com/percona-toolkit/index.html
文档：https://docs.percona.com/percona-toolkit/installation.html
下载：https://www.percona.com/downloads/percona-toolkit/LATEST/

1）安装前准备

#percona-toolkit的yum仓库
yum install https://repo.percona.com/yum/percona-release-latest.noarch.rpm -y
#MYSQL的yum仓库
yum install -y  https://repo.mysql.com//mysql80-community-release-el7-3.noarch.rpm
yum install percona-release-0.1-6.noarch.rpm
yum list | grep mysql | grep libs-compat
mysql-community-libs-compat.i686         5.7.30-1.el7                  mysql57-community
mysql-community-libs-compat.x86_64       5.7.30-1.el7                  mysql57-community

yum -y install mysql-community-libs-compat.x86_64
#安装依赖
yum install perl perl-DBI perl-DBD-MySQL perl-IO-Socket-SSL perl-Time-HiRes perl-Digest-MD5 perl-ExtUtils-MakeMaker -y 
#下载
wget https://www.percona.com/redir/downloads/percona-release/redhat/0.1-6/percona-release-0.1-6.noarch.rpm

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

2）安装

sudo yum install percona-toolkit  //直接
yum list | grep percona-toolkit
#离线编译安装，下载工具集
#wget https://www.percona.com/downloads/percona-toolkit/2.2.18/tarball/percona-toolkit-2.2.18.tar.gz
#https://downloads.percona.com/downloads/percona-toolkit/2.2.1/deb/percona-toolkit_2.2.1-2.tar.gz
https://downloads.percona.com/downloads/percona-toolkit/3.5.4/binary/tarball/percona-toolkit-3.5.4_x86_64.tar.gz
#https://downloads.percona.com/downloads/percona-toolkit/3.2.0/binary/tarball/percona-toolkit-3.2.0_x86_64.tar.gz
#https://downloads.percona.com/downloads/percona-toolkit/3.3.0/binary/tarball/percona-toolkit-3.3.0_x86_64.tar.gz
#https://downloads.percona.com/downloads/percona-toolkit/3.4.0/binary/tarball/percona-toolkit-3.4.0_x86_64.tar.gz
#解压缩
tar -xzf percona-toolkit-3.5.4_x86_64.tar.gz
#进入目录
mv percona-toolkit-3.5.4 ptk-3.5.4
cd ptk-3.5.4/

bin        CONTRIBUTE.md    COPYING             docs        Gopkg.toml  lib          MANIFEST   run-tests.sh
Changelog  CONTRIBUTING.md  docker-compose.yml  Gopkg.lock  INSTALL     Makefile.PL  README.md  runtests.s
#执行perl脚本,生成Makefile
perl Makefile.PL 

Checking if your kit is complete...
Looks good
Writing Makefile for percona-toolkit

#yum配置
[base]
name=CentOS-$releasever - Base
baseurl=http://mirrors.huaweicloud.com/centos/$releasever/os/$basearch/
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-CentOS-7

#released updates 
[updates]
name=CentOS-$releasever - Updates
baseurl=http://mirrors.huaweicloud.com/centos/$releasever/updates/$basearch/
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-CentOS-7

#additional packages that may be useful
[extras]
name=CentOS-$releasever - Extras
baseurl=http://mirrors.huaweicloud.com/centos/$releasever/extras/$basearch/
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-CentOS-7

[centosplus]
name=CentOS-$releasever - Plus
baseurl=http://mirrors.huaweicloud.com/centos/$releasever/centosplus/$basearch/
gpgcheck=1
enabled=0
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-CentOS-7

#如报错：
Can't locate ExtUtils/MakeMake
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53