کالبدشکافی Binary Log در MySQL: از شاه‌کلید رپلیکیشن تا فاجعه دیسک در سرورهای Standalone

در معماری پایگاه‌داده‌های مبتنی بر MySQL و MariaDB، لاگ دودویی یا Binary Log (Binlog) یکی از حیاتی‌ترین اجزای سیستم ذخیره‌سازی و پایداری داده‌ها محسوب می‌شود. اما این ابزار قدرتمند مانند یک شمشیر دو لبه عمل می‌کند؛ اگر به درستی درک و پیکربندی نشود، می‌تواند پایگاه‌داده پروداکشن شما را با بحران‌های خاموش نظیر پر شدن ناگهانی دیسک (Disk Space Outage) و گلوگاه‌های مرگبار I/O روبه‌رو کند.

در این مقاله، معماری درونی Binlog، نقش آن در رپلیکیشن و ریکاوری، خطرات آن در دیتابیس‌های مستقل (Standalone) و مجموعه تنظیمات بهینه‌سازی عملیاتی را بررسی می‌کنیم.

۱. باینری لاگ (Binlog) دقیقاً چیست و چگونه کار می‌کند؟

بر خلاف Redo Log که یک لاگ فیزیکی با بافر چرخشی (Circular Buffer) ویژه موتور ذخیره‌سازی InnoDB جهت تضمین ویژگی‌های ACID و بازیابی پس از کرش (Crash Recovery) است، Binary Log در لایه سرور MySQL (بالاتر از Storage Engine) قرار دارد.

باینری لاگ تمامی تغییرات روی داده‌ها (DML نظیر INSERT، UPDATE، DELETE) و تغییرات ساختاری پایگاه‌داده (DDL نظیر CREATE TABLE، ALTER TABLE) را به صورت ترتیبی و رویدادمحور ثبت می‌کند.

فرآیند Two-Phase Commit (2PC)

برای جلوگیری از هرگونه عدم تطابق بین داده‌های ذخیره‌شده روی دیسک و لاگ‌های ارسالی به رپلیکاها، MySQL از مکانیزم XA Two-Phase Commit میان InnoDB و Binlog استفاده می‌کند:

1. Prepare Phase: تغییرات در Redo Log نوشته شده و وضعیت آن روی PREPARED قرار می‌گیرد.

2. Binlog Write & Fsync: تراکنش در باینری لاگ نوشته شده و با دستور fsync روی دیسک پایدار می‌شود.

3. Commit Phase: موتور InnoDB تراکنش را در Redo Log به حالت COMMITTED تغییر می‌دهد.

۲. نقش حیاتی Binlog در کلاسترهای رپلیکیشن (Replication)

در سناریوهای Master-Replica (Source-Replica)، باینری لاگ موتور محرک همگام‌سازی است:

1. Binlog Dump Thread: روی سرور سورس اجرا شده و با رسیدن هر رویداد جدید به باینری لاگ، آن را از طریق سوکت شبکه به رپلیکا ارسال می‌کند.

2. I/O Thread: روی سرور رپلیکا رویدادها را دریافت کرده و در فایلی محلی به نام Relay Log ذخیره می‌کند.

3. SQL Thread (یا Worker Threads در MTS): رویدادها را از Relay Log خوانده و روی داده‌های محلی رپلیکا اعمال می‌نماید.

فرمت‌های مختلف باینری لاگ

• STATEMENT: فقط متن کوئری SQL لاگ می‌شود. (حجم کم، اما خطرناک در توابع غیر قطعی مثل NOW()، UUID() یا RAND()).

• ROW: تغییرات واقعی ردیف‌ها به صورت بیتی ذخیره می‌شوند. (استاندارد پیشنهادی صنعت و الزامی برای Group Replication و ابزارهای CDC مانند Debezium).

• MIXED: ترکیبی هوشمند که پیش‌فرض Statement است و در موارد غیراستاندارد به Row تغییر وضعیت می‌دهد.

شناسه تراکنش جهانی (GTID)

با استفاده از GTID (Global Transaction Identifier)، هر تراکنش دارای یک امضای یکتا (Source_UUID:Transaction_ID) در سراسر کلاستر است. این ویژگی عملیات Failover و جابجایی رپلیکا بین نودها را بدون نیاز به آدرس‌دهی دستی فایل و موقعیت لاگ (binlog-file و binlog-pos) ممکن می‌سازد.

۳. خطرات و دردسرهای فعال بودن Binlog در دیتابیس‌های تک‌نود (Standalone)

بسیاری از معماران سیستم با این تصور که «چون از نسخه ۸ پیش‌فرض روشن است، پس نیازی به دستکاری ندارد»، پایگاه‌های داده منفرد (بدون رپلیکا) را رها می‌کنند. این موضوع ریسک‌های عملیاتی شدیدی به همراه دارد:

۱. پر شدن ناگهانی دیسک (Disk Exhaustion Disaster)

در دیتابیس‌های تک‌نود، اگر مقدار نگهداری لاگ‌ها (binlog_expire_logs_seconds) نامناسب باشد، به مرور زمان صدها فایل گیگابایتی ایجاد می‌شود. زمانی که دیسک به ۱۰۰٪ برسد:

• سرویس mysqld وارد حالت Read-Only شده یا کرش می‌کند.

• جداول خراب شده و بالا آمدن مجدد نیازمند فرآیند پر ریسک Crash Recovery خواهد بود.

۲. افت شدید توان عملیاتی (I/O Bottleneck & fsync Overhead)

تنظیم پیش‌فرض sync_binlog = 1 به ازای تک‌تک تراکنش‌های Commit شده یک درخواست fsync به سیستم‌فایل ارسال می‌کند. این کار دیسک (مخصوصاً ذخیره‌سازهای اشتراکی، HDD یا SSDهای با IOPS محدود) را به شدت اشباع کرده و Latency تراکنش‌ها را چند برابر می‌کند.

۳. سرریز لاگ موقت در تراکنش‌های حجیم (Bulk Transactions)

اجرای دستوراتی مانند UPDATE یا DELETE روی چند میلیون رکورد باعث پر شدن binlog_cache_size در RAM شده و MySQL را مجبور می‌کند داده‌های موقت لاگ را در دیسک بنویسد (Binlog_cache_disk_use) که سرعت اجرای تراکنش را به شدت کاهش می‌دهد.

۴. چه زمانی در سرورهای Standalone به باینری لاگ نیاز داریم؟

خاموش کردن کامل Binlog در سرورهای تک‌نود همیشه راهکار درستی نیست. شما در دو سناریوی حیاتی به آن نیاز دارید:

1. بازیابی نقطه در زمان (Point-In-Time Recovery - PITR):
   اگر ساعت ۱۲:۰۰ ظهر بکاپ کامل تهیه کرده باشید و ساعت ۱۶:۰۰ دیتابیس دچار خطای انسانی شود، با داشتن Binlog می‌توانید دیتابیس را دقیقاً به ثانیه قبل از رخداد خطا بازگردانید.

2. ابزارهای Change Data Capture (CDC):
   انتقال بلادرنگ تغییرات به Elasticsearch، Kafka یا ClickHouse از طریق ابزارهایی نظیر Debezium به باینری لاگ وابسته است.

نکته کلیدی: اگر به هیچ‌کدام از موارد بالا نیاز ندارید و بکاپ‌های روزانه برای شما کافی است، می‌توانید در دیتابیس Standalone باینری لاگ را به طور کامل با disable_log_bin غیرفعال کرده و بهره‌وری I/O را تا ۳۰٪ افزایش دهید.

۵. چک‌لیست و تنظیمات بهینه‌سازی در my.cnf

برای دستیابی به بیشترین پایداری و حداقل تاخیر در محیط پروداکشن، کانفیگ زیر پیشنهاد می‌شود:

[mysqld]
# -------------------------------------------------------------
# 1. فعال‌سازی و فرمت باینری لاگ
# -------------------------------------------------------------
server-id                          = 101
log_bin                            = /var/log/mysql/mysql-bin
binlog_format                      = ROW
binlog_row_image                   = MINIMAL      # فقط ستون‌های تغییر یافته ذخیره شوند تا حجم لاگ کاهش یابد

# -------------------------------------------------------------
# 2. مدیریت دیسک و چرخه حذف خودکار لاگ‌ها (بسیار حیاتی)
# -------------------------------------------------------------
max_binlog_size                    = 256M         # اندازه هر فایل لاگ (بین 256M تا 1G)
binlog_expire_logs_seconds         = 259200       # نگهداری دقیقاً به مدت ۳ روز (3 * 86400)

