MySQL Server Configuration

在ProxySQL中配置后端MySQL Server

可以在mysql_servers表和mysql_replication_hostgroups表(可选)中配置后端的MySQL Servers。

注意：在阅读下面内容之前，请确定你已经理解了ProxySQL中的多层次配置系统。
特别是：

修改mysql_servers表和mysql_replication_hostgroups表后，在没有运行LOAD MYSQL SERVERS TO RUNTIME时是不会生效的。
修改mysql_servers表和mysql_replication_hostgroups表后，在没有运行SAVE MYSQL SERVERS TO DISK时，ProxySQL的重启或崩溃都会丢失这些修改。**

也就是说，修改mysql_servers和mysql_replication_hostgroups表后，需要运行LOAD MYSQL SERVERS TO RUNTIME才能让这些修改生效，需要运行SAVE MYSQL SERVERS TO DISK才能持久化这些修改。

重要：下面的所有例子中，都需要执行LOAD MYSQL SERVERS TO RUNTIME才生效，都需要执行SAVE MYSQL SERVERS TO DISK才永久保存。

将MySQL Servers从内存数据库拷贝到runtime

例如，要将MySQL Servers从内存数据库拷贝到runtime数据结构：

Admin> LOAD MYSQL SERVERS TO RUNTIME;

等价写法：

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

将MySQL Servers从内存数据库持久化到磁盘数据库

例如，要将MySQL Servers从内存数据库持久化到磁盘数据库：

Admin> SAVE MYSQL SERVERS TO DISK;

等价写法：

SAVE MYSQL SERVERS FROM MEM
SAVE MYSQL SERVERS FROM MEMORY

将MySQL Servers从runtime数据结构拷贝到内存数据库

例如，将MySQL Servers从runtime数据结构拷贝到内存数据库：

Admin> SAVE MYSQL SERVERS TO MEMORY;

等价写法：

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

将MySQL Servers从磁盘数据结构拷贝到内存数据库

例如，要将MySQL Servers从磁盘数据结构拷贝到内存数据库：

Admin> LOAD MYSQL SERVERS TO MEMORY;

等价写法：

LOAD MYSQL SERVERS TO MEM
LOAD MYSQL SERVERS FROM DISK

添加一个新的后端MySQL

向mysql_servers表中插入一个新行，就表示添加一个新的后端MySQL节点。
需要注意，这个表中有几个字段带有默认值属性。

例如，下面完全使用默认配置来添加一个后端节点：

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)

(译注：可见，如果不需要将后端节点分组，添加时只需指定一个hostname字段即可。同时也发现，不指定主机组的后端，默认都属于id=0的主机组)

添加新的后端到主机组中

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)

限制某后端节点的连接数量

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)

通过修改权重来确定流量的优先级

权重属性只在主机组中才有效。

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)

和某个后端指定使用SSL连接

下面的示例演示了如何配置和后端建立SSL连接。此处没有说明如何配置全局的SSL，全局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)

自动避开拖后腿的slave节点

如果设置了某个节点的max_replication_lag为非0值，则Monitor模块会对该节点是否拖后腿(replication lag)做常规检查。如果该字段设置为0，则Monitor模块不会做replication lag的检查。

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)

上面的配置中，如果 172.16.0.3 相比master的数据延迟了30秒，则ProxySQL会暂时自动避开(忽略)该slave节点。

注意，如果将该字段设置为0，那么后端slave节点永远不会被自动避开，即使这个后端节点的复制线程工作不正常(译注：例如，SQL线程未开启，IO线程未开启，未连接上master等)，详见：mysql-monitor_slave_lag_when_null。

将一个后端节点添加到不同主机组中

mysql_servers表的主键字段定义方式为PRIMARY KEY (hostgroup_id,hostname,port)。这意味着同一个后端节点可以加入不同主机组中。

至于为什么要让一个节点加入多个组，有多种原因。例如，在一个复制拓扑结构中，有1个master节点，2个slave节点，你希望在slave故障时(例如它们宕机、或者拖后腿而被ProxySQL避开等)可以向master节点发送读请求。

(译注：为了让读尽量路由到slave上，可以将那个跨多组的节点(可能是master)在组中的权重设置的足够小)

一个示例：

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)

上面的例子中，如果我们让hostgroup_id=1的组负责read请求，那么99.95%的读请求都会均衡到 172.16.0.2 和 172.16.0.3 上，只有0.05%的读操作才会路由到 172.16.0.1 上。如果 172.16.0.2 和 172.16.0.3 都不可用了， 172.16.0.1 将负责所有的read请求。

注意：max_replication_lag只对slave节点有效。如果某后端MySQL未启用复制功能，则Monitor模块不会做任何和复制相关的动作。

压缩传输到后端节点的数据

只需设置mysql_servers.compression字段为一个非0值即可。注意，启用压缩功能(已load到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禁用一个后端节点

要graceful禁用一个后端节点，需要修改该后端节点的status字段值为OFFLINE_SOFT，这表示该节点正在处理的事务会继续执行，但ProxySQL不会向该节点发送新的请求。

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)

直接禁用后端节点

将status字段的值设置为OFFLINE_HARD，即表示强制禁用，可认为直接杀掉了该节点。该节点上正在执行的事务会停止，也不会向其发送任何请求。

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)

重新启用被禁用的后端节点

直接将status字段的值改回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)

移除一个后端节点

删除mysql_servers表中的行可以完全移除对应的后端节点：

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)

在ProxySQL的内部，删除一个后端节点或将它设置为OFFLINE_HARD的处理方式是一样的。当执行LOAD MYSQL SERVERS TO RUNTIME后，主机组管理器(Hostgroup Manager)将探测到该节点已被移除，并在内部将器标记为OFFLINE_HARD。

Translator：马龙帅
Blog：http://www.cnblogs.com/f-ck-need-u

以下是无关紧要或正处于测试中的内容，不翻译
SSL configuration(不翻译)
HTTP Web Server(不翻译)
Clickhouse Support(不翻译)
Mirroring(不翻译)
探测内存泄漏(不翻译)
错误代码(不翻译)
程序包制作(不翻译)

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

MySQL Server Configuration

在ProxySQL中配置后端MySQL Server

将MySQL Servers从内存数据库拷贝到runtime

将MySQL Servers从内存数据库持久化到磁盘数据库

将MySQL Servers从runtime数据结构拷贝到内存数据库

将MySQL Servers从磁盘数据结构拷贝到内存数据库

添加一个新的后端MySQL

添加新的后端到主机组中

限制某后端节点的连接数量

通过修改权重来确定流量的优先级

和某个后端指定使用SSL连接

自动避开拖后腿的slave节点

将一个后端节点添加到不同主机组中

压缩传输到后端节点的数据

Gracefully禁用一个后端节点

直接禁用后端节点

重新启用被禁用的后端节点

移除一个后端节点

Clone this wiki locally