您的位置:永利集团登录网址 > 计算机入门 > MySQL复制异常大扫盲:快速溯源与排查错误全解

MySQL复制异常大扫盲:快速溯源与排查错误全解

2019-11-15 02:50

图片 1

MySQL复制异常大扫盲:快速溯源与排查错误全解

数据不一致的原因

我们平台中上线了大量的MySQL业务,但是每个MySQL业务中主从数据库之间的一致性,我们基本不可知,而且由于性能原因的考虑,我们99%左右的MySQL中的两个影响数据一致性的参数innodb_flush_log_at_trx_commit设置为1,并且sync_binlog设置为0。这样,一旦出现机器重启时,MySQL数据库的主从节点之间就会存在数据不一致性的情况。

一、错误日志解析:

作者介绍
王松磊,现任职于UCloud,从事MySQL数据库内核研发工作,主要负责UCloud云数据库UDB的内核故障排查工作以及数据库新特性的研发工作。

一致性检测原理

数据不一致性出现以后,一方面会影响业务,另外一方面DBA经常会收到告警:数据库同步错误。为了解决这个问题,我们要实现平台级的数据库一致性对比功能,在平台中定期对必要的业务进行定期的一致性检测和数据修复就非常有必要性了。

所以最近研究了一下MySQL的一致性对比和修复工具:pt-table-checksum和pt-table-sync。下面分别对pt-table-checksum和pt-table-sync的原理做一下简单介绍。

(1) 【ERROR】1452:无法在外键的表插入参考主键没有的数据

复制作为MySQL原生的数据同步功能,在MySQL高可用架构中起着至关重要的作用。本文梳理了MySQL高可用产品UDB在日常运维中遇到的复制问题,并总结了当复制发生异常时,排查复制异常的方法。

pt-table-checksum的原理:

  1. 校验表的结构如下:

CREATE TABLE checksums (
db char(64) NOT NULL,
tbl char(64) NOT NULL,
chunk int NOT NULL,
chunk_time float NULL,
chunk_index varchar(200) NULL,
lower_boundary text NULL,
upper_boundary text NULL,
this_crc char(40) NOT NULL,
this_cnt int NOT NULL,
master_crc char(40) NULL,
master_cnt int NULL,
ts timestamp NOT NULL,
PRIMARY KEY (db, tbl, chunk),
INDEX ts_db_tbl (ts, db, tbl)
) ENGINE=InnoDB;

  1. 把数据进行分块,先计算第一个块的数据的校验和,并写入到校验表中的this_crc和this_cnt字段中,这条检核和的SQL语句会传递到从库,从库会收到后计算自己的SQL语句;
  2. 然后读取主库的中校验表的this_crc和this_cnt字段数据,生成SQL语句更新checksums表中的master_crc和master_cnt字段,从库收到SQL语句以后,会把从库中的checksums表的master_crc和master_cnt字段进行更新,这样在从库中就得到了主库的校验和以及自己的数据校验和;
  3. 轮询整张表,重复2,3步把所有的块计算完成;
  4. 通过下面这条语句就可以得出主库和从库的各个块是否一致select * from checksums where master_cnt <>this_cnt OR master_crc <> this_crc OR ISNULL(master_crc) <>ISNULL(this_crc)

 

一、错误排查

pt-table-sync的原理:

  1. 对每一个从库,每一个表,循环进行如下校验和修复过程。
  2. 对每一个chunk,在校验时加上for update锁。一旦获得锁,就记录下当前主库的show master status值。
  3. 在从库上执行select master_pos_wait()函数,等待从库sql线程执行到show master status得到的位置。以此保证,主从上关于这个chunk的内容均不再改变。
  4. 对这个chunk执行checksum,然后与主库的checksum进行比较。
  5. 如果checksum相同,说明主从数据一致,就继续下一个chunk。
  6. 如果checksum不同,说明该chunk有不一致。深入chunk内部,逐行计算checksum并比较(单行的checksum的比较过程与chunk的比较过程一样,单行实际是chunk的size为1的特例)。
  7. 如果发现某行不一致,则标记下来。继续检测剩余行,直到这个chunk结束。
  8. 对找到的主从不一致的行,采用replace into语句,在主库执行一遍以生成该行全量的binlog,并同步到从库,这会以主库数据为基准来修复从库;对于主库有的行而从库没有的行,采用replace在主库上插入(必须不能是insert);对于从库有而主库没有的行,通过在主库执行delete来删除(pt-table-sync强烈建议所有的数据修复都只在主库进行,而不建议直接修改从库数据;但是也有特例)。
  9. 直到修复该chunk所有不一致的行。继续检查和修复下一个chunk。
  10. 直到这个从库上所有的表修复结束。开始修复下一个从库。

 

