确定谁在往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;

启动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@%,这种是通配的。

  • 0
    点赞
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值