基于ProxySQL中间件实现MySQL的读写分离

目录

ProxySQL简介

ProxySQL安装

配置YUM仓库

YUM安装

关于读写分离的实现 

集群信息

测试读写分离

 测试总结

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

关于读写分离的实现 

集群信息

proxysql192.168.0.114
master192.168.0.104
slave1192.168.0.103
slave2192.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, match_pattern, destination_hostgroup,apply) VALUES (1, 1, '^(INSERT|UPDATE|DELETE)', 20, 0);
load mysql query rules to runtime;
save mysql query rules to disk;

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

ProxySQL Configuration CLI

关于上面proxysql数据库中内置库表的详解请参考官网文档Documentation - ProxySQL 

本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如若转载，请注明出处：/a/627725.html

如若内容造成侵权/违法违规/事实不符，请联系我们进行投诉反馈qq邮箱809451989@qq.com，一经查实，立即删除！

相关文章

51单片机：点亮一个LED灯

51单片机：点亮一个LED灯

1.新建工程 选择AT89C52&#xff0c;在Atmel下显示的是See Microchip 并不需要添加启动文件到文件夹中。 添加main.c文件&#xff0c;c比cpp效率高&#xff0c;.asm汇编即更底层 程序编写好后 nop(); 该函数在这个头文件里面 #include <INTRINS.H> #include <R…
阅读更多...
AI应用案例：律师服务质量评价

AI应用案例：律师服务质量评价

利用微调后的模型对律师的服务质量进行评价是一种高效且客观的方法。首先&#xff0c;我们需要一个预先训练好的模型作为基础&#xff0c;这个模型可能已经具备了处理文本或类似数据的能力。然后&#xff0c;针对律师服务质量的特定需求&#xff0c;我们对模型进行微调&#xf…
阅读更多...
电脑录屏软件有哪些？这3款神器必须要知道

电脑录屏软件有哪些？这3款神器必须要知道

在当今现代社会&#xff0c;电脑录屏软件已经成为人们日常生活中不可或缺的一部分。无论是录制游戏精彩瞬间、制作教程、还是在线会议记录&#xff0c;一款好用的电脑录屏软件都能帮助我们更高效地完成任务。可是电脑录屏软件有哪些呢&#xff1f;接下来&#xff0c;我们将介绍…
阅读更多...
MES系统在电线电缆行业生产上的应用

MES系统在电线电缆行业生产上的应用

MES系统在线缆行业的应用可以带来多重价值&#xff0c;包括提高生产效率、降低生产成本、提高产品质量、优化库存管理、改善生产环境和提高企业竞争力等方面。因此&#xff0c;在电线电缆行业中广泛应用MES系统可以提高企业的经济效益和社会效益&#xff0c;推动企业发展和行业…
阅读更多...
30W 宽电压输入 AC/DC 导轨式开关电源——TPR/DG-30-XS 系列

30W 宽电压输入 AC/DC 导轨式开关电源——TPR/DG-30-XS 系列

TPR/DG-30-XS 系列导轨式开关电源&#xff0c;额定输出功率为30W&#xff0c;产品输入范围&#xff1a;90-264VAC。提供12V、24V输出&#xff0c;12V输出时&#xff0c;工作温度范围 (-25℃~70℃)具有短路保护&#xff0c;过载保护等功能&#xff0c;并具备高效率&#xff0c;高…
阅读更多...
地平线旭日X3开发板Object Detection测试调试

地平线旭日X3开发板Object Detection测试调试