1、收集复制信息

平台一致性对比方案

有了这两个一致性对比工具,就很容易在平台中搭建一致性对比和数据修复了,我们在选择一个业务进行对比,通过指令下发就能够对业务进行一致性对比,并从平台中查询对比的进行和最终对比的结果,依据对比结果,决定是否进行数据修复。

1452:无法在外键的表插入或更新参考主键没有的数据。由于item_discovery.itemid字段(外键)参考了items.itemid字段(主键),当要在item_discovery表插数据时,如果items表的主键没有对应的数据,则无法插入,报1452错误。此时可以检查参考的表的主键是否有主库对应的数据,如果有,则插入参考的表相应的数据,再开启复制恢复SQL线程。

在复制发生异常时,我们首先要收集复制相关的信息以及错误相关的信息,主要通过如下手段收集。

问题出现了

测试的过程中,我们发现一旦表比较大的情况下,并且不一致的情况比较严重的情况下,会造成对比非常缓慢,最坏的情况20G的数据对比和修复需要花费6个小时,而且整个对比过程有可能会影响线上正在运行的业务。为了减少这种大表对比和修复的影响,我们必须要针对大表提出我们自己的对比和修复方案来。

 

(1)查看show slave status

解决思路

(2) 【ERROR】1032:删除或更新数据,从库找不到记录

执行命令"show slave status"查看复制相关信息。主要关注以下几条信息:

1.首先需要解决的是要找出数据库中的大表

我们可以通过指令show table status from DBNAME 获取数据库中的表的信息,可以获取到表的行数信息,就可以知道那些表是大表了。但是这条指令有可能会产生副作用,因为这条sql语句会访问information_schema下的表,访问这些表的时候,如果是innodb,这些innodb可能会自动做更新索引统计的操作,这可能会造成大量磁盘操作和缓存数据的换出,为了减少副作用,我们可以在执行show table status from DBNAME之前,先执行set global innodb_stats_on_metadata=0,来防止innodb的“顺手”操作。

 

Master_Log_File: mysql-bin.000063
Read_Master_Log_Pos: 282657539

2.大表对比和修复方案

大表的数据量大,所以我们想到的方案就是大数据计算平台:hadoop和spark平台。所以我们的对比过程图如下所示:

导出主从数据库表中的数据 ---> 上传到Hadoop ---> 用大数据平台对比出不同的key值 ---> 不同的key值采用pt-table-sync类似的原理一行行再次对比,过滤掉相同key ----> 剩下不同的key进行修复

通过以上手段流程就能完美的把大量的数据计算从MySQL迁移到大数据平台,剩下的少量不一致的key值,再到MySQL上比对,从而避免了对业务数据库的影响。大数据库对比有两种选择MapReduce以及Spark,相对来说Spark会比MapReduce简单很多,所以我们选择Spark,

下面简单介绍一下大概的对比方案的实施步骤:

 

IO线程读取到的主库的binlog文件名和该binlog中的位置。这两个字段代表复制过程中binlog由主库传输到备库的进度。

1 导出文件

采用select * into outfile从mysql中导出要对比的表,假设我们从master中导出了一张表,导出文件为:master.txt,内容为:

1,"abc","dddddd"
2,"ef","adddd"

从slave中导出一张表,导出文件为:slave.txt,内容为:

1,"abc","tttt"
2,"ef","fffff"

 1032:删除或更新从库的数据,从库找不到记录。此时,主库的数据是比从库新的,可以采取从库添加相同的数据在开启复制恢复SQL线程。

Relay_Log_File: mysql-relay.000002
Relay_Log_Pos: 313885

2 上传文件到hdfs中

hadoop fs -copyFromLocal master.txt /
hadoop fs -copyFromLocal slave.txt /

 

SQL线程执行到的relay log的文件名和该relay log中的位置。

3 采用下面的spark脚本计算不同的key值

val rddMaster = sc.textFile("/master.txt").map(x =>(x.split(",")(0), x)) // 假设第一个字段是主键
val rddSlave = sc.textFile("/slave.txt").map(x =>(x.split(",")(0), x))
val difference = rddMaster.cogroup(rddSlave).filter(x=> x._2._1 != x._2._2 )
val differenceKey = difference.keys()
differenceKey.collect

通过三面的三个步骤我们就能够得出不同的key值有哪些了。

上面的代码非常简单,只是提供一个思路,对于具体的情况肯定会复杂一些,如有多个主键的情况,依据主键数据中包含了分割符号等等情况,这里就不对这些情况进行细说了。

(3) 【ERROR】1062:从库插入数据,发生唯一性冲突

Relay_Master_Log_File: mysql-bin.000002
Exec_Master_Log_Pos: 316585

 

SQL线程执行到的relay log对应的主库中的binlog文件名和该binlog的位置。
这四个字段代表复制过程中,主库的数据在备库上重放的进度。

 

Slave_IO_Running: Yes
Slave_SQL_Running: No

 1062:从库插入数据,发生唯一性冲突。此时从库已经有相同主键的数据,如果再插入相同主键值的数据则会报错。可以查看主库的改行数据与从库的要插入数据是否一致,如一致则跳过错误,恢复SQL线程,如不一致,则以主库为准,将从库的该行记录删除,再开启复制。

当前发生问题的是哪个线程,IO线程或者时SQL线程。

 

Retrieved_Gtid_Set: ed7c5ee4-762d-11e6-ab9e-6c92bf24c36a:14-3920163
Executed_Gtid_Set: 04ffb4f5-762e-11e6-81e4-6c92bf26c5c2:1

如果当前高可用架构为Master-Master,则以下均在从库的操作都必须set sql_log_bin=0,避免从库执行的语句同步到主库(恢复时以主库的数据为准)。

这两个字段在开启GTID后才有意义。分别代表IO线程接收到的binlog中的事务对应的GTID和SQL线程执行过的事务对应的GTID。

二、怎么解决问题:

这里的GTID不会因为复制而发生改变,即主库的GTID对应的事务一定是主库执行过之后,通过复制发送过来的。备库的GTID对应的事务一定是备库执行的。

1.临时解决方案(业务运行期间不适宜使用数据对比和修复工具)

Last_Errno/Last_IO_Errno/Last_SQL_Errno
Laset_Error/Last_IO_Error/Last_SQL_Error

 

IO/SQL线程发生的错误的相关描述。

【ERROR】1452:

(2)查看错误日志

 

错误日志记录了mysqld发生的错误信息,即复制的错误信息,同时也会记录复制的开始和停止的相关信息,记录位置可以通过如下方式查看:

 

在error log中,主要关注如下的信息。

 

开始复制(start slave)

普通主从复制环境

在从库启动复制时,error log中会记录复制起始位置,包括IO线程读取主库端binlog的起始位置和SQL线程执行的relay log的起始位置。同时error log中还会记录开始复制的具体时间。

 

停止复制(stop slave)

从库:

在从库停止复制时,error log会记录IO线程停止时读取到的主库的binlog的位置,以及停止复制的时间。

 

复制错误信息

 

复制错误信息的描述会在show slave status中的last_error中展现,但是如果错误信息较长的话(尤其是在多线程复制的情况下),show slave status并不能完全的显示错误的全部信息,需要查看错误日志才能查看到完整的错误信息。比如

主库:

上述错误信息并不是一个完整的错误信息描述,可以在error log中看到更完成的信息描述,以及发生错误的时间。

查看主库在出错的相应位置的执行语句,可通过SQL得出当时insert或者update的对应的主键值。

(3)查看二进制日志文件

 

这里的二进制日志文件包括主库的binlog、从库的relay log、从库的binlog。

 

主库的binlog是指主库执行过的事务记录的binlog日志。
从库的relay log是指从库接收到的主库的binlog日志。
从库的binlog是指从库SQL线程复现relay log后记录的日志(log-slave-updates开启)以及从库执行过的事务记录的binlog日志。

查询item_discovery的外键约束c_item_discovery_1参考的表items对应主键值的数据行。

二进制日志文件中记录的日志是以event为单位进行记录,比如一个DML语句通常由4-5个event组成,一个DDL语句通常由2个event组成。

 

