كيفية استخدام ProxySQL كموازن تحميل لخادم MySQL على نظام التشغيل أوبنتو 16.04

ProxySQL هو عبارة عن خادم وكيل (proxy server) مفتوح المصدر لخادم قاعدة البيانات MySQL، أي أنه يعمل كوسيط بين خادم قاعدة البيانات MySQL والتطبيقات التي تتعامل مع قاعدة البيانات هذه. حيث يقوم الخادم الوكيل بتحسين الأداء، وذلك عن طريق توزيع النقل بين خوادم قاعدة البيانات مشتركة. كما يحسّن من عملية توفّر قاعدة البيانات، وذلك من خلال الانتقال الآلي إلى خادم أخر متاح عند فشل أحد خوادم قاعدة البيانات.

سيتم في هذه المقالة شرح طريقة تنصيب الخادم الوكيل ProxySQL كموازن تحميل (load balancer، توزيع الحمل) لعدة خوادم بيانات MySQL مع تمكين الانتقال الآلي إلى الخوادم المتاحة عند فشل أحدها. حيث سيتم استخدام مجموعة التناسخ المتماثل Group Replication ذات العُقد الرئيسية المتعددة multi-primary مكونة من ثلاث خوادم MySQL، غير أنه يمكن تطبيق الشرح الوارد في هذه المقالة على أي عدد من الخوادم.

المتطلبات

قبل البدء يجب أن يتوفر ما يلي:

• خادم أوبنتو 16.04 يتضمن على حساب مستخدم عادي (غير جزر) بصلاحيات sudo وجدار حماية.
• ثلاث خوادم بيانات MySQL مهيّأة للعمل كمجموعة تناسخ متماثل، والتي يمكن إعدادها اعتمادًا على هذه المقالة، الفقرة multi-primary.

الخطوة الأولى- تنصيب ProxySQL

قام مطورو ProxySQL بتوفير حزم أوبنتو رسمية لكل إصدارات ProxySQL، وذلك على صفحتهم في موقع GitHub، لذا سنقوم بتحميل أخر إصدار ProxySQL من هذه الصفحة، حيث تتضمن هذه الصفحة على لائحة بجميع إصدارات ProxySQL، وكل إصدار تَمّت تسميته وفق الصيغة proxysql_version-distribution.deb، إذ version هي عبارة عن رقم إصدار حزمة ProxySQL مثل 1.4.4، و distribution هي عبارة عن توزيعة لينكس المخصصة لها هذه الحزمة مثل ubuntu16_amd64.

سنقوم الآن بتحميل أخر إصدار من حزمة ProxySQL (1.4.4 هو آخر إصدار لحظة كتابة هذه المقالة) إلى المسار ‎/tmp، وذلك بتنفيذ الأوامر التالية في موجه أوامر لينكس:

$ cd /tmp
$  curl -OL https://github.com/sysown/proxysql/releases/download/v1.4.4/
proxysql_1.4.4-ubuntu16_amd64.deb

ثم نقوم بعد انتهاء عملية التحميل، بتنصيب هذه الحزمة باستخدام أداة إدارة حزم ‎.deb البرمجية dpkg، وذلك بتنفيذ الأوامر التالية في موجه أوامر لينكس:

$ sudo dpkg -i proxysql_*

حيث استُخدمت الراية ‎-i للدلالة على رغبتنا بتنصيب الحزمة من ملف محدد. ثم نقوم بعد انتهاء عملية التنصيب بحذف ملف الحزمة المُحمّل لعدم الحاجة إليه بعد الآن، وذلك بتنفيذ الأمر التالي:

$ rm proxysql_*

