إنّ Mytop هي أداة سطر أوامر مفتوحة المصدر open source تُستخدَم لمراقبة أداء MySQL، وهي مستوحاة من أداة مراقبة نظام لينِكس التي تُدعى top ومُشابِهة لها في الشّكل والمظهر، تتصل Mytop إلى خادوم MySQL وتقوم بشكل دوري بتشغيل الأمرين show processlist و show global status، وتقوم بعدها بتلخيص المعلومات بشكل مفيد، نستطيع باستخدام Mytop مراقبة (بشكلٍ آني real-time) مناقشات MySQL threads، الاستعلامات queries، وزمن التشغيل uptime، بالإضافة إلى أنّها ترى أي مستخدم يقوم بتنفيذ استعلامات على أي قاعدة بيانات، وأي الاستعلامات تجري ببطء والمزيد من ذلك، نستطيع استخدام كل هذه المعلومات لتحسين أداء خادوم MySQL.

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

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

قبل البدء في هذا الدرس ينبغي أن نمتلك ما يلي لدينا:

• CentOS 7 64-bit Droplet (تعمل أيضًا مع CentOS 6).
• مستخدم غير جذري non-root مع صلاحيّات sudo سيتم تنفيذ جميع الأوامر عن طريق هذا المستخدم.
• خادوم MySQL يعمل على الـ Droplet.

الخطوة الأولى – تثبيت Mytop

فلنقم بتثبيت الحِزَم المطلوبة من أجل mytop.

