Posted inMySQL

Phục hồi dữ liệu MySQL đến từng giây với Binary Log: Hướng dẫn Point-in-Time Recovery (PITR)

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

Lúc 2 giờ sáng, câu lệnh DELETE không có WHERE

Điện thoại reo. Slack nổ. Người dùng báo toàn bộ đơn hàng trong ngày biến mất. Dev nhìn lại lịch sử query, thấy một câu lệnh quen thuộc nhưng thiếu mất cái đuôi quan trọng nhất:

DELETE FROM orders WHERE status = 'pending';
-- Thực tế đã chạy:
DELETE FROM orders;

Mình từng gặp sự cố database corruption lúc 3 giờ sáng và phải restore từ backup — từ đó mình luôn kiểm tra backup hàng ngày. Nhưng chỉ có backup thôi chưa đủ. Nếu backup gần nhất là 1 giờ sáng và lỗi xảy ra lúc 2:47 sáng, bạn mất gần 2 tiếng dữ liệu. Với hệ thống thương mại điện tử xử lý vài trăm đơn mỗi giờ, đó là thảm họa thực sự.

Đó chính xác là lúc bạn cần Point-in-Time Recovery (PITR) — khả năng tua ngược database về bất kỳ thời điểm nào, tính đến từng giây, bằng cách kết hợp full backup với MySQL Binary Log.

Binary Log là gì và tại sao nó cứu được bạn

Binary Log (binlog) là file nhật ký MySQL ghi lại mọi thay đổi dữ liệu theo thứ tự thời gian: INSERT, UPDATE, DELETE, CREATE TABLE, DROP TABLE… Nó không ghi SELECT vì SELECT không thay đổi dữ liệu.

Cơ chế PITR thực ra không có gì huyền bí:

  1. Restore từ full backup gần nhất (điểm bắt đầu)
  2. Replay lại Binary Log từ thời điểm backup đến ngay trước khi lỗi xảy ra (điểm kết thúc)

Kết quả: database quay về trạng thái sạch, không có câu lệnh phá hoại kia.

Kiểm tra Binary Log đã bật chưa

SHOW VARIABLES LIKE 'log_bin';
-- Variable_name | Value
-- log_bin       | ON

Nếu thấy OFF, bạn cần bật lên trước — và đáng buồn là không thể phục hồi những gì đã qua nếu binlog chưa được ghi. Trên production, đây không phải tùy chọn — là điều kiện tối thiểu.

Bật Binary Log trên MySQL 8.x

# Thêm vào /etc/mysql/mysql.conf.d/mysqld.cnf
[mysqld]
server-id          = 1
log_bin            = /var/log/mysql/mysql-bin.log
binlog_format      = ROW
binlog_expire_logs_seconds = 604800  # Giữ 7 ngày
max_binlog_size    = 100M

sudo systemctl restart mysql

ROW format là lựa chọn tốt nhất cho PITR — nó ghi chính xác từng row bị thay đổi, không chỉ ghi câu SQL. Điều này quan trọng với những câu UPDATE dùng hàm như NOW() hoặc RAND(): STATEMENT format sẽ cho kết quả khác nhau mỗi lần replay, còn ROW format luôn lưu đúng giá trị thực tế đã ghi.

Thực hành PITR từng bước

Bước 1: Xác định thời điểm và vị trí trong Binary Log

Ưu tiên số một ngay khi sự cố xảy ra: tìm chính xác câu lệnh nào gây vấn đề và vị trí của nó trong binlog — trước khi restore hay làm bất cứ điều gì khác.

# Xem danh sách file binlog hiện có
mysqlbinlog --no-defaults /var/log/mysql/mysql-bin.index

# Hoặc trong MySQL
SHOW BINARY LOGS;

# Đọc nội dung binlog, lọc theo thời gian
mysqlbinlog --no-defaults \
  --start-datetime="2026-04-01 02:00:00" \
  --stop-datetime="2026-04-01 03:00:00" \
  /var/log/mysql/mysql-bin.000023 | grep -A5 "DELETE FROM orders"

Output sẽ cho bạn thấy dòng tương tự:

# at 487219
#260401  2:47:33 server id 1  end_log_pos 487391
### DELETE FROM `mydb`.`orders`

Ghi lại hai thứ: timestamp (2:47:33) và position (487219). Position chính xác hơn timestamp vì timestamp có thể trùng nhau.

Bước 2: Restore full backup

Giả sử bạn có backup từ 01:00 sáng (trước sự cố):

