Skip to content
Percona Software for MySQL Documentation

The proxysql-admin functions

The script has the following functions.

–enable

This option creates the entry for the Galera hostgroups and adds the Percona XtraDB Cluster nodes to ProxySQL.

It adds two new users into the Percona XtraDB Cluster with the USAGE privilege; one monitors the cluster nodes through ProxySQL, and the other connects to the PXC Cluster node through the ProxySQL console.

$ sudo proxysql-admin --config-file=/etc/proxysql-admin.cnf --enable

Output

This script will assist with configuring ProxySQL for use with
      Percona XtraDB Cluster (currently only PXC in combination
      with ProxySQL is supported)

      ProxySQL read/write configuration mode is singlewrite

      Configuring the ProxySQL monitoring user.
      ProxySQL monitor user name as per command line/config-file is monitor

      User 'monitor'@'127.%' has been added with USAGE privileges

      Configuring the Percona XtraDB Cluster application user to connect through ProxySQL
      Percona XtraDB Cluster application user name as per command line/config-file is proxysql_user

      Percona XtraDB Cluster application user 'proxysql_user'@'127.%' has been added with ALL privileges, 
      this user is created for testing purposes
      Adding the Percona XtraDB Cluster server nodes to ProxySQL

      Write node info
      +-----------+--------------+-------+--------+
      | hostname  | hostgroup_id | port  | weight |
      +-----------+--------------+-------+--------+
      | 127.0.0.1 | 10           | 26100 | 1000   |
      +-----------+--------------+-------+--------+

      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 -p --host=localhost --port=6033 --protocol=tcp

You can use the following login credentials to connect your application through ProxySQL:

$ mysql --user=proxysql_user -p --host=127.0.0.1 --port=6033 --protocol=tcp
mysql> select hostgroup_id,hostname,port,status from runtime_mysql_servers;
+--------------+-----------+-------+--------+
| hostgroup_id | hostname  | port  | status |
+--------------+-----------+-------+--------+
| 10           | 127.0.0.1 | 25000 | ONLINE |
| 11           | 127.0.0.1 | 25100 | ONLINE |
| 11           | 127.0.0.1 | 25200 | ONLINE |
| 12           | 127.0.0.1 | 25100 | ONLINE |
| 12           | 127.0.0.1 | 25200 | ONLINE |
+--------------+-----------+-------+--------+
5 rows in set (0.00 sec)

mysql> select * from mysql_galera_hostgroups\G
writer_hostgroup: 10
backup_writer_hostgroup: 12
reader_hostgroup: 11
offline_hostgroup: 13
active: 1
max_writers: 1
writer_is_also_reader: 2
max_transactions_behind: 100
comment: NULL
1 row in set (0.00 sec)

The --enable command can be used with --update-cluster. If the cluster has not been setup, then the enable function is run. If the cluster has been setup, then the update cluster function runs.

–disable

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

$ proxysql-admin --config-file=/etc/proxysql-admin.cnf --disable
Removing cluster application users from the ProxySQL database.
Removing cluster nodes from the ProxySQL database.
Removing query rules from the ProxySQL database if any.
Removing the cluster from the ProxySQL database.
ProxySQL configuration removed!

A specific Galera cluster can be disabled by using the –writer-hg option with --disable.

–adduser

This option will aid with adding the Cluster application user to the ProxySQL database for you

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

–syncusers

This option will sync user accounts currently configured in Percona XtraDB Cluster with the ProxySQL database except for password-less users and admin users. It also deletes ProxySQL users not in Percona XtraDB Cluster from the ProxySQL database.

$ /usr/bin/proxysql-admin --syncusers
Syncing user accounts from Percona XtraDB Cluster to ProxySQL
Synced Percona XtraDB Cluster users to the ProxySQL database!

From ProxySQL DB

mysql> select username from mysql_users;
+---------------+
| username      |
+---------------+
| monitor       |
| one           |
| proxysql_user |
| two           |
+---------------+
4 rows in set (0.00 sec)

From PXC

mysql> select user,host from mysql.user where authentication_string!='' and user not in ('admin','mysql.sys');
+---------------+-------+
| user          | host  |
+---------------+-------+
| monitor       | 192.% |
| proxysql_user | 192.% |
| two           | %     |
| one           | %     |
+---------------+-------+
4 rows in set (0.00 sec)

–-sync-multi-cluster-users

This option works in the same way as –syncusers but does not delete ProxySQL users that are not present in the Percona XtraDB Cluster. Used this option when syncing proxysql instances that manage multiple clusters.

