Percona Toolkit系列 — pt-fk-error-logger

原创

brightdeng@DBA

发布于 2021-05-31 10:14:59

7552

发布于 2021-05-31 10:14:59

前言

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 删除。

云数据库 SQL Server

原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。

如有侵权，请联系 cloudcommunity@tencent.com 删除。

云数据库 SQL Server

登录后参与评论

0 条评论

热度