Skip to content

MySQL Server Configuration

Jump to bottom
Nick edited this page Dec 11, 2017 · 8 revisions

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.

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 column 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
             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 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 check 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 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 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 send 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 send 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 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.

 

Clone this wiki locally