MySQL Server Configuration

Configuring Backend MySQL Servers in ProxySQL

ProxySQL主要是通过mysql_servers来配置MySQL servers，有时候可能会用到mysql_replication_hostgroups

备注：在读下面内容之前，确保理解multi-layer configuration system，或者看我前面的文章

注意：

  • 更新mysql_servers 和mysql_replication_hostgroups表后，如果不执行LOAD MYSQL SERVERS TO RUNTIME，当前配置并不会生效
  • 如果不执行SAVE MYSQL SERVERS TO DISK，当前配置并不会被持久化，服务重启后数据将丢失

多层配置系统略过，详细内容参考multi-layer configuration system

Adding a new server

要添加一个后台MySQL server，insert一条新纪录到mysql_servers表即可，这张表部分列都带有默认值

下面的操作是新增一个MySQL server，除hostname外其他都采用默认值

 1 Admin> SELECT * FROM mysql_servers;
 2 Empty set (0.00 sec)
 3 
 4 Admin> INSERT INTO mysql_servers (hostname) VALUES ('172.16.0.1');
 5 Query OK, 1 row affected (0.00 sec)
 6 
 7 Admin> SELECT * FROM mysql_serversG
 8 *************************** 1. row ***************************
 9        hostgroup_id: 0
10            hostname: 172.16.0.1
11                port: 3306
12              status: ONLINE
13              weight: 1
14         compression: 0
15     max_connections: 1000
16 max_replication_lag: 0
17             use_ssl: 0
18      max_latency_ms: 0
19             comment:
20 1 row in set (0.00 sec)
View Code

Adding new servers to a hostgroup

 1 Admin> SELECT hostgroup_id,hostname FROM mysql_servers;
 2 +--------------+------------+
 3 | hostgroup_id | hostname   |
 4 +--------------+------------+
 5 | 0            | 172.16.0.1 |
 6 +--------------+------------+
 7 1 row in set (0.00 sec)
 8 
 9 Admin> INSERT INTO mysql_servers (hostgroup_id, hostname) VALUES (1, '172.16.0.2'), (1,'172.16.0.3');
10 Query OK, 2 rows affected (0.00 sec)
11 
12 Admin> SELECT hostgroup_id,hostname FROM mysql_servers;
13 +--------------+------------+
14 | hostgroup_id | hostname   |
15 +--------------+------------+
16 | 0            | 172.16.0.1 |
17 | 1            | 172.16.0.2 |
18 | 1            | 172.16.0.3 |
19 +--------------+------------+
20 3 rows in set (0.00 sec)
View Code

Limiting the number of connections to a backend

 1 Admin> SELECT hostgroup_id,hostname,max_connections FROM mysql_servers;
 2 +--------------+------------+-----------------+
 3 | hostgroup_id | hostname   | max_connections |
 4 +--------------+------------+-----------------+
 5 | 0            | 172.16.0.1 | 1000            |
 6 | 1            | 172.16.0.2 | 1000            |
 7 | 1            | 172.16.0.3 | 1000            |
 8 +--------------+------------+-----------------+
 9 3 rows in set (0.00 sec)
10 
11 Admin> UPDATE mysql_servers SET max_connections=10 WHERE hostname='172.16.0.2';
12 Query OK, 1 row affected (0.00 sec)
13 
14 Admin> SELECT hostgroup_id,hostname,max_connections FROM mysql_servers;
15 +--------------+------------+-----------------+
16 | hostgroup_id | hostname   | max_connections |
17 +--------------+------------+-----------------+
18 | 0            | 172.16.0.1 | 1000            |
19 | 1            | 172.16.0.2 | 10              |
20 | 1            | 172.16.0.3 | 1000            |
21 +--------------+------------+-----------------+
22 3 rows in set (0.00 sec)
View Code

Prioritizing traffic by changing the weight of a backend

只有在同一个hostgroup内的server才互相区分权重

 1 Admin> SELECT hostgroup_id,hostname,weight FROM mysql_servers;
 2 +--------------+------------+--------+
 3 | hostgroup_id | hostname   | weight |
 4 +--------------+------------+--------+
 5 | 0            | 172.16.0.1 | 1      |
 6 | 1            | 172.16.0.2 | 1      |
 7 | 1            | 172.16.0.3 | 1      |
 8 +--------------+------------+--------+
 9 3 rows in set (0.00 sec)
