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).
Pre-requisites
- Cluster name (wsrep_cluster_name) should be unique.
- proxysql-admin.cnf configuration differences:
- ProxySQL READ/WRITE hostgroup should be different for each cluster.
- Application user should be different for each cluster.
- Host priority feature support only one cluster at a time.
Configuring /etc/proxysql-admin.cnf
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 2 3 4 5 6 7 8 9 10 11 |
+--------------------+----------+ | Variable_name | Value | +--------------------+----------+ | wsrep_cluster_name | cluster1 | +--------------------+----------+ +--------------------+----------+ | Variable_name | Value | +--------------------+----------+ | wsrep_cluster_name | cluster2 | +--------------------+----------+ |
Sample configuration of /etc/proxysql-admin.cnf for cluster1:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 |
# proxysql admin interface credentials. export PROXYSQL_DATADIR='/var/lib/proxysql' export PROXYSQL_USERNAME='admin' export PROXYSQL_PASSWORD='admin' export PROXYSQL_HOSTNAME='localhost' export PROXYSQL_PORT='6032' # PXC admin credentials for connecting to pxc-cluster-node. export CLUSTER_USERNAME='root' export CLUSTER_PASSWORD='sekret' export CLUSTER_HOSTNAME='10.0.3.41' export CLUSTER_PORT='3306' # proxysql monitoring user. proxysql admin script will create this user in pxc to monitor pxc-nodes. export MONITOR_USERNAME='monitor' export MONITOR_PASSWORD='monit0r' # Application user to connect to pxc-node through proxysql export CLUSTER_APP_USERNAME='cluster1_user' export CLUSTER_APP_PASSWORD='c1_passw0rd' # ProxySQL read/write hostgroup export WRITE_HOSTGROUP_ID='10' export READ_HOSTGROUP_ID='11' # ProxySQL read/write configuration mode. export MODE="singlewrite" # ProxySQL Cluster Node Priority File export HOST_PRIORITY_FILE=$PROXYSQL_DATADIR/host_priority.conf |
Sample configuration of /etc/proxysql-admin.cnf for cluster2
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 |
# proxysql admin interface credentials. export PROXYSQL_DATADIR='/var/lib/proxysql' export PROXYSQL_USERNAME='admin' export PROXYSQL_PASSWORD='admin' export PROXYSQL_HOSTNAME='localhost' export PROXYSQL_PORT='6032' # PXC admin credentials for connecting to pxc-cluster-node. export CLUSTER_USERNAME='root' export CLUSTER_PASSWORD='sekret' export CLUSTER_HOSTNAME='10.0.3.173' export CLUSTER_PORT='3306' # proxysql monitoring user. proxysql admin script will create this user in pxc to monitor pxc-nodes. export MONITOR_USERNAME='monitor' export MONITOR_PASSWORD='monit0r' # Application user to connect to pxc-node through proxysql export CLUSTER_APP_USERNAME='cluster2_user' export CLUSTER_APP_PASSWORD='c2_passw0rd' # ProxySQL read/write hostgroup export WRITE_HOSTGROUP_ID='20' export READ_HOSTGROUP_ID='21' # ProxySQL read/write configuration mode. export MODE="loadbal" # ProxySQL Cluster Node Priority File export HOST_PRIORITY_FILE=$PROXYSQL_DATADIR/host_priority.conf |
Setting up Percona XtraDB Cluster nodes in ProxySQL
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 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 |
[root@proxysql_multi-pxc ~]# proxysql-admin --config=/etc/proxysql-admin_cluster1.cnf --enable This script will assist with configuring ProxySQL (currently only Percona XtraDB cluster in combination with ProxySQL is supported) ProxySQL read/write configuration mode is singlewrite Configuring ProxySQL monitoring user.. ProxySQL monitor username as per command line/config-file is monitor User 'monitor'@'10.%' has been added with USAGE privilege Configuring the Percona XtraDB Cluster application user to connect through ProxySQL Percona XtraDB Cluster application username as per command line/config-file is cluster1_user Percona XtraDB Cluster application user 'cluster1_user'@'10.%' has been added with the USAGE privilege, please make sure to the grant appropriate privileges Adding the Percona XtraDB Cluster server nodes to ProxySQL Configuring singlewrite mode with the following nodes designated as priority order: Write node info +-----------+--------------+------+---------+---------+ | hostname | hostgroup_id | port | weight | comment | +-----------+--------------+------+---------+---------+ | 10.0.3.41 | 10 | 3306 | 1000000 | WRITE | +-----------+--------------+------+---------+---------+ ProxySQL configuration completed! ProxySQL has been successfully configured to use with Percona XtraDB Cluster You can use the following login credentials to connect your application through ProxySQL mysql --user=cluster1_user -p --host=localhost --port=6033 --protocol=tcp |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 |
[root@proxysql_multi-pxc ~]# proxysql-admin --config=/etc/proxysql-admin_cluster2.cnf --enable This script will assist with configuring ProxySQL (currently only Percona XtraDB cluster in combination with ProxySQL is supported) ProxySQL read/write configuration mode is loadbal Host priority file (/var/lib/proxysql/host_priority.conf) is already present. Would you like to replace with the new file [y/n] ? n Host priority file is not deleted. Please make sure you have properly configured /var/lib/proxysql/host_priority.conf Configuring ProxySQL monitoring user.. ProxySQL monitor username as per command line/config-file is monitor User 'monitor'@'10.%' has been added with USAGE privilege Configuring the Percona XtraDB Cluster application user to connect through ProxySQL Percona XtraDB Cluster application username as per command line/config-file is cluster2_user Percona XtraDB Cluster application user 'cluster2_user'@'10.%' has been added with the USAGE privilege, please make sure to the grant appropriate privileges Adding the Percona XtraDB Cluster server nodes to ProxySQL ProxySQL configuration completed! ProxySQL has been successfully configured to use with Percona XtraDB Cluster You can use the following login credentials to connect your application through ProxySQL mysql --user=cluster2_user -p --host=localhost --port=6033 --protocol=tcp |
Inspect ProxySQL tables
Login to ProxySQL to confirm that the setup is correct:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 |
[root@proxysql_multi-pxc ~]# mysql -uadmin -p -P6032 -h127.0.0.1 Enter password: Welcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is 33893 Server version: 5.5.30 (ProxySQL Admin Module) Copyright (c) 2009-2018 Percona LLC and/or its affiliates Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or 'h' for help. Type 'c' to clear the current input statement. mysql> select * from mysql_users; +---------------+-------------------------------------------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+ | username | password | active | use_ssl | default_hostgroup | default_schema | schema_locked | transaction_persistent | fast_forward | backend | frontend | max_connections | +---------------+-------------------------------------------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+ | cluster1_user | *448C417D62616B779E789F3BD72AA3DE9C319EA3 | 1 | 0 | 10 | | 0 | 1 | 0 | 1 | 1 | 10000 | | cluster2_user | *AB1E96267D16A9F26A201282F9ED80B50244B770 | 1 | 0 | 20 | | 0 | 1 | 0 | 1 | 1 | 10000 | +---------------+-------------------------------------------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+ 2 rows in set (0.00 sec) mysql> select * from mysql_servers; +--------------+------------+------+--------+---------+-------------+-----------------+---------------------+---------+----------------+-----------+ | hostgroup_id | hostname | port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment | +--------------+------------+------+--------+---------+-------------+-----------------+---------------------+---------+----------------+-----------+ | 11 | 10.0.3.81 | 3306 | ONLINE | 1000 | 0 | 1000 | 0 | 0 | 0 | READ | | 10 | 10.0.3.41 | 3306 | ONLINE | 1000000 | 0 | 1000 | 0 | 0 | 0 | WRITE | | 11 | 10.0.3.232 | 3306 | ONLINE | 1000 | 0 | 1000 | 0 | 0 | 0 | READ | | 20 | 10.0.3.173 | 3306 | ONLINE | 1000 | 0 | 1000 | 0 | 0 | 0 | READWRITE | | 20 | 10.0.3.78 | 3306 | ONLINE | 1000 | 0 | 1000 | 0 | 0 | 0 | READWRITE | | 20 | 10.0.3.141 | 3306 | ONLINE | 1000 | 0 | 1000 | 0 | 0 | 0 | READWRITE | +--------------+------------+------+--------+---------+-------------+-----------------+---------------------+---------+----------------+-----------+ 6 rows in set (0.00 sec) mysql> select * from scheduler; +----+--------+-------------+----------------------------------+------+------+------+------+------------------------------------------------------+----------+ | id | active | interval_ms | filename | arg1 | arg2 | arg3 | arg4 | arg5 | comment | +----+--------+-------------+----------------------------------+------+------+------+------+------------------------------------------------------+----------+ | 6 | 1 | 3000 | /usr/bin/proxysql_galera_checker | 10 | 11 | 1 | 1 | /var/lib/proxysql/cluster1_proxysql_galera_check.log | cluster1 | | 7 | 1 | 3000 | /usr/bin/proxysql_galera_checker | 20 | 20 | 0 | 1 | /var/lib/proxysql/cluster2_proxysql_galera_check.log | cluster2 | +----+--------+-------------+----------------------------------+------+------+------+------+------------------------------------------------------+----------+ 2 rows in set (0.00 sec) mysql> select * from mysql_query_rules; +---------+--------+---------------+------------+--------+-------------+------------+------------+--------+---------------------+---------------+----------------------+--------------+---------+-----------------+-----------------------+-----------+-----------+---------+---------+-------+-------------------+----------------+------------------+-----------+--------+-------------+-----------+-----+-------+---------+ | 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 | +---------+--------+---------------+------------+--------+-------------+------------+------------+--------+---------------------+---------------+----------------------+--------------+---------+-----------------+-----------------------+-----------+-----------+---------+---------+-------+-------------------+----------------+------------------+-----------+--------+-------------+-----------+-----+-------+---------+ | 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 | | 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 | +---------+--------+---------------+------------+--------+-------------+------------+------------+--------+---------------------+---------------+----------------------+--------------+---------+-----------------+-----------------------+-----------+-----------+---------+---------+-------+-------------------+----------------+------------------+-----------+--------+-------------+-----------+-----+-------+---------+ 2 rows in set (0.00 sec) mysql> exit Bye |
It’s as easy as that! We hope you continue to enjoy using ProxySQL Admin!
