بازیابی MySQL تا لحظهٔ دلخواه با binlog

۱فعال‌سازی لاگ باینری

در MySQL 8 لاگ باینری به‌صورت پیش‌فرض روشن است. در MariaDB و نسخه‌های قدیمی‌تر باید آن را در فایل تنظیمات فعال کنید و سرویس را ریستارت کنید.

# /etc/mysql/mysql.conf.d/mysqld.cnf
[mysqld]
server-id        = 1
log_bin          = /var/log/mysql/mysql-bin.log
binlog_expire_logs_seconds = 1209600   # ۱۴ روز
max_binlog_size  = 100M
binlog_format    = ROW

۲بررسی فعال بودن

با دستور زیر باید log_bin برابر ON باشد.

sudo systemctl restart mysql
mysql -e "SHOW VARIABLES LIKE 'log_bin'; SHOW BINARY LOGS;"

۳بکاپ کامل همراه با موقعیت binlog

گزینهٔ --source-data=2 (در نسخه‌های قدیمی --master-data=2) نام و موقعیت binlog را به‌صورت کامنت در ابتدای بکاپ می‌نویسد.

mysqldump --single-transaction --routines --events --source-data=2 appdb \
  | gzip > appdb_full.sql.gz

zcat appdb_full.sql.gz | grep -m1 'CHANGE'

۴پیدا کردن لحظهٔ خطا

فرض کنید ساعت 10:32:15 جدولی حذف شد. باید موقعیت دقیق آن دستور را در binlog پیدا کنید.

sudo mysqlbinlog --base64-output=decode-rows -v \
  --start-datetime="2026-09-29 10:30:00" \
  --stop-datetime="2026-09-29 10:35:00" \
  /var/log/mysql/mysql-bin.000012 | grep -n -i -B5 'DROP TABLE'

۵ریستور بکاپ کامل

ابتدا بکاپ شبانه را روی دیتابیس (ترجیحاً روی یک سرور موقت) ریستور کنید.

mysql -e "CREATE DATABASE appdb_recovery"
zcat appdb_full.sql.gz | mysql appdb_recovery

برای دیتابیس اصلی این کار را بدون فکر انجام ندهید؛ ابتدا روی نسخهٔ موقت امتحان کنید.

۶بازپخش binlog تا قبل از خطا

از موقعیتی که در بکاپ ثبت شده تا درست قبل از دستور اشتباه، تغییرات را دوباره اجرا کنید.

sudo mysqlbinlog \
  --start-position=157 \
  --stop-datetime="2026-09-29 10:32:14" \
  /var/log/mysql/mysql-bin.000012 | mysql appdb_recovery

start-position را از خط CHANGE ... MASTER_LOG_POS بکاپ بردارید و اگر چند فایل binlog دارید همهٔ آن‌ها را به‌ترتیب بدهید.

۷بررسی و انتقال داده‌های بازیابی‌شده

داده‌های دیتابیس بازیابی‌شده را بررسی کنید و جدول‌های لازم را به دیتابیس اصلی برگردانید (مثلاً با mysqldump یک جدول).

mysqldump appdb_recovery orders | mysql appdb