无法在外键的表插入参考主键没有的数据必威,

必威 1

MySQL复制万分大扫除文盲:飞快溯源与排查错误全解

数据不等同的缘由

大家平台北上线了汪洋的MySQL业务,可是各个MySQL业务中主从数据库之间的生龙活虎致性,我们基本不可见,而且由于天性原因的思考,大家99%左右的MySQL中的四个影响多少生龙活虎致性的参数innodb_flush_log_at_trx_commit设置为1,并且sync_binlog设置为0。那样,意气风发旦现身机械重启时,MySQL数据库的骨干节点之间就能存在数量不生龙活虎致性的景观。

 假若大家临盆景况复制出错?该如何是好吧?

 基于GTID的复制

生机勃勃、错误日志解析:

作者介绍
王松磊,现任职于UCloud,从事MySQL数据库内核研究开发专门的工作,首要承当UCloud云数据库UDB的内核故障每一个调查专门的工作以致数据库新特征的研究开发工作。

豆蔻年华致性检查评定原理

数据不风姿浪漫致性出现之后,一方面会影响职业,别的一方面DBA平时会抽取告警:数据库同步错误。为了缓和那个主题素材,大家要得以达成平台级的数据库大器晚成致性比较功效,在平桃园准时对供给的业务实行期限的意气风发致性检验和数据修复就丰盛有供给性了。

于是目前商讨了弹指间MySQL的风度翩翩致性比较和修复工具:pt-table-checksum和pt-table-sync。下面分别对pt-table-checksum和pt-table-sync的法则做一下简短介绍。

上面提供两种方法:

简介

(1) 【ERubiconRO奥迪Q5】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)

   1. 手工业管理,补回不生龙活虎致数据(能够选取主库来补多少、也能够运用binlog来补多少)

传闻GTID的复制是MySQL 5.6后新扩展的复制方式.

 

大器晚成、错误排查

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. 直至那些从库上有所的表修复截至。最先修复下贰个从库。

   2.用开源工具来消除一致性难题

GTID (global transaction identifier) 即全局职业ID, 保险了在种种在主库上交给的事情在集群中有三个唯意气风发的ID.

 

1、搜罗复制新闻

阳台湾学子机勃勃致性对比如案

有了那八个黄金年代致性比较工具,就超级轻易在平桃园搭建风流罗曼蒂克致性比较和数目修复了,大家在接收多个政工开展自己检查自纠,通过指令发出就能够对业务张开后生可畏致性比较,并从平台北询问相比的进行和最后相比较的结果,凭仗比较结果,决定是还是不是开展数量修复。

   3.协和造轮子,消除生龙活虎致性难点

在原本基于日志的复制中, 从库要求告诉主库要从哪些偏移量实行增量同步, 即使钦赐错误会导致数据的脱漏, 进而产生数据的不等致.

1452:无法在外键的表插入或更新参谋主键未有的数目。由于item_discovery.itemid字段(外键)参考了items.itemid字段(主键),当要在item_discovery表插数据时,如若items表的主键未有相应的数据,则无从插入,报1452不当。当时得以检查参谋的表的主键是不是有主库对应的数目,就算有,则插入仿照效法的表相应的数码,再展开复制恢复生机SQL线程。

在复制发生卓殊时,大家先是要搜聚复制相关的消息以致错误相关的新闻,主要通过如下手腕收集。

标题应际而生了

测量检验的进程中,大家开采只要表相当的大的景观下,並且差别样的景观比较严重的景观下,会造成相比较特别缓慢,最坏的情形20G的数量相比较和修补须要费用6个钟头,何况整个相比进度有相当的大可能率会影响线上正在运作的事务。为了削减这种大表相比和修复的震慑,大家一定要指向性大表提议大家温馨的比较和修复方案来。

 

而据悉GTID的复制中, 从库会告知主库已经实施的政工的GTID的值, 然后主库会将具有未进行的职业的GTID的列表再次来到给从库. 何况能够确定保证同三个作业只在钦赐的从库试行一次.

 

(1)查看show slave status

减轻思路

