MySQL هو نظام مفتوح المصدر لإدارة قواعد البيانات، ويُثبت غالبا كجزء من الحزمة الشهيرة LAMP (Linux, Apache, MySQL, PHP/Python/Perl). يستخدم MySQL قواعد بيانات علائقية و SQL (لغة استعلام مهيكلة) لإدارة بياناته.

الطريقة المختصرة لتثبيت MySQL سهلة جدا: حدث الحزمة الرئيسية، ثم ثبت حزمة mysql-server ثم شغِّل سكريبت الحماية المضمن.

$ sudo apt update
$ sudo apt install mysql-server
$ sudo mysql_secure_installation

متطلبات أولية

لإكمال هذا الإعداد، ستحتاج إلى: خادم أوبونتو 18.04 مُعد كما في مقال الإعداد الأولي لِخادم أوبونتو. متضمنا مستخدم غير مسؤول (non-root user) بصلاحيات sudo وجدار حماية.

خطوة 1 - تثبيت MySQL

تتضمن تلقائيا حزمة مخزن APT في أوبونتو 18.04 أحدث إصدار فقط من MySQL. في الوقت الراهن، أحدث اصدار هو MySQL 5.7.

للتثبيت، حدّث الحزمة الرئيسية على الخادم باستخدام الأمر apt:

$ sudo apt update

ثم ثبت الحزمة الافتراضية:

$ sudo apt install mysql-server

سيُثبت هذا الأمر MySQL لكنه لن يتيح لك إعداد كلمة مرور أو القيام بأي تعديل على الإعدادات. مما يجعل تثبيت MySQL غير آمن وسيتم شرح ذلك لاحقا.

خطوة 2 - إعداد MySQL

ستحتاج لِتشغيل سكريبت الحماية المضمن للحصول على تثبيت صحيح. الأمر الذي سيغير بعض الخيارات الأقل أمنا مثل دخول المستخدمين المسؤولين عن بعد أو عينات المستخدمين. كنا نقوم بإعداد هذه البيانات يدويا في إصدارات سابقة، لكن في هذا الإصدار يتم إعدادها تلقائيا.

قم بتشغيل السكربت:

$ sudo mysql_secure_installation

ستنتقل عبر بعض الخيارات حيث يمكنك القيام ببعض التغييرات على إعدادات حماية MySQL المٌعدة أثناء التثبيت. الخيار الأول سيسألك إن كنت تريد إعداد إضافة التحقق بكلمة المرور والمستخدمة لقياس قوة كلمة مرور MySQL. بغض النظر عن خيارك، الخيار التالي سيكون لإعداد كلمة مرور للمستخدم المسؤول ل MySQL. أدخل وأكِد كلمة مرور آمنة.

بعد ذلك يمكنك الضغط على Y ثم ENTER لقبول الإعداد الافتراضي لبقية الأسئلة التالية. سيقوم ذلك بحذف بعض المستخدمين المجهولين وقاعدة البيانات التجريبية، وإلغاء تفعيل دخول المستخدمين المسؤولين عن بعد، وسيُحمل الإعدادات الجديدة التي قمت بعملها على MySQL.

لإعداد دليل بيانات MySQL يمكنك استخدام mysql_install_db للإصدارات قبل 5.7.6 و mysqld --initialize لتهيئة الإصدار 5.7.6 والإصدارات الأحدث. لكن إن كنت قد ثبتت MySQL من موزع Debian كما في الخطوة 1، فإن دليل البيانات قد تم إعداده تلقائيا ولن تحتاج للقيام بأي شيء. وبحال أو بآخر إن قمت بتنفيذ الأمر، فسترى الخطأ التالي:

المخرجات:

mysqld: Can't create directory '/var/lib/mysql/' (Errcode: 17 - File exists)

. . .

2018-04-23T13:48:00.572066Z 0 [ERROR] Aborting

لاحظ أنه حتى إن قمت بإعداد كلمة مرور لمستخدم MySQL المسؤول، فإن هذا المستخدم ليس معدًا للتحقق عبر كلمة المرور عند الاتصال بشاشة اوامر MySQL. إن أردت تغيير هذا الإعداد اتبع الخطوة 3.

خطوة 3 - (اختيارية) إعداد مصادقة وصلاحيات المستخدم

المستخدم المسؤول في أنظمة أوبونتو التي تستخدم MySQL 5.7 مُعد للمصادقة تلقائيا باستخدام اضافة auth_socket بدلا من كلمة المرور. توفر هذه الإضافة أمانًا أكبر وسهولةً في الاستخدام في حالات متعددة، لكنها تضيف بعض التعقيد عند حاجتك للسماح لبرنامج خارجي بالوصول للمستخدم (مثل phpMyAdmin).

حتى تتمكن من الدخول إلى MySQL كمستخدم رئيسي باستخدام كلمة مرور، تحتاج لتبديل طريقة المصادقة من auth_socket إلى mysql_native_password. للقيام بذلك افتح شاشة اوامر MySQL:

 $ sudo mysql

ثم تأكد من طريقة المصادقة لكل حساب مستخدم MySQL باستخدام الأمر التالي:

mysql> SELECT user,authentication_string,plugin,host FROM mysql.user;

المخرجات:

+------------------+-------------------------------------------+-----------------------+-----------+

| user             | authentication_string                     | plugin                | host      |

+------------------+-------------------------------------------+-----------------------+-----------+

| root             |                                           | auth_socket           | localhost |

| mysql.session    | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | mysql_native_password | localhost |

| mysql.sys        | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | mysql_native_password | localhost |

| debian-sys-maint | *CC744277A401A7D25BE1CA89AFF17BF607F876FF | mysql_native_password | localhost |

+------------------+-------------------------------------------+-----------------------+-----------+

4 rows in set (0.00 sec)

يمكنك ملاحظة أن المستخدم المسؤول في هذا المثال يستخدم المصادقة بواسطة إضافة auth_socket. لإعداد حساب المستخدم المسؤول ليستخدم المصادقة بكلمة المرور استخدم أمر ALTER USER التالي. تأكد من تغيير password إلى كلمة مرور قوية من اختيارك وتذكر أن هذا الأمر سيغير كلمة المرور التي وضعتها في خطوة 2:

mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';

ثم نفذ الأمر FLUSH PRIVILEGES الذي يوجه الخادم بإعادة تحميل جداول الصلاحيات كي تعمل التغييرات التي قمت بها:

mysql> FLUSH PRIVILEGES;

تحقق من طريقة المصادقة لكل حساب مستخدم مجددا للتأكد من أن المستخدم المسؤول لم يعد يستخدم المصادقة باستخدام إضافة auth_socket:

mysql> SELECT user,authentication_string,plugin,host FROM mysql.user;

المخرجات:

+------------------+-------------------------------------------+-----------------------+-----------+

| user | authentication_string | plugin| host  |

+------------------+-------------------------------------------+-----------------------+-----------+

| root | *3636DACC8616D997782ADD0839F92C1571D6D78F | mysql_native_password | localhost |

| mysql.session| *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | mysql_native_password | localhost |

| mysql.sys| *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | mysql_native_password | localhost |

| debian-sys-maint | *CC744277A401A7D25BE1CA89AFF17BF607F876FF | mysql_native_password | localhost |

+------------------+-------------------------------------------+-----------------------+-----------+

4 rows in set (0.00 sec)

يمكنك رؤية أن المستخدم المسؤول أصبح يستخدم المصادقة بكلمة المرور في هذا المثال. بعد أن تتأكد من ذلك يمكنك الخروج من شاشة اوامر MySQL:

mysql> exit

بدلا من ذلك؛ قد يفضل البعض استخدام مستخدم خصص للاتصال ب MySQL. لِإنشاء مستخدم مخصص افتح شاشة اوامر MySQL مجددا:

 $ sudo mysql

ملاحظة: إن كنت تستخدم المصادقة بكلمة المرور للمستخدم المسؤول كما تم الشرح سابقا؛ فستحتاج لاستخدام أمر آخر للدخول إلى شاشة اوامر MySQL. سيقوم الأمر التالي بتشغيل عميل MySQL مع صلاحيات مستخدم عادي. وستحصل على صلاحيات المسؤول فقط عن طريق المصادقة:

 $ mysql -u root -p

بعد ذلك قم بإنشاء مستخدم بكلمة مرور قوية:

mysql> CREATE USER 'sammy'@'localhost' IDENTIFIED BY 'password';

ثم امنح الصلاحيات المناسبة للمستخدم الجديد. كمثال، يمكنك منح المستخدم صلاحيات لجميع جداول قاعدة البيانات، بالإضافة إلى إمكانية إضافة وتعديل وحذف صلاحيات المستخدمين باستخدام الأمر التالي:

mysql> GRANT ALL PRIVILEGES ON *.* TO 'sammy'@'localhost' WITH GRANT OPTION;

لاحظ أنك لا تحتاج في هذه النقطة لاستخدام أمر FLUSH PRIVILEGES مرة أخرى. تحتاج لاستخدام هذا الأمر فقط عند تعديل جداول منح الصلاحيات باستخدام أحد الأوامر INSERT، أو UPDATE، أو DELETE. ولأنك قمت بإنشاء مستخدم هنا عوضا عن تعديل مستخدم موجود فإن الأمر FLUSH PRIVILEGES غير مهم هنا.

أغلق شاشة اوامر MySQL:

mysql> exit

وأخيرا نختبر صحة تثبيت MySQL.

خطوة 4 - اختبار MySQL

يجب أن يبدأ MySQL بالعمل تلقائيا بغض النظر عن الطريقة التي قمت بتثبيته بها. للتحقق من ذلك، نقوم بفحص حالته.

 $ systemctl status mysql.service

سترى مخرجات شبيهة بالمخرجات التالية:

● mysql.service - MySQL Community Server

   Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: en

   Active: active (running) since Wed 2018-04-23 21:21:25 UTC; 30min ago

 Main PID: 3754 (mysqld)

    Tasks: 28

   Memory: 142.3M

      CPU: 1.994s

   CGroup: /system.slice/mysql.service

           └─3754 /usr/sbin/mysqld

إن كان MySQL لا يعمل فيمكنك تشغيله باستخدام الأمر sudo systemctl start mysql.

للمزيد من الفحص يمكنك محاولة الاتصال بقاعدة البيانات باستخدام أداة mysqladmin والتي تعتبر أداة عميل تتيح لك تنفيذ أوامر ذات صلاحيات مسؤول. كمثال، الأمر التالي يتصل ب MySQL كمستخدم مسؤول ("‎-u root") ويطلب كلمة مرور ("‎-p") ويعرض الإصدار.

 $ sudo mysqladmin -p -u root version

يجب أن ترى مخرجات كالتالي:

mysqladmin  Ver 8.42 Distrib 5.7.21, for Linux on x86_64
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Server version      5.7.21-1ubuntu1
Protocol version    10
Connection      Localhost via UNIX socket
UNIX socket     /var/run/mysqld/mysqld.sock
Uptime:         30 min 54 sec

Threads: 1  Questions: 12  Slow queries: 0  Opens: 115  Flush tables: 1  Open tables: 34  Queries per second avg: 0.006

هذا يعني أن MySQL يعمل.

الخلاصة

الآن أصبح لديك الإعداد الأولي ل MySQL على الخادم الخاص بك. هنا بعض الأمثلة على بعض الخطوات اللاحقة:

• إعداد بعض الميزات الأمنية الاضافية
• نقل مجلد البيانات

ترجمة -وبتصرف- للمقال How To Install MySQL on Ubuntu 18.04 لصاحبه الكاتب Mark Drake.

يهدف هذا الدليل إلى أن يكون مصدرًا ونقطة بداية لتشخيص واستكشاف مشاكل وإعدادات MySQL للتمهيد لحلها. سنتناول بعضًا من المشاكل التي يتعرّض لها كثيرون من مستخدمي MySQL ونوفّر إرشادات لكيفية تشخيصها وحلها. سيشتمل الدليل كذلك على روابط لمقالات من أكاديميّة حسوب ومن التوثيق الرسمي للاستفادة منها.

• كيف تعرض سجلّات الأخطاء وتشخّص الاستعلامات في MySQL
• كيف تسمح بالاتّصال عن بعد بقاعدة بيانات MySQL
• كيف تعالج انهيار خادوم MySQL
• كيف تصلح الجداول المعطوبة في MySQL
• كيف تشخّص أخطاء المقابس Sockets في MySQL

يُدير خادوم MySQL الاتّصالات القادمة إلى قاعدة البيانات اعتمادًا على ملفّ مقبس Socket، وهو نوعٌ خاصّ من الملفّات يسهّل التواصل بين عمليّات Processes مختلفة. يحمل الملفّ المقبس الخاصّ بخادوم MySQL اسمَ mysqld.sock ويوجد عادةً - بالنسبة للخواديم العاملة بتوزيعة أوبنتو – في المجلّد ‎/var/run/mysqld/‎. تُنشئ خدمة MySQL هذا الملفّ تلقائيّا.

تتسبّب التعديلات على نظام التشغيل أو على إعدادات MySQL في عدم تمكّن MySQL من قراءة الملفّ المقبس، ممّا يمنع الوصول إلى قواعد البيانات على الخادوم. يظهر خطأ المقبس الشائع على النحو التالي:

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)

توجد بضعة أمور تتسبّب في الخطأ أعلاه، وخيّارات محدودة لإصلاحه.

أحد الأسباب الشائعة هي توقيف خدمة MySQL أو الإخفاق في تشغيلها؛ بمعنى أنّ الخدمة لم تستطع إنشاء الملفّ المقبس ابتداءً. حاول تشغيل الخدمة بالأداة systemctl لمعرفة ما إذا كان هذا هو سبب ظهور الخطأ:

sudo systemctl start mysql

ثم حاول الوصول إلى سطر أوامر MySQL من جديد. تأكّد - إن استمرّ الخطأ في الظهور – من المسار الذي يبحث فيه MySQL عن الملفّ المقبس. يمكن معرفة هذا المسار من خلال ملفّ الإعداد mysqld.cnf:

sudo nano /etc/mysql/mysql.conf.d/mysql.cnf

ابحث عن المُعامل socket ضمن المقطع [mysqld] من الملفّ. يبدو المقطع المذكور كالتالي:

. . .
[mysqld]
user            = mysql
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
port            = 3306
. . .

أغلق الملفّ ثم تأكّد من وجود الملفّ mysqld.sock بتنفيذ الأمر ls على المسار الذي يتوقّع MySQL وجودَ الملفّ فيه:

ls -a /var/run/mysqld/

إذا كان الملفّ المقبس موجودًا فسيظهر في مُخرجات الأمر:

.  ..  mysqld.pid  mysqld.sock  mysqld.sock.lock

إذا لم يكن الملفّ موجودًا فربما يكون السبب هو أنّ MySQL يحاول إنشاءه دون أن تكون لديه الصلاحيّات المناسبة لذلك. يمكن التأكّد صحّة الصلاحيّات بتغيير ملكيّة المجلّد إلى المستخدم والمجموعة mysql:

sudo chown mysql:mysql /var/run/mysqld/

ثم تأكّد بعد ذلك من أنّ المستخدم mysql لديه الصلاحيّات المناسبة على المجلّد. تصلُح الصلاحيّات 755 لأغلب الحالات:

sudo chmod -R 755 /var/run/mysqld/

أخيرًا؛ أعد تشغيل MySQL لترى ما إذا كان يستطيع إنشاء الملفّ المقبس من جديد:

sudo systemctl restart mysql

ثم حاول الوصول إلى سطر أوامر MySQL. إن استمرّ خطأ المقبس في الظهور، فقد يشير ذلك إلى وجود مشكلة أعمق في خادوم MySQL، وفي هذه الحالة تجب مراجعة سجلّات الأخطاء بحثًا عن ما يقود إلى معرفة سبب المشكلة.

ترجمة – بتصرّف – للمقال How To Troubleshoot Socket Errors in MySQL لصاحبه Mark Drake.

اقرأ أيضًا

• كيف تعرض سجلّات الأخطاء وتشخّص الاستعلامات في MySQL
• كيف تسمح بالاتّصال عن بعد بقاعدة بيانات MySQL
• كيف تعالج انهيار خادوم MySQL
• كيف تصلح الجداول المعطوبة في MySQL

يهدف هذا الدليل إلى أن يكون مصدرًا ونقطة بداية لتشخيص واستكشاف مشاكل وإعدادات MySQL للتمهيد لحلها. سنتناول بعضًا من المشاكل التي يتعرّض لها كثيرون من مستخدمي MySQL ونوفّر إرشادات لكيفية تشخيصها وحلها. سيشتمل الدليل كذلك على روابط لمقالات من أكاديميّة حسوب ومن التوثيق الرسمي للاستفادة منها.

• كيف تعرض سجلّات الأخطاء وتشخّص الاستعلامات في MySQL
• كيف تسمح بالاتّصال عن بعد بقاعدة بيانات MySQL
• كيف تعالج انهيار خادوم MySQL
• كيف تصلح الجداول المعطوبة في MySQL
• كيف تشخّص أخطاء المقابس Sockets في MySQL

يحدُث أحيانًا أن تُصاب جداول MySQL بأعطاب سببها أخطاء أدّت إلى استحالة قراءة البيانات الموجودة بها. تؤدّي محاولة قراءة بيانات من جداول معطوبة – عادةً - إلى انهيّار الخادوم.

في ما يلي بعضٌ من الأسباب الشائعة لعطب الجداول:

• توقّف خادوم MySQL أثناء عمليّة كتابة في جدول.
• التعديل، من طرف برنامج خارجي، على جدول يُعدِّل عليه الخادوم في نفس الوقت.
• إيقاف الخادوم دون سابق إنذار.
• إخفاق في عتاد الحاسوب.
• وجود علّة في برمجة MySQL.

إذا كنت تظنّ أنّ أحد جداول قاعدة البيانات معطوب، فيجب أخذ نسخة احتيّاطيّة من مجلّد البيانات قبل تشخيص عطب الجدول أو محاولة إصلاحه.يُساعد هذا الإجراء في التقليل من خطر فقد البيانات. أول ما يجب عليك فعلُه هو إيقاف خدمة MySQL:

sudo systemctl stop mysql

ثم نقل جميع بيانات MySQL إلى مجلّد جديد. يوجد المجلّد المبدئي لبيانات MySQL بالنسبة للخواديم العاملة بتوزيعة أوبونتو في المسار ‎/var/lib/mysql/‎:

cp -r /var/lib/mysql /var/lib/mysql_bkp

تصبح جاهزًا – بعد إنشاء نسخة احتيّاطيّة – للبدء في التحقيق لمعرفة ما إذا كان الجدول معطوبًا أم لا. إذا كان الجدول يستخدم محرّك التخزين (MyISAM) فيمكن التحقّق من عطب الجدول باستخدام التعليمة CHECK TABLE من سطر أوامر MySQL:

CHECK TABLE table_name;

ستظهر رسالة في مُخرجات الأمر تخبرك ما إذا كان الجدول معطوبًا أم لا. إذا كان جدول MyISAM معطوبًا فيمكن عادةً إصلاحه بتنفيذ التعليمة REPAIR TABLE:

REPAIR TABLE table_name;

ستُظهرمخرجات التعليمة - بافتراض أنّ عمليّة الإصلاح ناجحة – رسالة تشبه التالي:

+--------------------------+--------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+--------------------------+--------+----------+----------+
| database_name.table_name | repair | status | OK |
+--------------------------+--------+----------+----------+

أما إذا لم تنجح العمليّة وبقي الجدول معطوبًا فإنّ توثيق MySQL يقترح بضعة طرق بديلة من أجل إصلاح الجداول المعطوبة. بالنسبة للجداول التي تستخدم محرّك التخزين InnoDB، فعمليّة الإصلاح مختلفة. بدأت قواعد بيانات MySQL باستخدام محرّك InnoDB - الذي يتميّز بتوفّرعمليّات التحقّق التلقائي من الأعطال وإصلاحها - مبدئيًّا منذ الإصدر 5.5. يتحقّق InnoDB من وجود أعطال في الصفحات بحساب مجموع تحقّق (Checksum) لكلّ صفحة يقرأها؛ وفي حال وجود اختلاف بين مجموعات التحقّق فإنّه يوقف خادوم MySQL تلقائيّا.

نادرًا مّا توجد حاجة لإصلاح الأعطاب في الجداول التي تستخدم InnoDB، إذ أنّ InnoDB لديه آليّة يمكنها حلّ أغلب الأعطاب عند إعادة تشغيل الخادوم. على الرغم من ذلك، ينصح توثيق MySQL باستخدام طريقة "الطرح وإعادة التحميل" Dump and reload. يعني هذا إعادة الوصول إلى الجدول المعطوب باستخدام الأداة mysqldump، لإنشاء نسخة احتيّاطيّة منطقيّة من الجدول يُحتَفظ فيها ببُنيته وبياناته؛ ثم إعادة تحميل الجدول إلى قاعدة البيانات. حاول إعادة تشغيل خدمة MySQL لترى إن كنت ستتمكّن من الوصول إلى الخادوم، مع استحضار آلية عمل طريقة "الطرح وإعادة التحميل:

sudo systemctl restart mysql

إنْ استمرّ انهيّار الخادوم أو استحال الوصول إليه لسبب آخر، فربّما يكون من المفيد تفعيل الخيّار force_recovery ضمن عدادات InnoDB. يمكن فعلُ ذلك بسهولة عبر تحرير الملفّ mysqld.cnf:

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

أضف السّطر التالي في المقطع [mysqld] من الملفّ:

. . .
[mysqld]
. . .
innodb_force_recovery=1

احفظ الملفّ ثم أغلقه، ثم جرّب إعادة تشغيل MySQL من جديد. إذا استطعت النجاح في الوصول إلى الجدول المعطوب فاستخدم الأداة mysqldump لطرح بيانات الجدول في ملفّ جديد. يمكنك تسميّة الملفّ بما يحلو لك. في ما يلي اخترنا الاسم out.sql:

mysqldump database_name table_name > out.sql

الخطوة المواليّة هي حذف الجدول من قاعدة البيانات. يمكنك استخدام الصيغة التاليّة لتفادي فتح سطرأوامر MySQL من جديد:

mysql -u user -p --execute="DROP TABLE database_name.table_name"

أخيرًا، استعد الجدول بالاعتماد على ملفّ الطرح الذي أنشاته للتو:

mysql -u user -p < out.sql

فليكن في علمك أن محرّك التخزين InnoDB – في العموم – أكثر مقاومةً للأخطاء من محركّ التخزين القديم MyISAM. لا يعني هذا أنّ جداول InnoDB غير معرَّضة للأخطاء، لكن وجود ميزات التغلّب على الأخطاء تلقائيّا يجعل احتمال عطب الجداول وما ينتج عنه من انهيّارات أقلّ بكثير.

ترجمة - بتصرّف - للمقال How To Fix Corrupted Tables in MySQL لصاحبه Mark Drake.

اقرأ أيضًا

• المقال التالي: كيف تشخّص أخطاء المقابس Sockets في MySQL

يهدف هذا الدليل إلى أن يكون مصدرًا ونقطة بداية لتشخيص واستكشاف مشاكل وإعدادات MySQL للتمهيد لحلها. سنتناول بعضًا من المشاكل التي يتعرّض لها كثيرون من مستخدمي MySQL ونوفّر إرشادات لكيفية تشخيصها وحلها. سيشتمل الدليل كذلك على روابط لمقالات من أكاديميّة حسوب ومن التوثيق الرسمي للاستفادة منها.

• كيف تعرض سجلّات الأخطاء وتشخّص الاستعلامات في MySQL
• كيف تسمح بالاتّصال عن بعد بقاعدة بيانات MySQL
• كيف تعالج انهيار خادوم MySQL
• كيف تصلح الجداول المعطوبة في MySQL
• كيف تشخّص أخطاء المقابس Sockets في MySQL

يعدّ التوقّف عن العمل، أو الإخفاق في تشغيل الخدمة بسبب نقص حجم الذاكرة، أكثرَ أسباب الانهيّارات التي تواجهها MySQL شيوعا. ستحتاج – من أجل التحقّق من الأمر – إلى مراجعة سجل أخطاء MySQL بعد الانهيار.

أوّلًا، ابدأ بمحاولة تشغيل خادوم MySQL بكتابة الأمر:

sudo systemctl start mysql

ثم راجع سجلّات الأخطاء لمعرفة سبب انهيّار MySQL. يمكن استخدام الأمر less لمراجعة السجلّات صفحةً صفحة:

sudo less /var/log/mysql/error.log

رسالتا الخطأ Out of memory و mmap can’t allocate من الرسائل الشائعة التي تنمّ عن نقص في الذاكرة. في ما يلي بعضٌ من الحلول المحتملة لنقص حجم الذاكرة:

• تحسين إعدادات MySQL. الأداة مفتوحة المصدر MySQLtuner رائعة لهذا الغرض. يؤدّي تنفيذ سكربت MySQLtuner إلى إظهار مجموعة من التعديلات الموصى بإجرائها على ملف إعدادات MySQL‏ (mysqld.cnf). يُرجى الانتباه إلى أنّه كل ما طالت مدّة تشغيل خادوم MySQL قبل استخدام MySQLTuner كانت اقتراحات السكريبت أحسن. استخدم حاسبة MySQL هذه لتقدير حجم الذاكرة المطلوب بالنسبة لكلّ من إعداداتك الحاليّة وتلك التي يقترحها MySQLtuner.
• التقليل من اعتماد تطبيق الويب على MySQL في تنزيل الصفحات. إضافة نظام تخزين مؤقّت (Cache) إلى التطبيق هو الوسيلة المعتادة لهذا الأمر. من أمثلة استخدام هذه الطريقة نظامُ Joomla الذي يتضمّن وظيفة تخبئة مُضمَّنة يمكن تفعيلها، والإضافة WP Super Cache التي تضيف خاصيّة التخبئة إلى ووردبريس.
• الترقيّة إلى خادوم افتراضي ذي قدرات أكبر. يُنصَح بخادوم لا تقلّ ذاكرة الوصول العشوائي فيه عن 1GB مهما كانت نوعيّة استخدام قاعدة البيانات MySQL، إلّا أنّ حجم البيانات ونوعيّتها يمكن أن يؤثر كثيرًا على متطلّبات الذاكرة. يُرجى الانتباه إلى أنّ ترقيّة الخادوم، رغم أنّها من المرجّح أن تحلّ المشكلة، إلّا أنه حلّ لا يُنصَح به إلّا بعد التحقّق من الخيّارات الأخرى ومعرفة احتمالات نجاحها. تكلّف إضافة قدرات أكبر إلى الخادوم سعرًا أعلى ويجدر بك اعتماد هذا الخيّار إلّا إذا كان الحلّ الأمثل في نهاية المطاف. انتبه أيضًا إلى أنّ توثيق MySQL يتضمّن اقتراحات أخرى لتشخيص الانهيّارات ومنع حدوثها.

ترجمة – بتصرّف – للمقال How To Address Crashes in MySQL لصاحبه Mark Drake.

اقرأ أيضًا

• المقال التالي: كيف تصلح الجداول المعطوبة في MySQL

يهدف هذا الدليل إلى أن يكون مصدرًا ونقطة بداية لتشخيص واستكشاف مشاكل وإعدادات MySQL للتمهيد لحلها. سنتناول بعضًا من المشاكل التي يتعرّض لها كثيرون من مستخدمي MySQL ونوفّر إرشادات لكيفية تشخيصها وحلها. سيشتمل الدليل كذلك على روابط لمقالات من أكاديميّة حسوب ومن التوثيق الرسمي للاستفادة منها.

• كيف تعرض سجلّات الأخطاء وتشخّص الاستعلامات في MySQL
• كيف تسمح بالاتّصال عن بعد بقاعدة بيانات MySQL
• كيف تعالج انهيار خادوم MySQL
• كيف تصلح الجداول المعطوبة في MySQL
• كيف تشخّص أخطاء المقابس Sockets في MySQL

تبدأ الكثير من المواقع والتطبيقات بوضع خادوم الويب وقاعدة البيانات على نفس النظام؛ إلّا أنّ هذا الإعداد يصبح مع الوقت ثقيلًا وغير قابل للتوسيع.. من الشائع التغلّب على هذا الإشكال بفصل الوظيفتيْن بضبط قاعدة بيانات منفصلة ممّا يسمح لخادوم الوِيب وقاعدة البيانات بالنموّ على الأجهزة الخاصّة بها، كلّ حسب حاجته. يعدّ الإعداد المبدئيّ لـ MySQL الذي لا يسمح سوى بالاتّصالات القادمة من نفس الجهاز، وبالتالي يمنع الاتّصالات القادمة من نظام آخر، أحد المشاكل الشائعة التي يواجهها المستخدمون أثناء محاولة إعداد الوصول إلى MySQL عن بعد. يمكن تعديل السلوك المبدئيّ لـ MySQL بتحرير الملف mysqld.cnf:

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

انتقل إلى السطر الذي يبدأ بالتوجيه bind-address. يبدو الملفّ على النحو التالي:

. . .
lc-messages-dir = /usr/share/mysql
skip-external-locking
#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address            = 127.0.0.1
. . .

يأخذ التوجيه مبدئيًّا القيمة 127.0.0.1، ممّا يعني أنّ الخادوم سيبحث عن الاتّصالات المحليّة فقط. ستحتاج لتعديل هذه القيمة لتكون عنوان IP خارجي (عنوان الجهاز الذي تريد التواصل معه). كما يمكن استخدام حرف بدل (Wildcard) لأغراض التشخيص والبحث عن الأخطاء. مثلًا: *، أو ::، أو 0.0.0.0:

. . .
lc-messages-dir = /usr/share/mysql
skip-external-locking
#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address            = 0.0.0.0
. . .

ملحوظة: لا يحتوي الملف mysqld.cnf في الإصدار 8 وما بعده من MySQL – مبدئيًّا – على التوجيه bind-address؛ لذا تجب إضافة التوجيه أسفل آخر سطر من الملفّ على النحو التالي:

[mysqld]
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
datadir         = /var/lib/mysql
log-error       = /var/log/mysql/error.log
bind-address            = 0.0.0.0

احفظ الملفّ بعد تعديله، ثم أغلقه وأعد تشغيل خدمة MySQL:

sudo systemctl restart mysql

حاول بعد ذلك الوصول إلى قاعدة البيانات من جهاز آخر كما يلي (حيث database_server_ip عنوان IP الخاص بخادوم قاعدة البيانات):

mysql -u user -h database_server_ip -p

إن استطعت الاتّصال بقاعدة البيانات، فهذا يؤكّد أن التوجيه bind-address كان السبب في تعذّر الاتّصال عن بعد بقاعدة البيانات. يُرجى ملاحظة أن القيمة 0.0.0.0 غير آمنة وتتيح الاتّصال بقاعدة البيانات من جميع عناوين IP. أمّا إن لم تستطع الاتّصال بقاعدة البيانات بعد ضبط إعداد bind-address، فهذا يعني أن السبب أمر آخر. قد ترغب - في الحالتين – في التعرّف على كيفيّة إعداد اتّصال بعيد أكثر أمانًا، والتي يشرحها المقال كيفية إعداد قاعدة بيانات بعيدة لتحسين أداء موقع يستخدِم MySQL.

ترجمة – بتصرّف – للمقال How To Allow Remote Access to MySQ لصاحبه Mark Drake.

اقرأ أيضًا

• المقال التالي: كيف تعالج انهيار خادوم MySQL

يهدف هذا الدليل إلى أن يكون مصدرًا ونقطة بداية لتشخيص واستكشاف مشاكل وإعدادات MySQL للتمهيد لحلها. سنتناول بعضًا من المشاكل التي يتعرّض لها كثيرون من مستخدمي MySQL ونوفّر إرشادات لكيفية تشخيصها وحلها. سيشتمل الدليل كذلك على روابط لمقالات من أكاديميّة حسوب ومن التوثيق الرسمي للاستفادة منها.

فهرس مقالات السلسلة:

• كيف تعرض سجلّات الأخطاء وتشخّص الاستعلامات في MySQL
• كيف تسمح بالاتّصال عن بعد بقاعدة بيانات MySQL
• كيف تعالج انهيّار خادوم MySQL
• كيف تصلح الجداول المعطوبة في MySQL
• كيف تشخّص أخطاء المقابس Sockets في MySQL

يمكن في كثير من الأحيان التعرّف على السبب وراء بطء، أو انهيار، أو أي سلوك غير متوقّع من قاعدة قاعدة البيانات MySQL بتحليل سجلات الأخطاء. توجد هذه السجلات مبدئيًّا في المسار ‎/var/log/mysql/error.log‎، بالنسبة للخواديم التي تعمل بتوزيعة أوبونتو. يعدّ برنامج less، وهو أداة تعمل على سطر الأوامر تتيح قراءة الملفّات دون إمكانيّة تعديلها، الوسيلة الأسهل في كثير من الأحيان، لقراءة سجلات الأخطاء:

$ sudo less /var/log/mysql/error.log

يمكن الحصول على معلومات أكثر عن مصدر المشاكل في حال سلوك غير متوقّع من MySQL عبر تنفيذ الأمر أعلاه وتشخيص الخطأ بناءً على محتويات السجلّات.

تشخيص استعلامات MySQL

قد يواجه المستخدمون، فور بدئهم في تنفيذ الاستعلامات Queries، مشاكل بطء في قاعدة بيانات MySQL. يجب عند تنفيذ استعلامات في بعض من أنظمة قواعد البيانات، بما فيها MySQL، إنهاء الاستعلام بفاصلة منقوطة (;) من أجل إتمام الاستعلام؛ كما في المثال التالي :

SHOW * FROM table_name;

إن لم تضف فاصلة منقوطة في نهاية الاستعلام، فإن سطر الأوامر سيستمر في الانتقال إلى سطر جديد ما لم تكتب الفاصلة المنقوطة وتضغط بعدها على زر الإدخال Enter. قد يجد بعضٌ من المستخدمين أن استعلاماتهم بطيئة إلى حد بعيد. يعدّ تمكين سجلّ الاستعلامات البطيئة في MySQL ومراجعته. إحدى الطرق الناجعة لمعرفة سبب البطء. لفعل ذلك، افتح الملف mysqld.cnf الذي يُستخدَم لضبط خيارات خادوم MySQL. يوجد هذا الملف عادةً في المسار ‎/etc/mysql/mysql.conf.d/‎ :

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

تَنقّل في الملف إلى أن تبلغ الأسطر التالية :

. . .
#slow_query_log         = 1
#slow_query_log_file    = /var/log/mysql/mysql-slow.log
#long_query_time = 2
#log-queries-not-using-indexes
. . .

تضبُط التوجيهات المُعلّقة أعلاه (توجد علامة # قبلها) خيّارات الإعداد المبدئيّة لسجلّ الاستعلامات البطيئة. في ما يلي شرحٌ لعمل كلّ منها :

• slow-query-log: إعطاء القيمة 1 لهذا التوجيه يفعّل سجلّ الاستعلامات البطيئة.
• Slow-query-log-file: يعرّف الملفّ الذي سيسجّل فيه MySQL الاستعلامات البطيئة. يشير التوجيه في المثال أعلاه إلى الملف ‎/var/log/mysql-slow.log.
• long_query_time : يؤدي إعطاء القيمة 2 لهذا التوجيه إلى ضبط MySQL لتسجيل جميع الاستعلامات التي تتجاوز مدة تنفيذها الثانيتين.
• log_queries_not_using_indexes : يخبر MySQL أن يسجّل أي استعلام لا يستخدم فهارس Indexes ضمن الملف ‎/var/log/mysql-slow.log. ليس مفروضًا استخدامُ هذا الإعداد لكي يعمل سجل الاستعلامات البطيئة إلا أنه مفيد جدًّا في التعرّف على الاستعلامات غير الفعّالة.

انزع تعليق الأسطُر بحذف علامة # من أوّلها. سيبدو المقطع أعلاه على النحو التالي بعد نزع التعليقات:

. . .
slow_query_log = 1
slow_query_log_file = /var/log/mysql-slow.log
long_query_time = 2
log_queries_not_using_indexes
. . .

ملحوظة: إن كنت تسخدم الإصدار 8 وما تلاه من MySQL، فلن يحتوي الملف mysqld.cnf مبدئيًّا على الأسطُر المعلّقة التي تحدّثنا عنها آنفًا، لذا أضف توجيهات الإعداد أسفل الملف على النحو التالي :

. . .
slow_query_log = 1
slow_query_log_file = /var/log/mysql-slow.log
long_query_time = 2
log_queries_not_using_indexes

احفظ الملف بعد تمكين سجلّ الاستعلامات البطيئة ثم أغلقه. أعد تشغيل خدمة MySQL كما يلي :

sudo systemctl restart mysql

سيمكن بعد هذه الإعدادات العثور على الاستعلامات التي تسبّب مشاكل بطء الخادوم. استخدم الأمر less لعرض الملف كما يلي :

sudo less /var/log/mysql_slow.log

الخطوة التالية بعد فرز الاستعلامات البطيئة هي البحث عن طريقة لتحسينها؛ مقال كيف تحسّن الاستعلامات والجداول في MySQL و MariaDB مفيد لهذا الغرض. علاوة على ذلك؛ يتضمّن MySQL التعليمة EXPLAIN التي توفّر معلومات عن كيفيّة تنفيذ MySQL للاستعلامات. تقدّم هذه الصفحة من التوثيق الرسمي لـ MySQL أفكارًا حول كيفيّة استخدام EXPLAIN لتسليط الضوء على الاستعلامات غير الفعّالة.

راجع المقال أساسيّات لغة SQL من أجل التعرّف على البنية الأساسيّة لتعليمات MySQL.

ترجمة – بتصرّف – للمقالين How to Access MySQL Error Logs و How To Troubleshoot MySQL Queries لصاحبهما Mark Drake.

اقرأ أيضًا

• المقال التالي: كيف تسمح بالاتّصال عن بعد بقاعدة بيانات MySQL

نشرح طريقة تنصيب وإعداد خادم MySQL على نظام أوبنتو 18.04. مع إعداد كلمة مرور للمستخدم root؛ وإنشاء قاعدة بيانات جديدة ومستخدم جديد لديه الصلاحيات الكاملة عليها.

نوفر توثيقًا كاملًا لجميع تعليمات SQL على موسوعة حسوب:
https://wiki.hsoub.com/SQL

MySQL replication هي العمليّة التي يمكن من خلالها نسخ البيانات أوتوماتيكيًا من أحد مخدّمات قاعدة بيانات MySQL “الرئيسي” (master) إلى واحد أو أكثر من مخدّمات قواعد بيانات MySQL “التابع” (slaves). وعادةً ما تستخدم لتوزيع إمكانيّة وصول القراءة read access على مخدّمات متعددة من أجل قابلية التوسع scalability، كما يمكن أن تستخدم أيضًا لأغراض أخرى مثل تجاوز الفشل failover، أو تحليل البيانات على الـ slave من أجل عدم زيادة التحميل على الـ master. كما أنّ الـ master-slave replication هي عمليّة باتجاه واحد (من الـ master إلى slave)، تستخدم قاعدة بيانات الـ master فقط لعمليات الكتابة. بينما يمكن أن توزّع عمليات القراءة على عدّة قواعد بيانات slave. ما الذي يعنيه إذا استخدم master-slave replication كحل للتوسع، ستحتاج لأن تملك على الأقل مصدرين محدّدين للبيانات، واحد لعمليات الكتابة وآخر لعمليات القراءة.

يعمل مطوّرو MySQL عادةً على جهاز واحد فقط و ويسعون لأن يكون لديهم كامل بيئة التطوير على ذلك الجهاز. مع منطق عدم الاعتماد على شبكة أو اتصال بالإنترنت. إذا كان هناك حاجة لـ master-slave replication فلأنه على سبيل المثال، يحتاجون إلى اختبار النسخ المتماثل/التكرار replication في بيئة التطوير قبل نشر التغييرات في مكان آخر، عليهم إنشاؤئه على نفس الجهاز. إن الإعداد لمثال MySQL واحد (single MySQL instance) بسيط نوعًا ما، ونحتاج لبذل بعض الجهد الإضافي لإعداد المثال الثاني، وبعدها عملية التكرار master-slave replication.
للمتابعة في هذه الدرس خطوة بخطوة، اخترت Ubuntu Linux كنظام تشغيل مضيف (host). والأوامر الواردة هي من أجل هذا النظام. إذا أردت إعداد الـ MySQL master-slave replication الخاص بك على نظام تشغيل مختلف، ستكون بحاجة إلى القيام بتعديلات على هذه الأوامر. ومع ذلك ، المبادئ العامة لإعداد الـ MySQL master-slave replication على نفس الجهاز هي نفسها بالنسبة لجميع أنظمة التشغيل.

تثبيت أول مثالMySQL (MySQL instance)

إذا كان لديك مسبقًا مثال قاعدة بيانات MySQL واحد على جهازك، فيمكنك تجاوز هذه الخطوة.
هذه أسهل طريقة لتثبيت MySQL على نظام Ubuntu وهي بتشغيل الأوامر التالية في موجّه الطرفية (terminal prompt):

sudo apt-get install mysql-server

خلال عملية التثبيت، ستُقاطع لوضع كلمة مرور لمستخدم root لـ MySQL.

إعداد mysqld_multi

من أجل إدارة مثالية لـ MuSQL على نفس الجهاز بكفاءة، نحن بحاجة إلى استخدام mysqld_multi. الخطوة الأولى في إعداد mysqld_multi هي إنشاء مجموعتي [mysqld] منفصلتين في الملف الموجود my.cnf. المكان الافتراضي لملف my.cnf في Ubuntu هو /etc/mysql/. لذلك افتح ملف my.cnf بمحرّر نصوصك المفضّل، وسمِّ مجموعة [mysqld] الموجودة إلى [mysqld1]. هذه المجموعة المسمّاة تستخدم لتكوين أول مثال MySQL وستكوّن أيضا كمثال رئيسي. كما هو الحال في MySQL master-slave replication كل مثال يجب أن يكون له server-id فريد خاص به، أضف السطر التالي في مجموعة [mysqld1]:

server-id = 1

نظرًا لأننا بحاجة إلى مجموعة [mysqld]منفصلة لمثال MySQL الثاني ، انسخ المجموعة [mysqld1] مع كافة التهيئات الحالية، والصقها في ملف my.cnf نفسه. الآن، أعد تسمية المجموعة المنسوخة إلى [mysqld2]، وقم بإجراء التغييرات التالية في تكوين الـ slave:

server-id           = 2
port                = 3307
socket              = /var/run/mysqld/mysqld_slave.sock
pid-file            = /var/run/mysqld/mysqld_slave.pid
datadir             = /var/lib/mysql_slave
log_error           = /var/log/mysql_slave/error_slave.log
relay-log           = /var/log/mysql_slave/relay-bin
relay-log-index     = /var/log/mysql_slave/relay-bin.index
master-info-file    = /var/log/mysql_slave/master.info
relay-log-info-file = /var/log/mysql_slave/relay-log.info
read_only           = 1

لإعداد مثيل MySQL الثاني كـتابع slave ، اضبط server-id إلى 2، والتي يجب أن تكون مختلفة بالنسبة لـ master’s server-id. كلا المثالين سيُشغّلان على نفس الجهاز، اضبط منفذ المثال الثاني إلى قيمة 3307 بحيث يمتلك رقم مختلف عن المنفذ الخاص بالمثال الأول، والذي يكون افتراضيًا 3306. من أجل تفعيل هذا المثال الثاني ليستخدم نفس MySQL binaries، نحتاج لضبط قيم مختلفة للـ socket، pid-file، datadir و log_error.
نحن أيضًا بحاجة لتفعيل relay-log من أجل استخدام المثال الثاني كتابع (slave) (المتغيرات relay-log، relay-log-index و relay-log-info-file)، فضلًا عن تعيين master-info-file. أخيرًا، من أجل جعل المثال التابع للقراءة فقط read-only، ضبط المتغير read_only بقيمة 1. يجب أن تكون حذرًا معه فهو لا يمنع التغيرات بشكل كامل على الـ slave. حتى عندما تضبط read_only إلى 1، سيسمح للتحديثات فقط من المستخدمين الذين يملكون امتياز SUPER. أدخل MySQL المتغير الجديد super_read_only لمنع إجراء تغييرات من قبل مستخدمين الـ SUPER. هذا الخيار متوفر مع الإصدار 5.7.8.
بعيدًا عن مجموعات الـ [mysqld1] و [mysqld2]، نحن بحاجة إلى إضافة مجموعة جديدة [mysqld_multi] إلى ملف my.cnf.

[mysqld_multi]
mysqld     = /usr/bin/mysqld_safe
mysqladmin = /usr/bin/mysqladmin
user       = multi_admin
password   = multipass

بمجرد تثبيتنا لمثال MySQL الثاني، والقيام بتشغيلهما، سنمنح الامتيازات المناسبة للمستخدم multi_admin ليكون قادرًا على إيقاف تشغيل أمثلة MySQL.

أنشئ مجلدات جديدة من أجل مثال MySQL الثاني

في الخطوة السابقة قمنا بإعداد ملف التكوين من أجل مثال MySQL الثاني. في ملف التكوين هذا يتم استخدام مجلدين جديدين. يجب استخدام أوامر لينكس التالية من أجل إنشاء هذه المجلدات مع الامتيازات المناسبة:

mkdir -p /var/lib/mysql_slave
chmod --reference /var/lib/mysql /var/lib/mysql_slave
chown --reference /var/lib/mysql /var/lib/mysql_slave
 
mkdir -p /var/log/mysql_slave
chmod --reference /var/log/mysql /var/log/mysql_slave
chown --reference /var/log/mysql /var/log/mysql_slave

إعدادات أمنية إضافية في AppArmor

في بعض بيئات لينكس، هناك حاجة إلى إعدادات أمن AppArmor لتشغيل مثال MySQL الثاني. على الأقل هي مطلوبة في Ubuntu .
لإعداد AppArmor بشكل صحيح. حرّر ملف /etc/apparmor.d/usr.sbin.mysqld بمحرّر نصوصك المفضّل، وأضف الأسطر التالية:

/var/lib/mysql_slave/ r،
/var/lib/mysql_slave/** rwk،
/var/log/mysql_slave/ r،
/var/log/mysql_slave/* rw،
/var/run/mysqld/mysqld_slave.pid rw،
/var/run/mysqld/mysqld_slave.sock w،
/run/mysqld/mysqld_slave.pid rw،
/run/mysqld/mysqld_slave.sock w،

بعد أن تحفظ الملف، أعد تشغيل الجهاز حتى تصبح هذه التغييرات نافذة المفعول.

تثبيت مثال MySQL الثاني

يمكن اتباع عدّة نُهج مختلفة لتركيب مثال MySQL الثاني. يستخدم النهج المعروض في هذه الدورة التعليميّة نفس ثنائيات الـ MySQL كما في الأول، مع ملفات بيانات منفصلة ضرورية للتثبيت الثاني. حيث أننا قمنا بالفعل بإعداد ملف التكوين والمجلدات الضرورية والتغييرات الأمنية في الخطوات السابقة،
الخطوة الأخيرة في التثبيت للمثال الثاني لـ MySQL هي تهيئة دليل بيانات MySQL. نفذ الأمر التالي من أجل تهيئة دليل بيانات MySQL جديد:

mysql_install_db --user=mysql --datadir=/var/lib/mysql_slave

بمجرد تهيئة دليل بيانات MySQL، يمكنك بدء كل من مثالي MySQL باستخدام الخدمة mysqld_multi.

mysqld_multi start

ضع كلمة سر الـ root لمثال MySQL الثاني باستخدام الـ mysqladmin مع المضيف والمنفذ المناسبين. وضع في اعتبارك ، أنه إذا تَركت المنفذ والمضيف دون تحديد، سيتصل mysqladmin بأول مثال MySQL افتراضيًا.

mysqladmin --host=127.0.0.1 --port=3307 -u root password rootpwd

في المثال الذي في الأعلى قم بوضع “rootpwd” ككلمة سر، ولكن يستحسن استخدام كلمة سر أكثر أمنًا.

تهيئة إضافية لـ mysqld_multi

في نهاية قسم " إعداد mysqld_multi، كتبت أننا سنقدم امتيازات مناسبة للمستخدم multi_admin في وقت لاحق، والآن حان الوقت لذلك. نحن بحاجة إلى منح امتيازات هذا المستخدم في كلا المثالين، لذلك دعونا أولًا نقوم بالاتصال بالمثال الأول:

mysql --host=127.0.0.1 --port=3306 -uroot -p

عند تسجيلك الدخول، نفّذ الأمرين التاليين:

mysql> GRANT SHUTDOWN ON *.* TO 'multi_admin'@'localhost' IDENTIFIED BY 'multipass';
mysql> FLUSH PRIVILEGES;

اخرج من MySQL client، وقم بالاتصال بالمثال الثاني:

mysql --host=127.0.0.1 --port=3307 -uroot -p

عند تسجيلك الدخول، نفّذ نفس الأمرين كما في الأعلى:

mysql> GRANT SHUTDOWN ON *.* TO 'multi_admin'@'localhost' IDENTIFIED BY 'multipass';
mysql> FLUSH PRIVILEGES;

اخرج من MySQL client.

بدء كل من مثالي MySQL أوتوماتيكيًا عند الإقلاع

الخطوة الأخيرة في إعداد mysqld_multi هي التثبيت لسكريبت الإقلاع الأتوماتيكي في الـ init.d.
لفعل ذلك، أنشئ ملف جديد سمّه mysqld_multi في /etc/init.d، وامنحه الامتيازات المناسبة:

cd /etc/init.d
touch mysqld_multi
chmod +x /etc/init.d/mysqld_multi

افتح هذا الملف الجديد بمحرّر نصوصك المفضل، وانسخ السكريبت التالي:

#!/bin/sh

### BEGIN INIT INFO
# Provides:      	scriptname
# Required-Start:	$remote_fs $syslog
# Required-Stop: 	$remote_fs $syslog
# Default-Start: 	2 3 4 5
# Default-Stop:  	0 1 6
# Short-Description: Start daemon at boot time
# Description:   	Enable service provided by daemon.
### END INIT INFO
 
bindir=/usr/bin
 
if test -x $bindir/mysqld_multi
then
    mysqld_multi="$bindir/mysqld_multi";
else
    echo "Can't execute $bindir/mysqld_multi";
    exit;
fi
 
case "$1" in
    'start' )
   	 "$mysqld_multi" start $2
   	 ;;
    'stop' )
   	 "$mysqld_multi" stop $2
   	 ;;
    'report' )
   	 "$mysqld_multi" report $2
   	 ;;
    'restart' )
   	 "$mysqld_multi" stop $2
   	 "$mysqld_multi" start $2
   	 ;;
    *)
   	 echo "Usage: $0 {start|stop|report|restart}" >&2
   	 ;;
esac

أضف خدمة mysqld_multi لـ runlevels الافتراضي بالأمر التالي:

update-rc.d mysqld_multi defaults

أعد تشغيل جهازك، وتحقق من أن كلا مثالي MySQL قيد التشغيل باستخدام الأمر التالي:

mysqld_multi report

إعداد Setup master-slave replication

الآن، عندما يكون لدينا مثالي MySQL قيد التشغيل على نفس الجهاز، نقوم بإعداد المثال الأول كـ master، والثاني كـ slave. تم القيام بجزء واحد من التكوين configuration مسبقا في فصل “إعداد mysqld_multi”. التغيير الوحيد المتبقي في الملف my.cnf هو ضبط binary logging على الـ master. لفعل ذلك، حرّر ملف my.cnf مع التغيرات التالية والإضافات في المجموعة [mysqld1]:

log_bin                    	= /var/log/mysql/mysql-bin.log
innodb_flush_log_at_trx_commit  = 1
sync_binlog                	= 1
binlog-format              	= ROW

أعد تشغيل مثال MySQL الرئيسي (master MySQL instance) لتأخذ التغيرات مفعولها:

mysqld_multi stop 1
mysqld_multi start 1

من أجل اتصال الـ slave بالـ master بامتيازات تكرار replication مناسبة، يجب أن ينشأ المستخدم الجديد في الـ Master. اتصل بمثال maste باستخدام الـ MySQL clien مع المضيف والمنفذ المناسبين:

mysql -uroot -p --host=127.0.0.1 --port=3306

أنشئ مستخدم جديد من أجل التكرار replication:

mysql> CREATE USER 'replication'@'%' IDENTIFIED BY 'replication';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'replication'@'%';

اخرج من MySQL client.
نفّذ الأمر التالي من أجل إنشاء ملف نسخ احتياطي (dump) لبيانات الـ master:

mysqldump -uroot -p --host=127.0.0.1 --port=3306 --all-databases --master-data=2 > replicationdump.sql

هنا استخدمنا الخيار --master-data=2 من أجل الحصول على تعليق يحتوي على عبارة CHANGE MASTER داخل ملف النسخ الاحتياطي، ذاك التعليق يشير الى إحداثيات الـ replication في وقت النسخ الاحتياطي، وسنحتاج هذه الإحداثيات لاحقًا لتحديث معلومات الـ master في مثال الـ slave. وهنا مثال لهذا التعليق:

--
-- Position to start replication or point-in-time recovery from
--

-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001'، MASTER_LOG_POS=349;

استورد dump الذي أنشأته في الخطوة السابقة ضمن مثال الـ slave.

mysql -uroot -p --host=127.0.0.1 --port=3307 < replicationdump.sql

أخيرًا، من أجل اتصال مثال الـ slave بمثال الـ master، يجب تحديث معلومات الـ master في الـ slave مع متغيرات الاتصال المناسبة.
اتصل بمثال الـ slave باستخدام MySQL clien مع المضيف والمنفذ المناسبين:

mysql -uroot -p --host=127.0.0.1 --port=3307

نفّذ الأمر التالي لتحديث معلومات الـ master (خُذ احداثيات التكرار replication من ملف (dump) replicationdump.sql، كما شُرح سابقًا):

mysql> CHANGE MASTER TO
	-> MASTER_HOST='127.0.0.1'،
	-> MASTER_USER='replication'،
	-> MASTER_PASSWORD='replication'،
	-> MASTER_LOG_FILE='mysql-bin.000001'،
	-> MASTER_LOG_POS=349;

نفذ الأمر التالي من أجل تشغيل الـ slave:

mysql> START SLAVE;

نفذ الأمر التالي للتأكد من أن replication قيد التشغيل:

mysql> SHOW SLAVE STATUS \G

تهانينا. تم إعداد MySQL master-slave replication على نفس الجهاز لديك بنجاح.

خاتمة

وجود النسخ المتماثل master-slave مهيئًا في بيئة التطوير الخاصة بك مفيد إذا كنت بحاجة لحل للتوسع في بيئة الإنتاج. بهذه الطريقة، سيكون لديك مصادر بيانات منفصلة مُهيئة لعمليات الكتابة والقراءة وبذلك تكون قادرًا على اختبار أن كل شيء يعمل محلياً كما هو متوقع قبل النشر.
بالإضافة إلى ذلك، قد تحتاج إلى تكوينات أمثلة slave على نفس الجهاز لاختبار موازن الحمل الذي يوزّع عمليات القراءة إلى عدد من الـ slave. في هذه الحالة، يمكنك استخدام هذه الطريقة لإعداد أمثلة أخرى بتكرار كل الخطوات السابقة نفسها.

ترجمة -وبتصرّف- للمقال MySQL Master-Slave Replication on the Same Machine لصاحبه Ivan Bojovic

من الممكن أن تكون المعايرة مهمّة صعبة جدًا، وخاصّة عند العمل مع بيانات ضخمة، حيث أنّ أصغر تغيير من الممكن أن يتسبب بتغيرات غير متوقعه ( إيجابية أو سلبية) على الأداء ككل.
تتم معظم عمليات معايرة SQl database في الشركات المتوسطة والصغيرة من قبل مدير قاعدة البيانات (Database Administrator (DBA، لكن صدقني، يوجد العديد من المطوّرين بحاجة للقيام بعمليات متعلقة بالإدارة، وأكثر من ذلك أنا شاهدت أنّهم يقومون فعليًّا بعمليات الإدارة في كثير من الشركات التي تتطلع للعمل مع المطوّرين، حين يتطلب الوضع ببساطة تقنيات مختلفة لحل المشاكل، والتي يمكن أن تؤدي إلى خلاف بين زملاء العمل.
عند التعامل مع بيانات ضخمة، فإنّ التغيير الصغير قد يؤدي إلى تغيرات غير متوقعة على الأداء.

وبناء على ذلك، فإنّ هيكلية الشبكة أيضًا لها تأثير، افترض أن فريق الإدارة DBA team مع قواعد البيانات التي يديرونها يتوضع في الطابق العاشر، بينما يتوضع المطوّرين في الطابق الخامس عشر، أو حتى في بناء مختلف تحت هيكلية منفصلة تمامًا، وبالتالي من الصعب العمل المشترك بينهم بمرونة تحت هذه الظروف، سأقوم بإتمام شيئين هامين في هذه المقالة:

• تزويد المطوّرين بتقنيات معايرة لقواعد بيانات خاصة بالمطوّرين.
• شرح كيف يمكن لمطوّري ومديري قواعد البيانات العمل معًا بكفاءة.

تحسين قاعدة البيانات (في Codebase)

الفهارس

إذا كنت حديثًا في مجال قواعد البيانات أو حتى تسأل نفسك، “ماهي معايرة SQL”، فيجب أن تعلم أنّ الفهرسة هي طريقة فعّالة لمعايرة قاعدة البيانات database SQL الخاصة بك، والتي غالبًا ما يتم إهمالها خلال عملية التطوير، كمصطلح أولي: الفهرس index هو هيكلة للبيانات تسمح بتسريع عملية استخلاص هذه البيانات من جدول قاعدة البيانات، عن طريق تأمين عمليات بحث عشوائية سريعة، وطريقة فعّالة للوصول إلى السجلات المرتبة، هذا يعني أنّه حالما تقوم بإنشاء فهرس يمكنك القيام بعمليات الاستعلام والترتيب بشكل أسرع.
تستخدم الفهارس أيضًا لتعريف المفتاح الرئيسي أو فهرس فريد يضمن أنّ أي عمود آخر لن يحتوي نفس القيمة. بالطبع إنّ موضوع الفهرسة متنوع وهام ولا يمكنني إعطاءه حقه في هذا الوصف الموجز.
إذا كنت حديثًا على موضوع الفهرسة فأنا أفضل أن تستخدم هذا المخطط لهيكلة استعلاماتك.

بشكل أولي، الهدف هو فهرسة أعمدة البحث والترتيب. لاحظ أنه إذا كانت تجري بشكل مستمر عمليات إدخال INSERT أو تعديل UPDATE أو حذف DELETE، فيجب عليك الحذر من القيام بعمليات الفهرسة، حيث يمكن أن يؤدي إلى هبوط في الأداء، إذ أنّه يتطلب تعديل جميع الفهارس بعد هذه العمليات.
كذلك مديري قواعد البيانات يقومون بحذف الفهارس قبل القيام مثلًا بإدخال أكثر من مليون سجل دفعة واحدة، وذلك لتسريع عملية الإدخال، ثم يقومون بإعادة إنشاء الفهارس بعد انتهاء الإدخال، تذكر على أية حال أن حذف الفهارس يؤثر على كافة الاستعلامات على الجداول، لذلك هذه الطريقة محبذة فقط عند عملية إدخال واحدة ضخمة للبيانات.

معايرة أداء SQL Server: خطط التنفيذ

بالمناسبة، أداة خطة التنفيذ في SQL Server مفيدة لإنشاء الفهارس. مهمتها الأساسية تتمثل في الإظهار المرئي لطرق تحصيل البيانات المختارة من قبل منسق استعلام SQL Server. للحصول على خطة التنفيذ (في برنامج الإدارة SQL Server Management Studio)، قم فقط بالنقر على Include Actual Execution Plan" CTRL + M" قبل بدء تشغيل الاستعلام.
بعدئذ ،سوف يظهر إطار ثالث اسمه “Execution Plan”، حيث يمكن أن تشاهد اكتشاف فهرس مفقود، ولإنشائه قم بالنقر بالزر اليميني على execution plan وقم باختيار Missing Index Details، بكل بساطة.

معايرة استعلام SQL عن طريق تجنب حلقات الشفرة

تخيّل سيناريو يقوم فيه 1000 استعلام بإضافة بيانات على قاعدة بياناتك بشكل تسلسلي، كالتالي:

for (int i = 0; i < 1000; i++)
{
    SqlCommand cmd = new SqlCommand("INSERT INTO TBL (A,B,C) VALUES...");
    cmd.ExecuteNonQuery();
}

يجب عليك تجنب مثل هذه الحلقات في شفرتك، وكمثال سنقوم بتحويل باستخدام العبارات الفريدة INSERT UPDATE مع أسطر وقيم عدّة.

INSERT INTO TableName (A,B,C) VALUES (1,2,3),(4,5,6),(7,8,9) -- SQL SERVER 2008

INSERT INTO TableName (A,B,C) SELECT 1,2,3 UNION ALL SELECT 4,5,6 -- SQL SERVER 2005

UPDATE TableName SET A = CASE B
        WHEN 1 THEN 'NEW VALUE'
        WHEN 2 THEN 'NEW VALUE 2'
        WHEN 3 THEN 'NEW VALUE 3'
    END
WHERE B in (1,2,3)

تأكد من أن عبارة تتجنب تحديث القيم المخزنة إذا كانت تتطابق مع القيمة الموجودة. مثل هذه يمكن أن تحسّن أداء الاستعلام بشكل كبير عن طريق تعديل مئات السجلات بدل الآلاف وكمثال:

UPDATE TableName
SET A = @VALUE
WHERE
      B = 'YOUR CONDITION'
            AND A <> @VALUE -- VALIDATION

تجنّب الاستعلامات الفرعيّة المرتبطة Correlated Subqueries

الاستعلامات المرتبطة هي الاستعلامات التي تستخدم قيم من الاستعلام الأب، هذه النوع من الاستعلامات ينحو إلى العمل سجل بعد سجل row-by-row، مرة لكل سجل معاد من الاستعلام الخارجي الأب، ولذلك فإنه ينقص أداء استعلام SQl. المطوّرين الحديثين غالبًا ما يبنون استعلاماتهم على هذه الشاكلة لأنها عادة الطريقة الأسهل.
هنا تجد مثال عن الاستعلامات المرتبطة:

SELECT c.Name, 
       c.City,
       (SELECT CompanyName FROM Company WHERE ID = c.CompanyID) AS CompanyName 
FROM Customer c

بشكل خاص، المشكلة تكون بأن الاستعلام الداخلي(...SELECT CompanyName) ينفذ لكل سجل مسترجع من الاستعلام الخارجي (...SELECT c.Name)، لكن لماذا العودة إلى مرّة ثم أخرى في كل مرة يعالج فيها سجل من الاستعلام الخارجي؟
التقنية الافضل لمعايرة الأداء تكون بمعاملة الاستعلام الفرعي كعملية ربط join

SELECT c.Name, 
       c.City, 
       co.CompanyName 
FROM Customer c 
	LEFT JOIN Company co
		ON c.CompanyID = co.CompanyID

وبهذه الحالة نقوم بالمرور على جدول Company مرة واحدة ، نربطه بجدول Customer، وعندها يمكننا الحصول على القيم التي نريدها (co.CompanyName) بشكل أفضل.

الاختيار باعتدال Select Sparingly

واحدة من أهم نصائح المعايرة هو تجنب استخدام تعليمة SELECT * . وبدلًا من ذلك يجب تحديد الأعمدة التي تحتاجها فقط مره ثانية. هذا الأمر بسيط، لكن هذا الخطأ منتشر، افترض جدولًا بمئات الأعمدة وملايين السجلات فإذا كان تطبيقك يحتاج إلى عدد محدّد من الأعمدة فقط، فلا حاجة لاستجلاب جميع البيانات، لأن ذلك تضييع للمصادر
كمثال:

SELECT * FROM Employees

مقابل

SELECT FirstName, City, Country FROM Employees

إذا كنت بالفعل تحتاج إلى كل الأعمدة ، قم بتسميتهم ضمن الاستعلام، هذه ليست قاعدة، لكنها طريقة للمعايرة و لتجنب الأخطاء المستقبلية. كمثال إذا كنت تستخدم التعليمة INSERT... ...SELECT وتم تغيير الجدول عن طريق إضافة عمود جديد، عندها ربما تقع في مشكلة إذا كان هذا العمود غير مطلوبًا في الجدول الهدف وكمثال:

INSERT INTO Employees SELECT * FROM OldEmployees

Msg 213, Level 16, State 1, Line 1
Insert Error: Column name or number of supplied values does not match table definition

ولتجنب هذا الخطأ عليك تسمية كل عمود بشكل مستقل.

INSERT INTO Employees (FirstName, City, Country)
SELECT Name, CityName, CountryName
FROM OldEmployees

ملاحظة: على أية حال، هنالك بعض الحالات يكون فيها استخدام SELECT مناسبًا، كمثال من أجل الجداول المؤقتة، وهذا يقودنا الى الفقرة التالية.

استخدام الجداول المؤقتة Temporary Tables

الجداول المؤقتة عادة ما تزيد تعقيد الاستعلام، فإذا كان بالإمكان كتابة شيفرتك بطريقة بسيطة ومباشرة، فأنا أنصحك بتجنب الجداول المؤقتة. لكن إذا كنت تمتلك إجرائية مخزّنة stored procedure مع عمليات تعديل على البيانات لا يمكن إتمامها باستعلام واحد ، يمكنك استخدام الجداول المؤقتة كوسيط لمساعدتك في تحصيل النتائج النهائية.
عندما تودّ أن تقوم بعملية ضم جدول ضخم وهنالك قيود على الجدول المذكور، يمكنك تحسين أداء قاعدة البيانات عن طريق ترحيل البيانات الى جدول مؤقت، والقيام بعملية ضم ذلك الجدول، حيث ان الجدول المؤقت يحتوي على سجلات أقل من الجدول الأساسي الضخم، ولذلك ستتم عملية الضم بشكل أسرع. القرار ليس واضح بشكل دائم، ولكن هذا المثال سوف ينبهك للحالات التي يمكنك فيها استخدام الجداول المؤقتة.
تخيّل جدول الزبائن بملايين من السجلات، وعليك أن تقوم بعملية الضم لمنطقة محدّدة، يمكنك القيام بذلك عن طريق استخدام SELECT INTO ،والقيام بعملية الضم مع الجدول المؤقت.

SELECT * INTO #Temp FROM Customer WHERE RegionID = 5
SELECT r.RegionName, t.Name FROM Region r JOIN #Temp t ON t.RegionID = r.RegionID

لاحظ : مطوري SQl أيضًا يتجنبون استخدام SELECT INTO لإنشاء الجداول المؤقتة، لأن هذا الأمر يؤدي إلى قفل قاعدة بيانات tempdb database مما يمنع باقي المستخدمين من إنشاء جداول مؤقتة ، ولحسن الحظ تم التصحيح في النسخة السابعة وما بعدها. وكحل بديل عن استخدام الجداول المؤقتة يمكن استخدام الاستعلام الفرعي كجدول.

SELECT r.RegionName, t.Name FROM Region r 
JOIN (SELECT * FROM Customer WHERE RegionID = 5) AS t 
ON t.RegionID = r.RegionID

لكن انتظر، هنالك مشكلة في الاستعلام الثاني، كما تم شرحه أعلاه، يجب تضمين الأعمدة التي نحتاجها فقط (عدم استخدام * SELECT )، يجب أخذ هذا الأمر بالحسبان

SELECT r.RegionName, t.Name FROM Region r 
JOIN (SELECT Name, RegionID FROM Customer WHERE RegionID = 5) AS t 
ON t.RegionID = r.RegionID

كل هذه الطرق ستعيد نفس البيانات، لكن باستخدام الجداول المؤقتة، وكمثال يمكننا انشاء فهرس في جدول مؤقت لتحسين الأداء.
وأخيرًا، عندما تنهي عملك بالجدول المؤقت، قم بحذفه لتنظيف مصادر tempdb بدل أن تنتظر عملية الحذف الاوتوماتيكي (عندما يتم إنهاء اتصالك مع قاعدة البيانات)

DROP TABLE #temp

هل يتواجد السجل الخاص بي؟

تقنية المعايرة هذه الخاصة ب SQl متعلقة باستخدام التعليمة ()EXISTS إذا كنت تنوي تفحص فيما إذا كان السجل موجودًا استخدم ()EXISTS بدل من ()COUNT. حيث أن ()COUNT يقوم بفحص كامل الجدول، معتبرًا جميع القيم التي تطابق شروطك، بينما ()EXISTS ستوقف الفحص حالما تجد قيمة مطابقة لما تحتاجه. وهذا يفضي إلى أداء أفضل و شفرة أوضح

IF (SELECT COUNT(1) FROM EMPLOYEES WHERE FIRSTNAME LIKE '%JOHN%') > 0
 	PRINT 'YES'

مقابل

IF EXISTS(SELECT FIRSTNAME FROM EMPLOYEES WHERE FIRSTNAME LIKE '%JOHN%')
    PRINT 'YES'

معايرة قاعدة البيانات (في المكتب)

مديري ومطوري قاعدة البيانات غالبًا ما يتجادلون فيما إذا كانت القضايا التي يواجهونها متعلقة أو غير متعلقة بالبيانات. ومن خبرتي الشخصية أقدم هنا مجموعة من النصائح لكليهما كي يتمكنا من العمل بشكل أكثر فاعلية.

للمطوّرين:

1. إذا توقف تطبيقك عن العمل فجأة، فقد لايكون الأمر متعلقًا بقاعدة البيانات، كمثال ربما هنالك مشكلة بالشبكة، قم بالتحقق قليلًا قبل أن ترجع السبب إلى مدير قاعدة البيانات.
2. حتى ولو كنت بارعًا في نمذجة بيانات SQl، قم بطلب المساعدة من مدير قاعدة البيانات في المخطط العلائقي relational diagram فلديه الكثير ليقدمه لك.
3. مديري قاعدة البيانات لا يحبون التغيرات السريعة، هذا طبيعي، عليهم تحليل قاعدة البيانات بشكل كامل واكتشاف مدى تأثير أي تغيير من كافة الزوايا. تغيير بسيط في عمود يمكن أن يستغرق اسبوعًا للإنجاز هذا بسبب أن الخطأ قد يعتبر خسارة ضخمة للشركة. كن صبورًا!
4. لا تقم بالطلب من مدير قاعدة بيانات SQL بإجراء تغييرات على البيانات في بيئة العمل وقت التشغيل. إذا كنت تود الوصول الى قاعدة البيانات النشطة، يجب عليك تحمل المسؤولية عن كافة التغيرات.

لمديري قاعدة بيانات SQl:

1. إذا كنت لا تحب أن يسألك النّاس بشأن قاعدة البيانات، قم بتزويدهم بلوحة تبيّن الحالة وقت التشغيل. فالمطوّرين عادة لديهم شك بحالة قاعدة البيانات، وهذه اللوحة تساعد في توفير الوقت والجهد للجميع.
2. ساعد المطوّرين في اختبارات ضمان الجودة للبيئة. قم بتسهيل عملية محاكاة عمل المخدّم مع اختبار بسيط على بيانات حقيقية. مما يوفر الوقت بشكل كبير عليك وعليهم.
3. يقضي المطوّرين كل اليوم متعاملين مع أنظمة بمفاهيم عمل تتغير بشكل مستمر. تفهمك لذلك يجعل الأمر أكثر مرونة، ويمكنك من تجاوز بعض القواعد في اللحظات الحرجة.
4. قواعد البيانات تتطور، سيأتي اليوم الذي تحتاج فيه إلى نقل بياناتك الى إصدار جديد، حيث يعتمد المطوّرين على خصائص جديدة في كل إصدار جديد، لذلك خطط و تحسب لعملية النقل بدلًا من أن تقوم برفض هذه التغيرات.

ترجمة -وبتصرّف- للمقال SQL-Database-Performance-Tuning-for-Developers لصاحبه Rodrigo Koch

حقوق خلفية الصورة البارزة Gradient clouds background محفوظة لـ Vexels

إحدى المهارات الأساسية والمهمة التي عليك أن تتقنها هي القدرة على استيراد وتصدير قواعد البيانات، إذ تستطيع أخذ نسخ احتياطية لقواعد بياناتك وتستعيدها عند الحاجة، أو يمكنك الاستفادة منها لنقل البيانات إلى خادوم جديد أو بيئة تطوير مختلفة.
إجراء عملية تصدير من قواعد بيانات MySQL و MariaDB هو أمرٌ بسيطٌ، وسنشرحه في هذا الدرس إضافةً إلى شرح طريقة استيراد النسخة الاحتياطية.

المتطلبات المسبقة

لتصدير أو استيراد قواعد بيانات MySQL ستحتاج إلى:

• وصول إلى خادوم لينكس يستعمل قواعد بيانات MySQL أو MariaDB.
• قاعدة بيانات مع معلومات المستخدم القادر على إجراء عمليات عليها.

تصدير قاعدة البيانات

تُستعمَل الأداة mysqldump لتصدير قواعد البيانات إلى ملفات SQL نصية، ويمكن بسهولة نقل تلك الملفات كيفما تشاء.
ستحتاج إلى معرفة اسم قاعدة البيانات نفسها وإلى اسم المستخدم وكلمة المرور لحسابٍ يملك امتيازاتٍ تسمح –على الأقل– بأذونات القراءة الكاملة على قاعدة البيانات.
استعمل الأمر الآتي لتصدير قاعدة البيانات:

mysqldump -u username -p database_name > data-dump.sql

• username هو اسم المستخدم الذي يستطيع الوصول إلى قاعدة البيانات
• database_name هو اسم قاعدة البيانات التي نريد تصديرها
• data-dump.sql هو مسار الملف الذي نريد حفظ مخرجات عملية التصدير إليه

لن يعرض الأمر السابق أيّة مخرجات مرئية، لكن يمكنك النظر في محتويات الملف data-dump.sql لتتأكد من وجود ملف SQL سليم فيه باستعمال الأمر:

head -n 5 data-dump.sql

يجب أن تبدو المخرجات (التي تظهر أوّل خمسة أسطر من الملف) كما يلي، يجدر بالذكر أنَّ الملف الآتي هو ناتج عملية تصدير قاعدة بيانات باسم database_name:

-- MySQL dump 10.13  Distrib 5.7.16, for Linux (x86_64)
--
-- Host: localhost    Database: database_name
-- ------------------------------------------------------
-- Server version       5.7.16-0ubuntu0.16.04.1

إذا حدثت أيّة أخطاء أثناء عملية التصدير، فسيعرضها mysqldump على الشاشة مباشرةً.

استيراد قاعدة البيانات

لاستيراد ملف SQL مُصدَّر إلى قاعدة بيانات MySQL أو MariaDB، فعليك أولًا إنشاء قاعدة بيانات جديدة التي ستستضيف البيانات المستوردة.
سجِّل دخولك إلى عميل قواعد البيانات بحساب المستخدم root أو أي حساب آخر له امتيازات كافية لإنشاء قواعد بيانات جديدة:

mysql -u root -p

ستنتقل الآن إلى سطر الأوامر الخاص بقواعد MySQL، أنشِئ الآن قاعدة بيانات جديدة وليكن اسمها new_database:

CREATE DATABASE new_database;

يجب أن تشاهد الناتج الآتي الذي يدل على نجاح إنشائها:

Query OK, 1 row affected (0.00 sec)

اخرج الآن من عميل MySQL بكتابة الأمر exit أو بالضغط على الزرين Ctrl+D، وبعد عودتك إلى سطر الأوامر ستستطيع استيراد الملف باستخدام الأمر الآتي:

mysql -u username -p new_database < data-dump.sql

• username هو اسم المستخدم الذي يستطيع الوصول إلى قاعدة البيانات
• new_database هو اسم قاعدة البيانات التي نريد استيراد البيانات إليها
• data-dump.sql هو مسار الملف الذي نريد استيراد محتوياته

لن تظهر أية مخرجات إذا نجح تنفيذ الأمر السابق، لكن إن حدثت أخطاء خلال العملية فسيظهرها الأمر mysql على الشاشة.
يمكنك التأكد من استيراد قاعدة البيانات بتسجيل الدخول إلى عميل MySQL مجددًا وتفحص البيانات، يمكن فعل ذلك بتحديد قاعدة البيانات الجديدة عبر USE new_database ثم كتابة الأمر SHOW TABLES;‎ أو ما شابهه.

الخلاصة

أصبحت تعلم كيف تصدِّر قواعد بيانات MySQL إلى ملفات وكيف تستوردها مجددًا.
هنالك خياراتٌ أخرى متاحةٌ للأمر mysqldump التي تُعدِّل سلوك هذا الأمر عند تصدير قواعد البيانات، والتي يمكنك التعرف عليها في صفحة التوثيق الرسمي.
ترجمة –وبتصرّف– للمقال How To Import and Export Databases in MySQL or MariaDB لصاحبه Mateusz Papiernik

تمهيد

إنَّ MySQL هي أشهر نظام مفتوح المصدر لإدارة قواعد البيانات العلاقية في العالم؛ وحاولت برمجيات إدارة الحزم تقليل الجهد اللازم لتثبيت خادوم MySQL وتشغيله، لكن ما يزال عليك إجراء بعض عمليات الضبط بعد التثبيت. وأنصحك بقضاء بعض الوقت محاولًا زيادة حماية وأمان قواعد بياناتك.

تُضبَط MySQL مبدئيا لقبول الاتصالات المحلية فقط، فلو أردتَ السماح بالاتصالات «البعيدة» فمن المهم أن تكون تلك الاتصالات آمنة؛ وسنشرح في هذا الدرس كيفية السماح بالاتصالات البعيدة إلى خادوم MySQL على أوبنتو 16.04 مع تشفير SSL/TLS.

المتطلبات المسبقة

إذا أردتَ المتابعة مع هذا الدرس، فستحتاج إلى خادومَي أوبونتو 16.04، إذ سنستخدم أحدها لاستضافة خادوم MySQL وسيلعب الآخر دور العميل. عليك أيضًا إنشاء مستخدم ليس جذرًا لكنه يمتلك امتيازات الجذر عبر الأمر sudo، راجع درس الإعداد الابتدائي لخادوم أوبنتو 14.04 للمزيد من المعلومات حول الضبط المبدئي لخادومك.

يجب أن تثبّت على الجهاز الأول خادوم MySQL وتضبطه؛ يمكنك العودة إلى درستثبيت وإعداد نظامي إدارة قواعد البياناتMySQL وPostgreSQL على أوبنتو لمزيدٍ من المعلومات حول تثبيت وضبط هذه البرمجية.
أما على الجهاز الثاني، فعليك تثبيت حزمة عميل MySQL، إذ تستطيع استخدام الأمر apt لتحديث فهرس الحزم ثم تثبيت البرمجيات الضرورية وذلك بتنفيذ الأمرين الآتيين:

sudo apt-get update
sudo apt-get install mysql-client

يُفتَرَض أن يعمل خادومك وعمليك عملًا سليمًا.

التحقق من حالة تشفير SSL/TLS الراهنة على خادوم MySQL

علينا قبل البدء أن نتحقق من الحالة الراهنة لتشفير SSL/TLS على خادومنا.
سجِّل دخولك إلى خادومك MySQL عبر المستخدم root التابع لقواعد MySQL. سنستخدم في الأمر الآتي الخيار ‎-h لتحديد عنوان IP للجهاز المحلي لكي يتصل العميل باستخدام بروتوكول TCP بدلًا من استخدام ملف socket محلي؛ وهذا سيُمكننا من التحقق من حالة تشفير SSL لاتصالات TCP:

mysql -u root -p -h 127.0.0.1

ستُسأل عن كلمة مرور مستخدم root التي اخترتها أثناء عملية تثبيت خادوم MySQL؛ وبعدئذٍ ستنتقل إلى جلسة MySQL تفاعلية.

يمكننا إظهار حالة متغيرات SSL/TLS عبر كتابة التعليمة الآتية:

SHOW VARIABLES LIKE '%ssl%';

الناتج:

+---------------+----------+
| Variable_name | Value    |
+---------------+----------+
| have_openssl  | DISABLED |
| have_ssl      | DISABLED |
| ssl_ca        |          |
| ssl_capath    |          |
| ssl_cert      |          |
| ssl_cipher    |          |
| ssl_crl       |          |
| ssl_crlpath   |          |
| ssl_key       |          |
+---------------+----------+
9 rows in set (0.01 sec)

لاحظ أنَّ قيمة المتغيرين have_openssl و have_ssl تساوي DISABLED، وهذا يعني أنَّ دعم تشفير SSL مبنيٌ داخل الخادوم لكنه ليس مفعلًا بعد.
لنتحقق من حالة الاتصال الحالي للتأكد من النتائج السابقة، وذلك بإدخال:

\s

الناتج:

--------------
mysql  Ver 14.14 Distrib 5.7.17, for Linux (x86_64) using  EditLine wrapper

Connection id:      30
Current database:   
Current user:       root@localhost
SSL:         Not in use
Current pager:      stdout
Using outfile:      ''
Using delimiter:    ;
Server version:     5.7.17-0ubuntu0.16.04.1 (Ubuntu)
Protocol version:   10
Connection:      127.0.0.1 via TCP/IP
Server characterset:    latin1
Db     characterset:    latin1
Client characterset:    utf8
Conn.  characterset:    utf8
TCP port:       3306
Uptime:         3 hours 38 min 44 sec

Threads: 1  Questions: 70  Slow queries: 0  Opens: 121  Flush tables: 1  Open tables: 40  Queries per second avg: 0.005
--------------

يمكننا أن نلاحظ من الناتج السابق أنَّ الاتصال غير مشفر عبر SSL، حتى لو كنّا قد اتصلنا عبر بروتوكول TCP.

أغلق جلسة MySQL عندما تنتهي:

exit

يمكننا الآن البدء بضبط MySQL لتستعمل تشفير SSL للاتصالات القادمة إليها.

توليد شهادات ومفاتيح SSL/TLS

لتفعيل اتصالات مشفرة عبر SSL إلى MySQL، فعلينا أولًا توليد الشهادات والمفاتيح المناسبة.

هنالك أداةٌ باسم mysql_ssl_rsa_setup متوافرة مع MySQL 5.7 وما بعدها مهمتها تبسيط هذه العملية؛ وهنالك إصدار MySQL متوافق معها متوافر في أوبنتو 16.04، لذا سنستخدم هذا الأمر لتوليد الملفات اللازمة.

ستُنشَأ الملفات في مجلد ملفات MySQL الموجود في المسار ‎/var/lib/mysql؛ وعلينا السماح لعملية MySQL (أي MySQL process) بقراءة الملفات المولّدة، لذا سنُمرِّر القيمة mysql اسمًا للمستخدم الذي سيملك الملفات المولّدة:

sudo mysql_ssl_rsa_setup –uid=mysql

سيكون ناتج عملية التوليد مشابهًا لما يلي:

Generating a 2048 bit RSA private key
...................................+++
.....+++
writing new private key to 'ca-key.pem'
-----
Generating a 2048 bit RSA private key
......+++
.................................+++
writing new private key to 'server-key.pem'
-----
Generating a 2048 bit RSA private key
......................................................+++
.................................................................................+++
writing new private key to 'client-key.pem'
-----

تحقق من إنشاء الملفات الذي جرت عملية توليدها بالأمر find:

sudo find /var/lib/mysql -name '*.pem' -ls

الناتج:

   256740      4 -rw-r--r--   1 mysql    mysql        1078 Mar 17 17:24 /var/lib/mysql/server-cert.pem
   256735      4 -rw-------   1 mysql    mysql        1675 Mar 17 17:24 /var/lib/mysqlsql/ca-key.pem<^>
   256739      4 -rw-r--r--   1 mysql    mysql         451 Mar 17 17:24 /var/lib/mysqlsql/public_key.pem<^>
   256741      4 -rw-------   1 mysql    mysql        1679 Mar 17 17:24 /var/lib/mysqlsql/client-key.pem<^>
   256737      4 -rw-r--r--   1 mysql    mysql        1074 Mar 17 17:24 /var/lib/mysqlsql/ca.pem<^>
   256743      4 -rw-r--r--   1 mysql    mysql        1078 Mar 17 17:24 /var/lib/mysqlsql/client-cert.pem<^>
   256736      4 -rw-------   1 mysql    mysql        1675 Mar 17 17:24 /var/lib/mysqlsql/private_key.pem<^>
   256738      4 -rw-------   1 mysql    mysql        1675 Mar 17 17:24 /var/lib/mysqlsql/server-key.pem<^>

آخر عمود من الناتج السابق يُظهِر أسماء الملفات المولّدة، أما العمود الرابع والخامس فيؤكدان أنَّ المستخدم المالك والمجموعة المالكة هو mysql.

تُمثِّل هذه الملفات مفاتيح وشهادات لسلطة الشهادات Certificate authority (للملفات التي تبدأ بالسابقة «ca»)، ولعملية خادوم MySQL (للملفات التي تبدأ بالسابقة «server»)، ولعملاء MySQL (للملفات التي تبدأ بالسابقة «client»). إضافةً إلى ما سبق، هنالك الملفان private_key.pem و public_key.pem اللذان تستعملهما MySQL لنقل كلمة المرور بأمان دون استخدام SSL.

تفعيل اتصالات SSL الآمنة على خادوم MySQL

ستبحث الإصدارات الحديثة من MySQL عن ملفات الشهادات الملائمة ضمن مجلد بيانات MySQL عندما يبدأ الخادوم. ولهذا السبب، لن نحتاج إلى تعديل ضبط MySQL لتفعيل تشفير SSL.

كل ما علينا فعله هو إعادة تشغيل خدمة MySQL:

sudo systemctl restart mysql

بعد إعادة التشغيل، افتح اتصالًا إلى خادوم MySQL بالأمر الذي ذكرناه في بداية هذا الدرس؛ وسيحاول عميل MySQL الاتصال عبر تشفير SSL إن كان مدعومًا من الخادوم:

mysql -u root -p -h 127.0.0.1

لننظر مرةً أخرى إلى المعلومات التي طلبناها أوّل مرة، ولنلاحظ القيم المرتبطة بالمتغيرات المتعلقة بتشفير SSL:

SHOW VARIABLES LIKE '%ssl%';

الناتج:

+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| have_openssl  | YES             |
| have_ssl      | YES             |
| ssl_ca        | ca.pem          |
| ssl_capath    |                 |
| ssl_cert      | server-cert.pem |
| ssl_cipher    |                 |
| ssl_crl       |                 |
| ssl_crlpath   |                 |
| ssl_key       | server-key.pem  |
+---------------+-----------------+
9 rows in set (0.00 sec)

نجد أنَّ قيم المتغيرات have_openssl و have_ssl أصبحت YES بدلًا من DISABLED؛ وسنلاحظ وجود قيم مرتبطة بالمتغيرات ssl_ca و ssl_cert و ssl_key وهي أسماء ملفات الشهادات المولَّدة.
لنتحقق مجددًا من معلومات الاتصال بتنفيذ:

\s

الناتج:

--------------
. . .
SSL:            Cipher in use is DHE-RSA-AES256-SHA
. . .
Connection:      127.0.0.1 via TCP/IP
. . .
--------------

أصبح الناتج هذه المرة يشير إلى استخدام تشفير SSL لجعل اتصالنا مع الخادوم آمنًا.
لنعد إلى سطر الأوامر بالخروج من MySQL:

exit

أصبح بإمكان خادومنا الآن تشفير الاتصالات، لكن ما يزال علينا إجراء بعض الضبط الإضافي للسماح بالاتصالات البعيدة وإجبار العملاء على استخدام الاتصالات الآمنة.

ضبط الاتصالات الآمنة للاتصالات البعيدة

بعد أن أصبح تشفير SSL متاحًا على الخادوم، يمكننا البدء بضبط الوصول البعيد، وعلينا فعل ما يلي لتحقيق ذلك:

• جعل استخدام SSL ضروريًا للاتصال.
• ربط الخادوم ببطاقة شبكية عامة.
• إنشاء مستخدم MySQL للاتصالات البعيدة.
• تعديل قواعد الجدار الناري للسماح بالاتصالات الخارجية.

جعل استخدام SSL ضروريًا لإجراء الاتصال

أصبح خادوم MySQL مضبوطًا لقبول اتصالات SSL من العملاء؛ لكنه ما يزال يسمح بإجراء اتصالات غير مشفرة إذا طلب العميل ذلك.
يمكننا حل هذه المشكلة بتفعيل الخيار require_secure_transport الذي لا يسمح إلا بالاتصالات المشفرة عبر SSL أو عبر مقبس Unix محلي؛ ولعدم إتاحة الاتصال عبر المقبس المحلي إلا ضمن الخادوم نفسه، فالحل الوحيد لاتصال العملاء البعيدين هو استخدام SSL للتشفير.

عدِّل ملف ‎/etc/mysql/my.cnf باستعمال محررك النصي المفضل لتفعيل هذا الخيار:

sudo nano /etc/mysql/my.cnf

ستجد داخل الملف تعليمتَي ‎!includedir التي تستخدم لقراءة ملفات الضبط الإضافية، علينا وضع الضبط الخاص بنا تحت تلك الأسطر لتجاوز أيّة تعليمات ضبط موجودة داخل ملفات الضبط الإضافية.
ابدأ بإنشاء قسم [mysqld] لاستهداف عملية خادوم MySQL، واضبط تحت ترويسة هذا القسم الخيار require_secure_transport إلى ON:

. . .

!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mysql.conf.d/

[mysqld]
# Require clients to connect either using SSL
# or through a local socket file
require_secure_transport = ON

لن نحتاج إلى أكثر من هذا السطر لجعل استخدام الاتصالات المشفرة ضروريًا.
إنَّ خادوم MySQL مضبوطٌ مبدئيًّا للاستماع إلى الاتصالات الآتية من الحاسوب المحلي فقط، وللاستماع إلى الاتصالات البعيدة، فيمكننا ضبط قيمة التعليمة bind-address للإشارة إلى بطاقة شبكية مختلفة.

للسماح لخادوم MySQL بقبول الاتصالات من جميع البطاقات الشبكية، فيمكننا ضبط قيمة التعليمة bind-address إلى 0.0.0.0:

. . .

!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mysql.conf.d/

[mysqld]
# Require clients to connect either using SSL
# or through a local socket file
require_secure_transport = ON
bind-address = 0.0.0.0

احفظ الملف وأغلقه بعد أن تنتهي من تعديله.
أعد تشغيل خادوم MySQL لتطبيق الضبط الجديد:

sudo systemctl restart mysql

تأكد أنَّ خادوم MySQL يستمع على 0.0.0.0 بدلًا من 127.0.0.1 بكتابة:

sudo netstat -plunt

الناتج:

Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address           Foreign Address         State       PID/Program name
tcp        0      0 0.0.0.0:3306            0.0.0.0:*               LISTEN      4330/mysqld     
tcp        0      0 0.0.0.0:22              0.0.0.0:*               LISTEN      1874/sshd       
tcp6       0      0 :::22                   :::*                    LISTEN      1874/sshd

القيمة 0.0.0.0 في الناتج السابق تُشير إلى أنَّ خدمة MySQL تستمع إلى الاتصالات في جميع البطاقات الشبكية المتاحة.

علينا بعدئذٍ السماح لاتصالات MySQL عبر الجدار الناري، وذلك بإنشاء استثناء كما يلي:

sudo ufw allow mysql

الناتج:

Rule added
Rule added (v6)

يجب أن تتمكن الاتصالات البعيدة من الوصول إلى خادوم MySQL.

إنشاء مستخدم MySQL للاتصالات البعيدة

أصبح خادوم MySQL جاهزًا للاستماع إلى الاتصالات البعيدة، لكننا لا نملك حاليًا أيّ مستخدم مضبوط لكي يتصل من حاسوبٍ آخر.

سجِّل دخولك إلى خادوم MySQL عبر المستخدم root:

mysql -u root -p

يمكننا الآن إنشاء حساب للمستخدم البعيد عبر الأمر CREATE USER، وسنستخدم عنوان IP لجهاز العميل في قسم المضيف من تعليمة تعريف المستخدم الجديد لجعل الاتصال عبر هذا المستخدم محصورًا بذاك الجهاز.
ولضمان الاتصال عبر SSL حتى لو عُطِّلَ الخيار require_secure_transport مستقبلًا، فسنُحدِّد في تعليمة تعريف الحساب أنَّه يجب استعمال SSL عند الاتصال باستخدام هذا الحساب بتضمين REQUIRE SSL:

CREATE USER 'remote_user'@'mysql_client_IP' IDENTIFIED BY 'password' REQUIRE SSL;

علينا بعد ذلك أن نمنح المستخدم صلاحيات على قواعد البيانات أو الجداول التي ينبغي له الوصول إليها؛ وسنُنشِئ قاعدة بيانات لشرح هذه الفكرة باسم example وسنمنح مستخدمنا ملكيتها:

CREATE DATABASE example;
GRANT ALL ON example.* TO 'remote_user'@'mysql_client_IP';

علينا تحديث جداول الصلاحيات لتطبيق التعديلات مباشرةً:

FLUSH PRIVILEGES;

أغلِق جلسة MySQL بعد أن تنتهي من تنفيذ الأوامر السابقة:

exit

اختبار الاتصالات البعيدة

لاختبار إمكانية إجراء اتصال من جهاز العميل إلى الخادوم بنجاح، فاستخدم الخيار ‎-u لتحديد اسم المستخدم البعيد، والخيار ‎-h لتحديد عنوان IP لخادوم MySQL:

mysql -u remote_user -p -h mysql_server_IP

بعد كتابتك لكلمة المرور، فستسجل دخولك إلى الخادوم البعيد.
نفذ الأمر الآتي للتأكد من أنَّ اتصالك آمن:

\s

الناتج:

--------------
. . .
SSL:         Cipher in use is DHE-RSA-AES256-SHA
. . .
Connection:      mysql_server_IP via TCP/IP
. . .
--------------

اخرج من عميل MySQL وعد إلى سطر الأوامر:

exit

جرِّب الآن الاتصال بطريقة غير آمنة إلى الخادوم:

mysql -u remote_user -p -h mysql_server_IP –ssl-mode=disabled

بعد أن يُطلَب منك إدخال كلمة المرور، فستجد أنَّ الاتصال قد رُفِض:

ERROR 1045 (28000): Access denied for user 'remote_user'@'mysql_server_IP' (using password: YES)

هذا ما بدأنا درسنا محاولين إنجازه: فالاتصالات المشفرة عبر SSL إلى الخادوم مسموحة، والاتصالات غير المشفرة ممنوعة.
لهذا الحد، أصبح خادوم MySQL مضبوطًا للسماح بالاتصالات البعيدة الآمنة؛ ويمكنك أن تتوقف عند هذه النقطة إذا كان ذلك يلبي احتياجاتك الأمنية، لكن هنالك المزيد من الأمور التي يمكنك فعلها لزيادة الحماية.

ضبط التحقق من الاتصالات الآتية إلى خادوم MySQL

إنَّ خادوم MySQL مضبوطٌ حاليًا مع شهادة SSL موقعة من سلطة شهادات محلية (Local certificate authority)؛ والشهادة والمفتاح الخاصَين بالخادوم كافيان لتشفير الاتصالات القادمة إلى الخادوم.

لكننا لم نستفد من «الثقة» التي يمكن لسلطة الشهادات توفيرها، فعند توزيع شهادة سلطة الشهادات إلى العملاء، إضافةً إلى شهادة ومفتاح خاصَين بالعميل، فيمكن لكلا الطرفين التأكد أنَّ الشهادات موقعة من سلطة شهادات موثوقة. يمكن أن يساعد ذلك بمنع الاتصالات المزورة إلى أجهزة خبيثة.
لتطبيق هذا النوع من الحماية الإضافية، فسنحتاج إلى:

• نقل ملفات SSL الملائمة إلى جهاز العميل
• إنشاء ملف ضبط للعميل
• تعديل حساب المستخدم البعيد لطلب وجود شهادة موثوقة

نقل شهادات العميل إلى جهازه

علينا بادئ الأمر الحصول على شهادة سلطة الشهادات التابعة لخادوم MySQL مع ملفات شهادات العميل ووضعها في جهاز العميل.
سنبدأ بفعل ذلك بإنشاء مجلد باسم client-ssl في جهاز العميل في مجلد المنزل للمستخدم الذي ستستعمله للاتصال:

mkdir ~/client-ssl

ولأنَّ مفتاح الشهادة هو ملفٌ حساس، فعلينا قفل الوصول إلى المجلد لكي لا يتمكن أي شخصٍ سوى المستخدم المالك له من الوصول إليه:

chmod 700 ~/client-ssl

يمكننا الآن نسخ معلومات الشهادة إلى مجلدٍ جديد.
اعرض محتوى شهادة سلطة الشهادات في خادوم MySQL بكتابة الأمر الآتي:

sudo cat /var/lib/mysql/ca.pem

الناتج:

-----BEGIN CERTIFICATE-----

. . .

-----END CERTIFICATE-----

انسخ جميع المحتوى بما فيه الأسطر التي فيها BEGIN CERTIFICATE و END CERTIFICATE إلى الحافظة.
أنشِئ ملفًا جديدًا في جهاز العميل باستعمال الأمر الآتي:

nano ~/client-ssl/ca.pem

ألصق محتوى الشهادة المنسوخ في ذاك الملف، ثم احفظه وأغلق الملف عندما تنتهي منه.
علينا الآن عرض محتوى شهادة العميل في خادوم MySQL:

sudo cat /var/lib/mysql/client-cert.pem

الناتج:

-----BEGIN CERTIFICATE-----

. . .

-----END CERTIFICATE-----

انسخ محتوياته مجددًا، واحرص على تضمين أول وآخر سطر.
أنشِئ ملفًا جديدًا في عميل MySQL بالاسم نفسه ضمن مجلد client-ssl:

nano ~/client-ssl/client-cert.pem

ألصق محتويات الحافظة إلى الملف، ثم احفظه وأغلقه.

ثم اعرض محتويات مفتاح العميل على خادوم MySQL:

sudo cat /var/lib/mysql/client-key.pem

الناتج:

-----BEGIN RSA PRIVATE KEY-----

. . .

-----END RSA PRIVATE KEY-----

انسخ محتوياته مجددًا إلى حافظتك، واحرص على تضمين أول وآخر سطر.
أنشِئ ملفًا جديدًا في عميل MySQL بالاسم نفسه ضمن مجلد client-ssl:

nano ~/client-ssl/client-key.pem

ألصق محتويات الحافظة إلى الملف، ثم احفظه وأغلقه.
يجب يكون في جهاز العميل جميع الشهادات اللازمة للوصول إلى خادوم MySQL؛ وعلينا الآن تعديل حساب المستخدم.

تعديل حساب المستخدم البعيد لطلب وجود شهادة موثوقة

أصبح لدى عميل MySQL الملفات اللازمة للتحقق من الشهادة عند الاتصال بخادوم MySQL، لكن الخادوم ليس مضبوطًا بعد لطلب وجود شهادة للعميل من سلطة شهادات موثوقة.
لتعديل ذلك، علينا تسجيل الدخول عبر المستخدم root في خادوم MySQL:

mysql -u root -p

سنحتاج إلى تعديل المتطلبات الخاصة بالمستخدم البعيد، فبدلًا من استعمال عبارة REQUIRE SSL علينا استعمال عبارة REQUIRE X509 والتي ستُطبِّق الإجراءات الأمنية نفسها، لكنها ستتطلب أيضًا أن يملك العميل شهادةً موقعةً من سلطة شهادات يثق بها خادوم MySQL.

علينا استعمال الأمر ALTER USER لتعديل المتطلبات الخاصة بالمستخدم:

ALTER USER 'remote_user'@'mysql_client_IP' REQUIRE X509;

ثم حدِّث جداول الصلاحيات لتأخذ التعديلات مجراها مباشرةً:

FLUSH PRIVILEGES;

اخرج من سطر أوامر MySQL عندما تنتهي بالأمر:

exit

سنتأكد في الخطوة التالية أننا ما زلنا نستطيع الاتصال.

تجربة التحقق من الشهادة عند الاتصال

من المناسب الآن معرفة أنَّ كلا الطرفين سيستطيع التحقق من شهادة الآخر عند الاتصال.
لنحاول أولًا الاتصال من عميل MySQL دون توفير شهادات له:

mysql -u remote_user -p -h mysql_server_IP

الناتج:

ERROR 1045 (28000): Access denied for user 'remote_user'@'mysql_client_IP' (using password: YES)

سيرفض الخادوم الاتصال إذا لم نوفِّر شهادة للعميل.

حاول الآن الاتصال مع توفير الخيارات ‎--ssl-ca و ‎--ssl-cert و ‎--ssl-key للإشارة إلى الملفات الموافقة لها والموجودة في مجلد ‎~/client-ssl:

mysql -u remote_user -p -h mysql_server_IP --ssl-ca=~/client-ssl/ca.pem --ssl-cert=~/client-ssl/client-cert.pem –ssl-key=~/client-ssl/client-key.pem

يجب أن تستطيع تسجيل الدخول إلى خادوم MySQL بنجاح. يمكنك العودة إلى سطر الأوامر بتنفيذ:

exit

لقد تأكدنا الآن من القدرة على الوصول إلى الخادوم، لكن يمكننا تحسين قابلية الوصول إليه بإنشاء ملف ضبط.

إنشاء ملف ضبط لعميل MySQL

لتجنب تحديد ملفات الشهادات في كل مرة نتصل فيها إلى الخادوم، فيمكننا إنشاء ملف ضبط بسيط لعميل MySQL.
أنشِئ ملفا مخفيًا في مجلد المنزل في عميل MySQL باسم ‎~/.my.cnf:

nano ~/.my.cnf

أنشِئ في بداية ذاك الملف قسمًا باسم [client]، ويمكننا بعد ذلك السطر إضافة الخيارات ssl-ca و ssl-cert و ssl-key للإشارة إلى الملفات التي نسخناها من الخادوم؛ يجب أن يبدو شكل الملف كالآتي:

[client]
ssl-ca = ~/client-ssl/ca.pem
ssl-cert = ~/client-ssl/client-cert.pem
ssl-key = ~/client-ssl/client-key.pem

يُخبِر الخيار ssl-ca العميل أن يتحقق أنَّ الشهادة التي وفرها خادوم MySQL موقعةٌ من سلطة الشهادات التي أشرنا إلى شهادتها. وهذا يجعل العميل متأكدًا أنه يتصل بخادوم MySQL موثوق.

أما الخياران ssl-cert و ssl-key فيشيران إلى الملفات اللازمة لإثبات أنَّ العميل يملك شهادة موقعة من سلطة الشهادات نفسها. سنحتاج إلى ذلك إذا أردنا في خادوم MySQL التحقق أنَّ العميل موثوق من سلطة الشهادات أيضًا.
احفظ الملف وأغلقه عندما تنتهي من التعديلات عليه.

يمكنك الآن الاتصال إلى خادوم MySQL دون إضافة الخيارات ‎--ssl-ca و ‎--ssl-cert و ‎--ssl-key في سطر الأوامر:

mysql -u remote_user -p -h mysql_server_ip 

يجب أن يملك الخادوم والعميل الآن الشهادات اللازمة للاتصال بأمان، وضبطنا كلا الطرفين للتحقق أنَّ الشهادة موقعة من سلطة الشهادات الموثوقة.

الخلاصة

يجب أن يكون خادومك مضبوطًا لقبول الاتصالات الآمنة من العملاء البعيدين. وإذا اتبعتَ الخطوات اللازمة للتحقق من الشهادات عبر سلطة الشهادات فسيتأكد كلا الطرفين من وثوقية الطرف الآخر.

ترجمة –وبتصرّف– للمقال How To Configure SSL/TLS for MySQL on Ubuntu 16.04 لصاحبه Justin Ellingwood

سهّل محرك قواعد بيانات MySQL من استخدام قواعد البيانات العلاقية Relational، وألغى – إلى حدٍ ما – الخطوط التي تفصل بين قواعد بيانات SQL و NoSQL بعد أن أضاف حقلًا لنوع البيانات JSON.

في يومٍ من الأيام السالفة، كان هناك حاسوب وحيدٌ، ثم أتى أحدهم وبنى آخر، وأراد أن ينقل بعض الشفرات من الحاسوب الأول، ولهذا السبب احتجنا إلى طريقةٍ لنقل المعلومات دون اعتمادها على العتاد الذي يُشغِّلُها؛ ومن ذلك الوقت وإلى الآن انتشرت الكثير من ترميزات المحارف مثل ASCII و EBCIDC و SGML و XML …إلخ. وسطع نجمها لفترةٍ من الزمن.

ولكن في السنوات الأخيرة اشتهرت صيغة JSON (أي JavaScript Object Notation) التي تستعمل لتبادل البيانات بصيغةٍ مهيكلة.

كان نظام قواعد البيانات MySQL قبل الإصدار 5.7 يخزّن مستندًا مُصاغًا بصيغة JSON في حقلٍ نصي، لكن من الصعب البحث في السلاسل النصية الطويلة، وكتابة تعبير نمطي Regular expression للعثور على القيم ضمن تلك السلاسل سيكون أمرًا صعبًا، وإذا غيّرت قيمة من القيم المخزنة ضمن سلسلة JSON فعليك إعادة كتابة السلسلة كلها، وهذا لا يصب في صالح الأداء، لكنه ضروريٌ إذا كنت تعمل على إصدار MySQL 5.6 وما قبله.

نوع البيانات JSON في قواعد MySQL

أُضيفَ نوعٌ جديدٌ من أنواع البيانات في الإصدار MySQl 5.7 ألا وهو JSON، فقد أصبح مثل الأرقام الصحيحة، والسلاسل النصية …إلخ. وبهذا أصبحت هنالك طريقةٌ لتخزين مستند JSON كامل في عمودٍ من أعمدة جدول في قاعدة البيانات، وقد يكون حجم هذا المستند مقدرًا بالغيغابايت!

يتحقق الخادوم أنَّ القيمة المُراد تخزينها في هذا الحقل هي مستند JSON صالح، ومن ثم يحفظه بصيغةٍ ثنائية Binary صُمِّمَت خصيصًا لتسهيل البحث فيها؛ يجدر بالذكر أنَّ نوع البيانات الجديد كان السبب في تحديث نسخة MySQL العاملة في مختلف الخواديم أكثر من أيّ ميزة أخرى.

يأتي نوع البيانات الجديد مدعّمًا بأكثر من 20 دالة، وهذه الدوال تستطيع أن تستخلص أزواج «المفتاح-القيمة» Key-value من المستند، أو تحدِّث البيانات، أو توفِّر بيانات وصفية Metadata حول البيانات المخزنة، وتخرج الأعمدة التي ليست بصيغة JSON إلى صيغة JSON، والكثير…
إصدار MySQL 8 أصبح في مرحلة Milestone release وسيضيف ميزات ودوال جديد. الدالة JSON_PRETTY_PRINT ستؤدي إلى إظهار المخرجات بتنسيق يُسهِّل فهمها؛ وستُضاف تحسينات أخرى مثل إمكانية تعديل البيانات دون إعادة كتابة المستند بأكمله.

كان حفظ البيانات بصيغةٍ غير منسقة يعدّ من ميزات NoSQL، لكن الكثير من قواعد البيانات العلاقية مثل Oracle و SOL Server و PostgreSQL وغيرها قد أضافت نوع البيانات JSON، مما يُصعِّب تعريف الفرق بين قواعد بيانات NoSQL و SQL.

لماذا علينا الاهتمام بميزة Document Store

بعد أن ظهر نوع البيانات JSON بفترةٍ قصيرة، ظهرت ميزة MySQL Document Store، والتي صُمِّمَت للمبرمجين الذين لا يعرفون لغة الاستعلام البنيوية (SQL) لكنهم يريدون استخدام قواعد البيانات.

يُنشِئ المُطوِّر بنى البيانات ويُطبِّق دوال إنشاء البيانات والحصول عليها وتحديثها وحذفها (تُختَصر هذه العمليات عادةً بالاختصار CRUD) على البيانات وفقًا لحاجته باستعمال لغة البرمجة التي يشاء (مثل Java و JavaScript و Node.js و Python و ‎C++‎ وستُتاح الميزة للغات الأخرى قريبًا).

ليس من الضروري أن يعرف المُطوِّر لغةَ SQL، ولن يلقيَ بالًا إلى أنَّ لغة SQL ستُستعمَل وراء الكواليس؛ حيث ستُخزَّن البيانات في عمود JSON، وبهذه الطريقة يستطيع المطورون استخدام البيانات وحفظها دون الحاجة إلى إنشاء جداول في قاعدة البيانات، ولا إلى تضييع الوقت في التخطيط لقاعدة البيانات، ولا إلى انتظار مدير قواعد البيانات لكي يُنشِئها لهم.

يجب ألّا ننسى أنَّ أغلبية المشاريع تبدأ دون معرفة كيف ستبدو البيانات بعد إطلاق المشروع، وستتطور بنية تلك البيانات مع مرور الزمن لملائمة مختلف الظروف والحاجات.

إذا احتجنا إلى إنشاء قاعدة بيانات علاقية تقليدية من مستندات JSON، فمن السهل استخراج المفاتيح وتحويلها إلى أعمدة لكي تعمل عليها SQL القياسية، ويلي ذلك فهرسة الأعمدة الناتجة لتسريع عملية البحث فيها.

الجوانب السلبية

إنَّ صيغة JSON ممتازةٌ لتخزين البيانات بصيغةٍ «غير رسمية»، لكن قواعد البيانات العلاقية ملائمةٌ للبيانات المُقسَّمة إلى أقسام صغيرة يسهل الوصول إليها.
أضف إلى ذلك عدم القدرة على فرض معايير دقيقة لتخزين البيانات، فيمكن أن يكون اسم المفتاح الذي يُخزِّن عنوان البريد الإلكتروني email أو eMail أو e-mail أو غير ذلك؛ لكن لا أظن أنَّ هذا الأمر سيُشكِّل عائقًا أمامك إذا كنتَ مدركًا له مسبقًا.
إذا كنتَ جاهزًا لتجربة حقل JSON، فنزِّل MySQL، ونزِّل بطريقك برمجية MySQL Workbench حيث تدعم أعمدة JSON، وكلا البرمجيتين متاحتان مجانًا.

ترجمة – بتصرّف – للمقال What you need to know about JSON in MySQL لصاحبه Dave Stokes.

إن كنت مدير قواعد بيانات DBA فإنه من المحتمل جدًّا أن يكون سطرُ الأوامر هو وسيلتك للتخاطب مع قواعد البيانات التي تديرها. يستزف استخدام سطر الأوامر الوقت عندما تحتاج للاتّصال بقواعد بيانات عدّة تعمل على مضيفات متباعدة؛ استخدامُ واجهة رسوميّة مفيد في هذه الحالة.

يمكنك بالطبع تثبيت phpMyAdmin على كلّ خادوم، إلا أنك ستحتاج إلى تسجيل الدخول إلى واجهة وِب مختلفة بالنسبة لكلّ خادوم. ماذا لو كانت لديك واجهة وحيدة للاتّصال بجميع قواعد بيانات MySQL التي تديرها؟ إن كان هذا ما تبحث عنه فأنت في المكان الصّحيح.

MySQL Workbench هو واجهة رسومية موَّحدة للتعامل مع قواعد بيانات MySQL، توفّر لمعماريّي البرمجيّات Architects، المطوّرين ومديري الأنظمة آليّة فعّالة للتعامل مع قواعد بيانات متعدّدة على مضيفات متباعدة. يمكن بهذه الأداة الحصول بنظرة على معلومات عن حالة خادوم قاعدة البيانات، اتصالات العملاء ومتغيّرات الخادوم. كما تمكّنك من إدارة المستخدمين وصلاحيّاتهم، تصدير البيانات واستيرادها، تنفيذ الاستعلامات Queries، إنشاء مخطّطات Schemas جديدة وغيرها من المهامّ.

يتيح MySQL Workbench أيضًا إمكانيّة تهجير Migrate البيانات من أنظمة إدارة قواعد بيانات أخرى، مثل Microsoft SQL Server وPostreSQL إلى MySQL بسهولة ويُسر.

سنبيّن في هذا الدرس كيفية تثبيت الأداة على حاسوبك الشخصي وخطوات ينبغي تطبيقها جهةَ خواديم قواعد البيانات للسماح للأداة بالاتصال بهذه الخواديم. سنأخذ مثالا لتثبيت MySQL Workbench على حاسوب شخصي يعمل بالإصدار 17.04 من أوبونتو وجعله يتّصل بقاعدة بيانات على خادوم يعمل بأوبونتو 16.04. تنطبق نفس الخطوات على بقيّة توزيعات لينكس مع اختلافات تمليها طبيعة مدير الحزم الخاص بالتوزيعة.

تثبيت MySQL Workbench

يمكن الحصول على الأداة MySQL Workbench من صفحة التنزيل على موقع الأداة. اختر صيغة الحزمة والمعماريّة المناسبتَيْن (حزمة deb. ومعماريّة 64 بت بالنسبة لنا) ثم اطلُب تنزيل الملف (قد تحتاج لتسجيل الدخول إلى حساب أوراكل أو إنشاء حساب جديد إن لم يكن لديك واحد، الحساب والأداة مجانيّان).

انتقل بعد اكتمال التنزيل إلى المُجلَّد حيثُ نزّلت الأداة (المجلّد Downloads بالنسبة لنا):

cd ~/Downloads

ثم استخدم الأمر dpkg لتثبيت حزمة الأداة:

sudo dpkg -i mysql-workbench-community-*.deb

قد تظهر رسائل خطأ تفيد بغياب حزم تعتمد عليها الأداة MySQL Workbench (رسائل الخطأ dependency problems). يمكن حلّ هذه المشاكل بتنفيذ الأمر التالي لتثبيت الحزم المفقودة:

sudo apt-get install -f

يحلّ الأمر السابق بتثبيت الحزم التي تطلبها الأداة MySQL Workbench.

نحن الآن جاهزون لتشغيل الأداة MySQL Workbench على الحاسوب الشخصي (العميل)، ولكن قبل ذلك سنضبُط خادوم قاعدة البيانات للسماح للاتصالات البعيدة (أي التي تأتي من عملاء لا يتواجدون مع خادوم قاعدة البيانات على نفس الجهاز).

إعداد خادوم MySQL للاتصالات البعيدة

إن لم تضبُط خادوم MySQL لقبول اتصالات بعيدة فإن MySQL Workbench لن يمكنه الاتصال بقواعد البيانات الموجودة على هذا الخاودم.

يجب أولا ضبط MySQL لقبول اتصالات قادمة من عناوين غير العنوان المحلّي 127.0.0.1. سجّل الدخول إلى الخادوم - مثلا عبر SSH - ثم افتح الملف التالي لتحريره:

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

ابحث عن السطر التالي:

bind-address 127.0.0.1

وعدّله ليصبح:

bind-address 0.0.0.0

احفظ الملف ثم أغلقه.

أعد تشغيل خادوم MySQL بالأمر التالي:

sudo systemctl restart mysql.service

خادوم قاعدة البيانات جاهز الآن لتلقّي الاتصالات.

الخطوة الموالية هي تحديد المستخدمين المسموح لهم بالاتصال بخادوم قاعدة البيانات ومن أي عنوان يمكنهم ذلك. يُضبَط هذا الإعداد من محثّ الأوامر command prompt في MySQL.

ننفّذ الأمر التالي للانتقال إلى محثّ أوامر MySQL:

mysql -u root -p

ملحوظة: أبدل root باسم الحساب الإداري لديك خادوم في قاعدة البيانات.

أدخل كلمة السّر عندما تُطلَب منك، ستجد بعدها أنك أمام سطر أوامر خاص بـMySQL. سنحتاج لتنفيذ أمر يعطي حساب المدير القدرة على الاتصال بخادوم قاعدة البيانات عن بعد (عبر MySQL Workbench).

سنفترض أن عنوان الجهاز الذي ثبّتنا عليه MySQL Workbench هو 192.168.1.139 وأننا نريد السماح للمسخدم root بالاتصال بجميع قواعد البيانات الموجودة على الخادوم انطلاقا من العنوان السابق. ننفّذ الأمر التالي للحصول على النتيجة المذكورة:

GRANT ALL ON *.* TO 'root'@'192.168.1.139' IDENTIFIED BY 'PASSWORD' WITH GRANT OPTION;

حيث PASSWORD كلمة سرّ المستخدم root (أو أي مستخدم آخر تريد إعطاءه ميزة الاتصال عن بعد بالقاعدة). تأكّد من أن الأمر نُفِّذ دون أخطاء.

نفّذ الأمر:

exit;

للخروج من سطر أوامر MySQL.

يمكنك الآن الاتصال بخادوم MySQL انطلاقا من MySQL Workbench.

الاتصال بقاعدة البيانات من MySQL Workbench

شغّل التطبيق MySQL Workbench ثم توجّه إلى القائمة Databaseواختر Connect to database. تظهر النافذة التالية.

نحدّد في هذه النافذة تفاصيل الاتصال: عنوان الخادوم، المنفذ (مبدئيّا 3306) واسم المستخدم ثم اضغط على الزرّ OK. سيُطلب منك إدخال كلمة سر المستخدم.

تظهر بعد إدخال كلمة السر الصحيحة النافذة التاليّة التي يمكن من خلالها تنفيذ إجراءات مختلفة على قاعدة البيانات.

تُستخدَم الطريقة السابقة لإنشاء اتصال واحد. إن كنت تعرف أنك ستتصّل بهذا الخادوم مرارا فالأفضل أن تختار Manage Connections من القائمة Database. تظهر النافذة التالية.

انقر على الزّر New، حدّد التفاصيل المطلوبة للاتصال بقاعدة البيانات. تأكّد من إعطاء اسم للاتصال (خانة Connection name) ثم انقر على زرّ Test Connection لاختبار الاتصال. أدخل كلمة السر عندما تُطلَب منك ثم انقر على الزرّ OK. انقر على الزرّ OK مرة أخرى بعد نجاح اختبار الاتصال ثم Close لإغلاق نافذة الاختبار.

سيُحفَظ الاتصال بعد إغلاق نافذة الاختبار. يمكنك بعدها الذهاب إلى القائمة Database واختيار Connect to Database ثم تحديد الخادوم الذي تريد الاتصال به من القائمة المنسدلة Stored Connection

يمكنك بالوصول إلى هذه النقطة البدء في إنشاء قواعد البيانات وإدارتها بمساعدة أداة رسوميّة فعّالة وصديقة للمستخدم.

ليس MySQL Workbench سوى أداة من بين أدوات رسومية كثيرة تساعد في جعل إدارتك لقواعد بيانات MySQL أكثر فاعليّة، جرّبها وستجد أنها ستصبح وسيلتك اليومية لإدارة قواعد بيانات MySQL.

ترجمة - بتصرّف - للمقال How to Install and Use MySQL Workbench As Your Database GUI لصاحبه Jack Wallen.

ينتشر استخدام MySQL وMariaDB كثيرا لإدارة قواعد البيانات العلاقيّة، وتستخدم الاثنتان استعلامات SQL لإدخال البيانات في القاعدة واستخراجها منها.

على الرغم من أنّ استعلامات SQL في مجملها أوامر سهلة يمكن تعلّمها بيُسر، إلا أن الاستعلامات ودوالّ قواعد البيانات لا تعمل بنفس الكفاءة. تصبح كفاءة استعلامات SQL أكثر أهميّة مع ازدياد كميّة البيانات التي تخزّنها؛ وازدياد شعبيّة موقعك، في حال كنت تستخدم قاعدة البيانات في موقع ويب.

يناقش هذا الدليل إجراءات بسيطة تمكّن من تسريع تنفيذ الاستعلامات في MySQL وMariaDB. سنفترض أنّ لديك قاعدة بيانات MySQL أو MariaDB مثبّتة على خادومك.

مبادئ عامّة في تهيئة الجداول Tables

تبدأ فعاليّة قاعدة البيانات بتصميم بنية جداول تحترم معايير مجرَّبة لتحسين الأداء. يعني هذا أنه يجب عليك التفكير في أفضل طريقة لتنظيم بياناتك قبل البدء في استخدام البرنامج.

في ما يلي بضعة أسئلة تساعدك في إيجاد طريقة مثلى لتهيئة مخطّط الجداول في قاعدة البيانات.

ما هو الاستخدام الأساسي للجدول؟

تُملي معرفةُ الكيفية التي ستُستغَل بها البيانات الموجودة في الجدول مستقبلا أفضلَ مقاربة لتخطيط الجداول في قاعدة البيانات.

إذا كانت بعض البيانات ستُحدَّث باستمرار فإن الأفضل غالبا هو وضعها في جدول خاصّ بها. يوجد في برامج إدارة قواعد البيانات تخبئة Cache داخليّة تُحدَّث ويُعاد بناءها مع كل تغيير على الجدول، فإن كانت البيانات المُحدَّثة في جدول مستقل فإن البرنامج لن يُضطر لتحديث بقيّة البيانات في كلّ مرة وهو ما يعني جهدا أقل ووصولا أسرع.

تكون عمليّات التحديث عموما أسرع بكثير عند تنفيذها على جداول صغيرة، بينما يُفضَّّل تنفيذ تحليل البيانات المعقّدة على الجداول الكبيرة، بدلا من تجميع الكثير من الجداول الصغيرة، عبر تعليمات join مثلا.

ما هي أنواع البيانات المطلوبة؟

يُمكن أحيانا اقتصاد الكثير من الوقت على المدى الطويل إن استطعت تطبيق قيود سلفا على أحجام البيانات المطلوبة.

إن كانت لديك، على سبيل المثال، قيم محدودة لأحد الحقول النصيّة فيمكنك استخدام نوع البيانات enum لهذا الحقل بدلا من varchar. البيانات من النوع البيانات enum ذات حجم صغير وبالتالي يُنفَّذ الاستعلام عنها بسرعة كبيرة. يصلُح النوع enum - مثلا - لحقل يُخزّن أدوار المستخدِمين في منتدى: مدير Admin، مشرف Moderator، مستخدم فعّال Poweruser أو مستخدم عادي User.

ماهي الحقول التي ستستعلِم عنها؟

تساهم المعرفة القبْليّة للحقول التي ستبحث عن قيمها باستمرار في التحسين المعتبر لسرعة تنفيذ الاستعلامات، وذلك بفهرسة Indexing حقول البحث.

يُضاف الفهرس على حقل على النحو التالي عند إنشاء الجدول:

CREATE TABLE example_table (
    id INTEGER NOT NULL AUTO_INCREMENT,
    name VARCHAR(50),
    address VARCHAR(150),
    username VARCHAR(16),
    PRIMARY KEY (id),
    INDEX (username)
);

لاحظ التعليمة INDEX أعلاه.

يفيد الفهرس على الحقل username كثيرا إذا كنا نعرف أن زوّار موقعنا سيبحثون عن المستخدمين بأسماء حساباتهم. يُنشئ الاستعلام أعلاه جدول example_table يمكن عرض خاصيّاته كالتالي:

explain example_table;

+----------+--------------+------+-----+---------+----------------+
| Field    | Type         | Null | Key | Default | Extra          |
+----------+--------------+------+-----+---------+----------------+
| id       | int(11)      | NO   | PRI | NULL    | auto_increment |
| name     | varchar(50)  | YES  |     | NULL    |                |
| address  | varchar(150) | YES  |     | NULL    |                |
| username | varchar(16)  | YES  | MUL | NULL    |                |
+----------+--------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

يوجد لدينا - كما يظهر - فهرسان. الأول المفتاح الرئيس Primary key ويوجد على حقل المعرِّف id، أما الثاني فهو الذي عرّفناه ويوجد على الحقل username. يعني هذا أن البحث عن المستخدمين بأسماء حساباتهم سيكون أسرع كثيرا من البحث عنهم بأحد الحقول المتبقيّة.

من المهمّ جدا، من وجهة نظر التصميم البرمجي، التفكير في الحقول التي يجب أن تُفهرَس وفعل ذلك مع إنشاء الجدول؛ إلا أن بالإمكان ايضا إضافة فهارس إلى جداول موجودة سلفا على النحو التالي:

CREATE INDEX index_name ON table_name(column_name);

توجد طريقة أخرى للحصول على نفس النتيجة:

ALTER TABLE table_name ADD INDEX ( column_name );

استخدام الدالة explain لإيجاد حقول لفهرستها

إذا كان برنامجك يطلب تنفيذ استعلامات منتظمة يمكن التنبؤ بها فيجب عليك تحليل هذه الاستعلامات للتأكد من أن الاستعلامات تستخدم حقولا بها فهارس كلما كان ذلك ممكنا. تساعد الدالة explain في هذه المهمة.

سنستورد قاعدة البيانات التجريبية الموجودة في المرفق employees_db.zip لتطبيق بعض الأمثلة عليها. نزّل الملف المضغوط ثم نفّذ الأمرين التاليين لفكّ ضغطه والانتقال إلى المجلّد employees_db الناتج عن فك الضغط:

tar xjvf employees_db-full-1.0.6.tar.bz2
cd employees_db

ثم ننفّذ اﻷمر التالي لاستيراد القاعدة إلى MySQL (ينبغي أن يكون عميل MySQL - حزمة mysql-client - مثبتا لديك):

mysql -u root -p -t < employees.sql

ستُطلب منك كلمة سرّ خادوم MySQL.

نسجّل الدخول إلى خادوم MySQL:

mysql -u root -p

ننفّذ الأمر التالي داخل المحثّ Prompt الخاص بـMySQL لتحديد قاعدة البيانات التي استوردناها للتو:

use employees;

نحتاج أولا لإخبار MySQL ألا يستخدم تخبئته الداخليّة ليمكننا الحكم بدقّة على الوقت اللازم لتنفيذ هذه المهامّ.

SET GLOBAL query_cache_size = 0;
SHOW VARIABLES LIKE "query_cache_size";

+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| query_cache_size | 0     |
+------------------+-------+
1 row in set (0.00 sec)

يمكننا الآن تشغيل استعلام بسيط على مجموعة كبيرة من البيانات:

SELECT COUNT(*) FROM salaries WHERE salary BETWEEN 60000 AND 70000;

+----------+
| count(*) |
+----------+
|   588322 |
+----------+
1 row in set (0.80 sec)

يطلُب الاستعلام أعلاه عدَّ الموظفين ذوي الدخل المحصور بين 60000 و70000. تُظهر النتيجة وجود 588322 موظف في هذا المجال.

نستفيد الآن من الدالة explain لرؤية كيف نُفِّذ الاستعلام السابق وذلك بإضافة الكلمة EXPLAIN أمام الاستعلام الذي طبّقناه للتو:

EXPLAIN SELECT COUNT(*) FROM salaries WHERE salary BETWEEN 60000 AND 70000;

+----+-------------+----------+------+---------------+------+---------+------+---------+-------------+
| id | select_type | table    | type | possible_keys | key  | key_len | ref  | rows    | Extra       |
+----+-------------+----------+------+---------------+------+---------+------+---------+-------------+
|  1 | SIMPLE      | salaries | ALL  | NULL          | NULL | NULL    | NULL | 2844738 | Using where |
+----+-------------+----------+------+---------------+------+---------+------+---------+-------------+
1 row in set (0.00 sec)

يظهر في نتيجة استخدام التعليمة EXPLAIN أن قيمة الحقل key هي NULL، ما يعني أنه لم يُستخدَم أي فهرس في الاستعلام السابق.

فلنضف فهرسا على الحقل salary، ثم لنعد نفس الاستعلام ولننظر إن كان ذلك يُسرّع من تنفيذه:

ALTER TABLE salaries ADD INDEX ( salary );
SELECT COUNT(*) FROM salaries WHERE salary BETWEEN 60000 AND 70000;

النتيجة:

+----------+
| count(*) |
+----------+
|   588322 |
+----------+
1 row in set (0.14 sec)

يمكن ملاحظة أن سرعة تنفيذ الاستعلام تحسّنت كثيرا من 0.80 ثانية قبل إضافة الفهرس إلى 0.14بعد إضافته.

قاعدة مهمّة أخرى عند استخدام الفهارس هي أنه ينبغي الانتباه إلى ربط الجداول (عبر التعليمة join مثلا)؛ إذ يجب أن تُنشَأ فهارس للحقول المستخدمة في الربط، كما يجب أن تشترك هذه الحقول في نوع البيانات.

على سبيل المثال، إن كان لديك جدول يحوي وصفات طعام (وليكن اسمه cheeses) وآخر لمكوّنات كلّ وصفة (وليكن اسمُه ingredients) فإن ربط الجدوليْن يمكن أن يكون حسب حقل من النوع INT (عدد طبيعي) يحوي معرّف المكوّن (وليكن اسمُه ingredient_id) موجود في الجدوليْن. يمكننا بعدها إنشاء فهرس على كل من الحقليْن ingredient_id وبالتالي تسريع استعلامات join كثيرا.

تحسين الاستعلامات لتسريع تنفيذها

يتعلّق الجزء الثاني من معادلة تسريع الاستعلامات بتحسين الاستعلامات نفسها. تتطلّب بعض التعليمات قدرات حسابية أعلى من تعليمات أخرى، كما يمكن في أحيان كثيرة الحصول على نفس النتيجة بأكثر من طريقة، مع اختلاف في الكلفة الحسابية لكلّ طريقة.

يمكن ألا تحتاج إلا إلى نتائج قليلة العدد، حسب الغرض الذي ستستخدم فيه نتيجة الاستعلام. إذا أردت على سبيل المثال معرفة ما إذا كان هناك موظَّف بدخل أقل من 40000 في قاعدة البيانات التي نزّلناها سابقا، فالاستعلام التالي يجيبك:

SELECT * FROM SALARIES WHERE salary < 40000 LIMIT 1;

النتيجة:

+--------+--------+------------+------------+
| emp_no | salary | from_date  | to_date    |
+--------+--------+------------+------------+
|  10022 |  39935 | 2000-09-02 | 2001-09-02 |
+--------+--------+------------+------------+
1 row in set (0.00 sec)

يُنفَّّذ الاستعلام سريعا لأنه يكتفي بأول تسجيلة تحقّق الشرط المطلوب.

إن كان الاستعلام - مثلا - يستخدم عامل المقارنة OR وكان عنصرا المقارنة يختبران حقولا مختلفة فإن الاستعلام قد يأخذ وقتا أكثر من اللازم. على سبيل المثال، إن أردت البحث عن جميع الموظفين الذين تبدأ أسماءهم الشخصية والعائلية بـBre فستبحث عن قيم حقليْن مختلفيْن (first_name وlast_name). يكون الاستعلام باستخدام العامل OR على النحو التالي:

SELECT * FROM employees WHERE last_name like 'Bre%' OR first_name like 'Bre%';

إلا أن الاستعلام قد يكون (حسب طبيعة البيانات في الجدول) أسرع إن بحثتَ عن الأسماء الشخصيّة أولا في استعلام خاصّ ثم عن الأسماء العائلية ثانيا ودمجت الاثنين:

SELECT * FROM employees WHERE first_name like 'Bre%' UNION SELECT * FROM employees WHERE last_name like 'Bre%';

خاتمة

عرضنا في هذا المقال لبضعة حيّل تساعدك في البدء في تحسين الاستعلامات الموجهة لقاعدة بيانات MySQL (أو MariaDB). توجد الكثير من الحيّل الأخرى التي ستساعدك في الرفع من أداء قاعدة البيانات وبالتالي أداء تطبيقك أو موقعك.

يوفّر نظاما إدارة قواعد البيانات MySQL وMariaDB توثيقا جيّدا حول كيفية تحسين استعلاماتك وإعداد قاعدة البيانات لأداء أعلى. يجب أن تتذكّر أن تحسين أداء قاعدة البيانات مرتبط ارتباطا وثيقا بطبيعة استخدامها والبيانات المخزّنة فيها، لذا كلّ ما كانت سيناريوهات الاستخدام أوضح وأكثر انتظاما كلّ ما كان تحسين الاستعلامات أسهل.

ترجمة - بتصرف - للمقال How To Optimize Queries and Tables in MySQL and MariaDB on a VPS لصاحبه Justin Ellingwood.

 تمهيد

تتوسع قواعد البيانات بسرعة مع مرور الزمن، وتكاد في بعض الأحيان أن تملأ المساحة التخزينية المتاحة في نظام الملفات كلها. وقد تتعرض أيضًا إلى مشاكل في الإدخال والإخراج نتيجةً لمحاولة عدِّة خدمات الكتابة على (أو القراءة من) نفس القسم معًا.

هذا الدرس سيفيدك لو كنتَ تريد إضافة المزيد من المساحة التخزينية، أو استخدام خصائص جهاز التخزين لزيادة الأداء (ربما عبر استخدام RAID)، أو تتطلّع إلى استعمال ميزات أخرى للتخزين. سيعلِّمُك هذا الدرس طريقة تغيير مجلد تخزين بيانات MySQL.
التعليمات المذكورة هنا تناسب الخواديم التي تُشغِّل نسخةً وحيدةً من MySQL، أمّا لو كانت عندك أكثر من نسخة، فسيساعدك درس «How To Move a MySQL Data Directory to a New Location on Ubuntu 16.04» في ذلك، لأنَّه يحتوي معلومات عن كيفية تغيير مكان التخزين عبر تعديل الضبط.

المتطلبات المسبقة

• خادوم أوبنتو 16.04 (أو 14.04) مضبوطٌ كما في درس «الإعداد الابتدائي لخادوم أوبنتو 14.04»، بما في ذلك إعداد حساب مستخدم عادي لكنه يملك امتيازات الجذر (root) عبر الأداة sudo.
• خادوم MySQL. لو لم يكن عندك خادوم MySQL مضبوطٌ مسبقًا، فسيساعدك الدرس «تثبيت وإعداد نظامي إدارة قواعد البياناتMySQL وPostgreSQL على أوبنتو».
• نسخة احتياطية من قواعد بياناتك. ما لم تكن تتعامل مع نسخةٍ حديثة التثبيت من MySQL، فاحرص على أخذ نسخة احتياطية من بياناتك. سيساعدك درس «كيف تقوم بالنسخ الاحتياطي لقواعد بيانات MySQL على Ubuntu» على فعل ذلك.

سننقل البيانات من جهاز تخزينٍ موصولٍ (mounted) في نقطة الوصل ‎/mnt/volume-nyc1-01..

سيعلّمك هذا الدرس طريقة نقل مجلد تخزين بيانات MySQL إلى مكانٍ جديد بغض النظر عن وسيط التخزين الذي تستخدمه (قرص صلب، أو مصفوفة RAID، أو تخزين شبكي).

الخطوة الأولى: نقل مجلد بيانات MySQL

لكي نضمن سلامة البيانات، علينا أولًا إيقاف خادوم MySQL:

sudo systemctl stop mysql

الأمر systemctl لا يُظهِر نتيجة تنفيذ أوامر إدارة الخدمات، لذا إذا أردتَ التحقق أنَّ الخادوم قد أُغلِق بنجاح، فنفِّذ الأمر الآتي:

sudo systemctl status mysql

انظر إلى آخر سطر من ناتج الأمر السابق الذي يجب أن يخبرك أنَّ الخادوم قد توقف عن العمل:

. . .
Jul 18 11:24:20 ubuntu-512mb-nyc1-01 systemd[1]: Stopped MySQL Community Server.

نستطيع الآن –بعد إغلاق الخادوم– نقل مجلد قواعد البيانات إلى مكانٍ آخر:

sudo mv /var/lib/mysql /mnt/volume-nyc1-01/mysql

ثم سنُنشِئ وصلةً رمزيةً (symbolic link):

sudo ln -s /mnt/volume-nyc1-01/mysql /var/lib/mysql

يبدو أنَّنا نستطيع تشغيل خادوم MySQL بعد إنشاء الوصلة الرمزية، لكن هنالك أمرٌ إضافيٌ يجب ضبطه.

الخطوة الثانية: ضبط قواعد الوصول في AppArmor

بعد أن نقلتَ مجلد MySQL إلى مكانٍ آخر في نظام الملفات، فعليك أن تُعدِّل في ضبط AppArmor، وذلك بتعديل ملف alias التابع لبرمجية AppArmor:

sudo nano /etc/apparmor.d/tunables/alias

أضف الآن التعليمة الآتية في نهاية الملف:

. . .
alias /var/lib/mysql/ -> /mnt/volume-nyc1-01/mysql/,

لكي تأخذ التعديلات مفعولها، فيجب إعادة تشغيل AppArmor:

sudo systemctl restart apparmor

ملاحظة: إذا تخطيت خطوة ضبط AppArmor وحاولت تشغيل mysql مباشرةً، فستظهر لك رسالة الخطأ الآتية:

Job for mysql.service failed because the control process 
exited with error code. See "systemctl status mysql.service" 
and "journalctl -xe" for details.

يمكن تلخيص الناتج الظاهر من الأمرَين systemctl و journalctl بما يلي:

Jul 18 11:03:24 ubuntu-512mb-nyc1-01 systemd[1]: 
mysql.service: Main process exited, code=exited, status=1/FAILURE

ولمّا كانت رسائل الخطأ لا تُظهِر ربطًا مباشرًا بين AppArmor ومجلد التخزين، فقد يصعب عليك أن تعرف لماذا يحدث هذا الخطأ. لكن إن نظرنا إلى ملف syslog فسنرى ما هي المشكلة:

sudo tail /var/log/syslog

الناتج:

Nov 24 00:03:40 digitalocean kernel: 
[  437.735748] audit: type=1400 audit(1479945820.037:20): 
apparmor="DENIED" operation="mknod" profile="/usr/sbin/mysqld" 
name="/mnt/volume-nyc1-01/mysql/mysql.lower-test" pid=4228 
comm="mysqld" requested_mask="c" denied_mask="c" fsuid=112 ouid=112

يمكننا الآن أن نُشغِّل خدمة MySQL:

sudo systemctl start mysql
sudo systemctl status mysql

بعد أن تُعيد تشغيل MySQL، فتحقق أنَّ بياناتك سليمة وأنَّ خادوم MySQL يعمل عملًا سليمًا دون مشاكل.

الخلاصة

نقلنا في هذا الدرس مجلد تخزين بيانات MySQL واستعملها وصلةً رمزيةً لكي نخبر MySQL ما هو مكان التخزين الجديد؛ ثم حدّثنا ضبط برمجية AppArmor (الموجودة في توزيعة أوبنتو وغيرها) لكي يتوافق مع ما عدّلناه. وصحيحٌ أننا استعملنا جهاز تخزين مستقل، إلا أنَّك تستطيع اتباع تعليمات هذا الدرس لإعادة تعريف مكان تخزين البيانات بغض النظر عن تقنية التخزين المستعملة.

ترجمة -وبتصرّف- للمقال How to Change a MySQL Data Directory to a New Location Using a Symlink لصاحبته Melissa Anderson

سنشرح في هذه الدرس كيفية تقليل عدد السجلات المُعادة من طلبية SELECT، وطريقة ترتيب الناتج بناءً على شرطٍ معيّن.
إضافةً إلى ما سبق، سنتعلم كيفية تجميع السجلات وإجراء عمليات حسابية أساسية على الحقول الرقمية، وكل ما سبق سيساعدنا على إنشاء سكربت SQL الذي سنستخدمه لإنشاء تقارير مفيدة.
إذا لم تكن لديك خلفية جيّدة حول قواعد بيانات MySQL فأنصحك بقراءة هذا الدّرس أوّلا

المتطلبات المسبقة

عليك اتباع الخطوات الآتية قبل المتابعة:

•  نزِّل قاعدة بيانات employees، التي تحتوي على ستة جداول تتضمن حوالي 4 ملايين سجل إجماليًا:

# wget https://launchpad.net/test-db/employees-db-1/1.0.6/+download/employees_db-full-1.0.6.tar.bz2
# tar xjf employees_db-full-1.0.6.tar.bz2
# cd employees_db

• ادخل إلى مِحَث MariaDB وأنشِئ قاعدة بيانات باسم employees:

1. # mysql -u root -p
   Enter password: 
   Welcome to the MariaDB monitor.  Commands end with ; or \g.
   Your MariaDB connection id is 2
   Server version: 10.1.14-MariaDB MariaDB Server
   Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.
   Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
   MariaDB [(none)]> CREATE DATABASE employees;
   Query OK, 1 row affected (0.00 sec)

    

• استورد قاعدة البيانات إلى خادوم MairaDB كالآتي:

MariaDB [(none)]> source employees.sql

انتظر دقيقةً أو دقيقتين إلى أن ينتهي استيراد قاعدة البيانات (أبقِ في بالك أننا نتحدث هنا عن أربعة ملايين سجل!).

•  تأكد من أنَّ استيراد قاعدة البيانات قد تمَّ بشكلٍ صحيح بعرض جداولها:

MariaDB [employees]> USE employees;
Database changed
MariaDB [employees]> SHOW TABLES;
+---------------------+
| Tables_in_employees |
+---------------------+
| departments         |
| dept_emp            |
| dept_manager        |
| employees           |
| salaries            |
| titles              |
+---------------------+
6 rows in set (0.02 sec)

• أنشِئ حسابًا مخصصًا لاستخدام قاعدة بيانات employees (اختر اسمًا وكلمة مرور):

MariaDB [employees]> CREATE USER empadmin@localhost IDENTIFIED BY 'empadminpass';
Query OK, 0 rows affected (0.03 sec)
MariaDB [employees]> GRANT ALL PRIVILEGES ON  employees.* to empadmin@localhost;
Query OK, 0 rows affected (0.02 sec)
MariaDB [employees]> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
MariaDB [employees]> exit
Bye

• سجِّل دخولك الآن إلى مِحَث MariaDB بالمستخدم empadmin:

# mysql -u empadmin -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 4
Server version: 10.1.14-MariaDB MariaDB Server
Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> USE employees;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed

احرص على تنفيذ جميع الخطوات الست السابقة قبل إكمالك لقراءة هذا الدرس.

ترتيب وتقليل عدد السجلات المعروضة

جدول الرواتب salaries يحتوي على جميع واردات كل موظف مع تاريخ البداية والنهاية. ربما تود أن تعرض رواتب الموظف emp_no=10001 خلال فترةٍ من الزمن. وهذا سيساعدك على الإجابة عن التساؤلات الآتية:

• هل حصل على زيادة في راتبه؟
• إذا حدث ذلك، فمتى؟

نفِّذ الأمر الآتي لمعرفة ذلك:

MariaDB [employees]> SELECT * FROM salaries WHERE emp_no=10001 ORDER BY from_date;
+--------+--------+------------+------------+
| emp_no | salary | from_date  | to_date    |
+--------+--------+------------+------------+
|  10001 |  60117 | 1986-06-26 | 1987-06-26 |
|  10001 |  62102 | 1987-06-26 | 1988-06-25 |
|  10001 |  66074 | 1988-06-25 | 1989-06-25 |
|  10001 |  66596 | 1989-06-25 | 1990-06-25 |
|  10001 |  66961 | 1990-06-25 | 1991-06-25 |
|  10001 |  71046 | 1991-06-25 | 1992-06-24 |
|  10001 |  74333 | 1992-06-24 | 1993-06-24 |
|  10001 |  75286 | 1993-06-24 | 1994-06-24 |
|  10001 |  75994 | 1994-06-24 | 1995-06-24 |
|  10001 |  76884 | 1995-06-24 | 1996-06-23 |
|  10001 |  80013 | 1996-06-23 | 1997-06-23 |
|  10001 |  81025 | 1997-06-23 | 1998-06-23 |
|  10001 |  81097 | 1998-06-23 | 1999-06-23 |
|  10001 |  84917 | 1999-06-23 | 2000-06-22 |
|  10001 |  85112 | 2000-06-22 | 2001-06-22 |
|  10001 |  85097 | 2001-06-22 | 2002-06-22 |
|  10001 |  88958 | 2002-06-22 | 9999-01-01 |
+--------+--------+------------+------------+
17 rows in set (0.03 sec)

لكن ماذا لو أردنا معرفة آخر خمس زيادات؟ يمكننا أن نستخدم ORDER BY form_date DESC. تُشير الكلمة المحجوزة DESC إلى أنَّ الترتيب الذي نريد اتباعه هو الترتيب التنازلي.
إضافةً إلى ما سبق، يسمح لنا التعبير LIMIT 5 بإعادة أوّل خمسة سجلات:

MariaDB [employees]> SELECT * FROM salaries WHERE emp_no=10001 ORDER BY from_date DESC LIMIT 5;
+--------+--------+------------+------------+
| emp_no | salary | from_date  | to_date    |
+--------+--------+------------+------------+
|  10001 |  88958 | 2002-06-22 | 9999-01-01 |
|  10001 |  85097 | 2001-06-22 | 2002-06-22 |
|  10001 |  85112 | 2000-06-22 | 2001-06-22 |
|  10001 |  84917 | 1999-06-23 | 2000-06-22 |
|  10001 |  81097 | 1998-06-23 | 1999-06-23 |
+--------+--------+------------+------------+
5 rows in set (0.00 sec)

يمكنك أيضًا استخدام ORDER BY على عدِّة حقول، فمثلًا ستُرتِّب الطلبية الآتية النتائج اعتمادًا على تاريخ ولادة الموظف تصاعديًا (وهو الترتيب الافتراضي) ثم عبر اسم الموظف الأخير بترتيبٍ هجائيٍ تنازلي:

MariaDB [employees]> SELECT CONCAT(last_name, ', ', first_name) AS Name, gender AS Gender,  hire_date AS "Hire date" FROM employees ORDER BY birth_date, last_name DESC LIMIT 10;
+--------------------+--------+------------+
| Name               | Gender | Hire date  |
+--------------------+--------+------------+
| Whitcomb, Kiyokazu | M      | 1988-07-26 |
| Schaad, Ronghao    | M      | 1988-07-10 |
| Remmele, Supot     | M      | 1989-01-27 |
| Pocchiola, Jouni   | M      | 1985-03-10 |
| Kuzuoka, Eishiro   | M      | 1992-02-12 |
| Decaestecker, Moni | M      | 1986-10-06 |
| Wiegley, Mircea    | M      | 1985-07-18 |
| Vendrig, Sachar    | M      | 1985-11-04 |
| Tsukuda, Cedric    | F      | 1993-12-12 |
| Tischendorf, Percy | M      | 1986-11-10 |
+--------------------+--------+------------+
10 rows in set (0.31 sec)

يمكنك معرفة المزيد من المعلومات عن LIMIT في الدليل الرسمي.

تجميع السجلات، واستخدام MAX و MIN و AVG و ROUND

كما ذكرنا سابقًا، الجدول salaries يحتوي على رواتب الموظفين خلال فترات زمنية، وبجانب استخدام LIMIT، يمكننا استخدام الكلمتين المحجوزتين MAX و MIN لتحديد متى كان أعلى راتب وأدنى راتب لموظفين معينين:

MariaDB [employees]> SELECT CONCAT(last_name, ', ', first_name) AS Name, MAX(B.salary) AS "Max. salary" FROM employees A JOIN salaries B ON A.emp_no = B.emp_no WHERE A.emp_no IN (10001, 10002, 10003) GROUP BY A.emp_no;
+-----------------+-------------+
| Name            | Max. salary |
+-----------------+-------------+
| Facello, Georgi |       88958 |
| Simmel, Bezalel |       72527 |
| Bamford, Parto  |       43699 |
+-----------------+-------------+
3 rows in set (0.02 sec)
MariaDB [employees]> SELECT CONCAT(last_name, ', ', first_name) AS Name, MIN(B.salary) AS "Min. salary" FROM employees A JOIN salaries B ON A.emp_no = B.emp_no WHERE A.emp_no IN (10001, 10002, 10003) GROUP BY A.emp_no;
+-----------------+-------------+
| Name            | Min. salary |
+-----------------+-------------+
| Facello, Georgi |       60117 |
| Simmel, Bezalel |       65828 |
| Bamford, Parto  |       40006 |
+-----------------+-------------+
3 rows in set (0.00 sec)

وبناءً على النتائج السابقة، هل تستطيع أن تعرف ماهو ناتج الطلبية الآتية:

MariaDB [employees]> SELECT CONCAT(last_name, ', ', first_name) AS Name, ROUND(AVG(B.salary), 2) AS "Avg. salary" FROM employees A JOIN salaries B ON A.emp_no = B.emp_no WHERE A.emp_no IN (10001, 10002, 10003) GROUP BY A.emp_no;
+-----------------+-------------+
| Name            | Avg. salary |
+-----------------+-------------+
| Facello, Georgi |    75388.94 |
| Simmel, Bezalel |    68854.50 |
| Bamford, Parto  |    43030.29 |
+-----------------+-------------+
3 rows in set (0.01 sec)

إذا توقعتَ أنَّ الناتج هو المتوسط الحسابي (عبر AVG) للراتب مقرّبًا إلى منزلتين عشريتين (عبر ROUND)، فأنت مصيب.
إذا أردنا أن نعرف مجموع رواتب الموظفين وإعادة أوّل خمسة، فيمكننا حينها استعمال الطلبية الآتية:

MariaDB [employees]> SELECT emp_no, SUM(salary) AS Salary FROM salaries GROUP BY emp_no ORDER BY Salary DESC LIMIT 5;
+--------+---------+
| emp_no | Salary  |
+--------+---------+
| 109334 | 2553036 |
|  43624 | 2492873 |
|  66793 | 2383923 |
| 237542 | 2381119 |
|  47978 | 2374024 |
+--------+---------+
5 rows in set (2.22 sec)

جمّعنا الرواتب في الطلبية السابقة عبر الموظف، ثم أجرينا عليها عملية SUM.

تجميع المعلومات السابقة في سكربت

لحسن الحظ، لا نحتاج إلى تشغيل طلبية تلو أخرى لإنشاء تقرير، وإنما نستطيع إنشاء سكربت فيه سلسلة من تعليمات SQL التي تُعيد جميع النتائج المطلوبة.
بعد تنفيذ السكربت، فستظهر جميع النتائج دون تدخل منا، ولنسمِّ الملف باسم maxminavg.sql في مجلد العمل الحالي وفيه المحتويات الآتية:

--Select database
USE employees;
--Calculate maximum salaries
SELECT CONCAT(last_name, ', ', first_name) AS Name, MAX(B.salary) AS "Max. salary" FROM employees A JOIN salaries B ON A.emp_no = B.emp_no WHERE A.emp_no IN (10001, 10002, 10003) GROUP BY A.emp_no;
--Calculate minimum salaries
SELECT CONCAT(last_name, ', ', first_name) AS Name, MIN(B.salary) AS "Min. salary" FROM employees A JOIN salaries B ON A.emp_no = B.emp_no WHERE A.emp_no IN (10001, 10002, 10003) GROUP BY A.emp_no;
--Calculate averages, round to 2 decimal places
SELECT CONCAT(last_name, ', ', first_name) AS Name, ROUND(AVG(B.salary), 2) AS "Avg. salary" FROM employees A JOIN salaries B ON A.emp_no = B.emp_no WHERE A.emp_no IN (10001, 10002, 10003) GROUP BY A.emp_no;

الأسطر التي تبدأ بشرطتين -- هي تعليقات وسيتم تجاهلها. وستُنفَّذ الطلبيات تلو بعضها. ويمكننا تنفيذ هذا السكربت إما من سطر أوامر لينكس:

# mysql -u empadmin -p < maxminavg.sql
Enter password: 
Name    Max. salary
Facello, Georgi 88958
Simmel, Bezalel 72527
Bamford, Parto  43699
Name    Min. salary
Facello, Georgi 60117
Simmel, Bezalel 65828
Bamford, Parto  40006
Name    Avg. salary
Facello, Georgi 75388.94
Simmel, Bezalel 68854.50
Bamford, Parto  43030.29

أو من مِحث MariaDB:

# mysql -u empadmin -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 4
Server version: 10.1.14-MariaDB MariaDB Server
Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> source maxminavg.sql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed

الخلاصة

شرحنا في هذه المقالة كيفية استخدام عدِّة دوال في MariaDB لتحسين النتائج التي يُخرجها الاستعلام SELECT. وبعد أن تكتب الاستعلامات لأول مرة، فيمكنك بكل بساطة وضعها داخل سكربت وتنفيذها بسهولة مما سيُقلِّل من الخطأ البشري عند كتابتها.
ترجمة -وبتصرّف- للمقال Learn How to Use Several Functions of MySQL and MariaDB – Part 2 لصاحبه Gabriel Cánepa.

سنشرح في هذا الدرس كيفية إنشاء قاعدة بيانات، وجداول (مع تبيان أنواع بيانات حقولها)، وسنفصّل طريقة إجراء عمليات على البيانات على خادوم MySQL أو MariaDB.
سنفترض أنَّك قد ثبّتَ الحزم اللازمة على نظامك، ونفّذتَ الأمر mysql_secure_installation لتحسين حماية خادوم قواعد البيانات. وإلا فانظر إلى درسنا عن كيفية تثبيت خادوم MySQL/MariaDB .
للاختصار، سنشير إلى قواعد البيانات باسم «MariaDB» في هذا الدرس، لكن نفس الاصطلاحات والتعليمات المشروحة هنا ستنطبق تمامًا على MySQL.

إنشاء قواعد البيانات والجداول والمستخدمين

كما تعلم، يمكن تعريف قواعد البيانات بأبسط الكلمات على أنها مجموعة منظمة من المعلومات؛ وندعو قواعد MariaDB تحديدًا أنَّها نظام إدارة قواعد بيانات علائقية (relational database management system اختصارًا RDBMS) التي تستخدم لغة الاستعلام البنيوية (Structure Query Language أي SQL) لإجراء العمليات على قواعد البيانات. أبقِ في بالك أنَّ MariaDB تستعمل الاصطلاحين «database» (قاعدة بيانات) و «schema» (مخطط) لنفس المعنى تمامًا.
نستعمل الجداول (tables) لتخزين المعلومات الدائمة في قواعد البيانات التي تُخزَّن فيها «سجلاتٌ» (rows) من البيانات. وعادةً يرتبط جدولان أو أكثر مع بعضها بطريقةٍ ما. وهذا هو جزءٌ مهمٌ من عملية التنظيم التي تُميّز قواعد البيانات العلائقية.

إنشاء قاعدة بيانات جديدة

علينا أن ندخل أولًا إلى مِحَث (prompt) قواعد بيانات MariaDB بإدخال الأمر الآتي، وذلك لإنشاء قاعدة بيانات جديدة (سيُطلَب منك إدخال كلمة مرور المستخدم root لقواعد البيانات):

[root@TecMint ~]# mysql -u root -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 10.1.14-MariaDB MariaDB Server
Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> CREATE DATABASE BookstoreDB;
Query OK, 1 row affected (0.00 sec)
MariaDB [(none)]> 

بعد إنشاء قاعدة البيانات، فسنحتاج إلى إنشاء جدولين على الأقل فيها، لكن دعنا أولًا نلقي نظرةً على مفهوم أنواع البيانات في الجداول.

لمحة عن أنواع البيانات في قواعد MariaDB

كما ذكرنا سابقًا، الجداول هي الكائن الرئيسية في قواعد البيانات والتي ستُخزَّن فيها المعلومات الدائمة. يتألف كل جدول من حقلين أو أكثر (تُعرَف أيضًا بالأعمدة [columns]) لها نوع بيانات معيّن (أي نوع المعلومات التي ستُخزَّن فيها) التي يستطيع كل حقلٍ (أو عمودٍ) تخزينها.
سأستعرض أشهر أنواع البيانات في MariaDB (يمكنك الرجوع إلى الدليل الرسمي لتحصل على قائمةٍ كاملةٍ):

أنواع البيانات الرقمية

• BOOLEAN: قيمٌ منطقيةٌ، وتعتبَر فيها القيم المساوية للصفر (0) على أنَّها false، وبقية القيم true.
• TINYINT: إذا استخدمَت مع الكلمة المحجوزة SIGNED فستُمثِّل المجال من ‎-128 إلى 127، بينما مجالها إن كانت UNSIGNED فهو من 0 إلى 255.
• SMALLINT: إذا استخدمت مع SIGNED فهي تمثِّل المجال من ‎-32768 إلى 32767. بينما مجال UNSIGNED فهو من 0 إلى 65535.
• INT: إذا استخدمت مع SIGNED فهي تغطي المجال من 0 إلى 4294967295، ومن ‎‎-2147483648 إلى 2147483647 فيما عدا ذلك.
  ملاحظة: في أنواع البيانات TINYINT و SMAILLINT و INT، القيمة الافتراضية المرتبطة معها هي SIGNED.
• DOUBLE(M, D): يُمثِّل هذا النوع الأعداد العشرية ذات الفاصلة، حيث M هو العدد الكلي للأرقام و D هو عدد الأرقام بعد الفاصلة العشرية. إذا اُستعمِلَت الكلمة UNSIGND فلن يُسمَح بإدراج قيم سالبة في هذا الحقل.

أنواع البيانات النصية

• VARCHAR(M): يُمثِّل هذا النوع السلاسل النصية ذات الطول المتغير حيث M هو عدد الأحرف المسموح الأقصى للسلسلة النصية مقدرًا بالبايت (يمكن أن يبلغ نظريًا 65535 بايت). في حال كانت السلاسل النصية المُدخَلة بالإنكليزية، فسيأخذ كل محرف بايتًا واحدًا، لكن هنالك استثناءٌ لبعض المحارف التي قد تأخذ 3 بايت لكي تُمثَّل، فمثلًا، الحرف الإسباني ñ لا يأخذ بايتًا واحدًا وإنما ثلاثة بايتات.
• TEXT(M): يُمثِّل هذا النوع الأعمدة ذات الطول الأقصى المقدّر 65535 محرفًا، لكن كما في نوع البيانات VARCHAR(M) سينقص طول التخزين الأقصى إذا استعملنا محارف تأخذ أكثر من بايت لتُمثَّل في الحاسوب. إذا حُدِّدَ M فسيُنشأ العمود بأصغر نوع بيانات التي يستطيع تخزين هذا العدد من المحارف.
• MEDIUMTEXT(M) و LONGTEXT(M) شبيهة بنوع البيانات TEXT(M) لكن الطول الأقصى المسموح هو 16777215 و 4294967295 محرفًا على التوالي وبالترتيب.

الوقت والتاريخ

• DATE: تمثيل التاريخ بصيغة YYYY-MM-DD.
• TIME: تمثيل الوقت بصيغة HH:MM:SS.sss (أي الساعة والدقيقة والثانية والملي ثانية).
• DATETIME: جمعٌ بين DATE و TIME بالصيغة YYYY-MM-DD HH:MM:SS.
• TIMESTAMP: تُستعمَل بصمة الوقت لتعريف اللحظة التي أضيف أو حُدِّث فيها سجلٌ (row) ما.
  بعد اطلاعك على أنواع البيانات السابقة، فيمكن أن تستطيع الآن تحديد ما هو نوع البيانات الذي ستحتاج له لإسناده إلى عمودٍ معيّن في الجدول.
  على سبيل المثال، يمكن أن يتسع اسم المستخدم بسهولة في عمودٍ بنوع البيانات VARCHAR(50)، لكن محتوى تدوينة سيحتاج إلى نوع TEXT (اختر قيمة M بما يلبي احتياجاتك).

إنشاء جداول فيها مفاتيح أساسية وأجنبية

قبل أن نشرع في إنشاء جداول، فمن الضروري استيعاب مفهومين أساسيين عن قواعد البيانات العلائقية: المفتاح الأساسي (primary key) والمفتاح الأجنبي (foreign key).
يحتوي المفتاح الأساسي قيمةً تُمثِّلُ مُعرِّفًا فريدًا لكل سطرٍ أو سجلٍ (row أو record) في الجدول، لكن في المقابل يستعمل المفتاح الأجنبي للربط بين البيانات في جدولين، وللتحكم في البيانات التي يمكن تخزينها في الجدول الذي يحتوي على المفتاح الأجنبي. يُعتَبَر المفتاحان الأساسي والأجنبي قيمًا رقميةً (INT) عادةً.
لشرح هذا المفهوم عمليًا، فلنستخدم قاعدة بيانات BookstoreDB ونُنشِئ جدولين باسم AuthorsTBL و BooksTBL. تُشير NOT NULL إلى أنَّ الحقل المرتبط بها يتطلب قيمةً تختلف عن NULL.
يمكن أيضًا استعمال AUTO_INCREMENT لزيادة قيمة عمود المفتاح الرئيسي الرقمي بمقدار 1 عند إضافة سجل جديد إلى الجدول.

MariaDB [(none)]> USE BookstoreDB;
Database changed
MariaDB [BookstoreDB]> CREATE TABLE AuthorsTBL (
-> AuthorID INT NOT NULL AUTO_INCREMENT,
-> AuthorName VARCHAR(100),
-> PRIMARY KEY(AuthorID)
-> );
Query OK, 0 rows affected (0.05 sec)
MariaDB [BookstoreDB]> CREATE TABLE BooksTBL (
-> BookID INT NOT NULL AUTO_INCREMENT,
-> BookName VARCHAR(100) NOT NULL,
-> AuthorID INT NOT NULL,
-> BookPrice DECIMAL(6,2) NOT NULL,
-> BookLastUpdated TIMESTAMP,
-> BookIsAvailable BOOLEAN,
-> PRIMARY KEY(BookID),
-> FOREIGN KEY (AuthorID) REFERENCES AuthorsTBL(AuthorID)
-> );
Query OK, 0 rows affected (0.05 sec)
MariaDB [BookstoreDB]> 

يمكننا الآن المتابعة وإضافة السجلات إلى جدولَي AuthorsTBL و BooksTBL.

تحديد السجلات وإضافتها وتحديثها وحذفها

علينا أولًا ملء الجدول AuthorsTBL، لكن لماذا؟ لأننا سنحتاج إلى وجود قيم في العمود AuthorID قبل إضافة سجلات إلى جدول BooksTBL.
نفِّذ الأمر الآتي من مِحَث MariaDB:

MariaDB [BookstoreDB]> INSERT INTO AuthorsTBL (AuthorName) VALUES ('Agatha Christie'), ('Stephen King'), ('Paulo Coelho');

بعدئذٍ سنُحدِّد جميع السجلات من جدول AuthorsTBL. تذكّر أننا نحتاج إلى AuthorID لكل سجل لإنشاء استعلام INSERT في جدول BooksTBL.
إذا أردتَ الحصول على سجلٍ وحيدٍ فقط، فاستعمل عبارة WHERE لكتابة شرط الذي يجب أن تُحقِّقه السجلات المُعادة. على سبيل المثال:

MariaDB [BookstoreDB]> SELECT * FROM AuthorsTBL WHERE AuthorName='Agatha Christie';

يمكنك عوضًا عن ذلك أن تحصل على جميع السجلات معًا:

MariaDB [BookstoreDB]> SELECT * FROM AuthorsTBL;

ناتج تنفيذ الطلبيتين السابقتين:

MariaDB [BookstoreDB]> SELECT * FROM AuthorsTBL WHERE AuthorName='Agatha Christie';
+----------+-----------------+
| AuthorID | AuthorName      |
+----------+-----------------+
|        1 | Agatha Christie |
+----------+-----------------+
1 row in set (0.00 sec)
MariaDB [BookstoreDB]> SELECT * FROM AuthorsTBL;
+----------+-----------------+
| AuthorID | AuthorName      |
+----------+-----------------+
|        1 | Agatha Christie |
|        2 | Stephen King    |
|        3 | Paulo Coelho    |
+----------+-----------------+
3 rows in set (0.00 sec)
MariaDB [BookstoreDB]>

لنُنشِئ الآن استعلام INSERT لجدول BooksTBL، وذلك عبر استخدام حقل AuthorID المناسب لمُطابقة مؤلف كل كتاب. القيمة 1 في حقل BookIsAvaiable تعني توافر الكتاب، بينما 0 تعني عكس ذلك:

MariaDB [BookstoreDB]> INSERT INTO BooksTBL (BookName, AuthorID, BookPrice, BookIsAvailable)
-> VALUES ('And Then There Were None', 1, 14.95, 1),
-> ('The Man in the Brown Suit', 1, 23.99, 1),
-> ('The Stand', 2, 35.99, 1),
-> ('Pet Sematary', 2, 17.95, 0),
-> ('The Green Mile', 2, 29.99, 1),
-> ('The Alchemist', 3, 25, 1),
-> ('By the River Piedra I Sat Down and Wept', 3, 18.95, 0);
Query OK, 7 rows affected (0.03 sec)
Records: 7  Duplicates: 0  Warnings: 0

سنستخدم الآن SELECT لرؤية السجلات في جدول BooksTBL ثم سنُحدِّث عبر UPDATE سعر كتاب «The Alchemist» لمؤلفه Paulo Coelho ثم سنعرض السجلات (عبر SELECT) مرةً أخرى.
لاحظ كيف يعرض الحقل BookLastUpdated قيمةً مختلفةً، وكما شرحنا سابقًا، الحقل TIMESTAMP يُخزِّن الوقت الذي أُنشِئ أو حُدِّث فيه السجل.

MariaDB [BookstoreDB]> SELECT * FROM BooksTBL;
+--------+-----------------------------------------+----------+-----------+---------------------+-----------------+
| BookID | BookName                                | AuthorID | BookPrice | BookLastUpdated     | BookIsAvailable |
+--------+-----------------------------------------+----------+-----------+---------------------+-----------------+
|      1 | And Then There Were None                |        1 |     14.95 | 2016-10-01 23:31:41 |               1 |
|      2 | The Man in the Brown Suit               |        1 |     23.99 | 2016-10-01 23:31:41 |               1 |
|      3 | The Stand                               |        2 |     35.99 | 2016-10-01 23:31:41 |               1 |
|      4 | Pet Sematary                            |        2 |     17.95 | 2016-10-01 23:31:41 |               0 |
|      5 | The Green Mile                          |        2 |     29.99 | 2016-10-01 23:31:41 |               1 |
|      6 | The Alchemist                           |        3 |     25.00 | 2016-10-01 23:31:41 |               1 |
|      7 | By the River Piedra I Sat Down and Wept |        3 |     18.95 | 2016-10-01 23:31:41 |               0 |
+--------+-----------------------------------------+----------+-----------+---------------------+-----------------+
7 rows in set (0.00 sec)
MariaDB [BookstoreDB]> UPDATE BooksTBL SET BookPrice=22.75 WHERE BookID=6;
Query OK, 1 row affected (0.04 sec)
Rows matched: 1  Changed: 1  Warnings: 0
MariaDB [BookstoreDB]> SELECT * FROM BooksTBL WHERE BookID=6;
+--------+---------------+----------+-----------+---------------------+-----------------+
| BookID | BookName      | AuthorID | BookPrice | BookLastUpdated     | BookIsAvailable |
+--------+---------------+----------+-----------+---------------------+-----------------+
|      6 | The Alchemist |        3 |     22.75 | 2016-10-01 23:35:00 |               1 |
+--------+---------------+----------+-----------+---------------------+-----------------+
1 row in set (0.00 sec)
MariaDB [BookstoreDB]> 

لنفترض أننا نريد حذف أحد السجلات الذي لم يعد مفيدًا، فمثلًا سنحذف كتاب «The Alchemist» من جدول BooksTBL. يمكنك استخدام عبارة DELETE لفعل ذلك كما يلي:

MariaDB [BookstoreDB]> DELETE FROM BooksTBL WHERE BookID=6;

وكما في عبارة UPDATE، من المستحسن إجراء عملية SELECT أولًا لرؤية الحقول التي ستؤثر فيها عملية الحذف.
لا تنسَ أن تُضيف عبارة WHERE وشرطًا(BookID=6) لتحديد سجل مُعيّن لحذفه. وإلا فقد تصبح جميع سجلات جدولك معرضةً للحذف.
إذا أردتَ دمج حقلين (أو أكثر) معًا، فيمكنك استخدام عبارة CONCAT، فلنقل على سبيل المثال أننا نريد عرض ناتج يحتوي على عمودٍ فيه اسم الكتاب والمؤلف على الشكل الآتي «The Alchemist (Paulo Coelho)» ثم يليه عمودٌ فيه سعره.
هذا سيتطلب إجراء عملية JOIN بين جدولَي AuthorsTBL و BooksTBL في الحقل المشترك بينهما (ألا وهو AuthorID):

MariaDB [BookstoreDB]> SELECT CONCAT(BooksTBL.BookName, ' (', AuthorsTBL.AuthorName, ')') AS Description, BooksTBL.BookPrice FROM AuthorsTBL JOIN BooksTBL ON AuthorsTBL.AuthorID = BooksTBL.AuthorID;

وكما نرى، تسمح لنا عبارة CONCAT بدمج عدِّة تعبيرات نصية المفصول بينها بفاصلة. ستلاحظ أيضًا أننا استخدمنا اسمًا بديلًا وهو Description لعرض ناتج عملية دمج الحقلين.
هذا هو ناتج تنفيذ الاستعلام السابقة:

MariaDB [BookstoreDB]> SELECT CONCAT(BooksTBL.BookName, ' (', AuthorsTBL.AuthorName, ')') AS Description, BooksTBL.BookPrice FROM AuthorsTBL JOIN BooksTBL ON AuthorsTBL.AuthorID = BooksTBL.AuthorID;
+--------------------------------------------------------+-----------+
| Description                                            | BookPrice |
+--------------------------------------------------------+-----------+
| And Then There Were None (Agatha Christie)             |     14.95 |
| The Man in the Brown Suit (Agatha Christie)            |     23.99 |
| The Stand (Stephen King)                               |     35.99 |
| Pet Sematary (Stephen King)                            |     17.95 |
| The Green Mile (Stephen King)                          |     29.99 |
| The Alchemist (Paulo Coelho)                           |     25.00 |
| By the River Piedra I Sat Down and Wept (Paulo Coelho) |     18.95 |
+--------------------------------------------------------+-----------+
7 rows in set (0.00 sec)

إنشاء مستخدم للوصول إلى قاعدة بيانات BookstoreDB

لا أنصحك باستخدام المستخدم root لإجراء جميع عمليات معالجة البيانات، ولتجنب ذلك سنُنِشئ حساب مستخدم جديد (سنسميه bookstoreuser) ونُسنِد إليه جميع الامتيازات اللازمة لإدارة قاعدة البيانات BookstoreDB:

MariaDB [BookstoreDB]> CREATE USER bookstoreuser@localhost IDENTIFIED BY 'tecmint';
Query OK, 0 rows affected (0.00 sec)
MariaDB [BookstoreDB]> GRANT ALL PRIVILEGES ON  BookstoreDB.* to bookstoreuser@localhost;
Query OK, 0 rows affected (0.00 sec)
MariaDB [BookstoreDB]> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

وجود حساب مستخدم منفصل ومخصص لكل قاعدة بيانات سيحمي كامل الخادوم إن حدث اختراقٌ لأحد الحسابات.

نصائح إضافية في قواعد MySQL

لمسح الشاشة وإظهار مِحَث MariaDB فيها فقط، فأدخِل الأمر الآتي ثم اضغط على Enter:

MariaDB [BookstoreDB]> \! clear

نفِّذ الأمر الآتي لرؤية بنية أحد الجداول:

MariaDB [BookstoreDB]> SHOW COLUMNS IN [TABLE NAME HERE ];

لعرض الأعمدة الموجودة في جدول BooksTBL في قاعدة بياناتنا:

MariaDB [BookstoreDB]> SHOW COLUMNS IN BooksTBL;
+-----------------+--------------+------+-----+-------------------+-----------------------------+
| Field           | Type         | Null | Key | Default           | Extra                       |
+-----------------+--------------+------+-----+-------------------+-----------------------------+
| BookID          | int(11)      | NO   | PRI | NULL              | auto_increment              |
| BookName        | varchar(100) | NO   |     | NULL              |                             |
| AuthorID        | int(11)      | NO   | MUL | NULL              |                             |
| BookPrice       | decimal(6,2) | NO   |     | NULL              |                             |
| BookLastUpdated | timestamp    | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| BookIsAvailable | tinyint(1)   | YES  |     | NULL              |                             |
+-----------------+--------------+------+-----+-------------------+-----------------------------+
6 rows in set (0.02 sec)

تفحص سريع لبنية الجدول سيكشف أنَّ الحقل BookIsAvailable يقبل القيم NULL. ولعدم رغبتنا في ذلك، فسنُعدِّل (ALTER) الجدول كالآتي:

MariaDB [BookstoreDB]> ALTER TABLE BooksTBL MODIFY BookIsAvailable BOOLEAN NOT NULL;

(تستطيع الآن عرض بنية الجدول مرةً أخرى، وستجد أنَّ كلمة YES الموجودة في عمود Null أصبحت NO).
أخيرًا، لعرض جميع قواعد البيانات في خادومك، تستطيع تنفيذ الأمر:

MariaDB [BookstoreDB]> SHOW DATABASES;

أو

MariaDB [BookstoreDB]> SHOW SCHEMAS;

الناتج الآتي يعرض مخرجات الأمر السابق بعد الدخول إلى مِحَث MariaDB عبر المستخدم bookstoreuser (لاحظ أنَّ هذا الحساب لا يستطيع «رؤية» أيّة قواعد بيانات ما عدا BookstoreDB و information_schema [المتوافرة لجميع المستخدمين]):

[root@TecMint ~]# mysql -u bookstoreuser -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 3
Server version: 10.1.14-MariaDB MariaDB Server
Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [BookstoreDB]> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| BookstoreDB        |
| information_schema |
+--------------------+
2 rows in set (0.00 sec)
MariaDB [BookstoreDB]> SHOW SCHEMAS;
+--------------------+
| Database           |
+--------------------+
| BookstoreDB        |
| information_schema |
+--------------------+
2 rows in set (0.00 sec)

الخلاصة

شرحنا في هذا الدرس كيفية إجراء عمليات على البيانات، إضافةً إلى إنشاء قاعدة بيانات، وجداولها، وتخصيص مستخدم لقاعدة البيانات، ثم رأينا فائدة بعض التلميحات التي تساعد في تسهيل إدارة قواعد البيانات عليك.
ترجمة -وبتصرّف- للمقال Learn MySQL / MariaDB for Beginners – Part 1 لصاحبه Gabriel Cánepa.

أداة mysqladmin هي أداةٌ سطريةٌ (أي تعمل من سطر الأوامر) تأتي مع خادوم MySQL ويستعملها مدراء قواعد البيانات لإجراء مهام أساسية لإدارة قواعد MySQL مثل ضبط كلمة مرور المستخدم root أو تغييرها، ومراقبة عمليات mysql وإعادة تحديث امتيازات المستخدمين والتحقق من حالة الخدمة …إلخ.

سنضع في هذا الدرس بعض أوامر mysqladmin المفيدة والمُستعمَلة من مدراء النظام أو مدراء قواعد البيانات يوميًا. يجب أن يكون خادوم MySQL مثبتًا على نظامك لإجراء هذه المهام.
إذا لم يكن خادوم MySQL مثبتًا على نظامك، أو كنتَ تستعمل إصدارًا قديمًا منه، فأنصحك بتثبيت أو تحديث نسختك كما ذكرنا في هذه المقالة.

1. كيفية ضبط كلمة مرور المستخدم root في MySQL؟

إذا كانت لديك نسخةٌ حديثةُ التثبيت من خادوم MySQL، فلن تحتاج إلى استخدام كلمة مرور للاتصال بها عبر المستخدم root، لكن ذلك ليس آمنًا، وأنصحك بضبط كلمة مرور له، وذلك بتنفيذ الأمر الآتي:

# mysqladmin -u root password YOURNEWPASSWORD

2. كيفية تغيير كلمة مرور المستخدم root؟

إذا أردتَ تحديث أو تغيير كلمة مرور المستخدم root في قواعد بيانات MySQL، فيمكنك الاستعانة بالأمر الآتي. لنفترض مثلًا أنَّ كلمة مرورك القديمة هي 123456 وأردتَ الآن تغييرها إلى كلمةٍ أخرى ولتكن xyz123:

# mysqladmin -u root -p123456 password 'xyz123'

3. كيفية التحقق إذا كان خادوم MySQL يعمل؟

استعمل الأمر الآتي لتعرف إن كان خادوم MySQL يعمل أم لا:

# mysqladmin -u root -p ping
Enter password:
mysqld is alive

4. كيف أعرف إصدار MySQL الذي أعمل عليه؟

الأمر الآتي سيُظهِر إصدار MySQL المُثبَّت على خادومك، إضافةً إلى حالته:

# mysqladmin -u root -p version
Enter password:
mysqladmin  Ver 8.42 Distrib 5.5.28, for Linux on i686
Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Server version          5.5.28
Protocol version        10
Connection              Localhost via UNIX socket
UNIX socket             /var/lib/mysql/mysql.sock
Uptime:                 7 days 14 min 45 sec
Threads: 2  Questions: 36002  Slow queries: 0  Opens: 15  Flush tables: 1  Open tables: 8  Queries per second avg: 0.059

5. كيف أعرف ما هي حالة خادوم MySQL الآن؟

يمكن أن يُظهِر الأمر mysqladmin حالة uptime بالإضافة إلى عدد الخيوط (threads) وإحصائيات عن الاستعلامات:

# mysqladmin -u root -ptmppassword status
Enter password:
Uptime: 606704  Threads: 2  Questions: 36003  Slow queries: 0  Opens: 15  Flush tables: 1  Open tables: 8  Queries per second avg: 0.059

6. كيفية التحقق من حالة جميع متغيرات خادوم MySQL وقيمها؟

استخدم الخيار extended-status لأمر mysqladmin لرؤية حالة جميع متغيرات خادوم MySQL والقيم المرتبطة بها. يجب أن تكون المخرجات شبيهةً بما يلي:

# mysqladmin -u root -p extended-status
Enter password:
+------------------------------------------+-------------+
| Variable_name                            | Value       |
+------------------------------------------+-------------+
| Aborted_clients                          | 3           |
| Aborted_connects                         | 3           |
| Binlog_cache_disk_use                    | 0           |
| Binlog_cache_use                         | 0           |
| Binlog_stmt_cache_disk_use               | 0           |
| Binlog_stmt_cache_use                    | 0           |
| Bytes_received                           | 6400357     |
| Bytes_sent                               | 2610105     |
| Com_admin_commands                       | 3           |
| Com_assign_to_keycache                   | 0           |
| Com_alter_db                             | 0           |
| Com_alter_db_upgrade                     | 0           |
| Com_alter_event                          | 0           |
| Com_alter_function                       | 0           |
| Com_alter_procedure                      | 0           |
| Com_alter_server                         | 0           |
| Com_alter_table                          | 0           |
| Com_alter_tablespace                     | 0           |
+------------------------------------------+-------------+

7. كيفية معرفة قيم جميع المتغيرات المُفعّلة لخادوم MySQL؟

لرؤية جميع القيم التي تعمل حاليًا في خادوم MySQL، أدخِل الأمر الآتي:

# mysqladmin  -u root -p variables
Enter password:
+---------------------------------------------------+----------------------------------------------+
| Variable_name                                     | Value                                        |
+---------------------------------------------------+----------------------------------------------+
| auto_increment_increment                          | 1                                            |
| auto_increment_offset                             | 1                                            |
| autocommit                                        | ON                                           |
| automatic_sp_privileges                           | ON                                           |
| back_log                                          | 50                                           |
| basedir                                           | /usr                                         |
| big_tables                                        | OFF                                          |
| binlog_cache_size                                 | 32768                                        |
| binlog_direct_non_transactional_updates           | OFF                                          |
| binlog_format                                     | STATEMENT                                    |
| binlog_stmt_cache_size                            | 32768                                        |
| bulk_insert_buffer_size                           | 8388608                                      |
| character_set_client                              | latin1                                       |
| character_set_connection                          | latin1                                       |
| character_set_database                            | latin1                                       |
| character_set_filesystem                          | binary                                       |
| character_set_results                             | latin1                                       |
| character_set_server                              | latin1                                       |
| character_set_system                              | utf8                                         |
| character_sets_dir                                | /usr/share/mysql/charsets/                   |
| collation_connection                              | latin1_swedish_ci                            |
+---------------------------------------------------+----------------------------------------------+

8. كيفية التحقق من جميع العمليات التي يستخدمها خادوم MySQL؟

الأمر الآتي سيعرض جميع العمليات التي تستعملها استعلامات قواعد بيانات MySQL:

# mysqladmin -u root -p processlist
Enter password:
+-------+---------+-----------------+---------+---------+------+-------+------------------+
| Id    | User    | Host            | db      | Command | Time | State | Info             |
+-------+---------+-----------------+---------+---------+------+-------+------------------+
| 18001 | rsyslog | localhost:38307 | rsyslog | Sleep   | 5590 |       |                  |
| 18020 | root    | localhost       |         | Query   | 0    |       | show processlist |
+-------+---------+-----------------+---------+---------+------+-------+------------------+

9. كيفية إنشاء قاعدة بيانات على خادوم MySQL؟

استخدم الأمر الآتي لإنشاء قاعدة بيانات جديدة في MySQL:

# mysqladmin -u root -p create databasename
Enter password:

للتحقق من إنشائها:

# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 18027
Server version: 5.5.28 MySQL Community Server (GPL) by Remi
Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| databasename       |
| mysql              |
| test               |
+--------------------+
8 rows in set (0.01 sec)
mysql>

10. كيفية حذف قاعدة بيانات في خادوم MySQL؟

لحذف قاعدة بيانات في خادوم MySQL، فأدخِل الأمر الآتي. سيُطلَب منك الموافقة على ذلك بكتابة الحرف y:

# mysqladmin -u root -p drop databasename
Enter password:
Dropping the database is potentially a very bad thing to do.
Any data stored in the database will be destroyed.
Do you really want to drop the 'databasename' database [y/N] y
Database "databasename" dropped

11. كيفية إعادة تحميل أو إعادة تحديث الامتيازات في MySQL؟

الأمر reload سيطلب من الخادوم إعادة تحميل جداول الامتيازات، بينما الأمر refresh سيؤدي إلى إعادة قراءة جميع الجداول وإعادة فتح ملفات التسجيل (log files):

# mysqladmin -u root -p reload;
# mysqladmin -u root -p refresh

12. كيفية إيقاف خادوم MySQL بأمان؟

لإيقاف خادوم MySQL إيقافًا آمنًا، فاكتب الأمر الآتي:

mysqladmin -u root -p shutdown
Enter password:

أو يمكنك استخدام الأمرين الآتيين لتشغيل أو إيقاف خادوم MySQL:

# /etc/init.d/mysqld stop
# /etc/init.d/mysqld start

13. بعض أوامر «التنظيف»

هذه بعض أوامر التنظيف (flush) مع شرحٍ لها:

• flush-hosts: حذف جميع معلومات المُضيف من التخزين المؤقت.
• flush-tables: «تنظيف» جميع الجداول.
• flush-threads: تنظيف التخزين المؤقت للخيوط (threads).
• flush-logs: تنظيف السجلات.
• flush-privileges: إعادة تحميل جداول الامتيازات (مثل reload).
• flush-status: مسح متغيرات الحالة (status variables).

• # mysqladmin -u root -p flush-hosts
  # mysqladmin -u root -p flush-tables
  # mysqladmin -u root -p flush-threads
  # mysqladmin -u root -p flush-logs
  # mysqladmin -u root -p flush-privileges
  # mysqladmin -u root -p flush-status

   

14. كيفية «قتل» عمليات عملاء MySQL المتوقفة؟

يمكنك استخدام الأمر الآتي للتعرف على عمليات عملاء MySQL المتوقفة (sleeping):

# mysqladmin -u root -p processlist
Enter password:
+----+------+-----------+----+---------+------+-------+------------------+
| Id | User | Host      | db | Command | Time | State | Info             |
+----+------+-----------+----+---------+------+-------+------------------+
| 5  | root | localhost |    | Sleep   | 14   |       |                  |
| 8  | root | localhost |    | Query   | 0    |       | show processlist |
+----+------+-----------+----+---------+------+-------+------------------+

يمكنك الآن تشغيل الأمر الآتي مع استخدام الخيار kill متبوعًا برقم العملية كما يلي:

# mysqladmin -u root -p kill 5
Enter password:
+----+------+-----------+----+---------+------+-------+------------------+
| Id | User | Host      | db | Command | Time | State | Info             |
+----+------+-----------+----+---------+------+-------+------------------+
| 12 | root | localhost |    | Query   | 0    |       | show processlist |
+----+------+-----------+----+---------+------+-------+------------------+

أما إذا أردت «قتل» (اصطلاح «قتل» [kill] في يونكس يعني إيقاف العملية قسريًا) أكثر من عملية معًا، فافصل بين أرقام العمليات بفاصلة كما في المثال الآتي:

# mysqladmin -u root -p kill 5,10

15. كيفية تشغيل أكثر من أمر لأداة mysqladmin معًا؟

إذا أردت تشغيل أكثر من أمر mysqladmin معًا فيمكنك فعل ذلك بذكرها بعضها تلو بعض كالآتي:

# mysqladmin  -u root -p processlist status version
Enter password:
+----+------+-----------+----+---------+------+-------+------------------+
| Id | User | Host      | db | Command | Time | State | Info             |
+----+------+-----------+----+---------+------+-------+------------------+
| 8  | root | localhost |    | Query   | 0    |       | show processlist |
+----+------+-----------+----+---------+------+-------+------------------+
Uptime: 3801  Threads: 1  Questions: 15  Slow queries: 0  Opens: 15  Flush tables: 1  Open tables: 8  Queries per second avg: 0.003
mysqladmin  Ver 8.42 Distrib 5.5.28, for Linux on i686
Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Server version          5.5.28
Protocol version        10
Connection              Localhost via UNIX socket
UNIX socket             /var/lib/mysql/mysql.sock
Uptime:                 1 hour 3 min 21 sec
Threads: 1  Questions: 15  Slow queries: 0  Opens: 15  Flush tables: 1  Open tables: 8  Queries per second avg: 0.003

16. كيفية الاتصال بخادوم MySQL بعيد

للاتصال بخادوم MySQL بعيد فاستخدام الخيار ‎-h (أي host، ويعني المضيف) وبعده عنوان IP للخادوم البعيد:

# mysqladmin  -h 172.16.25.126 -u root -p

17. كيفية تنفيذ أمر على خادوم MySQL بعيد

لنقل أنك تريد معرفة حالة (status) خادوم MySQL بعيد، فسيكون الأمر حينئذٍ كالآتي:

# mysqladmin  -h 172.16.25.126 -u root -p status

18. تشغيل وإيقاف استنساخ MySQL على خادوم ثانوي

استخدم الأمرين الآتيين لتشغيل أو إيقاف استنساخ MySQL (أي MySQL replication):

# mysqladmin  -u root -p start-slave
# mysqladmin  -u root -p stop-slave

19. كيفية تخزين معلومات التنقيح في MySQL إلى السجلات

معلومات التنقيح هي المعلومات التي تخبرنا ما هو استخدام الذاكرة ومعلومات متفرقة عن الاستعلامات، ويمكننا كتابتها إلى ملف سجل MySQL باستخدام الأمر الآتي:

# mysqladmin  -u root -p debug
Enter password:

20. كيفية رؤية الخيارات المتاحة للاستخدام في mysqladmin

لمعرفة المزيد من خيارات الأمر mysqladmin وطريقة استخدامه، فاطلع على المساعدة التي تظهر بكتابة الأمر الآتي، والذي سيعرض قائمة بالخيارات المتوافرة:

# mysqladmin --help

حاولتُ قدر المستطاع أن أُضمِّن أغلبية أوامر mysqladmin مع أمثلةٍ عنها في هذا الدرس، لكن لا مانع من محاولة استكشاف غيرها، وربما تشاركنا بها في التعليقات.
ترجمة -وبتصرّف- للمقال ‎20 MySQL (Mysqladmin) Commands for Database Administration in Linux لصاحبه Ravi Saive

قاعدة البيانات هي بُنيةٌ هيكليةٌ تحتوي على مجموعة من البيانات المُخزَّنة إلكترونيًا؛ وكان مفهوم قواعد البيانات معروفًا لدى أسلافنا قبل وجود الحواسيب، لكن إنشاء وصيانة تلك القواعد كان أمرًا شاقًا ومملًا. فلنقل أنَّ لدينا قاعدة بيانات فيها 100 صفحة، وأردتَ أن تبحث عن جميع الموظفين الذين يتقاضون أقل من 50 ألف دولار سنويًا، فتخيّل مدى صعوبة الأمر ومقدار الوقت الذي سيستغرقه في ذاك الحين.

أما حاليًا، فأنت تصادف قواعد البيانات في كل مكان، إذ تعمل ملايين قواعد البيانات في أنحاء العالم لتخزين والحصول على أي نوع من أنواع البيانات كالبيانات العسكرية وسجلات الموظفين أو حتى مواقع الويب وتقنياتها.
تُصنّف قواعد البيانات عمومًا على أنها جزءٌ من «السند الخلفي» (back-end)، وذلك لعدم ظهورها للمستخدم النهائي ولعدم تعامل المستخدم معها مباشرةً. إذ تعمل قواعد البيانات مع لغةٍ برمجيةٍ مثل PHP أو VB أو ASP.NET وتطلب تلك اللغات من قواعد البيانات إجراء عملية ما.
تتوافر عدِّة خواديم قواعد بيانات وعملاءٍ لها مثل Oracle و MySQL و MySQLi و MariaDB و MongoDB …إلخ. تتشابه طريقة التعامل مع هذه البيانات، والبنية اللغوية لأوامرها شبه متماثلة، واحتراف التعامل مع إحداها يعني أنَّك ستستطيع التحكم بأريحية مع البقية، وعملية تعلم كتابة الاستعلامات (queries) هي عملية سهلة وممتعة.
لنبدأ ببعض الاستعلامات البسيطة على قواعد البيانات؛ وسنستخدم قواعد MySQL التي تأتي مع أغلبية توزيعات لينُكس افتراضيًا، وتستطيع بسهولة تثبيتها من مستودعات توزيعتك إن لم تكن مثبتةً لديك.
لكن ربما ينتابك فضولٌ حول معنى «استعلام» (Query)، فبأبسط الكلمات: هي شيفرةٌ بسيطةٌ (أو أمر) تُرسَل إلى قواعد البيانات للحصول على النتيجة المطلوبة.

تثبيت قواعد بيانات MySQL

يمكنك استخدام مديرَي الحزم yum أو apt لتثبيت قواعد بيانات MySQL:

# yum install mysql mysql-client mysql-server  (on Yum based Systems)
# apt-get install mysql mysql-client mysql-server (on Apt based Systems)

تشغيل خادوم MySQL

تستطيع تشغيل خدمة MySQL كالآتي:

# service mysqld start

أو:

# service mysql start

أذكِّرك أنَّ رمز # في بداية السطر يعني أنَّ عليك تنفيذ الأمر بامتيازات الجذر.
سيُطلَب منك أثناء تثبيت قاعدة بيانات MySQL إعداد كلمة مرور لحساب المدير فيها… بعدئذٍ ستتمكن من الوصول إلى مِحَث (prompt، أي المكان الذي تكتب فيه الأوامر) MySQL بتنفيذك للأمر الآتي:

# mysql -u root -p

ضع اسم المستخدم الذي ضبطته بدلًا من root، وأدخِل كلمة مرورك عند طلبها، فإذا كانت معلومات الدخول صحيحةً، فيمكنك أن تشاهد مِحَث MySQL مباشرةً:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 195 
Server version: 5.5.31-0+wheezy1 (Debian) 
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved. 
Oracle is a registered trademark of Oracle Corporation and/or its affiliates. 
Other names may be trademarks of their respective owners. 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
Mysql>

تستطيع الآن البدء بتنفيذ الاستعلامات وتعلم إنشاء الاستعلامات.

إنشاء قاعدة بيانات

سنُنشِئ الآن قاعدة بيانات باسم tecmint كالتّالي:

mysql> create database tecmint ;
Query OK, 1 row affected (0.02 sec) 
mysql>

ظهر عندنا أنَّ الاستعلام السابق قد نُفِّذَ تنفيذًا سليمًا، وهذا يعني أنَّ قاعدة البيانات قد أُنشِئَت. يمكنك التحقق من وجود قاعدة البيانات الجديدة بتنفيذ الاستعلام الآتي:

mysql> show databases; 
+--------------------+
| Database           | 
+--------------------+ 
| information_schema | 
| mysql              | 
| performance_schema | 
| tecmint            | 
| test               | 
+--------------------+ 
9 rows in set (0.00 sec) 
mysql>

لاحظ وجود قاعدة البيانات في مخرجات الاستعلام أعلاه.

اختيار قاعدة البيانات

علينا قبل إجراء الاستعلامات على إحدى قواعد البيانات أن نختارها، وذلك باستخدام التعليمة use كما يلي:

mysql> use tecmint;
Database changed
mysql>

إنشاء الجداول في MySQL

لنقل أننا نريد إنشاء جدول باسم minttec في قاعدة البيانات وفيه ثلاثة حقول:

mysql> CREATE TABLE minttec (
-> id Int(3), 
-> first_name Varchar (15), 
-> email Varchar(20) 
-> ); 
Query OK, 0 rows affected (0.08 sec) 
mysql>

لاحظ أنَّ الاستعلام السابق قد أعاد OK مما يعني أنَّ الجدول قد أُنشِئ دون أخطاء. نفِّذ الاستعلام الآتي للتحقق من إنشاء الجدول:

mysql> show tables; 
+-------------------+ 
| Tables_in_tecmint | 
+-------------------+ 
| minttec           | 
+-------------------+ 
1 row in set (0.00 sec) 
mysql>

جيد، كل شيء يسير كما ينبغي. يمكننا أن نُطالِع الأعمدة التي أنشأتها في جدول minttec كما يلي:

mysql> show columns from minttec; 
+------------+-------------+------+-----+---------+-------+ 
| Field      | Type        | Null | Key | Default | Extra | 
+------------+-------------+------+-----+---------+-------+ 
| id         | int(3)      | YES  |     | NULL    |       | 
| first_name | varchar(15) | YES  |     | NULL    |       | 
| email      | varchar(20) | YES  |     | NULL    |       | 
+------------+-------------+------+-----+---------+-------+ 
3 rows in set (0.00 sec)
mysql>

سأخبرك الآن عن أنواع البيانات الموجودة في الجدول السابق ومعانيها:
- int: عدد صحيح.
- varchar: سلسلة من المحارف التي لا يتجاوز طولها الرقم المُعرَّف. القيمة الرقمية التي تأتي بعد النوع هي «طول» الحقل الذي ستُخزَّن فيه البيانات.
لنقل الآن أننا نريد إضافة عمود جديد باسم last_name بعد العمود first_name سنقوم بذلك على النّحو التّالي:

mysql> ALTER TABLE minttec ADD last_name varchar (20) AFTER first_name; 
Query OK, 0 rows affected (0.16 sec)
Records: 0  Duplicates: 0  Warnings: 0

ثم سنتحقق من أعمدة الجدول:

mysql> show cسolumns from minttec; 
+------------+-------------+------+-----+---------+-------+ 
| Field      | Type        | Null | Key | Default | Extra | 
+------------+-------------+------+-----+---------+-------+ 
| id         | int(3)      | YES  |     | NULL    |       | 
| first_name | varchar(15) | YES  |     | NULL    |       | 
| last_name  | varchar(20) | YES  |     | NULL    |       | 
| email      | varchar(20) | YES  |     | NULL    |       | 
+------------+-------------+------+-----+---------+-------+ 
4 rows in set (0.00 sec) 
mysql>

إضافة أعمدة في MySQL

سنضيف الآن عمودًا باسم country بعد العمود email كالتّالي:

mysql> ALTER TABLE minttec ADD country varchar (15) AFTER email; 
Query OK, 0 rows affected (0.16 sec) 
Records: 0  Duplicates: 0  Warnings: 0 
mysql>

لنتحقق من إضافة العمود:

mysql> show columns from minttec; 
+------------+-------------+------+-----+---------+-------+ 
| Field      | Type        | Null | Key | Default | Extra | 
+------------+-------------+------+-----+---------+-------+
| id         | int(3)      | YES  |     | NULL    |       | 
| first_name | varchar(15) | YES  |     | NULL    |       | 
| last_name  | varchar(20) | YES  |     | NULL    |       | 
| email      | varchar(20) | YES  |     | NULL    |       | 
| country    | varchar(15) | YES  |     | NULL    |       | 
+------------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec) 
mysql>

إضافة قيمة إلى الحقول

ماذا لو أردنا إضافة قيم إلى حقول الجدول؟ سنقوم بذلك كالتّالي:

mysql> INSERT INTO minttec VALUES ('1' , 'Ravi' , 'Saive' , 'raivsaive@xyz.com' , 'India' );
Query OK, 1 row affected (0.02 sec) 
mysql>

ماذا عن إضافة أكثر من قيمة واحدة في آنٍ واحد:

mysql> INSERT INTO minttec VALUES ('2' , 'Narad' , 'Shrestha' , 'narad@xyz.com' , 'India' ), ('3' , 'user' , 'singh' , 'user@xyz.com' , 'Aus' ), ('4' , 'tecmint' , '[dot]com' , 'tecmint@gmail.com' , 'India' );
Query OK, 3 rows affected (0.05 sec) 
Records: 3  Duplicates: 0  Warnings: 0

سنتحقق من إدخال المعلومات السابقة في الجدول:

mysql> select * from minttec; 
+------+------------+-----------+-------------------+---------+ 
| id   | first_name | last_name | email             | country | 
+------+------------+-----------+-------------------+---------+ 
|    1 | Ravi       | Saive     | raivsaive@xyz.com | India   | 
|    2 | Narad      | Shrestha  | narad@xyz.com     | India   | 
|    3 | user       | singh     | user@xyz.com      | Aus     | 
|    4 | tecmint    | [dot]com  | tecmint@gmail.com | India   | 
+------+------------+-----------+-------------------+---------+ 
4 rows in set (0.00 sec)
mysql>

حذف قيم في حقول الجدول

لنقل أنَّ المُدخَلة الثالثة في الناتج السابق لم تكن صحيحةً ونريد أن نحذفها:

mysql> DELETE FROM minttec WHERE id = 3;
Query OK, 1 row affected (0.02 sec)

التحقق من تنفيذ الاستعلام السابق:

mysql> select * from minttec;
+------+------------+-----------+-------------------+---------+ 
| id   | first_name | last_name | email             | country | 
+------+------------+-----------+-------------------+---------+
|    1 | Ravi       | Saive     | raivsaive@xyz.com | India   | 
|    2 | Narad      | Shrestha  | narad@xyz.com     | India   | 
|    4 | tecmint    | [dot]com  | tecmint@gmail.com | India   | 
+------+------------+-----------+-------------------+---------+
3 rows in set (0.00 sec)

تحديث قيم في حقول الجدول

لنفترض أننا نريد تعديل السجل ذي المُعرِّف 4 (أي id=4):

mysql> UPDATE minttec SET id = 3 WHERE first_name = 'tecmint'; 
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql>

لاحظ أنَّ الاستعلام السابق ليس مثاليًا، فستُغيّر الحقل id إلى القيمة 4 لكل سجل يكون اسمه الأول مساويًا للقيمة tecmint. من الجيد استخدام مُطابقة أكثر من عمود واحد في عبارة WHERE لتقليل نسبة حدوث خطأ، كما في الاستعلام الآتي:

mysql> UPDATE minttec SET id = 6 WHERE first_name = 'tecmint'AND last_name = '[dot]com'; 
Query OK, 1 row affected (0.03 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql>

حذف أحد أعمدة الجدول

لنفترض أنَّك تحتاج إلى حذف أحد أعمدة الجدول غير الضرورية، ولنقل أنَّه العمود country:

mysql> ALTER TABLE minttec drop country; 
Query OK, 3 rows affected (0.15 sec)
Records: 3  Duplicates: 0  Warnings: 0
mysql>

عرض بُنية الجدول:

mysql> select * from minttec; 
+------+------------+-----------+-------------------+ 
| id   | first_name | last_name | email             | 
+------+------------+-----------+-------------------+ 
|    1 | Ravi       | Saive     | raivsaive@xyz.com | 
|    2 | Narad      | Shrestha  | narad@xyz.com     | 
|    6 | tecmint    | [dot]com  | tecmint@gmail.com | 
+------+------------+-----------+-------------------+
3 rows in set (0.00 sec) 
mysql>

إعادة تسمية الجداول في MySQL

ربما لا تجد اسم minttec ذا معنى، وتريد تحويله مثلًا إلى tecmint_table سنقوم بتغييره كالتّالي:

mysql> RENAME TABLE minttec TO tecmint_table; 
Query OK, 0 rows affected (0.03 sec)
mysql>

عرض جميع الجداول

لرؤية جميع الجداول الموجودة في قاعدة البيانات الحالية:

mysql> show tables; 
+-------------------+ 
| Tables_in_tecmint | 
+-------------------+ 
| tecmint_table     | 
+-------------------+
1 row in set (0.00 sec) 
mysql>

لاحظ إعادة تسمية الجدول.
لنأخذ الآن نسخةً احتياطيةً من قاعدة البيانات السابقة، وذلك عبر تنفيذ استعلامٍ من سطرٍ وحيد دون استخدام أيّة أدواتٍ معقدة. نفِّذ الشيفرة أدناه في سطر الأوامر وليس في مِحَث MySQL:

# mysqldump -u root -p tecmint > tecmint.sql
check the dumped file on your desktop which would have contents something like
-- MySQL dump 10.13  Distrib 5.5.31, for debian-linux-gnu (i686) --
-- Server version 5.5.31-0+wheezy1 -- 
Dump completed on 2013-09-02 12:55:37

من المستحسن الإبقاء على نسخ احتياطية لقواعد بياناتك، فاستعادة قاعدة بيانات MySQL هو أمرٌ بسيطٌ ويجرى بتنفيذ أمرٍ قصيرٍ في سطر الأوامر (أكرِّر، في سطر الأوامر وليس في مِحَث MySQL).
لكن ما رأيك أن نحذف قاعدة البيانات أولًا لنرى كيف ستتم عملية الاستعادة؟

حذف قاعدة بيانات

mysql> drop database tecmint; 
Query OK, 1 row affected (0.02 sec)

تحقق من وجود قاعدة بيانات باسم tecmint في خادوم قواعد البيانات عندك:

mysql> show databases; 
+--------------------+ 
| Database           | 
+--------------------+ 
| information_schema | 
| my_database        | 
| mysql              | 
| performance_schema | 
| phpmyadmin         | 
| sisso              | 
| test               | 
+--------------------+
7 rows in set (0.00 sec) 
mysql>

رائع، لقد فقدنا قاعدة البيانات :-) لكن لا تقلق، فلقد أخذنا نسخةً احتياطيةً منها منذ قليل.

استعادة قاعدة بيانات من نسخةٍ احتياطيةٍ منها

نفِّذ الأمر الآتي لاستعادة قاعدة البيانات:

# mysql -u root -p tecmint < tecmint.sql
Enter password:
ERROR 1049 (42000): Unknown database 'tecmint'

ماذا حدث؟ لقد واجهنا رسالة خطأ، فنحن لم نُنشِئ قاعدة بيانات باسم tecmint بعد. لذا اذهب إلى مِحَث MySQL وأنشِئها:

mysql> create database tecmint; 
Query OK, 1 row affected (0.00 sec) 
mysql>

حان الآن الوقت لتنفيذ أمر الاستعادة في سطر الأوامر:

# mysql -u root -p tecmint < tecmint.sql 
Enter password:

لم تظهر بوجهنا أيّة رسائل خطأ مرعبة، لنتحقق الآن من وجود القاعدة في خادومنا:

mysql> show databases; 
+--------------------+ 
| Database           | 
+--------------------+ 
| information_schema | 
| mysql              | 
| performance_schema | 
| tecmint            | 
| test               | 
+--------------------+ 
8 rows in set (0.00 sec)

ثم سنتحقق من محتويات قاعدة البيانات (أي جداولها):

mysql> show tables from tecmint;
+-------------------+ 
| Tables_in_tecmint | 
+-------------------+ 
| tecmint_table     | 
+-------------------+ 
1 row in set (0.00 sec)
mysql>

وليطمئن قلبنا، سنرى محتويات الجدول:

mysql> select * from tecmint_table; 
+------+------------+-----------+-------------------+ 
| id   | first_name | last_name | email             | 
+------+------------+-----------+-------------------+ 
|    1 | Ravi       | Saive     | raivsaive@xyz.com | 
|    2 | Narad      | Shrestha  | narad@xyz.com     | 
|    6 | tecmint    | [dot]com  | tecmint@gmail.com | 
+------+------------+-----------+-------------------+
3 rows in set (0.00 sec)

هذه ليست نهاية المطاف، ما زال أمامنا مناقشة بعض المفاهيم مثل primary key، و foreign key…
ترجمة -وبتصرّف- للمقال MySQL Basic Database Administration Commands – Part I.