نحتاج في البداية إلى تثبيت مستودع yum الذي يُدعى (EPEL (Extra Packages for Enterprise Linux على الخادوم، إنّ EPEL هي مجموعة ذات اهتمامات مشتركة بتوزيعة Fedora تقوم بإنشاء، إدارة، والحفاظ على مجموعة حِزَم برمجيّة إضافيّة add-on مفتوحة المصدر عالية الجّودة من أجل Enterprise Linux، نقوم بتنفيذ الأمر التالي لتثبيت وتمكين مستودع EPEL على خادومنا:

على CentOS 7:

sudo rpm -ivh http://dl.fedoraproject.org/pub/epel/7/x86_64/e/epel-release-7-5.noarch.rpm

على CentOS 6:

sudo rpm -ivh http://download.fedoraproject.org/pub/epel/6/x86_64/epel-release-6-8.noarch.rpm

وقبل المتابعة نتحقّق أنّه تم تمكين المستودع EPEL باستخدام:

sudo yum repolist

إن تمّ تمكينه سنشاهد في الخَرْج ما يلي:

epel/x86_64 Extra Packages for Enterprise Linux 7 - x86_64

فلنقم بعد ذلك بحماية الحِزَم الأساسيّة من EPEL باستخدام إضافة yum plugin التي تُدعى protectbase:

sudo yum install yum-plugin-protectbase.noarch -y

الغرض من الإضافة protectbase هو حماية بعض مستودعات yum من تحديثات المستودعات الأخرى، فلن يتم تحديث أو تجاوز override الحِزَم الموجودة في المستودعات المحميّة بواسطة الحِزَم في المستودعات غير المحميّة حتى ولو كان المستودع غير المحمي يملك إصدارًا أحدث.

نحن الآن مستعدون لتثبيت حِزمة mytop، فلنقم بتنفيذ الأمر التالي لتثبيتها:

sudo yum install mytop -y

سيقوم هذا الأمر بتثبيت حِزمة mytop بالإضافة إلى جميع اعتمادياتها dependencies والتي هي في معظمها وحدات perl modules.

الخطوة الثانية – ضبط إعدادات Mytop

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

sudo nano /root/.mytop

نضيف المحتوى التالي إلى الملف ونقوم بحفظه وإغلاقه:

root/.mytop/

host=localhost
db=mysql
delay=5
port=3306
socket=
batchmode=0
color=1
idle=1

سيتم استخدام ملف الإعدادات عندما نقوم بتشغيل mytop بشكل مباشر كمستخدم جذري root وعندما نقوم بتشغيلها باستخدام الأمر sudo كمستخدم غير جذري يملك صلاحيّات sudo.

نستطيع القيام بتغييرات إلى ملف الإعدادات اعتمادًا على احتياجاتنا، على سبيل المثال يُحدِّد الخيار delay الفترة الزمنيّة مقدرةً بالثانية بين تحديثات العرض display، فإن كُنّا نرغب بتحديث عرض mytop كل 3 ثوان بإمكاننا تحرير الملف root/.mytop/ باستخدام:

sudo nano /root/.mytop

ومن ثمّ تغيير ما يلي:

root/.mytop/

delay=3

يُحدِّد المُعامِل idle إذا ما كان سيسمح للمناقشات threads الخاملة idle (النائمة) بالظهور في قائمة شاشة عرض mytop، الوضع الافتراضي هو إظهار المناقشات الخاملة، إن تمّ حذف المناقشات الخاملة سينعكس ترتيب الفرز sorting الافتراضي بحيث تظهر أطول الاستعلامات قيد التشغيل في أعلى القائمة، إن كُنّا نرغب في فعل هذا نُحرّر الملف root/.mytop/ ونغيّر ما يلي:

root/.mytop/

idle=0

نستطيع الرجوع إلى صفحات mytop اليدويّة للمزيد من المعلومات عن جميع المُعامِلات في ملف الإعدادات والتي تحتوي على وصف لكل مُعامِل، للوصول إلى الصفحات اليدويّة نستخدم الأمر:

man mytop

نستطيع كتابة q للخروج من الدّليل.

الخطوة الثالثة – الاتصال إلى Mytop

سنناقش في هذا القسم كيفيّة الاتصال إلى mytop واستخدامها لعرض استعلامات MySQL.

تتطلّب Mytop اعتمادات credentials للنفاذ إلى قاعدة البيانات، والتي يمكن تزويدها عبر مُحِث prompt في سطر الأوامر أو عبر تخزينها في ملف الإعدادات، سنستخدم من أجل أمان أفضل الخيار prompt-- والذي يسأل كل مرّة عن كلمة السّر.

فلنتصل إلى mytop باستخدام:

sudo mytop --prompt

ونُدخِل كلمة سر المستخدم root في MySQL في المُحِث، بإمكاننا أيضًا استخدام العديد من مُعطيات arguments سطر الأوامر مع الأمر mytop، نرجو الرجوع إلى الصفحات اليدويّة من أجل الحصول على قائمة كاملة بها، على سبيل المثال إن كُنّا نرغب باستخدام مستخدم mysql مُختلف مثل sammy للاتصال إلى mytop نقوم باستخدام الأمر التالي:

sudo mytop -u sammy --prompt

وللاتصال ومراقبة قاعدة بيانات مُحدّدة فقط نستخدم الأمر التالي:

sudo mytop -d databasename --prompt

للخروج من mytop والعودة إلى مُحث الصّدفة shell prompt نكتب q.

الخطوة الرابعة – عرض وتفسير شاشة عرض Mytop

سنرى في هذا القسم كيفيّة تفسير شاشة عرض mytop والميزات المختلفة التي تُقدّمها هذه الأداة.

حالما نتصل إلى mytop باستخدام mytop --prompt سيتم أخذنا إلى طريقة عرض المناقشات thread view، والتي ستظهر خَرْج مشابه لما يلي:

Output of mytop

MySQL on localhost (5.5.41-MariaDB)                up 0+00:05:52 [01:33:15]
Queries: 148 qps: 0 Slow: 0.0              Se/In/Up/De(%): 09/00/00/00
qps now: 2 Slow qps: 0.0 Threads: 6 ( 5/ 0) 67/00/00/00
Key Efficiency: 2.0% Bps in/out: 14.7/320.7k Now in/out: 192.5/731.8k

Id   User   Host/IP     DB      Time   Cmd     Query or State
--   ----   -------     --      ----   ---     ----------
 2   root   localhost   mysql     0    Query    show full processlist
16   root   localhost             0             Sleep
17   root   localhost   testdb    0    Query    SELECT * FROM dept_emp
18   root   localhost   testdb    0    Query    SELECT * FROM dept_emp
19   root   localhost   testdb    0    Query    SELECT * FROM dept_emp
20   root   localhost   testdb    0    Query    SELECT * FROM dept_emp

نستطيع العودة إلى طريقة العرض هذه إن كُنّا في طريقة عرض أخرى بكتابة t.

تُقسم شاشة العرض السّابقة إلى قسمين، تُؤلّف الأسطر الأربعة الأولى الترويسة header والتي يُمكن تشغيلها وإيقافها بضغط SHIFT-H، تحتوي الترويسة على معلومات موجزة حول خادوم MySQL لدينا.

• يُحدِّد السطر الأول اسم المضيف hostname للخادوم وإصدار MySQL الموجود، يُظهر القسم الأيمن منه زمن التشغيل uptime لعمليّة خادوم MySQL بصيغة الأيام+السّاعات:الدّقائق:الثواني (days+hours:minutes:seconds) بالإضافة للوقت الحالي.
• يعرض السطر الثاني العدد الكلّي للاستعلامات التي قام الخادوم بمعالجتها (148 في حالتنا)، متوسط عدد الاستعلامات في الثانية، عدد الاستعلامات البطيئة، والنسبة المئوية للاستعلامات اختيار Select، إدخال Insert، تحديث Update، وحذف Delete.
• يُظهِر السطر الثالث قيم آنيّة real-time منذ التحديث refresh الأخير لـ mytop، إنّ زمن التّحديث (التأخير) الطبيعي في mytop هو 5 ثوان، لذا إن تمّ إجراء 100 استعلام خلال آخر 5 ثوان منذ التحديث سيكون عدد qps now هو 20، الحقل الأول هو عدد الاستعلامات في الثانية (qps now: 2). القيمة الثانية هي عدد الاستعلامات البطيئة في الثانية، يُشير القسم Threads: 6 ( 5/ 0) إلى وجود 6 مناقشات threads مُتّصلة، 5 منها نشطة (واحدة منها نائمة) وعدم وجود أي مناقشات (الرقم 0) في الذّاكرة المؤقّتة cache للمناقشات، يُظهر الحقل الأخير في السّطر الثالث النّسبة المئويّة للاستعلام، كما هو الحال في السّطر السابق، ولكن منذ التحديث الأخير لـ mytop.
• يعرض السّطر الرّابع فعاليّة key buffer (وهو عدد المرات التي فيها قراءة المفاتيح keys من الـ buffer بدلًا من القرص) وعدد البايتات التي أرسلتها واستقبلتها MySQL، كلاهما بالمجمل ومنذ آخر تحديث لـ mytop. تُظهِر Key Efficiency: 2.0% أنّه يتم قراءة 2% من المفاتيح من الـ buffer وليس من القرص، تُظهِر Bps in/out: 14.7/320.7k أنّه منذ بدء التشغيل تلقّت MySQL ما يُعادِل 14.7kbps من حركة البيانات traffic الواردة و 320.7kbps من حركة البيانات الصادرة، يُظهِر Now in/out حركة البيانات أيضًا ولكن منذ آخر تحديث لـ mytop.

يعرض القسم الثاني من شاشة العرض مناقشات MySQL الحاليّة مع فرزها بحسب زمن خمولها (الأقل خمولًا أولًا)، نستطيع عكس ترتيب الفرز بضغط O عند الحاجة لذلك، يتم هنا أيضًا عرض مُعرِّف المناقشة thread id، اسم المستخدم، المُضيف الذي يتصل منه المستخدم، قاعدة البيانات التي يتصل إليها المستخدم، زمن الخمول مُقدّرًا بالثانية، الأمر الذي تقوم المناقشة بتنفيذه (أو حالة المناقشة)، والقسم الأول من معلومات الاستعلام، إن كانت المناقشة في حالة استعلام Query (أي يعرض العمود Cmd القيمة Query) فسيعرض العمود التالي الذي يُدعى Query or State القسم الأول من الاستعلام الذي يتم تنفيذه، أمّا إن كانت حالة الأمر هي نائم Sleep أو خامل Idle فسيكون العمود Query or State فارغًا عادةً، في مثال الخَرْج السّابق لدينا فإنّ المناقشة ذات المُعرِّف id 2 هي فعليًّا mytop والتي تقوم بتشغيل الاستعلام show processlist لجمع المعلومات، والمناقشة ذات المُعرِّف 16 نائمة (أي لا تقوم بمعالجة استعلام ولكنّها تبقى متصلة)، والمناقشة ذات المُعرِّف 17 تقوم بتشغيل استعلام SELECT على قاعدة البيانات testdb.

الآن وقد فهمنا أساسيّات شاشة عرض mytop، سنرى كيفيّة استخدامها لجمع المزيد من المعلومات حول مناقشات واستعلامات MySQL، فلنلقِ نظرة على شاشة عرض mytop التّالية:

[secondary_output Output of mytop]

MySQL on localhost (5.5.41-MariaDB) up 0+00:13:10 [23:54:45]
Queries: 2.8k qps: 4 Slow: 51.0 Se/In/Up/De(%): 45/00/00/00
qps now: 17 Slow qps: 0.0 Threads: 52 ( 51/ 0) 96/00/00/00
Key Efficiency: 100.0% Bps in/out: 215.4/ 7.6M Now in/out: 2.0k/16.2M

Id     User   Host/IP     DB       Time   Cmd     Query or State
--     ----   -------     --       ----   ---     ----------
  34   root   localhost   testdb    0     Query   show full processlist
1241   root   localhost             1             Sleep
1242   root   localhost   testdb    1     Query   SELECT * FROM dept_emp
1243   root   localhost   testdb    1     Query   SELECT * FROM dept_emp
1244   root   localhost   testdb    1     Query   SELECT * FROM dept_emp
1245   root   localhost   testdb    1     Query   SELECT * FROM dept_emp
1246   root   localhost   testdb    1     Query   SELECT * FROM dept_emp
1247   root   localhost   testdb    1     Query   SELECT * FROM dept_emp

تكون الاستعلامات مبتورة في طريقة عرض المناقشات thread view (طريقة العرض الافتراضيّة)، ولمشاهدة كامل الاستعلام بإمكاننا أن نضغط F وسيتم سؤالنا كما يلي:

Full query for which thread id:

نُدخِل مُعرِّف المناقشة thread id التي نريد عرض استعلامها، على سبيل المثال نكتب 1244 وسنشاهد ما يلي:

Thread 1244 was executing following query:

SELECT * FROM dept_emp WHERE ...

-- paused. press any key to resume or (e) to explain --

نستطيع كتابة e لشرح explain الاستعلام، والذي يقوم بشرح الاستعلام الذي يتم تنفيذه حتى نعرف إذا ما كان هذا هو الاستعلام الأمثل، إنّ EXPLAIN هي واحدة من أقوى الأدوات لفهم وتحسين استعلامات MySQL الصعبة، على سبيل المثال:

EXPLAIN SELECT * FROM dept_emp:

*** row 1 ***
        table: dept_emp
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 332289
        Extra: NULL
-- paused. press any key to resume --

بإمكاننا أن نضغط على أي مفتاح للخروج من هذا الوضع أو نكتب t للعودة إلى طريقة عرض المناقشات الافتراضيّة.

ومن طرق العرض المفيدة الأخرى المتاحة في mytop هي طريقة عرض الأوامر command view، وللوصول إليها نكتب c، ستبدو مشابهة لما يلي:

         Command   Total   Pct  |  Last Pct
         -------   -----   ---  |  ---- ---
          select    1782   55%  |  100 8%
     show status     723   22%  |  533 45%
show processlist     708   22%  |  532 45%
       change db       2    0%  |    0 0%
  show variables       1    0%  |    0 0%
     Compression       0    0%  |    0 0%

يُظهِر العمود Command نوع الأمر أو الاستعلام الذي يتم تنفيذه، يرمز العمود Total إلى العدد الإجمالي لهذا النوع من الأوامر التي تم تنفيذها منذ بدء تشغيل الخادوم، ويُظهِر العمود Pct نفس ما سبق ولكن بالنسبة المئوية. وعلى الناحية الأخرى من الخط العمودي نجد العمود Last والذي يُخبرنا بعدد هذا النوع من الأوامر التي تم تشغيلها منذ آخر تحديث لـ mytop، تُعطينا هذه المعلومات فكرة عمّا يقوم به خادوم MySQL على المدى القريب والبعيد.

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

الخاتمة

يجب أن يكون لدينا الآن معرفة جيّدة حول كيفيّة استخدام mytop لمراقبة خادوم MySQL لدينا، وهي أيضًا نقطة انطلاق لإيجاد مشاكل استعلامات SQL وتحسينها، وبالتالي زيادة الأداء الإجمالي للخادوم.

ترجمة -وبتصرّف- لـ How To Use Mytop to Monitor MySQL Performance لصاحبته Veena K John.

 بعد أن قمنا مسبقًا بتثبيت وإعداد خادوم قاعدة بيانات MySQL التجريبي الخاصّ بنا، يمكننا الآن البدء باستخدام mysqlslap. يمكن تشغيل mysqlslap عبر طرفية الصدفة العادية، لذا لن يكون هناك حاجة إلى الدخول إلى MySQL لتشغيله من هناك. وعلى الرغم من ذلك، فإننا سنقوم في درسنا هذا بفتح اتصالٍ جديد إلى خادومنا بالإضافة إلى بدء جلسة MySQL جديدة من هناك بواسطة المستخدم sysadmin الذي أنشأناه من قبل، لكي نتمكّن من التحقق من بعض الأمور وتحديث أخرى في MySQL بشكلٍ أسهل. لذا وفي المجمل، فإننا سنمتلك طرفيةً واحدة مفتوحة مع مستخدمٍ بصلاحيات إدارية (sudo)، وطرفية واحدة لـMySQL.

قبل أن ندخل في الأوامر الرئيسية المُستخدمة للاختبار، قد تودّ أن تلقي نظرةً على هذه القائمة لأكثر الخيارات المفيدة لـmysqlslap. يمكن أن يساعدك هذا على تصميم أوامر mysqlslap الخاصّ بك لاحقًا.

حالة استخدام: اختبار الأداء مع بيانات واستعلامات SQL منشئة تلقائيا

سنبدأ عبر استخدام ميّزة mysqlslap في الإنشاء التلقائي لجمل SQL. عندما نقوم باستخدامها، فإنّ mysqlslap سيقوم بإنشاء قاعدة بيانات مؤقّتة منفصلة تدعى "mysqlslap". ستحوي قاعدة البيانات هذه جدولًا بسيطًا يحوي هو الآخر عددًا صحيحًا واحدًا وعمودًا واحدًا من نوع varchar بداخله بيانات تجريبية. يمكن أن تكون هذه طريقةً سهلة وسريعة للتحقق من أداء خادوم قاعدة البيانات الكلّي.

سنبدأ عبر اختبار اتصال عميلٍ واحدٍ فقط يتم تكراره مرّةً واحدة كذلك باستخدام جملة SQL مُنشئة تلقائيًا:

sudo mysqlslap --user=sysadmin --password --host=localhost  --auto-generate-sql --verbose

يجب أن يبدو الخرج كالتالي:

Benchmark
        Average number of seconds to run all queries: 0.009 seconds
        Minimum number of seconds to run all queries: 0.009 seconds
        Maximum number of seconds to run all queries: 0.009 seconds
        Number of clients running queries: 1
        Average number of queries per client: 0

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

الآن، جرّب محاكاة 50 اتصال، وقم باختيار تشغيل عملية الاستعلام المُنشَئة تلقائيًا 10 مرّات:

sudo mysqlslap --user=sysadmin --password --host=localhost  --concurrency=50 --iterations=10 --auto-generate-sql --verbose

يعني هذا الأمر أنّه سيتم محاكاة 50 اتصالًا، يقوم كلّ منها بتنفيذ نفس عملية الاستعلام بنفس الوقت، وسيتم إعادة هذه الاختبار 10 مرّات.

يظهر لنا الخرج فرقًا واضحًا في الوقت مع ازدياد الحِمل:

Benchmark
        Average number of seconds to run all queries: 0.197 seconds
        Minimum number of seconds to run all queries: 0.168 seconds
        Maximum number of seconds to run all queries: 0.399 seconds
        Number of clients running queries: 50
        Average number of queries per client: 0

لاحظ كيف أنّ حقل "Number of clients running queries" أصبح الآن يظهر الرقم 50، وأنّ عدد عمليات الاستعلام بواسطة العميل الواحد هو صفر.

تقوم SQL المُشَئة تلقائيًا بإنشاء جدولٍ بسيط يحوي حقلين اثنين، إلّا أنّه وفي معظم البيئات الإنتاجية فستكون بنية الجدول أكبر بكثير من هذا. يمكننا أن نفرض على mysqlslap أن يقوم بمحاكاة هذا عبر إضافة بعض الحقول الإضافية إلى جدول الاختبار. لفعل ذلك، يمكننا استخدام المُعامِلين الجديدين number-char-cols-- وnumber-int-cols--. تقوم هذه المُعامِلات بتحديد عدد الأعمدة من نوع varchar وint لإضافتها إلى جدول الاختبار.

سنختبر استعلام SQL مُنشَئ تلقائيًا عن جدولٍ بـ5 أعمدة رقمية و20 عمود نصّي في المثال التالي. سنحاكي أيضًا 50 اتصالًا من أجهزة العملاء في الوقت ذاته وسنقوم بتكرار الاختبار 100 مرّة:

sudo mysqlslap --user=sysadmin --password --host=localhost  --concurrency=50 --iterations=100 --number-int-cols=5 --number-char-cols=20 --auto-generate-sql --verbos

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

من طرفية MySQL، طبّق:

show databases;

لاحظ وجود قاعدة mysqlslap:

+--------------------+
| Database           |
+--------------------+
| information_schema |
| employees          |
| mysql              |
| mysqlslap          |
| performance_schema |
+--------------------+
5 rows in set (0.01 sec)

يمكنك أن تتحقق من الجدول في قاعدة البيانات الاختبارية إن أردت; إنّه يدعى t1.

تحقق من نافذة الطرفية الأخرى الآن. عندما ينتهي الاختبار، ستلاحظ أنّ الأداء قد انخفض بدرجةٍ أكبر من السابق بسبب الحِمل الزائد:

Benchmark
        Average number of seconds to run all queries: 0.695 seconds
        Minimum number of seconds to run all queries: 0.627 seconds
        Maximum number of seconds to run all queries: 1.442 seconds
        Number of clients running queries: 50
        Average number of queries per client: 0

ارجع إلى نافذة طرفية MySQL. يمكننا أن نرى أنّ mysqlslap قد حذف قاعدة البيانات المؤقّتة الخاصّة به، طبّق:

show databases;

الخرج:

+--------------------+
| Database           |
+--------------------+
| information_schema |
| employees          |
| mysql              |
| performance_schema |
+--------------------+
4 rows in set (0.00 sec)

حالة استخدام: اختبار الأداء مع استعلامات مخصصة

استعلامات SQL المُنشَئة تلقائيًا جيّدة إذا كنت تريد تقييم موارد الخادوم الفيزيائية. هيَ مفيدة عندما تريد معرفة درجة الحِمل التي يمكن للنظام أن يتحمّلها.

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

الآن، سنفترض أنّك تعرف الاستعلامات المحددة التي تريد اختبارها. في القسم التالي، سنريك طريقةً لمعرفة عمليات الاستعلام التي يتم تنفيذها على خادومك.

يمكنك جعل mysqlslap ينفّذ عملية استعلام معيّنة عبر الخيار query--. لا يمكن لجُمل الـSQL أن تحتوي على فواصل الأسطر (أكثر من سطر) ضمنها، ويجب فصلها بواسطة فاصلة منقوطة (;). يجب أيضًا إغلاق الاستعلامات بعلامتيّ تنصيص.

في الشفرة التالية، سنقوم بتشغيل عملية استعلام بسيطة عن الجدول deptemp. يحوي جدول "deptemp" أكثر من ثلاثمئة ألف سجل بداخله. لاحظ كيف قمنا بتحديد قاعدة البيانات employees مع الخيار create-schema--:

sudo mysqlslap --user=sysadmin --password --host=localhost  --concurrency=50 --iterations=10 --create-schema=employees --query="SELECT * FROM dept_emp;" --verbose

سيستغرق هذا بعض الوقت ليكتمل. بعدها، يجب أن تتلقّى تقريرًا عن اختبار الأداء كالتالي بعد دقيقة أو اثنتين:

Benchmark
        Average number of seconds to run all queries: 18.486 seconds
        Minimum number of seconds to run all queries: 15.590 seconds
        Maximum number of seconds to run all queries: 28.381 seconds
        Number of clients running queries: 50
        Average number of queries per client: 1

(ملاحظة: إذا استغرقت العملية حوالي 10 دقائق أو لم ترجع أيّ خرج، فيجب عليك المحاولة مجددًا مع عددٍ أقل للخيار concurrency-- و/أو iterations--, أو محاولة تطبيقها على خادوم أقوى).

بعدها، سنستخدم أكثر من جملة SQL مع الخيار query--. في المثال التالي، نقوم بإنهاء كلّ جملة بفاصلة منقوطة. سيعرف mysqlslap أننا نستخدم عددًا من أوامر SQL المنفصلة بسبب استخدامنا للخيارdelimiter--:

sudo mysqlslap --user=sysadmin --password --host=localhost  --concurrency=20 --iterations=10 --create-schema=employees --query="SELECT * FROM employees;SELECT * FROM titles;SELECT * FROM dept_emp;SELECT * FROM dept_manager;SELECT * FROM departments;" --delimiter=";" --verbose

يستخدم هذا الاختبار نفس عدد الاتصالات ونفس عدد مرّات التكرار، إلّا أنّ الأداء كان أبطئ بشكلٍ ملحوظ بسبب استخدام أكثر من جملة SELECT واحدة (متوسّط 23.8 ثانية مقابل 18.486 ثانية).

Benchmark
        Average number of seconds to run all queries: 23.800 seconds
        Minimum number of seconds to run all queries: 22.751 seconds
        Maximum number of seconds to run all queries: 26.788 seconds
        Number of clients running queries: 20
        Average number of queries per client: 5

يمكن لجُمل SQL التي يتم استخدامها في بيئة إنتاجية أن تكون معقّدة. إضافة جملة SQL معقّدة إلى سكربت هو أسهل من وضعها للاختبارات، لذا، يمكننا أن نطلب من mysqlslap أن يقوم بقراءة الاستعلامات من ملفّ سكربت.

للقيام بهذا، فلنقم بإنشاء ملفّ سكربت من أوامر SQL. يمكننا استخدام الشفرة البرمجية أدناه لإنشاء الملفّ:

sudo echo "SELECT * FROM employees;SELECT * FROM titles;SELECT * FROM dept_emp;SELECT * FROM dept_manager;SELECT * FROM departments;" > ~/select_query.sql

sudo cp ~/select_query.sql /mysqlslap_tutorial/

يحوي ملفّ select_query.sql جميع عبارات SELECT الآن.

بما أنّ السكربت يحوي أكثر من عملية استعلام واحدة، فيمكننا استخدام مفهومٍ جديد في الاختبار. يمكن لـmysqlslap أن يقوم بتوزيع عمليات الاستعلام. يمكننا القيام بهذا عبر تحديد عدد الاستعلامات التي يجب على كلّ جهازٍ عميل متّصل أن ينفذّها. يسمح mysqlslap بفعل هذا عبر استخدام الخيار number-of-queries--. لذا، إذا كان لدينا 50 اتصال و1000 عمليّة استعلام يجب تنفيذها، فإنّ كلّ جهاز عميل متّصل سينفّذ حوالي 20 عملية استعلام تقريبًا.

أخيرًا، يمكننا أيضًا استخدام الخيار debug-info--، والذي سيظهر لنا حسبة تقريبية للموارد المستعملة.

في الشفرة البرمجية التالية، نطلب من mysqlslap أن يستخدم ملفّ السكربت الذي أنشأناه للتوّ. إننا نقوم أيضًا بتحديد المُعامِل number-of-queries. سيتم تكرار العمليّة مرّتين كما أننا نريد معلومات التنقيح (debug) ضمن الخرج:

sudo mysqlslap --user=sysadmin --password --host=localhost  --concurrency=20 --number-of-queries=1000 --create-schema=employees --query="/mysqlslap_tutorial/select_query.sql" --delimiter=";" --verbose --iterations=2 --debug-info

بعد أن يكتمل هذا الأمر، يجب أن نرى بعض النتائج المثيرة للاهتمام:

Benchmark
        Average number of seconds to run all queries: 217.151 seconds
        Minimum number of seconds to run all queries: 213.368 seconds
        Maximum number of seconds to run all queries: 220.934 seconds
        Number of clients running queries: 20
        Average number of queries per client: 50


User time 58.16, System time 18.31
Maximum resident set size 909008, Integral resident set size 0
Non-physical pagefaults 2353672, Physical pagefaults 0, Swaps 0
Blocks in 0 out 0, Messages in 0 out 0, Signals 0
Voluntary context switches 102785, Involuntary context switches 43

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

يمكننا أن نرى وجود عددٍ كبير من أخطاء الصفحات غير العتادية (البرمجية). تحصل أخطاء الصفحات (page faults) عندما لا يمكن العثور على البيانات في الذاكرة ويتوجّب على النظام أن يبحث عنها على قرص الـSwap. يظهر الخرج أيضًا بعض المعلومات المتعلّقة بالمعالج المركزي. في حالتنا، فإننا نرى عدد كبيرًا من تبديلات السياق كذلك.

حالة استخدام: سيناريو اختبار أداء عملي مع التقاط مباشر للاستعلامات

إلى الآن في أمثلتنا، كنّا نقوم بتطبيق عمليات الاستعلام مع قاعدة بيانات "employees" الخاصّة بنا، وهو ما قد لا يكون شيئًا يريدك مُدراء قواعد البيانات أن تفعله، وهناك سببٌ وجيهٌ لذلك. لا تريد أن تقوم بإضافة حِمل إضافي إلى قاعدة بياناتك الإنتاجية ولا تريد أن تقوم بتفيذ استعلامات اختبارية يمكنها أن تقوم بحذف، تحديث أو إدراج البيانات إلى جداول قاعدة البيانات الإنتاجية الخاصّة بك.

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

والأكثر أهميّة من ذلك، سنريك كيف تقوم بتسجيل الاستعلامات بشكلٍ حيّ أو مباشر (live) من قاعدة بيانات إنتاجية بالإضافة إلى كيفيّة إضافة الاستعلامات إلى سكربتٍ اختباري. في المجمل، ستحصل على الاستعلامات من بيئة عمل إنتاجية، ولكنّك ستقوم بتنفيذ الاختبارات على قاعدة البيانات التجريبية.

الخطوات العامّة هي كالتالي، ويمكنك استخدامها لأيّ اختبار mysqlslap:

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

للبدء، قم بإنشاء نسخة احتياطية من قاعدة البيانات "employees". سنقوم بإنشاء مسارٍ منفصل لهذه النسخة الاحتياطية:

sudo mkdir /mysqlslap_tutorial/mysqlbackup

cd /mysqlslap_tutorial/mysqlbackup

أنشئ النسخة الاحتياطية وانقلها إلى المسار الجديد:

sudo mysqldump --user sysadmin --password --host localhost employees > ~/employees_backup.sql

sudo cp ~/employees_backup.sql /mysqlslap_tutorial/mysqlbackup/

اذهب إلى خادوم MySQL التجريبي الخاصّ بك، وأنشئ قاعدة البيانات "employees_backup":

CREATE DATABASE employees_backup;

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

sudo mysql -u sysadmin -p employees_backup < /mysqlslap_tutorial/mysqlbackup/employees_backup.sql

على خادوم قاعدة بيانات MySQL الإنتاجية الخاصّ بك، فعّل سجل استعلامات MySQL العام وقم بتوفير اسم ملفٍّ خاصٍّ به. يقوم سجل الاستعلامات العام بالتقاط الاتصالات، الاتصالات المنقطعة وسجل الاستعلامات لقاعدة بيانات MySQL:

SET GLOBAL general_log=1, general_log_file='capture_queries.log';

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

هذه هي عملية الاستعلام التي سنقوم بطلبها على خادوم MySQL الإنتاجي الخاصّ بنا. أولًا، قم باستخدام قاعدة البيانات الصحيحة:

USE employees;

والآن عملية الاستعلام:

SELECT e.first_name, e.last_name, d.dept_name, t.title, t.from_date, t.to_date FROM employees e INNER JOIN  dept_emp de ON e.emp_no=de.emp_no INNER JOIN departments d ON de.dept_no=d.dept_no INNER JOIN titles t ON e.emp_no=t.emp_no ORDER BY  e.first_name, e.last_name, d.dept_name, t.from_date;

الخرج المتوقّع:

489903 rows in set (4.33 sec)

سنقوم بتعطيل التسجيل العام عندما تكتمل عملية الاستعلام:

SET GLOBAL general_log=0;

لاحظ أنّه في حال تركت التسجيل العام مفعّلًا، سيستمر إضافة الاستعلامات إلى السجل، والذي من شأنه جعل الاختبارات أصعب. لذا تأكّد أنّك قمت بتعطيل التسجيل مباشرةً بعد الإنتهاء من اختبارك. فلنتحقق من أنّ ملفّ السجل تمَّ إنشاؤه ضمن المسار var/lib/mysql/:

sudo ls -l /var/lib/mysql/capt*

-rw-rw----. 1 mysql mysql 861 Sep 24 15:09 /var/lib/mysql/capture_queries.log

فلننسخ هذا الملفّ إلى مسار MySQL الاختباري الخاصّ بنا. إذا كنتَ تستخدم خادومًا منفصلًا للاختبار، فقم بنسخه إلى ذلك الخادوم:

sudo cp /var/lib/mysql/capture_queries.log /mysqlslap_tutorial/

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

sudo tail /mysqlslap_tutorial/capture_queries.log

الخرج المتوقّع:

         6294 Query show databases
         6294 Query show tables
         6294 Field List    departments 
         6294 Field List    dept_emp 
         6294 Field List    dept_manager 
         6294 Field List    employees 
         6294 Field List    salaries 
         6294 Field List    titles 

140930 15:34:52  6294 Query SELECT e.first_name, e.last_name, d.dept_name, t.title, t.from_date, t.to_date FROM employees e INNER JOIN  dept_emp de ON e.emp_no=de.emp_no INNER JOIN departments d ON de.dept_no=d.dept_no INNER JOIN titles t ON e.emp_no=t.emp_no ORDER BY  e.first_name, e.last_name, d.dept_name, t.from_date
140930 15:35:06  6294 Query SET GLOBAL general_log=0

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

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

لاحظ أنّه في حال قمت بطلب استعلامات مختلفة أثناء عملية التسجيل (logging)، فإنّ هذا الملفّ سيبدو مختلفًا تمامًا. في سيناريو حقيقي، يمكن أن يتمّ ملئ هذا الملفّ بالمئات من المُدخَلات القادمة من مختلف الاتصالات. هدفك هو العثور على الاستعلام أو الاستعلامات التي تسبب هذا الحِمل. يمكنك أن تبدأ عبر إنشاء قائمة بكلّ سطر يتضمّن الكلمة "Query". بعدها، ستمتلك قائمةً دقيقة بالاستعلامات التي تمّ تنفيذها على قاعدة البيانات الخاصّة بك أثناء الاختبار.

قم بنسخ كلّ استعلام تريد اختباره إلى ملفٍّ ينتهي بالامتداد sql.

كمثال:

sudo vi /mysqlslap_tutorial/capture_queries.sql

يجب أن تكون المحتويات هي استعلامات MySQL التي تريد اختبارها، دون استخدام أيّ سطور إضافية (استخدم سطر واحد فقط) ودون فاصلة منقوطة بالنهاية:

SELECT e.first_name, e.last_name, d.dept_name, t.title, t.from_date, t.to_date FROM employees e INNER JOIN  dept_emp de ON e.emp_no=de.emp_no INNER JOIN departments d ON de.dept_no=d.dept_no INNER JOIN titles t ON e.emp_no=t.emp_no ORDER BY  e.first_name, e.last_name, d.dept_name, t.from_date

بعدها، تأكّد أنّ نتائج الاستعلامات لم يتم تخزينها في ذاكرة الخبيئة (cache). ارجع إلى جلسة MySQL الخاصّة بالخادوم الاختباري وطبّق الأمر التالي:

RESET QUERY CACHE;

الآن، صار الوقت المناسب لتشغيل أداة mysqlslap مع ملفّ السكربت. تأكّد أنّك تستخدم اسم ملفّ السكربت الصحيح مع المُعامِل query--. سنستخدم فقط 10 اتصالات افتراضية وسنكرر العمليّة مرّتين فقط. قم بتشغيل الأمر التالي من خادومك الاختباري:

sudo mysqlslap --user=sysadmin --password --host=localhost  --concurrency=10 --iterations=2 --create-schema=employees_backup --query="/mysqlslap_tutorial/capture_queries.sql" --verbose

يجب أن يبدو خرج اختبار الأداء شيئًا كالتالي:

Benchmark
        Average number of seconds to run all queries: 68.692 seconds
        Minimum number of seconds to run all queries: 59.301 seconds
        Maximum number of seconds to run all queries: 78.084 seconds
        Number of clients running queries: 10
        Average number of queries per client: 1

إذًا، كيف يمكننا الآن تحسين هذا الأداء؟

ستحتاج معرفةً جيّدة باستعلامات MySQL لتقييم ما تفعله عمليات الاستعلام.

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

مع القليل من التمرّن، بمجرّد أن تجد الاستعلامات التي تسبب ضغطًا على خادوم البيانات (وهو القسم الذي سيساعدك mysqlslap فيه!)، ستكون قادرًا على تقييم عمليات الاستعلام المتوفّرة بناءً على معرفتك بـMySQL وقاعدة بياناتك.

بعدها، يمكنك محاولة تحسين قاعدة بياناتك أو الاستعلامات التي يتم تنفيذها عليها.

في حالتنا، فلنضف الفهارس التي ذكرناه بالأعلى، سنقوم بإنشاء 3 فهارس فارغة لـempno. سيتم إنشاء فهرس واحد في حقل empno في جدول employees، وسيتم إنشاء فهرس آخر في حقل empno بالجدول deptemp، وسيتم إنشاء الأخير في حقل emp_no في جدول titles.

فلنذهب إلى جلسة MySQL الاختبارية الخاصّة بنا ونطبّق الأوامر التالية:

USE employees_backup;

CREATE INDEX employees_empno ON employees(emp_no);

CREATE INDEX dept_emp_empno ON dept_emp(emp_no);

CREATE INDEX titles_empno ON titles(emp_no);

بالعودة إلى نافذة الطرفية الرئيسية على خادومنا الاختباري، إذا قمنا بتشغيل mysqlslap بنفس المُعاملات، فسنرى اختلافًا في نتائج اختبار الأداء:

sudo mysqlslap --user=sysadmin --password --host=localhost  --concurrency=10 --iterations=2 --create-schema=employees_backup --query="/mysqlslap_tutorial/capture_queries.sql" --verbose

Benchmark
        Average number of seconds to run all queries: 55.869 seconds
        Minimum number of seconds to run all queries: 55.706 seconds
        Maximum number of seconds to run all queries: 56.033 seconds
        Number of clients running queries: 10
        Average number of queries per client: 1

يمكن أن نرى وجود تحسّن فوري في متوسّط، أدنى وأقصى وقت لتنفيذ عمليات الاستعلام. عوضًا عن متوسّط 68 ثانية، يتم تنفيذ الاستعلامات الآن بغضون 55 ثانية. هذا تحسّن بحوالي 13 ثانية لنفس الاستعلامات التي تمّ تنفيذها.

بما أنّ هذا التغيير في قاعدة البيانات أعادة نتيجةً جيّدة في البيئة الاختبارية، فإنّه يمكنك الآن أخذه بعين الاعتبار لتنفيذه على خادوم قاعدة البيانات الإنتاجية الخاصّة بك، ولكن لا تنسى أنّ تغييرات قاعدة البيانات لها دومًا إيجابيات وسلبيات عليك المفاضلة بينها.

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

استكشاف الأخطاء وإصلاحها: mysqlslap لا يظهر الخرج

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

mysqlslap: Error when storing result: 2013 Lost connection to MySQL server during query

ربّما قد تودّ إعادة الاختبار مجددًا مع عددٍ أقل للمُعامِل concurrency-- أو iterations--، أو يمكنك محاولة ترقية بيئة خادومك الاختباري لإصلاح المشكلة.

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

الخاتمة

mysqlslap هو أداة بسيطة وخفيفة يمكنها أن تندمج بسهولة مع محرّك قاعدة بيانات MySQL. وهي متوفّر لجميع إصدارات MySQL بدءًا من الإصدار 5.1.4.

في هذا الدرس، رأينا كيفيّة استخدام mysqlslap مع خياراته المتعددة وجرّبنا الأمر مع قاعدة بيانات اختبارية كعيّنة. يمكنك تحميل قواعد بيانات عينية أخرى للاختبار من موقع MySQL والتمرّن عليها أيضًا. كما ذكرنا من قبل: رجاءًا لا تقم بتنفيذ الاختبارات على خادوم قاعدة بيانات إنتاجية.

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

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

ترجمة -وبتصرف- للمقال: How To Measure MySQL Query Performance with mysqlslap لصاحبه: Sadequl Hussain.

تأتي MySQL مع أداة تشخيص (diagnostic) تدعى mysqlslap، تمّ توفير هذه الأداة منذ الإصدار 5.1.4 من MySQL، وهي عبارة عن أداة قياس للأداء (benchmarking) يمكنها أن تساعد مدراء قواعد البيانات والمطورّين على أن يختبروا أداء خواديم قواعد البيانات الخاصّة بهم بسهولة.

يمكن لـmysqlslap أن تحاكي عددًا كبيرًا من أجهزة العملاء التي تتصل بخادوم قاعدة البيانات بنفس الوقت. مُعامِلات اختبار الحمل (load testing parameters) قابلة للضبط بشكلٍ كلّي ويمكن استخدام نتائج الاختبارات المختلفة بهدف تحسين تصميم قواعد البيانات أو استهلاك موارد العتاد.

في هذا الدرس، سنشرح كيفيّة تثبيت وإعداد mysqlslap بهدف إجراء اختبار الحِمْل (load test) على قاعدة بيانات MySQL باستخدام بعض عمليات الاستعلام الأساسية ولنرى كيف يمكن لاختبارات الأداء أن تساعدنا على تحسين هذه الاستعلامات لاحقًا. بعد القليل من التوضيحات المبدئية، سنقوم بعمل سيناريو تجريبي مشابه للتجربة الحقيقية حيث سنقوم بإنشاء نسخة من قاعدة بيانات موجودة حاليًا لنقوم بالاختبارات عليها، وسنجمع الاستعلامات من ملفّات السجل ونبدأ بالاختبار بواسطة سكربت.

ما هو حجم الخادوم الذي يجب أن أستخدمه؟

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

إذا كنتَ تريد المضيّ قدمًا بهذا الدرس بهدف التعلّم وتنفيذ كلّ أمر موجودٍ فيه، فإننا ننصحك بخادوم يمتلك 2 جيجابت من الذاكرة العشوائية (RAM) على الأقل. وبما أنّ الأوامر الموجودة في هذا الدرس تهدف إلى إرهاق الخادوم بالطلبات الكثيرة بهدف قياس أدائه، فإنّك قد تلاحظ أنّ الخواديم الأصغر حجمًا قد ينقطع الاتصال بها بسبب ذلك الحِمل.

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

الخطوة الأولى: تثبيت خادوم MySQL على نظام اختباري

سنبدأ عبر تثبيت نسخة جديدة من خادوم MySQL المطوّر بواسطة المجتمع أو MySQL Community Server على نظام تشغيل اختباري. يجب ألّا تقوم بتشغيل أيّ أوامر أو استعلامات تجدها في هذا الدرس على خادوم قاعدة بيانات ضمن بيئة إنتاجية بتاتًا.

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

• CentOS 7.
• أوامر تمّ تنفيذها بواسطة مستخدم جذر.
• 2 جيجابت من الذاكرة العشوائية (RAM); لا تنسى أنّ الاختبارات التي سيتم ذكرها هنا تمّ إنتاجها بهدف التعليم ولا تعني بأيّ شكل من الأشكال أنّها نتيجة اختبارات رسمية صادرة عنّا.

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

sudo mkdir /mysqlslap_tutorial
cd /mysqlslap_tutorial

بعدها، سنقوم بتحميل مستودع yum الخاصّ بنسخة المجتمع من خادوم MySQL. المستودع الذي سنحمّله هو مخصص بشكل اساسي لـRed Hat Enterprise Linux 7 إلّا أنّه يعمل بالطبع مع CentOS 7 كذلك:

sudo wget http://dev.mysql.com/get/mysql-community-release-el7-5.noarch.rpm

الآن يمكننا تنفيذ rpm -Uvh لتثبيت هذا المستودع:

sudo rpm -Uvh mysql-community-release-el7-5.noarch.rpm

تحقق أنّه قد تمّ تثبيت المستودعات عبر تفحّص محتويات المجلّد etc/yum.repos.d/:

sudo ls -l /etc/yum.repos.d

يجب أن يبدو الخرج كالتالي:

-rw-r--r--. 1 root root 1612 Jul  4 21:00 CentOS-Base.repo
-rw-r--r--. 1 root root  640 Jul  4 21:00 CentOS-Debuginfo.repo
-rw-r--r--. 1 root root 1331 Jul  4 21:00 CentOS-Sources.repo
-rw-r--r--. 1 root root  156 Jul  4 21:00 CentOS-Vault.repo
-rw-r--r--. 1 root root 1209 Jan 29  2014 mysql-community.repo
-rw-r--r--. 1 root root 1060 Jan 29  2014 mysql-community-source.repo

وفي حالتنا، فإنّ MySQL 5.6 Community Server هو ما نريده:

mysql-connectors-community/x86_64       MySQL Connectors Community           10
mysql-tools-community/x86_64            MySQL Tools Community                 6
mysql56-community/x86_64                MySQL 5.6 Community Server           64

والآن قم بتثبيت خادوم MySQL:

sudo yum install mysql-community-server

بمجرّد اكتمال العمليّة، قم بالتحقق من أنّه تمّ تثبيت المكوّنات فعلًا:

sudo yum list installed | grep mysql

يجب أن تبدو القائمة كالتالي:

mysql-community-client.x86_64      5.6.20-4.el7      @mysql56-community
mysql-community-common.x86_64      5.6.20-4.el7      @mysql56-community
mysql-community-libs.x86_64        5.6.20-4.el7      @mysql56-community
mysql-community-release.noarch     el7-5             installed
mysql-community-server.x86_64      5.6.20-4.el7      @mysql56-community

بعدها، يجب أن نتأكّد أن عفريت MySQL أو MySQL Daemon يعمل بالفعل وأنّه سيبدأ تلقائيًا عند تشغيل الخادوم. يمكنك القيام بذلك عبر الأمر التالي:

sudo systemctl status mysqld.service

يجب أن ترى خرجًا كهذا:

mysqld.service - MySQL Community Server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; disabled)
   Active: inactive (dead)

ابدأ الخدمة عبر:

sudo systemctl start mysqld.service

ولجعلها تبدأ عند بدء تشغيل الخادوم:

sudo systemctl enable mysqld.service

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

sudo mysql_secure_installation

سيظهر لك هذا مجموعة من الأسئلة. سنريك بالأسفل جميع الأسئلة مع أجوبتها التي يجب أن تختارها. في البداية لن يكون هناك كلمة مرور للمستخدم root الخاصّ بـMySQL، لذا، قم فقط بالضغط على زرّ Enter.

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

...
Enter current password for root (enter for none):
OK, successfully used password, moving on...
...
Set root password? [Y/n] y
New password:
Re-enter new password:
Password updated successfully!
Reloading privilege tables..
 ... Success!
...
Remove anonymous users? [Y/n] y
 ... Success!
...
Disallow root login remotely? [Y/n] y
 ... Success!
Remove test database and access to it? [Y/n] y
 - Dropping test database...
 ... Success!
...
Reload privilege tables now? [Y/n] y
 ... Success!
Cleaning up...

يمكننا الآن أن نتّصل بقاعدة البيانات لنتأكّد من أنّ كلّ شيءٍ يعمل بشكلٍ صحيح:

sudo mysql -h localhost -u root -p

قم بإدخال كلمة مرور MySQL التي كتبتها للتوّ الآن. يجب أن ترى الخرج التالي:

Enter password:
Welcome to the MySQL monitor....

mysql>

في طرفيّة <mysql قم بإدخال الأمر الذي سيظهر لك جميع قواعد البيانات:

show databases;

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

+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
3 rows in set (0.00 sec)

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

create user sysadmin identified by 'mypassword';

الخرج:

Query OK, 0 rows affected (0.00 sec)

قم بإعطاء جميع الصلاحيات للمستخدم:

grant all on *.* to sysadmin;

الخرج:

Query OK, 0 rows affected (0.01 sec)

والآن فلنعد إلى طرفيّة نظام التشغيل العادية:

quit;

الخرج:

Bye

الخطوة الثانية: تثبيت قاعدة بيانات تجريبية

الآن، سنحتاج إلى تثبيت قاعدة بيانات تجريبية بهدف الاختبار. اسم قاعدة البيانات هذه هو "employees" وهي وهي متوفّرة بشكلٍ مجاني من على موقع MySQL، كما أنّه يمكن تحميل قاعدة البيانات من Launchpad.

اخترنا قاعدة "employees" لأنّها تحتوي على مجموعة كبيرة من البيانات. رغم ذلك، فإنّ بنية قاعدة البيانات بسيطة بدرجة كافية، إنّها تحتوي على 6 جداول فقط، ولكن وفي داخلها، فهي تحتوي على سجلات 3 مليون موظّف (يمتلك جدول الرواتب لوحده حوالي 3 ملايين صفّ)، وسيساعدنا هذا على محاكاة حِمل بيئة عمل إنتاجية بشكل أفضل.

أوّلًا، دعنا نتأكّد أننا في المسار mysqlslap_tutorial/ الخاصّ بنا:

cd /mysqlslap_tutorial

قم بتحميل آخر إصدار من قاعدة البيانات التجريبية:

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

قم بتثبيت bzip2 لنتمكّن من استخراج الملفّ:

sudo yum install bzip2

والآن قم بفكّ الضغط عن أرشيف قاعدة البيانات، قد يأخذ هذا وقتًا:

sudo bzip2 -dfv employees_db-full-1.0.6.tar.bz2
sudo tar -xf employees_db-full-1.0.6.tar

سيتم فكّ ضغط المحتويات إلى مسار منفصل جديد يدعى "employees_db"، نحتاج أن نقوم بالذهاب إلى هذا المجلّد لنقوم بتشغيل الأمر الذي يقوم بتثبيت قاعدة البيانات هذه. تتضمّن المحتويات ملفّ README، سجل للتغييرات، حِزَم بيانات وملفّات استعلامات SQL مختلفة تشكّل جميعها بنية قاعدة البيانات:

cd employees_db
ls -l

وهذا هو ما يجب أن تراه:

-rw-r--r--. 1 501 games       752 Mar 30  2009 Changelog
-rw-r--r--. 1 501 games      6460 Oct  9  2008 employees_partitioned2.sql
-rw-r--r--. 1 501 games      7624 Feb  6  2009 employees_partitioned3.sql
-rw-r--r--. 1 501 games      5660 Feb  6  2009 employees_partitioned.sql
-rw-r--r--. 1 501 games      3861 Nov 28  2008 employees.sql
-rw-r--r--. 1 501 games       241 Jul 30  2008 load_departments.dump
-rw-r--r--. 1 501 games  13828291 Mar 30  2009 load_dept_emp.dump
-rw-r--r--. 1 501 games      1043 Jul 30  2008 load_dept_manager.dump
-rw-r--r--. 1 501 games  17422825 Jul 30  2008 load_employees.dump
-rw-r--r--. 1 501 games 115848997 Jul 30  2008 load_salaries.dump
-rw-r--r--. 1 501 games  21265449 Jul 30  2008 load_titles.dump
-rw-r--r--. 1 501 games      3889 Mar 30  2009 objects.sql
-rw-r--r--. 1 501 games      2211 Jul 30  2008 README
-rw-r--r--. 1 501 games      4455 Mar 30  2009 test_employees_md5.sql
-rw-r--r--. 1 501 games      4450 Mar 30  2009 test_employees_sha.sql

قم بتشغيل هذا الأمر للاتصال بـMySQL وتشغيل سكربت employees.sql، والذي سيقوم بإنشاء قاعدة البيانات وتحميل البيانات إليها:

sudo mysql -h localhost -u sysadmin -p -t < employees.sql

الآن، يجب عليك إدخال كلمة المرور التي اخترتها للمستخدم "sysadmin" من قبل.

يجب أن يكون خرج العملية شيئًا كهذا، قد تأخذ وقتًا (حوال الدقيقة) ليكتمل:

+-----------------------------+
| INFO                        |
+-----------------------------+
| CREATING DATABASE STRUCTURE |
+-----------------------------+
+------------------------+
| INFO                   |
+------------------------+
| storage engine: InnoDB |
+------------------------+
+---------------------+
| INFO                |
+---------------------+
| LOADING departments |
+---------------------+
+-------------------+
| INFO              |
+-------------------+
| LOADING employees |
+-------------------+
+------------------+
| INFO             |
+------------------+
| LOADING dept_emp |
+------------------+
+----------------------+
| INFO                 |
+----------------------+
| LOADING dept_manager |
+----------------------+
+----------------+
| INFO           |
+----------------+
| LOADING titles |
+----------------+
+------------------+
| INFO             |
+------------------+
| LOADING salaries |
+------------------+

الآن، يمكنك الولوج إلى MySQL وتشغيل بعض الاستعلامات البدائية للتحقق من أنّ البيانات قد تمّ استيرادها بنجاح:

sudo mysql -h localhost -u sysadmin -p

قم بإدخال كلمة المرور الخاصّة بالمستخدم sysadmin.

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

show databases;

الخرج:

+--------------------+
| Database           |
+--------------------+
| information_schema |
| employees          |
| mysql              |
| performance_schema |
+--------------------+
4 rows in set (0.01 sec)

استخدم قاعدة البيانات "employees" عبر الأمر:

use employees;

وللتحقق من الجداول الموجودة:

show tables;

الخرج:

+---------------------+
| Tables_in_employees |
+---------------------+
| departments         |
| dept_emp            |
| dept_manager        |
| employees           |
| salaries            |
| titles              |
+---------------------+
6 rows in set (0.01 sec)

إذا كنت تريد ذلك، فيمكنك التحقق من تفاصيل كلّ جدول من هذه الجداول. سنتحقق الآن من تفاصيل جدول titles فقط:

describe titles;

الخرج:

+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| emp_no    | int(11)     | NO   | PRI | NULL    |       |
| title     | varchar(50) | NO   | PRI | NULL    |       |
| from_date | date        | NO   | PRI | NULL    |       |
| to_date   | date        | YES  |     | NULL    |       |
+-----------+-------------+------+-----+---------+-------+
4 rows in set (0.01 sec)

وللتحقق من رقم المُدخَلات:

mysql> select count(*) from titles;

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

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

quit;

انتهى درسنا حول تثبيت mysqlslap وإعداده، اقرأ الدرس الآخر لمتابعة طريقة استخدامه للقيام بالاختبارات المطلوبة.

ترجمة -وبتصرف- للمقال: How To Measure MySQL Query Performance with mysqlslap لصاحبه: Sadequl Hussain.

هناك العديد من لغات قواعد البيانات SQL التي تعمل على أنظمة اللينكس واليونكس، ومن أشهر لغات قواعد البيانات العلائقية التي تعمل في بيئات الخوادم هما MySQL وMariaDB.

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

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

الإعداد الأولي

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

$ sudo apt-get install mysql-server

Configuring mysql-server-5.5
While not mandatory, it is highly recommended that you set a password for the MySQL administrative "root" user.
If this field is left blank, the password will not be changed.

New password for the MySQL "root" user:

يمكنك دائما تعيين كلمة سر root في وقت لاحق، ولكن ليس هناك سبب لتخطي هذه الخطوة، لذلك يجب تأمين حساب المسؤول الخاص بك من البداية.

بمجرد اكتمال التثبيت، يجب علينا تشغيل عدد قليل من النصوص المدرجة.

أولاً، سوف نستخدم "mysql_install_db" وهو سكريبت نصي لإنشاء تصميم قواعد البيانات الخاصة بنا.

$ sudo mysql_install_db

بعد ذلك، قم بتشغيل السكريبت الذي يسمى "mysql_secure_installation"، وسيرشدنا لبعض الإجراءات التي من شأنها إزالة بعض الافتراضات التي تشكل خطرا على استخدامها في بيئة الإنتاج.

$ sudo mysql_secure_installation

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

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

يجب أن تكون الإجابة "Y" (نعم) لجميع الأسئلة المتبقية.

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

اعتبارات أمنية

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

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

زيادة الأمن من خلال ملف My.cnf

ملف الإعدادت الرئيسية في MySQL هو ملف يسمى "my.cnf" الموجود في  "/etc/mysql/" هذا الامتداد على أوبونتو وامتداد "/etc/" على بعض الخواديم الأخرى.

سوف نقوم بتغيير بعض الإعدادات في هذا الملف لتأمين MySQL الخاصة بنا.

فتح الملف مع صلاحيات الجذر، تغيير مسار الامتداد حسب الحاجة إذا كنت تتبع هذا الشرح التعليمي على نظام مختلف:

$ sudo nano /etc/mysql/my.cnf

الإعداد الأولي التي يجب علينا أن نتحقق منه "وضع عنوان IP" ضمن قسم "[mysqld]". ويجب تعيين هذا الإعداد على جهاز الشبكة المحلي loopback ، وهو "127.0.0.1".

 $ bind-address = 127.0.0.1

هذا يجعل من أن MySQL لا تقبل الاتصالات من أي مكان باستثناء الجهاز المحلي.

إذا كنت بحاجة للوصول إلى قاعدة البيانات من جهاز آخر، خذ بالاعتبار الاتصال عن طريق SSH للقيام بالاستعلام وادارة قاعدة البيانات الخاصة بك محليا وإرسال النتائج من خلال قناة SSH.

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

في نفس المقطع من الملف، سوف نقوم بإضافة التوجيه لتعطيل هذه القدرة على تحميل الملفات المحلية:

local-infile=0

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

تسجيل معلومات أكثر من اللازم يضعف الأداء، لذلك قم بوزن أي شيء تحتاجه بعناية.

يمكنك وضع تسجيل الأحداث المتغيرة داخل القسم نفسه "[mysqld]" التي قمنا بالإضافة فيها:

log=/var/log/mysql-logfile

تأكد من أن سجل MySQL يعمل، سجل الأخطاء، وسجل MySQL ليس سهل القراءة:

$ sudo ls -l /var/log/mysql*

-rw-r----- 1 mysql adm 0 Jul 23 18:06 /var/log/mysql.err
-rw-r----- 1 mysql adm 0 Jul 23 18:06 /var/log/mysql.log
/var/log/mysql:
total 28
-rw-rw---- 1 mysql adm 20694 Jul 23 19:17 error.log

تأمين MySQL من الداخل

هناك عدد من الخطوات التي يمكنك اتخاذها أثناء استخدام MySQL لتحسين الوضع الأمني.

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

$ mysql -u root -p

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

تأمين كلمات السر والمستخدمين المرتبطين

أولاً، تأكد من وجود مستخدمين بدون كلمة مرور أو المضيف المرتبط في MySQL:

SELECT User,Host,Password FROM mysql.user;

+------------------+-----------+-------------------------------------------+
| user             | host      | password                                  |
+------------------+-----------+-------------------------------------------+
| root             | localhost | *DE06E242B88EFB1FE4B5083587C260BACB2A6158 |
| demo-user        | %         |                                           |
| root             | 127.0.0.1 | *DE06E242B88EFB1FE4B5083587C260BACB2A6158 |
| root             | ::1       | *DE06E242B88EFB1FE4B5083587C260BACB2A6158 |
| debian-sys-maint | localhost | *ECE81E38F064E50419F3074004A8352B6A683390 |
+------------------+-----------+-------------------------------------------+
5 rows in set (0.00 sec)

كما ترون في مثالنا هذا "المستخدم التجريبي " ليس لديه كلمة مرور، وهو ساري المفعول بغض النظر عن ما هو عليه في المضيف، ويعتبر هذا آمن جدا.

يمكننا وضع كلمة سر للمستخدم مع هذا الأمر Change "كلمة المرور الجديدة" أدخل كلمة المرور التي ترغب بها.

UPDATE mysql.user SET Password=PASSWORD('newPassWord') WHERE User=""demo-user";

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

SELECT User,Host,Password FROM mysql.user;

+------------------+-----------+-------------------------------------------+
| user             | host      | password                                  |
+------------------+-----------+-------------------------------------------+
| root             | localhost | *DE06E242B88EFB1FE4B5083587C260BACB2A6158 |
| demo-user        | %         | *D8DECEC305209EEFEC43008E1D420E1AA06B19E0 |
| root             | 127.0.0.1 | *DE06E242B88EFB1FE4B5083587C260BACB2A6158 |
| root             | ::1       | *DE06E242B88EFB1FE4B5083587C260BACB2A6158 |
| debian-sys-maint | localhost | *ECE81E38F064E50419F3074004A8352B6A683390 |
+------------------+-----------+-------------------------------------------+
5 rows in set (0.00 sec)

إذا ما نظرت هذا الحقل"host"، سترى أن لا يزال لدينا "٪"، هي عبارة عن بطاقة بديلة وهذا يعني أي مضيف. وليس هذا ما نريده، دعونا نغيرها إلى " localhost".

UPDATE mysql.user SET Host='localhost' WHERE User="demo-user";

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

SELECT User,Host,Password FROM mysql.user;

إذا كان لدينا جدول يحتوي على مستخدمين فارغين (لا يجب في هذه المرحلة أن يكونوا "mysql_secure_installation", سنغطي هذه الناحية بأي حال من الأحوال لاحقا)، وينبغي علينا إزالتهم.

للقيام بذلك، يمكننا استخدام الأمور التالي لحذف المستخدمين الفارغين من جدول الوصول:

DELETE FROM mysql.user WHERE User="";

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

FLUSH PRIVILEGES;

إنشاء مستخدمين محددين لتطبيقات معينة

طريقة تشغيل العمليات داخل لينكس تكون معزولة لكل مستخدم على حدى، وتستخدم قاعدة بيانات MySQL نفس طريقة العزل.

كل تطبيق يستخدم MySQL يجب أن يمتلك مستخدم خاص به ولديه صلاحيات محدودة ويستطيع الوصول إلى قواعد البيانات التي يحتاج لتشغيلها فقط.

عندما نقوم بإعداد تطبيق جديد لاستخدام MySQL، يجب أن ننشئ قواعد البيانات التي يحتاجها هذا التطبيق:

create database testDB;
Query OK, 1 row affected (0.00 sec)

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

لإنشاء مستخدم جديد، استخدم الأمر التالي:

CREATE USER 'demo-user'@'localhost' IDENTIFIED BY 'password';

يمكننا منح المستخدم الجديد صلاحيات على الجدول الجديد بالأمر التالي. انظر الشرح التعليمي حول كيفية إنشاء مستخدم ومنح صلاحيات جديدة في MySQL لمعرفة المزيد عن الصلاحيات المحددة:

GRANT SELECT,UPDATE,DELETE ON testDB.* TO 'demo-user'@'localhost';

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

REVOKE UPDATE ON testDB.* FROM 'demo-user'@'localhost';

إذا كنا بحاجة إلى كافة الصلاحيات على قاعدة بيانات معينة، يمكننا تحديد ذلك بما يلي:

GRANT ALL ON testDB.* TO 'demo-user'@'localhost';

لإظهار الصلاحيات الحالية للمستخدم، علينا أولا أن ننفذ الصلاحيات حددناه باستخدام أمر "flush privileges"، ثم بعد ذلك يمكننا الاستعلام عن الصلاحيات التي بحوزة المستخدم.

FLUSH PRIVILEGES;

show grants for 'demo-user'@'localhost';

Grants for demo-user@localhost
GRANT USAGE ON *.* TO 'demo-user'@'localhost' IDENTIFIED BY PASSWORD '*2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19' 
GRANT SELECT, UPDATE, DELETE ON `testDB`.* TO 'demo-user'@'localhost'
2 rows in set (0.00 sec)

دائما امسح الصلاحيات عندما تنتهي من إجراء التغييرات.

تغيير المستخدم الجذر

خطوة إضافية واحدة وهي، ربما تريد تغيير اسم الجذر(root login name )، فإذا كان الهاكر يحاول أن يقوم بتسجيل الدخول باسم الروت في MySQL ، فسوف يحتاج إلى تنفيذ خطوة إضافية هي العثور على اسم المستخدم.

تستطيع تغيير اسم المستخدم روت باستخدام الأمر التالي:

rename user 'root'@'localhost' to 'newAdminUser'@'localhost';

يمكننا أن نرى التغيير باستخدام نفس الاستعلام الذي استخدمناه لقاعدة بيانات المستخدم:

select user,host,password from mysql.user;

مرة أخرى، يجب علينا مسح الصلاحيات التغيرات التي حدثت:

FLUSH PRIVILEGES;

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

mysql -u newAdminUser -p

بأي حال من الأحوال هذه قائمة شاملة من الاجراءات الأمنية لقواعد البيانات MySQL, MariaDB، وقد أصبح لديك مقدمة جيدة لأنواع القرارات التي ستتخذها عندما تريد تأمين قواعد البيانات الخاصة بك.

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

ترجمة -وبتصرّف- للمقال: How To Secure MySQL and MariaDB Databases in a Linux VPS.

مقدِّمة

يحتاج الكثيرُ من المستخدمين إلى نظام إدارة قواعد بيانات Database Managment System, DBMS مثل MySQL، إلّا أنّهم قد لا يكونون مرتاحين للتّفاعل مع النّظام من خلال سطر أوامر MySQL فقط.
أُنشئ phpMyAdmin بحيث يتفاعل هؤلاء المستخدمون مع MySQL عن طريق واجهة ويب. سنعرض في هذا الدّرس لكيفيّة تثبيت وتأمين phpMyAdmin بحيث يُمكن استخدامُه بأمان من أجل إدارة قواعد بيانات MySQL على أوبنتو 14.04.

المُتطلَّبات

نفترض، قبل البدء مع خطوات هذا الدّرس، أنّك تتوفّر على المتطلَّبات التّاليّة:

• حساب بصلاحيّات sudo، غير حساب المستخدِم الجذر Root user كما هوّ موضَّح في الخطوات 1-4 من درس الإعداد الابتدائي لخادوم أوبنتو.
• حزم MySQL، Apache، Linux وPHP (المعروفة بLAMP) مُثَبَّتة ومُعدَّة. يُمكن مراجعة درس كيف تُثبِّت حِزم MySQL، Apache، Linux) LAMP وPHP) على Ubuntu 14.04 لهذا الغرض.

