Posted inMySQL

Hướng dẫn Point-in-Time Recovery (PITR) MySQL: Khôi phục dữ liệu chính xác đến từng giây

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

1. Cơn ác mộng DROP TABLE lúc 2 giờ chiều

Chiều thứ Sáu, 14:15. Bạn vừa mở cốc cà phê thì kênh Slack thông báo lỗi réo dồn dập. Một bạn junior dev định dọn database staging nhưng lại kết nối nhầm terminal production. Lệnh chạy xong trong 0.2 giây:

DROP TABLE orders; -- Thực thi lúc 14:15:23

Bảng orders với hơn 1.2 triệu bản ghi biến mất. Cổng thanh toán báo lỗi hàng loạt. Khách không thể checkout.

Bản Full Backup gần nhất của hệ thống hoàn thành lúc 00:00 sáng. Nếu restore ngay file backup đó, bạn sẽ xóa sạch hơn 14 tiếng giao dịch. Khoảng 8.500 đơn hàng phát sinh từ nửa đêm đến đầu giờ chiều sẽ bốc hơi hoàn toàn. Bộ phận kinh doanh và kế toán chắc chắn không chấp nhận điều này.

Bạn không thể rollback theo cách thông thường nếu chưa thiết lập Delayed Replication trong MySQL để làm phao cứu sinh. Mục tiêu duy nhất lúc này: đưa database về trạng thái tại mốc 14:15:22 — đúng 1 giây trước khi lệnh drop được commit.

2. Tại sao chỉ dựa vào Full Backup là cầm chắc rủi ro?

Nhiều team chỉ setup cron job mysqldump mỗi đêm rồi yên tâm ra về. Tuy nhiên, cách làm này có một lỗ hổng chí mạng:

  • Full Backup chỉ chụp lại trạng thái database đúng tại thời điểm $T_0$ (00:00).
  • Toàn bộ lệnh INSERT, UPDATE, DELETE phát sinh từ $T_0$ đến thời điểm gặp nạn $T_1$ (14:15) đều không nằm trong file .sql backup đó.

Vậy dữ liệu trong 14 tiếng đó đi đâu? Nếu server có bật Binary Log (binlog) — cơ chế ghi log quen thuộc trong replication và Change Data Capture (CDC) — MySQL sẽ ghi lại mọi thay đổi cấu trúc và dữ liệu vào các file log nhị phân. Mỗi transaction commit thành công đều có timestamp và vị trí byte (position) rõ ràng.

Sở dĩ nhiều sự cố biến thành thảm họa thường do hai nguyên nhân:

  1. Server chưa kích hoạt binlog, hoặc đặt thời gian dọn log tự động (expire logs) quá ngắn dưới 24 giờ.
  2. Đội ngũ vận hành chưa từng drill (diễn tập) quy trình Point-in-Time Recovery (PITR). Đến khi có biến, mọi người lúng túng gõ nhầm lệnh đè dữ liệu.

3. So sánh 3 phương án xử lý sự cố

Khi bảng quan trọng bị drop, bạn thường có 3 lựa chọn trên bàn xử lý:

Cách 1: Restore đè bản Full Backup gần nhất

Cách này đơn giản nhất. Bạn import lại file dump 00:00 sáng rồi cho app chạy tiếp.

  • Ưu điểm: Nhanh gọn. Chỉ cần một lệnh mysql < backup.sql là xong trong 20-30 phút.
  • Nhược điểm: Mất trắng 14 giờ dữ liệu mới. Chỉ chấp nhận được trên môi trường dev/staging hoặc hệ thống nội bộ ít cập nhật.

Cách 2: Đọc access log để cào lại dữ liệu thủ công

Sau khi restore bản 00:00, team backend ngồi parse log từ Nginx và microservices để trích xuất payload, sau đó chạy script bù đơn.

  • Ưu điểm: Vớt vát được một phần đơn hàng có ghi nhận trong log.
  • Nhược điểm: Tốn nhiều ngày công. Dữ liệu rất dễ lệch trạng thái do webhook thứ ba, voucher giảm giá và race condition không được tái hiện chuẩn xác.

Cách 3: Khôi phục theo mốc thời gian (PITR) bằng Full Backup + Binary Log

Restore bản Full Backup lúc 00:00 ra một instance mới. Tiếp theo, dùng công cụ mysqlbinlog để phát lại (replay) toàn bộ transaction từ 00:00:01 đến 14:15:22, cố tình ngắt trước câu lệnh drop bảng.

  • Ưu điểm: Giữ trọn vẹn 100% dữ liệu hợp lệ đến đúng mili-giây trước thảm họa.
  • Nhược điểm: Đòi hỏi kỹ sư nắm vững position trong binlog và thao tác cẩn trọng để tránh replay sót file.

4. Quy trình PITR 6 bước chuẩn production

Các bước dưới đây áp dụng trực tiếp cho MySQL 8.0/5.7 đang chạy trên Linux (nếu bạn sử dụng PostgreSQL, hãy tham khảo giải pháp PostgreSQL Point-in-Time Recovery (PITR) tương đương).

Bước 1: Kiểm tra cấu hình Binary Log

Kiểm tra file cấu hình /etc/mysql/mysql.conf.d/mysqld.cnf (hoặc my.cnf). Server cần có các dòng sau:

[mysqld]
server-id = 1
log_bin = /var/log/mysql/mysql-bin.log
binlog_format = ROW
binlog_expire_logs_seconds = 604800 # Lưu log 7 ngày (trên MySQL 8.0)

Vào terminal MySQL gõ lệnh kiểm tra nhanh:

SHOW VARIABLES LIKE 'log_bin';
-- Trả về 'ON' là đạt yêu cầu

Bước 2: Tạo Full Backup có lưu tọa độ Binlog

Khi chạy mysqldump định kỳ hàng ngày, bắt buộc phải dùng cờ --source-data=2 (MySQL 8.0.26+) hoặc --master-data=2 (bản cũ hơn). Cờ này chụp lại đúng vị trí binlog tại thời điểm dump mà không khóa bảng quá lâu nếu đi kèm --single-transaction:

mysqldump -u root -p --single-transaction --source-data=2 --databases production_db > full_backup_00h.sql

Kiểm tra 25 dòng đầu của file dump để lấy tọa độ xuất phát:

head -n 25 full_backup_00h.sql | grep "CHANGE REPLICATION"

Output mẫu:

-- CHANGE REPLICATION SOURCE TO SOURCE_LOG_FILE='mysql-bin.000005', SOURCE_LOG_POS=157;

Dòng này xác nhận: Toàn bộ dữ liệu đến vị trí byte 157 của file mysql-bin.000005 đã nằm trọn trong file dump.

Bước 3: Cô lập database và cắt file Binlog

Đừng để traffic ghi tiếp vào database lỗi. Chuyển database sang chế độ Read-Only hoặc điều hướng tải từ gateway/ProxySQL sang trang bảo trì ngay lập tức:

SET GLOBAL read_only = ON;

Sau đó, chạy lệnh xoay vòng log:

FLUSH LOGS;

MySQL sẽ đóng file mysql-bin.000005 lại và mở file mới mysql-bin.000006. File log chứa sự cố sẽ giữ nguyên kích thước, không bị dữ liệu mới làm nhiễu.

Bước 4: Định vị chính xác vị trí (Position) của lệnh DROP TABLE

Dùng công cụ mysqlbinlog đọc nội dung file log và lọc ra lệnh drop:

mysqlbinlog -v /var/log/mysql/mysql-bin.000005 | grep -B 3 -A 3 -i "DROP TABLE"

Output hiển thị trên terminal:

# at 742180
#241104 14:15:23 server id 1  end_log_pos 742265 CRC32 0x9f5a7d21   Query   thread_id=45    exec_time=0 error_code=0
SET TIMESTAMP=1730729723/*!*/;
DROP TABLE `orders` /* xid=1284 */
/*!*/;

Từ output này, bạn xác định được 2 mốc then chốt:

  • Lệnh DROP TABLE bắt đầu tại position: 742180.
  • Tọa độ an toàn cuối cùng là vị trí ngay trước 742180.

Nhiệm vụ: Replay toàn bộ event từ position 157 đến trước position 742180.

Bước 5: Trích xuất các Transaction sạch ra file SQL

Lọc các transaction hợp lệ từ file binlog ra một file SQL tạm bằng tham số --start-position và --stop-position:

mysqlbinlog --start-position=157 --stop-position=742180 \
  --database=production_db \
  /var/log/mysql/mysql-bin.000005 > /tmp/replay_transactions.sql

Lệnh trên đã bỏ qua hoàn toàn câu lệnh DROP TABLE ở vị trí 742180. File /tmp/replay_transactions.sql chỉ chứa các transaction mua hàng, cập nhật giỏ hàng từ 00:00:01 đến 14:15:22.

Bước 6: Khôi phục trên server dự phòng hoặc database tạm

Tuyệt đối không restore thẳng vào instance production đang bị lỗi. Hãy dựng một server staging riêng hoặc tạo schema tạm production_recovery để thao tác:

1. Import bản Full Backup 00:00:

mysql -u root -p production_recovery < full_backup_00h.sql

2. Chạy tiếp các transaction bù đắp từ binlog:

mysql -u root -p production_recovery < /tmp/replay_transactions.sql

3. Đối soát lại số lượng bản ghi:

SELECT COUNT(*) AS total_orders FROM production_recovery.orders;
SELECT MAX(created_at) AS latest_order FROM production_recovery.orders;

Kết quả trả về mốc 2024-11-04 14:15:21. Toàn bộ 8.500 đơn hàng phát sinh trong buổi sáng đã trở lại đầy đủ, không thiếu một dòng.

5. Ba lưu ý xương máu khi vận hành PITR

  • Dùng Position thay vì Datetime: Tham số --stop-datetime dễ lệch do timezone giữa server (UTC) và máy trạm (GMT+7). Chỉ dùng --stop-position để đảm bảo độ chính xác tuyệt đối.
  • Tắt binlog khi import: Khi import lượng dữ liệu lớn vào recovery server, hãy chạy SET sql_log_bin = 0; trong session import. Thao tác này giúp tiết kiệm I/O đĩa và tăng tốc độ restore gấp 2-3 lần.
  • Đẩy binlog ra ngoài server chính: Nếu ổ cứng SSD của database server chết vật lý, binlog nằm cục bộ sẽ đi theo ổ cứng. Cần dùng script rsync hoặc công cụ như Percona XtraBackup đẩy binlog lên S3/MinIO mỗi 15-30 phút.
Share:
Tags: