Skip to content
logo
Percona ProxySQL admin tools
The proxysql-admin functions
Initializing search
    percona/proxysql-admin-tool-doc
    percona/proxysql-admin-tool-doc
    • Home
      • Use ProxySQL 1.x.x with ProxySQL Admin
      • Install ProxySQL 1.x.x
      • Install ProxySQL 1.X from a binary tarball
      • Configure ProxySQL 1.x.x
        • Download locations for ProxySQL 2.x.x and ProxySQL admin utilities
        • Install ProxySQL 2.x.x and the admin utilities
        • Install ProxySQL 2.x.x binary tarball
        • Build the pxc_scheduler_handler tool
        • Start and stop ProxySQL
        • Upgrade ProxySQL
        • Uninstall ProxySQL 2.x.x
          • ProxySQL Admin tool changes
          • ProxySQL 2.x and proxysql-admin utility
          • The proxysql-admin functions
            • --enable
              • Output
            • --disable
            • --adduser
            • --syncusers
            • From ProxySQL DB
              • From PXC
            • –-sync-multi-cluster-users
            • –-add-query-rule
            • –-quick-demo
            • --update-cluster
            • –-is-enabled
            • --status
            • --force
            • –-update-mysql-version
              • Extra options
            • –-mode
              • singlewrite mode setup
              • loadbal mode setup
            • –-node-check-interval
            • –-write-node
              • The proxysql-status script
          • ProxySQL 2.x.x and pxc_scheduler_handler tool
          • pxc-scheduler-handler configuration
          • pxc_scheduler_handler statements
          • pxc-scheduler-handler options
          • Known issues in pxc_scheduler_handler
      • Release notes index
      • ProxySQL 2.4.8 and ProxySQL admin tools (2023-03-15)
      • ProxySQL 2.4.7 and ProxySQL admin tools (2023-02-14)
      • ProxySQL 2.4.4-1.2 and ProxySQL admin tools (2022-11-08)
      • ProxySQL 2.4.4 and ProxySQL admin tools (2022-10-04)
      • ProxySQL 2.4.3 and ProxySQL admin tools (2022-08-31)
      • ProxySQL 2.4.2 and proxysql admin tools (2022-08-10)
      • ProxySQL 2.3.2-1.2, proxysql-admin, and percona-scheduler-admin (2022-06-15)
      • ProxySQL 2.3.2 and proxysql-admin (2021-11-03)
      • ProxySQL 2.2.0 and proxysql-admin (2021-08-10)
      • ProxySQL 2.1.1 and proxysql-admin (2021-07-09)
      • ProxySQL 2.0.18 and proxysql-admin (2021-04-19)
      • ProxySQL 2.0.17 and proxysql-admin (2021-04-06)
      • ProxySQL 2.0.15 and proxysql-admin (2020-11-17)
      • ProxySQL 2.0.14 and proxysql-admin (2020-09-24)
      • ProxySQL 2.0.13 and proxysql-admin (2020-08-05)
      • ProxySQL 2.0.12 and proxysql-admin (2020-06-11)
      • ProxySQL 2.0.7 and proxysql-admin (2019-10-23)
      • ProxySQL 2.0.6 and proxysql-admin (2019-08-21)
      • ProxySQL 2.0.5 and proxysql-admin (2019-11-23)
      • ProxySQL 2.0.4 and proxysql-admin (2019-05-28)
      • ProxySQL 2.0.3 and proxysql-admin (2019-05-07)
      • ProxySQL 1.4.16 and proxysql-admin (2020-02-11)
      • ProxySQL 1.4.12 and proxysql-admin (2018-11-13)
      • ProxySQL 1.4.8 and proxysql-admin (2018-05-22)
      • ProxySQL 1.4.7 and proxysql-admin (2018-04-16)
      • ProxySQL 1.4.6 and proxysql-admin (2018-03-12)
      • ProxySQL 1.4.5 and proxysql-admin (2018-02-15)
      • ProxySQL 1.4.4 and proxysql-admin (2018-01-18)
    • --enable
      • Output
    • --disable
    • --adduser
    • --syncusers
    • From ProxySQL DB
      • From PXC
    • –-sync-multi-cluster-users
    • –-add-query-rule
    • –-quick-demo
    • --update-cluster
    • –-is-enabled
    • --status
    • --force
    • –-update-mysql-version
      • Extra options
    • –-mode
      • singlewrite mode setup
      • loadbal mode setup
    • –-node-check-interval
    • –-write-node
      • The proxysql-status script

    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.

    Contact us

    For free technical help, visit the Percona Community Forum.

    To report bugs or submit feature requests, open a JIRA ticket.

    For paid support and managed or consulting services , contact Percona Sales.


    Last update: 2023-03-24
    Percona LLC and/or its affiliates, © 2023
    Made with Material for MkDocs

    Cookie consent

    We use cookies to recognize your repeated visits and preferences, as well as to measure the effectiveness of our documentation and whether users find what they're searching for. With your consent, you're helping us to make our documentation better.