ProxySQL Admin Support for Multiple Clusters

ProxySQL Admin

ProxySQL AdminIn 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.

+--------------------+----------+
| Variable_name      | Value    |
+--------------------+----------+
| wsrep_cluster_name | cluster1 |
+--------------------+----------+
+--------------------+----------+
| Variable_name      | Value    |
+--------------------+----------+
| wsrep_cluster_name | cluster2 |
+--------------------+----------+

Sample configuration of /etc/proxysql-admin.cnf for cluster1:

# 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

# 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:

[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

[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:

[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!

The post ProxySQL Admin Support for Multiple Clusters appeared first on Percona Database Performance Blog.

关注dbDao.com的新浪微博

扫码加入微信Oracle小密圈,了解Oracle最新技术下载分享资源

TEL/電話+86 13764045638
Email service@parnassusdata.com
QQ 47079569