proxysql配置读写分离

proxysql配置读写分离

配置mysql

添加用户(如果使用已存在的用户,无需添加用户)

-- 注意,这里的认证方式要使用mysql_native_password
create user `proxysql` identified with mysql_native_password by 'proxysql-password';

grant all privileges on proxysqldb.* to 'proxysql'@'%' identified with mysql_native_password by 'proxysql-password'

检查用户的认证方式

SELECT Host,User,plugin,authentication_string from  mysql.user;

如果用户使用的不是mysql_native_password加密方式,需要修改一下加密方式:

drop user ${mysql_username}@'%';

create user 'root'@'%' identified with mysql_native_password  BY '${msqyl_password}';

GRANT ALL PRIVILEGES ON *.* TO ${mysql_username}@'%' IDENTIFIED BY '${msqyl_password}' WITH GRANT OPTION;

检查autocommit

proxysql的配置读写规则后,如果开启了事务,那么在commit之前都会走同一个库,举个例子:
如果开启了autocommit.在执行玩commit之后,如果第一条语句是select,那么后面的所有语句都会走读库,包括insert和update语句.知道下一次commit为止.

所以要检查my.cnf里面的autocommit是否为off

proxysql的基础配置

连接proxysql

在执行完systemctl restart proxysql之后,就可以使用mysql命令连接proxysql进行配置

mysql -uadmin -padmin -h127.0.0.1 -P6032

分层说明

官方文档

┌─────────────────────────────────┐
│                                 │
│            Runtime              │
│                                 │
└─────────────────────────────────┘
                                   
                                   
                                   
┌─────────────────────────────────┐
│                                 │
│            Memory               │
│                                 │
└─────────────────────────────────┘
                                   
                                   
                                   
┌─────────────────────────────────┐
│                                 │
│    Disk & Configuration File    │
│                                 │
└─────────────────────────────────┘

简单来说proxysql一共有3层,我们通过mysql客户端连接到proxysql进行配置后,是在Memory层进行操作,所有想使修改生效/持久化,要使用laod/save命令

global variables 配置

表说明

修改proxysql提供的msyql服务的地址以及端口(可选)

-- 配置proxysql的mysql服务任意地址访问3306端口访问
SET mysql-interfaces='0.0.0.0:3306';

-- 注意修改这个变量要重启proxysql 服务
SAVE MYSQL VARIABLES TO DISK;
proxysql restart;

配置proxysql提供给应用的mysql版本号(可选)

-- 根据你的应用要求以及后端数据库自行配置自行配置
update global_variables set variable_value = '8.0.33' where variable_name = 'mysql-server_version';
LOAD MYSQL VARIABLES TO RUNTIME;
SAVE MYSQL VARIABLES TO DISK;

添加后端服务器

表说明

INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (10,'10.0.0.1',3306);
INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (20,'10.0.0.2',3306);

load mysql servers to runtime;
save mysql servers to disk;

配置监控

监控的主要作用是监控后端服务器的健康状态
表说明

-- 根据你的定义自行修改
set mysql-monitor_username=proxysqlusername;
set mysql-monitor_password=proxysqlpasword;

LOAD MYSQL VARIABLES TO RUNTIME;
SAVE MYSQL VARIABLES TO DISK;

检查检查日志

配置好监控和servers之后就可以查看监控日志了,如果error列为NULL,就是没有问题

select * from monitor.mysql_server_connect_log order by time_start_us desc limit 3;
select * from monitor.mysql_server_ping_log order by time_start_us desc limit 3;

配置分组信息

表说明

-- 注意这里的writer_hostgroup和reader_hostgroup 要和上面的mysql_servers 里面的hostgroup_id对应上
insert into mysql_replication_hostgroups(writer_hostgroup, reader_hostgroup, comment) values (10,20,'proxy');
load mysql servers to runtime;
save mysql servers to disk;

配置访问MYSQL的用户

表说明

-- 根据你的配置自行修改
INSERT INTO mysql_users(username,password,default_hostgroup) VALUES ('proxysql','proxysqlpassword',1);
load mysql user to runtime;
save mysql user to disk;

查看统计信息

show tables from stats;

stats_mysql_connection_pool

查看mysql后端的连接信息

stats_mysql_commands_counters

查看各种sql语句分类的统计信息

stats_mysql_query_digest

查看sql的digest的统计信息

配置读写分离

proxysql默认所有的sql全部会走写库

proxysql默认不建议简单粗暴的将所有的select走读库,其他的操作走写库的配置,而是根据慢sql日志或者上面的stats_mysql_query_digest日志来进行配置

三种规则

match digest

正则匹配sql语句的digest text
这里要先说明什么是sql的digest,就是将一个sql语句进行摘要,规则如下:

• 关键字大写
• 标识符都被加反引号`
• 常量包括数值常量和字符串常量都被替换成?

可以在任意mysql客户端查看一条sql语句的digest text:

SELECT STATEMENT_DIGEST_TEXT('SELECT * FroM T1 WHERE c1 > 10 and c2="abc" AND 1+SIN(3) > 0;') AS label;

+------------------------------------------------------------------------+
| label                                                                  |
+------------------------------------------------------------------------+
| SELECT * FROM `T1` WHERE `c1` > ? AND `c2` = ? AND ? + `SIN` (?) > ? ; |
+------------------------------------------------------------------------+
1 row in set (0.00 sec)

digest

digest就是将上面获得的digest text哈希运算后得到的hash字符串,可以在任意mysql客户端查看:

mysql> SELECT STATEMENT_DIGEST('SELECT * FROM T1 WHERE c1 > 2;') AS label;
+------------------------------------------------------------------+
| label                                                            |
+------------------------------------------------------------------+
| 3e2db85c14a4bc5662e406bedb24bc3b47bc98ee99f28bba846e32980a09448d |
+------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT STATEMENT_DIGEST('SelecT   * FROM T1 WHERE c1 > 2;') AS label;
+------------------------------------------------------------------+
| label                                                            |
+------------------------------------------------------------------+
| 3e2db85c14a4bc5662e406bedb24bc3b47bc98ee99f28bba846e32980a09448d |
+------------------------------------------------------------------+
1 row in set (0.00 sec)

match_pattern

这个最好理解,就是sql语句的正则

mysql_query_rules

表说明

根据mysql的慢sql查询或者上面的stats_mysql_query_digest表的统计信息结果,就可以配置读写规则了

-- 注意这里只是例子,要根据场面的统计信息来进行配置然后在测试环境进行验证
-- 注意这里的destination_hostgroup 要和 mysql_replication_hostgroups里面的对应上
insert into mysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply) values (2,1,'^select.*for update$',10,1);
insert into mysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply) values (3,1,'^select',20,1);

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

检查是否sql执行是否遵循规则

select * from stats_mysql_query_digest;

参考文档

How to configure ProxySQL for the first time
How to set up ProxySQL Read/Write Split
Main (runtime tables definition)