MySQL数据库操作审计：如何追踪对特定表的更新来源

-- 查看当前日志状态
SHOW VARIABLES LIKE 'general_log%';

-- 开启通用查询日志
SET GLOBAL general_log = 'ON';
SET GLOBAL general_log_file = '/var/log/mysql/mysql-general.log';

SELECT event_time, user_host, argument 
FROM mysql.general_log 
WHERE argument LIKE '%UPDATE%statistics_test%' 
   OR argument LIKE '%INSERT%statistics_test%' 
   OR argument LIKE '%DELETE%statistics_test%';

SET GLOBAL general_log = 'OFF';

-- 检查是否已安装
SHOW PLUGINS WHERE NAME LIKE '%audit%';

-- 安装插件（需提前下载.so文件）
INSTALL PLUGIN server_audit SONAME 'server_audit.so';

-- 配置审计规则
SET GLOBAL server_audit_events = 'QUERY_DDL,QUERY_DML';
SET GLOBAL server_audit_logging = 'ON';
SET GLOBAL server_audit_file_path = '/var/log/mysql/audit.log';

cat /var/log/mysql/audit.log | grep "statistics_test"

SELECT 
    trx.trx_id, 
    trx.trx_started, 
    trx.trx_query, 
    usr.user 
FROM 
    information_schema.innodb_trx trx 
JOIN 
    information_schema.processlist usr 
ON 
    trx.trx_mysql_thread_id = usr.id 
WHERE 
    trx.trx_query LIKE '%statistics_test%';

CREATE TABLE statistics_test_audit (
    id INT AUTO_INCREMENT PRIMARY KEY,
    change_type ENUM('INSERT', 'UPDATE', 'DELETE'),
    changed_by VARCHAR(100),
    change_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    record_id INT,          -- 原表的主键
    old_data JSON,          -- 旧数据（可选）
    new_data JSON           -- 新数据（可选）
);

DELIMITER //

-- UPDATE 触发器
CREATE TRIGGER after_statistics_test_update
AFTER UPDATE ON statistics_test
FOR EACH ROW
BEGIN
    INSERT INTO statistics_test_audit (change_type, changed_by, record_id, old_data, new_data)
    VALUES ('UPDATE', CURRENT_USER(), NEW.id, JSON_OBJECT('column1', OLD.column1, 'column2', OLD.column2), 
                                          JSON_OBJECT('column1', NEW.column1, 'column2', NEW.column2));
END//

-- INSERT 触发器
CREATE TRIGGER after_statistics_test_insert
AFTER INSERT ON statistics_test
FOR EACH ROW
BEGIN
    INSERT INTO statistics_test_audit (change_type, changed_by, record_id, new_data)
    VALUES ('INSERT', CURRENT_USER(), NEW.id, JSON_OBJECT('column1', NEW.column1, 'column2', NEW.column2));
END//

-- DELETE 触发器
CREATE TRIGGER after_statistics_test_delete
AFTER DELETE ON statistics_test
FOR EACH ROW
BEGIN
    INSERT INTO statistics_test_audit (change_type, changed_by, record_id, old_data)
    VALUES ('DELETE', CURRENT_USER(), OLD.id, JSON_OBJECT('column1', OLD.column1, 'column2', OLD.column2));
END//

DELIMITER ;

SHOW BINARY LOGS;

mysqlbinlog --database=your_db_name /var/lib/mysql/mysql-bin.000123 | grep "statistics_test"

mysqlbinlog --database=your_db_name --start-datetime="2024-01-01 00:00:00" /var/lib/mysql/mysql-bin.000123 | grep -A 10 -B 10 "statistics_test"