【Linux】ProxySQL读写分离

news/2024/10/31 7:56:53/

proxysql-2.7.1-1-centos7.x86_64.rpm

读写分离

读写分离的概念

读写分离是⼀种数据库优化技术，主要⽬的是通过将数据库的读操作和写操作分散到不同的数据库
实例上，来提⾼数据库的整体性能和可扩展性。其基本原理是让主数据库处理事务性增、改、删操
作（即写操作），⽽从数据库处理SELECT查询操作（即读操作）。通过这种⽅式，可以有效地减
轻单⼀数据库服务器的负担，特别是当读操作远多于写操作时，能够显著提升数据库的并发处理能
⼒和响应速度。
读写分离的基本实现步骤通常包括以下⼏个⽅⾯：

  1. 数据库服务器搭建主从集群：可以是⼀主⼀从，也可以是⼀主多从。
  2. 主数据库负责读写操作：承担写操作的任务，并通过复制机制将数据变更同步到从数据库。
  3. 从数据库只负责读操作：接收主数据库同步过来的数据，并提供读服务。
  4. 数据同步机制：主数据库通过复制（Replication）等⽅式将数据变更同步到从数据库，确保
    数据的⼀致性。
  5. 业务服务器的读写分离策略：业务服务器需要将写操作发送到主数据库，将读操作发送到从数
    据库。这可以通过应⽤程序代码中的逻辑控制，或者通过使⽤中间件来实现。
    读写分离的主要优点包括：
    提⾼数据库的并发处理能⼒：通过分散读写操作，减轻单⼀数据库服务器的负担。
    提升读操作的性能：从数据库可以专⻔优化读操作，提⾼查询效率。
    增强系统的可扩展性：可以通过增加从数据库的数量来线性地提升读性能。
    降低写操作的延迟：主数据库专注于写操作，可以更快地完成事务处理。

然⽽，读写分离也并⾮适⽤于所有场景。其适⽤性主要取决于具体的业务需求和数据库访问模式。

例如，对于读多写少的应⽤场景，读写分离可以带来显著的性能提升；

⽽对于写操作⾮常频繁的场景，读写分离可能⽆法有效解决问题，甚⾄可能引⼊新的复杂性。

此外，读写分离还可能带来数据

⼀致性的延迟问题，需要根据具体业务需求来权衡。
在实际应⽤中，读写分离通常结合其他数据库优化技术⼀起使⽤，如缓存、分库分表等，以综合提
升数据库系统的整体性能和可扩展性。

ProxySQL是什么

ProxySQL 是⼀款⾼性能、⾼可⽤性的 MySQL 中间件，其主要特点和功能如下：

  1. 读写分离：ProxySQL ⽀持多种⽅式的读写分离，可以根据不同的策略（如基于⽤户、基于
    schema、基于语句）将读写操作路由到不同的后端数据库实例。
  2. 查询路由：ProxySQL 具有强⼤的规则路由引擎，可以根据预定义的规则对 SQL 语句进⾏路
    由，⽀持复杂的查询改写和分库分表操作。
  3. 查询缓存：ProxySQL 提供了查询缓存功能，可以缓存查询结果，减少后端数据库的负载，提
    升查询效率。
  4. 后端节点监控：ProxySQL 能够监控后端数据库节点的状态，包括⼼跳信息、读写状态、数据
    同步延迟等，提供详细的监控数据。
  5. 故障切换：ProxySQL ⽀持故障切换功能，当主数据库出现故障时，可以⾃动切换到备⽤数据
    库，保证系统的⾼可⽤性。
  6. 动态配置：ProxySQL 的配置数据基于 SQLite 存储，⼏乎所有的配置都可以在线更改，⽆需
    重启服务。
  7. 连接池：ProxySQL ⽀持连接池功能，可以有效管理数据库连接，减少连接建⽴和释放的开
    销，提升系统性能。
  8. 跨平台⽀持：ProxySQL ⽀持多种数据库系统，包括 Amazon Aurora、RDS、ClickHouse、
    Galera、Group Replication、MariaDB Server、NDB、Percona Server 等，并且⽀持在不
    同的硬件架构（AMD64 和 ARM64）上部署。
  9. 易于集成：ProxySQL 提供了丰富的 API 和管理界⾯，便于与其他系统集成，简化了数据库操
    作和管理的流程。
  10. 社区⽀持：ProxySQL 是⼀个开源项⽬，拥有活跃的社区和⼤量的⽤户，提供了丰富的⽂档和
    资源，⽅便⽤户学习和使⽤。
    总之，ProxySQL 作为⼀个强⼤的 MySQL 代理，能够有效地提升数据库系统的性能、可扩展性和
    ⾼可⽤性，适⽤于各种规模的企业和应⽤场景。

除了ProxySQL，还有其他⼀些数据库中间件和⼯具可以实现类似的功能，例如：

  • MySQL Router：这是MySQL官⽅提供的⼀款命令⾏⼯具，它可以实现MySQL的读写分离。
  • MariaDB MaxScale：MaxScale是⼀个开源的数据库代理，它可以为MySQL和MariaDB提供
    读写分离、负载均衡、缓存等功能。
  • Tungsten Replicator：这是⼀个开源的数据复制⼯具，⽀持MySQL和MongoDB的复制。它
    可以⽤来实现读写分离和数据分⽚。
  • Vitess：Vitess是⼀个开源的数据库集群系统，主要⽤于⼤规模的MySQL数据库。它可以实现
    读写分离、数据分⽚等功能。
  • Arbitrator：Arbitrator是⼀个开源的MySQL代理，它可以实现读写分离、负载均衡等功能。

读写分离

读写分离

读写分离是⼀种数据库优化技术，主要⽬的是通过将数据库的读操作和写操作分散到不同的数据库
实例上，来提⾼数据库的整体性能和可扩展性。其基本原理是让主数据库处理事务性增、改、删操
作（即写操作），⽽从数据库处理SELECT查询操作（即读操作）。通过这种⽅式，可以有效地减
轻单⼀数据库服务器的负担，特别是当读操作远多于写操作时，能够显著提升数据库的并发处理能
⼒和响应速度。
读写分离的基本实现步骤通常包括以下⼏个⽅⾯：

  1. 数据库服务器搭建主从集群：可以是⼀主⼀从，也可以是⼀主多从。
  2. 主数据库负责读写操作：承担写操作的任务，并通过复制机制将数据变更同步到从数据库。
  3. 从数据库只负责读操作：接收主数据库同步过来的数据，并提供读服务。
  4. 数据同步机制：主数据库通过复制（Replication）等⽅式将数据变更同步到从数据库，确保
    数据的⼀致性。
  5. 业务服务器的读写分离策略：业务服务器需要将写操作发送到主数据库，将读操作发送到从数
    据库。这可以通过应⽤程序代码中的逻辑控制，或者通过使⽤中间件来实现。

安装ProxySQL

防火墙配置

#开放防⽕墙，6032是ProxySQL的管理端⼝，6033是ProxySQL的客户端接⼊端⼝
firewall-cmd --zone=public --add-port=6032/tcp--permanent
firewall-cmd --zone=public --add-port=6033/tcp--permanent
firewall-cmd --reload

下载/安装ProxySQL

wget https://github.com/sysown/proxysql/releases/download/v2.7.1/proxysql2.7.1-1-centos7.x86_64.rpm
yum localinstall -y proxysql-2.7.1-1-centos7.x86_64.rpm

启动

service proxysql start

安装MySQL

Linux安装MySQL

连接到ProxySQL Admin，它是⼀个伪装为MySQL的 ProxySQL服务端

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

在MySQLGRM主节点执⾏创建监控命令，该账户会通过主 节点同步到其他从属节点

# 通过主节点的SQL客户端登录后执⾏
#MySQL降低密码强度
set global validate_password.policy=0;
set global validate_password.length=4;
create user 'proxysql_monitor'@'%' identified with mysql_native_password b
y '123456';
create user 'proxysql_work'@'%' identified with mysql_native_password by
'123456';
GRANT ALL PRIVILEGES ON *.* To 'proxysql_monitor'@'%';
GRANT ALL PRIVILEGES ON *.* TO 'proxysql_work'@'%';
flush privileges;

在n0节点创建函数与视图，这个脚本⽤于让ProxySQL知道那个节点是主服务器。

这三段代码要分开执⾏，每⼀⾏确保执⾏成功。

在ProxySQL中执行

set mysql-monitor_username='proxysql_monitor';
set mysql-monitor_password='123456';
load mysql variables to runtime;
save mysql variables to disk;

在proxySQL中配置主存分组信息

insert into mysql_group_replication_hostgroups values (10,20,30,40,1,1,0,'1 00','proxysql'); 
load mysql servers to runtime;
save mysql servers to disk; 
# 查看信息 
select * from mysql_group_replication_hostgroups;

在MySQLMGR主节点上执⾏下⾯语句来创建 gr_member_routing_candidate _status视图，这是主从故 障切换关键所在

USE sys;
DROP VIEW IF EXISTS gr_member_routing_candidate_status;
DROP FUNCTION IF EXISTS IFZERO;
DROP FUNCTION IF EXISTS LOCATE2;
DROP FUNCTION IF EXISTS GTID_NORMALIZE;
DROP FUNCTION IF EXISTS GTID_COUNT;
DROP FUNCTION IF EXISTS gr_applier_queue_length;
DROP FUNCTION IF EXISTS gr_member_in_primary_partition;
DROP FUNCTION IF EXISTS gr_transactions_to_cert;
DELIMITER $$
CREATE FUNCTION IFZERO(a INT, b INT)
RETURNS INT
DETERMINISTIC
RETURN IF(a = 0, b, a)$$
CREATE FUNCTION LOCATE2(needle TEXT(10000), haystack TEXT(10000), offset
INT)
RETURNS INT
DETERMINISTIC
RETURN IFZERO(LOCATE(needle, haystack, offset), LENGTH(haystack) + 1)$$
CREATE FUNCTION GTID_NORMALIZE(g TEXT(10000))
RETURNS TEXT(10000)
DETERMINISTIC
RETURN GTID_SUBTRACT(g, '')$$
CREATE FUNCTION GTID_COUNT(gtid_set TEXT(10000))
RETURNS INT
DETERMINISTIC
BEGINDECLARE result BIGINT DEFAULT 0;DECLARE colon_pos INT;DECLARE next_dash_pos INT;DECLARE next_colon_pos INT;DECLARE next_comma_pos INT;SET gtid_set = GTID_NORMALIZE(gtid_set);SET colon_pos = LOCATE2(':', gtid_set, 1);WHILE colon_pos != LENGTH(gtid_set) + 1 DOSET next_dash_pos = LOCATE2('-', gtid_set, colon_pos + 1);SET next_colon_pos = LOCATE2(':', gtid_set, colon_pos + 1);SET next_comma_pos = LOCATE2(',', gtid_set, colon_pos + 1);
IF next_dash_pos < next_colon_pos AND next_dash_pos < next_comma_po
s THENSET result = result +SUBSTR(gtid_set, next_dash_pos + 1,LEAST(next_colon_pos, next_comma_pos) - (next_dash_pos +
1)) -SUBSTR(gtid_set, colon_pos + 1, next_dash_pos - (colon_pos + 1))
+ 1;ELSESET result = result + 1;END IF;SET colon_pos = next_colon_pos;END WHILE;RETURN result;
END$$
CREATE FUNCTION gr_applier_queue_length()
RETURNS INT
DETERMINISTIC
BEGINRETURN (SELECT sys.gtid_count( GTID_SUBTRACT( (SELECT
Received_transaction_set FROM performance_schema.replication_connection_s
tatus
WHERE Channel_name = 'group_replication_applier' ), (SELECT
@@global.GTID_EXECUTED) )));
END$$
CREATE FUNCTION gr_transactions_to_cert() RETURNS int(11)DETERMINISTIC
BEGINRETURN (select performance_schema.replication_group_member_stats.COUNT
_TRANSACTIONS_IN_QUEUE AS transactions_to_certFROMperformance_schema.replication_group_member_stats where MEMBER_ID
=@@SERVER_UUID );
END$$
CREATE FUNCTION my_server_uuid() RETURNS TEXT(36) DETERMINISTIC NO SQL RE
TURN (SELECT @@global.server_uuid as my_id);$$
CREATE VIEW gr_member_routing_candidate_status ASSELECTIFNULL((SELECTIF(MEMBER_STATE = 'ONLINE'AND ((SELECTCOUNT(*)FROM
performance_schema.replication_gr
oup_membersWHEREMEMBER_STATE != 'ONLINE') >= ((SE
LECTCOUNT(*)FROMperformance_schema.replication_gr
oup_members) / 2) = 0),'YES',
'NO')FROMperformance_schema.replication_group_membersJOINperformance_schema.replication_group_member_stat
s rgms USING (member_id)WHERErgms.MEMBER_ID = my_server_uuid()),'NO') AS viable_candidate,IF((SELECT((SELECTGROUP_CONCAT(performance_schema.global_va
riables.VARIABLE_VALUESEPARATOR ',')FROMperformance_schema.global_variablesWHERE(performance_schema.global_variables.VARI
ABLE_NAME IN ('read_only' , 'super_read_only'))) <> 'OFF,OFF')),'YES','NO') AS read_only,IFNULL(sys.gr_applier_queue_length(), 0) AS transactions_behind,IFNULL(sys.gr_transactions_to_cert(), 0) AS transactions_to_cert;
$$
DELIMITER ;