10 
11 Admin> UPDATE mysql_servers SET weight=1000 WHERE hostname NOT IN ('172.16.0.2', '172.16.0.1') AND hostgroup_id=1;
12 Query OK, 1 row affected (0.00 sec)
13 
14 Admin> SELECT hostgroup_id,hostname,weight FROM mysql_servers;
15 +--------------+------------+--------+
16 | hostgroup_id | hostname   | weight |
17 +--------------+------------+--------+
18 | 0            | 172.16.0.1 | 1      |
19 | 1            | 172.16.0.2 | 1      |
20 | 1            | 172.16.0.3 | 1000   |
21 +--------------+------------+--------+
22 3 rows in set (0.00 sec)
View Code

Using SSL connections for a specific backend

下面是一个配置SSL连接的例子，不过不是一个完整的全局配置，详细完整的配置参考here

1 Admin> SELECT hostgroup_id,hostname,use_ssl FROM mysql_servers;
2 +--------------+------------+---------+
3 | hostgroup_id | hostname   | use_ssl |
4 +--------------+------------+---------+
5 | 0            | 172.16.0.1 | 1       |
6 | 1            | 172.16.0.2 | 0       |
7 | 1            | 172.16.0.3 | 0       |
8 +--------------+------------+---------+
9 3 rows in set (0.00 sec)
View Code

Automatically shunning slaves with replication lag

如果max_replication_lag设置成非零值，监控模块将会监控后台mysql server的复制延迟

 1 Admin> SELECT hostgroup_id,hostname,max_replication_lag FROM mysql_servers;
 2 +--------------+------------+---------------------+
 3 | hostgroup_id | hostname   | max_replication_lag |
 4 +--------------+------------+---------------------+
 5 | 0            | 172.16.0.1 | 0                   |
 6 | 1            | 172.16.0.2 | 0                   |
 7 | 1            | 172.16.0.3 | 0                   |
 8 +--------------+------------+---------------------+
 9 3 rows in set (0.00 sec)
10 
11 Admin> UPDATE mysql_servers SET max_replication_lag=30 WHERE hostname='172.16.0.3';
12 Query OK, 1 row affected (0.00 sec)
13 
14 Admin> SELECT hostgroup_id,hostname,max_replication_lag FROM mysql_servers;
15 +--------------+------------+---------------------+
16 | hostgroup_id | hostname   | max_replication_lag |
17 +--------------+------------+---------------------+
18 | 0            | 172.16.0.1 | 0                   |
19 | 1            | 172.16.0.2 | 0                   |
20 | 1            | 172.16.0.3 | 30                  |
21 +--------------+------------+---------------------+
22 3 rows in set (0.00 sec)
View Code

就上面的这个配置，如果复制延迟超过30秒，连接将不再被路由到172.16.0.3，如果max_replication_lag设置成0(SET max_replication_lag=0)，那么监控模块将不再检查延迟，详见mysql-monitor_slave_lag_when_null

Adding a server to two different hostgroups

mysql_servers表的主键被定义成PRIMARY KEY (hostgroup_id, hostname, port)，也就是说同一个后台的MySQL  sever可以有两个不同的hostgroups，其实在很多场景会用到这种配置。

比如，在一个一主两从的复制结构中，如果从库不可用了（挂了，或者延迟太高），那么读就可以路由到master。

看下面的例子

 1 Admin> SELECT hostgroup_id,hostname,weight,max_replication_lag FROM mysql_servers;
 2 +--------------+------------+--------+---------------------+
 3 | hostgroup_id | hostname   | weight | max_replication_lag |
 4 +--------------+------------+--------+---------------------+
 5 | 0            | 172.16.0.1 | 1      | 0                   |
 6 | 1            | 172.16.0.2 | 1      | 0                   |
 7 | 1            | 172.16.0.3 | 1000   | 30                  |
 8 +--------------+------------+--------+---------------------+
 9 3 rows in set (0.00 sec)
