Why mysqldump Fails Large MySQL Databases & How to Fix It

بۆ دەیان ساڵە، mysqldump وەک چەقۆی سویسری بێ ڕکابەر بۆ کۆپیی یەدەگی (backup) بنکەدراوەی MySQL ناسراوە. ئەم ئامرازە لە هەموو شوێنێک هەیە، بەکارهێنانی ئاسانە، و لەگەڵ هەموو دابەشکردنەکانی MySQL و MariaDB بە شێوەیەکی پێشوەختە دامەزراوە. بۆ بنکەدراوە بچووک و مامناوەندەکان، ئاستێکی زۆر باش پێشکەش دەکات.

سەرەڕای ئەوەش، کاتێک ڕێکخراوەکان گەشە دەکەن و قەبارەی داتاکان دەگاتە 100GB، 500GB، یان چەندین تێرابایت، پشت بەستن بە mysqldump لە باشترین پراکتیزەوە دەگۆڕێت بۆ لاوازییەکی تەلارسازیی مەترسیدار. ئەگەر تۆ بەڕێوەبەری بنکەدراوە (DBA) یان ئەندازیاری DevOps بیت کە بنکەدراوەی گەورەی بەرهەمهێنان (production) بەڕێوەدەبەیت، ئەوا ئەگەری زۆرە تووشی شکستی بێدەنگ، دابەزینی ئاستی بەرهەمهێنان، و کاتی گەڕاندنەوەی (RTO) قبوڵنەکراو بووبیت کە پەیوەستن بە کۆپییە لۆژیکییەکانەوە.

لەو وتارەدا، ئێمە سنووردارێتییە تەلارسازییەکانی mysqldump شیدەکەینەوە، دەبینین بۆچی لە قەبارەی گەورەدا شکست دەهێنێت، و بە وردی باس لەوە دەکەین چۆن ستراتیژییەکانی کۆپیی یەدەگی فیزیکی بە ئاستی کۆمپانیاکان (enterprise-grade) جێبەجێ بکەیت بۆ پاراستنی داتا گرنگەکانت.

سنووردارێتییە تەلارسازییەکانی mysqldump

بۆ تێگەیشتن لەوەی بۆچی mysqldump لە قەبارەی گەورەدا شکست دەهێنێت، دەبێت بزانین لە پشت پەردەوە چۆن کار دەکات. mysqldump کۆپیی یەدەگی لۆژیکی ئەنجام دەدات. ئەو پرسیار (query) ئاراستەی بزوێنەری بنکەدراوەکە دەکات، داتا دەخوێنێتەوە، و دەیگۆڕێت بۆ زنجیرەیەک لە فەرمانەکانی SQL (بە پلەی یەکەم CREATE TABLE و INSERT INTO).

هەرچەندە ئەمە فایلێکی زۆر گواستراوە و خوێندنەوەی بۆ مرۆڤ ئاسان دروست دەکات، بەڵام لە ژینگەکانی خێرایی بەرزدا دەبێتە هۆی دروستبوونی تەنگەژەی توند.

1. تەنگەژەی تاک-ڕێچکەیی (Single-Threaded)

بەپێی دیزاینەکەی، mysqldump کردارێکی تاک-ڕێچکەییە. یەک خشتە لە یەک کاتدا، ڕیز بە ڕیز پرۆسە دەکات. لە کاتێکدا ڕەقەکاڵای مۆدێرن خاوەنی دەیان ناوکی CPU و کۆگای NVMe-یە کە توانای گواستنەوەی چەندین گێگابایت لە چرکەیەکدا هەیە، mysqldump تەنها بەشێکی کەمی ئەم سەرچاوانە بەکاردەهێنێت.

تەنانەت کاتێک ئاڵای ستاندارد بۆ خشتەکانی InnoDB بەکاردەهێنیت:

mysqldump -u root -p --single-transaction --routines --triggers --events --quick production_db > backup.sql

ئاڵای --quick ناچاری mysqldump دەکات کە ڕیزەکان یەک بە یەک بهێنێت لەبری ئەوەی هەموو خشتەکە لە بیرگەدا (memory) هەڵبگرێت، ئەمەش ڕێگری لە هەڵەکانی Out of Memory (OOM) لە لایەنی کڕیار دەکات. بەڵام، سروشتی تاک-ڕێچکەیی واتە بنکەدراوەیەکی 500GB ڕەنگە 10 بۆ 15 کاتژمێری پێ بچێت بۆ کۆپیکردن، ئەمەش کاریگەرییەکی خراپ لەسەر ئامانجی خاڵی گەڕاندنەوەت (RPO) دادەنێت.

