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

2024-06-04 06:28

本文主要是介绍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的smtplib和email库构建一个支持图文混排,多附件,多... 目录前言一、基础配置:搭建邮件发送框架1.1 邮箱服务准备1.2 核心库导入1.3 基础发送函数二、

c++ 类成员变量默认初始值的实现

《c++类成员变量默认初始值的实现》本文主要介绍了c++类成员变量默认初始值,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,需要的朋友们下面随着小编来一起学习学习吧... 目录C++类成员变量初始化c++类的变量的初始化在C++中,如果使用类成员变量时未给定其初始值,那么它将被

PowerShell中15个提升运维效率关键命令实战指南

《PowerShell中15个提升运维效率关键命令实战指南》作为网络安全专业人员的必备技能,PowerShell在系统管理、日志分析、威胁检测和自动化响应方面展现出强大能力,下面我们就来看看15个提升... 目录一、PowerShell在网络安全中的战略价值二、网络安全关键场景命令实战1. 系统安全基线核查

Java通过驱动包(jar包)连接MySQL数据库的步骤总结及验证方式

《Java通过驱动包(jar包)连接MySQL数据库的步骤总结及验证方式》本文详细介绍如何使用Java通过JDBC连接MySQL数据库,包括下载驱动、配置Eclipse环境、检测数据库连接等关键步骤,... 目录一、下载驱动包二、放jar包三、检测数据库连接JavaJava 如何使用 JDBC 连接 mys

SQL中如何添加数据(常见方法及示例)

《SQL中如何添加数据(常见方法及示例)》SQL全称为StructuredQueryLanguage,是一种用于管理关系数据库的标准编程语言,下面给大家介绍SQL中如何添加数据,感兴趣的朋友一起看看吧... 目录在mysql中,有多种方法可以添加数据。以下是一些常见的方法及其示例。1. 使用INSERT I

Qt使用QSqlDatabase连接MySQL实现增删改查功能

《Qt使用QSqlDatabase连接MySQL实现增删改查功能》这篇文章主要为大家详细介绍了Qt如何使用QSqlDatabase连接MySQL实现增删改查功能,文中的示例代码讲解详细,感兴趣的小伙伴... 目录一、创建数据表二、连接mysql数据库三、封装成一个完整的轻量级 ORM 风格类3.1 表结构

基于Python实现一个图片拆分工具

《基于Python实现一个图片拆分工具》这篇文章主要为大家详细介绍了如何基于Python实现一个图片拆分工具,可以根据需要的行数和列数进行拆分,感兴趣的小伙伴可以跟随小编一起学习一下... 简单介绍先自己选择输入的图片,默认是输出到项目文件夹中,可以自己选择其他的文件夹,选择需要拆分的行数和列数,可以通过

Python中将嵌套列表扁平化的多种实现方法

《Python中将嵌套列表扁平化的多种实现方法》在Python编程中,我们常常会遇到需要将嵌套列表(即列表中包含列表)转换为一个一维的扁平列表的需求,本文将给大家介绍了多种实现这一目标的方法,需要的朋... 目录python中将嵌套列表扁平化的方法技术背景实现步骤1. 使用嵌套列表推导式2. 使用itert

C#读写文本文件的多种方式详解

《C#读写文本文件的多种方式详解》这篇文章主要为大家详细介绍了C#中各种常用的文件读写方式,包括文本文件,二进制文件、CSV文件、JSON文件等,有需要的小伙伴可以参考一下... 目录一、文本文件读写1. 使用 File 类的静态方法2. 使用 StreamReader 和 StreamWriter二、二进

Python使用pip工具实现包自动更新的多种方法

《Python使用pip工具实现包自动更新的多种方法》本文深入探讨了使用Python的pip工具实现包自动更新的各种方法和技术,我们将从基础概念开始,逐步介绍手动更新方法、自动化脚本编写、结合CI/C... 目录1. 背景介绍1.1 目的和范围1.2 预期读者1.3 文档结构概述1.4 术语表1.4.1 核