MySQL运维实战之ProxySQL（9.3）使用ProxySQL实现读写分离

俊达1年前 (2023-08-04)技术文章708

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

下面是具体的配置步骤：

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;

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;

用户表字段说明：

字段	描述
username	用户名。
password	账号密码。可以使用明文，或者使用hash。若后端账号使用caching_sha2_password，则需要使用明文密码
use_ssl	是否开启ssl
default_hostgroup	默认hostgroup
default_schema	默认schema
schema_locked	暂时不支持
transaction_persistent	设置为1时，如果连接开启了事务，则该连接后续的SQL转发给同一个hostgroup。读写分离场景下，通常需要将事务中的SELECT查询转发到主库，以保证事务一致性，可以将transaction_persistent设置为1。该字段默认为1
fast_forward	设置为1时，绕过query路由选择，直接将SQL转发到指定hostgroup。
backend	设置为1时，表示该账号是后端账号
frontend	设置为1时，表示该账号是前端账号
max_connections	后端连接池
comment	描述

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 |

配置读写分离规则

insert into mysql_query_rules
(rule_id, username, match_pattern, destination_hostgroup,active, apply, comment )
values (101, 'user1', '^SELECT.*FOR UPDATE$', 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表字段说明：

字段	描述
rule_id	规则ID。proxysql 按rule_id的顺序来判断SQL的路由
active	规则是否启用
username	连接用户。如果配置了username，则使用该username连接的会话发送的SQL才能匹配当前规则。
schemaname	如果配置了schemaname，则默认数据库名为该schemaname的会话发送的SQL才能匹配当前规则
match_pattern	正则表达式，匹配SQL文本
negate_match_pattern	如果设置为1，则只有不匹配match_pattern的SQL才匹配当前规则
destination_hostgroup	SQL转发目的hostgroup
apply	如果设置为1，则匹配当前规则后，就直接转发，不再匹配后续规则。
comment	描述