CN110569307A - 一种基于ProxySQL和MGR的MySQL读写分离方法 - Google Patents

一种基于ProxySQL和MGR的MySQL读写分离方法 Download PDF

Info

Publication number
CN110569307A
CN110569307A CN201910849796.0A CN201910849796A CN110569307A CN 110569307 A CN110569307 A CN 110569307A CN 201910849796 A CN201910849796 A CN 201910849796A CN 110569307 A CN110569307 A CN 110569307A
Authority
CN
China
Prior art keywords
mysql
proxysql
node
read
group
Prior art date
Legal status (The legal status is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the status listed.)
Pending
Application number
CN201910849796.0A
Other languages
English (en)
Inventor
刘小龙
唐军
周文龙
Current Assignee (The listed assignees may be inaccurate. Google has not performed a legal analysis and makes no representation or warranty as to the accuracy of the list.)
Sichuan Changhong Electric Co Ltd
Original Assignee
Sichuan Changhong Electric Co Ltd
Priority date (The priority date is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the date listed.)
Filing date
Publication date
Application filed by Sichuan Changhong Electric Co Ltd filed Critical Sichuan Changhong Electric Co Ltd
Priority to CN201910849796.0A priority Critical patent/CN110569307A/zh
Publication of CN110569307A publication Critical patent/CN110569307A/zh
Pending legal-status Critical Current

Links

Classifications

    • G—PHYSICS
    • G06—COMPUTING; CALCULATING OR COUNTING
    • G06F—ELECTRIC DIGITAL DATA PROCESSING
    • G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
    • G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
    • G06F16/27—Replication, distribution or synchronisation of data between databases or within a distributed database system; Distributed database system architectures therefor
    • G06F16/275—Synchronous replication

Landscapes

  • Engineering & Computer Science (AREA)
  • Databases & Information Systems (AREA)
  • Theoretical Computer Science (AREA)
  • Computing Systems (AREA)
  • Data Mining & Analysis (AREA)
  • Physics & Mathematics (AREA)
  • General Engineering & Computer Science (AREA)
  • General Physics & Mathematics (AREA)
  • Information Retrieval, Db Structures And Fs Structures Therefor (AREA)

Abstract

本发明公开了一种基于ProxySQL和MGR的MySQL读写分离方法，包括步骤：A.至少准备三个节点，分别装上Centos系统及MySQL数据库；B.在各节点上分别创建复制账号，并安装GR插件；C.在各节点上分别配置集群参数文件，并重启数据库；D.在各节点上分别创建复制通道，并启动GR服务，其中，多个节点组成一个复制组节点；E.在复制组节点上初始化ProxySQL，并完成数据库、用户、分组的配置；F.在MySQL端执行监控脚本，添加监控视图及其依赖的函数；G.在ProxySQL中配置读写分离的规则。本发明的方法使MySQL架构可以应用于对数据一致性要求很高的场景，对集群的写扩展性上有比较明显的提高。

Description

