基于ProxySQL读写分离实现_proxysql 读写分离

基于ProxySQL读写分离实现

前段时间与一个同事聊天，过程中却被吐槽。

因为自己的博客中空空如也，一直是技术类文章忠实的读者和使用者，自己却不曾有什么分享

有点惭愧…

最近刚好完成了一些项目，特此做些总结和分享

（附上Larry Ellison的照片，吐槽下MySQL Proxy就别自己写了，这么多年还没有GA版本，还是买吧，毕竟有钱~）

1、读写分离概述

读写分离是数据库架构部署里一个老生常谈的话题，提高MySQL主备库资源利用率的同时实现读写压力均匀的分担。在讲究弹性缩容和资源最大化的今天，无比是一个非常务实的行为。

读写分离的实现方式：

• 业务代码实现
• 中间件实现

今天主要介绍下基于ProxySQL读写分离的实现，也是第二种，基于中间件的实现

2、环境场景描述

相信大家都有使用阿里云ECS的经历，辛苦部署了数据库，主备库也有了，却发现不支持VIP，高可用一下子

成为难题要使应用可以伴随数据库的高可用做到瞬时切换，vip是一个非常可靠的选择。为了满足高可用的同时，

还要实现读写分离，于是开始思考基于中间件实现

（使用阿里云RDS的就不要吐槽了，萝卜青菜各有所爱，不要问那么多为什么）

MySQL
192.168.1.1 master
192.168.1.2 slave
192.168.1.3 slave

ProxySQL
192.168.1.4 
192.168.1.5

LVS
192.168.1.6
1
2
3
4
5
6
7
8
9
10
11

3、ProxySQL介绍

有朋友或许会疑惑，中间件那么多，知名的MaxScale、mycat、HAproxy…为什么非要使用这个产品

先引入对比图，proxysql官方的产品特点对比

ProxySQL官方的产品特点对比图

ProxySQL由René Cannaò开发，作者是一个拥有十多年MySQL数据库维护和开发的经验的DBA。

目前Proxysql与AWS和Percona等很多公司合作，该产品也被大量部署使用

ProxySQL产品优势:

支持查询缓存

ProxySQL可以对某些路由规则设定查询缓存，在缓存时间内多次匹配这条查询规则，则通过query cache来获取数据

支持查询路由

具有灵活的查询路由规则配置，可以通过来源地址、sql语句正则、用户名进行路由规则的自定义

支持故障转移

ProxySQL会自动监视后端mysql的状态，根据后端mysql状态自动做对应的调整

支持高级配置，动态配置，0停机

ProxySQL支持动态加载配置，包括查询规则、后端server、新增用户等等

跨平台

ProxySQL支持多种平台的Linux

应用代理

高级拓扑支持

ProxySQL支持复杂的mysql复制拓扑架构，包括级联复制

防火墙

支持MGR和PXC

4、ProxySQL安装

安装部分不想过多描述，建议参照下面的地址，直接使用rpm包的方式比较方便
https://github.com/sysown/proxysql/wiki

1、启动proxysql
rpm包安装完成后，即可直接启动proxysql
/etc/init.d/proxysql start

2、登陆proxysql
mysql -uadmin -padmin -h127.0.0.1 -P6032
(使用admin用户登陆到proxysql管理端口) 
1
2
3
4
5
6
7
8
9
10

5、ProxySQL配置

5.1 proxysql_cluster

用于配置proxysql的集群，其中包括用于部署proxysql机器的ip地址配置，proxysql_cluster能实现proxysql节点之间的配置自动同步,多台proxysql节点还能起到负载均衡和高可用的作用

配置proxysql集群的节点信息

insert into proxysql_servers (hostname, port, `comment`)values ("192.168.1.4", 6032, "ProxySQL1");
insert into proxysql_servers (hostname, port, `comment`)values ("192.168.1.5", 6032, "ProxySQL2");
Hostname:proxysql节点地址
Port:proxysql端口，6032默认管理端口，6033默认数据源端口
Comment:简单的节点备注
1
2
3
4
5

