Configuring Backend MySQL Servers in ProxySQL

MySQL Servers are configured in the mysql_servers and (optionally) mysql_replication_hostgroups tables.

Note: Before reading the following section, please make sure you understand the multi-layer configuration system used by ProxySQL.
Specifically:

changes in mysql_servers and mysql_replication_hostgroups tables do not take effect without running LOAD MYSQL SERVERS TO RUNTIME
changes in mysql_servers and mysql_replication_hostgroups tables are lost after a restart/crash if not saved to disk running SAVE MYSQL SERVERS TO DISK

All changes in mysql_servers and mysql_replication_hostgroups tables do not take effect until LOAD MYSQL SERVERS TO RUNTIME is executed, and are not persistent unless SAVE MYSQL SERVERS TO DISK is executed.
Changes to mysql_servers and mysql_replication_hostgroups are to be considered as editing a config file without saving it or reloading the service.

IMPORTANT: all the examples below do not take effect until LOAD MYSQL SERVERS TO RUNTIME is executed. SAVE MYSQL SERVERS TO DISK is required to persist the configuration across ProxySQL restarts.

Copying mysql servers from memory to runtime

loads MySQL servers and replication hostgroups from the in-memory database to the runtime data structures

Admin> LOAD MYSQL SERVERS TO RUNTIME;

Other alias accepted:

LOAD MYSQL SERVERS TO RUN
LOAD MYSQL SERVERS FROM MEM
LOAD MYSQL SERVERS FROM MEMORY

Copying mysql servers from memory to disk

persists the MySQL servers and replication hostgroups from the in-memory database to the on-disk database

Admin> SAVE MYSQL SERVERS TO DISK;

Other alias accepted:

SAVE MYSQL SERVERS FROM MEM
SAVE MYSQL SERVERS FROM MEMORY

Copying mysql servers from runtime to memory

persists the MySQL servers and replication hostgroups from the runtime data structures to the in-memory database

Admin> SAVE MYSQL SERVERS TO MEMORY;

Other alias accepted:

SAVE MYSQL SERVERS TO MEM
SAVE MYSQL SERVERS FROM RUN
SAVE MYSQL SERVERS FROM RUNTIME

Copying mysql servers from disk to memory

loads MySQL servers and replication hostgroups from the on-disk database to the in-memory database

Admin> LOAD MYSQL SERVERS TO MEMORY;

Other alias accepted:

LOAD MYSQL SERVERS TO MEM
LOAD MYSQL SERVERS FROM DISK

Adding a new server

In order to add a new server, it must be defined inserting a new row in mysql_servers table.
Note that the table has several columns with defaults.

The following adds a new backend with all default configuration:

Admin> SELECT * FROM mysql_servers;
Empty set (0.00 sec)

Admin> INSERT INTO mysql_servers (hostname) VALUES ('172.16.0.1');
Query OK, 1 row affected (0.00 sec)

Admin> SELECT * FROM mysql_servers\G
*************************** 1. row ***************************
       hostgroup_id: 0
           hostname: 172.16.0.1
               port: 3306
          gtid_port: 0
             status: ONLINE
             weight: 1
        compression: 0
    max_connections: 1000
max_replication_lag: 0
            use_ssl: 0
     max_latency_ms: 0
            comment: 
1 row in set (0.00 sec)

Adding new servers to a hostgroup

Admin> SELECT hostgroup_id,hostname FROM mysql_servers;
+--------------+------------+
| hostgroup_id | hostname   |
+--------------+------------+
| 0            | 172.16.0.1 |
+--------------+------------+
1 row in set (0.00 sec)

Admin> INSERT INTO mysql_servers (hostgroup_id, hostname) VALUES (1, '172.16.0.2'), (1,'172.16.0.3');
Query OK, 2 rows affected (0.00 sec)

Admin> SELECT hostgroup_id,hostname FROM mysql_servers;
+--------------+------------+
| hostgroup_id | hostname   |
+--------------+------------+
| 0            | 172.16.0.1 |
| 1            | 172.16.0.2 |
| 1            | 172.16.0.3 |
+--------------+------------+
3 rows in set (0.00 sec)

Limiting the number of connections to a backend

Admin> SELECT hostgroup_id,hostname,max_connections FROM mysql_servers;
+--------------+------------+-----------------+
| hostgroup_id | hostname   | max_connections |
+--------------+------------+-----------------+
| 0            | 172.16.0.1 | 1000            |
| 1            | 172.16.0.2 | 1000            |
| 1            | 172.16.0.3 | 1000            |
+--------------+------------+-----------------+
3 rows in set (0.00 sec)

Admin> UPDATE mysql_servers SET max_connections=10 WHERE hostname='172.16.0.2';
Query OK, 1 row affected (0.00 sec)

