ProxySQL + MySQL MGR 实现读写分离实战

2024-06-04 06:28
文章标签 实战 分离 实现 mysql 读写 database proxysql mgr

本文主要是介绍ProxySQL + MySQL MGR 实现读写分离实战，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

文章目录

  • 前言
  • 1、ProxySQL 介绍
    • 1.1、ProxySQL 如何工作
    • 1.2、ProxySQL 工作原理
  • 2、ProxySQL 安装与读写分离实战
    • 2.1、ProxySQL 安装
    • 2.2、读写分离配置
    • 2.3、读写分离实战
    • 2.4、SpringBoot 整合

前言

该文章实践之前，需要搭建MySQL MGR集群，关于 MySQL MGR 集群搭建，请参考该文章：
万字详解 MySQL MGR 高可用集群搭建

1、ProxySQL 介绍

1.1、ProxySQL 如何工作

ProxySQL 和 ShardingJDBC 属于竞品关系，国内使用ShardingJDBC更多，国外使用ProxySQL更多。

当我们搭建好了MGR集群，由于组复制的存在，保证每个节点的数据都是强一致的。不会出现主节点有数据，而从节点没有数据的情况。主节点可读可写，从节点只负责读操作，这是经典的MGR集群方案：
在这里插入图片描述
不过，试想这样一种情况，当主节点发生宕机，MGR集群虽然虽然可以自动进行故障转移，重新选举新的主节点，但是Java应用不能感知到数据库发生了宕机现象，依旧会把写操作发送给宕机的主节点，此时就会发生错误。
在这里插入图片描述
针对这种情况，ProxySQL就发挥出了作用，通过ProxySQL自动对MGR集群进行健康状态感知。
在这里插入图片描述
使用ProxySQL时，如果主服务器发生了宕机，会将写入请求转移到故障转移到新晋升的主节点上，并且提供了读写分离功能。
在这里插入图片描述

1.2、ProxySQL 工作原理

ProxySQL对于MGR集群节点，都新增了一个数据库用户，例如：monitor。ProxySQL通过monitor用户定时发送select查询请求，判断当前数据库节点是否可以，如果出现3次不能执行select查询，就认为该节点产生故障，就需要从集群中移除。
在这里插入图片描述
不过此时有个问题，就是ProxySQL如何判断哪个节点是主，哪个节点是从呢？ProxySQL要求每个MGR集群中的节点都在各自服务器上创建视图，作用是收集当前节点运行状态，判断当前节点是主是从。
在这里插入图片描述
当ProxySQL知道了主从之后，此时Java应用进行SQL操作，就会被ProxySQL进行路由分发了！
在这里插入图片描述

2、ProxySQL 安装与读写分离实战

2.1、ProxySQL 安装

我这里使用一台阿里云ECS（2核2G），开放22、6032、6033端口号。

首先，下载ProxySQL安装包，进行安装操作：

# 下载 ProxySQL 安装包
wget --no-check-certificate https://manongbiji.oss-cn-beijing.aliyuncs.com/ittailkshow/mgr/download/proxysql-2.2.0-1-centos7.x86_64.rpm# 安装 ProxySQL
cd /home/
yum localinstall -y proxysql-2.2.0-1-centos7.x86_64.rpm

启动 ProxySQL 服务，可以发现启动完毕：

# 启动 ProxySQL
service proxysql start

在这里插入图片描述

安装MySQL YUM仓库源：

cd /home/
wget --no-check-certificate https://repo.mysql.com/mysql80-community-release-el7-5.noarch.rpm
yum localinstall -y mysql80-community-release-el7-5.noarch.rpm
sudo yum makecache fast

将 MySQL 8 下载地址调整为腾讯云镜像（否则下载速度KB维度）：

# 修改配置
vim /etc/yum.repos.d/mysql-community.repo

