Skip to content
logo
Percona ProxySQL admin tools
Configure ProxySQL 1.x.x
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
        • Add cluster nodes to ProxySQL
        • Create a ProxySQL monitoring user
        • Create a ProxySQL client user
        • Add Galera support in ProxySQL 1.x.x
        • Test the cluster with sysbench
        • Automatic fail-over
        • Assisted maintenance mode
        • 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
          • 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)
    • Add cluster nodes to ProxySQL
    • Create a ProxySQL monitoring user
    • Create a ProxySQL client user
    • Add Galera support in ProxySQL 1.x.x
    • Test the cluster with sysbench
    • Automatic fail-over
    • Assisted maintenance mode

    Configure ProxySQL 1.x.x¶

    This tutorial describes how to configure ProxySQL with three Percona XtraDB Cluster nodes.

    Node Host name IP address
    Node 1 pxc1 192.168.70.61
    Node 2 pxc2 192.168.70.62
    Node 3 pxc3 192.168.70.63
    Node 4 proxysql 192.168.70.64

    ProxySQL can be configured either using etc/proxysql-admin.cnf or using the admin interface. The admin interface can change the configuration dynamically and there is no need to restart the proxy.

    To connect to the ProxySQL admin interface, use the mysql client. You can either connect to the admin interface from a Percona XtraDB Cluster node that already has the mysql client installed (Node 1, Node 2, Node 3) or install the client on Node 4 and connect locally. For this tutorial, install Percona XtraDB Cluster on Node 4:

        [root@proxysql ~]# apt install percona-xtradb-cluster-client
    
        [root@proxysql ~]# yum install percona-xtradb-cluster-client
    

    To connect to the admin interface, use the credentials, host name, and port specified in the ProxySQL global variables.

    Do not use the default credentials in production.

    The following example shows how to connect to the ProxySQL admin interface with the default credentials:

    root@proxysql:~# mysql -u admin -padmin -h 127.0.0.1 -P 6032
    
    Expected output
    Welcome to the MySQL monitor. Commands end with ; or \g.
    Your MySQL connection id is 8
    Server version: 5.1.30 (ProxySQL Admin Module)
    
    Copyright (c) 2009-2022 Percona LLC and/or its affiliates
    Copyright (c) 2000, 2022, Oracle and/or its affiliates.
    
    Oracle is a registered trademark of Oracle Corporation and/or its
    affiliates. Other names may be trademarks of their respective
    owners.
    
    Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
    
    mysql@proxysql>
    

    To see the ProxySQL databases and tables use the following commands:

    mysql@proxysql> SHOW DATABASES;
    
    Expected output
    +-----+---------+-------------------------------+
    | seq | name    | file                          |
    +-----+---------+-------------------------------+
    | 0   | main    |                               |
    | 2   | disk    | /var/lib/proxysql/proxysql.db |
    | 3   | stats   |                               |
    | 4   | monitor |                               |
    +-----+---------+-------------------------------+
    4 rows in set (0.00 sec)
    
    mysql@proxysql> SHOW TABLES;
    
    Expected output
    +--------------------------------------+
    | tables                               |
    +--------------------------------------+
    | global_variables                     |
    | mysql_collations                     |
    | mysql_query_rules                    |
    | mysql_replication_hostgroups         |
    | mysql_servers                        |
    | mysql_users                          |
    | runtime_global_variables             |
    | runtime_mysql_query_rules            |
    | runtime_mysql_replication_hostgroups |
    | runtime_mysql_servers                |
    | runtime_scheduler                    |
    | scheduler                            |
    +--------------------------------------+
    12 rows in set (0.00 sec)
    

    ProxySQL has three areas where the configuration can reside:

    • MEMORY (your current working place)

    • RUNTIME (the production settings)

    • DISK (durable configuration saved in a SQLite database)

    When you change a parameter, you change it in the MEMORY area. This method allows you to test the changes before pushing the change to production (RUNTIME) or to disk.

    Add cluster nodes to ProxySQL¶

    To configure the backend Percona XtraDB Cluster nodes in ProxySQL, insert the corresponding records into the mysql_servers table.

    ProxySQL uses the concept of hostgroups to group cluster nodes. This approach enables balancing the load in a cluster by routing different types of traffic to different groups.

    There are many ways you can configure hostgroups (for example, master and slaves, read and write load, etc.) and a node can be a member of multiple hostgroups.

    This example adds three Percona XtraDB Cluster nodes to the default hostgroup (0), which receives both write and read traffic:

    mysql@proxysql> INSERT INTO mysql_servers(hostgroup_id, hostname, port) VALUES (0,'192.168.70.61',3306);
    mysql@proxysql> INSERT INTO mysql_servers(hostgroup_id, hostname, port) VALUES (0,'192.168.70.62',3306);
    mysql@proxysql> INSERT INTO mysql_servers(hostgroup_id, hostname, port) VALUES (0,'192.168.70.63',3306);
    

    To see the nodes:

    mysql@proxysql> SELECT * FROM mysql_servers;
    
    Expected output
    hostgroup_id hostname port status weight compression max_connections max_replication_lag use_ssl max_latency_ms comment
    192.168.70.61 3306 ONLINE 1 0 1000 0 0
    192.168.70.62 3306 ONLINE 1 0 1000 0 0
    192.168.70.63 3306 ONLINE 1 0 1000 0 0

    Create a ProxySQL monitoring user¶

    To enable monitoring of Percona XtraDB Cluster nodes in ProxySQL, create a user with the USAGE privilege on any node in the cluster and configure the user in ProxySQL.

    The following example shows how to add a monitoring user on Node 2:

    mysql@pxc2> CREATE USER 'proxysql'@'%' IDENTIFIED BY 'ProxySQLPa55';
    mysql@pxc2> GRANT USAGE ON *.* TO 'proxysql'@'%';
    

    The following example shows how to configure this user on the ProxySQL node:

    mysql@proxysql> UPDATE global_variables SET variable_value='proxysql'
                  WHERE variable_name='mysql-monitor_username';
    mysql@proxysql> UPDATE global_variables SET variable_value='ProxySQLPa55'
                  WHERE variable_name='mysql-monitor_password';
    

    To load this configuration at runtime, issue a LOAD command. Issue a SAVE command to save these changes to disk, this operation ensures that the changes persist after ProxySQL shuts down.

    mysql@proxysql> LOAD MYSQL VARIABLES TO RUNTIME;
    mysql@proxysql> SAVE MYSQL VARIABLES TO DISK;
    

    Check the monitoring logs to ensure that monitoring is enabled:

    mysql@proxysql> SELECT * FROM monitor.mysql_server_connect_log ORDER BY time_start_us DESC LIMIT 6;
    
    Expected output
    +---------------+------+------------------+----------------------+---------------+
    | hostname      | port | time_start_us    | connect_success_time | connect_error |
    +---------------+------+------------------+----------------------+---------------+
    | 192.168.70.61 | 3306 | 1469635762434625 | 1695                 | NULL          |
    | 192.168.70.62 | 3306 | 1469635762434625 | 1779                 | NULL          |
    | 192.168.70.63 | 3306 | 1469635762434625 | 1627                 | NULL          |
    | 192.168.70.61 | 3306 | 1469635642434517 | 1557                 | NULL          |
    | 192.168.70.62 | 3306 | 1469635642434517 | 2737                 | NULL          |
    | 192.168.70.63 | 3306 | 1469635642434517 | 1447                 | NULL          |
    +---------------+------+------------------+----------------------+---------------+
    6 rows in set (0.00 sec)
    
    mysql> SELECT * FROM monitor.mysql_server_ping_log ORDER BY time_start_us DESC LIMIT 6;
    
    Expected output
    +---------------+------+------------------+-------------------+------------+
    | hostname      | port | time_start_us    | ping_success_time | ping_error |
    +---------------+------+------------------+-------------------+------------+
    | 192.168.70.61 | 3306 | 1469635762416190 | 948               | NULL       |
    | 192.168.70.62 | 3306 | 1469635762416190 | 803               | NULL       |
    | 192.168.70.63 | 3306 | 1469635762416190 | 711               | NULL       |
    | 192.168.70.61 | 3306 | 1469635702416062 | 783               | NULL       |
    | 192.168.70.62 | 3306 | 1469635702416062 | 631               | NULL       |
    | 192.168.70.63 | 3306 | 1469635702416062 | 542               | NULL       |
    +---------------+------+------------------+-------------------+------------+
    6 rows in set (0.00 sec)
    

    The previous examples show that ProxySQL is able to connect and ping the nodes you added.

    To enable monitoring of these nodes, load them at runtime:

    mysql@proxysql> LOAD MYSQL SERVERS TO RUNTIME;
    

    Create a ProxySQL client user¶

    ProxySQL must have users that can access backend nodes to manage connections.

    To add a user, insert credentials into mysql_users table:

    mysql@proxysql> INSERT INTO mysql_users (username,password) VALUES ('sbuser','sbpass');
    
    Expected output
    Query OK, 1 row affected (0.00 sec)
    

    Note

    ProxySQL currently does not encrypt passwords.

    Load the user into runtime space and save these changes to disk to ensure that the user account persists after ProxySQL shuts down:

    mysql@proxysql> LOAD MYSQL USERS TO RUNTIME;
    mysql@proxysql> SAVE MYSQL USERS TO DISK;
    

    To confirm that the user has been set up correctly, you can try to log in:

    root@proxysql:~# mysql -u sbuser -psbpass -h 127.0.0.1 -P 6033
    
    Expected output
    Welcome to the MySQL monitor. Commands end with ; or \g.
    Your MySQL connection id is 1491
    Server version: 5.1.30 (ProxySQL)
    
    Copyright (c) 2009-2016 Percona LLC and/or its affiliates
    Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
    
    Oracle is a registered trademark of Oracle Corporation and/or its
    affiliates. Other names may be trademarks of their respective
    owners.
    
    Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
    

    To provide read/write access to the cluster for ProxySQL, add this user on one of the Percona XtraDB Cluster nodes:

    mysql@pxc3> CREATE USER 'sbuser'@'192.168.70.64' IDENTIFIED BY 'sbpass';
    
    Expected output
    Query OK, 0 rows affected (0.01 sec)
    
    mysql@pxc3> GRANT ALL ON *.* TO 'sbuser'@'192.168.70.64';
    
    Expected output
    Query OK, 0 rows affected (0.00 sec)
    

    Add Galera support in ProxySQL 1.x.x¶

    ProxySQL 2.x.x supports monitoring the status Percona XtraDB Cluster nodes. ProxySQL 1.x.x can’t detect a node which isn’t in Synced state. To monitor the status of Percona XtraDB Cluster nodes in ProxySQL 1.x.x, use the script proxysql_galera_checker.

    To use this script, load it into ProxySQL scheduler.

    The following example shows how you can load the script for default ProxySQL 1.x.x configuration:

    mysql> INSERT INTO scheduler (active,interval_ms,filename,arg1,comment)
    VALUES (1,10000,'/usr/bin/proxysql_galera_checker','--config-file=/etc/proxysql-admin.cnf
    --write-hg=10 --read-hg=11 --writer-count=1 --mode=singlewrite
    --priority=192.168.100.20:3306,192.168.100.40:3306,192.168.100.10:3306,192.168.100.30:3306
    --log=/var/lib/proxysql/cluster_one_proxysql_galera_check.log','cluster_one');
    

    This scheduler script accepts the following options in the arg1 argument:

    Option Name Required Description
    --config-file Configuration file Yes Specify proxysql-admin conifiguration file
    --write-hg HOSTGROUP WRITERS No Specify ProxySQL write hostgroup
    --read-hg HOSTGROUP READERS No Specify ProxySQL read hostgroup
    --writer-count NUMBER WRITERS No Specify the write node count. The options are: 0 for loadbal mode and 1 for singlewrite mode.
    --mode MODE No Specify ProxySQL read/write configuration mode
    --priority WRITER PRIORITY No Specify write notes priority
    --log LOG FILE No Specify proxysql_galera_checker log file

    Note

    Specify the cluster name in comment column.

    To load the scheduler changes into the runtime space:

    mysql@proxysql> LOAD SCHEDULER TO RUNTIME;
    

    To make sure that the script has been loaded, review the runtime_scheduler table:

    mysql@proxysql> SELECT * FROM scheduler\G
    
    Expected output
    *************************** 1. row ***************************
            id: 1
        active: 1
    interval_ms: 10000
    filename: /bin/proxysql_galera_checker
        arg1: --config-file=/etc/proxysql-admin.cnf --write-hg=10 --read-hg=11
                --writer-count=1 --mode=singlewrite
                --priority=192.168.100.20:3306,192.168.100.40:3306,192.168.100.10:3306,192.168.100.30:3306
                --log=/var/lib/proxysql/cluster_one_proxysql_galera_check.log
        arg2: NULL
        arg3: NULL
        arg4: NULL
        arg5: NULL
        comment: cluster_one
    1 row in set (0.00 sec)
    

    Review the status of available nodes:

    mysql@proxysql> SELECT hostgroup_id,hostname,port,status FROM mysql_servers;
    
    Expected output
    +--------------+---------------+------+--------+
    | hostgroup_id | hostname      | port | status |
    +--------------+---------------+------+--------+
    | 0            | 192.168.70.61 | 3306 | ONLINE |
    | 0            | 192.168.70.62 | 3306 | ONLINE |
    | 0            | 192.168.70.63 | 3306 | ONLINE |
    +--------------+---------------+------+--------+
    3 rows in set (0.00 sec)
    

    Each node can have the following status:

    ONLINE

    Backend node is fully operational.
    

    SHUNNED

    backend node is temporarily taken out of use, because either too many
    connection errors happened in a short time, or replication lag exceeded the
    allowed threshold.
    

    OFFLINE_SOFT

    New incoming connections aren’t accepted, while existing connections are kept
    until they become inactive. In other words, connections are kept in use until
    the current transaction is completed.  This allows to gracefully detach a
    backend node.
    

    OFFLINE_HARD

    Existing connections are dropped, and new incoming connections aren’t
    accepted.  This is equivalent to deleting the node from a hostgroup, or
    temporarily taking it out of the hostgroup for maintenance.
    

    Test the cluster with sysbench¶

    Sysbench is designed to run CPU, memory and I/O test and has the option to run Online Transaction Processing (OLTP) workloads on a MySQL database. Install Sysbench from Percona software repositories:

    root@proxysql:~#> apt install sysbench
    
    [root@proxysql ~]#> yum install sysbench
    

    Sysbench requires the ProxySQL client user credentials from Create a ProxySQL client user.

    1. Create a database on one of the Percona XtraDB Cluster nodes. Use this database for testing.

      mysql@pxc1> CREATE DATABASE sbtest;
      
    2. Populate the table with data for the benchmark on the ProxySQL node:

      root@proxysql:~#> sysbench --report-interval=5 --num-threads=4 \
      --num-requests=0 --max-time=20 \
      --test=/usr/share/doc/sysbench/tests/db/oltp.lua \
      --mysql-user='sbuser' --mysql-password='sbpass' \
      --oltp-table-size=10000 --mysql-host=127.0.0.1 --mysql-port=6033 \
      prepare
      
    3. Run the benchmark on the ProxySQL node:

      root@proxysql:~#> sysbench --report-interval=5 --num-threads=4 \
      --num-requests=0 --max-time=20 \
      --test=/usr/share/doc/sysbench/tests/db/oltp.lua \
      --mysql-user='sbuser' --mysql-password='sbpass' \
      --oltp-table-size=10000 --mysql-host=127.0.0.1 --mysql-port=6033 \
      run
      

    ProxySQL stores collected data in the stats schema:

    mysql@proxysql> SHOW TABLES FROM stats;
    
    Expected output
    +--------------------------------+
    | tables                         |
    +--------------------------------+
    | stats_mysql_query_rules        |
    | stats_mysql_commands_counters  |
    | stats_mysql_processlist        |
    | stats_mysql_connection_pool    |
    | stats_mysql_query_digest       |
    | stats_mysql_query_digest_reset |
    | stats_mysql_global             |
    +--------------------------------+
    

    For example, to see the number of commands that run on the cluster:

    Automatic fail-over¶

    ProxySQL automatically detects if a node isn’t available or if the node isn’t synced with the cluster.

    You can check the status of all available nodes by running:

    mysql@proxysql> SELECT hostgroup_id,hostname,port,status FROM mysql_servers;
    
    Expected output
    +--------------+---------------+------+--------+
    | hostgroup_id | hostname      | port | status |
    +--------------+---------------+------+--------+
    | 0            | 192.168.70.61 | 3306 | ONLINE |
    | 0            | 192.168.70.62 | 3306 | ONLINE |
    | 0            | 192.168.70.63 | 3306 | ONLINE |
    +--------------+---------------+------+--------+
    3 rows in set (0.00 sec)
    

    To test problem detection and fail-over mechanism, shut down Node 3:

    root@pxc3:~# service mysql stop
    

    ProxySQL detects that the node is down and updates the node’s status to OFFLINE_SOFT:

    mysql@proxysql> SELECT hostgroup_id,hostname,port,status FROM mysql_servers;
    
    Expected output
    +--------------+---------------+------+--------------+
    | hostgroup_id | hostname      | port | status       |
    +--------------+---------------+------+--------------+
    | 0            | 192.168.70.61 | 3306 | ONLINE       |
    | 0            | 192.168.70.62 | 3306 | ONLINE       |
    | 0            | 192.168.70.63 | 3306 | OFFLINE_SOFT |
    +--------------+---------------+------+--------------+
    3 rows in set (0.00 sec)
    

    Start Node 3 again:

    root@pxc3:~#> service mysql start
    

    The script detects the change and marks the node as ONLINE:

    mysql@proxysql> SELECT hostgroup_id,hostname,port,status FROM mysql_servers;
    
    Expected output
    +--------------+---------------+------+--------+
    | hostgroup_id | hostname      | port | status |
    +--------------+---------------+------+--------+
    | 0            | 192.168.70.61 | 3306 | ONLINE |
    | 0            | 192.168.70.62 | 3306 | ONLINE |
    | 0            | 192.168.70.63 | 3306 | ONLINE |
    +--------------+---------------+------+--------+
    3 rows in set (0.00 sec)
    

    Assisted maintenance mode¶

    For maintenance, identify that node, update its status in ProxySQL to OFFLINE_SOFT, wait for ProxySQL to divert traffic from this node, and then initiate the shutdown or perform maintenance tasks. Percona XtraDB Cluster includes a special maintenance mode for nodes that enables you to take a node down without adjusting ProxySQL manually. This mode is controlled by the pxc_maint_mode variable, which is monitored by ProxySQL and can be set to one of the following values:

    • DISABLED: This is the default state that tells ProxySQL to route traffic to the node as usual.

    • SHUTDOWN: This state is set automatically when you initiate node shutdown.

    • MAINTENANCE: You can manually change to this state if you need to perform maintenance on a node without shutting it down.

    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.