Bắt đầu ngay trong 5 phút
Mình mất gần 2 tiếng mới tìm ra nguyên nhân khi production DB mất 30 nghìn dòng trong bảng orders — không phải do bug code, mà do ai đó chạy nhầm câu DELETE không có WHERE. Từ hôm đó mình mới thật sự hiểu giá trị của binary log và công cụ mysqlbinlog.
Trước tiên, kiểm tra binary log đã bật chưa:
mysql -u root -p -e "SHOW VARIABLES LIKE 'log_bin';"Nếu kết quả trả về Value = ON, bạn đã sẵn sàng. Nếu là OFF, thêm các dòng sau vào file cấu hình MySQL rồi restart:
# /etc/mysql/mysql.conf.d/mysqld.cnf (Ubuntu/Debian)
# /etc/my.cnf.d/mysql-server.cnf (CentOS/RHEL)
[mysqld]
log_bin = /var/lib/mysql/mysql-bin
binlog_format = ROW
binlog_row_image = FULL
server_id = 1
binlog_expire_logs_seconds = 1209600 # 14 ngày
max_binlog_size = 100MXem danh sách file binlog hiện có:
mysql -u root -p -e "SHOW BINARY LOGS;"
# +------------------+-----------+-----------+
# | Log_name | File_size | Encrypted |
# +------------------+-----------+-----------+
# | mysql-bin.000001 | 524288 | No |
# | mysql-bin.000002 | 1048576 | No |
# +------------------+-----------+-----------+Đọc nội dung file binlog mới nhất:
mysqlbinlog /var/lib/mysql/mysql-bin.000002Chỉ vậy thôi — bạn đã có thể xem mọi thay đổi đã xảy ra trong database.
Hiểu binlog_format: Tại sao chọn ROW?
Có 3 chế độ ghi binlog và mình đã thử cả ba trước khi quyết định:
- STATEMENT: Ghi lại câu SQL gốc — nhỏ gọn nhưng rủi ro khi replay. Các hàm như
NOW(),UUID(), hayRAND()có thể trả về giá trị khác khi chạy lại. - ROW: Ghi từng dòng data thực tế thay đổi (before/after image). An toàn nhất, đây là lựa chọn của mình trên production.
- MIXED: Tự động chọn STATEMENT cho câu SQL đơn giản, chuyển sang ROW khi cần — compromise hợp lý cho môi trường đọc nhiều.
Database production của mình chạy MySQL 8.0 với khoảng 50GB data. Mình set binlog_format = ROW và binlog_row_image = FULL để capture đủ cả before/after image. Disk tốn hơn nhưng khi cần điều tra thì đầy đủ thông tin hơn nhiều.
Các lệnh mysqlbinlog thực dụng nhất
Lọc theo khoảng thời gian
Đây là use case phổ biến nhất — biết thời điểm sự cố, lọc ngay vào khoảng đó:
mysqlbinlog \
--start-datetime="2026-06-17 14:00:00" \
--stop-datetime="2026-06-17 14:30:00" \
/var/lib/mysql/mysql-bin.000005Lọc theo database cụ thể
mysqlbinlog --database=myapp_db /var/lib/mysql/mysql-bin.000005Đọc ROW format dạng human-readable
Binlog ROW format mặc định encode dữ liệu dạng base64 — không đọc được bằng mắt thường. Cần thêm hai flag:
mysqlbinlog --base64-output=DECODE-ROWS -v \
/var/lib/mysql/mysql-bin.000005Output sẽ hiện giá trị before/after từng dòng:
### UPDATE `myapp_db`.`orders`
### WHERE
### @1=1234 /* id */
### @2='pending' /* status */
### @3='2026-06-17 10:00:00' /* updated_at */
### SET
### @1=1234
### @2='completed'
### @3='2026-06-17 14:17:05'Đọc nhiều file binlog liên tiếp
mysqlbinlog --base64-output=DECODE-ROWS -v \
/var/lib/mysql/mysql-bin.000003 \
/var/lib/mysql/mysql-bin.000004 \
/var/lib/mysql/mysql-bin.000005Đọc binlog từ remote server
mysqlbinlog --read-from-remote-server \
--host=192.168.1.100 \
--user=repl_user \
--password \
--database=myapp_db \
mysql-bin.000005Ứng dụng thực tế: Điều tra sự cố và Point-in-Time Recovery
Tình huống 1 — Tìm ai xóa dữ liệu
Kịch bản: Bảng products mất 500 dòng vào khoảng 15:30. Luồng điều tra như sau:
# Bước 1: Xác định file binlog nào chứa khoảng giờ đó
mysql -u root -p -e "SHOW BINARY LOGS;"
# Bước 2: Xuất ra file text để grep thoải mái
mysqlbinlog --base64-output=DECODE-ROWS -v \
--start-datetime="2026-06-17 15:25:00" \
--stop-datetime="2026-06-17 15:35:00" \
/var/lib/mysql/mysql-bin.000005 > /tmp/incident_15h.sql
# Bước 3: Tìm DELETE trên bảng products
grep -i "DELETE.*products" /tmp/incident_15h.sql -A 5Kết hợp với SHOW PROCESSLIST log hoặc general query log nếu bật, bạn có thể trace được session nào đã thực thi lệnh đó.
Tình huống 2 — Point-in-Time Recovery (PITR)
Đây chính xác là những gì mình đã làm để khôi phục 30k dòng orders bị xóa nhầm. Flow gồm 2 bước:
- Restore backup gần nhất (backup lúc 03:00 sáng)
- Dùng
mysqlbinlogreplay binlog từ sau thời điểm backup đến ngay trước câu DELETE thảm họa
# Tìm position của câu DELETE để biết điểm dừng
mysqlbinlog --base64-output=DECODE-ROWS -v \
/var/lib/mysql/mysql-bin.000005 | grep -B 10 "DELETE FROM orders" | head -30
# Output sẽ có dạng: # at 1234567
# Đó là position cần stop
# Replay từ sau backup đến trước position đó
mysqlbinlog \
--start-datetime="2026-06-17 03:00:05" \
--stop-position=1234567 \
/var/lib/mysql/mysql-bin.000005 | mysql -u root -p myapp_dbMẹo quan trọng: Trước khi làm thao tác nguy hiểm (bulk delete, schema migration…), luôn chạy SHOW MASTER STATUS và ghi lại File + Position. Đó là điểm rollback nếu có sự cố.
mysql -u root -p -e "SHOW MASTER STATUS\G"
# *************************** 1. row ***************************
# File: mysql-bin.000005
# Position: 1189432
# Binlog_Do_DB:
# Binlog_Ignore_DB:Tình huống 3 — Kiểm toán hoạt động database theo ngày
# Xuất toàn bộ thay đổi trong ngày ra file
mysqlbinlog --base64-output=DECODE-ROWS -v \
--start-datetime="2026-06-17 00:00:00" \
--stop-datetime="2026-06-17 23:59:59" \
/var/lib/mysql/mysql-bin.000005 > audit_20260617.sql
# Đếm số lần UPDATE/DELETE/INSERT
echo "INSERT count: $(grep -c '^### INSERT' audit_20260617.sql)"
echo "UPDATE count: $(grep -c '^### UPDATE' audit_20260617.sql)"
echo "DELETE count: $(grep -c '^### DELETE' audit_20260617.sql)"Nâng cao: Automation và binlog encryption
Script alert khi có DELETE lớn
Mình viết script nhỏ này chạy qua cron mỗi giờ, gửi cảnh báo nếu phát hiện xóa quá nhiều dòng:
#!/bin/bash
# /opt/scripts/check_large_deletes.sh
MYSQL_PASS="your_password"
THRESHOLD=500
BINLOG_PATH="/var/lib/mysql"
CURRENT_FILE=$(mysql -u root -p"$MYSQL_PASS" -Nse "SELECT @@log_bin_basename;" 2>/dev/null)
LATEST=$(ls -t ${CURRENT_FILE}.* 2>/dev/null | head -1)
[ -z "$LATEST" ] && exit 0
DELETE_COUNT=$(mysqlbinlog --base64-output=DECODE-ROWS -v \
--start-datetime="$(date -d '1 hour ago' '+%Y-%m-%d %H:%M:%S')" \
"$LATEST" 2>/dev/null | grep -c '^### DELETE')
if [ "$DELETE_COUNT" -gt "$THRESHOLD" ]; then
echo "[ALERT] $DELETE_COUNT DELETE events trong 1 giờ qua trên $(hostname)" | \
mail -s "MySQL Binlog Alert" [email protected]
fiBinlog encryption trên MySQL 8.0.14+
Nếu bật binlog_encryption = ON, không thể đọc trực tiếp file binlog trên disk. Cần kết nối qua server:
mysqlbinlog --read-from-remote-server \
--host=127.0.0.1 \
--user=root \
--password \
--base64-output=DECODE-ROWS -v \
mysql-bin.000005Tips thực tế sau 6 tháng dùng trên production
- Đặt thời gian giữ binlog phù hợp: Mình để 14 ngày — đủ để điều tra mà không chiếm quá nhiều disk. Với 50GB data, binlog của mình vào khoảng 1-2GB/ngày tùy traffic.
- Lọc theo position thay vì đọc toàn bộ: Nếu file binlog lớn hơn 500MB, dùng
--start-positionvà--stop-positionsẽ nhanh hơn nhiều so với đọc hết rồi grep. - mysqlbinlog không ghi MySQL user: Binlog ghi những gì thay đổi, không ghi ai đã login. Để audit theo user cụ thể, cần bật thêm MySQL Audit Log Plugin hoặc general_log.
- Replay binlog trên môi trường test trước: Khi thực hiện PITR, luôn test trên bản sao database trước — replay binlog lên production mà không kiểm tra có thể tạo ra duplicate hoặc conflict.
- Kết hợp với
pt-query-digest: Tool này từ Percona Toolkit có thể đọc binlog và thống kê các pattern query — hữu ích cho audit report hàng tuần.
Sau 6 tháng vận hành database production MySQL 8.0 với khoảng 50GB data, mình xem binlog như “hộp đen” của máy bay — ít khi cần nhưng khi cần thì cực kỳ quan trọng. Đầu tư thời gian làm quen với mysqlbinlog từ bây giờ sẽ tiết kiệm cho bạn nhiều giờ điều tra stress khi sự cố thật sự xảy ra.
