Database production sập lúc 2 giờ sáng — bài học không muốn nhớ lại
Năm ngoái mình phải xử lý một sự cố khá nặng: table bị corrupt do quá trình ghi bị interrupt bởi power outage trên server. Điều đau lòng không phải là database bị lỗi — mà là backup cuối cùng của team chỉ lưu được schema, không có data. Ba tiếng đồng hồ đêm đó mình và lead dev ngồi recover từng bảng bằng tay từ log file.
Từ đó mình học được: backup MySQL không phải cứ chạy lệnh là xong. Phải hiểu mình đang backup cái gì, restore thế nào, và quan trọng nhất — phải test restore định kỳ.
Tại sao backup MySQL dễ sai hơn bạn nghĩ
Nhìn lại các incident mình từng xử lý, lỗi backup MySQL thường rơi vào đúng mấy cái này:
- Backup schema-only: Chạy mysqldump nhưng sai flag, chỉ dump structure mà không có data
- Không có consistency: Backup trong khi đang có write transaction → data không nhất quán giữa các bảng
- File backup bị corrupt: Disk đầy giữa chừng, process bị kill → file dump không hoàn chỉnh nhưng không ai phát hiện
- Không test restore: Backup chạy OK nhưng restore thì lỗi character set, foreign key, trigger…
Database production của mình khi đó là MySQL 8.0, khoảng 50GB data. Mình đã thử nhiều cách trước khi tìm ra workflow ổn định. Ban đầu dùng mysqldump thuần — mỗi lần backup mất gần 45 phút, và server bị chậm rõ rệt trong suốt thời gian đó.
Các phương pháp backup MySQL
1. mysqldump — Đơn giản, đủ dùng cho database nhỏ
Không cần cài thêm gì, có sẵn khi bạn cài MySQL. Phù hợp với database dưới 10GB hoặc môi trường dev/staging không đòi hỏi zero-downtime backup.
# Backup một database
mysqldump -u root -p mydb > mydb_backup_$(date +%Y%m%d_%H%M%S).sql
# Backup nhiều database cùng lúc
mysqldump -u root -p --databases mydb otherdb > multi_backup.sql
# Backup toàn bộ MySQL server
mysqldump -u root -p --all-databases > full_backup.sqlFlag không thể thiếu khi backup production:
mysqldump -u root -p \
--single-transaction \ # Đảm bảo consistency, không lock table với InnoDB
--routines \ # Include stored procedures, functions
--triggers \ # Include triggers
--events \ # Include scheduled events
--hex-blob \ # Encode binary data an toàn hơn
mydb > mydb_backup.sql--single-transaction là flag mình luôn bật trên production. Thay vì lock table, nó mở một transaction đọc nhất quán — các write transaction khác vẫn chạy bình thường trong lúc backup. Bỏ flag này trên database đang có write traffic thì dữ liệu backup sẽ không nhất quán giữa các bảng.
Restore từ dump file:
# Restore vào database đã tồn tại
mysql -u root -p mydb < mydb_backup.sql
# Tạo database mới rồi restore
mysql -u root -p -e "CREATE DATABASE mydb_restored CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;"
mysql -u root -p mydb_restored < mydb_backup.sql2. mysqlpump — Parallel backup, nhanh hơn đáng kể
MySQL 5.7.8 bổ sung mysqlpump với tính năng parallel threads — export nhiều table cùng lúc thay vì tuần tự. Cùng database 50GB đó, thời gian backup giảm từ 45 phút xuống còn 18 phút.
mysqlpump -u root -p \
--default-parallelism=4 \ # 4 threads chạy song song
--single-transaction \
mydb > mydb_pump_backup.sqlOutput có progress indicator — biết được đang chạy đến đâu thay vì ngồi chờ màn hình đứng im. Một lưu ý thực tế: mình test với 8 threads thì I/O disk bị saturation, kết quả lại chậm hơn 4 threads. Nên benchmark trước trên staging nếu chưa rõ disk throughput của server.
3. Percona XtraBackup — Hot backup cho production nghiêm túc
Database lớn mà không thể chấp nhận server chậm trong lúc backup? XtraBackup là câu trả lời. Thay vì export SQL như mysqldump, nó copy file InnoDB trực tiếp ở physical level — không lock table, không impact query, và hỗ trợ incremental backup để tiết kiệm dung lượng.
# Cài trên Ubuntu/Debian
apt install percona-xtrabackup-80
# Full backup
xtrabackup --backup \
--user=root \
--password=yourpassword \
--target-dir=/backup/mysql/full/$(date +%Y%m%d)
# Incremental backup (chỉ backup phần thay đổi từ lần trước)
xtrabackup --backup \
--user=root \
--password=yourpassword \
--target-dir=/backup/mysql/inc/$(date +%Y%m%d) \
--incremental-basedir=/backup/mysql/full/20250301Restore với XtraBackup cần 2 bước — bước prepare là bắt buộc, không thể bỏ qua:
# Bước 1: Prepare — apply logs để đảm bảo consistency
xtrabackup --prepare --target-dir=/backup/mysql/full/20250301
# Bước 2: Copy data vào MySQL datadir
systemctl stop mysql
xtrabackup --copy-back --target-dir=/backup/mysql/full/20250301
chown -R mysql:mysql /var/lib/mysql
systemctl start mysqlScript backup tự động — cái mình đang dùng thực tế
Script dưới đây chạy qua cron lúc 2 giờ sáng mỗi đêm. Backup từng database riêng thay vì dùng --all-databases — cách này restore từng phần dễ hơn nhiều khi cần. File được compress ngay, backup cũ hơn 7 ngày bị xóa tự động:
#!/bin/bash
# mysql_backup.sh
DB_USER="backup_user"
DB_PASS="$(cat /etc/mysql_backup_pass)" # Không hardcode password
BACKUP_DIR="/backup/mysql"
DATE=$(date +%Y%m%d_%H%M%S)
RETENTION_DAYS=7
LOG_FILE="/var/log/mysql_backup.log"
echo "[$DATE] Starting MySQL backup..." >> $LOG_FILE
# Backup từng database riêng (dễ restore hơn all-databases)
for DB in $(mysql -u$DB_USER -p$DB_PASS -e "SHOW DATABASES;" \
| grep -Ev "(Database|information_schema|performance_schema|sys)"); do
OUTPUT_FILE="$BACKUP_DIR/${DB}_${DATE}.sql.gz"
mysqldump -u$DB_USER -p$DB_PASS \
--single-transaction \
--routines \
--triggers \
"$DB" | gzip -9 > "$OUTPUT_FILE"
if [ $? -eq 0 ]; then
echo "[$DATE] OK: $DB → $OUTPUT_FILE" >> $LOG_FILE
else
echo "[$DATE] ERROR: Failed to backup $DB" >> $LOG_FILE
# Thêm alert Slack/email ở đây nếu cần
fi
done
# Xóa backup cũ hơn RETENTION_DAYS ngày
find $BACKUP_DIR -name "*.sql.gz" -mtime +$RETENTION_DAYS -delete
echo "[$DATE] Cleaned backups older than $RETENTION_DAYS days" >> $LOG_FILEThêm vào crontab để chạy lúc 2 giờ sáng mỗi ngày:
0 2 * * * /usr/local/bin/mysql_backup.shTest restore — bước bắt buộc mà hầu hết bỏ qua
Backup chưa được test restore = chưa thực sự có backup. Nghe có vẻ cực đoan, nhưng mình đã thấy tận mắt trường hợp backup chạy OK hàng tháng, đến khi restore thật thì lỗi encoding — hoặc dump file bị cắt không ai hay. Cứ mỗi tháng một lần, mình restore vào server staging và kiểm tra row count của các bảng quan trọng:
# Decompress và restore vào database test
gunzip -c /backup/mysql/mydb_20250301_020000.sql.gz | \
mysql -u root -p mydb_test
# Kiểm tra row count các bảng quan trọng
mysql -u root -p mydb_test -e "
SELECT 'users' AS tbl, COUNT(*) AS cnt FROM users
UNION ALL
SELECT 'orders', COUNT(*) FROM orders
UNION ALL
SELECT 'products', COUNT(*) FROM products;
"Chiến lược backup 3-2-1 kết hợp binlog
Sau vài sự cố kiểu đó, mình quyết định làm đàng hoàng hơn và áp dụng chiến lược 3-2-1 cho MySQL:
- 3 bản backup: Full weekly + incremental daily + binary log continuous
- 2 loại storage: Local disk và cloud (S3, Backblaze B2)
- 1 bản offsite: Tối thiểu 1 bản phải ở nơi khác server production
Upload backup lên S3 sau khi chạy xong script:
# Cần cài AWS CLI và cấu hình credentials
aws s3 cp /backup/mysql/mydb_${DATE}.sql.gz \
s3://your-bucket/mysql-backups/ \
--storage-class STANDARD_IA # Infrequent Access — rẻ hơn StandardBinary log (binlog) là thứ đã cứu mình trong sự cố đó. Với binlog enabled, có thể thực hiện point-in-time recovery — restore đến đúng thời điểm trước khi lỗi xảy ra:
# Bật binlog trong /etc/mysql/mysql.conf.d/mysqld.cnf
[mysqld]
log_bin = /var/log/mysql/mysql-bin.log
binlog_format = ROW
binlog_expire_logs_seconds = 1209600 # 14 ngày
# Point-in-time recovery: apply binlog từ sau backup đến thời điểm cần
mysqlbinlog \
--start-datetime="2025-03-01 02:00:00" \
--stop-datetime="2025-03-01 14:30:00" \
/var/log/mysql/mysql-bin.000123 | mysql -u root -p mydbSetup hiện tại của mình với database production 50GB: XtraBackup full backup mỗi cuối tuần, binlog cho recovery liên tục trong ngày, mysqldump cho backup daily của từng database quan trọng. Tất cả sync lên S3 sau khi chạy xong. Ba lớp bảo vệ — và quan trọng hơn, cả ba đều đã được test restore thực tế. Ngủ ngon hơn hồi chỉ có một cron job chạy âm thầm và không ai thèm check log.