二进制日志文件可以通过命令“show binlog events”或者工具mysqlbinlog来将binlog日志转换为可识别的格式。

 

show binlog events格式如下:

从库:

上图显示的为ROW格式的binlog中记录的内容,其中包含了一个DML语句和一条DDL语句。DML语句包含了GTID、QUERY、TABLE_MAP、WRITE_ROW、XID五个event,DDL语句包含了GTID、QUERY两个event。

在items表插入主库查询出来的数据。

mysqlbinlog工具同样可以解析binlog,提供与show binlog event类似的event信息,以其中一个event为例来说明:

 

Event的时间,Event的server_id,Event 的end_log_pos都是来自于主库的

 

Event的时间
为主库执行事务的时间,无论从库的relay log和binlog,时间均为主库执行事务的时间
Event的server_id
记录的是执行该事务的数据库的server_id,可以用来区分这条事务是主库还是从库执行的
Event 的end_log_pos
从库的relay log中的end_log_pos为对应的主库中的binlog的该event的真实文件位置
主库和从库的binlog中的end_log_pos为该binlog的文件真实位置
EVENT的at xxx
at xxx代表该event在文件中的真实位置

基于GTID复制环境

对于以上的二进制日志文件的内容,我们需要关注的信息包括:

与普通主从复制环境处理方式相同。

Previous_gtids events记录了当前binlog之前执行过的所有的gtid信息,用来定位具体的gtid。
GTID event中对应的GTID,与事务是一一对应的,表名该事务是由主库执行还是由重库执行的。
当错误发生时,事务执行的时间,事务的执行具体语句。
主库执行数据库操作后,将相关日志记录到主库的binlog中。备库的IO线程接收到主库传输的binlog日志后,将这些日志记录到relay log中,如果备库开启了log_slave_updates选项,那么SQL线程在重放relay log的过程中,会记录相关binlog日志。这三个二进制文件日志,执行内容上应该是相同的。

 

(4)查看其他变量

【ERROR】1032:

查看其他复制相关的系统变量或者状态,如:
执行“show variables like‘gtid_mode’”查看gtid是否开启;
执行“show status like ‘Rpl_semi_sync_master_status’”查看半同步复制的状态。

 

这里不再一一列举。

 

二、排查错误

 

在收集到以上复制信息后,主要通过如下手段排查复制错误:

发生1032可能是delete或者update时从库没有对应数据行,可以分两种情况处理:

1、查看show slave status

 

查看发生错误的是哪个线程(IO线程或者SQL线程),查看错误原因;
如果是IO线程发生错误,记录发生错误时接收到的binlog的文件名和位置(如果开启了GTID则记录GTID);
如果是SQL线程发生错误,记录发生错误时执行到的relay log的文件名和位置(如果开启了GTID则记录GTID)。

(1)如果是Could not execute Delete_rows,则可以直接跳过错误

2、查看错误日志

 

进一步确认发生错误的原因,部分原因只会记录在错误日志中,不会在show slave status中展示。比如空间不足导致IO线程出错、比如网络中断导致IO线程异常等等。

普通主从复制环境

查看是否是由于其他用户正常关闭复制或者kill复制相关的线程导致复制不可用。

 

查看发生错误时,是否为刚刚启动复制、发生错误的语句是否为第一条复制执行的语句。如果为第一条语句,则需要考虑是否由于搭建复制错误的原因导致复制异常,是否由于意外宕机等其他因素导致复制相关二进制日志文件不正确。

从库:

对比主库和备库的错误日志,查看是否均发生了同样的复制错误,是否主库做了特殊的错误处理。

 

3、对比二进制日志文件

 

对比备库正在接收的binlog与主库正在执行的binlog是否存在冲突(备库接收的binlog的文件和位置要大于主库执行的)。

基于GTID复制环境

如果开启了GTID,查看备库是否本身执行了数据库操作产生了GTID,查看备库执行过的GTID是否要多于主库,备库是否执行过其他主机的GTID。

 

根据发生错误时的binlog的文件和位置(或者GTID),解析主库和备库的二进制文件,对比相同的文件和位置(或者相同的GTID)时日志中记录的操作是否相同。

从库:

查看备库的二进制文件,备库是否执行过与主库冲突的操作。