نحتاج الآن لتطبيق عميل لقاعدة البيانات MySQL ليتصل مع نسخة ProxySQL المُنصّبة، ذلك لأن ProxySQL يستخدم واجهة تخاطب متوافقة مع MySQL من أجل مهام الإدارة. لذا سنستخدم موجه أوامر MySQL، والذي هو جزء من الحزمة mysql-client المتوفرة على مخازن حزم أوبنتو. لكن قبل البدء بتنصيب هذه الحزمة يجب أن نحدّث مستودع الحزم لضمان تحميل وتنصيب آخر إصدار من هذه الحزمة، وذلك بتنفيذ الأمر التالي:

$ sudo apt-get update

ثم نقوم بتنصيب الحزمة mysql-client، وذلك بتنفيذ الأمر التالي:

$ sudo apt-get install mysql-client

أصبح لدينا الآن جميع متطلبات تشغيل ProxySQL، غير أن هذه الخدمة لا تُشَّغل آليًا بعد التنصيب، لذا يتوجب علينا تشغيلها يدويًا وذلك بتنفيذ الأمر التالي:

$ sudo systemctl start proxysql

سيعمل ProxySQL الآن بإعداداته الافتراضية، والتي يمكن عرضها بتنفيذ الأمر التالي:

$ systemctl status proxysql

فنحصل على الخرج التالي في نافذة موجه الأوامر:

● proxysql.service - LSB: High Performance Advanced Proxy for MySQL
   Loaded: loaded (/etc/init.d/proxysql; bad; vendor preset: enabled)
   Active: active (running) since Thu 2017-12-21 19:19:20 UTC; 5s ago
     Docs: man:systemd-sysv-generator(8)
  Process: 12350 ExecStart=/etc/init.d/proxysql start (code=exited، status=0/SUCCESS)
    Tasks: 23
   Memory: 30.9M
      CPU: 86ms
   CGroup: /system.slice/proxysql.service
           ├─12355 proxysql -c /etc/proxysql.cnf -D /var/lib/proxysql
           └─12356 proxysql -c /etc/proxysql.cnf -D /var/lib/proxysql

حيث تدلّ العبارة active (running) على أن ProxySQL مُنصَّب وفي حالة عمل.

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

الخطوة الثانية- إعداد كلمة سر مدير ProxySQL

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

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

$ mysql -u admin -p -h 127.0.0.1 -P 6032 --prompt='ProxySQLAdmin> '

• ‎-u: يُستخدم هذا العلَم لتحديد اسم المستخدم الذي يريد القيام بالمهام الإدارية مثل تغيير الإعدادات، وبما أنه لم يتم بعد إضافة أي مستخدم، لذا تم استخدام اسم المستخدم الافتراضي admin.
• ‎-h: يُستخدم هذا العلَم لتحديد المضيف المُراد الاتصال به، وبما أننا نريد الاتصال بالواجهة التخاطبية لإدارة ProxySQL المحلي لذا استخدمنا المضيف المحلي 127.0.0.1.
• ‎-P: يُستخدم هذا العلَم لتحديد منفذ الاتصال بالواجهة التخاطبية لإدارة ProxySQL، حيث تَستخدم هذه الواجهة التخاطبية المنفذ 6032.
• ‎--prompt: وهو علَم اختياري لتغيير الموجِّه الافتراضي mysql>‎، حيث قمنا هنا بتغييره إلى ProxySQLAdmin>‎ لتوضيح أننا اتصلنا بالواجهة التخاطبية لإدارة ProxySQL، وذلك لتجنب الالتباس لاحقًا عند الاتصال أيضًا بخوادم قاعدة البيانات MySQL.

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

عند نجاح عملية الدخول إلى الواجهة التخاطبية لإدارة ProxySQL، يتغيّر موجه أوامر النظام إلى الموجه ProxySQLAdmin>‎ الخاص بالواجهة التخاطبية لإدارة ProxySQL كما يلي:

ProxySQL administration console prompt

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.5.30 (ProxySQL Admin Module)

Copyright (c) 2000، 2017، 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.

ProxySQLAdmin>

ثم نقوم بتغيير كلمة السر مدير ProxySQL، وذلك بتنفيذ الأمر التالي في موجه أوامر الواجهة التخاطبية لإدارة ProxySQL:

ProxySQLAdmin> UPDATE global_variables SET variable_value='admin:password' 
WHERE variable_name='admin-admin_credentials';

Output
Query OK، 1 row affected (0.00 sec)

حيث تمّ استخدام الأمر UPDATE من أجل تغيير كلمة السر password للحساب الإداري admin الموجودة في المتغير admin-admin_credentials ضمن قاعدة البيانات global_variables.

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

لا تدخل عملية تغيير كلمة السر حيِّز التنفيذ مباشرة، وذلك نتيجة بنية نظام الإعدادات الخاص بالخادم ProxySQL، والذي يتكون من ثلاث طبقات منفصلة هي:

• طبقة الذاكرة MEMORY: وهي عبارة عن قواعد بيانات الإعدادات المُحمّلة في الذاكرة والتي يتم التفاعل معها والتعديل عليها مباشرة من خلال موجه أوامر الواجهة التخاطبية لإدارة ProxySQL.
• طبقة وقت التشغيل RUNTIME: يَستعمل ProxySQL هذه الطبقة لتفعيل الإعدادات المعدَّلة ودخولها حيز التنفيذ مباشرة.
•  طبقة القرص DISK: وهي عبارة عن قاعدة بيانات محفوظة على القرص الصلب والتي يتم فيها حفظ الإعدادات، حيث تُحمَّل هذه الإعدادات في طبقة الذاكرة عند إقلاع النظام، وبالتالي تضيع أي تعديلات تم إجرائها على هذه الإعدادات في طبقة وقت التشغيل، وذلك أثناء إعادة تشغيل النظام إن لم يتم حفظها في طبقة القرص.

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

ProxySQLAdmin> LOAD ADMIN VARIABLES TO RUNTIME;

ثم يجب القيام بحفظها في طبقة القرص كي نحافظ عليها بشكل دائم، وذلك بتنفيذ الأمر التالي موجه أوامر الواجهة التخاطبية لإدارة ProxySQL:

ProxySQLAdmin> SAVE ADMIN VARIABLES TO DISK;

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

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

الآن وبعد أن تم تنصيب ProxySQL وإعداد كلمة سر مدير النظام، سنقوم بإعداد ثلاث عُقد MySQL ليتمكن ProxySQL من مراقبتها.

الخطوة الثالثة- إعداد المراقبة في MySQL

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

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

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

$ ssh sammy@your_mysql_server_ip_1

ثم نحمل ملف SQL الذي يحتوي على بعض الوظائف الضرورية لدعم عمل مجموعة التناسخ المتماثل الخاصة بالخادم الوكيل ProxySQL، وذلك بتنفيذ الأمر التالي في موجه الأوامر:

$ curl -OL https://gist.github.com/lefred
/77ddbde301c72535381ae7af9f968322/raw
/5e40b03333a3c148b78aa348fd2cd5b5dbb36e4d/addition_to_sys.sql

ملاحظة: إن هذه الملف مُوفَّر من قبل مطوري ProxySQL غير أنه موجود ضمن مخزن GitHub شخصي، مما يعني أنه قد يُحذف أو يصبح قديمًا. لكنه قد يُضاف في المستقبل لمخزن ProxySQL الرسمي على شكل ملف له إصدار. ولمزيد من المعلومات حول هذا الملف يمكن قراءة المقال التالي: native ProxySQL support for MySQL group replication.

يمكن رؤية محتويات هذا الملف بتنفيذ الأمر التالي:

$ less addition_to_sys.sql

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

$ mysql -u root -p < addition_to_sys.sql

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

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

$ mysql -u root -p

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

(member1)mysql> CREATE USER 'monitor'@'%' IDENTIFIED BY 'monitorpassword';

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

(member1)mysql> GRANT SELECT on sys.* to 'monitor'@'%';

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

(member1)mysql> FLUSH PRIVILEGES;

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

