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.