بإكمال الخطوات أعلاه تكون مستعدا لمتابعة هذا الدّرس.

الخطوة الأولى: ثبِّت phpMyAdmin

نبدأ بتنزيل وتثبيت phpMyAdmin من مستودعات أوبنتو عن طريق مدير الحزم apt:

sudo apt-get update
sudo apt-get install phpmyadmin

ستُطرَح عليك بعضُ الأسئلة لإعداد التّثبيت بما يُناسِب:

• عند اختيار خادوم الويب اختَر apache2. ملحوظة: إن لم تضغط زر المسافة SPACE على لوحة المفاتيح لاختيّار Apache فلن ينقُل برنامج التّثبيت الملفّات الضّرورية أثناء التّثبيت. اختر زر SPACE على لوحة المفاتيح ثمّ زر TAB ثمّ زرّ ENTER لاختيّار Apache.
• اختَر نعم (YES) عند السّؤال إذا ما كنت تُريد استخدام dbconfig-common لضبط قاعدة البيانات.
• أدخِل كلمة سرّ مدير قاعدة البيانات عندما يُطلَب منك ذلك.
• سيُطلب منك أيضًا اختيّار وتأكيد كلمة سرّ تطبيق phpMyAdmin نفسِه.

تُضيف عمليّة التّثبيت ملفّ إعداد phpMyAdmin إلى المُجلَّد /etc/apache2/conf-enabled/ حيثُ يقرؤه خادوم ويب Apache أوتوماتيكيًّا.

