澳门皇冠金沙网站-澳门皇冠844网站

热门关键词: 澳门皇冠金沙网站,澳门皇冠844网站

常见的错误及解决方案,快速溯源与排查错误全

图片 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线程恢复。

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

 

三、版本和配置

本文由澳门皇冠金沙网站发布于数据库研究,转载请注明出处:常见的错误及解决方案,快速溯源与排查错误全