For DBA

1.pt-fk-error-logger

pt-fk-error-logger是用于检测和记录数据库中插入违反外键约束的工具，个人感觉这种错误应该在应用层避免掉，而不是靠数据库来维护外键约束。

2.测试

2.1创建测表

/*table1*/
create table repo_table(
repo_id char(13) not null primary key,
repo_name char(14) not null)
engine=innodb;
/*table2*/
create table busi_table(
busi_id char(13) not null primary key,
busi_name char(13) not null,
repo_id char(13) not null,
foreign key(repo_id) references repo_table(repo_id))
engine=innodb;

启动pt-fk-error-logger，开始监测

[mysql@hpc02 ~]$ pt-fk-error-logger h=192.168.56.103,u=root,p=111111

插入测试数据:

insert into repo_table values("12","sz"); 
insert into repo_table values("13","cd");  
insert into busi_table values("1003","cd", "13");  
insert into busi_table values("1002","sz", "12");  
insert into busi_table values("1001","gx", "11");

终端已经打印出错误日志信息:

[mysql@hpc02 ~]$ pt-fk-error-logger h=192.168.56.103,u=root,p=111111 
2016-10-16 17:53:43 0x7fa394504700 Transaction:
TRANSACTION 859173, ACTIVE 0 sec inserting, thread declared inside InnoDB 5000
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 7, OS thread handle 140340544685824, query id 30 localhost root update
insert into busi_table values("1001","gx", "11")
Foreign key constraint fails for table `test`.`busi_table`:
,
  CONSTRAINT `busi_table_ibfk_1` FOREIGN KEY (`repo_id`) REFERENCES `repo_table` (`repo_id`)
Trying to add in child table, in index repo_id tuple:
DATA TUPLE: 2 fields;
 0: len 13; hex 31312020202020202020202020; asc 11           ;;
 1: len 13; hex 31303031202020202020202020; asc 1001         ;;

But in parent table `test`.`repo_table`, in index PRIMARY,
the closest match we can find is record:
PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 13; hex 31322020202020202020202020; asc 12           ;;
 1: len 6; hex 0000000d1c18; asc       ;;
 2: len 7; hex b5000000370110; asc     7  ;;
 3: len 14; hex 737a202020202020202020202020; asc sz            ;;

还可以指定--dest将信息记录到数据库中。