关于如何通过手工业方式来修补不等同数据,小编就不风流罗曼蒂克一说了,大约正是缺啥补撒把,如果一大波的不平等,那么手工业来搞必然会累的半死,并且也不得法,本文首要介绍一下三方工具pt-table-sync

实战

(2) 【E奥德赛RO智跑】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的“顺手”操作。

 

1、在主库上确立复制账户并付与权限

 

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以至斯Parker,相对来讲斯Parker会比MapReduce简单超多,所以大家选用斯Parker,

上边简要介绍一下大意的对照方案的施行步骤:

Pt-table-sync  原理:

据他们说GTID的复制会自动地将未有在从库实行的职业重播, 所以不要在任何从库上成立平等的账号. 若是构建了相符的账户, 有望导致复制链路的错误.

 

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"

1.在主库上剖判表结构,把说有字段转换来varchar类型

mysql> create user 'repl'@'172.%' identified by '123456';

 1032:删除或更新从库的数量,从库找不到记录。那时,主库的数目是比从库新的,能够利用从库增多扳平的数码在拉开复制恢复生机SQL线程。

Relay_Log_File: mysql-relay.000002
Relay_Log_Pos: 313885

2 上传文件到hdfs中

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

2.基于表上的目录,把表数据分为三个个的chunck