一种基于ProxySQL和MGR的MySQL读写分离方法
技术领域
本发明涉及数据库技术领域，特别涉及一种基于ProxySQL和MGR的MySQL读写分离方法。
背景技术
开源数据库技术发展至今，尤其是MySQL的应用，在互联网行业、传统行业都有举足轻重的地位。随着互联网快速发展，大数据量、高并发量的请求对数据库架构提出了更高的要求。而读写分离方案就是一种非常好的分散读写IO压力，提高集群资源利用率的解决方案。
目前的MySQL的读写分离方案，基本上是基于原生的异步复制或者半同步复制，都不能完全的保证集群节点的数据一致性，这对于数据一致性要求很高的金融系统很难接受。常规方案在读和写的节点分配中，为了防止并发条件下的写冲突，也只能做到单个节点的写入，这使得数据库集群的写能力的扩展受到局限性。
发明内容
本发明的目的是克服上述背景技术中不足，提供一种基于ProxySQL和MGR的MySQL读写分离方法，在前端采用ProxySQL做中间路由，后端采用MGR做集群架构，提供了一种强数据一致性的、多写模式下的读写分离解决方案，使MySQL架构可以应用于对数据一致性要求很高的场景，对集群的写扩展性上有比较明显的提高。
为了达到上述的技术效果，本发明采取以下技术方案：
一种基于ProxySQL和MGR的MySQL读写分离方法，包括以下步骤：
A.至少准备三个节点，分别装上Centos系统及MySQL数据库；
B.在各节点上分别创建复制账号，并安装GR插件；其中，GR插件需要额外的安装，安装完成后会在performance_schema下产生一系列相关的系统表，并且在参数文件中支持组复制相关的参数，其中复制账号是用来在各节点中传输的数据的登录凭证；
C.在各节点上分别配置集群参数文件，并重启数据库；
D.在各节点上分别创建复制通道，并启动GR服务，其中，多个节点组成一个复制组节点；
E.在复制组节点上初始化ProxySQL，并完成数据库、用户、分组的配置；
F.在MySQL端执行监控脚本，添加监控视图及其依赖的函数；
G.在ProxySQL中配置读写分离的规则；
本发明的基于ProxySQL和MGR的MySQL读写分离方法中，MGR采用多主模式，后端集群提供多节点可写服务，复制组在基于Paxos协议下实现分布式数据的最终一致性，通过ProxySQL配置语句规则定制SQL路由,进行中间层读写分离，并对集群节点动态分组，可读写组成员周期性的自动检测，使MySQL架构可以应用于对数据一致性要求很高的场景，在集群的读与写的扩展性上都有比较明显的提高。
进一步地，所述步骤A中Centos系统为版本号不低于Centos7的系统，MySQL数据库为版本号不低于MySQL5.7.17的数据库，MGR的集群是在MySQL5.7.17推出的第一个GA版本，因此数据库的版本是在5.7.17以上。
进一步地，所述步骤D中由3个节点组成一个复制组节点，在事务提交过程中发生组间通讯时，由2个节点决议通过这个事务，事务才能提交并响应。
进一步地，在复制组节点的始发节点上，当事务准备好提交时，始发节点广播写入值和对应的写入集，并为该事务建立一个全局的顺序，复制组节点的其他所有节点以相同的顺序接收同一组事务，最后以相同的顺序应用相同的更改，以确保组内一致。
进一步地，所述步骤E中ProxySQL版本不低于1.4.0，ProxySQL从1.4.0版本开始增加对MGR的原生支持。
进一步地，所述步骤E中，将数据库信息写入mysql_servers中，用户信息写入mysql_users中，MGR的分组规则则配置在mysql_group_replication_hostgroups中。
进一步地，所述步骤G中，具体通过对SQL正则匹配，把SELECT语句路由到只读组，把DML语句路由到可写组，由此完成读写分离。
本发明与现有技术相比，具有以下的有益效果：
本发明的基于ProxySQL和MGR的MySQL读写分离方法，可实现保证集群节点的强数据一致性，避免了主从节点可能出现的数据不一致，且提供了读写模式，使得集群的写扩展性得到明显的提高，同时还实现了对业务完全透明的读写分离，提高了高并发下的IO能力，在中间件层形成了连接池和查询缓存，减少了数据库端的资源消耗。
附图说明
图1是MGR实现分布式下数据的最终一致性的流程示意图。
图2是ProxySQL+MGR实现读写分离的架构示意图。
具体实施方式
下面结合本发明的实施例对本发明作进一步的阐述和说明。
实施例：
实施例一：
一种基于ProxySQL和MGR的MySQL读写分离方法，包括以下步骤：
步骤1.至少准备三个节点，分别装上Centos系统及MySQL数据库。
具体的，本实施例中，Centos系统为版本号不低于Centos7的系统，MySQL数据库为版本号不低于MySQL5.7.17的数据库，本实施例中具体采用于Centos7的系统及MySQL8.0.16的数据库。
步骤2.在各节点上分别创建复制账号，并安装GR插件。
其中，GR插件需要额外的安装，安装完成后会在performance_schema下产生一系列相关的系统表，并且在参数文件中支持组复制相关的参数，其中复制账号是用来在各节点中传输的数据的登录凭证；
步骤3.在各节点上分别配置集群参数文件，并重启数据库。
其中，MGR必须要开启GTID功能。本实施例中，在当前MySQL版本中，需要设置binlog_checksum＝none，并且binlog_format＝row。MGR中本地成员的地址由参数group_replication_local_address决定，其他成员的地址由参数group_replication_group_seeds决定。
步骤4.在各节点上分别创建复制通道，并启动GR服务，其中，多个节点组成一个复制组节点。
如图1所示，在MGR中3个节点组成一个复制组，concensus层为一致性协议层，在事务提交过程中，发生组间通讯，且由2个节点决议(certify)通过这个事务，事务才能最终得以提交并响应。组内各个节点维护各自的数据副本，通过一致性协议实现原子消息和全局有序消息。
步骤5.在复制组节点上初始化ProxySQL，并完成数据库、用户、分组的配置。
其中，数据库信息写入mysql_servers中，用户信息写入mysql_users中，MGR的分组规则配置在mysql_group_replication_hostgroups中。ProxySQL从1.4.0版本开始增加对MGR的原生支持，本实例ProxySQL选用的是2.0.6的版本。
步骤6.在MySQL端执行监控脚本，添加监控视图及其依赖的函数。
具体为，最终在MySQL里创建一张sys.gr_member_routing_candidate_status的视图，其中，ProxySQL也是根据这些数据来实现监测节点的监控及数据延迟。且脚本成功后，在runtime_mysql_servers里可以看到分组规则已经自动分组。
步骤7.在ProxySQL中配置读写分离的规则。
如图2所示，通过对SQL正则匹配，把SELECT语句路由到只读组，把DML语句路由到可写组，由此完成读写分离。而读写组的成员分配，是由MySQL中gr_member_routing_candidate_status的数据来动态分配，而本身MGR集群节点是多主模式，由此可实现故障自动切换。
由上可知，本发明的基于ProxySQL和MGR的MySQL读写分离方法，主要具有以下特点：
特点1：MGR采用多主模式，后端集群提供多节点可写服务。
即本发明的基于ProxySQL和MGR的MySQL读写分离方法中，MGR(Mysql GroupReplication)集群可支持多主模式，即可以满足集群中所有节点都可以写入数据。且集群中的成员不再有主从之分，成员的角色是完全对等的，同时，写操作可以被分散到任意一个节点中，使得整个系统的IO能力得到加强，且整个集群在写扩展性上有了保障，收缩或者扩张节点都不需要成员角色的重新选主的过程。
特点2：复制组在基于Paxos协议下实现分布式数据的最终一致性。
其中，引入组复制主要是为了解决传统异步复制和半同步复制可能产生数据不一致的问题，复制组由多个server成员组成，并且每个成员都可以独立的执行事务，但是所有的读写(RW)事务只有在冲突检测成功后才会提交，在始发server上，当事务准备好提交时，该server会广播写入值(已改变的行)和对应的写入集(已更新行的唯一标识符)，然后为该事务建立一个全局的顺序，所有server成员以相同的顺序接收同一组事务，最后以相同的顺序应用相同的更改，以确保组内一致，组复制依靠分布式一致性协议(Paxos协议)，实现了分布式下数据的最终一致性。
特点3：通过ProxySQL配置语句规则定制SQL路由，进行中间层读写分离。
即通过定制基于用户、基于Schema、基于语句的规则，在中间层对SQL进行路由转发，客户端的操作语句首先发送至中间层，ProxySQL在这一层按配置规则进行读写操作分离，一方面把只读操作负载均衡到只读组的各个节点，以提高并发下的读性能；另一方面把读写操作分离后，减少读写同时带来的IO竞争。
特点4：并对集群节点动态分组，可读写组成员周期性的自动检测。
动态分组的配置保证了读写分离方案的高可用性,ProxySQL对后端节点做周期性的连接心跳和数据延迟检测，来维持可写组和只读组成员的动态分配，根据业务系统本身的读写压力配比，为只读组和可写组设置成员数量和权重，自动剔除掉不可用的成员。
可以理解的是，以上实施方式仅仅是为了说明本发明的原理而采用的示例性实施方式，然而本发明并不局限于此。对于本领域内的普通技术人员而言，在不脱离本发明的精神和实质的情况下，可以做出各种变型和改进，这些变型和改进也视为本发明的保护范围。

