基于ProxySQL中间件实现MySQL的读写分离-CSDN博客

本文链接：https://blog.csdn.net/weixin_72610956/article/details/138851466

ProxySQL Configuration CLI

ProxySQL简介

ProxySQL是一个高性能的MySQL协议代理，它支持多种数据库后端，包括Amazon Aurora、RDS、ClickHouse、Galera、Group Replication、MariaDB Server、NDB、Percona Server等。它的设计目标是为了提高数据库访问的灵活性、性能和安全性。

ProxySQL的核心功能包括：

读写分离：能够智能地将读请求路由到从库（Slave），而写请求则发送到主库（Master），以此来优化负载均衡和提高读取性能。
负载均衡：可以根据不同的策略（如连接数、响应时间、服务器权重等）在多个后端数据库之间分配查询，以优化资源利用。
高可用性：ProxySQL能够监控后端数据库的状态，如果某个数据库实例变得不可用，它会自动重新路由流量到其他健康的实例，从而提高系统的整体可用性。
查询缓存：对于重复的查询，ProxyQL可以缓存结果，减少对数据库的访问，显著提升响应速度。
安全控制：提供了白名单、黑名单以及查询过滤功能，帮助防止SQL注入攻击，增强数据库的安全性。
动态配置与监控：允许管理员通过管理接口实时调整配置，同时提供详细的监控和日志记录功能，便于故障排查和性能调优。

ProxySQL作为一个中间件，位于应用程序和数据库之间，它理解MySQL协议，能够根据协议内容做出智能决策，而不仅仅是盲目地转发流量。这使得ProxySQL成为处理复杂数据库架构和提升数据库访问性能的强大工具。

ProxySQL安装

下面的安装教程是基于RedHat / CentOS的，其他的操作系统可以去官网查看proxysql官网

我们都知道在RedHat / CentOS安装一个服务，一般有三种方法，RPM包安装，源码编译安装，YUM安装，相比于RPM需要依赖的安装和源码编译复杂的安装方式YUM安装是最简单的，下面介绍到的就是YUM安装。

配置YUM仓库

下面的仓库是2.6的版本，你可以选择需要的版本来配置，目前官方维护的版本有：

cat <<EOF | tee /etc/yum.repos.d/proxysql.repo
[proxysql_repo]
name=ProxySQL repository
baseurl=https://repo.proxysql.com/ProxySQL/proxysql-2.6.x/centos/\$releasever
gpgcheck=1
gpgkey=https://repo.proxysql.com/ProxySQL/proxysql-2.6.x/repo_pub_key
EOF

YUM安装

安装，查看版本，设置开机启动立即生效

yum -y install proxysql
proxysql --version
systemctl enable --now proxysql

关于读写分离的实现

集群信息

proxysql	192.168.0.114
master	192.168.0.104
slave1	192.168.0.103
slave2	192.168.0.102

使用mysql客户端工具登录proxysql，用户名和密码都是admin，端口为6032，默认不允许localhost登录，所以要用127.0.0.1IP地址登录

mysql -P 6032 -h 127.0.0.1 -uadmin -padmin

在mysql_servers表中配置后端数据库信息

insert into mysql_servers (hostgroup_id,hostname,port,weight,comment) values (10,'192.168.0.104',3306,1,'只写'),(20,'192.168.0.103',3306,50,'只读'),(20,'192.168.0.102',3306,100,'只读');
load mysql servers to runtime;
save mysql servers to disk;

设置的master的hostgroup_id为10（写组）；
设置的slave的hostgroup_id为20（读组）；
每次在proxysql执行完操作之后，需要手动加载至内存上，然后手动保存至磁盘上。

然后在搭建了MySQL主从复制的架构中的master上创建一个用户并授权

（注意：是master的mysql，不是Proxysql）

CREATE USER 'monitor'@'%' IDENTIFIED WITH caching_sha2_password BY 'new_password';
grant replication client,replication slave on *.* to 'monitor'@'%';

在proxysql上添加该用户

set mysql-monitor_username='monitor';
set mysql-monitor_password='password';
load mysql variables to runtime;
save mysql variables to disk;

通过查看表mysql_server_ping_log了解后端云服务器状态信息

select * from mysql_server_ping_log limit 3;

对后端定义的云服务器的分组进行读组和写组的设定

insert into mysql_replication_hostgroups (writer_hostgroup,reader_hostgroup) values (10,20);
load mysql servers to runtime;
save mysql servers to disk;

查看后端节点是否具有read_only权限

select * from monitor.mysql_server_read_only_log limit 3;

我们在后端的master上创建两个用户账户

（注意：是master的mysql，不是Proxysql）

CREATE USER 'reader'@'%' IDENTIFIED WITH caching_sha2_password BY 'new_password';
CREATE USER 'writer'@'%' IDENTIFIED WITH caching_sha2_password BY 'new_password';
grant all on *.* to 'reader'@'%' identified by  'new_password';
grant all on *.* to 'writer'@'%' identified by  'new_password';

在proxysql节点上添加刚才创建的用户

insert into mysql_users(username,password,default_hostgroup,attributes,comment) values('writer','password',10,'{"address":"%"}','读写用户');
insert into mysql_users(username,password,default_hostgroup,attributes,comment) values('reader','password',20,'{"address":"%"}','只读用户');
load mysql users to runtime;
save mysql users to disk;

至此，基本配置完毕

测试读写分离

我们在client端测试基于用户的读写分离

最后可以在stats库的stats_mysql_connection_pool 表中查看mysql后端链接和整体流量

可以看到在102的节点上的 Queries 是15，而103节点上的 Queries 是6

这是因为我们配置的权重不同

当我们在client在执行一次查询，可以看到102节点上的 Queries增加了一次

测试总结

当我们用reader只读账号登陆和进行DML操作（这里也有个疑问，就是你可以在client上用只读用户登陆实例后可以进行DML，但是例如你执行了一个insert，你在cliet中可以查到你插入的值，但是你在登陆后端的server实例后会发现并没有这个插入的值），会发现也是可行的，（这里在slave节点上的实例中已经配置了read only）为什么呢？

在经过查询官方文档后发现我们没有配置路由测率和属性，也就是路由规则，我们在上面的测试中只是进行了基于用户的读写分离，而路由策略没有配置。（这里也可以理解为基于SQL语句实现读写分离）

在mysql_query_rules中配置路由策略

下面的SQL将会限制只读用户的DML语句，关于这个表中的字段含义可以去看官方文档Main (runtime tables definition) - ProxySQL

INSERT INTO mysql_query_rules (rule_id, active, username,match_pattern, destination_hostgroup,apply) VALUES (1, 1,'reader', '^(SELECT)', 20, 1);
load mysql query rules to runtime;
save mysql query rules to disk;

配置路由策略后在用只读用户去执行DML发现失败