10 
11 Admin> INSERT INTO mysql_servers(hostgroup_id,hostname) VALUES (1,'172.16.0.1');
12 Query OK, 1 row affected (0.00 sec)
13 
14 Admin> SELECT hostgroup_id,hostname,weight,max_replication_lag FROM mysql_servers;
15 +--------------+------------+--------+---------------------+
16 | hostgroup_id | hostname   | weight | max_replication_lag |
17 +--------------+------------+--------+---------------------+
18 | 0            | 172.16.0.1 | 1      | 0                   |
19 | 1            | 172.16.0.2 | 1      | 0                   |
20 | 1            | 172.16.0.3 | 1000   | 30                  |
21 | 1            | 172.16.0.1 | 1      | 0                   |
22 +--------------+------------+--------+---------------------+
23 4 rows in set (0.00 sec)
24 
25 Admin> UPDATE mysql_servers SET max_replication_lag=30, weight=1000;
26 Query OK, 4 rows affected (0.00 sec)
27 
28 Admin> UPDATE mysql_servers SET weight=1 WHERE hostname='172.16.0.1' AND hostgroup_id=1;
29 Query OK, 1 row affected (0.00 sec)
30 
31 Admin> SELECT hostgroup_id,hostname,weight,max_replication_lag FROM mysql_servers;
32 +--------------+------------+--------+---------------------+
33 | hostgroup_id | hostname   | weight | max_replication_lag |
34 +--------------+------------+--------+---------------------+
35 | 0            | 172.16.0.1 | 1000   | 30                  |
36 | 1            | 172.16.0.2 | 1000   | 30                  |
37 | 1            | 172.16.0.3 | 1000   | 30                  |
38 | 1            | 172.16.0.1 | 1      | 30                  |
39 +--------------+------------+--------+---------------------+
40 4 rows in set (0.00 sec)
View Code

在上面的例子，我们将HG1组配置成读，那么99.95%的连接负载将会路由到172.16.0.2 和172.16.0.3,0.05%的被路由到172.16.0.1.如果172.16.0.2 and 172.16.0.3都不可用了，那么所有的连接都会被路由到172.16.0.1

注意：max_replication_lag只是对slaves有用，如果没有从库，监控将不会监控延迟

mysql_servers.compression设置成非零值即可启用压缩传输，注意只有这个配置被加载到runtime后才被初始化的连接才会拥有压缩传输的特性

 1 Admin> SELECT hostgroup_id,hostname,compression FROM mysql_servers;
 2 +--------------+------------+-------------+
 3 | hostgroup_id | hostname   | compression |
 4 +--------------+------------+-------------+
 5 | 0            | 172.16.0.1 | 0           |
 6 | 1            | 172.16.0.2 | 0           |
 7 | 1            | 172.16.0.3 | 0           |
 8 | 1            | 172.16.0.1 | 0           |
 9 +--------------+------------+-------------+
10 4 rows in set (0.00 sec)
11 
12 Admin> UPDATE mysql_servers SET compression=1 WHERE hostname='172.16.0.2' AND hostgroup_id=1;
13 Query OK, 1 row affected (0.00 sec)
14 
15 Admin> SELECT hostgroup_id,hostname,compression FROM mysql_servers;
16 +--------------+------------+-------------+
17 | hostgroup_id | hostname   | compression |
18 +--------------+------------+-------------+
19 | 0            | 172.16.0.1 | 0           |
20 | 1            | 172.16.0.2 | 1           |
21 | 1            | 172.16.0.3 | 0           |
22 | 1            | 172.16.0.1 | 0           |
23 +--------------+------------+-------------+
24 4 rows in set (0.00 sec)
View Code

Gracefully disabling a backend server

将status设置成OFFLINE_SOFT能让后台server优雅的停用掉，这意味着当前活跃的事务和连接任然有效，只是新的连接将不会被路由到这个节点

 1 Admin> SELECT hostgroup_id,hostname,status FROM mysql_servers;
 2 +--------------+------------+--------+
 3 | hostgroup_id | hostname   | status |
 4 +--------------+------------+--------+
 5 | 0            | 172.16.0.1 | ONLINE |
 6 | 1            | 172.16.0.2 | ONLINE |
 7 | 1            | 172.16.0.3 | ONLINE |
 8 | 1            | 172.16.0.1 | ONLINE |
 9 +--------------+------------+--------+
10 4 rows in set (0.00 sec)
11 
12 Admin> UPDATE mysql_servers SET status='OFFLINE_SOFT' WHERE hostname='172.16.0.2';
13 Query OK, 1 row affected (0.00 sec)
14 
15 Admin> SELECT hostgroup_id,hostname,status FROM mysql_servers;
16 +--------------+------------+--------------+
17 | hostgroup_id | hostname   | status       |
18 +--------------+------------+--------------+
19 | 0            | 172.16.0.1 | ONLINE       |
20 | 1            | 172.16.0.2 | OFFLINE_SOFT |
21 | 1            | 172.16.0.3 | ONLINE       |
22 | 1            | 172.16.0.1 | ONLINE       |
23 +--------------+------------+--------------+
24 4 rows in set (0.00 sec)
View Code

Immediately disabling a backend server

