数据库中间件ProxySQL之读写分离

IT那活儿 2022-11-30

686

点击上方“IT那活儿”公众号，关注后了解更多内容，不管IT什么活儿，干就完了！！！

一

适用场景

在读多写少的业务应用场景中，常常需要实现业务数据读写分离，数据读写分离的好处是显而易见的，如提提高高并瓶颈下数据处理的效率，极大程度缓解锁争用，降低数据延迟时间等。

在没有配置性能良好的MySQL中间件产品情况下，仅通过MySQL主从设置由业务程序人工设置MySQL主从IP的配置方式来实现读写分离，很容易会出现人工配置错误的情况,从而导致主从数据不一致、数据混乱的极为严重情况。由此可见选取一款性能优异的中间件产品就显得尤为重要。

现在我们介绍一款基于ProxySQL中间件实现业务数据读写分离的方案，是一个可以实现MySQL读写分离的轻量级工具，是一款开源的中间件的产品，是一个灵活的 MySQL 代理层，可以实现数据的读写分离。

二

ProxySQL简介

2.1 介绍

ProxySQL是由C++语言开发，可以实现MySQL数据读写分离的轻量级工具，一款MySQL数据库的开源中间件产品，轻量级但性能优异（支持处理千亿级数据），具有如下功能：

多种方式的读写分离；
定制基于用户、基于schema、基于语言的规则对SQL语句进行路由；
缓存查询结果；
后端节点的控制。

2.2 特点

将所有配置保存写入到SQLit表中；
支持动态加载配置，即一般可以在线修改配置，但有少部分参数还是需要重启来生效；
支持query cache；
支持对query的路由，可以针对某个语句进行分配去哪个实例执行；
故障切换；
过滤危险的SQL；
不支持分表，可以分库，但是利用规则配置实现分表。

三

技术架构

四

MySQL、Keepalived安装

安装过程可参考往期发布的文章：

《mysql主主+keepalived高可用》

《Mysql+Keepalived高可用最佳配置实践》

五

ProxySql安装详细步骤

5.1 设置yum源

5.2 安装依赖包

#yum install openssl openssl-devel kernel-devel gcc gcc-c++ -y
#yum install perl-DBI -y
#yum install perl-DBD-MySQL -y
#yum install perl-Time-HiRes -y
#yum install perl-IO-Socket-SSL -y

5.3 安装proxysql

#yum install -y proxysql-1.4.8-1-centos7.x86_64.rpm

5.4 设置环境

#mkdir -p /var/lib/proxysql
#chown -Rf mysql.mysql /usr/share/proxysql/
#chown -Rf mysql.mysql /var/lib/proxysql
#chown mysql.mysql /etc/proxysql.cnf
#systemctl start proxysql.service

5.5 配置过程

Proxysql配置是通过一个多级配置系统，将配置从运行时环境移到内存，并在有需求时持久化到磁盘上，由这 3 个层次的组成，如下：

mysql -uadmin -padmin -h127.0.0.1 -P6032

1）添加后端mysql主机

mysql>insert into mysql_servers(hostgroup_id,hostname,port,weight,comment) values(1,' 192.168.100.1',3306,1,'Write Group');
mysql>insert into mysql_servers(hostgroup_id,hostname,port,weight,comment) values(2,' 192.168.100.2',3306,1,'Read Group');
mysql>insert into mysql_servers(hostgroup_id,hostname,port,weight,comment) values(2,' 192.168.100.3',3306,1,'Read Group');
mysql>insert into mysql_servers(hostgroup_id,hostname,port,weight,comment) values(2,' 192.168.100.4',3306,1,'Read Group');

2）添加可以访问后端主机的账号

mysql后端主机：

GRANT ALL ON *.* TO 'shsnc'@'192.168.%' IDENTIFIED BY '1qaz!QAZ';

#default_hostgroup默认组设置为写组，也就是1。

mysql>insert into 
mysql_users(username,password,default_hostgroup,transaction_persistent)values('shsnc','1qaz!QAZ',1,1);

3）添加健康监测的账号

#在后端主机中添加可以访问数据库的账号,SELECT权限即可。

mysql>GRANT SELECT ON *.* TO 'monitor'@'192.168.%' 
IDENTIFIED BY 'MonitoR';

#在proxysql管理端中修改变量设置健康检测的账号。

mysql>set mysql-monitor_username='monitor';
mysql>set mysql-monitor_password='MonitoR';

4）添加读写分离的路由规则

mysql>insert into 
mysql_query_rules(rule_id,active,match_digest,destination_ho
stgroup,apply)values(1,1,'^SELECT.*FOR UPDATE$',1,1);

mysql>insert into 
mysql_query_rules(rule_id,active,match_digest,destination_ho
stgroup,apply)values(2,1,'^SELECT',2,1);

5）修改的数据加载至RUNTIME中

#load进runtime，是配置生效。

mysql>load mysql users to runtime;
mysql>load mysql servers to runtime;
mysql>load mysql query rules to runtime;
mysql>load mysql variables to runtime;
mysql>load admin variables to runtime;

#save到磁盘(/var/lib/proxysql/proxysql.db)中。

mysql>save mysql users to disk;
mysql>save mysql servers to disk;
mysql>save mysql query rules to disk;
mysql>save mysql variables to disk;
mysql>save admin variables to disk;

6）验证

mysql>select * from stats_mysql_query_digest;
mysql>select * from stats_mysql_processlist ;
mysql>select * from runtime_mysql_servers;

7）查看日志

$less /var/lib/proxysql/proxysql.log

本文作者：程继贵(上海新炬王翦团队）

本文来源：“IT那活儿”公众号

文章转载自IT那活儿，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。