كلّ ما علينا فعله يدويًّا هو تفعيل امتداد Extension يحمل الاسم php5-mcrypt (واجهة للتّعامل مع مكتبة mcrypt الّتي تضم خوارزميّات للتّعميّة Encryption) عن طريق تنفيذ الأمر:

sudo php5enmod mcrypt

ستحتاج لإعادة تشغيل Apache لكي يتعرَّف على الامتداد الجديد:

sudo service apache2 restart

يُمكنك بعدها الولوج إلى واجهة الويب عن طريق إدخال عنوان IP العمومي لخادومك أو اسم النّطاق في شريط العناوين بمتصفّح الويب متبوعًا ب phpmyadmin/ كما يلي:

http://domain_name_or_IP/phpmyadmin

ملحوظة: إن كنت تجرِّب التّثبيت على جهازك المحلّي فيُمكنك الوصول إلى واجهة phpmyadmin عن طريق إدخال العنوان التّالي في شريط العناوين بمتصفّح الويب
http://127.0.0.1/phpmyadmin

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

الخطوة الثّانيّة: تأمين phpMyAdmin

أكملنا الآن تثبيت phpMyAdmin وسجلنا الدّخول إلى واجهة المستخدِم، لكن بقي أمر آخر. يستهدِف الكثيرُ من المخترقين phpMyAdmin نظرًا لشهرته، في محاولةٍ منهم لنيل طريقة للاتّصال بقاعدة البيانات. نحتاج لتأمين التّطبيق للمساعدة في الحماية من الاستخدام غير المُصرَّح به.
إحدى أسهل الطّرق لتأمين phpMyAdmin هيّ وضعُ بوّابة gateway أمام كامل التّطبيق. نستخدم الاستيثاق Authentication والتّصريح Authorization عن طريق ملفّات htaccess. المُضمَّنة في Apache.

