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

دیتابیس سه گیگابایتی دارید، دستور mysqldump را می‌زنید و بعد از بیست دقیقه انتظار، ترمینال قطع می‌شود. فایل خروجی نصفه است، یا اصلاً خالی است. اگر روی SSH هستید پیام Connection to server closed می‌آید و اگر از پنل زده‌اید، معمولاً چیزی شبیه 504 Gateway Timeout می‌بینید. مشکل نه دیتابیس است و نه سرور؛ مشکل این است که دستور پیش‌فرض برای دیتابیس کوچک نوشته شده، نه برای چیزی که چند گیگابایت داده دارد.

چرا بکاپ دیتابیس بزرگ وسط کار قطع می‌شود

سه عامل جدا پشت این قطع شدن هستند و باید تفکیکشان کنید، چون راه‌حل هرکدام فرق دارد.

اول، تایم‌اوت لایه وب. اگر از phpMyAdmin یا یک اسکریپت PHP بکاپ می‌گیرید، max_execution_time معمولاً روی 30 یا 60 ثانیه است و max_input_time هم چیزی مشابه. دیتابیس شما ده دقیقه زمان می‌خواهد. نتیجه‌اش یک فایل ناقص است که هیچ خطایی هم در لاگ PHP نمی‌گذارد، چون اسکریپت خودش را موفق می‌داند.

دوم، تایم‌اوت شبکه. اگر از روی سیستم خودتان به MySQL راه دور وصل می‌شوید، net_read_timeout و net_write_timeout سمت سرور پیش‌فرض 30 و 60 ثانیه‌اند. یک کوئری سنگین که 90 ثانیه طول بکشد، اتصال را می‌بندد.

سوم، و آن‌که بیشتر از همه دست‌کم گرفته می‌شود: قفل جدول. mysqldump به‌صورت پیش‌فرض با --opt کار می‌کند که داخلش --lock-tables هست. روی InnoDB این قفل تقریباً بی‌فایده است ولی روی MyISAM کل جدول را قفل می‌کند و سایت شما در تمام مدت بکاپ پاسخ نمی‌دهد. اگر سایتتان پرترافیک است، کاربران خطای 500 می‌بینند در حالی که شما فقط داشتید بکاپ می‌گرفتید.

دستور درست mysqldump برای دیتابیس چند گیگابایتی

این دستوری است که من روی دیتابیس‌های 5 تا 20 گیگابایتی استفاده می‌کنم:

mysqldump --single-transaction --quick --skip-lock-tables \
  --routines --triggers --events \
  --default-character-set=utf8mb4 \
  -u dbuser -p dbname | gzip -1 > backup-$(date +%F).sql.gz

هر گزینه‌اش دلیلی دارد. --single-transaction روی InnoDB یک تراکنش با isolation level قابل تکرار باز می‌کند و بدون قفل کردن جدول‌ها، یک snapshot یکدست می‌دهد. --quick باعث می‌شود ردیف‌ها یکی‌یکی به خروجی نوشته شوند نه اینکه کل result set در حافظه سرور جمع شود؛ روی جدولی با ده میلیون ردیف این تفاوت بین موفقیت و OOM kill است. --skip-lock-tables همان قفل MyISAM را حذف می‌کند.

نکته‌ای که خیلی‌ها نمی‌دانند: --single-transaction فقط روی InnoDB معنا دارد. اگر جدول‌های MyISAM دارید، این گزینه به شما snapshot یکدست نمی‌دهد و ممکن است بکاپتان از نظر منطقی ناسازگار باشد. اول با SHOW TABLE STATUS نوع جدول‌ها را چک کنید.

درباره gzip -1 هم توضیح بدهم چون معمولاً -9 می‌گذارند. سطح 9 روی دیتابیس متنی حدود 15 تا 20 درصد فایل کوچک‌تری می‌دهد ولی CPU را چند برابر می‌کند و زمان بکاپ را طولانی‌تر. روی سروری که هم‌زمان به کاربر سرویس می‌دهد، -1 انتخاب عاقلانه‌تری است. اگر بکاپ شبانه و سرور خلوت است، -6 تعادل خوبی است.

اگر دیتابیس روی سرور دیگری است

وقتی از راه دور وصل می‌شوید، این دو خط را قبل از dump اجرا کنید:

SET GLOBAL net_read_timeout = 600;
SET GLOBAL net_write_timeout = 600;

و در سمت کلاینت هم --max-allowed-packet=512M را اضافه کنید اگر جدولی با BLOB بزرگ دارید. بدون این، خطای MySQL server has gone away می‌گیرید که پیام گمراه‌کننده‌ای است؛ مشکل واقعی بسته بزرگ‌تر از حد مجاز بوده، نه قطع شدن سرور.

بازیابی تکه‌ای؛ راهی که تایم‌اوت را دور می‌زند

بکاپ گرفتن نصف ماجراست. بازیابی یک فایل 8 گیگابایتی با mysql -u user -p db < backup.sql روی هاست اشتراکی معمولاً شکست می‌خورد، چون اجرای یک اسکریپت SQL طولانی از تایم‌اوت پنل رد می‌شود.

راه‌حل، تقسیم فایل است. اگر فایل gzip دارید، اول بازش کنید و بعد:

split -l 50000 backup.sql part_

این فایل را به قطعه‌های پنجاه هزار خطی می‌شکند. حالا هر قطعه را جدا وارد کنید:

for f in part_*; do mysql -u dbuser -p dbname < "$f" || break; done

مزیت پنهان این روش این است که وقتی قطعه هفتم خطا بدهد، می‌دانید مشکل کجاست و لازم نیست از صفر شروع کنید. با یک فایل یکپارچه، خطا در دقیقه چهلم یعنی دوباره از اول.