# Restore từ mysqldump
mysql -u root -p mydb < backup_20260401_0100.sql

# Hoặc nếu dùng Percona XtraBackup
xtrabackup --copy-back --target-dir=/backup/2026-04-01_01-00/

Một mẹo nhỏ khi dùng mysqldump: luôn thêm flag --master-data=2 (MySQL 5.x) hoặc --source-data=2 (MySQL 8.x) khi dump. Flag này ghi position binlog tại thời điểm backup vào đầu file SQL, tiết kiệm thời gian tìm kiếm sau này:

mysqldump --single-transaction --source-data=2 \
  -u root -p mydb > backup_20260401_0100.sql

# Đầu file sẽ có dòng:
-- CHANGE REPLICATION SOURCE TO SOURCE_LOG_FILE='mysql-bin.000023', SOURCE_LOG_POS=321847;

Bước 3: Replay Binary Log đến ngay trước sự cố

Phần này cần làm chính xác — sai position là mất thêm dữ liệu. Replay từ position 321847 (thời điểm backup) đến ngay trước position 487219 (câu DELETE nguy hiểm):

mysqlbinlog --no-defaults \
  --start-position=321847 \
  --stop-position=487219 \
  /var/log/mysql/mysql-bin.000023 | mysql -u root -p mydb

Sự cố trải dài qua nhiều file binlog? Liệt kê hết vào một lệnh:

mysqlbinlog --no-defaults \
  --start-position=321847 \
  /var/log/mysql/mysql-bin.000022 \
  /var/log/mysql/mysql-bin.000023 \
  --stop-position=487219 | mysql -u root -p mydb

Bước 4: Kiểm tra kết quả

-- Kiểm tra số lượng đơn hàng đã về
SELECT COUNT(*), MIN(created_at), MAX(created_at) FROM orders;

-- Đối chiếu với log nghiệp vụ nếu có
SELECT * FROM orders ORDER BY created_at DESC LIMIT 10;

Xử lý trường hợp muốn bỏ qua một câu lệnh cụ thể giữa chừng

Đôi khi bạn cần replay phần trước lỗi, bỏ qua lỗi, rồi replay tiếp phần sau. Ví dụ lỗi ở position 487219 đến 487391, còn sau đó vẫn có giao dịch bình thường:

# Phần trước lỗi
mysqlbinlog --no-defaults \
  --start-position=321847 \
  --stop-position=487219 \
  /var/log/mysql/mysql-bin.000023 | mysql -u root -p mydb

# Phần sau lỗi (bỏ qua đoạn 487219 → 487391)
mysqlbinlog --no-defaults \
  --start-position=487391 \
  /var/log/mysql/mysql-bin.000023 | mysql -u root -p mydb

Một số lưu ý thực tế từ production

  • Backup binlog ra nơi khác: Binlog nằm trên cùng server với MySQL. Ổ đĩa hỏng là mất cả hai. Dùng rsync hoặc mysqlbinlog --read-from-remote-server để stream binlog sang server khác theo thời gian thực.
  • Test PITR định kỳ: Backup không được test là backup không tồn tại. Mỗi tháng, chạy thử một lần PITR trên staging — chắc chắn quy trình hoạt động trước khi cần dùng thật.
  • Retention hợp lý: 7 ngày thường là đủ. Ngắn quá thì không kịp phát hiện lỗi, dài quá thì tốn dung lượng (database lớn sinh vài GB binlog mỗi ngày là bình thường). Monitor bằng SHOW BINARY LOGS.
  • GTID mode: Nếu dùng GTID replication, thêm --skip-gtids khi replay binlog để tránh xung đột transaction ID.
# Với GTID mode
mysqlbinlog --no-defaults --skip-gtids \
  --start-position=321847 \
  --stop-position=487219 \
  /var/log/mysql/mysql-bin.000023 | mysql -u root -p mydb

Kết luận

PITR không phải tính năng cao siêu. Đây là kỹ năng sống còn — bất kỳ ai cầm MySQL production đều phải nắm. Full backup là điểm xuất phát, Binary Log là từng bước đi sau đó. Hai thứ kết hợp: bạn có thể quay ngược về bất kỳ giây nào trước thảm họa.

Lần tới khi thiết lập MySQL production mới, việc đầu tiên sau khi cài xong: bật binlog, cấu hình backup với --source-data=2, và test thử một lần PITR. Đừng đợi đến 2 giờ sáng mới học.

Share:
Tags: