Posted inMySQL

Hướng dẫn sử dụng MySQL Event Scheduler: Tự động hóa lịch chạy tác vụ và bảo trì Database định kỳ

MySQL tutorial - IT technology blog
MySQL tutorial - IT technology blog

Bối cảnh & Tại sao cần

Hệ thống sau vài tháng vận hành thường bắt đầu lộ vấn đề ở các bảng tích lũy nhanh như audit_logs, user_sessions hay sms_otps. Khi một bảng phình từ vài chục nghìn lên hơn 10 triệu bản ghi, dung lượng đĩa cứng bị chiếm dụng nhanh chóng. Tệ hơn, câu lệnh SELECT phân trang hay tìm kiếm thường ngày từ 50ms có thể vọt lên 3-4 giây.

Thói quen phổ biến của nhiều team là viết script Python hoặc Node.js rồi giao cho Linux crontab chạy đêm. Cách này giải quyết được việc trước mắt. Dù vậy, nó tạo thêm điểm phụ thuộc bên ngoài ứng dụng. Crontab chết không ai hay. Script ngoài đòi hỏi quản lý thông tin đăng nhập riêng, đồng thời tốn thêm network round-trip khi truyền tải hàng triệu dòng dữ liệu qua lại.

MySQL có sẵn giải pháp nội bộ: MySQL Event Scheduler. Tính năng này đóng vai trò như một cron job nằm ngay trong database engine. Máy chủ tự động lập lịch và thực thi các câu lệnh SQL trực tiếp ở tầng dữ liệu mà không cần kết nối trung gian.

Trong thực tế với database MySQL 8.0 dung lượng 60GB, việc cắt tỉa tự động 100.000 dòng log hết hạn mỗi đêm giúp bảng chính thu gọn dung lượng từ 18GB xuống còn 3.5GB. Kích thước chỉ mục (B-Tree index) nhỏ hơn rõ rệt. Nhờ đó, buffer pool giữ được nhiều dữ liệu nóng hơn, giảm tải I/O đĩa tới 40%.

Cài đặt

Trước khi lên lịch cho bất kỳ tác vụ nào, bạn cần đảm bảo tiến trình điều phối (scheduler thread) trên máy chủ MySQL đang hoạt động.

Bước 1: Kiểm tra trạng thái Event Scheduler

Truy cập vào MySQL CLI qua terminal và chạy truy vấn:

SHOW VARIABLES LIKE 'event_scheduler';

Nếu cột Value hiển thị OFF hoặc DISABLED, tính năng này đang tắt và hệ thống sẽ bỏ qua mọi event đã lên lịch.

Bước 2: Bật Event Scheduler

Kích hoạt tạm thời ngay trong phiên làm việc mà không cần khởi động lại MySQL:

SET GLOBAL event_scheduler = ON;

Lưu ý rằng lệnh SET GLOBAL sẽ bị reset về mặc định nếu server khởi động lại. Từ MySQL 8.0, bạn có thể lưu cấu hình vĩnh viễn trực tiếp bằng lệnh SQL mà không cần sửa file bằng tay:

SET PERSIST event_scheduler = ON;

Nếu dùng MySQL 5.7 hoặc muốn quản lý qua file, hãy thêm dòng sau vào khối [mysqld] trong file /etc/mysql/mysql.conf.d/mysqld.cnf (hoặc /etc/my.cnf):

[mysqld]
event_scheduler=ON

Khởi động lại tiến trình nếu bạn sửa file cấu hình vật lý:

sudo systemctl restart mysql

Bước 3: Phân quyền cho User thực thi

Tài khoản chạy tác vụ cần quyền EVENT trên database mục tiêu. Hãy đăng nhập bằng quyền root và thực thi lệnh cấp quyền:

GRANT EVENT ON my_project_db.* TO 'app_user'@'%';
FLUSH PRIVILEGES;

Cấu hình chi tiết

Cấu trúc chuẩn của một Event trong MySQL bao gồm các thành phần sau:

CREATE EVENT [IF NOT EXISTS] event_name
ON SCHEDULE schedule_definition
[ON COMPLETION [NOT] PRESERVE]
[ENABLE | DISABLE]
DO
  event_body;

1. Tác vụ đơn giản: Dọn dẹp token đăng nhập hết hạn

Xét trường hợp bảng user_tokens lưu refresh token. Token hết hạn sau 30 ngày cần được xóa định kỳ để dọn dẹp dung lượng bảng. Event dưới đây sẽ tự động kích hoạt vào lúc 03:00 sáng mỗi ngày:

CREATE EVENT IF NOT EXISTS ev_clean_expired_tokens
ON SCHEDULE EVERY 1 DAY
STARTS (CURRENT_DATE + INTERVAL 3 HOUR)
ON COMPLETION PRESERVE
ENABLE
DO
  DELETE FROM user_tokens 
  WHERE expires_at < NOW();