旭日X3派具有最高5TOPS的等效算力 (X3M BPU主频1.2GHz、算力5TOS; X3E BPU 主频0.6GHZ&#xff0c;算力3TOPS)&#xff0c; 因此在开发板上可以运行丰富的AI算法。 在系统/app/ai_inference目录下提供了基础算法示例&#xff1a; 01_basic_sample 系统给出的测试程序只能对给…
阅读更多...
中腾食品上海食堂承包创新食堂空间，构建企业第三文化沙龙

中腾食品上海食堂承包创新食堂空间，构建企业第三文化沙龙

在快节奏的现代职场生活中&#xff0c;企业食堂已不再是简单的餐饮供应地&#xff0c;而是逐渐演变成为员工休息休闲、互动体验、阅读思考的重要场所。中腾国际团餐产业集团通过专业的定制化食堂承包&#xff0c;在企业食堂内部打造企业第三文化空间&#xff0c;为员工提供一个…
阅读更多...
社交媒体的探索者：探寻Facebook的发展历程

社交媒体的探索者：探寻Facebook的发展历程

在当今数字化时代&#xff0c;社交媒体已经成为了人们日常生活中不可或缺的一部分&#xff0c;而Facebook作为最具影响力的社交平台之一&#xff0c;其发展历程承载着无数的探索与创新。本文将深入探讨Facebook的发展历程&#xff0c;从其创立初期到如今的全球化影响&#xff0…
阅读更多...
leetcode.环形链表问题

leetcode.环形链表问题

目录 题目1 示例 解题思路 代码实现 补充 题目2 示例 解题思路 代码实现 题目1 该题链接&#xff1a;https://leetcode.cn/problems/linked-list-cycle/description/ 示例 解题思路 要创建两个指针一个是快指针(fast)&#xff0c;另一个慢指针(slow)。快指针走两步慢指…
阅读更多...
网络安全快速入门（十二）(下) 目录结构相关命令补充

网络安全快速入门（十二）(下) 目录结构相关命令补充

12.4 补充命令 我们已经了解了linux的目录结构&#xff0c;接下来我们大概看一下针对目录及文件的一些相关命令&#xff0c; 我们本章只讲三个目录及文件相关的命令&#xff0c;分别是tree&#xff0c;find及校验文件命令&#xff0c;我们一个一个来看这些命令。 12.4.1 tree命…
阅读更多...
MySQL 编译安装

MySQL 编译安装

一、数据库的基本概念 数据&#xff08;Data&#xff09; 数据库中存储的实际信息&#xff0c;可以是数字、文本、图像等形式。 表 以行和列的形式结构化存储数据。 数据库 表的集合&#xff0c;存放数据的仓库 数据库 ——> 数据表 ——> 数据 数据库管理系统&…
阅读更多...
【OceanBase诊断调优】—— 备份恢复如何定位 NFS 服务异常

【OceanBase诊断调优】—— 备份恢复如何定位 NFS 服务异常

当备份、归档出现异常时&#xff0c;我们应该首先排除备份介质、网络是否正常&#xff0c;本文讲述如何通过系统表和日志来定位 NFS 服务异常。 适用版本 OceanBase 数据库所有版本。 如何查看备份归档异常&#xff1f; 查看备份归档状态表&#xff0c;MAX_NEXT_TIME 应与当…
阅读更多...
Adaboost集成学习 | Matlab实现基于CNN-BiLSTM-Adaboost集成学习时间序列预测（股票价格预测）

Adaboost集成学习 | Matlab实现基于CNN-BiLSTM-Adaboost集成学习时间序列预测（股票价格预测）

目录 效果一览基本介绍模型设计程序设计参考资料效果一览 基本介绍 Adaboost集成学习 | Matlab实现基于CNN-BiLSTM-Adaboost集成学习时间序列预测（股票价格预测） 模型设计 融合Adaboost的CNN-BiLSTM模型的时间序列预测，下面是一个基本的框架。 数据准备： 收集并整理用于时…
阅读更多...
MVP产品设计与数据指标

MVP产品设计与数据指标

MVP&#xff08;minimum viable product&#xff0c;最小化可行产品&#xff09;概念最早由埃里克莱斯提出&#xff0c;刊载于哈弗商业评论&#xff0c;并有出版物《精益创业》 和常规产品不同&#xff0c;MVP更侧重于对未知市场的勘测&#xff0c;用最小的代价接触客户的方法…
阅读更多...
5.14_练习

5.14_练习

1、字符串逆序 编写一个函数reverse_string(char* string)(递归实现) 实现&#xff1a;将参数字符串中的字符反向排列&#xff0c;不是逆序打印 要求&#xff1a;不能使用C函数库中的字符串操作函数 比如&#xff1a; char arr[ ]"abcdef"; 逆序之后数组的内容…
阅读更多...
Vue的学习 —— ＜vue事件处理＞

Vue的学习 —— ＜vue事件处理＞

前言 事件指的就是用户和网页交互的行为&#xff0c;这些行为&#xff0c;包括&#xff1a;鼠标单击、鼠标双击、键盘按下、抬起等。为了简化开发&#xff0c;Vue为开发者提供了事件修饰符&#xff0c;它可以与v-on配合使用&#xff0c;以便于对事件进行控制和处理&#xff0c…
阅读更多...
最新的GPT4o文档解析能力实测

最新的GPT4o文档解析能力实测

5.13日&#xff0c;openAI发布了最新的GPT模型-GPT4o&#xff0c;发布会虽短&#xff0c;但是带来的模型却提升很大&#xff0c;速度更快&#xff0c;推理能力更强&#xff0c;tokens更↓ 下面简单测一下他的文档解析能力如何&#xff1a; 1.我们使用国内某官方直连站&#xff…
阅读更多...
momentjs

momentjs

Moment.js 是一个用于处理日期和时间的 JavaScript 库&#xff0c;它提供了许多方便的函数和方法来操作、格式化和解析日期时间。官网 常见用法 格式化日期时间&#xff1a;可以使用format方法将日期时间格式化为指定的字符串格式&#xff0c;例如YYYY-MM-DD HH:mm:ss。获取日…
阅读更多...
免费申请https证书

免费申请https证书

免费申请https证书 https域名证书对提高网站排名有一定的好处&#xff0c;所以当今很多企业为了给网站一个好的安全防护&#xff0c;就会去申请该证书。如今很多企业虽然重视网站的安全防护&#xff0c;但是也重视成本&#xff0c;所以为了节约成本会考虑申请免费的https证书。…
阅读更多...
搭建Rust开发环境

搭建Rust开发环境

Windows搭建 下载&#xff1a;https://www.rust-lang.org/zh-CN/tools/install Linux搭建 这里我更推荐基于Linux搭建。 curl --proto https --tlsv1.2 -sSf https://sh.rustup.rs | sh等一会儿以后&#xff0c;会让你输入命令&#xff0c;这里输入1&#xff1a; 之后就…
阅读更多...
最新文章

Copyright @ 2022~2023