In this blog post, we demonstrate a new feature in ProxySQL Admin: support for multiple clusters.
In a previous blog post, Ramesh and Roel introduced a new tool that helps configured Percona XtraDB Cluster nodes into ProxySQL. However, at that time it only worked for a single cluster per ProxySQL Admin configuration. Starting from ProxySQL 1.4.6, which comes with an improved ProxySQL Admin tool (proxysql-admin), our tool now supports configuring multiple Percona XtraDB Cluster clusters with ease (PSQLADM-32).
As mentioned above, the CLUSTER_APP_USERNAME and the WRITE/READ_HOSTGROUP should be different for each cluster. Wsrep_cluster_name should also be unique for each cluster.
|
1 |
+--------------------+----------+<br>| Variable_name | Value |<br>+--------------------+----------+<br>| wsrep_cluster_name | cluster1 |<br>+--------------------+----------+<br><br>+--------------------+----------+<br>| Variable_name | Value |<br>+--------------------+----------+<br>| wsrep_cluster_name | cluster2 |<br>+--------------------+----------+ |
Sample configuration of /etc/proxysql-admin.cnf for cluster1:
|
1 |
# proxysql admin interface credentials.<br>export PROXYSQL_DATADIR='/var/lib/proxysql'<br>export PROXYSQL_USERNAME='admin'<br>export PROXYSQL_PASSWORD='admin'<br>export PROXYSQL_HOSTNAME='localhost'<br>export PROXYSQL_PORT='6032'<br><br># PXC admin credentials for connecting to pxc-cluster-node.<br>export CLUSTER_USERNAME='root'<br>export CLUSTER_PASSWORD='sekret'<br>export CLUSTER_HOSTNAME='10.0.3.41'<br>export CLUSTER_PORT='3306'<br><br># proxysql monitoring user. proxysql admin script will create this user in pxc to monitor pxc-nodes.<br>export MONITOR_USERNAME='monitor'<br>export MONITOR_PASSWORD='monit0r'<br><br># Application user to connect to pxc-node through proxysql<br>export CLUSTER_APP_USERNAME='cluster1_user'<br>export CLUSTER_APP_PASSWORD='c1_passw0rd'<br><br># ProxySQL read/write hostgroup<br>export WRITE_HOSTGROUP_ID='10'<br>export READ_HOSTGROUP_ID='11'<br><br># ProxySQL read/write configuration mode.<br>export MODE="singlewrite"<br><br># ProxySQL Cluster Node Priority File<br>export HOST_PRIORITY_FILE=$PROXYSQL_DATADIR/host_priority.conf |
Sample configuration of /etc/proxysql-admin.cnf for cluster2
|
1 |
# proxysql admin interface credentials.<br>export PROXYSQL_DATADIR='/var/lib/proxysql'<br>export PROXYSQL_USERNAME='admin'<br>export PROXYSQL_PASSWORD='admin'<br>export PROXYSQL_HOSTNAME='localhost'<br>export PROXYSQL_PORT='6032'<br><br># PXC admin credentials for connecting to pxc-cluster-node.<br>export CLUSTER_USERNAME='root'<br>export CLUSTER_PASSWORD='sekret'<br>export CLUSTER_HOSTNAME='10.0.3.173'<br>export CLUSTER_PORT='3306'<br><br># proxysql monitoring user. proxysql admin script will create this user in pxc to monitor pxc-nodes.<br>export MONITOR_USERNAME='monitor'<br>export MONITOR_PASSWORD='monit0r'<br><br># Application user to connect to pxc-node through proxysql<br>export CLUSTER_APP_USERNAME='cluster2_user'<br>export CLUSTER_APP_PASSWORD='c2_passw0rd'<br><br># ProxySQL read/write hostgroup<br>export WRITE_HOSTGROUP_ID='20'<br>export READ_HOSTGROUP_ID='21'<br><br># ProxySQL read/write configuration mode.<br>export MODE="loadbal"<br><br># ProxySQL Cluster Node Priority File<br>export HOST_PRIORITY_FILE=$PROXYSQL_DATADIR/host_priority.conf |
I would add that you have the option to use a single proxysql-admin.cnf file, and just edit the file where changes are appropriate. You could also use two different files to configure ProxySQL. In my example, I used two files with the contents as seen above:
|
1 |
[root@proxysql_multi-pxc ~]# proxysql-admin --config=/etc/proxysql-admin_cluster1.cnf --enable<br><br>This script will assist with configuring ProxySQL (currently only Percona XtraDB cluster in combination with ProxySQL is supported)<br><br>ProxySQL read/write configuration mode is singlewrite<br><br>Configuring ProxySQL monitoring user..<br>ProxySQL monitor username as per command line/config-file is monitor<br><br>User 'monitor'@'10.%' has been added with USAGE privilege<br><br>Configuring the Percona XtraDB Cluster application user to connect through ProxySQL<br>Percona XtraDB Cluster application username as per command line/config-file is cluster1_user<br><br>Percona XtraDB Cluster application user 'cluster1_user'@'10.%' has been added with the USAGE privilege, please make sure to the grant appropriate privileges<br><br>Adding the Percona XtraDB Cluster server nodes to ProxySQL<br><br>Configuring singlewrite mode with the following nodes designated as priority order:<br><br>Write node info<br>+-----------+--------------+------+---------+---------+<br>| hostname | hostgroup_id | port | weight | comment |<br>+-----------+--------------+------+---------+---------+<br>| 10.0.3.41 | 10 | 3306 | 1000000 | WRITE |<br>+-----------+--------------+------+---------+---------+<br><br>ProxySQL configuration completed!<br><br>ProxySQL has been successfully configured to use with Percona XtraDB Cluster<br><br>You can use the following login credentials to connect your application through ProxySQL<br><br>mysql --user=cluster1_user -p --host=localhost --port=6033 --protocol=tcp |
|
1 |
[root@proxysql_multi-pxc ~]# proxysql-admin --config=/etc/proxysql-admin_cluster2.cnf --enable<br><br>This script will assist with configuring ProxySQL (currently only Percona XtraDB cluster in combination with ProxySQL is supported)<br><br>ProxySQL read/write configuration mode is loadbal<br><br>Host priority file (/var/lib/proxysql/host_priority.conf) is already present. Would you like to replace with the new file [y/n] ? n<br><br>Host priority file is not deleted. Please make sure you have properly configured /var/lib/proxysql/host_priority.conf<br><br>Configuring ProxySQL monitoring user..<br>ProxySQL monitor username as per command line/config-file is monitor<br><br>User 'monitor'@'10.%' has been added with USAGE privilege<br><br>Configuring the Percona XtraDB Cluster application user to connect through ProxySQL<br>Percona XtraDB Cluster application username as per command line/config-file is cluster2_user<br><br>Percona XtraDB Cluster application user 'cluster2_user'@'10.%' has been added with the USAGE privilege, please make sure to the grant appropriate privileges<br><br>Adding the Percona XtraDB Cluster server nodes to ProxySQL<br><br>ProxySQL configuration completed!<br><br>ProxySQL has been successfully configured to use with Percona XtraDB Cluster<br><br>You can use the following login credentials to connect your application through ProxySQL<br><br>mysql --user=cluster2_user -p --host=localhost --port=6033 --protocol=tcp |
Login to ProxySQL to confirm that the setup is correct:
|
1 |
[root@proxysql_multi-pxc ~]# mysql -uadmin -p -P6032 -h127.0.0.1<br>Enter password:<br>Welcome to the MySQL monitor. Commands end with ; or g.<br>Your MySQL connection id is 33893<br>Server version: 5.5.30 (ProxySQL Admin Module)<br><br>Copyright (c) 2009-2018 Percona LLC and/or its affiliates<br>Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.<br><br>Oracle is a registered trademark of Oracle Corporation and/or its<br>affiliates. Other names may be trademarks of their respective<br>owners.<br><br>Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.<br><br>mysql> select * from mysql_users;<br>+---------------+-------------------------------------------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+<br>| username | password | active | use_ssl | default_hostgroup | default_schema | schema_locked | transaction_persistent | fast_forward | backend | frontend | max_connections |<br>+---------------+-------------------------------------------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+<br>| cluster1_user | *448C417D62616B779E789F3BD72AA3DE9C319EA3 | 1 | 0 | 10 | | 0 | 1 | 0 | 1 | 1 | 10000 |<br>| cluster2_user | *AB1E96267D16A9F26A201282F9ED80B50244B770 | 1 | 0 | 20 | | 0 | 1 | 0 | 1 | 1 | 10000 |<br>+---------------+-------------------------------------------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+<br>2 rows in set (0.00 sec)<br><br>mysql> select * from mysql_servers;<br>+--------------+------------+------+--------+---------+-------------+-----------------+---------------------+---------+----------------+-----------+<br>| hostgroup_id | hostname | port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |<br>+--------------+------------+------+--------+---------+-------------+-----------------+---------------------+---------+----------------+-----------+<br>| 11 | 10.0.3.81 | 3306 | ONLINE | 1000 | 0 | 1000 | 0 | 0 | 0 | READ |<br>| 10 | 10.0.3.41 | 3306 | ONLINE | 1000000 | 0 | 1000 | 0 | 0 | 0 | WRITE |<br>| 11 | 10.0.3.232 | 3306 | ONLINE | 1000 | 0 | 1000 | 0 | 0 | 0 | READ |<br>| 20 | 10.0.3.173 | 3306 | ONLINE | 1000 | 0 | 1000 | 0 | 0 | 0 | READWRITE |<br>| 20 | 10.0.3.78 | 3306 | ONLINE | 1000 | 0 | 1000 | 0 | 0 | 0 | READWRITE |<br>| 20 | 10.0.3.141 | 3306 | ONLINE | 1000 | 0 | 1000 | 0 | 0 | 0 | READWRITE |<br>+--------------+------------+------+--------+---------+-------------+-----------------+---------------------+---------+----------------+-----------+<br>6 rows in set (0.00 sec)<br><br>mysql> select * from scheduler;<br>+----+--------+-------------+----------------------------------+------+------+------+------+------------------------------------------------------+----------+<br>| id | active | interval_ms | filename | arg1 | arg2 | arg3 | arg4 | arg5 | comment |<br>+----+--------+-------------+----------------------------------+------+------+------+------+------------------------------------------------------+----------+<br>| 6 | 1 | 3000 | /usr/bin/proxysql_galera_checker | 10 | 11 | 1 | 1 | /var/lib/proxysql/cluster1_proxysql_galera_check.log | cluster1 |<br>| 7 | 1 | 3000 | /usr/bin/proxysql_galera_checker | 20 | 20 | 0 | 1 | /var/lib/proxysql/cluster2_proxysql_galera_check.log | cluster2 |<br>+----+--------+-------------+----------------------------------+------+------+------+------+------------------------------------------------------+----------+<br>2 rows in set (0.00 sec)<br><br>mysql> select * from mysql_query_rules;<br>+---------+--------+---------------+------------+--------+-------------+------------+------------+--------+---------------------+---------------+----------------------+--------------+---------+-----------------+-----------------------+-----------+-----------+---------+---------+-------+-------------------+----------------+------------------+-----------+--------+-------------+-----------+-----+-------+---------+<br>| rule_id | active | username | schemaname | flagIN | client_addr | proxy_addr | proxy_port | digest | match_digest | match_pattern | negate_match_pattern | re_modifiers | flagOUT | replace_pattern | destination_hostgroup | cache_ttl | reconnect | timeout | retries | delay | next_query_flagIN | mirror_flagOUT | mirror_hostgroup | error_msg | OK_msg | sticky_conn | multiplex | log | apply | comment |<br>+---------+--------+---------------+------------+--------+-------------+------------+------------+--------+---------------------+---------------+----------------------+--------------+---------+-----------------+-----------------------+-----------+-----------+---------+---------+-------+-------------------+----------------+------------------+-----------+--------+-------------+-----------+-----+-------+---------+<br>| 7 | 1 | cluster1_user | NULL | 0 | NULL | NULL | NULL | NULL | ^SELECT.*FOR UPDATE | NULL | 0 | CASELESS | NULL | NULL | 10 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 1 | NULL |<br>| 8 | 1 | cluster1_user | NULL | 0 | NULL | NULL | NULL | NULL | ^SELECT | NULL | 0 | CASELESS | NULL | NULL | 11 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 1 | NULL |<br>+---------+--------+---------------+------------+--------+-------------+------------+------------+--------+---------------------+---------------+----------------------+--------------+---------+-----------------+-----------------------+-----------+-----------+---------+---------+-------+-------------------+----------------+------------------+-----------+--------+-------------+-----------+-----+-------+---------+<br>2 rows in set (0.00 sec)<br><br>mysql> exit<br>Bye |
It’s as easy as that! We hope you continue to enjoy using ProxySQL Admin!
Resources
RELATED POSTS