Claims (7)

1.一种基于ProxySQL和MGR的MySQL读写分离方法，其特征在于，包括以下步骤：
A.至少准备三个节点，分别装上Centos系统及MySQL数据库；
B.在各节点上分别创建复制账号，并安装GR插件；
C.在各节点上分别配置集群参数文件，并重启数据库；
D.在各节点上分别创建复制通道，并启动GR服务，其中，多个节点组成一个复制组节点；
E.在复制组节点上初始化ProxySQL，并完成数据库、用户、分组的配置；
F.在MySQL端执行监控脚本，添加监控视图及其依赖的函数；
G.在ProxySQL中配置读写分离的规则。
2.根据权利要求1所述的一种基于ProxySQL和MGR的MySQL读写分离方法，其特征在于，所述步骤A中Centos系统为版本号不低于Centos7的系统，MySQL数据库为版本号不低于MySQL5.7.17的数据库。
3.根据权利要求1所述的一种基于ProxySQL和MGR的MySQL读写分离方法，其特征在于，所述步骤D中由3个节点组成一个复制组节点，在事务提交过程中发生组间通讯时，由2个节点决议通过这个事务，事务才能提交并响应。
4.根据权利要求3所述的一种基于ProxySQL和MGR的MySQL读写分离方法，其特征在于，在复制组节点的始发节点上，当事务准备好提交时，始发节点广播写入值和对应的写入集，并为该事务建立一个全局的顺序，复制组节点的其他所有节点以相同的顺序接收同一组事务，最后以相同的顺序应用相同的更改，以确保组内一致。
5.根据权利要求1所述的一种基于ProxySQL和MGR的MySQL读写分离方法，其特征在于，所述步骤E中ProxySQL版本不低于1.4.0。
6.根据权利要求5所述的一种基于ProxySQL和MGR的MySQL读写分离方法，其特征在于，所述步骤E中，将数据库信息写入mysql_servers中，用户信息写入mysql_users中，MGR的分组规则则配置在mysql_group_replication_hostgroups中。
7.根据权利要求1至6中任一所述的一种基于ProxySQL和MGR的MySQL读写分离方法，其特征在于，所述步骤G中，具体通过对SQL正则匹配，把SELECT语句路由到只读组，把DML语句路由到可写组，由此完成读写分离。
CN201910849796.0A 2019-09-09 2019-09-09 一种基于ProxySQL和MGR的MySQL读写分离方法 Pending CN110569307A (zh)

