作者：俊达

proxysql读写分离主要通过mysql_query_rules表中的规则来实现。

下面是具体的配置步骤：

1 hostgroup配置

insert into mysql_servers (
  hostgroup_id, hostname, port, max_replication_lag)
values ( 100, '172.16.121.236', 3380, 3);

insert into mysql_servers (
  hostgroup_id, hostname, port, max_replication_lag)
values ( 101, '172.16.121.237', 3380, 3);


load mysql servers  to runtime; 
SAVE MYSQL servers  TO DISK;

2 user配置

后端服务器创建用户

mysql> create user 'user1'@'%' identified by 'user1-backend';
Query OK, 0 rows affected (0.02 sec)

mysql> grant all privileges on *.* to 'user1'@'%';
Query OK, 0 rows affected (0.02 sec)

proxysql上配置用户信息

insert into mysql_users
(username, password, transaction_persistent, backend, frontend, comment)
values ('user1', 'user1-backend', 1, 1, 1, 'backend user for user1');

load mysql users  to runtime; 
SAVE MYSQL users  TO DISK;

用户表字段说明：

mysql 8.0 caching_sha2_password

如果后端mysql版本是8.0，并且后端账号使用了caching_sha2_password插件认证，则需要这样配置：

1、admin-hash_passwords设置为false

set admin-hash_passwords=1;

-- 加载 & 持久化配置项 
 LOAD ADMIN VARIABLES to runtime;
 save ADMIN VARIABLES to disk;

 -- 确认配置生效
 mysql> select * from runtime_global_variables where variable_name = 'admin-hash_passwords';
+----------------------+----------------+
| variable_name        | variable_value |
+----------------------+----------------+
| admin-hash_passwords | false           |
+----------------------+----------------+

2、mysql_users表的password字段写入账号明文

load mysql users to runtime;
save mysql users to disk;


-- 确认runtime_mysql_users表中的密码是明文
mysql> select username, password from runtime_mysql_users where username = 'user1';
+----------+---------------+
| username | password      |
+----------+---------------+
| user1    | user1-backend |

3 配置读写分离规则

insert into mysql_query_rules
(rule_id, username, match_pattern, destination_hostgroup,active, apply, comment )
values (101, 'user1', '^SELECT.*FOR UPDATE#39;, 100,1,1, 'user1 route to hostgroup 100');

insert into mysql_query_rules
(rule_id, username, match_pattern, destination_hostgroup, active, apply, comment)
values (102, 'user1', '^SELECT', 101,1,1, 'user1 select query route to hostgroup 101');


-- 生效规则 & 保存规则
load mysql query rules to runtime; 
SAVE MYSQL QUERY RULES TO DISK;

mysql_query_rules表字段说明：

在实际应用过程中，我们可以精细化配置SQL匹配规则，将特定模式的SQL语句路由的只读节点执行。

更多技术信息请查看云掣官网云掣YunChe - 可观测运维专家 | 大数据运维托管 | 云MSP服务