找出复制出错时的executed_Gtid_Set,若出现多个,则选择跟Master_uuid相同的那一条。

总结

 

对于处于正常状态的复制,应处于以下状态:

 

查看复制状态应该是正常状态,如show slave status显示IO线程和SQL线程的运行状态均为YES,如半同步复制中show status like “rpl%”显示的半同步复制状态为ON。
主库和备库均没有复制相关的错误信息报出。
主库和备库的二进制日志文件中记录的数据库操作内容应一致,主库和备库中的数据内容应保持一致。

(2)如果是Could not execute Update_rows,则需要在二进制日志找出出错位置的SQL,再找出该表在主库的对应的数据行,然后直接在从库插入这条数据,开启SQL线程恢复。

通过对比分析上述信息,查看异常的状态或者日志,可以为我们排查复制相关的错误提供更多的帮助。

 

三、版本和配置

普通主从复制环境

总体来说,版本和配置的不同,只是会造成各种信息的显示格式不同,并不会对上述的方法造成过多的影响。

 

1、版本

从库:

上述信息收集和分析的举例均是在mysql-5.7版本上进行的举例,不同的大版本在信息的内容或者信息的存放方式上可能存在一定的差异。

 

mysql-5.6版本与mysql-5.7版本在复制相关信息上存在以下差异:

 

日志:
在mysql-5.6在停止复制时,error log会有错误的信息记录:

主库:

GTID:
mysql-5.6的gtid_executed以global system variables的方式的展现,mysql-5.7是以mysql.gtid_executed表的方式展现。

查看主库在出错的相应位置的执行语句,可通过SQL得出当时update的对应的主键值。

BINLOG:
mysql-5.6版本在使用自增ID时,会使用如下event来记录自增ID。

 

#170419 11:27:12 server id 30061  end_log_pos 494 CRC32 0x7a9f75c6      Intvar
SET INSERT_ID=1/*!*/;

 

2、配置

查询item_discovery的对应主键值的数据行。

主要体现差异的配置包括gtid_mode和binlog_format。

 

(1)gtid_mode

 

当gtid开启时,gtid作为判断事务是由谁执行,是否执行过、事务接收和执行进度的判断标准。同时可以通过show slave status可以直观的看出gtid的接收、执行的情况。

从库:

当gtid关闭时,file和pos作为接收和执行的判断标准,server_id作为事务由谁执行的标准。但是事务对应的所有的server_id并没有完全的展现出来,所以对于我们排查问题,造成一定的困难。

在items表插入主库查询出来的数据。

(2)binlog_format

 

binlog_format影响的是记录到binlog中的日志内容的格式,以同一条INSERT语句为例,statement格式记录到binlog中的格式如下(只显示差异部分):

 

row格式记录到binlog中的格式如下:

基于GTID复制环境

四、常见复制错误原因及分析过程

与普通主从复制环境处理方式相同。

在收集到上述复制相关信息和错误信息后,我们需要根据实际的错误信息进行分析,这里罗列了几种常见的复制错误,我们可以通过部分或者全部在上述章节收集的相关信息,分析出复制错误发生原因。

 

1、从库执行语句与主库冲突

【ERROR】1062:

(1)错误原因

 

从库执行DML语句或者DDL语句后,主库和从库会出现数据不一致的情况。从而导致主库执行的语句在从库没有办法正常执行。

 

(2)错误信息

 

由于从库执行与主库冲突的语句而导致复制错误,常见的错误信息如下:

普通主从复制环境

创建库或者表失败

 

插入语句主键冲突

从库:

删除语句找不到对应的语句

 

由于这是比较常见的原因,所有导致主从冲突的操作均会导致复制出错,这里不再一一列举。

 

(3)原因分析过程

主库:

这里以由于数据库存在而导致创建数据库出错为例来分析原因。

查看主库在出错的相应位置的执行语句,可通过SQL得出当时insert的对应的主键值。

查看error log

 

Error log中显示的详细错误信息如下:

 

显示在执行GTID 0c1b77a7-c113-11e6-9bd6-d4ae52a34783:6时失败。错误原因为数据库已经存在,无法创建。

查询trends_uint表对应主键值的数据行。

查看show slave status

 

当错误发生后,查看show slave status显示的信息时,会发现如下信息:

 

在Executed_Gtid_Set显示的信息中,除了Master的UUID对应的GTID外,还存在另外一个GTID,我们可以查看从库的GTID,执行如下语句:

从库:

发现另外的GTID是由从库执行而产生的。

在trends_uint表删除主库查询出来的数据。

查看从库binlog日志

 

从库binlog日志记录的是SQL线程复现的主机的binlog信息或者时从库本身执行的事务的binlog日志。这些事务是可以通过server_id或者GTID来区分。

 

这里以创建数据库失败为例,在从库binlog中,查找3a169e6c-f1d0-11e6-bb30-d4ae52a34783:1对应的事务,发现如下信息:

基于GTID复制环境

查看从库relay log日志

与普通主从复制环境处理方式相同。

从库relay log日志记录的是IO线程从主库接收到的binlog日志信息,我们查看执行失败的GTID对应的事务信息:

 

总结

2.彻底解决方案

最终可以确认是由于从库执行了创建数据库语句后,SQL线程再次执行创建数据库的语句时发生复制失败的情况。

 

2、主库的binlog丢失

使用pt-table-checksum和pt-table-sync彻底修复数据不一致。

(1)错误原因

 

复制过程中,由于从库需要读取的主库的binlog丢失,从而导致复制发生异常。导致主库binlog丢失的主要原因如下:

注意:使用pt工具包首先要安装pt工具包和安装perl模块。

主库执行reset master命令
主库执行purge binary/master logs命令
主库设置了expire_logs_days,自动删除了binlog
主库的binlog被误删除

 

(2)错误信息

(1)   从库停止复制

如果发生找不到主机binlog的情况,从库error log会报出如下错误:

 

(3)原因分析过程

 

查看error log

 

Error log中显示的详细错误信息如下:

(2) 在主库创建校验信息表

错误信息显示无法找到对应的binlog文件。

 

查看主库binlog日志

 

查看主库的binlog日志文件列表,可能会发现主库的binlog变成重新开始记录:

 

或者需要复制的binlog已经被删除:

(3) 在主库用pt-table-checksum校验主从数据一致性

总结

 

如果binlog重新开始记录,通常是由于主库执行了reset master命令,导致所有的binlog被删除。

在从库执行以下语句,查看Last_Error,发现数据不一致的表:

如果binlog任然在继续记录,只是从库需要的binlog被删除,通常是由于主库手动执行了purge binary logs命令,或者日志的保留时间超过了expire_logs_days设置的时间。

 

3、从库没有执行主库复制的语句

 

由于GTID的特性,SQL线程不会去执行相同的GTID对应的事务,即如果SQL线程发现从relay log中读取到的事务对应的GTID已经存在于从库的GTID_EXECUTED中,那么SQL线程便不会存在。

 

(1)错误原因

然后返回操作系统执行以下命令:

复制过程中,用于主库执行的事务对应的GTID已经存在于从库的GTID_EXECUTED中,那么从库便不会执行这些事务,从而导致主库和从库的数据不一致。通常有如下情况:

 

主机执行了reset master(从库当前读取主机的第一个binlog,并不会因为reset master而导致找不到文件)
重做主从,从库没有清除从库的binlog

 

(2)错误信息

 

在从库忽略主机执行的事务的过程中,从库复制不会报出任何错误,所以这种复制的异常容易被忽略,没有办法及时的发现。

该命令可以查看该表是否发生数据不一致情况,若有,则使用pt-table-sync修复。

由于主库和从库的数据库不一致,后续的DML和DDL操作可能会发生执行失败的错误。

 

(3)原因分析过程

(4) 在主库用pt-table-sync打印出修复不一致数据的SQL(如果有外键约束,修复数据应先从外键参考的字段所属表开始修复),后将修复语句在从库执行。

这里我们以插入语句找不到对应的表为例。

 

查看error log

三、优化建议

Error log中记录错误信息:

在复制由于1045、1032、1062的原因中断后,应使用三.1的临时解决方案,恢复复制后再在业务低谷使用pt-check-sum检查数据一致性。

查看show slave status

 

show slave status显示的信息全部正常,无从库执行事务的binlog产生。这里不排除从库关闭binlog执行drop table操作的可能。

检查完后可以在从库执行这条语句查看有无数据不一致表:

查看表

 

分别在主机和从库执行命令show create table mydb.mytbl4,发现从库上并未不存在mydb.mytbl4。

 

