关于对MySQL高可用架构进行SQL阻断实现

合集 - MySQL(3)

1.MySQL GTID 主从复制错误修复方法2023-06-142.在线开启gtid功能方法2023-06-14

3.关于对MySQL高可用架构进行SQL阻断实现2023-06-30

收起

通过Proxy SQL 实现SQL阻断

简介

由于安全管理，对高危SQL进行阻断

以下是测试环境MySQL物理架构图：

配置

在架构原有的基础上进行配置，通过proxy SQL进行SQL阻断配置，如下：

-- 配置ProxySQL到MySQL各类账户（如存在则忽略该步）

 

# 步骤一 在proxySQL中添加MySQL用户
insert into mysql_users(username,password,default_hostgroup) values('alert_test','mysql',10); 
# 步骤二 对该用户开启事务持续功能
update mysql_users set transaction_persistent=1 where username='alert_test'; 
# 步骤三 加载配置
load mysql users to runtime; 
# 步骤四 保存配置
save mysql users to disk; 
# 步骤五 校验
select * from mysql_users;

 

必填项：

Username 用户名

Password 密码

Transaction_persistent 事务持续功能（0/1，关闭/开启）

选填项：

Default_hostgroup 默认组

 

清理之前执行历史SQL缓存，让porxySQL重新读取规则表使规则生效

 

select * from stats_mysql_query_digest_reset where 1=0;

 

添加新规则

INSERT INTO mysql_query_rules(active,username,match_pattern,replace_pattern,apply) VALUES (1, 'alert_test', '^DROP\s+DATABASE\s+\w+.*$', 'SELECT 1',1);
INSERT INTO mysql_query_rules(active,username,match_pattern,replace_pattern,apply) VALUES (1, 'alert_test', '^ALTER\s+USER\s+\w+\s+IDENTIFIED\s+BY\s+.*$', 'SELECT 2',1);
# 加载、保存配置
LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;

对于mysql_query_rules表字段解释：

Active 使此规则生效于查询处理模块处理；

Username 规则生效的用户；

Match_pattern 匹配模式（正则匹配）；

Replace_pattern 替换模式（标准输出）；

Apply 规则应用；

 

* 上述正则解释：

^ 表示开头匹配

\s 表示匹配单个空白

\w 表示匹配单个单词

+ 表示匹配多个，举例：\s+ 表示匹配多个空白

.*$ 其中“.”表示任意一个字符，“*”表示其前面元素零个或多个，“$”表示结尾；

 

测试及验证

通过连接proxy SQL访问MySQL：

 

[root@testOS ~]# mysql -ualert_test -pmysql -h192.168.68.136 -P6033
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 68
Server version: 5.5.30 (ProxySQL)

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>
mysql> drop database t1;
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.00 sec)

mysql>
mysql> ALTER USER alert_test IDENTIFIED BY 'mysql';
+---+
| 2 |
+---+
| 2 |
+---+
1 row in set (0.00 sec)

mysql> 

 

返回至proxy SQL中查询改写记录

 

mysql> select * from stats_mysql_query_digest_reset;
+-----------+--------------------+------------+----------------+--------------------+----------------------------------+------------+------------+------------+----------+----------+----------+-------------------+---------------+
| hostgroup | schemaname         | username   | client_address | digest             | digest_text                      | count_star | first_seen | last_seen  | sum_time | min_time | max_time | sum_rows_affected | sum_rows_sent |
+-----------+--------------------+------------+----------------+--------------------+----------------------------------+------------+------------+------------+----------+----------+----------+-------------------+---------------+
| 10        | information_schema | alert_test |                | 0x1C46AE529DD5A40E | SELECT ?                         | 2          | 1688027418 | 1688027444 | 579      | 225      | 354      | 0                 | 2             |
| 10        | information_schema | alert_test |                | 0x02033E45904D3DF0 | show databases                   | 1          | 1688027407 | 1688027407 | 5393     | 5393     | 5393     | 0                 | 6             |
| 10        | information_schema | alert_test |                | 0x226CD90D52A2BA0B | select @@version_comment limit ? | 1          | 1688027392 | 1688027392 | 0        | 0        | 0        | 0                 | 0             |
+-----------+--------------------+------------+----------------+--------------------+----------------------------------+------------+------------+------------+----------+----------+----------+-------------------+---------------+
3 rows in set (0.00 sec)

mysql>  

 

至此

已经实现成功。