Ý nghĩa các tham số thiết lập:

  • EVERY 1 DAY: Chu kỳ lặp lại. Bạn có thể linh hoạt đổi sang 1 HOUR, 30 MINUTE hoặc 1 WEEK tùy nhu cầu.
  • ON COMPLETION PRESERVE: Giữ lại event trong từ điển dữ liệu sau khi thực thi, thay vì bị MySQL tự xóa bỏ.
  • ENABLE: Kích hoạt lịch chạy ngay khi tạo xong.

2. Tác vụ nâng cao: Khối lệnh BEGIN … END dọn log và ghi vết

Đối với kịch bản phức tạp gồm nhiều bước — lưu trữ log cũ sang bảng archive, xóa sạch ở bảng chính, rồi ghi nhật ký hệ thống — bạn cần gom các lệnh vào khối BEGIN ... END kèm việc đổi DELIMITER tạm thời:

DELIMITER $$

CREATE EVENT IF NOT EXISTS ev_archive_and_purge_logs
ON SCHEDULE EVERY 1 WEEK
STARTS (CURRENT_DATE + INTERVAL 2 HOUR)
ON COMPLETION PRESERVE
DO
BEGIN
  -- 1. Sao chép dữ liệu quá 90 ngày sang bảng lưu trữ archive
  INSERT INTO audit_logs_archive
  SELECT * FROM audit_logs 
  WHERE created_at < NOW() - INTERVAL 90 DAY;

  -- 2. Xóa các dòng đã sao chép để giải phóng dung lượng và index
  DELETE FROM audit_logs 
  WHERE created_at < NOW() - INTERVAL 90 DAY;

  -- 3. Ghi vết bảo trì vào bảng quản trị
  INSERT INTO maintenance_history (task_name, executed_at, status)
  VALUES ('ev_archive_and_purge_logs', NOW(), 'SUCCESS');
END $$

DELIMITER ;

3. Tạm dừng và kích hoạt lại Event khi deploy

Trong quá trình migrate schema hoặc cập nhật cấu trúc bảng lớn, các event chạy ngầm có thể gây xung đột khóa (lock conflict). Tốt nhất là tạm tắt event trước khi deploy:

-- Tạm dừng chạy event
ALTER EVENT ev_clean_expired_tokens DISABLE;

-- Mở lại lịch chạy bình thường sau khi deploy hoàn tất
ALTER EVENT ev_clean_expired_tokens ENABLE;

Kiểm tra & Monitoring

Event chạy hoàn toàn ở chế độ nền (background thread). Vì không có màn hình hiển thị trực tiếp, bạn cần chủ động nắm các công cụ giám sát để phát hiện sự cố.

1. Xem danh sách và thời điểm chạy tiếp theo

Kiểm tra nhanh danh sách event đang tồn tại trong database:

SHOW EVENTS FROM my_project_db\G

Để theo dõi cụ thể lần chạy gần nhất (LAST_EXECUTED) và thời điểm kích hoạt tiếp theo (NEXT_RUN), hãy truy vấn bảng hệ thống:

SELECT 
  EVENT_NAME, 
  STATUS, 
  INTERVAL_VALUE, 
  INTERVAL_FIELD, 
  LAST_EXECUTED, 
  NEXT_RUN 
FROM information_schema.EVENTS
WHERE EVENT_SCHEMA = 'my_project_db';

2. Kiểm tra log lỗi hệ thống

Khi event vấp lỗi cú pháp hoặc đụng độ deadlock, MySQL không thể báo về client. Thay vào đó, toàn bộ chi tiết được ghi thẳng vào error.log của server. Bạn kiểm tra nhanh qua terminal:

sudo tail -n 50 /var/log/mysql/error.log

Log liên quan đến scheduler luôn đi kèm tiền tố nhận diện dạng [Warning] hoặc [ERROR] Event Scheduler: [user][db.event_name] ....

3. Kinh nghiệm tránh nghẽn Database khi chạy Event

  • Tránh DELETE nguyên khối hàng triệu bản ghi: Một câu lệnh DELETE xóa cùng lúc 500.000 dòng sẽ khóa hàng loạt bản ghi (row/gap lock), gây nghẽn buffer pool và tạo độ trễ replication rất lớn trên slave server. Hãy chia nhỏ thành các batch 5.000 dòng kèm mệnh đề LIMIT, hoặc đặt trong một vòng lặp Stored Procedure có quãng nghỉ ngắn.
  • Bắt buộc đánh Index cho điều kiện WHERE: Mọi cột lọc mốc thời gian như expires_at hay created_at cần có index. Thiếu index, event sẽ quét toàn bộ bảng (full table scan), đẩy CPU lên 100% và làm nghẽn các query nghiệp vụ khác.
  • Né khung giờ cao điểm: Hãy chọn khung giờ có lưu lượng thấp nhất, thường rơi vào khoảng 02:00 – 04:30 sáng, để chạy các tác vụ dọn dẹp hoặc aggregate số liệu nặng.
Share:
Tags: