场景描述
由于历史遗留问题,开发过程中不知道谁在往这张表里写入数据。线上开始了
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;
启动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;
- 创建触发器
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
- 日志表
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;
- 模拟
结论
这里关联了
information_schema
.processlist
,使得可以获得进程占用端口号。这个端口号对于容器部署来说挺重要的,可以根据端口进一步确定是谁往表里写入数据。
我在测试环境试了一下,有个缺陷。账户权限可能配置成yichen@%
,这种是通配的。