Downloads

Blog

ProxySQL Admin Configuration

January 25, 2017

Author

Ramesh Sivaraman

Percona Software

ProxySQL

Share this Post:

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:

$ sudo yum install http://www.percona.com/downloads/percona-release/redhat/0.1-4/percona-release-0.1-4.noarch.rpm

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:

$ sudo yum install proxysql

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:

$ wget https://repo.percona.com/apt/percona-release_0.1-4.$(lsb_release -sc)_all.deb

1	$ wget https://repo.percona.com/apt/percona-release_0.1-4.$(lsb_release -sc)_all.deb

Next, install the repository package:

$ sudo dpkg -i percona-release_0.1-4.$(lsb_release -sc)_all.deb

1	$ sudo dpkg -i percona-release_0.1-4.$(lsb_release -sc)_all.deb

Then update the local apt cache:

$ sudo apt-get update

1	$ sudo apt-get update

Finally, install the proxysql/proxysql-admin package:

$ sudo apt-get install proxysql

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.

Usage: [ options ]<br>Options:<br>--config-file Read login credentials from a configuration file (overrides any login credentials specified on the command line)<br>--quick-demo Setup a quick demo with no authentication<br>--proxysql-username=user_name Username for connecting to the ProxySQL service<br>--proxysql-password[=password] Password for connecting to the ProxySQL service<br>--proxysql-port=port_num Port Nr. for connecting to the ProxySQL service<br>--proxysql-hostname=host_name Hostname for connecting to the ProxySQL service<br>--cluster-username=user_name Username for connecting to the Percona XtraDB Cluster node<br>--cluster-password[=password] Password for connecting to the Percona XtraDB Cluster node<br>--cluster-port=port_num Port Nr. for connecting to the Percona XtraDB Cluster node<br>--cluster-hostname=host_name Hostname for connecting to the Percona XtraDB Cluster node<br>--cluster-app-username=user_name Application username for connecting to the Percona XtraDB Cluster node<br>--cluster-app-password[=password] Application password for connecting to the Percona XtraDB Cluster node<br>--monitor-username=user_name Username for monitoring Percona XtraDB Cluster nodes through ProxySQL<br>--monitor-password[=password] Password for monitoring Percona XtraDB Cluster nodes through ProxySQL<br>--enable, -e Auto-configure Percona XtraDB Cluster nodes into ProxySQL<br>--disable, -d Remove any Percona XtraDB Cluster configurations from ProxySQL<br>--node-check-interval=3000 Interval for monitoring node checker script (in milliseconds)<br>--mode=[loadbal|singlewrite] ProxySQL read/write configuration mode, currently supporting: 'loadbal' and 'singlewrite' (the default) modes<br>--write-node=host_name:port Writer node to accept write statments. This option is supported only when using --mode=singlewrite<br>--adduser Adds the Percona XtraDB Cluster application user to the ProxySQL database<br>--version, -v Print version info

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.

Shell
$ sudo proxysql-admin --config-file=/etc/proxysql-admin.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'@'127.%' 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 proxysql_user<br><br>Percona XtraDB Cluster application user 'proxysql_user'@'127.%' 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>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<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=proxysql_user --password=***** --host=127.0.0.1 --port=6033 --protocol=tcp<br><br>$<br><br>mysql> select hostgroup_id,hostname,port,status,comment from mysql_servers;<br>+--------------+-----------+-------+--------+---------+<br>| hostgroup_id | hostname  | port  | status | comment |<br>+--------------+-----------+-------+--------+---------+<br>| 11           | 127.0.0.1 | 25400 | ONLINE | READ    |<br>| 10           | 127.0.0.1 | 25000 | ONLINE | WRITE   |<br>| 11           | 127.0.0.1 | 25100 | ONLINE | READ    |<br>| 11           | 127.0.0.1 | 25200 | ONLINE | READ    |<br>| 11           | 127.0.0.1 | 25300 | ONLINE | READ    |<br>+--------------+-----------+-------+--------+---------+<br>5 rows in set (0.00 sec)<br><br>mysql>

				1

						$ sudo proxysql-admin --config-file=/etc/proxysql-admin.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'@'127.%' 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 proxysql_user<br><br>Percona XtraDB Cluster application user 'proxysql_user'@'127.%' 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>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<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=proxysql_user --password=***** --host=127.0.0.1 --port=6033 --protocol=tcp<br><br>$<br><br>mysql> select hostgroup_id,hostname,port,status,comment from mysql_servers;<br>+--------------+-----------+-------+--------+---------+<br>| hostgroup_id | hostname  | port  | status | comment |<br>+--------------+-----------+-------+--------+---------+<br>| 11           | 127.0.0.1 | 25400 | ONLINE | READ    |<br>| 10           | 127.0.0.1 | 25000 | ONLINE | WRITE   |<br>| 11           | 127.0.0.1 | 25100 | ONLINE | READ    |<br>| 11           | 127.0.0.1 | 25200 | ONLINE | READ    |<br>| 11           | 127.0.0.1 | 25300 | ONLINE | READ    |<br>+--------------+-----------+-------+--------+---------+<br>5 rows in set (0.00 sec)<br><br>mysql>