–-add-query-rule

Create query rules for synced mysql user. This is applicable only for singlewrite mode and works only with –syncusers and –sync-multi-cluster-users options.

Syncing user accounts from PXC to ProxySQL

Note : 'admin' is in proxysql admin user list, this user cannot be added to ProxySQL
-- (For more info, see https://github.com/sysown/proxysql/issues/709)
Adding user to ProxySQL: test_query_rule
Added query rule for user: test_query_rule

Synced PXC users to the ProxySQL database!

–-quick-demo

This option configures a dummy proxysql configuration.

$ sudo  proxysql-admin --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 from runtime_mysql_servers;
+--------------+-----------+-------+--------+
| hostgroup_id | hostname  | port  | status |
+--------------+-----------+-------+--------+
| 10           | 127.0.0.1 | 25000 | ONLINE |
| 11           | 127.0.0.1 | 25100 | ONLINE |
| 11           | 127.0.0.1 | 25200 | ONLINE |
| 12           | 127.0.0.1 | 25100 | ONLINE |
| 12           | 127.0.0.1 | 25200 | ONLINE |
+--------------+-----------+-------+--------+
5 rows in set (0.00 sec)

–update-cluster

This option will check the Percona XtraDB Cluster to see if any new nodes have joined the cluster. If so, the new nodes are added to ProxySQL. Any offline nodes are not removed from the cluster by default.

If used with --remove-all-servers, then the server list for this configuration will be removed before running the update cluster function.

A specific Galera cluster can be updated by using the --writer-hg option with --update-cluster. Otherwise, the cluster specified in the config file will be updated.

If --write-node is used with --update-cluster, then that node will be made the writer node (by giving it a larger weight), if the node is in the server list and is ONLINE. This should only be used if the mode is singlewrite.

$ sudo proxysql-admin --update-cluster --writer-hg=10 --remove-all-servers
Removing all servers from ProxySQL
Cluster node (127.0.0.1:25000) does not exist in ProxySQL, adding to the writer hostgroup(10)
Cluster node (127.0.0.1:25100) does not exist in ProxySQL, adding to the writer hostgroup(10)
Cluster node (127.0.0.1:25200) does not exist in ProxySQL, adding to the writer hostgroup(10)
Waiting for ProxySQL to process the new nodes...

Cluster node info
+---------------+-------+-----------+-------+-----------+
| hostgroup     | hg_id | hostname  | port  | weight    |
+---------------+-------+-----------+-------+-----------+
| writer        | 10    | 127.0.0.1 | 25000 | 1000      |
| reader        | 11    | 127.0.0.1 | 25100 | 1000      |
| reader        | 11    | 127.0.0.1 | 25200 | 1000      |
| backup-writer | 12    | 127.0.0.1 | 25100 | 1000      |
| backup-writer | 12    | 127.0.0.1 | 25200 | 1000      |
+---------------+-------+-----------+------+------------+

Cluster membership updated in the ProxySQL database!

–-is-enabled

This option checks if a Galera cluster (specified by the writer hostgroup, either from --writer-hg or from the config file) has any active entries in the mysql_galera_hostgroups table in ProxySQL.

Value Returned Value
0 An entry corresponding to the writer hostgroup and is set to active in ProxySQL.
1 No entry corresponding to the writer hostgroup.
2 An entry corresponding to the writer hostgroup but is not active. 
$ sudo proxysql-admin --is-enabled --writer-hg=10
The current configuration has been enabled and is active

$ sudo proxysql-admin --is-enabled --writer-hg=20
ERROR (line:2925) : The current configuration has not been enabled

–status

Displays information about all Galera hostgroups and their servers being supported by this ProxySQL instance, unless it is used with the --writer-hg option, which displays information about the given Galera cluster which uses that writer hostgroup.

$ sudo proxysql-admin --status --writer-hg=10
mysql_galera_hostgroups row for writer-hostgroup: 10
+--------+--------+---------------+---------+--------+-------------+-----------------------+------------------+
| writer | reader | backup-writer | offline | active | max_writers | writer_is_also_reader | max_trans_behind |
+--------+--------+---------------+---------+--------+-------------+-----------------------+------------------+
| 10     | 11     | 12            | 13      | 1      | 1           | 2                     | 100              |
+--------+--------+---------------+---------+--------+-------------+-----------------------+------------------+

mysql_servers rows for this configuration
+---------------+-------+-----------+-------+--------+-----------+----------+---------+-----------+
| hostgroup     | hg_id | hostname  | port  | status | weight    | max_conn | use_ssl | gtid_port |
+---------------+-------+-----------+-------+--------+-----------+----------+---------+-----------+
| writer        | 10    | 127.0.0.1 | 25000 | ONLINE | 1000000   | 1000     | 0       | 0         |
| reader        | 11    | 127.0.0.1 | 25100 | ONLINE | 1000      | 1000     | 0       | 0         |
| reader        | 11    | 127.0.0.1 | 25200 | ONLINE | 1000      | 1000     | 0       | 0         |
| backup-writer | 12    | 127.0.0.1 | 25100 | ONLINE | 1000      | 1000     | 0       | 0         |
| backup-writer | 12    | 127.0.0.1 | 25200 | ONLINE | 1000      | 1000     | 0       | 0         |
+---------------+-------+-----------+-------+--------+-----------+----------+---------+-----------+

–force

Skips the existing configuration checks with the --enable option in mysql_servers, mysql_users, and mysql_galera_hostgroups tables.

–-update-mysql-version

This option updates the mysql server version (specified by the writer hostgroup, either from --writer-hg or the config file) in proxysql db-based on the online writer node.

$  sudo proxysql-admin --update-mysql-version --writer-hg=10
ProxySQL MySQL version changed to 5.7.26

Extra options

–-mode

This option allows you to set up the read/write mode for PXC cluster nodes in the ProxySQL database based on the hostgroup. For now, the only supported modes are singlewrite and loadbal. The singlewrite option is the default mode, and configures Percona XtraDB Cluster to only accept writes on a single node. Depending on the --writers-are-readers value, the write node may accept read requests. All other remaining nodes are read-only and only receive read statements.

With the --write-node option we control which node ProxySQL uses as the writer node. The writer node is specified as the address:port - 10.0.0.51:3306 If --write-node is used, the writer node is given a weight of 1000000 (the default weight is 1000).

The loadbal mode is a load balanced set of evenly weighted read/write nodes.

singlewrite mode setup

$ 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 from runtime_mysql_servers;
+--------------+-----------+-------+--------+
| hostgroup_id | hostname  | port  | status |
+--------------+-----------+-------+--------+
| 10           | 127.0.0.1 | 25000 | ONLINE |
| 11           | 127.0.0.1 | 25100 | ONLINE |
| 11           | 127.0.0.1 | 25200 | ONLINE |
| 12           | 127.0.0.1 | 25100 | ONLINE |
| 12           | 127.0.0.1 | 25200 | ONLINE |
+--------------+-----------+-------+--------+
5 rows in set (0.00 sec)

loadbal mode setup

$ sudo proxysql-admin --config-file=/etc/proxysql-admin.cnf --mode=loadbal --enable
This script assists 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 from runtime_mysql_servers;
+--------------+-----------+-------+--------+
| hostgroup_id | hostname  | port  | status |
+--------------+-----------+-------+--------+
| 10           | 127.0.0.1 | 25000 | ONLINE |
| 10           | 127.0.0.1 | 25100 | ONLINE |
| 10           | 127.0.0.1 | 25200 | ONLINE |
+--------------+-----------+-------+--------+
3 rows in set (0.01 sec)

–-node-check-interval

This option configures the interval for the cluster node health monitoring by ProxySQL (in milliseconds). This is a global variable and is used by all clusters that are being served by this ProxySQL instance. This can only be used with --enable.

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

–-write-node

This option is used to choose which node will be the writer node when the mode is singlewrite. This option can be used with –enable and –update-cluster.

A single IP address and port combination is expected. For example, “–write-node=127.0.0.1:3306”

The proxysql-status script

The proxysql-status is a simple script to dump the ProxySQL configuration and statistics.

$ proxysql-status admin admin 127.0.0.1 6032

The default behavior is to display all tables and files. By using the following options, you can retrieve more specific information:

Option Use to display
–files The contents of proxysql-admin related files
–main Main tables (both on-disk and runtime)
–monitor Monitor tables
–runtime Runtime-related data (implies –main)
–stats Stats tables
–table= Only tables that contain the table name (a case-sensitive match)
–with-stats-reset _reset tables, by default _reset tables will not be queried.

Note

If no credentials are specified, the credentials in /etc/proxysql-admin.cnf are used.

Get expert help

If you need assistance, visit the community forum for comprehensive and free database knowledge, or contact our Percona Database Experts for professional support and services.

Community Forum Get a Percona Expert

Last update: 2024-11-20