یک هشدار عملی: split بر اساس خط می‌برد و ممکن است وسط یک INSERT چندخطی ببرد. اگر dump شما با --extended-insert گرفته شده (که پیش‌فرض است)، هر ردیف معمولاً در یک خط است و مشکلی پیش نمی‌آید. ولی اگر با --skip-extended-insert گرفته‌اید، اول با --complete-insert دوباره بگیرید.

اینجا اشتباه می‌کنند

اشتباهی که واقعاً زیاد می‌بینم: بکاپ را با --single-transaction می‌گیرند، فایل سالم است، ولی موقع بازیابی روی دیتابیس مقصدی که از قبل جدول‌هایی با همان نام دارد، فقط mysql db < backup.sql می‌زنند. نتیجه این است که CREATE TABLE خطای Table already exists می‌دهد، اسکریپت ادامه می‌دهد، و در نهایت دیتابیسی دارید که نیمی از جدول‌هایش داده قدیمی دارد و نیمی داده جدید. سایت بالا می‌آید ولی اعداد غلط است و ممکن است هفته‌ها نفهمید.

علامتش هم دقیقاً همین است: بازیابی «موفق» گزارش می‌شود، هیچ خطای واضحی در خروجی نیست، ولی تعداد ردیف‌ها با انتظار شما نمی‌خواند. همیشه قبل از بازیابی، دیتابیس مقصد را خالی کنید یا از --add-drop-table در زمان dump استفاده کنید.

جایگزین‌ها؛ کجا mysqldump جواب نمی‌دهد

انتخاب من برای دیتابیس زیر 20 گیگابایت همیشه mysqldump است، چون فایل خروجی متنی است و می‌توانید یک جدول خاص را از داخلش بیرون بکشید. بالای این حجم، XtraBackup را ترجیح می‌دهم؛ بکاپ فیزیکی روی دیتابیس 100 گیگابایتی چند دقیقه طول می‌کشد نه چند ساعت. ولی شرطش این است که فضای دیسک آزاد کافی داشته باشید، چون XtraBackup عملاً یک کپی از فایل‌های داده می‌سازد.

اگر روی هاست لینوکس هستید و دسترسی root ندارید، عملاً به همان mysqldump محدود می‌شوید. در آن حالت، بکاپ را به قطعه‌های کوچک‌تر بشکنید و هر شب فقط جدول‌های پرتغییر را بگیرید، نه کل دیتابیس.

چند نکته که وقت شما را نجات می‌دهد

• قبل از هر بکاپ، حجم دیتابیس را با SELECT table_schema, ROUND(SUM(data_length+index_length)/1024/1024) AS mb FROM information_schema.tables GROUP BY table_schema; چک کنید. عدد واقعی را بدانید، نه حدس.
• بکاپ را روی همان سرور نگه ندارید. اگر دیسک پر شود، هم دیتابیس و هم بکاپ را از دست می‌دهید.
• یک بار در ماه بازیابی را روی یک دیتابیس تستی امتحان کنید. بکاپی که تست نشده، بکاپ نیست.
• اگر وردپرس دارید و بکاپ‌های زمان‌بندی‌شده‌تان اجرا نمی‌شوند، مشکل معمولاً کرون است نه دیتابیس؛ wp-cron وردپرس؛ چرا اجرا نمی‌شود و چطور با کرون واقعی جایگزینش کنیم را ببینید.

برای دستورهای بیشتر و نمونه‌های آماده، مستندات و پایگاه دانش سرورنت مرجع خوبی است. اگر می‌خواهید قبل از بکاپ مطمئن شوید سرور از بیرون سالم پاسخ می‌دهد، ابزارهای رایگان هم در همین سایت در دسترس‌اند.

پرسش‌های پرتکرار

چرا mysqldump روی دیتابیس بزرگ خیلی کند است؟

چون به‌صورت پیش‌فرض تک‌رشته‌ای کار می‌کند و کل داده را از سرور می‌خواند، به دیسک می‌نویسد و فشرده می‌کند. روی دیتابیس 10 گیگابایتی، این می‌تواند یک تا دو ساعت طول بکشد. با --quick مصرف حافظه را کم می‌کنید ولی سرعت را چند برابر نمی‌کند. برای سرعت واقعی باید به mydumper یا XtraBackup مهاجرت کنید.

آیا می‌توانم فقط یک جدول را بکاپ بگیرم؟

بله. نام جدول را بعد از نام دیتابیس بنویسید: mysqldump -u user -p dbname table_name > table.sql. این برای جدول‌های پرتغییر مثل جدول سفارش‌ها یا لاگ‌ها بسیار کاربردی است و حجم بکاپ روزانه را به‌شدت کم می‌کند.

خطای MySQL server has gone away موقع بکاپ یعنی چه؟

یعنی بسته‌ای که بین کلاینت و سرور رد و بدل شده از max_allowed_packet بزرگ‌تر بوده، یا اتصال به‌خاطر تایم‌اوت شبکه بسته شده. مقدار max_allowed_packet را روی سرور و کلاینت بالا ببرید و net_write_timeout را افزایش دهید. اگر جدول BLOB بزرگ دارید، این خطا تقریباً حتمی است مگر پارامترها را تنظیم کنید.

بکاپ gzip شده را چطور بدون باز کردن کامل بازیابی کنم؟

می‌توانید مستقیم pipe کنید: gunzip < backup.sql.gz | mysql -u user -p dbname. این روش فایل میانی روی دیسک نمی‌سازد و برای فضای محدود مناسب است. ولی اگر وسط کار خطا بدهد، تشخیص نقطه خطا سخت‌تر می‌شود؛ برای دیتابیس‌های بزرگ همان روش تکه‌ای مطمئن‌تر است.