# 将 mysql80-community 的 baseurl 内容替换如下
https://mirrors.cloud.tencent.com/mysql/yum/mysql-8.0-community-el7-x86_64/
# 将 mysql80-community 的 gpgcheck 内容替换为 0

在这里插入图片描述

配置好镜像后，安装好MySQL YUM源，就可以下载MySQL客户端了：

yum install -y mysql-community-client

2.2、读写分离配置

通过上述操作，前置准备已完成，此时链接上ProxySQL：

mysql -u admin -padmin -h 127.0.0.1 -P6032 --prompt='Admin> '

在这里插入图片描述

接着，将MGR集群节点配置到ProxySQL中，使用的是MGR节点的内网ip：

# 我的MGR集群的三个节点内网IP分别是：172.21.180.98 | 172.21.180.99 | 172.21.180.100
insert into mysql_servers(hostgroup_id,hostname,port) values (10,'172.21.180.98',3306);
insert into mysql_servers(hostgroup_id,hostname,port) values (10,'172.21.180.99',3306);
insert into mysql_servers(hostgroup_id,hostname,port) values (10,'172.21.180.100',3306);# 启用上述配置 + 持久化保存
load mysql servers to runtime;
save mysql servers to disk;

在这里插入图片描述

接下来这步需要在MGR主节点上进行，配置会通过主节点同步到其他从属节点：

# 使用 sys 数据库
use sys;# MySQL降低密码强度
set global validate_password.policy=0;
set global validate_password.length=4;# 创建角色（monitor监听运行状态，proxysql是java应用连接账号）
CREATE USER 'monitor'@'%' IDENTIFIED BY "monitor@1025";
CREATE USER 'proxysql'@'%' IDENTIFIED BY "proxysql@1025";
GRANT ALL PRIVILEGES ON *.* TO 'monitor'@'%' ;
GRANT ALL PRIVILEGES ON *.* TO 'proxysql'@'%' ;
FLUSH PRIVILEGES;

从这里使用Navicat进入sys数据库，连接到MGR主节点上，创建视图（一段一段执行，用Navicat是因为避免控制台出现ERROR）：
在这里插入图片描述

# 创建函数
CREATE FUNCTION my_id() RETURNS TEXT(36) DETERMINISTIC NO SQL RETURN (SELECT @@global.server_uuid as my_id);

# 函数：判断是否是主分区
CREATE FUNCTION gr_member_in_primary_partition()RETURNS VARCHAR(3)DETERMINISTICBEGINRETURN (SELECT IF( MEMBER_STATE='ONLINE' AND ((SELECT COUNT(*) FROMperformance_schema.replication_group_members WHERE MEMBER_STATE NOT IN ('ONLINE', 'RECOVERING')) >=((SELECT COUNT(*) FROM performance_schema.replication_group_members)/2) = 0),'YES', 'NO' ) FROM performance_schema.replication_group_members JOINperformance_schema.replication_group_member_stats USING(member_id) where member_id=my_id());
END

# 创建视图
CREATE VIEW gr_member_routing_candidate_status AS SELECT
sys.gr_member_in_primary_partition() as viable_candidate,
IF( (SELECT (SELECT GROUP_CONCAT(variable_value) FROM
performance_schema.global_variables WHERE variable_name IN ('read_only',
'super_read_only')) != 'OFF,OFF'), 'YES', 'NO') as read_only,
Count_Transactions_Remote_In_Applier_Queue as transactions_behind, Count_Transactions_in_queue as 'transactions_to_cert'
from performance_schema.replication_group_member_stats where member_id=my_id();

此时，回到ProxySQL服务器控制台上，设置监控账号：

set mysql-monitor_username='monitor';
set mysql-monitor_password='monitor@1025';
insert into mysql_users(username,password,active,default_hostgroup,transaction_persistent) values('proxysql','proxysql@1025',1,10,1);

设置读写组：