1- إعداد Apache للسّماح بالتّجاوز Override في ملفّات htaccess.

يجب علينا أوّلًا تفعيل استخدام التّجاوز في ملفّات htaccess. وذلك بالتّعديل على ملفّ إعداد خاصّ بـ phpMyAdmin في خادوم ويب Apache. يعني تفعيل التّجاوز أنه يُمكن تعريف ملفّ htaccess. لمجلَّد مُحدَّد بحيثُ تُنفَّذ تعليمات ملفّ htaccess. الموجود في المجلَّد بدلًا من ملفّ htaccess. العامّ لخادوم الويب.
نُعدِّل على ملف إعداد phpMyAdmin الموجود في مجلّد Apache للإعدادات:

sudo nano /etc/apache2/conf-available/phpmyadmin.conf

نُضيف تعليمة AllowOverride All داخل مقطع

2- أنشئ ملفّ htaccess.

نبدأ الآن بعد أن فعَّلنا استخدامَ ملفّ htaccess. خاصّ بتطبيقنا، نبدأ بإنشاء هذا الملفّ من أجل إضافة إجراءات أمان إلى phpMyAdmin.
يجب أن يُنشَأ ملفّ htaccess. داخل مجلَّد التّطبيق. الأمر التّالي يُنشئ ثمّ يفتح الملفّ (يتطلّب صلاحيّات المستخدم الجذر):

sudo nano /usr/share/phpmyadmin/.htaccess

نُضيف التّعليمات التّالية داخل هذا الملفّ:

AuthType Basic
AuthName "Restricted Files
AuthUserFile /etc/phpmyadmin/.htpasswd
Require valid-user

فلنلقِ نظرة على دلالة كل واحدة من هذه التّعليمات:

• AuthType Basic: تُحدِّد AuthType نوع الاستيثاق الّذي سنطلُب وضعه. Basic تعني أنّنا سنستخدم الاستيثاق بكلمة سرّ عن طريق ملفّ يُسمّى ملفّ كلمة السّر.
• AuthName: هذه التّعليمة تُحدِّد الرّسالة الّتي تظهر في مربّع طلب كلمة السّر عند محاولة الوصول إلى التّطبيق. يجب أن تكون هذه الرّسالة عامّة بحيثُ لا تُعطي أيّ معلومات عن ما تحميه كلمة السّر.
• AuthUserFile: تُحدّد مكان حفظ ملفّ كلمة السّر المُستخدَمة للاستيثاق. يجب أن يُحفّظ الملفّ خارج المجلّدات الّتي يُقدّم منها خادوم الويب ملفّاتٍ للزوّار. سنُنشئ هذا الملفّ بعد قليل.
• Require valid-user: تُحدّد نوعيةَ المستخدمين المسموح لهم بالوصول إلى المجلَّد المحمي. هذه التّعلمية أساسيّة من أجل منع وصول المستخدمين غير المُصرَّح لهم بالدّخول إلى التّطبيق.

احفَظ الملفّ (Ctrl + O) بعد التّعديل ثم أغلقه (Ctrl+ X).

3- أنشئ ملفّ htpasswd. للاستيثاق

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

sudo apt-get install apache2-utils

سنحصُل بعد اكتمال التّثبيت على أداة كلمات السّر في Apache.
الأمر التّالي يُنشئ ملفّ كلمة السّر في المسار الّذي عيّنّاه في ملفّ htaccess. ويُحدّد اسمَ مُستخدِم مرفقًا بهذا الملفّ:

sudo htpasswd -c /etc/phpmyadmin/.htpasswd username

سيُطلَب منك إعطاء ثمّ تأكيد كلمة سرّ المستخدِم الّذي تُنشئه (username في الأمر أعلاه).سيُنشأ بعدها ملفّ يحوي اسم المُستخدم وتلبيد Hash كلمة السّرّ (تُحفَظ كلمة السّرّ بعد تعميّتها Encryption).
تُمكن إضافة مُستخدم جديد عن طريق الأمر أعلاه ولكن بدون خيار c- كما يلي:

sudo htpasswd /etc/phpmyadmin/.htpasswd additionaluser

عند محاولة الولوج الآن إلى phpMyAdmin فسيُطلَب منك إدخال اسم الحساب الجديد وكلمة السّر المُصاحبة له الّذيْن ضبطتهما للتّو.

http://domain_name_or_IP/phpmyadmin

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

خاتمة

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

ترجمة - وبتصرّف - لمقال How To Install and Secure phpMyAdmin on Ubuntu 14.04.

ماهو MySQL؟

MySQL هو برنامج لإدارة قواعد البيانات Database Management System, DBMS يُساعد مستخدميه في تخزين، تنظيم والعثور على البيانات. ينتشر استخدامُ MySQL في مواقع الويب نظرًا لميزاته والمرونة الّتي يُوفّرها. نهدِف في هذا الدّرس تقديم أساسيّات استخدام MySQL بطريقة مُيَسَّرة وسهلة.

كيف يُثَبَّت برنامج MySQL على توزيعتيْ Ubuntu وCentOS؟

يُمكِن تنزيلُ وتثبيتُ MySQL سريعًا عبر برنامج إدارة الحزم الخاصّ بالتّوزيعة.

1- على Ubuntu:

sudo apt-get install mysql-server

2- على CentOS:

sudo yum install mysql-server
/etc/init.d/mysqld start

ملحوظة: أثناء تثبيت MySQL سيُطلب منك إدخال ثمّ تأكيد كلمة سرّ للمستخدِم الجذر Root user في MySQL (انتبه إلى أنّ المُستخدم الّذي نتحدّث عنه هنا ليس هو المستخدِم الجذر في الخادوم، بل مستخدم جذر آخر خاصّ بـMySQL).

كيف يكون الاتصال بالصدفة Shell الخاصة بـ MySQL؟

يُمكن - بعد التّثبيت - الاتّصال بMySQL عن طريق صَدَفة ولوج خاصّة عبر تنفيذ الأمر التّالي:

mysql -u root -p

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

ينبغي الانتباه للنّقطتيْن التّاليّتيْن:

• تنتهي كلّ أوامر MySQL بنقطة-فاصلة إنجليزيّة (;). إن لم تختِم جملةً من أوامر MySQL بنقطة-فاصلة فلن تُتنفّذ هذه الأوامر.
• تُكتَب أوامرُ MySQL بأحرف كبيرة Uppercase وأسماءُ كلّ من قواعد البيانات، المُستخدمين، الجداول Tables والنّصوص بأحرف صغيرة. هذه النّقطة ليست إجبارية؛ سطرُ أوامر MySQL لا يتأثّر بحالة الأحرف Case insensitive ولكن التّفريق المذكور يجعلُ من التّمييز بين الأوامر وغيرها أكثر سهولة.

إنشاء وحذف قاعدة بيانات MySQL

يُنظِّم MySQL معلوماتِه في قواعد بيانات تتضمّن كلّ واحدة منها جداول ببيانات مُحدَّدة.

يُمكنك عرض قواعد البيانات الموجودة في MySQL عبر الأمر التّالي (يُنفَّذ في صَدَفة MySQL وليس سطر أوامر النّظام):

SHOW DATABASES;

يجب أن يبدو محتوى الشّاشة لديك مشابِهًا لما يلي:

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set (0.01 sec)

من اليسير إنشاءُ قاعدة بيانات عن طريق الأمر التّالي(حيثُ database name يُمثّل اسمَ قاعدة البيانات):

CREATE DATABASE database name;

لإنشاء قاعدة بيانات باسم events مثلًا نُنفّذ الأمر:

CREATE DATABASE events;

نُعيد عرضَ قواعد البيانات الموجودة في MySQL:

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| events             |
| mysql              |
| performance_schema |
| test               |
+--------------------+
5 rows in set (0.00 sec)

نُلاحِظ وجود قاعدة البيانات الّتي أنشأناها للتّو.

تُستخدم غالبًا عبارة DROP لحذف الكائنات Objects في MySQL. إذا أردتَ حذفَ قاعدة بيانات فالأمر المناسب هو DROP DATABASE، كما يلي (حيثُ database name اسم قاعدة البيانات المُراد حذفُها):

DROP DATABASE database name;

استخدام قاعدة بـيانات في MySQL

نبدأ بملْء قاعدة البيانات بعدَ إنشائها عبر إضافة معلومات إليها.

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

USE events;

يُمكن -كما فعلنا مع قواعد البيانات- عرضُ الجداول الموجودة في قاعدة البيانات الّتي نستخدمُها الآن:

SHOW tables;

تظهر، عندَ تنفيذ الأمر السّابق رسالة Empty set (مجموعة خاليّة) دلالةً على عدم وجود جداول في قاعدة البيانات. هذه النّتيجة طبيعيّة جدًّا نظرًا لجِدّة قاعدة البيانات.

إنشاء جدول في قاعدة بيانات MySQL

نفترِض أنّنا نُخطّط لحَدَث يجمع بعضَ الأصدقاء. سنستخدم MySQL لتتبّع تفاصيل هذا الحدث.

نُنشئ جدولًا في MySQL لهذا الغرض (تذكّر أننا نستخدم قاعدة بيانات باسم events أي أنّ الجدول الّذي سنُنشئه سيكون ضمن قاعدة البيانات هذه):

CREATE TABLE potluck (id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20),
food VARCHAR(30),
confirmed CHAR(1),
signup_date DATE);

نفّذنا عبر الأمر أعلاه الإجراءات التّالية:

• إنشاء جدول باسم potluck ضمن قاعدة البيانات events.
• إنشاء خمسة أعمِدة ضمن الجدول potluck. هذه الأعمدة هيّ: id (المُعرِِّف)، name (الاسم)، food (الطّعام)، confirmed (حضور مؤكَّد) و signup date (تاريخ التّسجيل).
• تطبيق الأمر INT NOT NULL PRIMARY KEY AUTO_INCREMENT للعمود id من أجل إعطاء عدد صحيح (INT في الأمر) أتوماتيكيًّا لكل صف جديد.
• قصرُ العمود name على عدد محارِف لا يتجاوز العشرين (20)VARCHAR.
• نفس الشيء بالنّسبة للعمود food الّذي يُمثِّل متعلّقات الطّعام الّتي سيجلبها كل واحد من الأصدقاء. اسم الطّعام مُكوّن من محارِف لا يتجاوز عددها الثّلاثين (30)VARCHAR.
• تسجيل تأكيد الحضور عبر العمود confirmed المُكوّن من محرف واحد فقط (1)CHAR. عند تأكيد الحضور نكتب Y وإلّا N.
• حفظ تاريخ التّسجيل عبر العمود signup_date. يطلُب MySQL كتابة التّاريخ على الشّكل yyyy-mm-dd، حيثُ yyyy السّنة مكتوبة على 4 أرقام، وmm الشهر في رقمين وdd اليوم في رقميْن أيضًا. 01-01-2015 تاريخ بصيغة صحيحة.

ملحوظة: كلّ من CHAR و VARCHAR يُستخدَم للدّلالة على أن العمود يحوي محارف مع تحديد طول المحتوى، ولكن طول المحتوى من نوع CHAR ثابت، أي أنّه يجب أن يكون مساويًّا للعدد بين قوسيْن؛ في حين أنّ طول المحتوى من نوع VARCHAR متغيِّر والعدد المُذكور هو الطّول الأقصى المسموح به.

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

mysql> SHOW TABLES;
+------------------+
| Tables_in_events |
+------------------+
| potluck          |
+------------------+
1 row in set (0.01 sec)

نُلاحِظ ظهور الجدول potluck.

إن أردتَ عرض تنظيم الجدول potluck فالأمر DESCRIBE يؤدّي هذه الوظيفة:

DESCRIBE potluck;

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

mysql>DESCRIBE potluck;
+-------------+-------------+------+-----+---------+----------------+
| Field       | Type        | Null | Key | Default | Extra          |
+-------------+-------------+------+-----+---------+----------------+
| id          | int(11)     | NO  | PRI  | NULL    | auto_increment |
| name        | varchar(20) | YES |      | NULL    |                |
| food        | varchar(30) | YES |      | NULL    |                |
| confirmed   | char(1)     | YES |      | NULL    |                |
| signup_date | date        | YES |      | NULL    |                |
+-------------+-------------+------+-----+---------+----------------+
5 rows in set (0.01 sec)

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

لدينا الآن جدول لتنظيم معلومات الحدَث، بقي ملؤه بالبيانات اللّازمة.

استخدِم الأمرَ التّالي لإضافة صف بيانات جديد:

INSERT INTO `potluck` (`id`,`name`,`food`,`confirmed`,`signup_date`) VALUES (NULL, "John", "Casserole","Y", '2012-04-11');

يُضيف هذا الأمر مُشاركًا جديدًا في الحدث اسمُه John وسيأتي بطنجرة (Casserole)؛ سجّلَ بتاريخ 2012-04-11 وأكّد حضوره (Y). المُعرّف سيُملأ أوتوماتيكيًّا لذا وضعنا NULL مكان العمود الأول.