2. پیسبوونی حەوزی بیرگەی InnoDB (Buffer Pool Pollution)

کاتێک mysqldump هەموو ڕیزێکی هەموو خشتەیەک دەخوێنێتەوە، بزوێنەری MySQL ناچار دەکات ئەو داتایە لە دیسکەوە بار بکات بۆ ناو حەوزی بیرگەی InnoDB. لە ژینگەی بەرهەمهێناندا، حەوزی بیرگەکەت بە وردی پڕکراوە لەو داتایانەی کە زۆر بەکاردێن (hot data).

کۆپییەکی لۆژیکی زەبەلاح حەوزی بیرگەکە پاک دەکاتەوە، و ئەو ئیندێکس و لاپەڕە داتایانەی کە زۆر بەکاردێن دەردەکات بۆ ئەوەی شوێن بۆ داتا ساردەکان (کە کۆپی دەکرێن) بکاتەوە. ئەمەش دەبێتە هۆی بەرزبوونەوەیەکی لەناکاو و گەورە لە خوێندنەوە و نووسینی دیسک (I/O) چونکە پرسیارەکانی بەرهەمهێنان ناچار دەبن لە دیسکەوە بخوێننەوە، ئەمەش دەبێتە هۆی دواکەوتنی توندی ئەپڵیکەیشنەکە.

3. قفڵەکانی میتاداتا و ناکۆکییەکانی DDL

بۆ پاراستنی یەکگرتوویی، بەڕێوەبەرانی بنکەدراوە پشت بە ئاڵای --single-transaction دەبەستن، کە ئاستی جیاکردنەوەی مامەڵەکان دەکات بە REPEATABLE READ و مامەڵەیەک پێش کۆپیکردنی داتا دەستپێدەکات.

هەرچەندە ئەمە ڕێگری لە قفڵی خوێندنەوەی ئاستی خشتە دەکات (FLUSH TABLES WITH READ LOCK)، بەڵام بەرگری لە گۆڕانکارییەکانی زمانی پێناسەی داتا (DDL) ناکات. ئەگەر فەرمانێکی ALTER TABLE، DROP TABLE، یان TRUNCATE TABLE لەسەر خشتەیەک جێبەجێ بکرێت لە کاتێکدا mysqldump کار دەکات، کۆپییەکە بە هەڵەی table definition has changed, please retry transaction شکست دەهێنێت. لە ژینگەکانی CI/CD کە کۆچکردنی سکێما (schema migrations) زۆرە، ئەمە دەبێتە هۆی شکستی بەردەوامی کۆپیی یەدەگ.

4. کابووسی RTO: کاتەکانی گەڕاندنەوە

گەورەترین شکستی mysqldump نەک لە کاتی کۆپیکردن، بەڵکو لە کاتی گەڕاندنەوەدا دەردەکەوێت.

گەڕاندنەوەی کۆپییەکی لۆژیکی پێویستی بەوەیە بزوێنەری MySQL ملیۆنان فەرمانی INSERT شیکار و جێبەجێ بکات. بۆ هەر ڕیزێک کە زیاد دەکرێت، MySQL دەبێت:
* مەرجەکان بپشکنێت (کلیلە بیانییەکان، کلیلە ناوازەکان).
* ئیندێکسە لاوەکییەکان لە کاتی خۆیدا دروست بکاتەوە.
* لە InnoDB redo log بنووسێت.
* بۆ binlog بنووسێت (ئەگەر چالاک کرابێت).

گەڕاندنەوەی بنکەدراوەیەکی 1TB لە کۆپییەکی لۆژیکییەوە ڕەنگە چەند ڕۆژێکی پێ بچێت. ئەگەر بازرگانییەکەت RTO-یەکی 4 کاتژمێری هەبێت، mysqldump دڵنیات دەکاتەوە کە ڕێککەوتنی ئاستی خزمەتگوزاری (SLA) شکاندووە.

جێگرەوەکانی ئاستی کۆمپانیا: گواستنەوە بۆ کۆپیی یەدەگی فیزیکی

بۆ بەدەستهێنانی کۆپی و گەڕاندنەوەی خێرا بۆ داتای گەورە، دەبێت واز لە کۆپییە لۆژیکییەکان بهێنیت و ڕوو لە کۆپیی یەدەگی فیزیکی بکەیت.