瞩目在生养上的密码必须比照相关标准以到达一定的密码强度, 何况规定在从库上的特定网段上才干访谈主库.

 

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.对此各个chunck,把字段利用 concat_ws() 函数链接到一同,然后对这一个值总括 MD5值,存到计算表(test库上面卡塔尔国

mysql> grant replication slave on *.* to 'repl'@'172.%';

(3) 【E悍马H2ROLacrosse】1062:从库插入数据,爆发唯意气风发性矛盾

Relay_Master_Log_File: mysql-bin.000002
Exec_Master_Log_Pos: 316585

4.由于复制关系,这么些计算数据会被复制到从库,并且相仿的言辞也会被实行一遍

查阅顾客

 

SQL线程试行到的relay log对应的主库中的binlog文件名和该binlog的职位。
这多少个字段代表复制进程中,主库的数额在备库上海重机厂放的速度。

5.到此基本的尺度都抱有了, 那么就领头施行相比了,具体相比进程是那般的:对于每一个chunck会有个 主库的MD5值和备考的MD5值,for 各个chunck,if MD5值相等,则跳过;else 深远chunck ,逐行检查每行数据;

mysql> select user, host from mysql.user;
+-----------+-----------+
| user  | host  |
+-----------+-----------+
| prontera | %   |
| root  | %   |
| mysql.sys | localhost |
| root  | localhost |
+-----------+-----------+
4 rows in set (0.00 sec)

 

Slave_IO_Running: Yes
Slave_SQL_Running: No

 

查阅授权

 1062:从库插入数据,发生唯风流倜傥性冲突。那个时候从库已经有同等主键的数码,假若再插入相符主键值的数码则会报错。可以查阅主库的改行数据与从库的要插入数据是不是相通,如生龙活虎致则跳过不当,复苏SQL线程,如不菲年老成致,则以主库为准,将从库的该行记录删除,再展开复制。

日前发生难题的是哪些线程,IO线程可能时SQL线程。

标题来了

mysql> show grants for repl@'172.%';
+--------------------------------------------------+
| Grants for repl@172.%       |
+--------------------------------------------------+
| GRANT REPLICATION SLAVE ON *.* TO 'repl'@'172.%' |
+--------------------------------------------------+
1 row in set (0.00 sec)

 

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

1.那么由于时主从涉嫌,复制必然有延期的图景,怎样确认保障在自己探讨md5时 主从数量是相同的啊,大概说是相符数量的行吧?

2、配置主库服务器

万风流倜傥当前高可用架构为Master-Master,则以下均在从库的操作都一定要set sql_log_bin=0,制止从库进行的口舌同步到主库(复苏时以主库的数据为准卡塔 尔(阿拉伯语:قطر‎。

那四个字段在拉开GTID后才有意义。分别表示IO线程接收到的binlog中的事务对应的GTID和SQL线程实践过的事体对应的GTID。

   这一个就的中肯到 pt-table-sync 在思谋chunck MD5值的时候是什么样总括的了:

[mysqld]
log_bin = /var/log/mysql/mysql-bin
log_bin_index = /var/log/mysql/mysql-bin.index
binlog_format = row
server_id = 101
gtid_mode = ON
enforce_gtid_consistency = ON
#log_slave_updates = ON

二、怎么消除难题:

此地的GTID不会因为复制而发生更改,即主库的GTID对应的事务一定是主库推行过现在,通过复制发送过来的。备库的GTID对应的事体一定是备库执行的。

   主库上:对每一个chunk,在校验时增加for update锁。风华正茂旦得到锁,就记下下当前主库的show master status值。

NOTE: 把日记与数量分开是个好习于旧贯, 最佳能(CANON卡塔尔松开不一样的数码分区

1.临时解决方案(业务运转时期不合适使用数据相比和修复工具卡塔尔国

Last_Errno/Last_IO_Errno/Last_SQL_Errno
Laset_Error/Last_IO_Error/Last_SQL_Error

   从库上:执行select master_pos_wait()函数,等待从库sql线程施行到show master status获得的职分。以此保险,主从上有关这些chunk的源委均不再改换。

enforce_gtid_consistency 强制GTID风度翩翩致性, 启用后以下命令不可能再使用

 

IO/SQL线程发生的荒诞的连带描述。

 

create table ... select ...

【ERROR】1452:

(2)查看错误日志

2.主备不等同一时候候,是怎么修复的吗?

mysql> create table dept select * from departments;
ERROR 1786 (HY000): Statement violates GTID consistency: CREATE TABLE ... SELECT.

 

错误日志记录了mysqld发生的错误新闻,即复制的错误新闻,同时也会记录复制的起来和安息的连锁消息,记录地方能够因而如下形式查看:

   对于主库有然则备库子虚乌有的数目(也许主备不相近卡塔尔:那么备库会跳过,在主库上实践replace into 操作,通过binlog复制的办法在备库举办重复行使。

因为实际是多个单身事件, 所以只可以将其拆分先成立表, 然后再把多少插入到表中

 

在error log中,主要关注如下的音讯。

对此备库有, 但主库不设有的数码:那么依然是在主库实行  delete 操作(主库本来就不设有多少, 所以那一个讲话对于主库来讲是优哉游哉的卡塔尔

create temporary table

 

开头复制(start slave卡塔 尔(阿拉伯语:قطر‎

 

事行业内部部不能够创立有时表

普通主从复制情况

在从库运转复制时,error log中会记录复制发轫地点,包涵IO线程读取主库端binlog的早前位置和SQL线程实行的relay log的起先地点。同时error log中还恐怕会记录起初复制的求实时刻。

关于那一个的沉凝:

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> create temporary table dept(id int);
ERROR 1787 (HY000): Statement violates GTID consistency: CREATE TEMPORARY TABLE and DROP TEMPORARY TABLE can only be executed outside transactional context. These statements are also not allowed in a function or trigger because functions and triggers are also considered to be multi-statement transactions.

 

终止复制(stop slave卡塔 尔(英语:State of Qatar)

 1.那是大器晚成种盲目标复制同步检查机制,相符的数据会在主库和备考总计,给主库备库都带给了额外的担任,同期鉴于复制关系,那些数目会被copy到从库,给互联网带来了压力,给备库也带给了压力(叁个是在选用主库传过来的数量的时候,另一个是在测算md5值时卡塔 尔(英语:State of Qatar)

一直以来业务中创新事务表与非事务表(MyISAM)

从库:

在从库结束复制时,error log会记录IO线程停止时读取到的主库的binlog的地点,以至结束复制的日子。

 2.由于我们的 replace into机制,那么就非得供给那个表上设有  主键或然唯生龙活虎健

mysql> CREATE TABLE `dept_innodb` (id INT(11) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT);
Query OK, 0 rows affected (0.04 sec)

mysql> CREATE TABLE `dept_myisam` (id INT(11) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT) ENGINE = `MyISAM`;
Query OK, 0 rows affected (0.03 sec)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into dept_innodb(id) value(1);
Query OK, 1 row affected (0.00 sec)

mysql> insert into dept_myisam(id) value(1);
ERROR 1785 (HY000): Statement violates GTID consistency: Updates to non-transactional tables can only be done in either autocommitted statements or single-statement transactions, and never in the same statement as updates to transactional tables.

 

复制错误音讯

 3.由于在检查 chunck 的时候利用 select for update 那么早晚会加 X锁,借使备库延迟太大,则应用的性质会有所收缩

于是建议选取Innodb作为暗许的数据库引擎.

 

复制错误信息的陈述会在show slave status中的last_error中显现,不过如果错误信息较长的话(越发是在二十四线程复制的状态下卡塔 尔(英语:State of Qatar),show slave status并不能够一心的体现错误的全数音信,要求查阅错误日志本领查看见完整的错误信息。比方

 

log_slave_updates 该接受在MySQL 5.6版本时依照GTID的复制是必需的, 不过其增大了从服务器的IO负载, 而在MySQL 5.7中该选拔已经不是必须项

主库:

上述错误新闻实际不是三个总体的错误音讯描述,能够在error log中看出更到位的新闻描述,以至发生错误的时间。

那么大家有未有风流罗曼蒂克种不盲目标主意啊?其实我们能够协和完成的,具体怎么实现,我就要稍后讲到

3、配置从库服务器

翻开主库在差之毫厘的附和岗位的进行语句,可通过SQL得出那个时候insert只怕update的呼应的主键值。

(3卡塔尔查看二进制日志文件

master_info_repository 与relay_log_info_repository

 

此间的二进制日志文件包涵主库的binlog、从库的relay log、从库的binlog。

在MySQL 5.6.2事先, slave记录的master消息以致slave应用binlog的音讯寄放在文件中, 即master.info与relay-log.info. 在5.6.2版本之后, 允许记录到table中. 对应的表分别为mysql.slave_master_info与mysql.slave_relay_log_info, 且那四个表均为innodb引擎表.

 

主库的binlog是指主库推行过的专业记录的binlog日志。
从库的relay log是指从库接收到的主库的binlog日志。
从库的binlog是指从库SQL线程复现relay log后记录的日记(log-slave-updates开启卡塔 尔(英语:State of Qatar)以至从库实行过的作业记录的binlog日志。

[mysqld]
log_bin = /var/log/mysql/mysql-bin
log_bin_index = /var/log/mysql/mysql-bin.index
server_id = 102
# slaves
relay_log  = /var/log/mysql/relay-bin
relay_log_index = /var/log/mysql/relay-bin.index
relay_log_info_file = /var/log/mysql/relay-bin.info
enforce_gtid_consistency = ON
log_slave_updates = ON
read_only = ON
master_info_repository = TABLE
relay_log_info_repository = TABLE

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

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

4、从库数据开首化 - [optional]

 

二进制日志文件能够透过命令“show binlog events”只怕工具mysqlbinlog来将binlog日志转变为可辨识的格式。

先在主库上备份数据

 

show binlog events格式如下:

复制代码 代码如下:

从库:

上海体育场合突显的为ROW格式的binlog中著录的内容,个中满含了多少个DML语句和一条DDL语句。DML语句包罗了GTID、QUEMuranoY、TABLE_MAP、WRITE_ROW、XID三个event,DDL语句包蕴了GTID、QUE昂CoraY七个event。

mysqldump --single-transaction --master-data=2 --triggers --routines --all-databases --events -u root -p > backup.sql

在items表插入主库查询出来的多少。

mysqlbinlog工具相通能够深入剖判binlog,提供与show binlog event相通的event消息,以中间八个event为例来申明:

—master-data=2 该接纳将日前服务器的binlog的职责和文件名增到输出文件中(show master status). 要是为1, 将偏移量拼接到CHANGE MASTEENCORE 命令. 固然为2, 输出的偏移量信息将会被讲授。

本文由必威发布于必威-数据,转载请注明出处:无法在外键的表插入参考主键没有的数据必威,

TAG标签:
Ctrl+D 将本页面保存为书签,全面了解最新资讯,方便快捷。