likes
comments
collection
share

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

作者站长头像
站长
· 阅读数 13

🌲其他工具对比

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

工具优势劣势
ProxySQL- 高性能的负载均衡和连接池管理- 支持MySQL和MariaDB- 灵活的配置和规则定义- 只支持MySQL和MariaDB数据库- 功能相对专注，适用性可能有限- 学习和配置可能需要一些时间
MaxScale- 支持多种数据库，包括MariaDB- 丰富的插件和功能- 官方支持- 功能较多，学习曲线可能较陡峭- 需要按照MariaDB的生态整合
HAProxy- 高性能的负载均衡- 支持TCP和HTTP流量- 易于配置- 主要关注TCP和HTTP，可能不适用于所有数据库类型- 不支持复杂的数据库协议
pgBouncer- 专门为PostgreSQL设计- 轻量级的连接池管理- 易于部署和配置- 仅支持PostgreSQL数据库- 功能较简单，不适用于所有需求
MySQL Router- 由MySQL官方提供- 支持MySQL负载均衡和读写分离- 简单易用- 仅支持MySQL数据库- 功能可能较为基础，不适用于所有场景
Orchestrator- 自动化MySQL高可用性管理- 支持故障转移和主从切换- 易于部署和配置- 不是严格意义上的数据库代理工具- 功能主要集中于高可用性
Galera Cluster- 支持MySQL多主复制- 高可用性和负载均衡的综合解决方案- 自动化管理- 仅适用于特定的MySQL架构- 功能较复杂，不适用于所有情况

🌲为什么我们选择proxySql？

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

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

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

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

🌲第四，开源免费

🌲什么是proxySql？

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

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

  1. 负载均衡： ProxySQL能够将客户端请求均匀地分配到多个后端数据库服务器上，从而实现负载均衡，提高数据库的吞吐量和响应能力。

  2. 读写分离： ProxySQL支持将读和写请求分别路由到不同的数据库实例上，从而有效地分担主库的压力，提高整体性能。

  3. 自动故障检测和故障转移： 当后端数据库发生故障时，ProxySQL能够自动检测并将请求路由到可用的数据库节点，实现高可用性。

  4. 查询缓存： ProxySQL可以缓存查询结果，以减轻数据库负担，并加速相同查询的响应时间。

  5. SQL过滤和重写： ProxySQL允许对SQL语句进行过滤和重写，从而实现应用层的优化和安全控制。

  6. 统计和监控： ProxySQL提供了详细的性能统计和监控信息，帮助开发者了解数据库的使用情况和瓶颈。

  7. 连接池管理： ProxySQL维护了一个连接池，可以更有效地管理数据库连接，避免连接过多导致的性能问题。

  8. 动态配置： ProxySQL支持动态的配置更改，无需重启服务即可生效，方便调整和优化。

🌲安装proxySql

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

ip端口角色
192.168.75.1283306从
192.168.75.1293306主
192.168.75.1303306从

🌲第一步，下载rpm包

github.com/sysown/prox…

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

🌲第二步，安装proxysql的环境

yum install perl-DBD-MySQL

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

🌲第三步，安装proxySql

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

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

🌲第四步，启动服务

systemctl start proxysql

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

服务命令：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@'%';

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

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

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

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

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

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

🌲第六步，添加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');

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

 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表中定义查询规则，可以实现灵活的查询路由和行为控制。

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

  1. 读写分离：通过定义查询规则，可以指定哪些查询语句应该路由到读库（从服务器）进行处理，哪些查询语句应该路由到写库（主服务器）进行处理。这样可以实现读写分离，提高系统的读取性能和可扩展性。

  2. 负载均衡：通过定义查询规则，可以指定多个读库（从服务器）之间的负载均衡策略，如轮询、最小连接数等。这样可以平衡读操作的负载，确保每个从服务器都得到合理的请求分发。

  3. 查询重写：通过定义查询规则，可以对特定类型的查询进行重写和改写。例如，可以将一些查询语句转换为优化的形式，以提高查询性能或满足特定的业务需求。

  4. 查询限制和过滤：通过定义查询规则，可以限制某些查询的执行权限、资源使用或返回结果集的大小。这样可以对系统进行保护，防止恶意查询或意外的大查询对系统造成影响。

通过在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如何实现读写分离？看这一篇就够了

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

写库

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

读库

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

准备了不同的数据

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

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

连接到proxySql的客户端

mysql -uproxyAdmin -p123456 -P6033 -h127.0.0.1

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

  1. 6032端口（管理端口）：

    • 用于与ProxySQL的管理工具或客户端进行交互。
    • 通过6032端口，可以连接到ProxySQL的管理界面，执行管理操作，如配置ProxySQL的规则、查询和修改运行时参数等。
    • 这个端口通常用于管理员或运维人员对ProxySQL进行管理和监控。

  2. 6033端口（代理端口）：

    • 用于应用程序与ProxySQL之间的通信。
    • 应用程序需要将其连接指向ProxySQL的6033端口，而不是直接连接到数据库后端。
    • ProxySQL会根据配置的规则和负载均衡算法将请求转发到后端的数据库服务器。
    • 这个端口通常用于应用程序与数据库之间的代理层，实现读写分离、负载均衡和故障转移等功能。

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

select走到了读库

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

update走到了写库

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

🌲springboot如何连接mysql？

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

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

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

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

转载自:https://juejin.cn/post/7270464435297648652
评论
请登录