查看状态：

SELECT * FROM sys.gr_member_routing_candidate_status;

主服务器

从服务器：

在proxySQL添加服务器列表

insert into mysql_servers(hostgroup_id,hostname,port)values (10,'192.168.43.136',3306);
insert into mysql_servers(hostgroup_id,hostname,port)values (10,'192.168.43.137',3306);
insert into mysql_servers(hostgroup_id,hostname,port)values (10,'192.168.43.138',3306);
load mysql servers to runtime;
save mysql servers to disk;
# 测试
select * from mysql_servers;

status： 后端实例状态，默认为online，可取值为：

  • online：当先后端实例状态正常
  • shunned：临时被剔除，可能由于后端too many connections error，或者超过了可容忍延迟阀值
  • max_replication_lag
  • offline_soft：“软离线”状态，再也不接收新的链接，但已创建的链接会等待活跃事务完成
  • offline_hard：“硬离线”状态，再也不接收新的链接，已创建的链接或被强制中断，当后端实例宕机或⽹络不可达时，会出现

weight： 后端实例权重，默认为1

max_connections： 容许链接到该后端实例的最⼤链接数，不能⼤于MySQL设置的max_connections，若是后端实例 hostname:port在多个hostgroup中，以较⼤者为准，⽽不是各⾃独⽴容许的最⼤链接数，默认为 1000 max_replication_lag： 容许的最⼤延迟，master节点不受此影响，默认为0，若是>0，monitor模块监控主从延迟⼤于阀值 时，会临时把它变为shunned

max_latency_ms： mysql_ping响应时⻓，⼤于这个阀值会把它从链接池中剔除（即便是ONLINE状态），默认为0

在proxySQL配置访问用户

insert into mysql_users(username,password,default_hostgroup) values('proxysql_work','123456',10);
load mysql users to runtime;
save mysql users to disk;

在proxySQL配置路由规则，实现读写分离

insert into mysql_query_rules(active,match_pattern,destination_hostgroup,apply) values(1,'^select.*for update$',10,1),(1,'^select',30,1);
load mysql query rules to runtime;
save mysql query rules to disk;

查看读写分离的状态

select * from runtime_mysql_servers;

使用java连接ProxySQL

连接时会出现⼀个问题： Unknown system variable ‘query_cache_size’

这个错误表明系统变量 query_cache_size 未知。这通常发⽣在尝试在MySQL 8.0及以上版本中设置或获取这个变量时，因为在MySQL 8.0版本中，查询缓存（Query Cache）已被移除。

但是ProxySQL中内置的MySQL是5版本的，所以需要在ProxySQL中进⾏设置使⽤版本，最好能与安装的MySQL版本保持⼀致：