(4)解析binlog日志

 

解析主机binlog日志,查看建表的事务日志:

针对核心表,可以定制自动数据校验脚本,每周进行数据校验,但必须要在业务低谷进行校验哦!

解析从库的binlog日志,查找是否存在建表的事务日志:

 ————————————————————

这时我们发现对于相同的GTID,从库和主机执行的语句是不相同的。

推荐阅读:

(5)总结

【干货】Linux监控 sar命令解析

通过上述分析,我们推断是从库并没有执行建表语句,从而导致主库数据不一致。

【干货】Linux Shell常用经典脚本收藏

(6)说明

教你分分钟搞定Python之Flask框架

这种情况在mysql-5.7版本会在复制时有更严格的校验,如果主机发送GTID要少于从库的GTID,那么会报告出如下的错误:

网页加载性能调优

但是即使在5.7版本,如果启动复制的时(错误后重新启动),主库执行的GTID超过了从库,仍然会报出同样的错误。

4、主库执行了不进行复制的语句

(1)错误原因

主库上执行的操作并不会写入binlog。这里不考虑主库主动关闭binlog的情况。

(2)错误信息

由于主库和从库的数据不一致,从而导致主库执行的操作复制到从库后,发生从库执行失败的情况。如:

创建FEDERATED引擎的表失败

(3)原因分析过程

这里以使用CONNECTION创建FEDERATED引擎的表为例。

查看error log

Error log中记录错误信息:

查看主库和从库的server表

主库中server表中存在名字为s的记录:

从库中不存在名字为s的记录:

查看CREATE SERVER文档说明

文档中记录,create server语句并不会记录到binlog中。所以导致了主库和从库的数据不一致。复制无法正常进行。

总结

对于不记入binlog的操作,需要主库和从库同时执行,以防发生主库和从库不一致的情况。

5、从库重复执行relay log的语句(非GTID,非多线程复制)

当变量relay_log_info_repository设置为FILE时,从库的SQL线程每次执行完一个事务后,会把对应的文件和位置信息更新到文件relay_log.info中。用于在从库重启时,SQL能够从正确的位置继续进行复制。

(1)错误原因

如果物理机发生宕机或者从库发生意外中断,那么可能发生SQL线程已经执行过了某一个relay log中的事务,但是这个事务对应文件和位置信息并没有及时更新到relay_log.info中的情况。在从库发生重启之后,会将执行过的事务重新再次执行。

(2)错误信息

重复执行的事务包括任何记录到relay log中的事务,可能出现的错误信息包括:

创建库或者表失败:

插入语句主键冲突:

删除语句找不到对应的语句:

由于各种类型的事务均可能执行,这里不再一一列举。

(3)原因分析过程

这里以插入语句主键冲突为例。

查看error log

Error log中记录以下报错信息:

可以看到是SQL线程在启动后执行的第一个事务就发生主键冲突的错误。

查看show slave status

show slave status显示的信息全部正常,无从库执行事务的binlog产生。

查看表mydb.k2

表中已经存在了这条记录。

查看从库的relay log和binlog

查看从库的relay log,从复制的起始位置./relaylog002.000002:616查看

查看从库的binlog:

总结

通过分析上述binlog内容,relay log中并没有记录相同的insert语句,而从库的binlog显示已经执行过该语句,当从库重启后,试图再次执行相同的insert语句,从而导致插入语句的主键冲突。

说明

如果复制使用GTID,那么GTID的特性会使从库不执行相同的语句。
如果在5.7版本复制使用多线程复制,那么mts_recovery会修复这个问题。
只有在非多线程复制、非GTID复制的情况下才可能出现这个错误。

五、总结

如果复制发生了错误,通过收集上述的复制相关信息和错误相关信息,分析这些信息中与正常复制异常的地方,便可为排查复制错误提供更多的可以用来排除异常的信息。

当然复制的错误是多种多样的,并不是所有的错误都可以排查到具体的产生原因。很多复制错误是较难或者无法进行排查的,比如主库或者从库的binlog日志文件已经丢失、比如关闭binlog后执行某些操作导致复制不一致,再比如某些内核BUG导致MySQL的复制逻辑本身发生了异常等。

本文由永利集团登录网址发布于计算机入门,转载请注明出处:MySQL复制异常大扫盲:快速溯源与排查错误全解

关键词: