MySQL主从不一致的修复过程

昨天发现一个5.7的MySQL从库在应用日志的时候报出了错误。从库启用过了并行复制。Last Error的内容为:

Last_Error: Coordinator stopped because there were error(s) in the
worker(s). The most recent failure being: Worker 0 failed executing
transaction '8fc8d9ac-a62b-11e6-a3ee-a4badb1b4a00:7649' at master log
mysql-bin.000011, end_log_pos 5290535. See error log and/or
performance_schema.replication_applier_status_by_worker table for more
details about this failure or others, if any.

对于这类问题看起来还是比较陌生,如果想查看一些明细的信息,可以到binlog里面看到一些。此处的relay log是teststd-relay-bin.000013

/usr/local/mysql/bin/mysqlbinlog --no-defaults
--base64-output=DECODE-ROWS --verbose teststd-relay-bin.000013 >
/tmp/mysqlbin.log

而修复方式和常规的略有一些差别。

STOP SLAVE;
SET @@SESSION.GTID_NEXT = '8fc8d9ac-a62b-11e6-a3ee-a4badb1b4a00:7649';
BEGIN; COMMIT;
SET @@SESSION.GTID_NEXT = AUTOMATIC;
START SLAVE;

然后再次应用,不过我发现我这列碰到的问题貌似比想象的要麻烦一些。可以从错误日志看出是在更修改backend数据库的表sys_user_audit的时候抛出了错误。

2016-11-29T00:03:58.754386+08:00 161 [Note] Slave SQL thread for channel
'' initialized, starting replication in log 'mysql-bin.000011' at
position 5290028, relay log './teststd-relay-bin.000013' position: 27175
2016-11-29T00:03:58.754987+08:00 162 [ERROR] Slave SQL for channel '': Worker 0 failed executing transaction '8fc8d9ac-a62b-11e6-a3ee-a4badb1b4a00:7649' at master log mysql-bin.000011, end_log_pos 5290535; Could not execute Update_rows event on table backend.sys_user_audit; Can't find record in 'sys_user_audit', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log FIRST, end_log_pos 5290535, Error_code: 1032 

手工跳过了几次之后,发现这样也不是事儿,如果这样的问题较多,可以直接修改参数slave_exec_mode来完成。

set global slave_exec_mode=IDEMPOTENT;

当然这种方式解决当前问题还是比较合适的,跟上了主库的变更,重新设置为原值。

set global slave_exec_mode=STRICT;很快从库的状态就正常了,但是又一个新的问题又来了。主从数据库的数据怎么不一致了。而且更加直接的是我对这个表在主从做了对比,发现数据是不一致的,从库的数据比主库少了9条。如此一来,这个从库就是不合格的。

怎么修复数据呢,一种直接的方式就是重建从库,但是这样不是一个很好的方案。还有其它的方案吗,使用navicator也是一个不错的方案,图形界面点点配配就可以完成。还有一种方案是使用pt工具来修复。

早就耳闻,今天终于感受了一下。

首先安装很常规,可以参考我之前的一篇文章。Percona-toolkit的安装和配置(r8笔记第86天)其实就是下载解压,基本的安装。

在主从库各创建一个临时作为同步的用户,先做checksum,然后根据checksum的情况来修复数据,这样就涉及两个命令行工具,pt-table-checksum和 pt-table-sync,当然这两个工具的选项很多,我只做一些基本的操作。

创建用户的方式如下,需要做对比主从checksum的数据库为backend

GRANT SELECT, PROCESS, SUPER, REPLICATION SLAVE ON *.* TO 'pt_checksum'@'10.127.%.%' IDENTIFIED BY 'pt_checksum';

创建的临时数据库为percona,也需要赋予相应的权限。

grant all on percona.* to  'pt_checksum'@'10.127.%.%' ;

checksum的过程其实很复杂,大体有一下的步骤,当然我们可以简化一下,达到目标然后再深究。

在主库端开始做checksum,如果碰到下面的错误。

# pt-table-checksum
h='10.127.128.99',u='pt_checksum',p='pt_checksum',P=3306 -d backend
--nocheck-replication-filters --replicate=percona.checksums
Replica teststd.test.com has binlog_format ROW which could cause pt-table-checksum to break replication.  Please read "Replicas using row-based replication" in the LIMITATIONS section of the tool's documentation.  If you understand the risks, specify --no-check-binlog-format to disable this check.

这个选项的具体含义后续再琢磨,在row模式下会有这种警告,可以忽略这项检查。

[root@testdb2 bin]# pt-table-checksum
h='10.127.128.99',u='pt_checksum',p='pt_checksum',P=3306 -d backend
--nocheck-replication-filters --replicate=percona.checksums  
--no-check-binlog-format
            TS ERRORS  DIFFS     ROWS  CHUNKS SKIPPED    TIME TABLE
11-29T17:45:34      0      0      105       1       0   0.017 backend.sys_resource
11-29T17:45:34      0      0       17       1       0   0.015 backend.sys_role
11-29T17:45:34      0      1       99       1       0   0.017 backend.sys_user
11-29T17:45:34      0      1      172       1       0   0.017 backend.sys_user_audit

完成之后,在percona下会就生成一个表,里面的数据就是一些对比的元数据,如果存在差别则会有diffs字段会有标示

如果确认无误,可以开始修复数据,借助pt-table-sync,先把SQL输出不执行,把主库和从库的信息都正确输入。

pt-table-sync --print --replicate=percona.checksums h=10.127.128.99,u=pt_checksum,p=pt_checksum,P=3306 h=10.127.130.58,u=pt_checksum,p=pt_checksum,P=3306

而这个操作的原理其实就是replace into。

REPLACE INTO `backend`.`sys_user`(`id`, `user_name`, xxxx) VALUES
('100', 'songlijiao@test-inc.com', 'songlijiao', xxxxx)
/*percona-toolkit src_db:backend src_tbl:sys_user
src_dsn:P=3306,h=10.127.128.99,p=...,u=pt_checksum dst_db:backend
dst_tbl:sys_user dst_dsn:P=3306,h=teststd.test.com,p=...,u=pt_checksum
lock:1 transaction:1 changing_src:percona.checksums
replicate:percona.checksums bidirectional:0 pid:28684 user:root
host:testdb2.test.com*/;

切记要注意权限,对于这个同步数据的用户要开通操作目标数据库的权限。

grant insert,delete,update,select on backend.* to 'pt_checksum'@'10.127.%.%' ;

这个过程持续的时间不长,很快就能够执行完毕,修复之后再次做checksum就完全正常了。

时间: 2024-10-23 19:56:05

MySQL主从不一致的修复过程的相关文章

MySQL主从不一致发现的细小问题分析(r12笔记第63天)

   今天和同事一起看了一个问题,她在一个主从环境中发现了数据不一致,存在主键冲突.     show slave status的报错信息大概是下面的样子. Last_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 0 failed executing transaction '0e454161-3169-11e7-98f6

使用pt工具检测MySQL主从延迟(r12笔记第7天)

 今天翻看了下<高性能MySQL>,真是让人拍手称绝,里面的很多实战思路非常不错,各种问题分析如数家珍,如果是有一定基础的同学,看起来会非常不错.    当然里面提到的一个地方,感觉很有意思,那就是主从延迟的一个测算思路.书中他们是通过建立一张表,插入时间相关的数据,值得一提的是这个表的存储引擎是Federated,主要就是为了完成类似Oracle DB link一样的特殊需求,在备库端来对比这个时间差来得到一个相对精准的延迟值.    当然有的同学可能会说,我们有show slave sta

高性能Mysql主从架构的复制原理及配置详解(转)