سيتم في الخطوة التالية تزويد ProxySQL بمعلومات المستخدم المخصص للمراقبة، ليتمكن ProxySQL من الدخول إلى عُقد MySQL.

الخطوة الرابعة- إعداد المراقبة في ProxySQL

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

ننتقل إلى موجه أوامر الواجهة التخاطبية لإدارة ProxySQL، ثم نحدّث قيمة المتغير mysql-monitor_username بقيم حساب المستخدم المخصص لمراقبة عُقد MySQL، وذلك بتنفيذ الأمر:

ProxySQLAdmin> UPDATE global_variables SET variable_value='monitor' 
WHERE variable_name='mysql-moniter_username';

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

ProxySQLAdmin> LOAD MYSQL VARIABLES TO RUNTIME;
ProxySQLAdmin> SAVE MYSQL VARIABLES TO DISK;

حيث استخدمنا الأمر MYSQL بدل ADMIN عند تحديث قيمة المتغير، لأننا نتعامل هنا مع متغيرات إعداد MySQL.

وهكذا تم إعداد ProxySQL بحساب المستخدم المراقب، وسيتم في الخطوة التالية إضافة عُقد MySQL إلى ProxySQL.

الخطوة الخامسة- إضافة عُقد MySQL إلى ProxySQL

لجعل ProxySQL مدركاً لعُقد MySQL، يجب إخباره بطريقة توزُّع هذه العُقد في المجموعات المضيفة، والتي هي عبارة عن مجموعة محددة من العُقد. وتعرَّف كل مجموعة مضيفة برقم موجب مثل 1 أو 2. حيث تُستخدم المجموعات المضيفة من أجل توجيه استعلامات SQL المختلفة إلى مجموعات مختلفة من المضيفات عند قيام ProxySQL بتوجيه الاستعلامات.

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

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

إن المعرِّف الرقمي لكل مجموعة مضيفة لا يتم تحديده آليًا، لذا يجب إخبار ProxySQL بالمعرِّف الرقمي الذي سيتم استخدامه لكل مجموعة. سنستخدم في حالتنا هنا المعرِّف 1 لمجموعة العُقد المفصولة، و المعرِّف 2 لمجموعة عُقد الكتابة، والمعرِّف 3 لمجموعة عُقد القراءة، والمعرِّف 4 لمجموعة عُقد الكتابة الاحتياطية. ولتطبيق هذه المعرّفات، سنقوم بإنشاء صف بهذه المتغيرات (المجموعات المضيفة) وقيمها الموافقة (المعرّفات الرقمية) في جدول الإعدادات mysql_group_replication_hostgroups، وذلك بتنفيذ الأمر التالي في موجه أوامر الواجهة التخاطبية لإدارة ProxySQL:

ProxySQLAdmin> LOAD INSERT INTO mysql_group_replication_hostgroups (writer_hostgroup، backup_writer_hostgroup، reader_hostgroup، offline_hostgroup، active، max_writers، writer_is_also_reader، max_transactions_behind) VALUES (2، 4، 3، 1، 1، 3، 1، 100);

تم في الأمر السابق إعداد متغيرات إضافية ضمن صف هي:

• active: وهو المتغيّر الخاص بتمكين ProxySQL من مراقبة المجموعات المضيفة، حيث تم إسناده بالقيمة 1 (تمكين).
• max_writers: وهو المتغير الخاص بتحديد العدد الأعظمي من عُقد الكتابة، حيث تم إسناده بالقيمة 3 كون مجموعة التناسخ المتماثل الخاصة بنا من النوع multi-primary، أي قمنا بتعيين جميع العُقد الثلاثة كعُقد كتابة.
• writer_is_also_reader: يُستخدم لتفعيل القراءة في عُقد الكتابة. حيث تم إسناده بالقيمة 1 وذلك لإعلام ProxySQL باستخدام جميع عُقد القراءة كعُقد كتابة أيضًا.
• max_transactions_behind: يُستخدم لتحديد العدد الأعظمي من محاولات الاتصال قبل تصنيف العُقدة كعُقدة مفصولة.

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

بما أن ProxySQL يعلم الآن طريقة توَّزع عُقد MySQL ضمن المجموعات المضيفة، لذا أصبح بمقدورنا الآن إضافة خوادم MySQL الموافقة لهذه العُقد إلى قائمة الخوادم المرتبطة بالخادم الوكيل ProxySQL. وذلك من خلال إدخال INSERT عنوان IP واسم المجموعة المضيفة الأولية لكل خادم إلى الجدول mysql_servers، والمتضمّن على لائحة بخوادم MySQL التي يستطيع ProxySQL التفاعل معها. وذلك بتنفيذ الأوامر التالية في موجه أوامر الواجهة التخاطبية لإدارة ProxySQL بعد استبدال عناوين IP الخوادم بالقيمة الحقيقية:

ProxySQLAdmin> INSERT INTO mysql_servers(hostgroup_id، hostname، port) VALUES (2، '203.0.113.1'، 3306);
ProxySQLAdmin> INSERT INTO mysql_servers(hostgroup_id، hostname، port) VALUES (2، '203.0.113.2'، 3306);
ProxySQLAdmin> INSERT INTO mysql_servers(hostgroup_id، hostname، port) VALUES (2، '203.0.113.3'، 3306);

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

ProxySQLAdmin> LOAD MYSQL VARIABLES TO RUNTIME;
ProxySQLAdmin> SAVE MYSQL VARIABLES TO DISK;

سيقوم ProxySQL الآن بتوزيع عُقد MySQL الخاصة بنا ضمن المجموعات المضيفة كما هو محدد. وللتأكد من ذلك سنقوم بالاستعلام SELECT عن الجدول runtim330e_mysql_servers والذي يعرض الحالة الآنية لقائمة خوادم MySQL المرتبطة بالخادم ProxySQL، وذلك بتنفيذ الأمر التالي:

ProxySQLAdmin> LOAD MYSQL VARIABLES TO RUNTIME;

Output
+--------------+-------------+--------+
| hostgroup_id | hostname    | status |
+--------------+-------------+--------+
| 2            | 203.0.113.1 | ONLINE |
| 2            | 203.0.113.2 | ONLINE |
| 2            | 203.0.113.3 | ONLINE |
| 3            | 203.0.113.1 | ONLINE |
| 3            | 203.0.113.2 | ONLINE |
| 3            | 203.0.113.3 | ONLINE |
+--------------+-------------+--------+
6 rows in set (0.01 sec)

نلاحظ من الجدول أنه تم عرض جميع الخوادم في كل من المجموعة المضيفة ذات المعرِّف 2 والمجموعة المضيفة ذات المعرِّف 3، وهذا يدلّ على أن جميع الخوادم هي عُقد كتابة وقراءة في نفس الوقت. كما نلاحظ أن جميع الخوادم بحالة ONLINE أي أنها جميعها بحالة عمل.

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

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

يلعب ProxySQL دور موازن تحميل، حيث يقوم المستخدمون بالاتصال بالخادم ProxySQL والذي يقوم بدوره بتمرير هذا الاتصال إلى عُقدة MySQL المختارة. هذا ويتصل ProxySQL بكل عُقدة عن طريق متطلبات الولوج التي استخدمها المستخدم للدخول إلى ProxySQL.

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

ننتقل إلى موجه أوامر MySQL، ثم نُنشئ مستخدم جديد اسمه playgrounduser وكلمة سره playgrounduser، وذلك بتنفيذ الأمر التالي:

(member1)mysql> CREATE USER 'playgrounduser'@'%' IDENTIFIED BY 'playgroundpassword';

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

(member1)mysql> GRANT ALL PRIVILEGES on playground.* to 'playgrounduser'@'%';

ثم نُطبِّق التعديلات، وننهي موجه أوامر MySQL كما يلي:

(member1)mysql> FLUSH PRIVILEGES;
(member1)mysql> EXIT;

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

