MySQL中，如果能看到谁在什么时候在某表上执行了什么操作，会让我们有能力在出现该表数据丢失时更好地确定谁是问题的原因。
开启general log会将所有到达MySQL服务器的SQL语句记录下来。但是，一般不会持续开启这个功能，因为log的量会非常庞大，IO很大，而且会对general log的表或文件有争用现象造成性能瓶颈。
二进制文件（binlog）也会记录，但是不会记录执行SQL语句的用户名。
此文中，我提出另外一种方案，用触发器来实现记录这个表上的增删改操作日志，包括一个完整的SQL查询、执行此语句的用户和执行此操作的时间。这是一个更高层次的解决方案，更接近应用层，而不是一个低层次的系统日志解决方案。
1. 新建一个数据库，
create database if not exists audit;
use audit;
##新建一个操作记录表，用来存储特定表上的增删改操作
create table if not exists audit.log_per_tb(query longtext, user varchar(32), operation_time timestamp );
2. 新建一个测试表，记录表tb1的增删改操作
create table if not exists audit.tb1(c1 int,c2 int);
3. 创建表tb1上的增删改触发器，当tb1上有增删改操作时，触发器向操作记录表中插入相应的增删改语句、用户名和当前时间。
INSERT触发器，
drop TRIGGER if exists log_insert;
DELIMITER |
CREATE TRIGGER log_insert BEFORE INSERT ON audit.tb1
FOR EACH ROW
BEGIN
DECLARE original_query VARCHAR(1024);
declare username varchar(32);
declare i int default row_count();
SET original_query = (SELECT info FROM INFORMATION_SCHEMA.PROCESSLIST WHERE id = CONNECTION_ID());
IF i <> 1 THEN INSERT INTO audit.log_per_tb() VALUES (original_query,session_user(),now());
END IF;
END;
|
DELIMITER ;
注意，mysql中只支持基于行的触发器，也就是说，触发器是针对一条记录的，而不是针对整个SQL语句的。因此，在上述的触发器中，我利用了触发器函数 row_count() 绕过 基于行的触发器 的限制。因为 row_count() 总是返回1.利用这个特点，使得触发器不再是针对每一行都运行，而是针对一条SQL语句运行一次。
因此，一条INSERT语句不管其涉及插入了多少行，上述 INSERT触发器只向操作记录表中写入一条记录。
UPDATE触发器，
DROP TRIGGER if exists log_update;
DELIMITER |
CREATE TRIGGER log_update BEFORE UPDATE ON audit.tb1
FOR EACH ROW
BEGIN
DECLARE original_query VARCHAR(1024);
declare username varchar(32);
declare i int default row_count();
SET original_query = (SELECT info FROM INFORMATION_SCHEMA.PROCESSLIST WHERE id = CONNECTION_ID());
IF i <> 1 THEN INSERT INTO audit.log_per_tb() VALUES (original_query,session_user(),now());
END IF;
END;
|
DELIMITER ;
DELETE触发器，
DROP TRIGGER if exists log_delete;
DELIMITER |
CREATE TRIGGER log_delete BEFORE DELETE ON audit.tb1
FOR EACH ROW
BEGIN
DECLARE original_query VARCHAR(1024);
declare username varchar(32);
declare i int default row_count();
SET original_query = (SELECT info FROM INFORMATION_SCHEMA.PROCESSLIST WHERE id = CONNECTION_ID());
IF i <> 1 THEN INSERT INTO audit.log_per_tb() VALUES (original_query,session_user(),now());
END IF;
END;
|
DELIMITER ;
至此，准备环境已好。
4. 测试
在表上做增删改操作。
insert audit.tb1 values(2,1),(2,3);
insert audit.tb1 values(1,1),(1,1),(1,2),(2,2),(3,1),(3,2);
UPDATE audit.tb1 SET c2=44 WHERE c1=3;
delete from audit.tb1 WHERE c1=2;
SELECT * FROM audit.log_per_tb;
可以看出，用户root在TB1上做的增删改操作都被记录下来。
总结，我用触发器实现了一个跟踪某表上增删改操作的更接近应用层的解决方案。