温习<高性能MySQL>的复制篇. 1 复制概述       Mysql内建的复制功能是构建大型,高性能应用程序的基础.将Mysql的数据分布到多个系统上去,这种分布的机制,是通过将Mysql的某一台主机的数据复制到其它主机(slaves)上,并重新执行一遍来实现的.复制过程中一个服务器充当主服务器,而一个或多个其它服务器充当从服务器.主服务器将更新写入二进制日志文件,并维护文件的一个索引以跟踪日志循环.这些日志可以记录发送到从服务器的更新.当一个从服务器连接主服务器时,它通知主服务器从服务器

高性能Mysql主从架构的复制原理及配置详解

1 复制概述       Mysql内建的复制功能是构建大型,高性能应用程序的基础.将Mysql的数据分布到多个系统上去,这种分布的机制,是通过将Mysql的某一台主机的数据复制到其它主机(slaves)上,并重新执行一遍来实现的.复制过程中一个服务器充当主服务器,而一个或多个其它服务器充当从服务器.主服务器将更新写入二进制日志文件,并维护文件的一个索引以跟踪日志循环.这些日志可以记录发送到从服务器的更新.当一个从服务器连接主服务器时,它通知主服务器从服务器在日志中读取的最后一次成功更新的位置.

MHA实现mysql主从数据库手动切换的方法_Mysql

本文实例讲述了MHA实现mysql主从数据库手动切换的方法,分享给大家供大家参考.具体方法如下: 一.准备工作 1.分别在Master和Slave执行如下,方便mha检查复制: 复制代码 代码如下: grant all privileges on *.* to 'root'@'10.1.1.231' identified by 'rootpass'; grant all privileges on *.* to 'root'@'10.1.1.234' identified by 'rootpas

如何恢复MySQL主从数据一致性_Mysql

最近被告知,MySQL主从数据库的数据不一致,猜测备库在同步过程中出现了问题,于是,登上备库,使用 mysql> show slave status\G查看,果然,备库在insert语句中因违反主键约束,导致备库停止了同步.现在的问题很明确,就是如何恢复主从库数据的一致性. 可选方案如下: 一.查看Master最新的Position,将其作为Slave复制的起点. 这种思路体现的是过去的不一致既往不咎,现在保持同步即可.看起来,这个思路和恢复主从库数据的一致性的初衷有所违背,但这种方法,简单,高

MySQL主从复制不一致的解决办法

在MySQL主从复制程中或多少出现一些主从不同步的情况,本文将对数据主从不同步的情况进行简单的总结,请注意本文主要从数据库层面上探讨数据库的主从不一致的情况. 1.网络的延迟 由于mysql主从复制是基于binlog的一种异步复制,通过网络传送binlog文件,理所当然网络延迟是主从不同步的绝大多数的原因,特别是跨机房的数据同步出现这种几率非常的大,所以做读写分离,注意从业务层进行前期设计. 2.主从两台机器的负载不一致 由于mysql主从复制是主数据库上面启动1个io线程,而从上面启动1个sq

mysql主从配置教程及容错方案详解

主从配置我们需要有两台mysql服务器,我们先要清楚两点 1.mysql配置文件my.cnf的位置 2.如何启动.停止mysql,找好启动文件 假设有两台机器,已经安装好了mysql(尽量同版本,且两台机器同一网络,可以ping通) 有朋友说:"从服务器,不能低于主服务器的版本",不过我是低于的,没有出现问题. 主机A: 192.168.1.100 从机B:192.168.1.101 可以有多台从机 环境windows 2003系统 主服配置文件 server-id = 1 log-b

MHA实现mysql主从切换之主故障

本文主要模拟实现主Master故障后,手动指定Master到从节点.   #masterha_master_switch --master_state=dead --conf=/etc/masterha/app1.cnf  --dead_master_host=10.1.1.231 --dead_master_ip=10.1.1.231 --dead_master_port=63306  --new_master_host=10.1.1.234 --new_master_ip=10.1.1.23