$ mysql -u playgrounduser -p

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

(member1)mysql> SHOW TABLES FROM playground;

Output
+----------------------+
| Tables_in_playground |
+----------------------+
| equipment            |
+----------------------+
1 row in set (0.00 sec)

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

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

نحتاج الآن لإنشاء مستخدم موافق في خادم ProxySQL.

الخطوة السابعة- إنشاء مستخدم ProxySQL

إن آخر خطوة في الإعدادات هو سماح الاتصال بالخادم ProxySQL باستخدام حساب المستخدم playgrounduser، ومرور هذا الاتصال عبره إلى عُقد MySQL.

لتحقيق ذلك نحتاج لإعداد المتغيرات الموجودة في الجدول mysql_users والتي تحتفظ بمعلومات حساب المستخدم. وذلك بإدخال اسم المستخدم (في حالتنا playgrounduser) وكلمة مروره ومعرِّف المجموعة المضيفة الافتراضية (في حالتنا المعرّف 2 الموافق لمجموعة الكتابة) في قاعدة بيانات الإعدادات، من خلال موجه أوامر الواجهة التخاطبية لإدارة ProxySQL:

ProxySQLAdmin> INSERT INTO mysql_users(username، password، default_hostgroup) VALUES ('playgrounduser'، 'playgroundpassword'، 2);

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

ProxySQLAdmin> LOAD MYSQL VARIABLES TO RUNTIME;
ProxySQLAdmin> SAVE MYSQL VARIABLES TO DISK;

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

$ ssh sammy@your_proxysql_server_ip_1

يتنصّت ProxySQL على المنفذ 6033 للاتصالات القادمة من العملاء، لذا سنحاول الاتصال بقاعدة البيانات الحقيقية (ليس بالواجهة الإدارية) باستخدام اسم المستخدم playgrounduser وكلمة مرورهplaygroundpassword (التي سيطلبها موجه الأوامر) والمنفذ 6033:

$ mysql -u playgrounduser -p -h 127.0.0.1 -P 6033 --prompt='ProxySQLClient> '

حيث تم استبدال الموجه الافتراضي بالموجه ProxySQLClient>‎ وذلك من أجل تمييزه عن موجه الواجهة التخاطبية لإدارة ProxySQL، كونه سنستخدم كلاهما في الإعدادات النهائية.

وعند نجاح عملية الاتصال سيظهر موجه الأوامر ProxySQLClient>‎ للدلالة على أن الخادم ProxySQL قد قبل حساب المستخدم playgrounduser:

ProxySQL client prompt
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 31
Server version: 5.5.30 (ProxySQL)

Copyright (c) 2000، 2017، 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.

ProxySQLClient>

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

ProxySQLClient> SELECT @@hostname;
Output
+------------+
| @@hostname |
+------------+
| member1    |
+------------+
1 row in set (0.00 sec)

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

وهكذا تم الانتهاء من الإعدادات التي تسمح بعمل ProxySQL كموازن تحميل للاتصالات بين عُقد MySQL.

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

الخطوة الثامنة- التحقق من إعدادات ProxySQL

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

سنقوم بتنفيذ تعبير القراءة SELECT في موجه أوامر عميل ProxySQL للتحقق من قدرتنا على قراءة البيانات من قاعدة البيانات playground، وذلك كما يلي:

ProxySQLClient> SELECT * FROM playground.equipment;
Output
+----+--------+-------+--------+
| id | type   | quant | color  |
+----+--------+-------+--------+
|  3 | slide  |     2 | blue   |
| 10 | swing  |    10 | yellow |
| 17 | seesaw |     3 | green  |
+----+--------+-------+--------+
3 rows in set (0.00 sec)

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

والآن سنحاول الكتابة في قاعدة البيانات، وذلك بتنفيذ تعبير الكتابة INSERT لكتابة بيانات جديدة في الجدول equipment الموجود ضمن قاعدة البيانات playground، وذلك كما يلي:

ProxySQLClient> INSERT INTO playground.equipment (type، quant، color) VALUES ("drill"، 5، "red");

وللتحقق من أنه تم كتابة البيانات السابقة في قاعدة البيانات playground، نعيد تنفيذ تعبير القراءة SELECT السابق كما يلي:

ProxySQLClient> SELECT * FROM playground.equipment;

Output
+----+--------+-------+--------+
| id | type   | quant | color  |
+----+--------+-------+--------+
|  3 | slide  |     2 | blue   |
| 10 | swing  |    10 | yellow |
| 17 | seesaw |     3 | green  |
| 24 | drill  |     5 | red    |
+----+--------+-------+--------+
4 rows in set (0.00 sec)

نلاحظ من الخرج ظهور مكون جديد في الجدول equipment الموجود في قاعدة البيانات، وهذا يدل على نجاح عملية الكتابة في قاعدة بيانات MySQL عن طريق الخادم ProxySQL.

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

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

$ systemctl stop mysql

بعد توقف أحد خوادم MySQL، سنحاول ثانيةً الاستعلام عن البيانات الموجودة في الجدول equipment من موجه أوامر عميل ProxySQL كما يلي:

ProxySQLClient> SELECT * FROM playground.equipment;

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

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

ProxySQLAdmin> SELECT hostgroup_id، hostname، status FROM runtime_mysql_servers;

Output
+--------------+-------------+---------+
| hostgroup_id | hostname    | status  |
+--------------+-------------+---------+
| 1            | 203.0.113.1 | SHUNNED |
| 2            | 203.0.113.2 | ONLINE  |
| 2            | 203.0.113.3 | ONLINE  |
| 3            | 203.0.113.2 | ONLINE  |
| 3            | 203.0.113.3 | ONLINE  |
+--------------+-------------+---------+
6 rows in set (0.01 sec)

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

سيقوم ProxySQL بمراقبة حالة العُقدة المتوقفة عن العمل بشكل مستمر، ويعيدها للحالة online في حال عادت للعمل، أو يعتبرها مفصولة offline في حال تجاوزت زمن الانتظار المحدد في الخطوة الخامسة (في المتغير max_transactions_behind).

سنقوم الآن باختبار مراقبة ProxySQL لحالة عُقد MySQL، وذلك بإعادة تشغيل خادم MySQL المتوقّف، وبالتالي إعادة العُقدة المتوقّفة إلى العمل ثانيةً:

$ systemctl start mysql

ثم ننتظر قليلًا ونعيد الاستعلام عن الجدول runtime_mysql_servers من موجه أوامر الواجهة التخاطبية لإدارة ProxySQL:

ProxySQLAdmin> SELECT hostgroup_id، hostname، status FROM runtime_mysql_servers;

Output
+--------------+-------------+--------+
| hostgroup_id | hostname    | status |
+--------------+-------------+--------+
| 2            | 203.0.113.1 | ONLINE |
| 2            | 203.0.113.2 | ONLINE |
| 2            | 203.0.113.3 | ONLINE |
| 3            | 203.0.113.1 | ONLINE |
| 3            | 203.0.113.2 | ONLINE |
| 3            | 203.0.113.3 | ONLINE |
+--------------+-------------+--------+
6 rows in set (0.01 sec)

نلاحظ من الخرج أن ProxySQL لاحظ فورًا عودة العُقدة للعمل، وقام بإعادة حالتها إلى ONLINE. يمكنك إعادة هذا الاختبار على عُقدة أخرى (أو اثنين منها)، والتحقق من أن وجود عُقدة واحدة على الأقل بحالة عمل، كافي لاستخدام قاعدة البيانات بحرية في عمليتي القراءة والكتابة.

ملخص

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

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

ترجمة المقال بتصرف للمقال How to Use ProxySQL as a Load Balancer for MySQL on Ubuntu 16.04 لصاحبته Mateusz Papiernik