# -------------------------------------------------------------
# 3. بهینه‌سازی کارایی دیسک و Group Commit
# -------------------------------------------------------------
# در سرورهای با حساسیت حداکثری: 1
# در سیستم‌های نیازمند Throughput بالا با ریسک از دست رفتن چند میلی‌ثانیه: 0 یا 100
sync_binlog                        = 1

# فعال‌سازی Group Commit برای تجمیع تراکنش‌ها قبل از fsync
binlog_group_commit_sync_delay     = 50           # میکروثانیه تاخیر برای تجمیع تراکنش‌ها
binlog_group_commit_sync_no_delay_count = 10

# -------------------------------------------------------------
# 4. حافظه بافر باینری لاگ
# -------------------------------------------------------------
binlog_cache_size                  = 1M           # حافظه رم اختصاصی به ازای هر سشن برای نگهداری تراکنش
max_binlog_cache_size              = 1G           # حداکثر حجم یک تراکنش واحد در باینری لاگ

# -------------------------------------------------------------
# 5. فعال‌سازی GTID و بهینه‌سازی رپلیکیشن چندنخی
# -------------------------------------------------------------
gtid_mode                          = ON
enforce_gtid_consistency          = ON
binlog_transaction_dependency_tracking = WRITESET # رپلیکیشن موازی فوق‌سریع در رپلیکاها

۶. دستورات عملیاتی و نگهداری (Operations & Maintenance)

بررسی وضعیت و حجم باینری لاگ‌های موجود

-- مشاهده لیست تمام فایل‌های باینری لاگ و حجم آن‌ها
SHOW BINARY LOGS;

-- مشاهده فایل لاگ جاری و آخرین موقعیت نوشته شده
SHOW MASTER STATUS;

مانیتورینگ وضعیت بافر حافظه و مصرف دیسک موقت

SHOW GLOBAL STATUS LIKE 'Binlog_cache%';

اگر مقدار Binlog_cache_disk_use نسبت به Binlog_cache_use بالا باشد، باید مقدار binlog_cache_size را در فایل تنظیمات افزایش دهید.

پاکسازی دستی و ایمن باینری لاگ‌ها

هرگز فایل‌های لاگ را مستقیماً با دستور rm از لینوکس حذف نکنید! این کار شاخص (.index) را خراب کرده و سرور را از کار می‌اندازد. همیشه از دستورات داخلی MySQL استفاده کنید:

-- حذف تمام لاگ‌های قدیمی‌تر از ۷ روز قبل
PURGE BINARY LOGS BEFORE NOW() - INTERVAL 7 DAY;

-- حذف لاگ‌ها تا قبل از یک فایل مشخص
PURGE BINARY LOGS TO 'mysql-bin.000142';

۷. جدول خلاصه پارامترها

پارامتر مقدار پیشنهادی کاربرد اصلی binlog_format ROW تضمین یکپارچگی داده‌ها در رپلیکیشن و CDC binlog_row_image MINIMAL کاهش ۴۰ الی ۶۰ درصدی حجم لاگ‌های حجیم binlog_expire_logs_seconds 259200 (۳ روز) جلوگیری خودکار از پر شدن دیسک سرور max_binlog_size 256M یا 512M جلوگیری از ایجاد فایل‌های لاگ غول‌آسا sync_binlog 1 (یا 100) مدیریت توازن میان دوام داده (Durability) و سرعت نوشتن binlog_transaction_dependency_tracking WRITESET تسریع چند برابری پردازش در نودهای Replica

نتیجه‌گیری

باینری لاگ ستون فقرات پایداری، مقیاس‌پذیری و پشتیبان‌گیری در دنیای MySQL است. در سرورهای تک‌نود (Standalone)، اگر نیاز به PITR یا CDC ندارید، غیرفعال‌سازی آن بار بزرگی از دوش ذخیره‌ساز برمی‌دارد؛ اما در صورت نیاز به فعال بودن آن، تعیین سقف نگهداری (binlog_expire_logs_seconds)، مدیریت اندازه فایل‌ها و بهینه‌سازی binlog_row_image از الزامات انکارناپذیر یک زیرساخت پایدار خواهد بود.