ProxySQL Admin (proxysql-admin) is a powerful tool for configuring Percona XtraDB Cluster nodes into ProxySQL. The proxysql-admin tool comes with the ProxySQL package from Percona apt/yum repositories.
ProxySQL 1.3.2-1 is now available from the Percona repositories. This release is based on ProxySQL v1.3.2a and introduces the following new changes: proxysql-admin_v1.3.2a.md.
Installing on Red Hat or CentOS
If you are running an RPM-based Linux distribution, use the yum package manager to install ProxySQL and proxysql-admin from the official Percona software repository.
First, if your system does not already have the Percona’s yum repository configured, please run the following command:
1 | $ sudo yum install http://www.percona.com/downloads/percona-release/redhat/0.1-4/percona-release-0.1-4.noarch.rpm |
Next, install the proxysql/proxysql-admin package:
1 | $ sudo yum install proxysql |
Installing on Debian or Ubuntu
If you are running a DEB-based Linux distribution, use the apt package manager to install ProxySQL and proxysql-admin from the official Percona software repository.
First, if your system does not already have the Percona’s apt repository configured, please fetch the repository package:
1 | $ wget https://repo.percona.com/apt/percona-release_0.1-4.$(lsb_release -sc)_all.deb |
Next, install the repository package:
1 | $ sudo dpkg -i percona-release_0.1-4.$(lsb_release -sc)_all.deb |
Then update the local apt cache:
1 | $ sudo apt-get update |
Finally, install the proxysql/proxysql-admin package:
1 | $ sudo apt-get install proxysql |
Pre-Requisites
- ProxySQL and Percona XtraDB Cluster should be up and running.
- For security purposes, please make sure to change the default user settings in the ProxySQL configuration file. It is recommended that you use –config-file to run the proxysql-admin script. Default –config-file location is /etc/proxysql-admin.cnf.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 | Usage: [ options ] Options: --config-file Read login credentials from a configuration file (overrides any login credentials specified on the command line) --quick-demo Setup a quick demo with no authentication --proxysql-username=user_name Username for connecting to the ProxySQL service --proxysql-password[=password] Password for connecting to the ProxySQL service --proxysql-port=port_num Port Nr. for connecting to the ProxySQL service --proxysql-hostname=host_name Hostname for connecting to the ProxySQL service --cluster-username=user_name Username for connecting to the Percona XtraDB Cluster node --cluster-password[=password] Password for connecting to the Percona XtraDB Cluster node --cluster-port=port_num Port Nr. for connecting to the Percona XtraDB Cluster node --cluster-hostname=host_name Hostname for connecting to the Percona XtraDB Cluster node --cluster-app-username=user_name Application username for connecting to the Percona XtraDB Cluster node --cluster-app-password[=password] Application password for connecting to the Percona XtraDB Cluster node --monitor-username=user_name Username for monitoring Percona XtraDB Cluster nodes through ProxySQL --monitor-password[=password] Password for monitoring Percona XtraDB Cluster nodes through ProxySQL --enable, -e Auto-configure Percona XtraDB Cluster nodes into ProxySQL --disable, -d Remove any Percona XtraDB Cluster configurations from ProxySQL --node-check-interval=3000 Interval for monitoring node checker script (in milliseconds) --mode=[loadbal|singlewrite] ProxySQL read/write configuration mode, currently supporting: 'loadbal' and 'singlewrite' (the default) modes --write-node=host_name:port Writer node to accept write statments. This option is supported only when using --mode=singlewrite --adduser Adds the Percona XtraDB Cluster application user to the ProxySQL database --version, -v Print version info |
This script will accept two different options to configure Percona XtraDB Cluster nodes:
1) −−enable
This option configures Percona XtraDB Cluster nodes into the ProxySQL database, and add two cluster monitoring scripts into the ProxySQL scheduler table for checking the cluster status.
_scheduler script info:
- proxysql_node_monitor: checks cluster node membership, and re-configures ProxySQL if cluster membership changes occur
- proxysql_galera_checker: checks desynced nodes, and temporarily deactivates them. It will also add two new users into the Percona XtraDB Cluster with the USAGE privilege. One monitors cluster nodes through ProxySQL, and the other connects to Cluster node via the ProxySQL console. Please make sure to use super user credentials from Percona XtraDB Cluster to setup the default users.
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 | $ sudo proxysql-admin --config-file=/etc/proxysql-admin.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'@'127.%' 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 proxysql_user Percona XtraDB Cluster application user 'proxysql_user'@'127.%' has been added with the USAGE privilege, please make sure to the grant appropriate privileges Adding the Percona XtraDB Cluster server nodes to ProxySQL You have not given the writer node info through the command line or in the config-file. Please enter the writer-node info (eg : 127.0.0.1:3306): 127.0.0.1:25000 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=proxysql_user --password=***** --host=127.0.0.1 --port=6033 --protocol=tcp $ mysql> select hostgroup_id,hostname,port,status,comment from mysql_servers; +--------------+-----------+-------+--------+---------+ | hostgroup_id | hostname | port | status | comment | +--------------+-----------+-------+--------+---------+ | 11 | 127.0.0.1 | 25400 | ONLINE | READ | | 10 | 127.0.0.1 | 25000 | ONLINE | WRITE | | 11 | 127.0.0.1 | 25100 | ONLINE | READ | | 11 | 127.0.0.1 | 25200 | ONLINE | READ | | 11 | 127.0.0.1 | 25300 | ONLINE | READ | +--------------+-----------+-------+--------+---------+ 5 rows in set (0.00 sec) mysql> |
2. −−disable
This option removes Percona XtraDB Cluster nodes from ProxySQL and stops the ProxySQL monitoring daemon.
1 2 3 | $ proxysql-admin --config-file=/etc/proxysql-admin.cnf --disable ProxySQL configuration removed! $ |
Extra options
i) −−mode
This option sets up read/write mode for Percona XtraDB Cluster nodes in the ProxySQL database, based on the hostgroup. For now, the only supported modes are loadbal and singlewrite. singlewrite is the default mode, and it accepts writes only on one single node (and this node can be provided either interactively or by using the –write-node to specify the hostname and the port number for the one single write node). All other remaining nodes will be read-only and accept only read statements. The mode loadbal, on the other hand, is a load balanced set of evenly weighted read/write nodes.
singlewrite mode setup:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | $ sudo grep "MODE" /etc/proxysql-admin.cnf export MODE="singlewrite" $ $ sudo proxysql-admin --config-file=/etc/proxysql-admin.cnf --write-node=127.0.0.1:25000 --enable ProxySQL read/write configuration mode is singlewrite [..] ProxySQL configuration completed! $ mysql> select hostgroup_id,hostname,port,status,comment from mysql_servers; +--------------+-----------+-------+--------+---------+ | hostgroup_id | hostname | port | status | comment | +--------------+-----------+-------+--------+---------+ | 11 | 127.0.0.1 | 25400 | ONLINE | READ | | 10 | 127.0.0.1 | 25000 | ONLINE | WRITE | | 11 | 127.0.0.1 | 25100 | ONLINE | READ | | 11 | 127.0.0.1 | 25200 | ONLINE | READ | | 11 | 127.0.0.1 | 25300 | ONLINE | READ | +--------------+-----------+-------+--------+---------+ 5 rows in set (0.00 sec) mysql> |
loadbal mode setup:
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 | $ sudo proxysql-admin --config-file=/etc/proxysql-admin.cnf --mode=loadbal --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 [..] 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=proxysql_user --password=***** --host=127.0.0.1 --port=6033 --protocol=tcp $ mysql> select hostgroup_id,hostname,port,status,comment from mysql_servers; +--------------+-----------+-------+--------+-----------+ | hostgroup_id | hostname | port | status | comment | +--------------+-----------+-------+--------+-----------+ | 10 | 127.0.0.1 | 25400 | ONLINE | READWRITE | | 10 | 127.0.0.1 | 25000 | ONLINE | READWRITE | | 10 | 127.0.0.1 | 25100 | ONLINE | READWRITE | | 10 | 127.0.0.1 | 25200 | ONLINE | READWRITE | | 10 | 127.0.0.1 | 25300 | ONLINE | READWRITE | +--------------+-----------+-------+--------+-----------+ 5 rows in set (0.01 sec) mysql> |
ii) −−node-check-interval
This option configures the interval for monitoring via the proxysql_galera_checker script (in milliseconds):
1 | $ proxysql-admin --config-file=/etc/proxysql-admin.cnf --node-check-interval=5000 --enable |
iii) −−adduser
This option aids with adding the Percona XtraDB Cluster application user to the ProxySQL database:
1 2 3 4 5 6 7 | $ proxysql-admin --config-file=/etc/proxysql-admin.cnf --adduser Adding Percona XtraDB Cluster application user to ProxySQL database Enter Percona XtraDB Cluster application user name: root Enter Percona XtraDB Cluster application user password: Added Percona XtraDB Cluster application user to ProxySQL database! $ |
iv) −−test-run
This option sets up a test/dummy proxysql configuration:
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 | $ sudo proxysql-admin --enable --quick-demo You have selected the dry test run mode. WARNING: This will create a test user (with all privileges) in the Percona XtraDB Cluster & ProxySQL installations. You may want to delete this user after you complete your testing! Would you like to proceed with '--quick-demo' [y/n] ? y Setting up proxysql test configuration! Do you want to use the default ProxySQL credentials (admin:admin:6032:127.0.0.1) [y/n] ? y Do you want to use the default Percona XtraDB Cluster credentials (root::3306:127.0.0.1) [y/n] ? n Enter the Percona XtraDB Cluster username (super user): root Enter the Percona XtraDB Cluster user password: Enter the Percona XtraDB Cluster port: 25100 Enter the Percona XtraDB Cluster hostname: localhost ProxySQL read/write configuration mode is singlewrite Configuring ProxySQL monitoring user.. User 'monitor'@'127.%' has been added with USAGE privilege Configuring the Percona XtraDB Cluster application user to connect through ProxySQL Percona XtraDB Cluster application user 'pxc_test_user'@'127.%' has been added with ALL privileges, this user is created for testing purposes 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=pxc_test_user --host=127.0.0.1 --port=6033 --protocol=tcp $ mysql> select hostgroup_id,hostname,port,status,comment from mysql_servers; +--------------+-----------+-------+--------+---------+ | hostgroup_id | hostname | port | status | comment | +--------------+-----------+-------+--------+---------+ | 11 | 127.0.0.1 | 25300 | ONLINE | READ | | 10 | 127.0.0.1 | 25000 | ONLINE | WRITE | | 11 | 127.0.0.1 | 25100 | ONLINE | READ | | 11 | 127.0.0.1 | 25200 | ONLINE | READ | +--------------+-----------+-------+--------+---------+ 4 rows in set (0.00 sec) mysql> |
We hope you enjoy ProxySQL Admin!
It seems confusing to reference proxysql-admin as a package since it’s a script inside the proxysql package from Percona repository.
Hi Tomislav,
We have updated the blog.
Thank you very much for the suggestion.
It would be great to have some better explanation of the admin config.
#proxysql-admin credentials
export PROXYSQL_USERNAME=”admin”
export PROXYSQL_PASSWORD=”admin”
export PROXYSQL_HOSTNAME=”localhost”
export PROXYSQL_PORT=”6032″
export CLUSTER_USERNAME=”admin”
export CLUSTER_PASSWORD=”admin”
export CLUSTER_HOSTNAME=”localhost”
export CLUSTER_PORT=”3306″
export MONITOR_USERNAME=”monitor”
export MONITOR_PASSWORD=”monit0r”
export CLUSTER_APP_USERNAME=”proxysql_user”
export CLUSTER_APP_PASSWORD=”passw0rd”
export WRITE_HOSTGROUP_ID=”10″
export READ_HOSTGROUP_ID=”11″
export MODE=”singlewrite”
I’ve been having some difficulty connecting to my Percona cluster because it’s not clear the different between the “CLUSTER_APP” account and “CLUSTER_USERNAME”. I see this error in the log file:
2017-02-02T20:17:21.624590Z 50765 [Note] Aborted connection 50765 to db: ‘unconnected’ user: ‘monitor’ host: ‘192.168.1.201’ (Got an error reading communication packets)
Any help or a push in the right direction would be a big help.
thanks!
Hi Ken,
We have updated blog with script usage information.
CLUSTER_USERNAME variable is used for connecting to the Percona XtraDB Cluster node for ProxySQL configuration. It is important that this user is present in the Cluster and that the super user account is being used to setup the configuration.
CLUSTER_APP_USERNAME is a ProxySQL application username for connecting to the Percona XtraDB Cluster node. proxysql-admin tool will create this user in Percona XtraDB Cluster. If this user is already present proxysql-admin tool will skip the user creation.
Hi!
I have a problem with theparoxysql-admin tool… I have my percona cluster in a VPC in EC2 and my Proxysql in another VPC.
When I run the admin it configures my sqlservers private ip address so my proxysql cannot reach them from the other VPC.
Is there any way of fixxing this? I have tried updating the mysql_server config table table with the public ip addresses but it just adds the private addresss again and ignores the public ones.
Thanks.
Hi Angel,
proxysql-admin script will configure PXC cluster based on wsrep_incoming_addresses status variable. I think should configure PXC with public IPs. Even if you manually update mysql_server table with public IPs, proxysql-monitoring script will update the IPs from wsrep_incoming_addresses status variable.
Thanks
Ramesh
I tried to use Proxy-admin package on CentOS and it keep on throwing error that “The mysql client was not found, please install the mysql client package.”. I ensured that mysql client exists on the machine. I am trying to sync users between PXC and ProxySQL.