# 主负责写、从负责读，当MGR主库切换后，代理自动识别主从。
# ProxySQL代理每一个后端MGR集群时，都必须为这个MGR定义写组10、备写组20、读组30、离线组40，
# 注意：max_transactions_behind 是设置延迟大小，可以给大点,建议自己去开个并行复制。
insert into mysql_group_replication_hostgroups (writer_hostgroup,backup_writer_hostgroup,reader_hostgroup,
offline_hostgroup,active,max_writers,writer_is_also_reader,max_transactions_behind) values (10,20,30,40,1,1,0,100);

启用规则：

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

状态校验，编号为10是主节点，编号为30是从节点：

select hostgroup_id, hostname, port,status from runtime_mysql_servers;

在这里插入图片描述

进行读写分离配置：

# select for update走主节点，其他select走从节点
# 其他 insert update delete 走主节点
insert into mysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply)
VALUES (1,1,'^SELECT.*FOR UPDATE$',10,1),(2,1,'^SELECT',30,1);

# 配置加载 + 持久化
load mysql query rules to runtime;
save mysql query rules to disk;

2.3、读写分离实战

这里注意，进行读写分离实战测试时，要连接ProxySQL 6033端口，6032是Admin，6033才是客户端（Java程序也连6033！）：

# 链接 ProxySQL 6033
mysql -uproxysql -pproxysql@1025 -h127.0.0.1 -P6033# 创建一个test数据库，查询一条数据，写一条数据
use test;
select * from test;
insert into test values(20);
select * from test for update;# 回到6032，查看路由日志
mysql -uadmin -padmin -h127.0.0.1 -P6032
select hostgroup,digest_text from stats_mysql_query_digest order by digest_text desc limit 10;

可以看到，读写分离成功！
在这里插入图片描述

2.4、SpringBoot 整合

