您现在的位置是：首页 > 其他

当前栏目

proxysql运维实践

运维实践

2023-09-11 14:21:08 时间

前置基础参考：https://www.cnblogs.com/gered/p/15868767.html#autoid-4-0-0

【1】环境

当前架构

　　、

【2】主从场景

（2.1）1主2从,主库挂掉

10秒就超时（这个阈值是连接超时）反馈出来了，但如果我们的DML、select 等其他操作超过10秒是没有关系的；

主库挂了，非 mysql_query_rules 定位到从组的请求，都会连不上，我们查看一下相关日志

select * from runtime_mysql_servers;
select from_unixtime(time_start_us/1000/1000) as `datetime`,* from mysql_server_ping_log where ping_error is not null limit 10;
select from_unixtime(time_start_us/1000/1000) as `datetime`,* from mysql_server_connect_log where connect_error is not null order by time_start_us desc limit 10;
select from_unixtime(time_start_us/1000/1000) as `datetime`,* from mysql_server_read_only_log where error is not null limit 10;

结果：

admin@mysqldb 17:02:48 [(none)]>select * from runtime_mysql_servers;
+--------------+----------------+------+-----------+---------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname       | port | gtid_port | status  | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+----------------+------+-----------+---------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 10           | 192.168.148.39 | 3306 | 0         | SHUNNED | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
| 20           | 192.168.148.30 | 3306 | 0         | ONLINE  | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
| 20           | 192.168.148.27 | 3306 | 0         | ONLINE  | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
+--------------+----------------+------+-----------+---------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
3 rows in set (0.00 sec)

admin@mysqldb 17:02:52 [(none)]>select from_unixtime(time_start_us/1000/1000) as `datetime`,* from mysql_server_ping_log where ping_error is not null limit 10;
+---------------------+----------------+------+------------------+----------------------+---------------------------------------------------------+
| datetime            | hostname       | port | time_start_us    | ping_success_time_us | ping_error                                              |
+---------------------+----------------+------+------------------+----------------------+---------------------------------------------------------+
| 2022-02-16 08:56:43 | 192.168.148.39 | 3306 | 1645001803735858 | 0                    | Can't connect to MySQL server on '192.168.148.39' (115) |
| 2022-02-16 08:56:45 | 192.168.148.39 | 3306 | 1645001805750545 | 0                    | Can't connect to MySQL server on '192.168.148.39' (115) |
| 2022-02-16 08:56:47 | 192.168.148.39 | 3306 | 1645001807770656 | 0                    | Can't connect to MySQL server on '192.168.148.39' (115) |
| 2022-02-16 08:56:49 | 192.168.148.39 | 3306 | 1645001809742158 | 0                    | Can't connect to MySQL server on '192.168.148.39' (115) |
| 2022-02-16 08:56:51 | 192.168.148.39 | 3306 | 1645001811771683 | 0                    | Can't connect to MySQL server on '192.168.148.39' (115) |
| 2022-02-16 08:56:53 | 192.168.148.39 | 3306 | 1645001813752847 | 0                    | Can't connect to MySQL server on '192.168.148.39' (115) |
| 2022-02-16 08:56:55 | 192.168.148.39 | 3306 | 1645001815746628 | 0                    | Can't connect to MySQL server on '192.168.148.39' (115) |
| 2022-02-16 08:56:57 | 192.168.148.39 | 3306 | 1645001817742752 | 0                    | Can't connect to MySQL server on '192.168.148.39' (115) |
| 2022-02-16 08:56:59 | 192.168.148.39 | 3306 | 1645001819728439 | 0                    | Can't connect to MySQL server on '192.168.148.39' (115) |
| 2022-02-16 08:57:01 | 192.168.148.39 | 3306 | 1645001821729269 | 0                    | Can't connect to MySQL server on '192.168.148.39' (115) |
+---------------------+----------------+------+------------------+----------------------+---------------------------------------------------------+
10 rows in set (0.00 sec)