ستظهر بعد تنفيذ الأمر رسالة شبيهة بالتّاليّة:

Query OK, 1 row affected (0.07 sec)

نُضيف مشاركين جددًا بتفاصيل مختلفة إلى جدول المُشاركين عبر الأوامر التّاليّة:

INSERT INTO `potluck` (`id`,`name`,`food`,`confirmed`,`signup_date`) VALUES (NULL, "Sandy", "Key Lime Tarts","N", '2012-04-14');

INSERT INTO `potluck` (`id`,`name`,`food`,`confirmed`,`signup_date`) VALUES (NULL, "Tom", "BBQ","Y", '2012-04-18');

INSERT INTO `potluck` (`id`,`name`,`food`,`confirmed`,`signup_date`) VALUES (NULL, "Tina", "Salad","Y", '2012-04-10');

لعرض جميع البيانات الموجودة في الجدول نستخدم الأمر أدناه. علامة * تعني معلومات جميع الأعمدة.

mysql> SELECT * FROM potluck;
+----+-------+----------------+-----------+-------------+
| id | name  | food           | confirmed | signup_date |
+----+-------+----------------+-----------+-------------+
| 1  | John  | Casserole      | Y         | 2012-04-11  |
| 2  | Sandy | Key Lime Tarts | N         | 2012-04-14  |
| 3  | Tom   | BBQ            | Y         | 2012-04-18  |
| 4  | Tina  | Salad          | Y         | 2012-04-10  |
+----+-------+----------------+-----------+-------------+
4 rows in set (0.00 sec)

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

SELECT name, food FROM potluck;

تحديث بيانات موجودة في جدول MySQL

يُتيح MySQL إمكانيّة تغيير بيانات موجودة في جدول. في المثال السّابق أضفنا مشارِكةً باسم Sandy. هذم المُشارِكة لم تؤكّد الحضور (قيمة العمود confirmed هي N). أبلغتْنا الآن بأنّها تأكّدت من حضورها. لتحديث عمود confirmed بالنّسبة لـsandy نُنفذ الأمر:

UPDATE `potluck`
SET
`confirmed` = 'Y'
WHERE `potluck`.`name` ='Sandy';

يُفهم الأمر أعلاه كما يلي: حدّث بيانات الجدول potluck بضبط عمود confirmed على القيمة Y إذا كانت قيمة العمود name تُساوي Sandy. كما تُلاحظ يوجد شرط للتّحديث وهو الاسم = Sandy.

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

إضافة أوحذف عمود من جدول في MySQL

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

ALTER TABLE potluck ADD email VARCHAR(40);

يقول هذا الأمر "غيّر الجدول potluck عن طريق إضافة عمود جديد باسم email لا يتجاوز المُحتوى فيه 40 محرفًا".

يُضاف هذا العمود افتراضًا في آخر الجدول، إذا كنتَ تُريد وضعَه في مكان مُعيّن يُمكن ذلك كما يلي:

ALTER TABLE potluck ADD email VARCHAR(40) AFTER name;

أي أنّ العمود الجديد سيُضاف بعد (AFTER) العمود name.

بالنّسبة لحذف عمود فهو مُشابه لإضافته:

ALTER TABLE potluck DROP email;

حذف صفّ من جدول

يُمكن إن اقتضت الحاجة حذف صفوف من الجدول عبر الأمر التّالي:

DELETE from [table name] where [column name]=[field text];

حيثُ [table name] اسم الجدوَل ، [column name] اسم العمود و[field text] هيّ قيمة العمود في الصّف.

نفرض مثلًا أنّنا نُريد حذف Sandy من قائمة المُشاركين:

mysql> DELETE from potluck where name='Sandy';
Query OK, 1 row affected (0.00 sec)

أي احذَف الصّف الّذي تُساوي فيه قيمةُ عمود الاسم Sandy. في حال وجود أكثر من شخص بهذا الاسم فسيُحذف جميع هؤلاء الأشخاص.

نُعيد عرض محتوى الجدول بعد تنفيذ أمر الحذف:

mysql> SELECT * FROM potluck;
+----+------+-----------+-----------+-------------+
| id | name | food      | confirmed | signup_date |
+----+------+-----------+-----------+-------------+
| 1  | John | Casserole | Y         | 2012-04-11  |
| 3  | Tom  | BBQ       | Y         | 2012-04-18  |
| 4  | Tina | Salad     | Y         | 2012-04-10  |
+----+------+-----------+-----------+-------------+
3 rows in set (0.00 sec)

لاحِظ عدم تغيّر معرّف أي واحد من المُشتركين المتبقّين.

ترجمة -وبتصرّف- للمقال A Basic MySQL Tutorial لصاحبته Etel Sverdlov.

مقدِّمة

يحدُث، مع ازدياد استخدام التطبيق أو موقع الويب الخاص بك، أن يتجاوز الضغط الناتج عن هذا النمو قدرةَ الإعداد الحالي على الاستجابة. في حال كنتَ تستضيف خادوم الويب والنهاية الخلفيةBackend لقاعدة البيانات على نفس الخادوم الافتراضي الخاص Virtual private server, VPS فمن الجيد فصلُ هاتين الوظيفتيْن بحيثُ تنمو كل منهما على جهاز خاص بها.

سنناقِش في هذا المقال كيفية إعداد خادوم قاعدة بيانات بعيد Remote database server يتّصل به خادوم الويب للحصول على محتوى ديناميكي. سنأخذ ووردبريس مثالا للعمل عليه. سيكون Nginx خادومَ الويب، وسنُعدّه للاتصال بقاعدة بيانات MySQL توجد على حاسوب منفصِل. تجري جميع هذه الإعدادات على خادوم افتراضي خاص يعمل بتوزيعة Ubuntu 12.04.

تثبيت MySQL على خادوم قاعدة البيانات

نبدأ بإعداد خادوم افتراضي خاص ليعمل كخادوم MySQL. يُساعِد وجود خادوم قاعدة البيانات على جهاز منفصِل في سهولة التوسّع عند الوصول إلى الحد الأعلى الذي يسمح به إعدادٌ من جهاز واحد، كما أنه يضع أساسا يُمكن استخدامه في ما بعد للجوء إلى توزيع الحِمل Load balancing من أجل توسيع بيئة العمل.

سنحتاج لتثبيت بعض الحِزم Packages على خادوم قاعدة البيانات قبل بدْء العمل. هذه الحِزم هي نفسها المستخدَمة في تثبيت حِزم LEMP اعتيادية ولكن لن نُثبِّتَها كلَّها هنا (البقية ستتواجد على الخادوم الآخر).

ابدأ بتحديث النسخ المخبَّأة من الحِزم على النظام لديك، وتثبيت خادوم MySQL:

sudo apt-get update

sudo apt-get install mysql-server 

سيُطلب منك أثناء تثبيت خادوم MySQL إدخالُ كلمة سر لحساب مُدير قاعدة البيانات (الحساب الجذرRoot user) ثم تأكيدُها.

ستحتاج بعد الانتهاء من تثبيت الحزمة إلى تنفيذ أمر تثبيت قاعدة البيانات والذي سيُنشئ بنية المجلَّدات الضرورية:

sudo mysql_install_db 

نأتي بعد التثبيت إلى خطوة الرفع من مستوى الأمان عن طريق سكريبت يطلُب تعطيل بعض إعدادات MySQL الافتراضية غير الآمنة:

sudo mysql_secure_installation

أدخِل كلمة سر مدير قاعدة بيانات MySQL (ضُبِطت أعلاه). سيطلُب منك ما إذا كنت تريد تغيير كلمة السّر، أدخِل N إن لم ترغَب في ذلك.

بالنسبة لبقية الأسئلة اضغط زر Enter لترك الخيارات الافتراضية، وهو ما سينتُج عنه حذف بعض قواعد البيانات التجريبية وقفل الولوج Access.

إعداد MySQL للسماح للولوج عن بعد

في هذه الفقرة سنضبُط قاعدة البيانات بحيث تسمح بالاتصالات القادمة من حواسيب أخرى.

افتح ملف إعداد MySQL الرئيس بصلاحيات الجذر في محرّر نصوص (Nano في هذا المثال):

sudo nano /etc/mysql/my.cnf 

يُقسَّم هذا الملف إلى مقاطِع مُعرَّفة بكلمات بين معكوفَيْن ([ و ]). اعثُر على المقطَع المعرَّف ب mysqld (لاحِظ حرف d في آخر الكلمة):

[mysqld]

ابحَث في هذا المقطَع من الملف (بينَه وبين المُعرِّف الموالي) عن مُعطى Parameter باسم bind-address. يُخبِر هذا المُعطى قاعدةَ البيانات بعناوين الشبكة التي تستمع للاتصالات القادمة عبرها. الإعداد الحالي هو الإنصات للاتصالات القادمة من الحاسوب المحلي Local host فقط. سنحتاج لتغيير هذا الإعداد والإشارة إلى عنوان IP خارجي يُمكن الاتصال عن طريقه بالخادوم الآخر.

استخدِم عنوان IP الخاص لخادومك إن كنتَ تستضيف الخواديم ضمن شبكة داخلية Private network. إن لم تكن لديك شبكة داخلية استخدم عنوان IP العمومي.

bind-address = your_database_IP 

احفظ الملف (Ctrl + O ثم زر Enter) ثم أغلقه بعد الانتهاء من تحريره (Ctrl + x).

نُعيد تشغيل قاعدة البيانات لأخذ التغييرات بالاعتبار:

sudo service mysql restart 

إعداد اعتماداتCredentials ووردبريس وقاعدة البيانات

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

سنغتنم هذه الفرصة أيضًا لاعتماد صلاحيات Privileges تختلف حسب الجهة التي يتصل منها المستخدِم. يُمكن مثلا أن نُنشئ مستخدمَيْن باسم "user" ولكن كل منهما مرتبط بمستضيف Host مختلف. يعني هذا أنه يُمكننا إنشاء مستخدِم مرتبط بخادوم قاعدة البيانات نفسه وتكون لديه صلاحيات واسعة ثم نستعمل نفس اسم المستخدم على خادوم الويب ونعطيه فقط الصلاحيات التي يحتاجها ووردبريس.

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

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

mysql -u root -p 

سيُطلَب منك إدخال كلمة سر المدير بعد تنفيذ هذا الأمر.

بعد الولوج نُنشئ قاعدة البيانات التي سيتّصل بها ووردبريس. اخترنا إعطاء اسم wordpress لقاعدة البيانات ليسهُل علينا التعرف عليها لاحقا:

CREATE DATABASE wordpress; 

الآن سننشئ مستخدِما جديدا لديه كامل الصلاحيات على قاعدة البيانات wordpress. سنسميه wordpressuser ثم نحدّد الجهات التي يُمكن منها استعمال هذا المُستخدِم بحيث يقتصر استعماله على الاتصالات من الخادوم نفسه عن طريق استخدام localhost أثناء تنفيذ الأمر، كما يلي:

CREATE USER 'wordpressuser'@'localhost' IDENTIFIED BY 'password'; 

في الأمر السّابق أنشأنا مستخدِما باسم wordpressuser مع كلمة السر password (اكتب كلمة سر خاصة بك مكانها) وحصرنا الجهات التي يُمكنه الاتصال منها بالمستضيف المحلي (localhost)، أي خادوم قاعدة البيانات نفسِه. نُكمل بإعطاء هذا المستخدِم كامل الصّلاحيات على قاعدة البيانات wordpress:

GRANT ALL PRIVILEGES ON wordpress.* TO 'wordpressuser'@'localhost'; 

يُمكن للمستخدم wordpressuser أن يُجري أي عملية على قاعدة البيانات wordpress بشرطِ أن يكون متصلا من خادوم قاعدة البيانات.

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

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

CREATE USER 'wordpressuser'@'web_server_IP' IDENTIFIED BY 'password'; 

يُنشِئ هذا الأمر مستخدِما باسم wordpressuser مع كلمة السر password وحصرنا الجهات التي يُمكنه الاتصال منها بخادوم الويب (حيثُ web_server_IP عنوان IP خادوم الويب).