Admin> SELECT hostgroup_id,hostname,max_connections FROM mysql_servers;
+--------------+------------+-----------------+
| hostgroup_id | hostname   | max_connections |
+--------------+------------+-----------------+
| 0            | 172.16.0.1 | 1000            |
| 1            | 172.16.0.2 | 10              |
| 1            | 172.16.0.3 | 1000            |
+--------------+------------+-----------------+
3 rows in set (0.00 sec)

Prioritizing traffic by changing the weight of a backend

Weights are relevant only within a hostgroup

Admin> SELECT hostgroup_id,hostname,weight FROM mysql_servers;
+--------------+------------+--------+
| hostgroup_id | hostname   | weight |
+--------------+------------+--------+
| 0            | 172.16.0.1 | 1      |
| 1            | 172.16.0.2 | 1      |
| 1            | 172.16.0.3 | 1      |
+--------------+------------+--------+
3 rows in set (0.00 sec)

Admin> UPDATE mysql_servers SET weight=1000 WHERE hostname NOT IN ('172.16.0.2', '172.16.0.1') AND hostgroup_id=1;
Query OK, 1 row affected (0.00 sec)

Admin> SELECT hostgroup_id,hostname,weight FROM mysql_servers;
+--------------+------------+--------+
| hostgroup_id | hostname   | weight |
+--------------+------------+--------+
| 0            | 172.16.0.1 | 1      |
| 1            | 172.16.0.2 | 1      |
| 1            | 172.16.0.3 | 1000   |
+--------------+------------+--------+
3 rows in set (0.00 sec)

Using SSL connections for a specific backend

This example shows how to configure SSL for a backend. Although it does not show how to globally configure SSL. Check here for details on how to globally configure SSL.

Admin> SELECT hostgroup_id,hostname,use_ssl FROM mysql_servers;
+--------------+------------+---------+
| hostgroup_id | hostname   | use_ssl |
+--------------+------------+---------+
| 0            | 172.16.0.1 | 1       |
| 1            | 172.16.0.2 | 0       |
| 1            | 172.16.0.3 | 0       |
+--------------+------------+---------+
3 rows in set (0.00 sec)

Automatically shunning slaves with replication lag

The Monitor module regularly checks replication lag if a server has max_replication_lag set to a non-zero value.

Admin> SELECT hostgroup_id,hostname,max_replication_lag FROM mysql_servers;
+--------------+------------+---------------------+
| hostgroup_id | hostname   | max_replication_lag |
+--------------+------------+---------------------+
| 0            | 172.16.0.1 | 0                   |
| 1            | 172.16.0.2 | 0                   |
| 1            | 172.16.0.3 | 0                   |
+--------------+------------+---------------------+
3 rows in set (0.00 sec)

Admin> UPDATE mysql_servers SET max_replication_lag=30 WHERE hostname='172.16.0.3';
Query OK, 1 row affected (0.00 sec)

Admin> SELECT hostgroup_id,hostname,max_replication_lag FROM mysql_servers;
+--------------+------------+---------------------+
| hostgroup_id | hostname   | max_replication_lag |
+--------------+------------+---------------------+
| 0            | 172.16.0.1 | 0                   |
| 1            | 172.16.0.2 | 0                   |
| 1            | 172.16.0.3 | 30                  |
+--------------+------------+---------------------+
3 rows in set (0.00 sec)

With this configuration, server 172.16.0.3 will only be shunned in case replication delay exceeds 30 seconds behind master. In case max_replication_lag is set to zero (SET max_replication_lag=0), the Monitor module will not check replication lag at all i.e. with max_replication_lag set to 0 a host won’t be shunned if the replication lag exceeds the threshold, see mysql-monitor_slave_lag_when_null for more info.

Adding a server to two different hostgroups

The primary key on mysql_servers table is defined as PRIMARY KEY (hostgroup_id, hostname, port). That means that the same server can be present in two different hostgroups, and there are multiple reasons why you may want to configure that.
For example, in a replication topology with one master and 2 slaves, you would like to send reads to the master if for any reason the slaves disappear (because they die, or because they lag too much).
Here’s an example:

Admin> SELECT hostgroup_id,hostname,weight,max_replication_lag FROM mysql_servers;
+--------------+------------+--------+---------------------+
| hostgroup_id | hostname   | weight | max_replication_lag |
+--------------+------------+--------+---------------------+
| 0            | 172.16.0.1 | 1      | 0                   |
| 1            | 172.16.0.2 | 1      | 0                   |
| 1            | 172.16.0.3 | 1000   | 30                  |
+--------------+------------+--------+---------------------+
3 rows in set (0.00 sec)

