有些情况,我们不希望业务对某张表进行写操作,而MySQL不支持针对某张表设置只读,通过账号设置也不理想,因为账号可能要访问多张表,而不是这一张,账号若只分配只读权限就会所有表的访问请求都会只读。

但ProxySQL中间件可以通过设置query rule,阻止应用对某张表的写入,这很好地满足了我们的需求!

设置规则,不允许应用对test库下表st1进行写入:

1
2
3
4
5
insert into mysql_query_rules (rule_id,active,schemaname, match_digest ,error_msg,apply)values(3,1,'test','^insert.*st1.*','No write is allowed on this table!',1);

insert into mysql_query_rules (rule_id,active,schemaname, match_digest ,error_msg,apply)values(4,1,'test','^update.*st1.*','No write is allowed on this table!',1);

insert into mysql_query_rules (rule_id,active,schemaname, match_digest ,error_msg,apply)values(5,1,'test','^delete.*st1.*','No write is allowed on this table!',1);

三条规则有些复杂,合并成一条规则更容易维护:

1
insert into mysql_query_rules (rule_id,active,schemaname, match_digest ,error_msg,apply)values(3,1,'test','(^insert|^update|^delete).+st1.+','No write is allowed on this table!',1);

当然,如果你只希望拒绝增删改中的一项操作,可取上面三条规则中的一条!
登陆ProxySQL应用端界面,执行相关SQL验证

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
mysql> select * from st1;
+----+
| id |
+----+
| 2 |
| 3 |
| 10 |
+----+
3 rows in set (0.00 sec)

mysql> update st1 set id =11 where id =10;
ERROR 1148 (42000): No write is allowed on this table!
mysql> delete from st1;
ERROR 1148 (42000): No write is allowed on this table!
mysql> insert into st1 values(111);
ERROR 1148 (42000): No write is allowed on this table!

效果符合预期!

创建一个新库:sbtest,区别于test, 在新库中创建相同表名:st1,测试是否受到影响!

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
mysql> create database sbtest;
Query OK, 1 row affected (0.01 sec)

mysql> use sbtest
Database changed
mysql> create table st1(id int primary key) engine=innodb;
Query OK, 0 rows affected (0.02 sec)

mysql> select * from st1;
Empty set (0.00 sec)

mysql> insert into st1 values(1);
Query OK, 1 row affected (0.00 sec)

mysql> insert into st1 values(2);
Query OK, 1 row affected (0.00 sec)

mysql> update st1 set id=4 where id=2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

sbtest库中的st1表可以正常写入,符合预期。