确定谁在往mysql的某张表中写入数据

场景描述

由于历史遗留问题，开发过程中不知道谁在往这张表里写入数据。线上开始了bin log但是没有写入记录，测试环境有时候会写入，但是没有开启bin log日志，也没权限开启日志。因此，无法知道是谁在写入数据。

方案

环境模拟

CREATE TABLE `test` (
  `id` int(11) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  `price` decimal(10,2) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
1
2
3
4
5
6

启动mysql binlog日志

参考

结论

这种方法应用的应该比较多，就是需要root权限。。

通过锁表

• 锁定test表
  lock tables test read;
• 启动另一个终端，往表中写入数据
  

这里可以看到卡住了，需要锁释放才能写入

• 查看test表的处理列表

SELECT * FROM information_schema.processlist where db = ‘test’;

这里可以看到，我们的插入sql目前正在等待，以及哪个谁触发的。我这里本地模拟，所以是localhost:55165

windows下可以看这个对应端口是谁在用，上图可以看出是mysql.exe。正常情况的话可以定位到对应服务。

• 解除表锁定

unlock tables;

通过触发器

• 建立日志表

CREATE TABLE `log` (
  `user` varchar(255) DEFAULT NULL,
  `create_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
1
2
3
4

• 创建触发器

create trigger log_trigger before insert on test for each row
insert into log(user) values (CURRENT_USER);

• 测试
  
• 删除触发器

drop trigger log_trigger;

结论

这种方法只能定位到机器和用户名，但是如果服务部署在容器上，然后各个服务用的同一个用户名，就难以区分了。

触发器优化版

• 触发器sql

create trigger log_test before insert on test for each row
begin
declare addr varchar(255);
declare addx varchar(30);
declare done int default false;
DECLARE cur CURSOR FOR SELECT `host` FROM `information_schema`.`processlist` where db = 'test' and user = SUBSTRING_INDEX(CURRENT_USER,'@',1) and SUBSTRING_INDEX(host,':',1) = SUBSTRING_INDEX(CURRENT_USER,'@',-1);
declare continue handler for not found set done = true;
set addr = '';
open cur;
repeat
fetch cur into addx;
set addr = CONCAT(addr,',',addx);
until done end repeat;
close cur;
insert into log(user,port) values (current_user,addr);
end
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

• 日志表

CREATE TABLE `log` (
  `port` varchar(255) DEFAULT NULL,
  `user` varchar(255) DEFAULT NULL,
  `create_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
1
2
3
4
5

• 模拟
  

结论

这里关联了information_schema.processlist，使得可以获得进程占用端口号。这个端口号对于容器部署来说挺重要的，可以根据端口进一步确定是谁往表里写入数据。
我在测试环境试了一下，有个缺陷。账户权限可能配置成yichen@%，这种是通配的。