【Linux】ProxySQL读写分离

ops/2024/10/30 15:58:42/

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/ops/129625.html

相关文章

C#判断带数字的字符串数组连续性的两种方式

C#判断带数字的字符串数组连续性的两种方式

给定一个包含数字的字符串数组&#xff0c;需要判断数组中每项包含的数字是否连续增长。   如果数组项中的非数字字符有规律&#xff0c;例如给数字增加固定的前缀、后缀等&#xff0c;则较快的判断方式是提前按规则生成包含连续数字的字符串数组&#xff0c;直接判断给定的字…
阅读更多...
rtp协议：rtcp包格式和传输间隔

rtp协议：rtcp包格式和传输间隔

RTP Control Protocol -- RTCP-rtp控制协议 实时传输控制协议&#xff08;RTCP&#xff09;基于对会话中的所有参与者定期传输控制包&#xff0c;使用与数据包相同的分发机制。底层协议必须提供数据包和控制包的多路复用&#xff0c;例如使用UDP时使用不同的端口号。RTCP执行四…
阅读更多...
2024三掌柜赠书活动第三十四期：破解深度学习

2024三掌柜赠书活动第三十四期：破解深度学习

目录 前言 深度学习的基本概念 深度学习的关键技术 深度学习的实践应用 关于《破解深度学习》 编辑推荐 内容简介 作者简介 图书目录 《破解深度学习》全书速览 结束语 前言 深度学习作为人工智能领域的一个重要分支&#xff0c;近年来取得了令人瞩目的进展。从图像…
阅读更多...
Java进阶篇设计模式之四 -----适配器模式和桥接模式

Java进阶篇设计模式之四 -----适配器模式和桥接模式

前言 在上一篇中我们学习了创建型模式的建造者模式和原型模式。本篇则来学习下结构型模式的适配器模式和桥接模式。 适配器模式 简介 适配器模式是作为两个不兼容的接口之间的桥梁。这种类型的设计模式属于结构型模式&#xff0c;它结合了两个独立接口的功能。 简单的来说就…
阅读更多...
Spring 全家桶新人入门指南：服务调用最佳实践

Spring 全家桶新人入门指南：服务调用最佳实践

spring 全家桶的整体介绍 Spring 全家桶提供了一整套解决方案&#xff0c;覆盖了从基础框架到企业级应用开发的各个方面。其中&#xff0c;Spring Boot 旨在简化新 Spring 应用的初始搭建以及开发过程&#xff0c;通过约定大于配置的原则来快速启动项目&#xff1b;Spring MVC…
阅读更多...
巴西电商市场神仙打架，美客多多月蝉联访问量榜首，9月Temu位居巴西APP下载量榜首

巴西电商市场神仙打架，美客多多月蝉联访问量榜首，9月Temu位居巴西APP下载量榜首

巴西电商市场近年来呈现出强劲的增长趋势&#xff0c;预计2024年巴西电子商务市场的销售额将达到2043亿雷亚尔&#xff08;约合373亿美元&#xff09;&#xff0c;同比增长约10%。作为拉美地区最大的经济体&#xff0c;巴西吸引了众多电商平台和商家&#xff0c;巴西电商市场竞…
阅读更多...
Hyperledger Fabric有那些核心技术，和其他区块链对比Hyperledger Fabric有那些优势

Hyperledger Fabric有那些核心技术，和其他区块链对比Hyperledger Fabric有那些优势

Hyperledger Fabric是一个模块化、权限化的企业级区块链平台&#xff0c;与比特币、以太坊等公有链相比&#xff0c;Fabric主要为私有链或联盟链设计&#xff0c;适用于企业应用。它包含多项核心技术&#xff0c;使其在企业级区块链应用中具有独特优势。以下是Fabric的核心技术…
阅读更多...
Android Studio获取本地aar，最新依赖jar/aar

Android Studio获取本地aar，最新依赖jar/aar

每次清缓存后打开Android Studio的项目都要加载依赖库很久&#xff0c;那是网络要下载 依赖库和各种指针索引构建&#xff0c;本文提供获取本地依赖库的aar方法&#xff0c;然后把aar以libs的包本地方式引入&#xff0c;减少网络下载&#xff0c;会把kts和groovy的代码都展示。…
阅读更多...
最新文章

Copyright @ 2022~2023