2. −−disable

This option removes Percona XtraDB Cluster nodes from ProxySQL and stops the ProxySQL monitoring daemon.

$ proxysql-admin --config-file=/etc/proxysql-admin.cnf --disable<br>ProxySQL configuration removed!<br>$

1	$ proxysql-admin --config-file=/etc/proxysql-admin.cnf --disable<br>ProxySQL configuration removed!<br>$

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:

$ sudo grep "MODE" /etc/proxysql-admin.cnf<br>export MODE="singlewrite"<br>$ <br>$ sudo proxysql-admin --config-file=/etc/proxysql-admin.cnf --write-node=127.0.0.1:25000 --enable<br>ProxySQL read/write configuration mode is singlewrite<br>[..]<br>ProxySQL configuration completed!<br>$<br><br>mysql> select hostgroup_id,hostname,port,status,comment from mysql_servers;<br>+--------------+-----------+-------+--------+---------+<br>| hostgroup_id | hostname  | port  | status | comment |<br>+--------------+-----------+-------+--------+---------+<br>| 11           | 127.0.0.1 | 25400 | ONLINE | READ    |<br>| 10           | 127.0.0.1 | 25000 | ONLINE | WRITE   |<br>| 11           | 127.0.0.1 | 25100 | ONLINE | READ    |<br>| 11           | 127.0.0.1 | 25200 | ONLINE | READ    |<br>| 11           | 127.0.0.1 | 25300 | ONLINE | READ    |<br>+--------------+-----------+-------+--------+---------+<br>5 rows in set (0.00 sec)<br><br>mysql>

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

$ sudo proxysql-admin --config-file=/etc/proxysql-admin.cnf --mode=loadbal --enable<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>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=proxysql_user --password=***** --host=127.0.0.1 --port=6033 --protocol=tcp<br><br>$<br><br>mysql> select hostgroup_id,hostname,port,status,comment from mysql_servers;<br>+--------------+-----------+-------+--------+-----------+<br>| hostgroup_id | hostname  | port  | status | comment   |<br>+--------------+-----------+-------+--------+-----------+<br>| 10           | 127.0.0.1 | 25400 | ONLINE | READWRITE |<br>| 10           | 127.0.0.1 | 25000 | ONLINE | READWRITE |<br>| 10           | 127.0.0.1 | 25100 | ONLINE | READWRITE |<br>| 10           | 127.0.0.1 | 25200 | ONLINE | READWRITE |<br>| 10           | 127.0.0.1 | 25300 | ONLINE | READWRITE |<br>+--------------+-----------+-------+--------+-----------+<br>5 rows in set (0.01 sec)<br><br>mysql>

$ 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):

$ proxysql-admin --config-file=/etc/proxysql-admin.cnf --node-check-interval=5000 --enable

				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:

$ proxysql-admin --config-file=/etc/proxysql-admin.cnf --adduser<br><br>Adding Percona XtraDB Cluster application user to ProxySQL database<br>Enter Percona XtraDB Cluster application user name: root <br>Enter Percona XtraDB Cluster application user password: <br>Added Percona XtraDB Cluster application user to ProxySQL database!<br>$

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

$ sudo proxysql-admin --enable --quick-demo<br><br>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.<br><br>You may want to delete this user after you complete your testing!<br><br>Would you like to proceed with '--quick-demo' [y/n] ? y<br><br>Setting up proxysql test configuration!<br><br>Do you want to use the default ProxySQL credentials (admin:admin:6032:127.0.0.1) [y/n] ? y<br>Do you want to use the default Percona XtraDB Cluster credentials (root::3306:127.0.0.1) [y/n] ? n<br><br>Enter the Percona XtraDB Cluster username (super user): root<br>Enter the Percona XtraDB Cluster user password: <br>Enter the Percona XtraDB Cluster port: 25100<br>Enter the Percona XtraDB Cluster hostname: localhost<br><br><br>ProxySQL read/write configuration mode is singlewrite<br><br>Configuring ProxySQL monitoring user..<br><br>User 'monitor'@'127.%' has been added with USAGE privilege<br><br>Configuring the Percona XtraDB Cluster application user to connect through ProxySQL<br><br>Percona XtraDB Cluster application user 'pxc_test_user'@'127.%' has been added with ALL privileges, this user is created for testing purposes<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=pxc_test_user --host=127.0.0.1 --port=6033 --protocol=tcp<br><br>$<br><br>mysql> select hostgroup_id,hostname,port,status,comment from mysql_servers;<br>+--------------+-----------+-------+--------+---------+<br>| hostgroup_id | hostname  | port  | status | comment |<br>+--------------+-----------+-------+--------+---------+<br>| 11           | 127.0.0.1 | 25300 | ONLINE | READ    |<br>| 10           | 127.0.0.1 | 25000 | ONLINE | WRITE   |<br>| 11           | 127.0.0.1 | 25100 | ONLINE | READ    |<br>| 11           | 127.0.0.1 | 25200 | ONLINE | READ    |<br>+--------------+-----------+-------+--------+---------+<br>4 rows in set (0.00 sec)<br><br>mysql>

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