设置proxysql集群的用户

update global_variables set variable_value='proxysql' where variable_name='admin-cluster_username';
update global_variables set variable_value='proxysql' where variable_name='admin-cluster_password';
update global_variables set variable_value='admin:admin;proxysql:proxysql' where variable_name='admin-admin_credentials';
--设置管理用户，默认admin
--设置proxysql的集群用户，默认proxysql，用于proxysql节点之间的配置同步
1
2
3
4
5

加载配置到runtime中生效，并保存配置到磁盘上

load proxysql servers to runtime;
save proxysql servers to disk;

load admin variables to runtime;
save admin variables to disk;
1
2
3
4
5

注意：
（这边补充proxysql加载配置时的原理

默认修改在memory层，既修改只在内存中，不会生效，下次重启后，配置自动消失
需要加载到runtime层，配置才能生效
为了让配置永久生效，需要保存到disk中，使用save命令）

5.2 mysql_replication_hostgroups

配置mysql主备角色对应组号，这是比较关键的配置，ProxySQL识别数据库主备角色就依赖对应的组号

Admin> SHOW CREATE TABLE mysql_replication_hostgroups\G
*************************** 1. row ***************************
       table: mysql_replication_hostgroups
Create Table: CREATE TABLE mysql_replication_hostgroups (
writer_hostgroup INT CHECK (writer_hostgroup>=0) NOT NULL PRIMARY KEY,
reader_hostgroup INT NOT NULL CHECK (reader_hostgroup<>writer_hostgroup AND reader_hostgroup>0),
comment VARCHAR,
UNIQUE (reader_hostgroup))
1 row in set (0.00 sec)

INSERT INTO mysql_replication_hostgroups VALUES (1,2,'cluster1');

这边我的定义
1--代表writer_hostgroup
2--代表reader_hostgroup
Cluster1-代表组的备注，可以是名称

If they have read_only=0 , they will be moved to hostgroup 1
If they have read_only=1 , they will be moved to hostgroup 2
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

5.3 mysql_servers

配置mysql的主备信息，其中包括ip、端口、角色

INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (1,'192.168.1.1',3306);
INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (2,'192.168.1.2',3306);
INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (2,'192.168.1.3',3306);
load mysql serves to runtime;
save mysql serves to disk;
--hostgroup_id 1对应writehost,也就是主库
--hostgroup_id 2对应readhost,也就是备库
--port就是端口，默认3306
1
2
3
4
5
6
7
8

5.4 mysql_users

配置mysql实例上的用户，登陆proxysql进行的权限验证，实则还是通过后端mysql的权限验证来实现的

INSERT INTO mysql_users(username,password,default_hostgroup,default_schema) VALUES ('user2','password2',1,'dbtest1');
load mysql users to runtime;
save mysql users to disk;
--默认组是1，也就是说默认读写都在主库
（如果通过数据源用户来分离读写，这边可以将只读用户对应的default_hostgroup设置为2）
1
2
3
4
5

5.5 mysql_query_rules

读写分离配置的核心表，用于配置mysql读写分离的路由规则，可以是sql digest值、sql正则、来源ip、来源用户名…
(这边暂不描述，由下面的章节单独描述)

5.6 proxysql参数配置

--配置monitor
UPDATE global_variables SET variable_value='monitor' WHERE variable_name='monitor';
UPDATE global_variables SET variable_value='monitor' WHERE variable_name='monitor';
load mysql variables to runtime;
save mysql variables to disk;
--proxysql需要不断对后端mysql进行监控
--monitor用户需要先创建在mysql实例上，需要REPLICATION CLIENT权限


--配置variables

	--sql文本的长度
	set mysql-query_digests_max_digest_length=20480;
	
	--事务的最大执行时间，8小时，超过8小时自动终止
	set mysql-max_transaction_time=28800000;

	--最大数据传输包设置为100M
	set mysql-max_allowed_packet = 104857600;
	
	--单个package传输的最大大小
	set mysql-max_allowed_packet=100000000;
	
	--proxysql支持的最大连接数
	set mysql-max_connections=4000;
	
	set mysql-verbose_query_error = 1;
	
	--proxysql内部处理的线程数，不超过CPU的核数
	set mysql-threads=8
	
	--设置为true时，set autocommit=0视为一个开启一个事务
	set mysql-autocommit_false_is_transaction = 'true';
	set mysql-forward_autocommit = 'true';
	set mysql-enforce_autocommit_on_reads = 'true';
	set mysql-autocommit_false_not_reusable = 'true'; 
	
load mysql variables to runtime;
save mysql variables to disk;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39

5.7 ProxySQL读写分离配置方式

这边开始是本章读写分离的重要内容了，三种读写分离的配置方式

一、基于使用不同端口的读写分离实现

SET mysql-interfaces='192.168.1.4:6401;192.168.1.5:6402';
## save it on disk and restart proxysql
SAVE MYSQL VARIABLES TO DISK;
PROXYSQL RESTART;

INSERT INTO mysql_query_rules (rule_id,active,proxy_port,destination_hostgroup,apply)
VALUES (1,1,6401,1,1), (2,1,6402,2,1);
LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK; # if you want this change to be permanent

--基于端口的方式实现读写分离，有比较明确的分离，需要业务代码拆分成两套，访问不同的端口
--6401的请求转发到主库
--6402的请求转发到从库
1
2
3
4
5
6
7
8
9
10
11
12
13

**二、基于正则表达式的读写分离实现

UPDATE mysql_users SET default_hostgroup=1; # by default, all goes to HG10
LOAD MYSQL USERS TO RUNTIME;
SAVE MYSQL USERS TO DISK; 

INSERT INTO mysql_query_rules (rule_id,active,match_digest,destination_hostgroup,apply)
VALUES (1,1,'^SELECT.*FOR UPDATE$',1,1),(2,1,'^SELECT',2,1);

LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK; # if you want this change to be permanent

--基于正则表达式的匹配方式来实现sql的路由，需要提醒大家一点，文中的正则案例是有bug的，作者明确说明不建议在生产环境中使用上述配置，只是用于在测试环境中测试。还是见到很多人运用到生产中去
1
2
3
4
5
6
7
8
9
10
11

三、基于SQL digest读写分离实现

基于SQL digest值实现的路由规则，业务切换到ProxySQL上，stats_mysql_query_digest会记录SQL的各种执行信息，包括执行时间、执行次数、以及生成去参后的digest值(digest用于唯一的定位一条sql)
1

TOP-sql

(依稀看到了oracle AWR报告的感觉)

Find the top 5 queries based on total execution time:

Admin> SELECT digest,SUBSTR(digest_text,0,25),count_star,sum_time FROM stats_mysql_query_digest WHERE digest_text LIKE 'SELECT%' ORDER BY sum_time DESC LIMIT 5;
+--------------------+--------------------------+------------+---------------+
| digest             | SUBSTR(digest_text,0,25) | count_star | sum_time      |
+--------------------+--------------------------+------------+---------------+
| 0x037C3E6D996DAFE2 | SELECT a.ip_id as ip_id, | 2030026798 | 1479082636017 |
| 0xB081A85245DEA5B7 | SELECT a.ip_id as ip_id, | 2025902778 | 1206116187539 |
| 0x38BE36BDFFDBE638 | SELECT instance.name as  | 59343662   | 1096236803754 |
| 0xB4233552504E43B8 | SELECT ir.type as type,  | 1362897166 | 488971769571  |
| 0x4A131A16DCFFD6C6 | SELECT i.id as id, i.sta | 934402293  | 475253770301  |
+--------------------+--------------------------+------------+---------------+
5 rows in set (0.01 sec)
1
2
3
4
5
6
7
8
9
10
11