Priority Applications (1)

Application Number Priority Date Filing Date Title
CN201910849796.0A CN110569307A (zh) 2019-09-09 2019-09-09 一种基于ProxySQL和MGR的MySQL读写分离方法

Applications Claiming Priority (1)

Application Number Priority Date Filing Date Title
CN201910849796.0A CN110569307A (zh) 2019-09-09 2019-09-09 一种基于ProxySQL和MGR的MySQL读写分离方法

Publications (1)

Publication Number Publication Date
CN110569307A true CN110569307A (zh) 2019-12-13

Family

ID=68778634

Family Applications (1)

Application Number Title Priority Date Filing Date
CN201910849796.0A Pending CN110569307A (zh) 2019-09-09 2019-09-09 一种基于ProxySQL和MGR的MySQL读写分离方法

Country Status (1)

Country Link
CN (1) CN110569307A (zh)

Cited By (3)

* Cited by examiner, † Cited by third party
Publication number Priority date Publication date Assignee Title
CN111090637A (zh) * 2019-12-20 2020-05-01 辽宁振兴银行股份有限公司 一种金融级基于A10的高可用MySQL数据库实现方法
CN111090527A (zh) * 2019-12-28 2020-05-01 辽宁振兴银行股份有限公司 一种基于mysql router 的MGR 读写分离和负载均衡方法
CN112667711A (zh) * 2020-12-29 2021-04-16 浪潮云信息技术股份公司 一种MySQL只读实例管理方法及系统

