一種基於proxysql的數據脫敏思路

• 2019 年 10 月 4 日
• 筆記

背景：我們這邊給研發查數據的是通過phpmyadmin進行的，通常情況下研發人員查數據寫法是 select * from db1.tb1 where id=xxxx 。

脫敏的思路：通過proxysql對 關於testdb.t_user 表的查詢做改寫。

實驗環境：

數據庫主機： 192.168.20.10:3306  

mysql賬號：dba    密碼： dba

proxysql版本不限，mysql版本不限

### 需要脫敏的原始SQL：

use testdb;  select * from t_user limit 0,25 ;

### 需要改寫成如下效果：

select id,uid,nickname,insert(usr_mobile, 4, 4, 'xxxxx') as usr_mobile from t_user LIMIT 0,25;

下面開始開搞。。。。。。

登錄進proxysql的6032管理端口，執行如下命令：

use main ;    insert into mysql_servers(hostgroup_id,hostname,port,weight,max_connections,max_replication_lag,comment)  values(100,'192.168.20.10',3306,1,1000,10,'testdb');  insert into mysql_users(username,password,active,default_hostgroup,transaction_persistent) values('dba','dba',1,100,1);    set mysql-default_charset='utf8mb4';  set mysql-query_retries_on_failure=0;  set mysql-ping_timeout_server=500;  set mysql-monitor_connect_timeout=1000;  set mysql-default_max_latency_ms=2000;  set mysql-monitor_replication_lag_interval=500;  set mysql-ping_interval_server_msec=3000;  set mysql-monitor_ping_interval=5000;  set mysql-connect_timeout_server_max=3000;    load mysql servers to runtime;  load mysql users to runtime;  load mysql variables to runtime;    save mysql servers to disk;  save mysql users to disk;  save mysql variables to disk;

然後，繼續在這個管理端口下 開始配置改寫規則：

use main;    select * from mysql_query_rules ;    delete from mysql_query_rules ;

### 注意： 這裡對於同一個SQL，有3個規則去適配

1、表名帶反引號 【根據統計，這種情況的SQL最多】

2、表名不帶反引號

3、帶庫名，表名也帶反引號

繼續下面操作：

# 寫入新的sql改寫規則(看上去複雜，實際上就一個規則）  insert into mysql_query_rules  (rule_id,active,apply,log,destination_hostgroup,match_pattern,replace_pattern)  values   (1,1,1,1,100,"^(select.*?from) `t_user` (.*)$","select usr_id,usr_nick,insert(usr_password, 6, 8, 'xxxxxxxx') as usr_password,usr_email,insert(usr_mobile, 4, 4, 'xxxxx') as usr_mobile  from `t_user` 2 ;");    insert into mysql_query_rules  (rule_id,active,apply,log,destination_hostgroup,match_pattern,replace_pattern)  values   (2,1,1,1,100,"^(select.*?from) t_user (.*)$","select usr_id,usr_nick,insert(usr_password, 6, 8, 'xxxxxxxx') as usr_password,usr_email,insert(usr_mobile, 4, 4, 'xxxxx') as usr_mobile  from t_user 2 ;");    insert into mysql_query_rules  (rule_id,active,apply,log,destination_hostgroup,match_pattern,replace_pattern)  values   (3,1,1,1,100,"^(select.*?from) testdb.`t_user` (.*)$","select usr_id,usr_nick,insert(usr_password, 6, 8, 'xxxxxxxx') as usr_password,usr_email,insert(usr_mobile, 4, 4, 'xxxxx') as usr_mobile  from testdb.t_user 2 ;");    # 開啟審計日誌（pma只允許有查詢操作的可能性）【這步設置實際上也可以不要，減少proxysql的日誌量】  set mysql-eventslog_filename = '/var/lib/proxysql/audit.log' ;  -- 會生成  audit.log.0000xx這種命名格式的文件  INSERT INTO mysql_query_rules (rule_id, active, match_digest,destination_hostgroup,log,apply) VALUES (4,1,'^select',100,1,0);  INSERT INTO mysql_query_rules (rule_id, active, match_digest,destination_hostgroup,log,apply) VALUES (5,1,'^SELECT',100,1,0);    # 規則載入runtime ，並持久化到存儲  LOAD MYSQL QUERY RULES TO RUNTIME;  SAVE MYSQL QUERY RULES TO DISK;    select rule_id,active,match_digest,match_pattern,destination_hostgroup,apply,log  from mysql_query_rules ;  +---------+--------+--------------+--------------------------------------------+-----------------------+-------+-----+  | rule_id | active | match_digest | match_pattern                              | destination_hostgroup | apply | log |  +---------+--------+--------------+--------------------------------------------+-----------------------+-------+-----+  | 1       | 1      | NULL         | ^(select.*?from) `t_user` (.*)$        | 100                   | 1     | 1   |  | 2       | 1      | NULL         | ^(select.*?from) t_user (.*)$          | 100                   | 1     | 1   |  | 3       | 1      | NULL         | ^(select.*?from) testdb.`t_user` (.*)$ | 100                   | 1     | 1   |  | 4       | 1      | ^select      | NULL                                       | 100                   | 0     | 1   |  | 5       | 1      | ^SELECT      | NULL                                       | 100                   | 0     | 1   |  +---------+--------+--------------+--------------------------------------------+-----------------------+-------+-----+  5 rows in set (0.00 sec)

連接 6033  測試規則是否生效

use testdb;  select * from t_user limit 0,25 ;  select * from `t_user` limit 0,25 ;  select * from testdb.`t_user` limit 0,25 ;    然後還可以use到其它庫，測試些 select操作， 然後看下是否被記錄到審計日誌

審計日誌的查看方法：

https://www.cnblogs.com/danhuangpai/p/9688075.html  鄧總的博客，全是精華~~~

https://github.com/sysown/proxysql/wiki/Query-Logging 官方文檔

參考文檔：

http://www.cnblogs.com/f-ck-need-u/p/7684762.html

https://www.cnblogs.com/f-ck-need-u/p/9309760.html