Find the top 5 queries based on count:

Admin> SELECT digest,SUBSTR(digest_text,0,25),count_star,sum_time FROM stats_mysql_query_digest WHERE digest_text LIKE 'SELECT%' ORDER BY count_star DESC LIMIT 5;
+--------------------+--------------------------+------------+---------------+
| digest             | SUBSTR(digest_text,0,25) | count_star | sum_time      |
+--------------------+--------------------------+------------+---------------+
| 0x037C3E6D996DAFE2 | SELECT a.ip_id as ip_id, | 2030040688 | 1479092529369 |
| 0xB081A85245DEA5B7 | SELECT a.ip_id as ip_id, | 2025916528 | 1206123010791 |
| 0x22E0A5C585C53EAD | SELECT id as instanceid, | 1551361254 | 426419508609  |
| 0x3DB4B9FA4B2CB36F | SELECT i.id as instancei | 1465274289 | 415565419867  |
| 0xB4233552504E43B8 | SELECT ir.type as type,  | 1362906755 | 488974931108  |
+--------------------+--------------------------+------------+---------------+
5 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11

Find the top 5 queries based on maximum execution time:

Admin> SELECT digest,SUBSTR(digest_text,0,25),count_star,sum_time,sum_time/count_star avg_time, min_time, max_time FROM stats_mysql_query_digest WHERE digest_text LIKE 'SELECT%' ORDER BY max_time DESC LIMIT 5;
+--------------------+--------------------------+------------+--------------+----------+----------+-----------+
| digest             | SUBSTR(digest_text,0,25) | count_star | sum_time     | avg_time | min_time | max_time  |
+--------------------+--------------------------+------------+--------------+----------+----------+-----------+
| 0x36CE5295726DB5B4 | SELECT COUNT(*) as total | 146390     | 185951894994 | 1270249  | 445      | 237344243 |
| 0xDA8C56B5644C0822 | SELECT COUNT(*) as total | 44130      | 24842335265  | 562935   | 494      | 231395575 |
| 0x8C1B0405E1AAB9DB | SELECT COUNT(*) as total | 1194       | 1356742749   | 1136300  | 624      | 216677507 |
| 0x6C03197B4A2C34BE | Select *, DateDiff(Date_ | 4796       | 748804483    | 156131   | 607      | 197881845 |
| 0x1DEFCE9DEF3BDF87 | SELECT DISTINCT i.extid  | 592196     | 40209254260  | 67898    | 416      | 118055372 |
+--------------------+--------------------------+------------+--------------+----------+----------+-----------+
5 rows in set (0.01 sec)
1
2
3
4
5
6
7
8
9
10
11

配置mysql_query_rules表

INSERT INTO mysql_query_rules (rule_id,active,digest,destination_hostgroup,apply)
VALUES
(1,1,'0x38BE36BDFFDBE638',2,1);

LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;
1
2
3
4
5
6

实现读写分离的方式很多，基于用户、来源地址、端口、sql digest、正则等等
可根据业务类型，选择合适的

读写分离路由规则定义需要考虑下面的两点：

一、SQL执行是否能接受读取到延迟的数据？？

	mysql主备延迟的问题，似乎一直存在，sql发布，高并发访问，在MySQL 5.7 无损复制下依然会有延迟，需要评估读取到延迟的数据影响

二、SQL是否是属于执行效率低的？？

	通过proxysql实现读写分离后，性能上收益有多少？
1
2
3
4
5
6
7

6、总结

本章描述了ProxySQL一些基础配置和实现原理

内容还是蛮多，先到这儿

关于proxysql监控、优化以及高可用切换，放到后面章节再补充

目前ProxySQL已经出了2.0版本，增加了基于GTID一致性读取，对它的未来比较看好

有兴趣大家可以一起测试下，遇到问题一起交流~
1
2
3
4
5
6
7
8
9