ProxySQL 匹配规则

现实中很多场景要求更新数据能立马查到数据，而主从同步在这方面无解，所以从规则上修改，一些需要及时查询的语句在主上。

#  用户登录
mysql -h192.168.0.103 -P16032 -uradmin -pradmin

# 先查看主库在哪个组，获取组所在值，假设为100，写组，1000为读组
mysql> select * from mysql_servers;

# 删除规则
delete from mysql_query_rules;

# 插入新规则，根据实际情况修改表名
insert into mysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply) 
VALUES 
(1,1,'^SELECT.*FROM .*score_.*',100,1),
(2,1,'^select.*getSequenceNo.*',100,1),
(3,1,'^SELECT.*FROM.*recharge_granularity.*',100,1),
(4,1,'^SELECT.*FROM.*product_.*',100,1),
(5,1,'^SELECT.*FROM.*person_.*',100,1),
(6,1,'^SELECT.*FROM.*monitoring_.*',100,1),
(7,1,'^SELECT.*FROM.*menu_resource.*',100,1),
(8,1,'^SELECT.*FROM.*manager_logs_info.*',100,1),
(9,1,'^SELECT.*FROM.*industry_.*',100,1),
(10,1,'^SELECT.*FROM.*customized_info.*',100,1),
(11,1,'^SELECT.*FROM.*custom_dictionary_company.*',100,1),
(12,1,'^SELECT.*FROM.*base_tables_.*',100,1),
(13,1,'^SELECT.*FROM.*base_change_group.*',100,1),
(14,1,'^SELECT.*FROM.*order_.*',100,1),
(15,1,'^SELECT.*FROM.*value_order_item.*',100,1),
(16,1,'^SELECT.*FROM.*secu_.*',100,1),
(17,1,'^SELECT.*FOR UPDATE$',100,1),
(18,1,'^SELECT',1000,1);

# 查询信息状态
select hostgroup hg,count_star cs,digest,digest_text dt from stats_mysql_query_digest;

# 查询规则
select rule_id,active,match_pattern,match_digest,destination_hostgroup,apply from mysql_query_rules;

# 修改规则
update mysql_query_rules set match_digest='^select.*getSequenceNo.*' where rule_id=2;

# 停止规则
update mysql_query_rules set active=0 where rule_id=2;

# 删除规则
delete from mysql_query_rules where rule_id=2;

# 主从分组信息
select hostgroup_id,hostname,port,status,weight from mysql_servers;

# log
select * from mysql_server_ping_log;
select * from mysql_server_replication_lag_log;
select * from mysql_server_read_only_log;

# 写入新规则
insert into mysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply) VALUES (2,1,'^select.*getSequenceNo',100,1);

load mysql query rules to runtime;
save mysql query rules to disk;