pt-fk-error-logger,通过定时拉取和解析show engine innodb status相关信息,并将错误信息打印在屏幕 或 写到日志文件 或 写到表,从而实现将所有发生的违反外键约束错误都保存下来;该工具默认是永久运行的,除非设置了--run-time 或 --iterations选项。
(1)基本语法
pt-fk-error-logger [OPTIONS] [DSN]
(2)常见选项
--host:-h,IP地址
--port:-P,端口
--socket:-S,套接字文件
--user:-u,用户名
--password:-p,密码
--charset:-A,字符集
--database:-D,数据库
--ask-pass:提示手动输入密码
--daemonize:后台运行
--dest:指定存储违反外键约束错误的表
--columns:违反外键约束错误存储表的列信息
--log:指定存储违反外键约束错误的日志文件
--run-time:运行时间,默认永久
--interval:运行间隔,默认30s
--iterations:运行次数,默认无限
(3)DSN选项
h:IP地址
P:端口
S:套接字文件
u:用户名
p:密码
A:字符集
D:数据库
t:表
(1)创建存储违反外键约束错误的库和表
mysql> show create database percona_schema\G
*************************** 1. row ***************************
Database: percona_schema
Create Database: CREATE DATABASE `percona_schema` /*!40100 DEFAULT CHARACTER SET utf8mb4 */
1 row in set (0.00 sec)
mysql> show create table foreign_key_errors\G
*************************** 1. row ***************************
Table: foreign_key_errors
Create Table: CREATE TABLE `foreign_key_errors` (
`ts` datetime NOT NULL,
`error` text NOT NULL,
PRIMARY KEY (`ts`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1 row in set (0.01 sec)
(2)后台运行pt-fk-error-logger
$ pt-fk-error-logger h=10.1.4.9,P=10057,u=test,p=Test@2020 --dest h=10.1.4.9,P=10057,u=test,p=Test@2020,D=percona_schema,t=foreign_key_errors --daemonize
(3)创建测试表
mysql> show create table t1\G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`k` int(10) unsigned NOT NULL DEFAULT '0',
`c` char(120) NOT NULL DEFAULT '',
`pad` char(60) NOT NULL DEFAULT '',
PRIMARY KEY (`id`),
KEY `k_2` (`k`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 MAX_ROWS=1000000
1 row in set (0.00 sec)
mysql> show create table t2\G
*************************** 1. row ***************************
Table: t2
Create Table: CREATE TABLE `t2` (
`id1` int(10) unsigned NOT NULL AUTO_INCREMENT,
`id2` int(10) unsigned NOT NULL,
PRIMARY KEY (`id1`),
KEY `id2` (`id2`),
CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`id2`) REFERENCES `t1` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)
(4)表t1插入测试数据
mysql> insert into t1 select * from sbtest1 limit 10;
Query OK, 10 rows affected (0.01 sec)
Records: 10 Duplicates: 0 Warnings: 0
mysql> select * from t1;
+----+--------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+
| id | k | c | pad |
+----+--------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+
| 1 | 495766 | 39608790894-68392670039-37548238433-20925234464-67089724457-98621148410-19759935899-31879799337-93408588279-71473642808 | 14889672350-61356874832-90330081378-61059434224-35046383728 |
| 2 | 502049 | 99357217743-27579710696-29634161678-68490301784-71286199462-01615674543-02927167839-64721672329-91298439062-10531459749 | 55069244140-88599530935-98759831323-95072900918-41378360656 |
| 3 | 498079 | 50128537286-10799577578-62404728334-52456441808-05833776482-61026995603-96057555624-83605612599-57933527185-49218679533 | 68241601191-64652034956-85488456634-53544332186-43439186142 |
| 4 | 504692 | 92675911393-14698622699-21306879609-90506073268-22071146625-50134565043-11245187652-15595092256-56826930491-13964438694 | 70591055710-64743409594-29252601588-01395565005-85868662716 |
| 5 | 503806 | 96656984077-26020741029-07783440599-71359977820-23324524203-24150793317-13054445223-46696601391-03747510397-76367509987 | 59597655225-00692027784-53164294049-40019214723-96357218011 |
| 6 | 433420 | 25281316021-32380986237-38580063448-15404997058-43067904668-55377391343-80232088139-55117574755-80652008263-04968031289 | 71796930813-37511906025-71457014659-40910560758-50403293189 |
| 7 | 500048 | 62548123999-69925874370-54676321111-48081340289-89414616766-73791844967-29184498486-48261822911-79399151754-41908359643 | 90162084609-00754384813-70265455817-99667220813-06579906880 |
| 8 | 503430 | 07698804415-97984960963-57318494759-67478150824-93876577915-65158332814-63121481170-48355669379-86092134581-05869772152 | 00565432492-97204683898-78110542991-07418083455-46607371026 |
| 9 | 473164 | 17827929919-85180163996-46622061637-57879278768-75484894580-71322922520-15224624550-75646038699-53458115999-23406030443 | 60402565761-11571716399-37841835723-64743647377-15395037304 |
| 10 | 508996 | 26584291592-28213800905-54842365044-18299974526-01606133239-56095476806-50159350293-32377307014-32499832630-85609388316 | 02728728126-91778438752-30409326705-08066668004-02099669911 |
+----+--------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+
10 rows in set (0.00 sec)
(5)表t2插入测试数据,报错违反外键约束
mysql> insert into t2 values(1,1);
Query OK, 1 row affected (0.00 sec)
mysql> insert into t2 values(11,11);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`id2`) REFERENCES `t1` (`id`))
(6)查询存储表,错误信息已记录
mysql> select * from foreign_key_errors\G
*************************** 1. row ***************************
ts: 2021-05-13 13:10:12
error: 0x7f5ae80f4700 Transaction:
TRANSACTION 1384423, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
3 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 1
MySQL thread id 114, OS thread handle 140028417099520, query id 22832 localhost root update
insert into t2 values(11,11)
Foreign key constraint fails for table `test`.`t2`:
,
CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`id2`) REFERENCES `t1` (`id`)
Trying to add in child table, in index id2 tuple:
DATA TUPLE: 2 fields;
0: len 4; hex 0000000b; asc ;;
1: len 4; hex 0000000b; asc ;;
But in parent table `test`.`t1`, in index PRIMARY,
the closest match we can find is record:
PHYSICAL RECORD: n_fields 6; compact format; info bits 0
0: len 4; hex 0000000a; asc ;;
1: len 6; hex 000000151fd8; asc ;;
2: len 7; hex e4000000280185; asc ( ;;
3: len 4; hex 0007c444; asc D;;
4: len 30; hex 32363538343239313539322d32383231333830303930352d353438343233; asc 26584291592-28213800905-548423; (total 120 bytes);
5: len 30; hex 30323732383732383132362d39313737383433383735322d333034303933; asc 02728728126-91778438752-304093; (total 60 bytes);
1 row in set (0.00 sec)
(7)表t2再次插入测试数据,报错违反外键约束
mysql> insert into t2 values(12,12);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`id2`) REFERENCES `t1` (`id`))
(8)再次查询存储表,错误信息已记录,之前的错误信息也没有被覆盖
mysql> select * from foreign_key_errors\G
*************************** 1. row ***************************
ts: 2021-05-13 13:10:12
error: 0x7f5ae80f4700 Transaction:
TRANSACTION 1384423, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
3 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 1
MySQL thread id 114, OS thread handle 140028417099520, query id 22832 localhost root update
insert into t2 values(11,11)
Foreign key constraint fails for table `test`.`t2`:
,
CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`id2`) REFERENCES `t1` (`id`)
Trying to add in child table, in index id2 tuple:
DATA TUPLE: 2 fields;
0: len 4; hex 0000000b; asc ;;
1: len 4; hex 0000000b; asc ;;
But in parent table `test`.`t1`, in index PRIMARY,
the closest match we can find is record:
PHYSICAL RECORD: n_fields 6; compact format; info bits 0
0: len 4; hex 0000000a; asc ;;
1: len 6; hex 000000151fd8; asc ;;
2: len 7; hex e4000000280185; asc ( ;;
3: len 4; hex 0007c444; asc D;;
4: len 30; hex 32363538343239313539322d32383231333830303930352d353438343233; asc 26584291592-28213800905-548423; (total 120 bytes);
5: len 30; hex 30323732383732383132362d39313737383433383735322d333034303933; asc 02728728126-91778438752-304093; (total 60 bytes);
*************************** 2. row ***************************
ts: 2021-05-13 13:13:55
error: 0x7f5ae80f4700 Transaction:
TRANSACTION 1384437, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
3 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 1
MySQL thread id 114, OS thread handle 140028417099520, query id 22861 localhost root update
insert into t2 values(12,12)
Foreign key constraint fails for table `test`.`t2`:
,
CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`id2`) REFERENCES `t1` (`id`)
Trying to add in child table, in index id2 tuple:
DATA TUPLE: 2 fields;
0: len 4; hex 0000000c; asc ;;
1: len 4; hex 0000000c; asc ;;
But in parent table `test`.`t1`, in index PRIMARY,
the closest match we can find is record:
PHYSICAL RECORD: n_fields 6; compact format; info bits 0
0: len 4; hex 0000000a; asc ;;
1: len 6; hex 000000151fd8; asc ;;
2: len 7; hex e4000000280185; asc ( ;;
3: len 4; hex 0007c444; asc D;;
4: len 30; hex 32363538343239313539322d32383231333830303930352d353438343233; asc 26584291592-28213800905-548423; (total 120 bytes);
5: len 30; hex 30323732383732383132362d39313737383433383735322d333034303933; asc 02728728126-91778438752-304093; (total 60 bytes);
2 rows in set (0.00 sec)
通过pt-fk-error-logger这个工具,可以将数据库中违反外键约束的错误,持续记录下来,非常方便。
原创声明:本文系作者授权腾讯云开发者社区发表,未经许可,不得转载。
如有侵权,请联系 cloudcommunity@tencent.com 删除。
原创声明:本文系作者授权腾讯云开发者社区发表,未经许可,不得转载。
如有侵权,请联系 cloudcommunity@tencent.com 删除。