Admin> INSERT INTO mysql_servers(hostgroup_id,hostname) VALUES (1,'172.16.0.1');
Query OK, 1 row affected (0.00 sec)

Admin> SELECT hostgroup_id,hostname,weight,max_replication_lag FROM mysql_servers;
+--------------+------------+--------+---------------------+
| hostgroup_id | hostname   | weight | max_replication_lag |
+--------------+------------+--------+---------------------+
| 0            | 172.16.0.1 | 1      | 0                   |
| 1            | 172.16.0.2 | 1      | 0                   |
| 1            | 172.16.0.3 | 1000   | 30                  |
| 1            | 172.16.0.1 | 1      | 0                   |
+--------------+------------+--------+---------------------+
4 rows in set (0.00 sec)

Admin> UPDATE mysql_servers SET max_replication_lag=30, weight=1000;
Query OK, 4 rows affected (0.00 sec)

Admin> UPDATE mysql_servers SET weight=1 WHERE hostname='172.16.0.1' AND hostgroup_id=1;
Query OK, 1 row affected (0.00 sec)

Admin> SELECT hostgroup_id,hostname,weight,max_replication_lag FROM mysql_servers;
+--------------+------------+--------+---------------------+
| hostgroup_id | hostname   | weight | max_replication_lag |
+--------------+------------+--------+---------------------+
| 0            | 172.16.0.1 | 1000   | 30                  |
| 1            | 172.16.0.2 | 1000   | 30                  |
| 1            | 172.16.0.3 | 1000   | 30                  |
| 1            | 172.16.0.1 | 1      | 30                  |
+--------------+------------+--------+---------------------+
4 rows in set (0.00 sec)

In the example above, if we configure HG1 to serve read traffic, 99.95% of the traffic will be sent to 172.16.0.2 and 172.16.0.3, while 0.05% of the traffic will be normally sent to 172.16.0.1 . If 172.16.0.2 and 172.16.0.3 become unavailable, 172.16.0.1 will take all the read traffic.
Note: max_replication_lag applies only to slaves. If a server doesn’t have replication enabled, Monitor won’t take any action.

Enable compression on a backend server

To enable compression it is enough to set mysql_servers.compression to a non-zero value. Note that compression will only be used for new connections initiated after the setting is loaded to runtime.

Admin> SELECT hostgroup_id,hostname,compression FROM mysql_servers;
+--------------+------------+-------------+
| hostgroup_id | hostname   | compression |
+--------------+------------+-------------+
| 0            | 172.16.0.1 | 0           |
| 1            | 172.16.0.2 | 0           |
| 1            | 172.16.0.3 | 0           |
| 1            | 172.16.0.1 | 0           |
+--------------+------------+-------------+
4 rows in set (0.00 sec)

Admin> UPDATE mysql_servers SET compression=1 WHERE hostname='172.16.0.2' AND hostgroup_id=1;
Query OK, 1 row affected (0.00 sec)

Admin> SELECT hostgroup_id,hostname,compression FROM mysql_servers;
+--------------+------------+-------------+
| hostgroup_id | hostname   | compression |
+--------------+------------+-------------+
| 0            | 172.16.0.1 | 0           |
| 1            | 172.16.0.2 | 1           |
| 1            | 172.16.0.3 | 0           |
| 1            | 172.16.0.1 | 0           |
+--------------+------------+-------------+
4 rows in set (0.00 sec)

Gracefully disabling a backend server

To gracefully disable a backend server it is required to change its status to OFFLINE_SOFT. Active transactions and connections will still be used, but no new traffic will be sent to the node.

Admin> SELECT hostgroup_id,hostname,status FROM mysql_servers;
+--------------+------------+--------+
| hostgroup_id | hostname   | status |
+--------------+------------+--------+
| 0            | 172.16.0.1 | ONLINE |
| 1            | 172.16.0.2 | ONLINE |
| 1            | 172.16.0.3 | ONLINE |
| 1            | 172.16.0.1 | ONLINE |
+--------------+------------+--------+
4 rows in set (0.00 sec)

Admin> UPDATE mysql_servers SET status='OFFLINE_SOFT' WHERE hostname='172.16.0.2';
Query OK, 1 row affected (0.00 sec)

Admin> SELECT hostgroup_id,hostname,status FROM mysql_servers;
+--------------+------------+--------------+
| hostgroup_id | hostname   | status       |
+--------------+------------+--------------+
| 0            | 172.16.0.1 | ONLINE       |
| 1            | 172.16.0.2 | OFFLINE_SOFT |
| 1            | 172.16.0.3 | ONLINE       |
| 1            | 172.16.0.1 | ONLINE       |
+--------------+------------+--------------+
4 rows in set (0.00 sec)

