mysql server configuration_MySQL Server Configuration

最新推荐文章于 2023-10-13 18:52:25 发布

730267

最新推荐文章于 2023-10-13 18:52:25 发布

阅读量280

点赞数

文章标签： mysql server configuration

本文链接：https://blog.csdn.net/weixin_32506761/article/details/113434429

版权

在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;

+--------------+------------+--------+---------------------+

+--------------+------------+--------+---------------------+

| 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;

+--------------+------------+--------+---------------------+

+--------------+------------+--------+---------------------+

| 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;

+--------------+------------+--------+---------------------+

+--------------+------------+--------+---------------------+

| 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。

730267

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
mysql server configuration_MySQL Server Configuration

在ProxySQL中配置后端MySQL Server可以在mysql_servers表和mysql_replication_hostgroups表(可选)中配置后端的MySQL Servers。注意：在阅读下面内容之前，请确定你已经理解了ProxySQL中的多层次配置系统。特别是：修改mysql_servers表和mysql_replication_hostgroups表后，在没有运行LOAD M...
复制链接

扫一扫