admin@mysqldb 17:02:52 [(none)]>select from_unixtime(time_start_us/1000/1000) as `datetime`,* from mysql_server_connect_log where connect_error is not null order by time_start_us desc limit 10;
+---------------------+----------------+------+------------------+-------------------------+---------------------------------------------------------+
| datetime            | hostname       | port | time_start_us    | connect_success_time_us | connect_error                                           |
+---------------------+----------------+------+------------------+-------------------------+---------------------------------------------------------+
| 2022-02-16 08:56:47 | 192.168.148.39 | 3306 | 1645001807770108 | 0                       | Can't connect to MySQL server on '192.168.148.39' (115) |
| 2022-02-16 08:56:45 | 192.168.148.39 | 3306 | 1645001805716648 | 0                       | Can't connect to MySQL server on '192.168.148.39' (115) |
| 2022-02-16 08:56:43 | 192.168.148.39 | 3306 | 1645001803713712 | 0                       | Can't connect to MySQL server on '192.168.148.39' (115) |
| 2022-02-16 08:56:41 | 192.168.148.39 | 3306 | 1645001801713565 | 0                       | Can't connect to MySQL server on '192.168.148.39' (115) |
+---------------------+----------------+------+------------------+-------------------------+---------------------------------------------------------+
4 rows in set (0.00 sec)

admin@mysqldb 17:02:52 [(none)]>select from_unixtime(time_start_us/1000/1000) as `datetime`,* from mysql_server_read_only_log where error is not null limit 10;
+---------------------+----------------+------+------------------+-----------------+-----------+---------------------------------------------------------------------------------------------+
| datetime            | hostname       | port | time_start_us    | success_time_us | read_only | error                                                                                       |
+---------------------+----------------+------+------------------+-----------------+-----------+---------------------------------------------------------------------------------------------+
| 2022-02-16 08:56:42 | 192.168.148.39 | 3306 | 1645001802730203 | 0               | NULL      | Lost connection to MySQL server during query                                                |
| 2022-02-16 08:56:44 | 192.168.148.39 | 3306 | 1645001804750337 | 0               | NULL      | timeout on creating new connection: Can't connect to MySQL server on '192.168.148.39' (115) |
| 2022-02-16 08:56:46 | 192.168.148.39 | 3306 | 1645001806742312 | 0               | NULL      | timeout on creating new connection: Can't connect to MySQL server on '192.168.148.39' (115) |
+---------------------+----------------+------+------------------+-----------------+-----------+---------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)

然后我们看一下最新的

select from_unixtime(time_start_us/1000/1000) as `datetime`,* from mysql_server_ping_log order by time_start_us desc limit 10;
select from_unixtime(time_start_us/1000/1000) as `datetime`,* from mysql_server_connect_log order by time_start_us  desc limit 10;
select from_unixtime(time_start_us/1000/1000) as `datetime`,* from mysql_server_read_only_log order by time_start_us desc limit 10;

从下图我们可以看出， ping 还是一直不通，而 connect 、read_only 中甚至直接剔除对主库 192.168.148.39 的连接尝试了

结合上面的所有，我们发现 ping_log 报错了 300次（2s一次的频率），而 connect_log（2秒一次的频率）只报了4次；

如下图，我们发现我们选中的参数，次数是3，所以就 connect 重试3次后，所以 connect_log 中只有4次记录是正常的；

（2.2）1主2从,主库挂掉后重新启动恢复

恢复主库后：可无缝连上，现有连接无需重连，无需断开原长连接

　　、

恢复后，我们看看 mysql server，hostgrou_id

我们可以看到，status 还是 shunned 状态

我们执行一个路由到主库的相关操作，它状态就变回来了；

（2.3）1主2从=》主库挂掉,从变主库=》1主1从

关闭主库，从库1： 192.168.148.27 执行如下代码，变成 proxysql中的主库

stop slave;
set global read_only=0;

如下图：

为什么主库变成下图这样，即在10 写组，又在20 读组？因为如下参数

mysql-monitor_writer_is_also_reader = true（当原read_only=1 的server read_only=0时，迁移一份到 writer 组的同时，也保留它的 reader 角色）

这个是为了避免故障转移后只有1个实例的时候，可以同时兼顾读写，从而业务连续；

我们再查询看看：是可以都读负载均衡分发的；写也是写到我们的新主库 192.168.148.27 中去了；

注意：proxysql 连接在任意时候都没断开过，所以主从切换等情况不影响它的长连接，也不需要重连等等；

（2.4）1主2从=》从库全部挂掉导致读操作失败

现有连接，断掉了；

如下图，我们发现从库都挂掉了

如下图，我们发现，只能写，不能读了，这是因为读规则里的所有机器都挂掉了；

所以我们要么立马所有20（读路由）的 query_rules，全部干掉（这很显然只是应急办法）

要么就是把主库额外生成一行记录加入读组20去（很显然只是应急办法）