الهدف هو أن تقتصر صلاحيات هذا المستخدم على صلاحيات يحتاجها ووردبريس للعمل وهي القراءة من قاعدة البيانات (SELECT) ، وحذف سطر من جدول (DELETE)، وإضافة سطر إلى جدول (INSERT) وتحديث البيانات الموجودة سلفا (UPDATE؛ ولكن ليس بإمكاننا تفعيل هذا الإعداد الآن. السبب أن هذه الصلاحيات لا تكفي للقيام ببعض العمليات، مثل تثبيت ووردبريس. وهو ما يعني أننا سنُفعِّل مؤقتا صلاحيات أوسع لهذا المستخدِم ثم نعود لنزعها منه وترك الصلاحيات الأربع التي ذكرناها سابقا فقط، وذلك بعد اكتمال التثبيت.

للمعلومة، الأمر المُستَخدم لتأمين الحساب والحد من صلاحياته هو التالي (سنعود له في ما بعد):

GRANT SELECT,DELETE,INSERT,UPDATE ON wordpress.* TO 'wordpressuser'@'web_server_ip'; 

قبل ذلك سننمح هذا المُستخدِم - مؤقتا - كامل الصلاحيات، وهو ما يجعله مطابِقا للمستخدِم السابق:

GRANT ALL PRIVILEGES ON wordpress.* TO 'wordpressuser'@'web_server_ip';

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

نحفظ التغييرات التي أجريناها على الصّلاحيات بكتابتها على القرص الصّلب:

FLUSH PRIVILEGES; 

يُمكن الآن الخروج من سطر أوامر MySQL عن طريق تنفيذ الأمر:

exit 

اختبِر الاتصالات المحلية والبعيدة

سنتأكد قبل المواصلة مع بقية الخطوات من إمكانية الاتصال باستخدام الحساب wordpressuser على كل من خادوم قاعدة البيانات وخادوم الويب.

جرِّب أولا الاتصال من الجهاز الذي توجد به قاعدة البيانات باستخدام الحساب الجديد:

mysql -u wordpressuser -p 

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

exit 

سجِّل الدخول إلى خادوم الويب لتجربة الاتصال عن بُعد.

ستحتاج لتثبيت بعض الأدوات على خادوم الويب حتى يُمكنك الولوج إلى قاعدة البيانات عن بعد. حدِّث النسخ المخبَّأة من الحزم ثم ثبّت أدوات عميل MySQL Client:

sudo apt-get update

sudo apt-get install mysql-client 

يُمكننا بعد اكتمال التثبيت تجربة الاتصال بخادوم قاعدة البيانات: 

mysql -u wordpressuser -h database_server_IP -p 

مُجدّدا، تأكد من استخدام نفس عناوين الشبكة التي استخدمتها أثناء إعداد خادوم قاعدة البيانات.

أدخِل كلمة السر عندما تُطلَب منك. ينبغي أن يظهر سطر أوامر MySQL، وهو ما يدل على نجاح الاختبار؛ يُمكنك بعدها الخروج من سطر أوامر MySQL.

للمزيد من التحقق يُمكنك تجربة الاتصال من خادوم ثالث للتأكد من عدم سماح خادوم قاعدة البيانات لك بالولوج. يعني هذا أنك تحققتَ من الولوج من الجهاز المحلي (خادوم قاعدة البيانات) ومن خادوم الويب، لكن لا يُسمَح بالولوج من خادوم آخر.

نُجرّب الولوج إلى قاعدة البيانات من خادوم غير مُصرَّح له بالدخول عن طريق عنوان IP:

mysql -u wordpressuser -h database_server_IP -p 

لن يُسمَح لك بالدخول، بدلا من ذلك تظهر رسالة خطأ كالتالي:

ERROR 1130 (HY000): Host '11.111.111.111' is not allowed to connect to this MySQL server 

يعني هذا أن الجهاز الذي تُريد الاتصال منه لا يُسمح له بالولوج إلى قاعدة البيانات، وهو بالضبط ما نريده.

إعداد خادوم الويب

نحتاج الآن بعد التأكد من إمكانية الاتصال من خادوم الويب إلى تثبيت البرامج الضرورية لهذا الخادوم ليقوم بعمله (تقديم صفحات الويب). سنضبُط كلًّا من Nginx وPHP وبقية العناصر الضرورية لخادوم الويب.

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

sudo apt-get install nginx php5-fpm php5-mysql 

بعد انتهاء تثبيت جميع الحزم، يمكن الانتقال إلى إعداد البرامج.

إعداد PHP

نبدأ بالأسهل، إعداد PHP.

افتَح ملف إعداد PHP حيثُ سنضبُط بعض القيم المتعلقة بphp-fpm، وهو المُكوِّن المسؤول عن تقديم المحتوى الديناميكي في PHP:

sudo nano /etc/php5/fpm/php.ini 

ابحَث عن مُعطى Parameter باسم cgi.fix_pathinfo. غالب الظن أنه سيكون مسبوقا بعلامة ";" (دلالةً على أن السّطر تعليق، أي أنه لا يُؤخذ في الاعتبار) وقيمته تساوي "1". سننزع علامة التعليق ونُعطيه القيمة "0". فيُصبِح السطر كالتالي:

cgi.fix_pathinfo=0 

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

احفَظ ثم أغلِق الملف.

ننتقل الآن إلى إعداد الطريقة التي يتواصل بها المعالج (Processor) الخاص بPHP مع خادوم الويب:

sudo nano /etc/php5/fpm/pool.d/www.conf 

ابحَث عن تعليمة Directive الاستماع، التي تأتي افتراضيا مضبوطة على 127.0.0.1:9000. بدلا من منفذ Port، سنستخدم مقبس Unix Socket ، Unix.

listen = /var/run/php5-fpm.sock 

احفَظ ثم أغلِق الملف بعد الانتهاء من تحريره.

نُعيد تشغيل معالج PHP لاعتماد القِيم الجديدة:

sudo service php5-fpm restart 

إعداد Nginx
ننتقل بعد إعداد PHP إلى إعداد خادوم ويب Nginx. نبدأ بنسخ ملف المستضيفات الافتراضية Virtual hosts الموجود سلفًا إلى ملف جديد سنعمل عليه، ونمنحه اسم نطاق الموقع. اخترنا "example.com" مثالاً للتوضيح هنا:

sudo cp /etc/nginx/sites-available/default /etc/nginx/sites-available/example.com 

الآن افتَح الملف الجديد:

sudo nano /etc/nginx/sites-available/example.com 

سنُعدّل الجزء الخاص بالخادوم داخل الملف (الأسطُر الموجودة بين الأقواس المعكوفة الموجودة بعد كلمة server). ابدأ بتعليق تعليمة الإنصات للمنفَذ رقم 80. سنغيِّر أيضًا المجلّد الجذر ونطلُب من Nginx تقديم ملف (فهرس) PHP بشكل افتراضي:

server {

listen 80;

root /var/www/example.com;

index index.php index.hmtl index.htm; 

نأتي الآن لتعليمة server_name التي سنُعدّلها حتى تستخدم اسم النطاق الذي نريد. تأكّد من ضبط تعليمة try_files بشكل صحيح (تمرير الطّلبات إلى PHP في حال عدم عثور خادوم ويب Nginx على الملف المطلوب) وكذلك من ضبط صفحات الخطأ.

server {

listen 80;

root /var/www/example.com;

index index.php index.hmtl index.htm;

server_name example.com;

location / {

try_files $uri $uri/ /index.php?q=$uri&$args;

}

error_page 404 /404.html;

error_page 500 502 503 504 /50x.html;

location = /50x.html {

root /usr/share/nginx/www;

} 

نحتاج قبل الانتهاء من إعداد Nginx لضبط معالجة PHP عن طريق استعمال جزء location لمُطابقة جميع طلبات PHP. في حال تعثر الحصول على الملف المطلوب نُجيب مباشرة بخطأ 404. نستخدم هنا أيضًا نفس المقبس الذي استخدمناه في PHP.

server {

listen 80;

root /var/www/example.com;

index index.php index.hmtl index.htm;

server_name example.com;

location / {

try_files $uri $uri/ /index.php?q=$uri&$args;

}

error_page 404 /404.html;

error_page 500 502 503 504 /50x.html;

location = /50x.html {

root /usr/share/nginx/www;

}

location ~ \.php$ {

try_files $uri =404;

fastcgi_pass unix:/var/run/php5-fpm.sock;

fastcgi_index index.php;

include fastcgi_params;

}

}

بهذا ننتهي من إعداد الجزء المتعلّق بالخادوم. احفَظ ثم أغلق الملف.

تبّقى لنا حذف رابط ملف الإعداد الافتراضي ثم ربط المجلّد المفعَّل حيثُ توجد ملفات الموقع بملف إعدادات الخادوم الذي ضبطناه للتو:

sudo rm /etc/nginx/sites-enabled/default

sudo ln -s /etc/nginx/sites-available/example.com /etc/nginx/sites-enabled/ 

أعد تشغيل Nginx لأخذ التعديلات بالاعتبار:

sudo service nginx restart 

ثبِّت ووردبريس

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

نزِّل آخر إصدار من ووردبريس (4.1.1 أثناء كتابة هذه السّطور) إلى مجلّدك الشخصي:

cd ~

wget https://ar.wordpress.org/wordpress-4.1.1-ar.tar.gz

استخرِج المفات عن طريق الأمر التالي الذي سينتُج عنه إنشاء مجلَّد باسم "wordpress" داخل مجلّدك الشخصي:

tar xzvf latest.tar.gz 

يأتي مع ووردبريس مثال لملف الإعداد ولكنه غير جاهز للاستعمال، سنُعيد تسميَّته حتى يُقرأ بشكل صحيح ثم نفتحه بمحرّر نصوص للتعديل عليه:

cp ~/wordpress/wp-config-sample.php ~/wordpress/wp-config.php

nano ~/wordpress/wp-config.php 

أدخِل القيم الصحيحة للتعليمات التالية، تذكَّر أن تستخدِم نفس عنوان IP الذي استخدمتَه أثناء تجرية الاتصال بقاعدة البيانات.

/** اسم قاعدة بيانات ووردبريس */

define('DB_NAME', 'wordpress');

/** اسم المستخدم لقاعدة البيانات */

define('DB_USER', 'wordpressuser');

/** كلمة المرور لقاعدة البيانات */

define('DB_PASSWORD', 'password');

/** عنوان خادوم قاعدة البيانات */

define('DB_HOST', 'database_server_ip'); 

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

الآن سننشئ بنية المجلَّد التي أدرجناها خلال إعداد خادوم Nginx، في الجزء المعلَّم ب server في ملف الإعداد، حيثُ استخدمنا "exemaple.com" كمثال توضيحي؛ استخدِم اسم النطاق الذي اخترتَه خلال إعداد Nginx:

sudo mkdir -p /var/www/example.com 

تم ننسَخ الملفات والمجلَّدات الموجودة داخل مجلّد ووردبريس (wordpress/~) إلى المجلّد الجذر الذي أنشأناه للتّو:

sudo cp -r ~/wordpress/* /var/www/example.com 

كل الملفات الآن في المكان الذي يجب أن تكون فيه، تبقى لنا فقط تغيير أذونات Permissions ومُلكية المجلّد. ننتقل إلى مبدأ المستند Document root في خادوم الويب (المجلّد الأعلى مستوًى Top-level directory الذي سيبحث فيه خادوم الويب عن محتوى الموقع):

cd /var/www/example.com 

سنجعل من حساب خادوم الويب واسمُه www-data، مالكَ جميع الملفات في هذا المجلّد:

sudo chown -R www-data:www-data * 

سنحتاج لإذن الكتابة في ملفات الموقع حتى يُمكننا التعديل عليها. لذا سنُضيف الحساب العادي غير الجذر إلى مجموعة Group خادوم الويب ثم نُعطي لهذه المجموعة إذن التعديل على الملفات الموجودة في هذا المجلَّد:

sudo usermod -a -G www-data your_user

sudo chmod -R g+rw /var/www/example.com 

إعداد الموقع عبر واجهة الويب

كل ما تحتاجه الآن هو استكمال التثبيت عن طريق واجهة الويب. استخدِم المتصفّح للدخول إلى اسم نطاق الموقع (يُمكن أيضا استخدام عنوان IP العمومي):

http://example.com

يجب أن تظهر شاشة تثبيت ووردبريس والتي يجب أن تملأ حقول النموذج الموجود فيها بالمعلومات الضرورية:

استخدِم الحساب الذي أنشأتَه على ووردبريس أثناء ضبط إعداداته للولوج إلى لوحة التحكم:

ستُنقَل بعدها إلى لوحة تحكم ووردبريس حيثُ يمكنك البدء في إعداد موقعك.

قيّد أذون الولوج عن بعد إلى قاعدة البيانات

نعود الآن بعد الانتهاء من تثبيت ووردبريس إلى استرجاع بعض الأذونات التي منحناها لمستخدِم قاعدة البيانات البعيد.

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

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

ادخُل على خادوم قاعدة البيانات ثم إلى MySQL باستخدام الحساب الجذر :

mysql -u root -p 

سيُطلب منك إدخال كلمة السر.

نفّذ الأمر التالي لإظهار أذون المستخدِم البعيد:

show grants for 'wordpressuser'@'web_server_IP';



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

| Grants for wordpressuser@xxx.xxx.xxx.xxx |

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

| GRANT USAGE ON *.* TO 'wordpressuser'@'xxx.xxx.xxx.xxx' IDENTIFIED BY PASSWORD '*5FD2B7524254B7F81B32873B1EA6D681503A5CA9' |

| GRANT ALL PRIVILEGES ON `wordpress`.* TO 'wordpressuser'@'xx.xxx.xxx.xxx' |

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

2 rows in set (0.00 sec) 

لا يعني إذن الاستعمال Usage في السّطر الأول أي صلاحيات فعلية، لذا لن نهتمّ به هنا. الصّلاحيات الموجودة في السّطر الثاني هي التي أعددناها في خطوة سابقة من هذا الدّليل حيثُ أعطينا المستخدِم wordpressuser كامل الصلاحيات (ALL PRIVILEGES في الجدول أعلاه) على قاعدة البيانات wordpress عند الاتصال عن بعد بخادوم قاعدة البيانات.

تتكون عملية تقييد الصّلاحيات من خطوتين. أولا ننزع كل الصّلاحيات الممنوحة حاليا للمستخدِم، وذلك عبر الأمر:

REVOKE ALL PRIVILEGES on wordpress.* FROM 'wordpressuser'@'web_server_IP'; 

عند طلب إظهار صلاحيّات المستخدِم الحالية سنُلاحظ اختفاء السّطر الثاني:

show grants for 'wordpressuser'@'web_server_IP';



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

| Grants for wordpressuser@xxx.xxx.xxx.xxx |

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

| GRANT USAGE ON *.* TO 'wordpressuser'@'xxx.xxx.xxx.xxx' IDENTIFIED BY PASSWORD '*5FD2B7524254B7F81B32873B1EA6D681503A5CA9' |

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

1 row in set (0.00 sec) 

ثانيا، نُحدّد الصلاحيات التي نرغب في منحها لحساب المستخدِم، وهي كما ذكرنا سالفا القراءة من قاعدة البيانات (SELECT) ، وحذف سطر من جدول (DELETE)، وإضافة سطر إلى جدول (INSERT) وتحديث البيانات الموجودة (UPDATE):

GRANT SELECT,DELETE,INSERT,UPDATE ON wordpress.* TO 'wordpressuser'@'web_server_ip'; 

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

بقي أن نطلُب من MySQL أخذ التغييرات التي أجريناها في الاعتبار عن طريق الأمر:

FLUSH PRIVILEGES; 

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

exit 

خاتمة

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

ترجمة -وبتصرّف- للمقال: How To Set Up a Remote Database to Optimize Site Performance with MySQL