Citations (2)

* Cited by examiner, † Cited by third party
Publication number Priority date Publication date Assignee Title
CN102622427A (zh) * 2012-02-27 2012-08-01 杭州闪亮科技有限公司 读写分离数据库的方法及系统
CN108984602A (zh) * 2018-06-05 2018-12-11 网易（杭州）网络有限公司 一种数据库控制方法和数据库系统

Patent Citations (2)

* Cited by examiner, † Cited by third party
Publication number Priority date Publication date Assignee Title
CN102622427A (zh) * 2012-02-27 2012-08-01 杭州闪亮科技有限公司 读写分离数据库的方法及系统
CN108984602A (zh) * 2018-06-05 2018-12-11 网易（杭州）网络有限公司 一种数据库控制方法和数据库系统

Non-Patent Citations (1)

* Cited by examiner, † Cited by third party
Title
散尽浮华: "ProxySQL+MGR实现读写分离和主节点故障无感知切换 - 完整操作记录", 《HTTPS://WWW.CNBLOGS.COM/KEVINGRACE/P/10384691.HTML》 *

Cited By (3)

* Cited by examiner, † Cited by third party
Publication number Priority date Publication date Assignee Title
CN111090637A (zh) * 2019-12-20 2020-05-01 辽宁振兴银行股份有限公司 一种金融级基于A10的高可用MySQL数据库实现方法
CN111090527A (zh) * 2019-12-28 2020-05-01 辽宁振兴银行股份有限公司 一种基于mysql router 的MGR 读写分离和负载均衡方法
CN112667711A (zh) * 2020-12-29 2021-04-16 浪潮云信息技术股份公司 一种MySQL只读实例管理方法及系统

Similar Documents

Publication Publication Date Title
CN108234307B (zh) 网络方法、网络装置和非暂时性计算机可读存储介质
CN111078121B (zh) 一种分布式存储系统数据迁移方法、系统、及相关组件
CN108234306B (zh) 网络装置、网络方法和计算机可读存储介质
CN108234302B (zh) 保持网络装置用的分布式操作系统中的一致性
US10789217B2 (en) Hierarchical namespace with strong consistency and horizontal scalability
US7877511B1 (en) Method and apparatus for adaptive services networking
US10027748B2 (en) Data replication in a tree based server architecture
CN110213352B (zh) 名字空间统一的分散自治存储资源聚合方法
JP2008533564A (ja) データ管理のための方法および装置
CN113535656B (zh) 数据访问方法、装置、设备及存储介质
US20130332484A1 (en) Data Management and Indexing Across a Distributed Database
CN110569307A (zh) 一种基于ProxySQL和MGR的MySQL读写分离方法
US20190340171A1 (en) Data Redistribution Method and Apparatus, and Database Cluster
US10712964B2 (en) Pre-forking replicas for efficient scaling of a distributed data storage system
US20070038679A1 (en) Dynamic configuration updating in a storage area network
US11068499B2 (en) Method, device, and system for peer-to-peer data replication and method, device, and system for master node switching
US8082344B2 (en) Transaction manager virtualization
CN112199427A (zh) 一种数据处理方法和系统
US9864689B2 (en) Near cache distribution in in-memory data grid (IMDG) non structured query language (NO-SQL) environments
CN102521063A (zh) 一种适用于虚拟机迁移和容错的共享存储方法
US10230567B2 (en) Management of a plurality of system control networks
WO2017113962A1 (zh) 访问分布式数据库的方法和分布式数据服务的装置
WO2016065776A1 (zh) 一种紧耦合可扩展的大数据交互方法
CN110569302A (zh) 一种基于lucene的分布式集群的物理隔离的方法及装置
CN111078120A (zh) 一种分布式文件系统的数据迁移方法、系统及相关组件

Legal Events

Date Code Title Description
PB01 Publication
PB01 Publication
SE01 Entry into force of request for substantive examination
SE01 Entry into force of request for substantive examination
RJ01 Rejection of invention patent application after publication

Application publication date: 20191213

RJ01 Rejection of invention patent application after publication