在一开始设计的阶段就要考虑到这个问题（详细见（5）中的）

delete from mysql_query_rules where destination_hostgroup=20;

删除前是不行的，删除 20的 query rule之后然后可以查了；

（2.5）1主2从=》解决全部从库挂掉引发的不可读问题

需求：当我们所有从挂掉的时候，proxysql的读请求无法访问！！因为select 路由还是会路由到 20分组，而20分组所有实例不可用；

解决：

　　把主库也额外加入到写组去，权重设置为1，其他的设置为1000，那就99.95% 请求都不会分发到权重为1的实例上去；

1、所有规则还原到【1】中的去

2、测试权重的作用

update mysql_servers set weight=1000 where   hostgroup_id=20; 
INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (20,'192.168.148.39',3306); 
load mysql servers to runtime;
save mysql servers to disk;

查询路由如下：

我们来看看短连接查询3000次；

for i in {1..3000}; do mysql -uproxysql -p123456 -P6033 -h 192.168.148.39 -e"select @@server_id" >>1.txt; done

发现没有 393306的，也就是3000次居然没有一次分发到主库；这就是权重的原因；

我们再来试试，1W次，分到主库的查询才6次，可以理解成近2000次查询请求，才分配到1次给主库；

3、模拟2个从库都挂掉

如下图，我们可以看到 2个从库都是 shunned 状态了；无法连接的上了

查询还是可以查的，主库承接起来了查询请求；

成功；

【3】双主场景

（3.0）架构

IP server-id 　db-version description
192.168.148.39 　　393306 　　8.0.20 　　Master（gtid）、ProxySQL
192.168.148.27 　　273306 　　8.0.22 　　Master（gtid）

（3.1）默认的=》读写分离负载均衡

这里权重为1，都是一样的，权重只在相同主机组内有效

　　其他的 mysql_replication_hostgroups 、 mysql_query_rules 均为空；

　　之前有的mysql servers相关信息，删除后才加的上图数据，连接会断，我们来看看连接；

读操作：

写操作：

　　如下图，我做了插入是个表 test_1 ~ test_10；

　　结果如下图：我们可以发现，建表语句也是相对均匀的分布到了2个主库上去；

【总结】主从模式的设计与规划

（1）解决 mysql 唯一的从库变成主库后，没有 reader 引发的不可读问题

mysql-monitor_writer_is_also_reader = true

　　在read_only=1 的读实例变成主后（变成 writer 后），依然保留一份它作为 reader

　　1主1从的时候：

　　　　为了防止主库挂掉后，从库变成新主库导致没有 read_only=1 的查询实例，所以加上这个参数就可以解决这个问题，新主库的单实例读写均可；

（2）解决 mysql所有从库挂掉，没有 reader 引发的不可读问题

mysql_servers 下的 weight 字段；权重的设置（权重只作用在同一个hostgroup中有效）

　　权重的设置，非多主的情况，主库也要额外起 mysql_servers 里面的一行记录加入读组，读写分离的问题可以通过权重来解决；

　　大概可以换算成，比如我们现在是 2个从库的权重是1000，主库的只读配置的权重是 1，那么主库承接请求的可能性就是 1/(1000+1000+1);

但在一主多从的情况下，要结合 mysql-monitor_writer_is_also_reader 来查看使用；

（3）同个事务内的SQL，禁止被路由到不同节点上

核心参数是 msyql_users 中的 transaction_persistent: 1

（4）自动回避复制延迟较大的节点

如果 mysql_servers 表将max_replication_lag设置为非零值，则Monitor模块会定期检查复制延迟

下图中，当172.16.0.3的复制延迟超过了30秒会自动回避，设置max_replication_lag = 0，代表不检查复制延迟。

（5）禁用后端Server

mysql_servers：

status字段：

　　ONLINE：在线

　　OFFLINE_SOFT：不会影响当前的活动事务和连接，但不会向该节点发送新流量。

　　OFFLINE_HARD：所有当前请求将立即终止，并且不会发送新请求。

所以要禁用的话，看情况修改成 OFFLINE_SOFT 或者 OFFLINE_HARD

与 delete 比较： 在内部，删除后端或将其设置为OFFLINE_HARD的方式相同。

　　　　　　　　当执行LOAD MYSQL SERVERS TO RUNTIME时，Hostgroup_Manager将检测到后端服务器已被删除，并在内部将其标记为OFFLINE_HARD

（6）mysql_query_rules 语句替换，语句禁用

《1》语句禁用=》不允许 select * from test1 语句，出现则报错：

INSERT INTO mysql_query_rules (rule_id, active, match_pattern, error_msg,destination_hostgroup,apply) VALUES (2,1,'^SELECT \* from test1$','Query not allowed',10,1);

LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;

同理，我们可以对 drop/delete 等等操作都做此限制；

《2》语句替换=》Delete 变成了 select

INSERT INTO mysql_query_rules (rule_id, active, match_pattern,replace_pattern, apply) VALUES (4,1,'^DELETE FROM test1$','select * from test.test1',1);
load mysql query rules to runtime;

但这个其实规则很严格，要正则适配好，比如我们这里是

^DELETE FROM test1$

那么如果我 DELETE FROM test1; 就是from后面额外多了一个空格，就匹配不到了；

【基础运维】

（1）如何防止脑裂----主库服务挂掉后重启恢复

场景：如果是服务器挂掉，或者是主库的 Mysql服务挂掉；

　　重启后因为故障转移，它作为老主库应该成为从库出现，但没加read_only 则会延续之前的写规则到老主库；

所以建议，在所有主库、从库中加入 read_only=1 参数，以便出现脑裂

（2）如何防止读请求到一个落后很多的从库上去？（回避落后大的节点）

场景：

　　1、如果从库因为某些原因阻塞，导致主从同步落后了（这时候访问就是过期数据）

　　2、如果主从切换后，这期间过去1小时，原主库变为新主库的从库，但落后1小时的数据，要追，这个时候也不能访问该从库（因为是过期数据）

如果 mysql_servers 表将max_replication_lag设置为非零值，则Monitor模块会定期检查复制延迟

下图中，当172.16.0.3的复制延迟超过了30秒会自动回避，设置max_replication_lag = 0，代表不检查复制延迟。

猜你喜欢

linux下cp目录时排除一个或者多个目录的方法
打开和关闭输入法编辑器
spring boot:使用log4j2做异步日志打印(spring boot 2.3.1)
针对域控制器的一种升级测试方案介绍
支持向量机libsvm实战入门
ML之LoR：kaggle比赛之利用titanic(泰坦尼克号)数据集建立LoR模型对每个人进行获救是否预测
【刷题】面筋-测开-所有的软件缺陷都能修复吗？所有的软件缺陷都要修复吗？
介绍一个统计各个网站访问时长的Chrome扩展 - Rooster
canal 配置多个监听推送到不同mq
Python: 在序列上执行聚集函数(比如sum() , min() , max() )
大数据颠覆金融业交易模式
家政服务小程序实战教程13-接入客服
oracle profile介绍
Atitit 项目范围管理目录 1. 应该包含下面过程：启动、范围计划、范围定义、范围核实及范围变更控制1 1.1. 项目范围管理的五个过程1 2. 启动过程1 2.1. 项目章程（如质量、
[AWS SAP] Exam Tips 2 -- Continues Improvement for Existing Solutions
安全测试===Mysql 注入技巧学习 MySQL注入技巧（1）
连续差相同的数字-dfs
测试基础 Jmeter基础使用

相关主题

运维脚本
Ansible自动化运维
linux运维常用脚本
Zabbix运维---监控
运维题目（十）
运维题目（六）
高级运维
OpenStack运维
MYSQL 运维
Kafka运维

zl程序教程

当前栏目

proxysql运维实践

【1】环境

【2】主从场景

（2.1）1主2从,主库挂掉

（2.2）1主2从,主库挂掉后重新启动恢复

（2.3）1主2从=》主库挂掉,从变主库=》1主1从

（2.4）1主2从=》从库全部挂掉导致读操作失败

（2.5）1主2从=》解决全部从库挂掉引发的不可读问题

【3】双主场景

（3.0）架构

（3.1）默认的=》读写分离负载均衡

【总结】主从模式的设计与规划

（1）解决 mysql 唯一的从库变成主库后，没有 reader 引发的不可读问题

（2）解决 mysql所有从库挂掉，没有 reader 引发的不可读问题

（3）同个事务内的SQL，禁止被路由到不同节点上

（4）自动回避复制延迟较大的节点

（5）禁用后端Server

（6）mysql_query_rules 语句替换，语句禁用

【基础运维】

（1）如何防止脑裂----主库服务挂掉后重启恢复

（2）如何防止读请求到一个落后很多的从库上去？（回避落后大的节点）

相关文章