Immediately disabling a backend server

To immediately disable a backend server it is required to change its status to OFFLINE_HARD. All the current traffic will be immediately terminated, and no new traffic will be sent.

Admin> SELECT hostgroup_id,hostname,status FROM mysql_servers;
+--------------+------------+--------------+
| hostgroup_id | hostname   | status       |
+--------------+------------+--------------+
| 0            | 172.16.0.1 | ONLINE       |
| 1            | 172.16.0.2 | OFFLINE_SOFT |
| 1            | 172.16.0.3 | ONLINE       |
| 1            | 172.16.0.1 | ONLINE       |
+--------------+------------+--------------+
4 rows in set (0.00 sec)

Admin> UPDATE mysql_servers SET status='OFFLINE_HARD' WHERE hostname='172.16.0.1' AND hostgroup_id=1;
Query OK, 1 row affected (0.00 sec)

Admin> SELECT hostgroup_id,hostname,status FROM mysql_servers;
+--------------+------------+--------------+
| hostgroup_id | hostname   | status       |
+--------------+------------+--------------+
| 0            | 172.16.0.1 | ONLINE       |
| 1            | 172.16.0.2 | OFFLINE_SOFT |
| 1            | 172.16.0.3 | ONLINE       |
| 1            | 172.16.0.1 | OFFLINE_HARD |
+--------------+------------+--------------+
4 rows in set (0.00 sec)

Re-enabling an offline / disabled backend server

To re-enable on offline backend it is enough to change its status back to ONLINE:

Admin> SELECT hostgroup_id,hostname,status FROM mysql_servers;
+--------------+------------+--------------+
| hostgroup_id | hostname   | status       |
+--------------+------------+--------------+
| 0            | 172.16.0.1 | ONLINE       |
| 1            | 172.16.0.2 | OFFLINE_SOFT |
| 1            | 172.16.0.3 | ONLINE       |
| 1            | 172.16.0.1 | OFFLINE_HARD |
+--------------+------------+--------------+
4 rows in set (0.00 sec)

Admin> UPDATE mysql_servers SET status='ONLINE' WHERE status NOT IN ('ONLINE');
Query OK, 2 rows affected (0.00 sec)

Admin> SELECT hostgroup_id,hostname,status FROM mysql_servers;
+--------------+------------+--------+
| hostgroup_id | hostname   | status |
+--------------+------------+--------+
| 0            | 172.16.0.1 | ONLINE |
| 1            | 172.16.0.2 | ONLINE |
| 1            | 172.16.0.3 | ONLINE |
| 1            | 172.16.0.1 | ONLINE |
+--------------+------------+--------+
4 rows in set (0.00 sec)

Removing a backend server

It is possible to completely remove a backend server by deleting it from mysql_servers table.

Admin> SELECT hostgroup_id,hostname,status FROM mysql_servers;
+--------------+------------+--------+
| hostgroup_id | hostname   | status |
+--------------+------------+--------+
| 0            | 172.16.0.1 | ONLINE |
| 1            | 172.16.0.2 | ONLINE |
| 1            | 172.16.0.3 | ONLINE |
| 1            | 172.16.0.1 | ONLINE |
+--------------+------------+--------+
4 rows in set (0.00 sec)

Admin> DELETE FROM mysql_servers WHERE hostgroup_id=1 AND hostname IN ('172.16.0.1','172.16.0.2');
Query OK, 2 rows affected (0.00 sec)

Admin> SELECT hostgroup_id,hostname,status FROM mysql_servers;
+--------------+------------+--------+
| hostgroup_id | hostname   | status |
+--------------+------------+--------+
| 0            | 172.16.0.1 | ONLINE |
| 1            | 172.16.0.3 | ONLINE |
+--------------+------------+--------+
2 rows in set (0.00 sec)

Internally, deleting a backend or setting it OFFLINE_HARD are handled in the same way. When LOAD MYSQL SERVERS TO RUNTIME is executed, the Hostgroup_Manager will detect that a backend server was deleted and internally mark it as OFFLINE_HARD.

Backend Server Configuration

Configuring Backend MySQL Servers in ProxySQL

Copying mysql servers from memory to runtime

Copying mysql servers from memory to disk

Copying mysql servers from runtime to memory

Copying mysql servers from disk to memory

Adding a new server

Adding new servers to a hostgroup

Limiting the number of connections to a backend

Prioritizing traffic by changing the weight of a backend

Using SSL connections for a specific backend

Automatically shunning slaves with replication lag

Adding a server to two different hostgroups

Enable compression on a backend server

Gracefully disabling a backend server

Immediately disabling a backend server

Re-enabling an offline / disabled backend server

Removing a backend server

Tracking Cookies