将mysql_serves表中对应记录的status字段值设置成OFFLINE_HARD即可，这时包括当前连接也会立即断开

 1 Admin> SELECT hostgroup_id,hostname,status FROM mysql_servers;
 2 +--------------+------------+--------------+
 3 | hostgroup_id | hostname   | status       |
 4 +--------------+------------+--------------+
 5 | 0            | 172.16.0.1 | ONLINE       |
 6 | 1            | 172.16.0.2 | OFFLINE_SOFT |
 7 | 1            | 172.16.0.3 | ONLINE       |
 8 | 1            | 172.16.0.1 | ONLINE       |
 9 +--------------+------------+--------------+
10 4 rows in set (0.00 sec)
11 
12 Admin> UPDATE mysql_servers SET status='OFFLINE_HARD' WHERE hostname='172.16.0.1' AND hostgroup_id=1;
13 Query OK, 1 row affected (0.00 sec)
14 
15 Admin> SELECT hostgroup_id,hostname,status FROM mysql_servers;
16 +--------------+------------+--------------+
17 | hostgroup_id | hostname   | status       |
18 +--------------+------------+--------------+
19 | 0            | 172.16.0.1 | ONLINE       |
20 | 1            | 172.16.0.2 | OFFLINE_SOFT |
21 | 1            | 172.16.0.3 | ONLINE       |
22 | 1            | 172.16.0.1 | OFFLINE_HARD |
23 +--------------+------------+--------------+
24 4 rows in set (0.00 sec)
View Code

Re-enabling an offline / disabled backend server

将status改为ONLINE即可

 1 Admin> SELECT hostgroup_id,hostname,status FROM mysql_servers;
 2 +--------------+------------+--------------+
 3 | hostgroup_id | hostname   | status       |
 4 +--------------+------------+--------------+
 5 | 0            | 172.16.0.1 | ONLINE       |
 6 | 1            | 172.16.0.2 | OFFLINE_SOFT |
 7 | 1            | 172.16.0.3 | ONLINE       |
 8 | 1            | 172.16.0.1 | OFFLINE_HARD |
 9 +--------------+------------+--------------+
10 4 rows in set (0.00 sec)
11 
12 Admin> UPDATE mysql_servers SET status='ONLINE' WHERE status NOT IN ('ONLINE');
13 Query OK, 2 rows affected (0.00 sec)
14 
15 Admin> SELECT hostgroup_id,hostname,status FROM mysql_servers;
16 +--------------+------------+--------+
17 | hostgroup_id | hostname   | status |
18 +--------------+------------+--------+
19 | 0            | 172.16.0.1 | ONLINE |
20 | 1            | 172.16.0.2 | ONLINE |
21 | 1            | 172.16.0.3 | ONLINE |
22 | 1            | 172.16.0.1 | ONLINE |
23 +--------------+------------+--------+
24 4 rows in set (0.00 sec)
View Code

Removing a backend server

将mysql_servers中对应记录delete掉即可

 1 Admin> SELECT hostgroup_id,hostname,status FROM mysql_servers;
 2 +--------------+------------+--------+
 3 | hostgroup_id | hostname   | status |
 4 +--------------+------------+--------+
 5 | 0            | 172.16.0.1 | ONLINE |
 6 | 1            | 172.16.0.2 | ONLINE |
 7 | 1            | 172.16.0.3 | ONLINE |
 8 | 1            | 172.16.0.1 | ONLINE |
 9 +--------------+------------+--------+
10 4 rows in set (0.00 sec)
11 
12 Admin> DELETE FROM mysql_servers WHERE hostgroup_id=1 AND hostname IN ('172.16.0.1','172.16.0.2');
13 Query OK, 2 rows affected (0.00 sec)
14 
15 Admin> SELECT hostgroup_id,hostname,status FROM mysql_servers;
16 +--------------+------------+--------+
17 | hostgroup_id | hostname   | status |
18 +--------------+------------+--------+
19 | 0            | 172.16.0.1 | ONLINE |
20 | 1            | 172.16.0.3 | ONLINE |
21 +--------------+------------+--------+
22 2 rows in set (0.00 sec)
View Code

delete和set OFFLINE_HARD实质上一样的，当执行了LOAD MYSQL SERVERS TO RUNTIME后，Hostgroup_Manager将会发现后台有服务被delete掉了并且内部会将他标记为OFFLINE_HARD。

参考文档

【推广】 免费学中医，健康全家人
原文地址：https://www.cnblogs.com/geek-ace/p/9555315.html