澳门新浦金网站MySQL复制异常大扫盲:快速溯源与

2019-11-30 08:30 来源:未知

澳门新浦金网站 1

MySQL复制异常大扫盲:快速溯源与排查错误全解
https://mp.weixin.qq.com/s/0Ic8BnUokyOj7m1YOrk1tA

一、错误日志解析:

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

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

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

 

一、错误排查

 

1、收集复制信息

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

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

 

(1)查看show slave status

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

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

 

Master_Log_File: mysql-bin.000063
Read_Master_Log_Pos: 282657539

 

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

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

Relay_Log_File: mysql-relay.000002
Relay_Log_Pos: 313885

 

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

(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

TAG标签:
版权声明:本文由澳门新浦金网站发布于数据库,转载请注明出处:澳门新浦金网站MySQL复制异常大扫盲:快速溯源与