【ProxySqlMysql如何实现读写分离？看这一篇就够了

🌲其他工具对比

其实市面上有很多关于读写分离的优秀的工具，例如

🌲为什么我们选择proxySql？

🌲第一，社区活跃度高，官网代码更新频繁

🌲第二，很多公司在用，例如我们公司就在用，经历过生产的考验

🌲第三，配置简单，学习成本低

🌲第四，开源免费

🌲什么是proxySql？

ProxySQL是一个高性能的开源数据库代理，专门用于在数据库系统和客户端之间进行流量路由和负载均衡。它能够管理和优化数据库连接，提高应用程序的性能和可用性。ProxySQL支持多种数据库系统，包括MySQL、MariaDB、Percona等，使其成为了许多企业和开发者在构建大规模、高性能数据库架构时的首选工具。

ProxySQL的主要功能和特点包括：

🌲安装proxySql

我们提前搭建了一个mysql集群（如何搭建查看博主往期博客），需要准备以下三台Linux服务器，选择把proxySql安装在129这台服务器上面（其实装哪台都一样）。

🌲第一步，下载rpm包

github.com/sysown/prox…

🌲第二步，安装proxysql的环境

yum install perl-DBD-MySQL

🌲第三步，安装proxySql

rpm -ivh proxysql-2.5.3-1-centos7.x86_64.rpm 

🌲第四步，启动服务

systemctl start proxysql

服务命令：systemctl start | stop | restart | status proxysql

🌲第五步，在mysql服务里面创建给proxySql访问的用户

创建主用户，用来暴露自己的api给proxySql

GRANT ALL PRIVILEGES ON *.* TO 'proxyAdmin'@'%' identified by '123456'  WITH GRANT OPTION;

创建监控用户，该用户可以查看本机mysql服务的只读属性

create user proxyMonitor@'%' identified by '123456';
grant replication client on *.* to proxyMonitor@'%';

在三台服务器的mysql服务都执行创建用户的命令，注意是mysql服务，不是proxySql服务
在两台读库中配置只读属性为ON，一定要配置，不然后面更新的时候会更新失败，读库会注册到写库中去

SET GLOBAL read_only = ON;
SHOW GLOBAL VARIABLES LIKE 'read_only';

🌲第六步，添加mysql_servers表数据

mysql_servers表是ProxySQL中用于配置MySQL服务器信息的表。它存储了与每个MySQL服务器相关的详细信息，包括主机名、端口、用户名、密码、权重、状态等。

通过在mysql_servers表中添加或更新记录，你可以告诉ProxySQL如何连接到MySQL服务器。这些记录定义了ProxySQL与后端MySQL服务器之间的连接和负载均衡策略。每个记录代表一个MySQL服务器实例。

下面是mysql_servers表的常用字段：

• hostgroup_id：MySQL服务器所属的hostgroup ID。hostgroup是一组具有相似角色的MySQL服务器，例如读库、写库等。
• hostname：MySQL服务器的主机名或IP地址。
• port：MySQL服务器的端口号。
• username：连接MySQL服务器所使用的用户名。
• password：连接MySQL服务器所使用的密码。
• weight：权重值，用于负载均衡。较高的权重值表示更多的流量将路由到该MySQL服务器。
• status：MySQL服务器的状态，如在线（ONLINE）或离线（OFFLINE）。
• max_connections：该服务器的最大连接数限制。
• comment：注释或描述信息。

通过在mysql_servers表中添加、更新或删除记录，你可以动态地配置ProxySQL与后端MySQL服务器的连接和负载均衡设置。

所以我们的配置都在mysql_servers表中配置，他存在proxySql的main库中

我们先连接他的服务端：

mysql -uadmin -padmin -h127.0.0.1 -P6032

切换库

use main

添加我们三台mysql的ip和地址

insert into mysql_servers(hostgroup_id,hostname,port)  values(20,'192.168.75.128',3306);
insert into mysql_servers(hostgroup_id,hostname,port)  values(10,'192.168.75.129',3306);
insert into mysql_servers(hostgroup_id,hostname,port)  values(20,'192.168.75.130',3306);

注意（192.168.75.129）这台机器的hostgroup_id不同

之后我们load和save一下

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

在ProxySQL中，更改配置后需要执行LOAD和SAVE命令来加载和保存配置更改。

当你在ProxySQL中修改了配置，比如修改了mysql_users表、mysql_servers表、mysql_query_rules表等，这些更改默认只会保存在内存中，而不会持久化到磁盘。如果不执行保存操作，ProxySQL在重启后将会丢失这些更改，重新加载默认的配置。

通过执行LOAD命令，ProxySQL将从磁盘加载配置更改到内存中，使其生效。然后，通过执行SAVE命令，ProxySQL将当前内存中的配置保存到磁盘文件中，以便在重启后可以恢复这些更改。

因此，为了确保你的配置更改在重启后仍然有效。

🌲第七步，配置读写库组id

添加好服务之后，我们配置读写库组id

mysql_replication_hostgroups表是ProxySQL中用于配置MySQL主从复制的主机组信息的表。

在该表中，可以定义主机组（Hostgroup）及其相关的属性，如主机组ID、读写分离规则、复制延迟等。每个主机组代表了一组MySQL服务器，可以包含主服务器（Master）和一个或多个从服务器（Slave）。通过配置主机组，ProxySQL可以实现自动的读写分离和主从复制功能。