到这里说一下大家可能关注的点，就是ProxySQL + MySQL MGR`整合完毕后，如何通过SpringBoot进行整合，其实很简单。

只需要将SpringBoot的yml配置文件中，连接到ProxySQL就可以了

spring:datasource:driver-class-name: com.mysql.cj.jdbc.Driverurl: jdbc:mysql://123.56.41.203:6033/quick_chat?useUnicode=true&characterEncoding=utf-8&serverTimezone=Asia/Shanghai&useSSL=false&rewriteBatchedStatements=trueusername: proxysqlpassword: proxysql@1025

如果运行期间出现以下错误，要么mysql驱动版本号不对，要么就是ProxySQL需要调整了：

proxysql Caused by: java.sql.SQLException: Unknown system variable 'query_cache_size'

如果要调整ProxySQL，请执行如下命令，即可解决上述问题：

# 连接到Proxy 6032
mysql -uadmin -padmin -h127.0.0.1 -P6032# 解决 Unknown system variable 'query_cache_size' 问题
update global_variables set variable_value='8.0.4 (ProxySQL)' where variable_name='mysql-server_version';
load mysql variables to run;save mysql variables to disk;

这篇关于ProxySQL + MySQL MGR 实现读写分离实战的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！


http://www.chinasem.cn/article/1029314。

相关文章

Python实现图片分割的多种方法总结

Python实现图片分割的多种方法总结

《Python实现图片分割的多种方法总结》图片分割是图像处理中的一个重要任务,它的目标是将图像划分为多个区域或者对象,本文为大家整理了一些常用的分割方法,大家可以根据需求自行选择... 目录1. 基于传统图像处理的分割方法(1) 使用固定阈值分割图片(2) 自适应阈值分割(3) 使用图像边缘检测分割(4)
阅读更多...
Android实现在线预览office文档的示例详解

Android实现在线预览office文档的示例详解

《Android实现在线预览office文档的示例详解》在移动端展示在线Office文档（如Word、Excel、PPT）是一项常见需求,这篇文章为大家重点介绍了两种方案的实现方法,希望对大家有一定的... 目录一、项目概述二、相关技术知识三、实现思路3.1 方案一：WebView + Office Onl
阅读更多...
Mysql用户授权(GRANT)语法及示例解读

Mysql用户授权(GRANT)语法及示例解读

《Mysql用户授权(GRANT)语法及示例解读》：本文主要介绍Mysql用户授权(GRANT)语法及示例,具有很好的参考价值,希望对大家有所帮助,如有错误或未考虑完全的地方,望不吝赐教... 目录mysql用户授权（GRANT）语法授予用户权限语法GRANT语句中的<权限类型>的使用WITH GRANT
阅读更多...
C# foreach 循环中获取索引的实现方式

C# foreach 循环中获取索引的实现方式

《C#foreach循环中获取索引的实现方式》：本文主要介绍C#foreach循环中获取索引的实现方式,本文给大家介绍的非常详细,对大家的学习或工作具有一定的参考借鉴价值,需要的朋友参考下吧... 目录一、手动维护索引变量二、LINQ Select + 元组解构三、扩展方法封装索引四、使用 for 循环替代
阅读更多...
Mysql如何解决死锁问题

Mysql如何解决死锁问题

《Mysql如何解决死锁问题》：本文主要介绍Mysql如何解决死锁问题,具有很好的参考价值,希望对大家有所帮助,如有错误或未考虑完全的地方,望不吝赐教... 目录【一】mysql中锁分类和加锁情况【1】按锁的粒度分类全局锁表级锁行级锁【2】按锁的模式分类【二】加锁方式的影响因素【三】Mysql的死锁情况【1
阅读更多...
Spring Security+JWT如何实现前后端分离权限控制

Spring Security+JWT如何实现前后端分离权限控制

《SpringSecurity+JWT如何实现前后端分离权限控制》本篇将手把手教你用SpringSecurity+JWT搭建一套完整的登录认证与权限控制体系,具有很好的参考价值,希望对大家... 目录Spring Security+JWT实现前后端分离权限控制实战一、为什么要用 JWT？二、JWT 基本结构
阅读更多...
Java实现优雅日期处理的方案详解

Java实现优雅日期处理的方案详解

《Java实现优雅日期处理的方案详解》在我们的日常工作中,需要经常处理各种格式,各种类似的的日期或者时间,下面我们就来看看如何使用java处理这样的日期问题吧,感兴趣的小伙伴可以跟随小编一起学习一下... 目录前言一、日期的坑1.1 日期格式化陷阱1.2 时区转换二、优雅方案的进阶之路2.1 线程安全重构2
阅读更多...
Android实现两台手机屏幕共享和远程控制功能

Android实现两台手机屏幕共享和远程控制功能

《Android实现两台手机屏幕共享和远程控制功能》在远程协助、在线教学、技术支持等多种场景下,实时获得另一部移动设备的屏幕画面,并对其进行操作,具有极高的应用价值,本项目旨在实现两台Android手... 目录一、项目概述二、相关知识2.1 MediaProjection API2.2 Socket 网络
阅读更多...
使用Python实现图像LBP特征提取的操作方法

使用Python实现图像LBP特征提取的操作方法

《使用Python实现图像LBP特征提取的操作方法》LBP特征叫做局部二值模式,常用于纹理特征提取,并在纹理分类中具有较强的区分能力,本文给大家介绍了如何使用Python实现图像LBP特征提取的操作方... 目录一、LBP特征介绍二、LBP特征描述三、一些改进版本的LBP1.圆形LBP算子2.旋转不变的LB
阅读更多...
Redis消息队列实现异步秒杀功能

Redis消息队列实现异步秒杀功能

《Redis消息队列实现异步秒杀功能》在高并发场景下,为了提高秒杀业务的性能,可将部分工作交给Redis处理,并通过异步方式执行,Redis提供了多种数据结构来实现消息队列,总结三种,本文详细介绍Re... 目录1 Redis消息队列1.1 List 结构1.2 Pub/Sub 模式1.3 Stream 结
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2