Tại sao ứng dụng doanh nghiệp cần Audit Trail?
Tưởng tượng khách hàng gọi lên phản ánh: “Đơn hàng của tôi bị thay đổi trạng thái mà tôi không làm gì cả.” Bạn mở database lên xem — dữ liệu hiện tại đúng là đã thay đổi, nhưng ai đổi, lúc mấy giờ, giá trị cũ là gì thì không ai biết.
Mình từng gặp tình huống đó — còn tệ hơn nhiều. Sự cố xảy ra lúc 3 giờ sáng, database bị corruption một phần. Phải restore từ backup nhưng không có cách nào phân biệt dữ liệu nào đã thay đổi hợp lệ, cái nào do lỗi gây ra. Từ đó mình để ý kỹ hơn đến việc ghi lại mọi thay đổi dữ liệu — không chỉ backup.
Audit Trail (hay Change Log) là bảng lịch sử ghi lại mọi thao tác INSERT/UPDATE/DELETE trên dữ liệu quan trọng — đủ để trả lời câu hỏi: Ai làm gì, với bản ghi nào, lúc nào, và giá trị trước/sau là gì?
Các tiêu chuẩn như PCI DSS (thanh toán thẻ), HIPAA (y tế), hay ISO 27001 đều yêu cầu hệ thống phải có khả năng truy vết mọi thay đổi dữ liệu nhạy cảm. Thiếu Audit Trail, bạn sẽ không qua được kiểm toán — đơn giản vậy thôi.
Thiết kế bảng Audit Log
Bước nền tảng là thiết kế bảng log — đủ dữ liệu để trả lời mọi câu hỏi nhưng không cồng kềnh đến mức làm chậm hệ thống. Đây là cấu trúc mình dùng sau nhiều lần thử nghiệm:
CREATE TABLE audit_log (
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
table_name VARCHAR(100) NOT NULL,
record_id BIGINT UNSIGNED NOT NULL,
action ENUM('INSERT','UPDATE','DELETE') NOT NULL,
changed_by VARCHAR(100) DEFAULT NULL,
changed_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
old_data JSON DEFAULT NULL,
new_data JSON DEFAULT NULL,
INDEX idx_table_record (table_name, record_id),
INDEX idx_changed_at (changed_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
Lý do chọn cấu trúc này:
old_data/new_datadùng kiểu JSON — một bảng log dùng được cho tất cả bảng nghiệp vụ, không cần tạo riêng mỗi bảng.changed_at DATETIME(3)— lưu đến mili-giây, quan trọng khi nhiều thao tác xảy ra trong cùng 1 giây.changed_by— user thực hiện thao tác, do application truyền vào qua session variable (xem phần tiếp theo).- Index
(table_name, record_id)để tra cứu toàn bộ lịch sử của 1 bản ghi trong vài millisecond.
Truyền thông tin user vào MySQL bằng Session Variable
Trigger không biết ‘user nào đang đăng nhập app’ — nó chỉ thấy MySQL connection. Giải pháp: application set một session variable trước khi thực hiện thao tác.
Thêm dòng này trước mỗi query quan trọng (PHP, Python, hay bất kỳ ngôn ngữ nào):
-- Application gọi câu này trước khi update/delete
SET @current_user = '[email protected]';
Trong Python với mysql-connector:
def execute_with_audit(conn, query, params, username):
cursor = conn.cursor()
# Set session variable trước
cursor.execute("SET @current_user = %s", (username,))
# Sau đó mới chạy query thật
cursor.execute(query, params)
conn.commit()
cursor.close()
Trong Trigger, lấy giá trị này bằng @current_user.
Viết Trigger cho bảng cần audit
Lấy bảng orders làm ví dụ — thường là bảng nhạy cảm nhất trong hệ thống thương mại điện tử. Cần 3 trigger: AFTER INSERT, AFTER UPDATE, và AFTER DELETE.
Trigger cho INSERT
DELIMITER //
CREATE TRIGGER trg_orders_after_insert
AFTER INSERT ON orders
FOR EACH ROW
BEGIN
INSERT INTO audit_log (table_name, record_id, action, changed_by, new_data)
VALUES (
'orders',
NEW.id,
'INSERT',
@current_user,
JSON_OBJECT(
'id', NEW.id,
'customer_id',NEW.customer_id,
'status', NEW.status,
'total', NEW.total,
'created_at', NEW.created_at
)
);
END//
DELIMITER ;
Trigger cho UPDATE
DELIMITER //
CREATE TRIGGER trg_orders_after_update
AFTER UPDATE ON orders
FOR EACH ROW
BEGIN
INSERT INTO audit_log (table_name, record_id, action, changed_by, old_data, new_data)
VALUES (
'orders',
NEW.id,
'UPDATE',
@current_user,
JSON_OBJECT(
'customer_id',OLD.customer_id,
'status', OLD.status,
'total', OLD.total
),
JSON_OBJECT(
'customer_id',NEW.customer_id,
'status', NEW.status,
'total', NEW.total
)
);
END//
DELIMITER ;
Trigger cho DELETE
DELIMITER //
CREATE TRIGGER trg_orders_after_delete
AFTER DELETE ON orders
FOR EACH ROW
BEGIN
INSERT INTO audit_log (table_name, record_id, action, changed_by, old_data)
VALUES (
'orders',
OLD.id,
'DELETE',
@current_user,
JSON_OBJECT(
'id', OLD.id,
'customer_id',OLD.customer_id,
'status', OLD.status,
'total', OLD.total
)
);
END//
DELIMITER ;
Tại sao dùng AFTER thay vì BEFORE? Đơn giản: bạn chỉ muốn ghi log khi thao tác thực sự thành công. Nếu transaction chính bị rollback, trigger cũng rollback theo — log và dữ liệu thật luôn đồng bộ.
Kiểm tra hệ thống Audit Trail
Chạy thử và xem log
-- Giả lập application set user
SET @current_user = '[email protected]';
-- Tạo đơn hàng mới
INSERT INTO orders (customer_id, status, total, created_at)
VALUES (42, 'pending', 1500000, NOW());
-- Cập nhật trạng thái
UPDATE orders SET status = 'confirmed', total = 1650000 WHERE id = LAST_INSERT_ID();
-- Xem log
SELECT
id,
action,
changed_by,
changed_at,
old_data,
new_data
FROM audit_log
WHERE table_name = 'orders'
ORDER BY changed_at DESC
LIMIT 10;
Query tra cứu lịch sử của 1 bản ghi
-- Xem toàn bộ lịch sử thay đổi của đơn hàng ID = 123
SELECT
action,
changed_by,
changed_at,
JSON_UNQUOTE(JSON_EXTRACT(old_data, '$.status')) AS old_status,
JSON_UNQUOTE(JSON_EXTRACT(new_data, '$.status')) AS new_status,
JSON_UNQUOTE(JSON_EXTRACT(old_data, '$.total')) AS old_total,
JSON_UNQUOTE(JSON_EXTRACT(new_data, '$.total')) AS new_total
FROM audit_log
WHERE table_name = 'orders' AND record_id = 123
ORDER BY changed_at ASC;
Monitoring: Phát hiện thao tác bất thường
Chạy hai query này định kỳ (ví dụ mỗi 15 phút qua cron job) để phát hiện sớm các thao tác đáng ngờ:
-- Tìm user xóa nhiều bản ghi nhất trong 1 giờ qua
SELECT
changed_by,
COUNT(*) AS delete_count
FROM audit_log
WHERE action = 'DELETE'
AND changed_at >= NOW() - INTERVAL 1 HOUR
GROUP BY changed_by
HAVING delete_count > 10
ORDER BY delete_count DESC;
-- Xem tất cả thay đổi trong 24 giờ qua theo bảng
SELECT
table_name,
action,
COUNT(*) AS total
FROM audit_log
WHERE changed_at >= NOW() - INTERVAL 24 HOUR
GROUP BY table_name, action
ORDER BY table_name, action;
Một số vấn đề thực tế cần xử lý
Bảng log lớn nhanh — cần partition hoặc archive
Một hệ thống xử lý 5,000 đơn hàng mỗi ngày, mỗi đơn trung bình cập nhật trạng thái 3-4 lần — audit_log sẽ tích lũy khoảng 600,000 dòng/tháng chỉ riêng bảng orders. Giải pháp gọn nhất là partition theo tháng:
-- Tạo bảng audit_log với RANGE partition theo tháng
CREATE TABLE audit_log (
id BIGINT UNSIGNED AUTO_INCREMENT,
-- ... các cột khác ...
changed_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
PRIMARY KEY (id, changed_at)
) ENGINE=InnoDB
PARTITION BY RANGE (YEAR(changed_at) * 100 + MONTH(changed_at)) (
PARTITION p2026_01 VALUES LESS THAN (202602),
PARTITION p2026_02 VALUES LESS THAN (202603),
PARTITION p2026_03 VALUES LESS THAN (202604),
PARTITION p_future VALUES LESS THAN MAXVALUE
);
Muốn xóa log cũ hơn 1 năm, chỉ cần ALTER TABLE audit_log DROP PARTITION p2025_01 — nhanh hơn DELETE thông thường gấp 50-100 lần, vì MySQL chỉ xóa metadata của partition thay vì scan từng dòng.
Không nên audit tất cả bảng
Mình chỉ đặt trigger cho bảng thực sự cần: orders, payments, users, inventory. Bảng sessions, cache, hay job_queue? Bỏ qua — mỗi request web thường đọc/ghi session vài chục lần, audit những thứ này chỉ làm bảng log phình to mà không có giá trị truy vết nào.
Bảo vệ bảng audit_log khỏi bị xóa
Một rủi ro ít người nghĩ đến: nếu attacker chiếm được connection của ứng dụng, họ có thể xóa luôn audit_log để xóa dấu vết. Tách quyền bằng cách tạo user riêng biệt:
CREATE USER 'audit_writer'@'localhost' IDENTIFIED BY 'strong_password';
GRANT INSERT ON yourdb.audit_log TO 'audit_writer'@'localhost';
-- Không GRANT UPDATE, DELETE
Lưu ý: Trigger chạy dưới quyền của MySQL user thực hiện thao tác — user đó phải có quyền INSERT vào audit_log thì trigger mới ghi được.
Tổng kết
Một buổi chiều setup xong, đổi lại toàn bộ khả năng truy vết lịch sử. Mình đã triển khai pattern này cho vài hệ thống ERP và thương mại điện tử — mỗi lần xảy ra sự cố dữ liệu, audit_log là nơi đầu tiên mở ra. Gần như lúc nào cũng tìm được câu trả lời trong vài phút, thay vì mò mẫm hàng giờ qua application log. Nếu hệ thống của bạn chưa có audit trail, hôm nay là thời điểm tốt để bắt đầu.
