binlog و بازیابی لحظه‌ای (Point-in-Time Recovery)

ساعت ۱۴:۳۷ کسی جدول سفارش‌ها را پاک کرد

بکاپ کامل ساعت ۲ بامداد گرفته شده. ساعت ۱۴:۳۷ یک نفر در DBeaver یک DELETE بدون WHERE روی جدول orders زده است. اگر فقط بکاپ شبانه را برگردانید، دوازده ساعت سفارش از دست می‌رود. binlog همه‌ی تغییرات بعد از بکاپ را نگه داشته؛ کافی است بکاپ را برگردانید و binlog را تا درست قبل از دستور مخرب دوباره اجرا کنید.

تنظیم binlog

در MySQL 8 binlog به‌طور پیش‌فرض روشن و با فرمت ROW است. تنظیمات توصیه‌شده:

[mysqld]
server_id                  = 1
log_bin                    = /var/lib/mysql/binlog
binlog_format              = ROW
binlog_expire_logs_seconds = 1209600   # 14 روز
sync_binlog                = 1

SHOW BINARY LOGS;
SHOW BINARY LOG STATUS;    -- 8.4؛ در 8.0: SHOW MASTER STATUS
FLUSH BINARY LOGS;         -- بستن فایل فعلی و شروع فایل تازه
PURGE BINARY LOGS BEFORE NOW() - INTERVAL 14 DAY;

بازیابی قدم‌به‌قدم

# 1) مختصات binlog در لحظه‌ی بکاپ (از --source-data=2)
zcat /backup/carpet_factory_2026-10-01.sql.gz | head -60 | grep -m1 'LOG_POS'
# -- CHANGE REPLICATION SOURCE TO SOURCE_LOG_FILE='binlog.000412', SOURCE_LOG_POS=1543;

# 2) پیدا کردن موقعیت دستور مخرب
mysqlbinlog --base64-output=DECODE-ROWS -vv \
  --start-datetime='2026-10-01 14:30:00' /var/lib/mysql/binlog.000415 \
  | grep -n -B 30 '### DELETE FROM `carpet_factory`.`orders`' | grep '# at'

# 3) بازیابی بکاپ کامل روی یک سرور جداگانه
gunzip < /backup/carpet_factory_2026-10-01.sql.gz | mysql --login-path=admin

# 4) اجرای binlogها تا قبل از دستور مخرب، در یک فراخوانی
mysqlbinlog --start-position=1543 --stop-position=88213 \
  binlog.000412 binlog.000413 binlog.000414 binlog.000415 \
  | mysql --login-path=admin

بهتر است بازیابی را روی سرور جداگانه انجام دهید، داده‌ی سالم را بررسی کنید و سپس فقط جدول آسیب‌دیده را به سرور اصلی برگردانید؛ این‌طور سفارش‌هایی که بعد از ۱۴:۳۷ ثبت شده‌اند هم حفظ می‌شوند.

راه دوم با GTID

اگر GTID روشن است، می‌توانید به‌جای توقف، فقط همان یک تراکنش را کنار بگذارید و بقیه را اجرا کنید: mysqlbinlog --exclude-gtids='3e11fa47-...:9921' .... شناسه‌ی GTID در خروجی مرحله‌ی ۲ کنار SET @@SESSION.GTID_NEXT دیده می‌شود.

نکته‌هایی که کمتر کسی می‌داند

• --start-position فقط به اولین فایل و --stop-position فقط به آخرین فایل فهرست اعمال می‌شود؛ ترتیب فایل‌ها را درست بدهید.
• --stop-datetime با منطقه‌ی زمانی ماشینی تفسیر می‌شود که mysqlbinlog روی آن اجرا شده؛ اختلاف ساعت تهران و UTC یعنی سه ساعت و نیم داده‌ی اضافه یا کم.
• همه‌ی فایل‌ها را در یک فراخوانی mysqlbinlog بدهید؛ اجرای جداگانه‌ی هر فایل جدول‌های موقت بین فایل‌ها را از دست می‌دهد.
• binlog_expire_logs_seconds باید از فاصله‌ی دو بکاپ کامل بیشتر باشد؛ وگرنه بین آخرین بکاپ و قدیمی‌ترین binlog شکاف می‌افتد و PITR ممکن نیست.
• binlog روی همان دیسک داده، با خرابی دیسک از بین می‌رود. mysqlbinlog --read-from-remote-server --raw --stop-never روی سرور دیگری binlogها را لحظه‌به‌لحظه کپی می‌کند.
• اگر binlogها را روی همان سروری اجرا کنید که GTIDهایشان از قبل در gtid_executed هست، تراکنش‌ها بی‌صدا رد می‌شوند؛ در آن حالت --skip-gtids لازم است.