在ProxySQL中，可以根据实际需求创建多个主机组，并根据需要指定读写分离规则和负载均衡策略。通过这些配置，ProxySQL可以自动将读操作路由到合适的从服务器（Slave），将写操作路由到主服务器（Master），实现读写分离和负载均衡。

mysql_replication_hostgroups表中的记录包含了主机组的相关信息，包括主机组ID、主服务器和从服务器的配置、读写分离规则、复制延迟等。通过对该表的配置，可以定义并管理ProxySQL的主从复制配置，实现高可用和负载均衡的数据库访问。

insert into mysql_replication_hostgroups(writer_hostgroup,reader_hostgroup,check_type) values(10,20,'read_only');

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

🌲第八步，配置用户表

mysql_users表是ProxySQL中的一个配置表，用于存储与MySQL用户相关的信息。

在ProxySQL中，mysql_users表定义了在连接到MySQL服务器时使用的用户名和密码，以及该用户在ProxySQL中的权限和其他属性。通过在mysql_users表中配置用户信息，ProxySQL可以根据客户端的连接请求进行身份验证，并根据定义的权限规则控制对MySQL服务器的访问。

在mysql_users表中，每一行表示一个MySQL用户的配置，包括以下字段：

• username：MySQL用户名。
• password：MySQL用户的密码，可以是明文密码或经过加密的密码。
• default_hostgroup：默认的MySQL服务器组，用于路由查询请求。
• default_schema：默认的数据库模式。
• transaction_persistent：是否启用持久事务。
• fast_forward：是否启用快速转发。
• backend：指定一个后端连接池，用于复制查询到MySQL服务器。
• frontend：指定一个前端连接池，用于接受客户端连接。

通过配置mysql_users表，可以实现对MySQL用户的身份验证、访问控制以及请求路由等功能，以满足不同应用场景下的需求。

所以我们要把刚刚在mysql创建的主用户添加到改表中去

insert into mysql_users(username,password,default_hostgroup) values('proxyAdmin','123456',10);

再load和save一下

load mysql users to runtime;
save mysql users to disk;

注意，这个和上面的load语句不同，一个是users，一个是service

🌲第九步，设置mysql的监控用户

set mysql-monitor_username='proxyMonitor';
set mysql-monitor_password='123456';

这两个命令用于在ProxySQL中设置MySQL监控用户的用户名和密码。

ProxySQL允许配置一个MySQL监控用户，用于与MySQL服务器建立监控连接并收集相关的性能统计信息。设置mysql-monitor_username和mysql-monitor_password就是为了配置这个MySQL监控用户的用户名和密码。

当设置完用户名和密码后，ProxySQL将使用这些凭据建立与MySQL服务器的监控连接，并定期获取性能指标、查询统计和连接状态等信息。这些收集到的数据可以用于性能监控、故障排除和优化分析等用途。

请注意，设置mysql-monitor_username和mysql-monitor_password后，确保使用的用户名和密码与MySQL服务器中的监控用户的凭据相匹配，以便ProxySQL可以成功连接和收集监控数据。

好了之后别忘了load和save一下

load mysql variables to runtime;
save mysql variables to disk;

🌲第十步，配置proxySql读写分离规则

要想proxySql实现读写分离，需要配置mysql_query_rules表

mysql_query_rules表是ProxySQL中用于定义查询规则的表。

在ProxySQL中，查询规则决定了如何处理不同类型的查询语句。通过在mysql_query_rules表中定义查询规则，可以实现灵活的查询路由和行为控制。

以下是一些常见的使用情况和功能：

通过在mysql_query_rules表中配置适当的规则，可以对查询的路由、行为和性能进行细粒度的控制和优化，以满足应用程序的需求和业务目标。

INSERT INTO mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup, apply)
VALUES (1, 1, '^SELECT.*', 10, 1), -- 将匹配以 SELECT 开头的查询语句路由到读库（hostgroup 10）
       (2, 1, '.*', 20, 1); -- 将匹配所有其他查询语句路由到写库（hostgroup 20）

在上述示例中，第一条规则使用正则表达式^SELECT.*匹配以SELECT开头的查询语句，并将其路由到hostgroup 10，这个hostgroup代表读库。第二条规则使用正则表达式.*匹配所有其他查询语句，并将其路由到hostgroup 20，这个hostgroup代表写库。

根据你的需求，你可以根据查询类型、关键字、表名等设置更复杂的规则。请注意，规则的顺序很重要，ProxySQL将按照规则列表的顺序逐条匹配，并在找到匹配项后停止匹配。

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

完成插入规则后，重启或重新加载ProxySQL配置，使其生效。

🌲第十一步，验证我们的配置是否成功

写库

读库

准备了不同的数据

连接到proxySql的客户端

mysql -uproxyAdmin -p123456 -P6033 -h127.0.0.1

综上所述，6032端口用于管理ProxySQL本身，而6033端口用于应用程序与ProxySQL之间的通信，实现数据库代理的功能。

select走到了读库

update走到了写库

🌲springboot如何连接mysql？

像什么都没发生一样，springboot连接mysql连接代理即可

至此，proxySql已经搭建完成，我们已经完成了mysql读写分离的要求了。希望通过本文的指导，你已经掌握了使用ProxySQL实现Mysql读写分离的核心概念和操作步骤。无论是在单一数据库服务器还是分布式数据库集群中，读写分离都是一个非常有益的优化手段，能够有效提升应用程序的性能和可用性！创作不易，多多三联。