-
النسخ الاحتياطي والاسترداد لـ MySQL السبت, 2006/09/30 - 14:21 — بالفعل
النسخ الاحتياطي والاسترداد MySQL
المؤلف/المترجم: Ye Jinrong (البريد الإلكتروني:)، المصدر: http://imysql.cn يرجى الإشارة إلى المؤلف/المترجم والمصدر عند إعادة الطبع، ولا يمكن استخدامه لأغراض تجارية.
التاريخ: 2006/10/01
تتناول هذه المقالة آلية النسخ الاحتياطي والاسترداد لـ MySQL وكيفية الحفاظ على جداول البيانات، بما في ذلك نوعي الجدول الرئيسيين: MyISAM وInnodb. إصدار MySQL المصمم في هذه المقالة هو 5.0.22.
تتضمن أدوات النسخ الاحتياطي المجانية التي يدعمها MySQL حاليًا: mysqldump، وmysqlhotcopy. ويمكنك أيضًا استخدام بناء جملة SQL للنسخ الاحتياطي: BACKUP TABLE أو SELECT INTO OUTFILE، أو السجلات الثنائية الاحتياطية (binlog)، أو نسخ ملفات البيانات وملفات التكوين ذات الصلة مباشرة. يتم حفظ جداول MyISAM كملفات، لذا يمكن استخدام العديد من الطرق المذكورة أعلاه بسهولة نسبيًا. يتم تخزين جميع الجداول في Innodb في نفس ملف البيانات ibdata1 (قد يكون أيضًا ملفات متعددة أو ملفات مساحة جدول مستقلة)، وهو أمر يصعب نسبيًا نسخه احتياطيًا. يمكن أن تكون الحلول المجانية هي نسخ ملفات البيانات والنسخ الاحتياطي لـ binlog أو استخدام mysqldump .
1.mysqldump
1.1 النسخ الاحتياطي
يستخدم mysqldump آلية النسخ الاحتياطي على مستوى SQL، حيث يقوم بتصدير جداول البيانات إلى ملفات نصية SQL، وهو مناسب نسبيًا للترقية بين إصدارات MySQL المختلفة، وهذه أيضًا طريقة النسخ الاحتياطي الأكثر استخدامًا.
الآن دعونا نتحدث عن بعض المعلمات الرئيسية لـ mysqldump:
--متوافق=اسم
فهو يخبر mysqldump بقاعدة البيانات أو الإصدار الأقدم من خادم MySQL الذي ستكون البيانات المصدرة متوافقة معه. يمكن أن تكون القيم ansi، وmysql323، وmysql40، وpostgresql، وOracle، وmssql، وdb2، وmaxdb، وno_key_options، وno_tables_options، وno_field_options، وما إلى ذلك. لاستخدام عدة قيم، افصل بينها بفواصل. وبطبيعة الحال، فهو لا يضمن التوافق الكامل، لكنه يحاول أن يكون متوافقا.
--إدراج كامل، -ج
تستخدم البيانات المصدرة طريقة INSERT الكاملة بما في ذلك أسماء الحقول، أي أن جميع القيم مكتوبة في سطر واحد. يمكن أن يؤدي القيام بذلك إلى تحسين كفاءة الإدراج، ولكنه قد يتأثر بالمعلمة max_allowed_packet ويتسبب في فشل الإدراج. لذلك، يجب استخدام هذه المعلمة بحذر، على الأقل لا أوصي بها.
--default-character-set=charset
حدد مجموعة الأحرف التي سيتم استخدامها عند تصدير البيانات. إذا كان جدول البيانات لا يستخدم مجموعة الأحرف اللاتينية 1 الافتراضية، فيجب تحديد هذا الخيار عند التصدير، وإلا فستظهر أحرف مشوهة بعد استيراد البيانات مرة أخرى.
--تعطيل المفاتيح
أخبر mysqldump بإضافة /*!40000 ALTER TABLE table DISABLE KEYS */; و /*!40000 ALTER TABLE table ENABLE KEYS */; يمكن أن يؤدي ذلك إلى تحسين سرعة عبارة الإدراج بشكل كبير لأنه يتم إعادة بناء الفهرس بعد إدراج جميع البيانات. هذا الخيار مناسب فقط لجداول MyISAM.
--extensive-insert = true|false
افتراضيًا، يقوم mysqldump بتشغيل وضع --complete-insert، لذا إذا كنت لا تريد استخدامه، فما عليك سوى استخدام هذا الخيار وتعيين قيمته على false.
--hex-blob
تصدير حقول السلسلة الثنائية باستخدام التنسيق الست عشري. يجب استخدام هذا الخيار في حالة وجود بيانات ثنائية. أنواع الحقول المتأثرة هي BINARY وVARBINARY وBLOB.
--قفل جميع الجداول،-x
قبل البدء في التصدير، أرسل طلبًا لتأمين جميع الجداول في جميع قواعد البيانات لضمان تناسق البيانات. هذا قفل قراءة عالمي ويتم إيقاف تشغيله تلقائيًا باستخدام خيارات --single-transaction و --lock-tables.
--قفل الجداول
إنه مشابه لـ --lock-all-tables، لكنه يقوم بتأمين جدول البيانات المصدرة حاليًا بدلاً من قفل جميع الجداول في قاعدة البيانات مرة واحدة. ينطبق هذا الخيار فقط على جداول MyISAM إذا كان جدول Innodb، فيمكنك استخدام خيار --single-transaction.
--no-create-info، -t
قم بتصدير البيانات فقط دون إضافة عبارة CREATE TABLE.
--لا توجد بيانات،-د
لا يتم تصدير أي بيانات، بل يتم تصدير بنية جدول قاعدة البيانات فقط.
--opt
هذا مجرد خيار سريع، يعادل أيضًا إضافة --add-drop-tables --add-locking --create-option --disable-keys --extensive-insert --lock-tables --quick --set- خيارات مجموعة المحارف. يسمح هذا الخيار لـ mysqldump بتصدير البيانات بسرعة، ويمكن استيراد البيانات المصدرة مرة أخرى بسرعة. يتم تمكين هذا الخيار افتراضيًا، ولكن يمكن تعطيله باستخدام --skip-opt. لاحظ أنه إذا قمت بتشغيل mysqldump دون تحديد الخيار --quick أو --opt، فسيتم وضع مجموعة النتائج بأكملها في الذاكرة. قد تحدث مشاكل إذا قمت بتصدير قاعدة بيانات كبيرة.
--سريع،-س
يعد هذا الخيار مفيدًا عند تصدير جداول كبيرة، فهو يفرض على mysqldump إخراج السجلات التي تم الحصول عليها من استعلام الخادم مباشرة بدلاً من استرجاع كافة السجلات وتخزينها مؤقتًا في الذاكرة.
--الروتين، -R
تصدير الإجراءات المخزنة والوظائف المخصصة.
--معاملة واحدة
يرسل هذا الخيار عبارة BEGIN SQL قبل تصدير البيانات ولا يحظر BEGIN أي تطبيقات ويضمن حالة متسقة لقاعدة البيانات أثناء التصدير. إنه يعمل فقط مع جداول المعاملات مثل InnoDB وBDB.
هذا الخيار وخيار --lock-tables متنافيان لأن LOCK TABLES يتسبب في الالتزام ضمنيًا بأي معاملات معلقة.
لتصدير جداول كبيرة، يجب استخدام الخيار --quick معًا.
--المشغلات
مشغلات التصدير أيضا. يتم تمكين هذا الخيار افتراضيًا، استخدم --skip-triggers لتعطيله.
يرجى الرجوع إلى الدليل للحصول على تفاصيل المعلمات الأخرى وعادة ما أستخدم SQL التالي لعمل نسخة احتياطية من جداول MyISAM:
/usr/local/mysql/bin/mysqldump -uyejr -pyejr
--default-character-set=utf8 --opt --extened-insert=false
--triggers -R --hex-blob -x db_name > db_name.sql
استخدم SQL التالي لعمل نسخة احتياطية من جداول Innodb:
/usr/local/mysql/bin/mysqldump -uyejr -pyejr
--default-character-set=utf8 --opt --extened-insert=false
--المشغلات -R --hex-blob --معاملة واحدة db_name > db_name.sql
بالإضافة إلى ذلك، إذا كنت تريد تنفيذ النسخ الاحتياطي عبر الإنترنت، فيمكنك أيضًا استخدام المعلمة --master-data، كما يلي:
/usr/local/mysql/bin/mysqldump -uyejr -pyejr
--default-character-set=utf8 --opt --master-data=1
--معاملة واحدة --سجلات التدفق db_name > db_name.sql
إنه يطلب فقط جدول القفل في البداية، ثم يقوم بتحديث binlog، ثم يضيف عبارة CHANGE MASTER إلى الملف المُصدَّر لتحديد موقع binlog للنسخة الاحتياطية الحالية، إذا كنت تريد استعادة هذا الملف إلى التابع، فيمكنك استخدامه بهذه الطريقة للقيام بذلك.
1.2 الاستعادة الملف الذي تم نسخه احتياطيًا باستخدام mysqldump هو برنامج نصي SQL يمكن استيراده مباشرة. هناك طريقتان لاستيراد البيانات.
استخدم عميل MySQL مباشرة، على سبيل المثال:
/usr/local/mysql/bin/mysql -uyejr -pyejr db_name < db_name.sql
إن استخدام بناء جملة SOURCE ليس في الواقع بناء جملة SQL قياسيًا، ولكنه وظيفة يوفرها عميل MySQL، على سبيل المثال:
المصدر /tmp/db_name.sql;
هنا تحتاج إلى تحديد المسار المطلق للملف، ويجب أن يكون ملفًا يمتلك المستخدم قيد التشغيل mysqld (على سبيل المثال، لا أحد) إذنًا لقراءته.
2.mysqlhotcopy
2.1 النسخ الاحتياطي
mysqlhotcopy هو برنامج PERL كتبه في الأصل تيم بونس. يستخدم LOCK TABLES وFLUSH TABLES وcp أو scp لعمل نسخة احتياطية من قاعدة البيانات بسرعة. إنها أسرع طريقة لإجراء نسخ احتياطي لقاعدة بيانات أو جدول واحد، ولكن يمكن تشغيلها فقط على الجهاز الذي توجد به ملفات قاعدة البيانات (بما في ذلك ملفات تعريف جدول البيانات، وملفات البيانات، وملفات الفهرس). لا يمكن استخدام mysqlhotcopy إلا لعمل نسخة احتياطية من MyISAM، وسيتم تشغيله فقط على الأنظمة المشابهة لـ Unix وNetWare.
يدعم mysqlhotcopy نسخ قواعد بيانات متعددة في وقت واحد، ويدعم أيضًا التعبيرات العادية. فيما يلي بعض الأمثلة:
root#/usr/local/mysql/bin/mysqlhotcopy -h=localhost -u=yejr -p=yejr
db_name /tmp (قم بتغيير دليل قاعدة البيانات db_name
انسخ إلى /tmp
تحت)
root#/usr/local/mysql/bin/mysqlhotcopy -h=localhost -u=yejr -p=yejr
db_name_1 ... db_name_n /tmp
root#/usr/local/mysql/bin/mysqlhotcopy -h=localhost -u=yejr -p=yejr
db_name./regex/ /tmp
يرجى الرجوع إلى الدليل لمزيد من الاستخدام التفصيلي، أو اتصل بالأمر التالي لعرض مساعدة mysqlhotcopy:
perldoc /usr/local/mysql/bin/mysqlhotcopy
لاحظ أنه إذا كنت تريد استخدام mysqlhotcopy، فيجب أن يكون لديك أذونات SELECT وRELOAD (لتنفيذ FLUSH TABLES)، ويجب أن يكون لديك أيضًا إذن لقراءة دليل datadir/db_name.
2.2 استعادة
يقوم Mysqlhotcopy بعمل نسخة احتياطية من دليل قاعدة البيانات بالكامل، عند استخدامه، يمكن نسخه مباشرة إلى datadir المحدد بواسطة mysqld (هنا هو /usr/local/mysql/data/). كما في المثال التالي:
root#cp -rf db_name /usr/local/mysql/data/
root#chown -R none:nobody /usr/local/mysql/data/ (قم بتغيير مالك دليل db_name إلى mysqld
مستخدم قيد التشغيل)
3. النسخ الاحتياطي لبناء جملة SQL
3.1 النسخ الاحتياطي
يشبه بناء جملة BACKUP TABLE في الواقع مبدأ عمل mysqlhotcopy. كلاهما يقوم بقفل الجدول ثم نسخ ملف البيانات. يمكنه إجراء نسخ احتياطي عبر الإنترنت، لكن التأثير ليس مثاليًا، لذا لا ينصح به. يقوم فقط بنسخ ملفات بنية الجدول وملفات البيانات، ولكنه لا ينسخ ملفات الفهرس في نفس الوقت، لذلك يكون الاسترداد أبطأ.
مثال:
الجدول الخلفي tbl_name TO '/tmp/db_name/'؛
لاحظ أنه يجب أن يكون لديك إذن FILE لتنفيذ SQL هذا، ويجب أن يكون الدليل /tmp/db_name/ قابلاً للكتابة بواسطة مستخدم mysqld. لا يمكن للملف المُصدر الكتابة فوق الملف الموجود لتجنب مشكلات الأمان.
يقوم SELECT INTO OUTFILE بتصدير البيانات إلى ملف نصي عادي. يمكنك تخصيص الفاصل الزمني للحقل لتسهيل معالجة هذه البيانات.
مثال:
حدد * في الملف الخارجي '/tmp/db_name/tbl_name.txt' من tbl_name؛
لاحظ أنه يجب أن يكون لديك إذن FILE لتنفيذ SQL هذا، ويجب أن يكون الملف /tmp/db_name/tbl_name.txt قابلاً للكتابة بواسطة مستخدم mysqld. لا يمكن للملف الذي تم تصديره الكتابة فوق الملف الموجود لتجنب مشكلات الأمان.
3.2 لاستعادة الملفات التي تم نسخها احتياطيًا باستخدام أسلوب BACKUP TABLE، يمكنك تشغيل عبارة RESTORE TABLE لاستعادة جدول البيانات.
مثال:
استعادة الجدول من '/tmp/db_name/'؛
متطلبات الأذونات مشابهة لتلك الموضحة أعلاه.
بالنسبة للملفات التي تم نسخها احتياطيًا باستخدام أسلوب SELECT INTO OUTFILE، يمكنك تشغيل عبارة LOAD DATA INFILE لاستعادة جدول البيانات.
مثال:
تحميل ملف البيانات '/tmp/db_name/tbl_name.txt' في الجدول tbl_name;
متطلبات الأذونات مشابهة لتلك الموضحة أعلاه. قبل استيراد البيانات، يجب أن يكون جدول البيانات موجودًا بالفعل. إذا كنت قلقًا بشأن تكرار البيانات، فيمكنك إضافة الكلمة الأساسية REPLACE لاستبدال السجلات الموجودة أو استخدام الكلمة الأساسية IGNORE لتجاهلها.
4. تمكين السجل الثنائي (binlog)
تعد طريقة استخدام binlog أكثر مرونة نسبيًا، وتوفر القلق والجهد، ويمكنها أيضًا دعم النسخ الاحتياطي المتزايد.
يجب إعادة تشغيل Mysqld عند تمكين binlog. أولاً، أغلق mysqld، وافتح my.cnf، وأضف الأسطر التالية:
معرف الخادم=1
سجل بن = binlog
سجل بن فهرس = binlog.index
ثم ابدأ mysqld. سيتم إنشاء Binlog.000001 وbinlog.index أثناء العملية. الملف الأول هو mysqld الذي يسجل جميع عمليات التحديث على البيانات، والملف الأخير هو فهرس جميع السجلات، والتي لا يمكن حذفها بسهولة. يرجى الاطلاع على الدليل للحصول على معلومات حول binlog.
عندما تحتاج إلى النسخ الاحتياطي، يمكنك أولاً تنفيذ عبارة SQL للسماح لـ mysqld بإنهاء الكتابة إلى binlog الحالي، ثم نسخ الملف احتياطيًا مباشرة بهذه الطريقة، يمكن تحقيق غرض النسخ الاحتياطي المتزايد:
FLUSH LOGS؛ إذا كنت تقوم بعمل نسخة احتياطية للخادم التابع في نظام النسخ المتماثل، فيجب عليك أيضًا عمل نسخة احتياطية من ملفات master.info وrelay-log.info.
يمكن عرض ملف binlog الذي تم نسخه احتياطيًا باستخدام الأداة mysqlbinlog التي توفرها MySQL، مثل:
/usr/local/mysql/bin/mysqlbinlog /tmp/binlog.000001
تتيح لك هذه الأداة عرض جميع عبارات SQL ضمن قاعدة بيانات محددة، ويمكنها أيضًا تحديد النطاق الزمني، وهو أمر مناسب تمامًا، يرجى الرجوع إلى الدليل للحصول على التفاصيل.
عند الاستعادة، يمكنك استخدام عبارات مشابهة لما يلي:
/usr/local/mysql/bin/mysqlbinlog /tmp/binlog.000001 | mysql -uyejr -pyejr db_name
استخدم عبارات SQL التي يتم إخراجها بواسطة mysqlbinlog مباشرة كمدخل لتنفيذها.
إذا كان لديك جهاز خامل، فيمكنك أيضًا استخدام هذه الطريقة لعمل نسخة احتياطية منه. نظرًا لأن متطلبات أداء الأجهزة التابعة منخفضة نسبيًا، فيمكن تحقيق النسخ الاحتياطي التزايدي بتكلفة منخفضة ويمكن مشاركة جزء من ضغط استعلام البيانات.
5. النسخ الاحتياطي المباشر لملفات البيانات مقارنة بالطرق السابقة، يعد النسخ الاحتياطي لملفات البيانات هو الأكثر مباشرة وسرعة وملاءمة، والعيب هو أن النسخ الاحتياطي المتزايد مستحيل في الأساس. من أجل ضمان تناسق البيانات، يجب تنفيذ عبارة SQL التالية قبل النسخ الاحتياطي للملف:
مسح الجداول باستخدام قفل القراءة؛ أي مسح جميع البيانات الموجودة في الذاكرة إلى القرص وقفل جدول البيانات لضمان عدم كتابة أي بيانات جديدة أثناء عملية النسخ. من السهل أيضًا استعادة البيانات التي تم نسخها احتياطيًا بهذه الطريقة، ما عليك سوى نسخها مرة أخرى إلى دليل قاعدة البيانات الأصلية.
لاحظ أنه بالنسبة للجداول من نوع Innodb، فإنك تحتاج أيضًا إلى عمل نسخة احتياطية من ملفات السجل الخاصة بها، أي ملفات ib_logfile*. لأنه عندما يتلف جدول Innodb، يمكنك الاعتماد على ملفات السجل هذه لاستردادها.
6. استراتيجية النسخ الاحتياطي بالنسبة للأنظمة ذات حجم الأعمال المتوسط، يمكن تحديد استراتيجية النسخ الاحتياطي على النحو التالي: النسخ الاحتياطي الكامل لأول مرة، والنسخ الاحتياطي المتزايد مرة واحدة يوميًا، والنسخ الاحتياطي الكامل مرة واحدة في الأسبوع، وهكذا. بالنسبة للأنظمة المهمة والمزدحمة، قد تحتاج إلى نسخة احتياطية كاملة مرة واحدة يوميًا، أو نسخة احتياطية تزايدية مرة واحدة كل ساعة، أو حتى بشكل متكرر. من أجل تحقيق النسخ الاحتياطي عبر الإنترنت والنسخ الاحتياطي المتزايد دون التأثير على الأعمال التجارية عبر الإنترنت، فإن أفضل طريقة هي استخدام آلية النسخ المتماثل للسيد والعبد (النسخ المتماثل) لعمل نسخ احتياطية على الجهاز التابع.
7. صيانة البيانات والتعافي من الكوارث بصفتي DBA (لم أفعل ذلك بعد، هاها)، إحدى أهم المهام هي التأكد من إمكانية استخدام جداول البيانات بأمان وثبات وبسرعة عالية. ولذلك، يجب صيانة جداول البيانات الخاصة بك بانتظام. عبارة SQL التالية مفيدة:
التحقق من الجدول أو إصلاح الجدول، والتحقق من جداول MyISAM أو صيانتها
تحسين الجدول، تحسين جدول MyISAM
تحليل الجدول، تحليل جدول MyISAM بالطبع، يمكن إكمال جميع الأوامر المذكورة أعلاه من خلال الأداة myisamchk، ولن يتم وصفها بالتفصيل هنا.
يمكن إلغاء تجزئة جداول Innodb وتحسين سرعة الفهرسة عن طريق تنفيذ العبارات التالية:
ALTER TABLE tbl_name ENGINE = Innodb;
هذه في الواقع عملية فارغة على السطح، ولا تفعل شيئًا، ولكنها في الواقع تعيد ترتيب الأجزاء.
يمكن استعادة جداول MyISAM شائعة الاستخدام باستخدام الطرق المذكورة أعلاه. إذا كان الفهرس معطلاً، فيمكنك استخدام أداة myisamchk لإعادة بناء الفهرس. بالنسبة لجداول Innodb، الأمر ليس بهذه البساطة، لأنه يخزن جميع الجداول في مساحة جدول واحدة. ومع ذلك، لدى Innodb آلية فحص تسمى نقطة التفتيش الغامضة، وطالما تم حفظ ملف السجل، يمكن إصلاح الأخطاء بناءً على ملف السجل. يمكنك إضافة المعلمات التالية في ملف my.cnf للسماح لـ mysqld بالتحقق تلقائيًا من ملف السجل عند بدء تشغيله:
innodb_force_recovery = 4
راجع الدليل للحصول على معلومات حول هذه المعلمة.
8. تلخيص النسخ الاحتياطي للبيانات وتحديد استراتيجية النسخ الاحتياطي المناسبة، وهذا جزء صغير مما يفعله DBA.