update global_variables set variable_value="8.0.18 (ProxySQL)" where variable_name='mysql-server_version';
load mysql variables to run;
save mysql variables to disk;

接下来就可以使⽤JDBC进⾏连接操作或者navicat


http://www.ppmy.cn/news/1543296.html

相关文章

Github 2024-10-30C开源项目日报 Top10

Github 2024-10-30C开源项目日报 Top10

根据Github Trendings的统计，今日(2024-10-30统计)共有10个项目上榜。根据开发语言中项目的数量，汇总情况如下： 开发语言项目数量C项目10C++项目1PHP项目1PHP：流行的Web开发脚本语言 创建周期：4710 天开发语言：C, PHP协议类型：OtherStar数量：37340 个Fork数量：7657 次…
阅读更多...
微信小程序-全局数据共享/页面间通信

微信小程序-全局数据共享/页面间通信

一.全局数据共享 声明全局的变量&#xff0c;在app.js文件里 App({//全局共享的数据globalData:{token:},//设置全局数据setToken(token){this.globalData.tokentoken}})使用 getApp() 获取全局App实例 //返回全局唯一的APP实例 const appInstancegetApp()Page({login(){con…
阅读更多...
3D点云与2D图像的相互转换：2D图像对应像素的坐标 转为3D空间的对应坐标

3D点云与2D图像的相互转换：2D图像对应像素的坐标 转为3D空间的对应坐标

2d ----> 3d 对应像素到空间坐标的转换 参考&#xff1a;深度相机&#xff0c;通过2d检测得到目标坐标系的3d检测框_深度图到相机坐标-CSDN博客
阅读更多...
【webpack学习】

【webpack学习】

webpack由于历史包袱导致复杂&#xff0c;只要把握关键流程即可 webpack的主要流程loader plugin难点&#xff1a;HMR / 懒加载 原理webpack 的优化手段 构建工具对比 webpack &#xff1a;可以打包任何资源&#xff0c;配置略复杂&#xff0c;适合项目开发rollup&#xff1…
阅读更多...
网络搜索引擎Shodan（2）

网络搜索引擎Shodan（2）

声明&#xff1a;学习视频来自b站up主 泷羽sec&#xff0c;如涉及侵权马上删除文章 声明&#xff1a;本文主要用作技术分享&#xff0c;所有内容仅供参考。任何使用或依赖于本文信息所造成的法律后果均与本人无关。请读者自行判断风险&#xff0c;并遵循相关法律法规。 感谢泷…
阅读更多...
JavaScript 赛博八音盒调制《ENDER LILIES-Harmonious》

JavaScript 赛博八音盒调制《ENDER LILIES-Harmonious》

前言 原谅我&#xff0c;在手写完数据之后实在没有精力和耐心再去整理逻辑的格式&#xff0c;而我手边又没有电脑. 最好复制下来到编辑器整理一下. js内有几个空格用了中文&#xff0c;自行替换. <div id "container"> <div id"play"> <p…
阅读更多...
【04】RabbitMQ的集群机制

【04】RabbitMQ的集群机制

1、RabbitMQ的性能监控 关于RabbitMQ的性能监控&#xff0c;在管理控制台中提供了非常丰富的展示。例如&#xff1a;首页这个整体监控页面&#xff0c;就展示了非常多详细的信息&#xff1a; 还包括消息的生产消费频率、关键组件的使用情况等等非常多的消息。都可以在这个管理…
阅读更多...
【Python单元测试】pytest框架单元测试 配置 命令行操作 测试报告 覆盖率

【Python单元测试】pytest框架单元测试 配置 命令行操作 测试报告 覆盖率

单元测试&#xff08;unit test&#xff09;&#xff0c;简称UT。本文将介绍在Python项目中&#xff0c;pytest测试框架的安装&#xff0c;配置&#xff0c;执行&#xff0c;测试报告与覆盖率 pytest简介 pytest是一款流行的&#xff0c;简单易上手的单元测试框架&#xff0c;…
阅读更多...
最新文章

Copyright @ 2022~2023