کۆپییە فیزیکییەکان بە تەواوی بزوێنەری جێبەجێکردنی SQL-ی MySQL تێپەڕ دەکەن. لەبری ئەوە، ئەوان فایلە داتا سەرەکییەکان (فایلەکانی .ibd، redo logs، و undo logs) ڕاستەوخۆ لە سیستەمی فایلەکانەوە کۆپی دەکەن. چونکە ئەوان تەنها فایل کۆپی دەکەن، دەتوانن بە ئەوپەڕی خێرایی خوێندنەوە/نووسینی ڕەقەکاڵاکەت کار بکەن و دەتوانرێت بە شێوەیەکی بەرفراوان هاوتەریب (parallelized) بکرێن.

Percona XtraBackup: ستانداردی پیشەسازی

بۆ بزوێنەرەکانی InnoDB و XtraDB، Percona XtraBackup باشترین ئامرازی کراوەی کۆپیی یەدەگی فیزیکییە. ئەم ئامرازە کۆپیی یەدەگی گەرم (hot) و بێ قفڵ بۆ بنکەدراوەکانی MySQL ئەنجام دەدات.

XtraBackup چۆن کار دەکات

کۆپیکردنی داتا: XtraBackup دەست دەکات بە کۆپیکردنی فایلە داتاکانی InnoDB (.ibd).
چاودێریکردنی لۆگ: چونکە بنکەدراوەکە چالاکە، داتا دەگۆڕێت لەو کاتەی فایلەکان کۆپی دەکرێن. XtraBackup ڕێچکەیەکی پشتەوە (background thread) دروست دەکات کە چاودێری و کۆپیکردنی InnoDB redo log (ib_logfile0، هتد) دەکات بۆ هەر مامەڵەیەک کە لە کاتی کۆپیکردنەکەدا ڕوودەدات.
ئامادەکاری (چاککردنەوەی پێکدادان): دوای کۆپیکردنەکە، فایلە داتا کۆپیکراوەکان لە دۆخێکی ناگونجاودان. XtraBackup ئەو redo log-انەی کۆپیکراون دەخاتە سەر فایلە داتاکان (وەک چۆن MySQL چاککردنەوەی پێکدادان لە کاتی دەستپێکردندا ئەنجام دەدات)، ئەمەش دەبێتە هۆی دروستبوونی وێنەیەکی تەواو گونجاوی بنکەدراوەکە لەو ساتەی کە کۆپیکردنەکە تەواو بوو.

جێبەجێکردنی ستراتیژییەکی کۆپیی یەدەگی فیزیکی

لێرەدا ڕێنماییەکی تەکنیکی بۆ جێبەجێکردنی ستراتیژییەکی کۆپیی یەدەگی فیزیکی بە بەکارهێنانی Percona XtraBackup هەیە.

هەنگاوی 1: پەخشکردنی کۆپیی یەدەگ (Streaming)

نووسینی کۆپییەکی یەدەگی زەبەلاح بۆ سەر دیسکی ناوخۆیی زۆرجار کێشەی توانای کۆگاکردن دروست دەکات. باشترین پراکتیزە ئەوەیە کە کۆپییەکە ڕاستەوخۆ بۆ فۆرماتێکی ئەرشیف پەخش بکرێت، پەستێنرێت (compress)، و بنێردرێت بۆ ناوچەیەکی کاتی یان ڕاستەوخۆ بۆ پلاتفۆرمێکی کۆپیی یەدەگ.

بە بەکارهێنانی xbstream، دەتوانین کۆپییەکە هاوتەریب بکەین و لە کاتی خۆیدا پەستی بکەین:

xtrabackup --backup 
  --user=backup_user 
  --password=SecurePassword! 
  --parallel=4 
  --stream=xbstream | lz4 > /mnt/backups/mysql_prod_backup.xbstream.lz4

--parallel=4: 4 ڕێچکە بەکاردەهێنێت بۆ خوێندنەوەی فایلە داتاکان بە هاوتەریبی.
--stream=xbstream: کۆپییەکە بە فۆرماتی پەخشکردنی تایبەتی Percona دەردەکات.
lz4: پەستانێکی زۆر خێرا و بە بەکارهێنانی CPU-یەکی کەم دابین دەکات.

هەنگاوی 2: ئامادەکردنی کۆپیی یەدەگ بۆ گەڕاندنەوە

پێش ئەوەی کۆپییەکی یەدەگی فیزیکی بگەڕێندرێتەوە، دەبێت “ئامادە” بکرێت (جێبەجێکردنی redo logs). سەرەتا، پەخشەکە دەربهێنە و لە پەستان ڕزگاری بکە:

mkdir -p /data/restore
lz4 -d /mnt/backups/mysql_prod_backup.xbstream.lz4 | xbstream -x -C /data/restore

دواتر، قۆناغی ئامادەکاری جێبەجێ بکە. ئەم هەنگاوە پێویستی بە بیرگە هەیە، بۆیە دڵنیابە کە سێرڤەرەکە RAM-ی پێویستی تەرخانکراوی هەیە:

xtrabackup --prepare --use-memory=4G --target-dir=/data/restore

هەنگاوی 3: گەڕاندنەوەی بنکەدراوە

بۆ گەڕاندنەوە، پێویستە بوخچەی داتای MySQL-ی ئامانج بە تەواوی بەتاڵ بێت. خزمەتگوزاری MySQL بوەستێنە، بوخچەکە پاک بکەرەوە، و فایلەکان بگەڕێنەرەوە:

systemctl stop mysql
rm -rf /var/lib/mysql/*

xtrabackup --copy-back --target-dir=/data/restore

لە کۆتاییدا، مۆڵەتەکانی سیستەمی فایلەکان چاک بکە پێش دەستپێکردنی خزمەتگوزارییەکە:

chown -R mysql:mysql /var/lib/mysql
systemctl start mysql

چونکە فایلە داتاکان پێشوەختە دروستکراون و ئیندێکسەکان کۆمپایل کراون، بنکەدراوەکە دەستبەجێ دەست پێ دەکات. گەڕاندنەوەیەک کە 48 کاتژمێری بە mysqldump پێ دەچوو، ئێستا تەنها ئەوەندەی پێ دەچێت کە فایلەکان بە تۆڕ یان دیسکەکەتدا بگوازرێنەوە—زۆرجار RTO بۆ چەند خولەکێک کەم دەکاتەوە.

ئۆپتیمایزکردنی گەڕاندنەوە لۆژیکییەکان (کاتێک ناچاری بەکاریان بهێنیت)

ئەگەر ناچار بوویت کۆپییەکی لۆژیکی گەورە بگەڕێنیتەوە (بۆ نموونە، کۆچکردن لە نێوان وەشانی سەرەکی جیاوازی MySQL یان تەلارسازیی جیاوازی CPU کە فایلە فیزیکییەکان تێیدا ناگونجێن)، دەبێت بە کاتی ڕێکخستنەکانی MySQL-ت ئۆپتیمایز بکەیت بۆ بەرزترین خێرایی نووسین.

ئەم ڕێکخستنانە بۆ my.cnf-ەکەت جێبەجێ بکە پێش دەستپێکردنی گەڕاندنەوە لۆژیکییەکە:

[mysqld]
# کاتی binlogging بە کاتی بوەستێنە ئەگەر ئەمە گەڕاندنەوەیەکی سەربەخۆیە
disable_log_bin

# دواخستنی پاککردنەوەی دیسک بۆ بەرزکردنەوەی خێرایی نووسین
innodb_flush_log_at_trx_commit = 2

# گەورەکردنی حەوزی بیرگە بۆ ئەوەی زۆرترین بەشی داتا لەخۆ بگرێت
innodb_buffer_pool_size = <Set to 70% of total RAM>

# گەورەکردنی قەبارەی فایلە لۆگەکان بۆ ڕێگریکردن لە checkpointing-ی توند
innodb_log_file_size = 2G

# ناچالاککردنی doublewrite buffer (مەترسیدارە بۆ بەرهەمهێنان، سەلامەتە بۆ بارکردنی سەرەتایی)
innodb_doublewrite = 0

تێبینی: هەمیشە ئەم ڕێکخستنانە بگەڕێنەرەوە بۆ دۆخە ستانداردەکانی ACID (innodb_flush_log_at_trx_commit = 1، innodb_doublewrite = 1) و خزمەتگوزاری MySQL دووبارە دەست پێ بکەرەوە پێش ئەوەی ڕێگە بە هاتووچۆی بەرهەمهێنان بدەیت.

ئۆتۆماتیککردن و پاراستنی کۆپییەکان لەگەڵ CloudSave

هەرچەندە ئامرازەکانی وەک Percona XtraBackup میکانیکی دەرهێنانی داتا بە شێوەیەکی کارا چارەسەر دەکەن، بەڵام ستراتیژییەکی ڕاستەقینەی ڕزگاربوون لە کارەسات لە ئاستی کۆمپانیاکان پێویستی بە ڕێکخستن، کۆگای دەرەکی سەلامەت، و بەڕێوەبردنی ژیانی داتا هەیە. پشت بەستن بە سکریپتی bash-ی تایبەت و cron jobs بۆ بەڕێوەبردنی کۆپییە فیزیکییەکان مەترسییەکی زۆری شکستی بێدەنگ و پێشێلکردنی یاساکان دروست دەکات.

لێرەدایە کە تێکەڵکردنی چینی بنکەدراوەکەت لەگەڵ پلاتفۆرمێکی کۆمپانیا وەک CloudSave دەبێتە شتێکی گرنگ.

CloudSave بۆشایی نێوان ئامرازە خاوەکانی بنکەدراوە و پابەندبوونی کۆمپانیاکان پڕ دەکاتەوە. بە بەکارهێنانی تواناکانی پێش و دوای سکریپتی CloudSave، تیمەکانی DevOps دەتوانن XtraBackup چالاک بکەن بۆ دروستکردنی وێنەیەکی فیزیکی گونجاو. پاشان CloudSave بە شێوەیەکی بێ کێشە پەخشی کۆپییەکە وەردەگرێت، پاراستنی AES-256 جێبەجێ دەکات، و داتا دووبارەبووەکان لادەبات (deduplicate) پێش ئەوەی بۆ کۆگای هەوری نەگۆڕ (immutable cloud storage) دووبارەی بکاتەوە.

ئەم تەلارسازییە دڵنیایی دەدات کە:
1. ئاستی بەرهەمهێنان دەپارێزرێت: کۆپییەکان بە خێرایی کۆگاکردن کار دەکەن بەبێ پیسکردنی حەوزی بیرگەی InnoDB.
2. پاراستن لە ڕانسۆموێر: سیاسەتەکانی کۆگای نەگۆڕ لە ناو CloudSave ڕێگری لە ئەکتەرە خراپەکارەکان دەکات لە سڕینەوە یان کۆدکردنی ئەرشیفی بنکەدراوەکەت.
3. مانەوەی ئۆتۆماتیکی: سیاسەتەکانی مانەوەی (GFS) بە شێوەیەکی ئۆتۆماتیکی بەڕێوەدەبرێن، ئەمەش دڵنیایی دەدات لە پابەندبوون بە سەروەری داتا و پێداویستییەکانی وردبینی.
4. RTO-ی پێشبینیکراو: چونکە CloudSave ئەرشیفی فایلە فیزیکییەکان بەڕێوەدەبات، گەڕاندنەوەی بنکەدراوەیەکی چەند تێرابایتی بۆ نموونەیەکی نوێ دەتوانرێت بە خێرایی ڕێکبخرێت، و ئامانجە توندەکانی RTO بەدی بهێنێت.

کۆتایی

بەردەوامبوون لە بەکارهێنانی mysqldump بۆ بنکەدراوە گەورەکان قومارکردنە لەسەر کاتی کارکردن و یەکگرتوویی داتای ڕێکخراوەکەت. سروشتی تاک-ڕێچکەیی، پیسبوونی حەوزی بیرگە، و کاتە کارەساتبارەکانی گەڕاندنەوە وای لێ دەکات کە بە بنەڕەتی بۆ ژینگەکانی مۆدێرن و خێرایی بەرز نەگونجێت.

بە گواستنەوە بۆ کۆپیی یەدەگی فیزیکی بە بەکارهێنانی ئامرازەکانی وەک Percona XtraBackup، و ڕێکخستنی ژیان، پاراستن، و دووبارەکردنەوەی دەرەکی لە ڕێگەی پلاتفۆرمێکی بەهێز وەک CloudSave، تۆ ستراتیژی کۆپیی یەدەگی بنکەدراوەکەت لە بەرپرسیارێتییەکی لاوازەوە دەگۆڕیت بۆ سەروەتێکی بەهێز و ئاست-کۆمپانیا. ئەمڕۆ پێوەرەکانی RTO و RPO-ی ئێستات هەڵبسەنگێنە—ئەگەر گەڕاندنەوەیەک زیاتر لەوەی بازرگانییەکەت دەتوانێت لە دەرەوەی کار بێت پێ بچێت، کاتی ئەوە هاتووە واز لە mysqldump بهێنیت.

بۆچی mysqldump بۆ بنکەدراوە گەورەکانی MySQL-ەکەت شکست دەهێنێت (و چۆن چارەسەری بکەیت)