CN112685234A - Financial-level two-place three-center high-availability MySQL database implementation method - Google Patents
Financial-level two-place three-center high-availability MySQL database implementation method Download PDFInfo
- Publication number
- CN112685234A CN112685234A CN202110237817.0A CN202110237817A CN112685234A CN 112685234 A CN112685234 A CN 112685234A CN 202110237817 A CN202110237817 A CN 202110237817A CN 112685234 A CN112685234 A CN 112685234A
- Authority
- CN
- China
- Prior art keywords
- database
- proxysql
- availability
- master
- main
- 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
Links
- 238000000034 method Methods 0.000 title claims abstract description 22
- 238000011084 recovery Methods 0.000 claims description 3
- 230000000694 effects Effects 0.000 claims description 2
- 230000007547 defect Effects 0.000 description 2
- 230000009286 beneficial effect Effects 0.000 description 1
- 238000010586 diagram Methods 0.000 description 1
- 238000012986 modification Methods 0.000 description 1
- 230000004048 modification Effects 0.000 description 1
- 230000001737 promoting effect Effects 0.000 description 1
- 230000010076 replication Effects 0.000 description 1
Images
Landscapes
- Information Retrieval, Db Structures And Fs Structures Therefor (AREA)
Abstract
The invention discloses a financial grade two-place three-center high-availability MySQL database implementation method, which relates to the technical field of database high-availability architecture design, and is based on a same-domain main IDC machine room 1, a same-domain main IDC machine room 2 and a remote disaster-tolerant IDC machine room, and is characterized in that: the method comprises the following steps: s1, defining the IP, the library name, the user name and the password of the MySQL master-slave database which needs to be connected at the back end in ProxySQL, and belonging to a group for management; s2, when the application server is configured, the database master base IP is not directly used, but the host IP and the port where the ProxySQL is located are configured, when the application initiates a request, the ProxySQL middleware is accessed first, then the high-availability database master base IP located in the group where the request is located, and the port is forwarded to the corresponding database at the back end through the request. According to the financial grade two-place three-center high-availability MySQL database implementation method, the high-availability automatic switching between the main and standby of the two-place three-center is realized through the replay-manager, and the condition that services cannot be provided to the outside when a main library fails can be prevented.
Description
Technical Field
The invention relates to the technical field of database high-availability architecture design, in particular to a financial grade two-place three-center high-availability MySQL database implementation method.
Background
The use scenes of the source database MySQL in the financial field are more and more extensive, a plurality of bank core systems are changed from Oracle to the MySQL database, most of the bank core systems use a high-availability architecture scheme taking master-slave replication as a main part, but the architecture has the problem that when the MySQL master database is down (under the basic architecture scene of one master in two places and three centers and multiple slaves), the master-slave switching needs manual intervention and data comparison to be completed; when the primary and standby are switched, the configuration of the new primary database of application connection needs to be changed manually by using the IP or domain name mode used by the application connection database.
The prior art has the following defects: (1) the master-slave switching needs manual switching of database management personnel, automatic master-slave switching cannot be performed to continue providing services when the master library fails, and (2) the application direct connection database is a simple method for connecting the application direct connection database and can be used in some non-critical service systems and systems with low service continuity requirements.
Therefore, a financial-level two-place three-center high availability MySQL database implementation is needed to solve the above problems.
Disclosure of Invention
Technical problem to be solved
Aiming at the defects of the prior art, the invention provides a financial grade two-place three-center high-availability MySQL database implementation method.
(II) technical scheme
In order to achieve the above purposes, the technical scheme adopted by the invention is as follows:
a financial grade two places three centers high available MySQL database realization method is based on a same domain main IDC machine room 1, a same domain main IDC machine room 2 and a remote disaster recovery IDC machine room, and is characterized in that: the method comprises the following steps:
s1, defining the main and standby database IP, library name, user name and password of MySQL which needs to be connected at the back end in ProxySQL, and belonging to a group for management;
s2, when the application server is configured, the database master base IP is not directly used, but the host IP and the port where the ProxySQL is located are configured, when the application initiates a request, the ProxySQL middleware is accessed firstly, then the high-availability database master base IP located in the group where the request is located is positioned, and the port is forwarded to the corresponding database at the back end through the request;
s3, the health state of the back-end database is checked regularly by a relative Monitor module of ProxySQL, and once the main database crashes to the standby database, the ProxySQL can quickly identify the main-standby switch and send a new service request to a new main database;
s4, master and slave database IPs, ports, user passwords and switching modes which need to be managed are defined in the Replication-manager, and multiple sets of master and slave database IPs, ports, user passwords and switching modes can be automatically managed or switched.
Preferably, the Replication-manager deploys the same configuration of middleware in each machine room of the two places and the three centers.
Preferably, the high-availability database adopts lossless enhancement semi-synchronization and adopts a primary-secondary architecture to ensure final data consistency, and when a user submits a transaction, the user needs to wait for at least one secondary database to receive a log copy and then returns a signal for the user to successfully finish the transaction, so that the reliability of the database is ensured.
Preferably, the ProxySQL automatically identifies the master-slave architecture through relevant configuration, when the master library is down, the ProxySQL automatically identifies and automatically forwards the application request to the new master library, and the application is deployed in multiple machine rooms and multiple active deployments by the ProxySQL deployed on each application server.
(III) advantageous effects
The invention has the beneficial effects that:
1. according to the financial grade two-place three-center high-availability MySQL database implementation method, the high-availability automatic switching between the main and standby of the two-place three-center is realized through the replay-manager, and the condition that services cannot be provided to the outside when a main library fails can be prevented.
2. According to the financial grade two-place three-center high-availability MySQL database implementation method, ProxySQL can bind IP in a master-slave framework, access requests are automatically switched through a monitor function, and meanwhile, the final consistency of data is achieved by utilizing MySQL to enhance semi-synchronization.
3. The implementation method of the financial grade two-place three-center high-availability MySQL database can meet the requirements of the financial industry on data consistency, business continuity, database open source controllability and the like, and plays a positive role in promoting the popularization of MySQL in the financial industry.
4. According to the financial grade two-place three-center high-availability MySQL database implementation method, the ProxySQL + Replicatio-manager financial grade high-availability database architecture achieves RPO =0, high reliability and high availability of the database are guaranteed, and high database requirements under financial scenes are met.
Drawings
FIG. 1 is a schematic diagram of a two-place-three-center architecture according to the present invention.
Detailed Description
The technical solutions in the embodiments of the present invention will be clearly and completely described below with reference to the drawings in the embodiments of the present invention, and it is obvious that the described embodiments are only a part of the embodiments of the present invention, and not all of the embodiments. All other embodiments, which can be derived by a person skilled in the art from the embodiments given herein without making any creative effort, shall fall within the protection scope of the present invention.
As shown in fig. 1, the present invention provides a technical solution:
a financial grade two places three centers high available MySQL database implementation method, the method is based on the main IDC computer lab 1 of the same domain, the main IDC computer lab 2 of the same domain and the long-distance disaster recovery IDC computer lab, characterized by that: the method comprises the following steps:
s1, defining the IP, the library name, the user name and the password of the MySQL master-slave database which needs to be connected at the back end in ProxySQL, and belonging to a group for management;
s2, when the application server is configured, the database master base IP is not directly used, but the host IP and the port where the ProxySQL is located are configured, when the application initiates a request, the ProxySQL middleware is accessed firstly, then the high-availability database master base IP located in the group where the request is located is positioned, and the port is forwarded to the corresponding database at the back end through the request;
s3, the ProxySQL module with a relevant Monitor module can regularly check the health state of the back-end database, once the main database crashes to the standby database, the ProxySQL module can quickly identify the main/standby switch and send a new service request to the new main database, and the high availability automatic switch of the main/standby of the two-place and three-center is realized through the replay-manager, so that the condition that the service cannot be provided to the outside when the main database fails is prevented;
s4, master and slave database IPs, ports, user passwords and switching modes needing to be managed are well defined in the Replication-manager, multiple sets of master and slave database IPs, ports, user passwords and switching modes can be automatically managed or switched, and the master and slave database IPs, ports, user passwords and switching modes can be automatically managed and switched, so that manual operation of database management personnel can be greatly reduced, and operation is more convenient.
The Replication-manager deploys the middleware with the same configuration in each machine room of the two places and the three centers, realizes automatic switching of the main and standby high-availability databases of the two places and the three centers through the Replication-manager, and can prevent the failure of providing services to the outside when the main database fails.
The high-availability database adopts lossless enhancement semi-synchronization, adopts a main-backup framework and a secondary-backup framework, can ensure the final consistency of data, and when a user submits a transaction, the user needs to wait for at least one secondary database to receive a log copy and then returns a signal for the successful completion of the transaction to the user, so as to ensure the reliability of the database.
The ProxySQL automatically identifies the master-slave architecture through relevant configuration, when the master library is down, the ProxySQL automatically identifies and automatically forwards the application request to the new master library, and the ProxySQL deployed on each application server adopts multi-machine-room multi-active deployment.
The above-mentioned embodiments, objects, technical solutions and advantages of the present invention are further described in detail, it should be understood that the above-mentioned embodiments are only illustrative of the present invention and are not intended to limit the present invention, and any modifications, equivalents, improvements and the like made within the spirit and principle of the present invention should be included in the protection scope of the present invention.
Claims (4)
1. A financial grade two places three centers high available MySQL database realization method is based on a same domain main IDC machine room 1, a same domain main IDC machine room 2 and a remote disaster recovery IDC machine room, and is characterized in that: the method comprises the following steps:
s1, defining the IP, the library name, the user name and the password of the MySQL master-slave database which needs to be connected at the back end in ProxySQL, and belonging to a group for management;
s2, when the application server is configured, the database master base IP is not directly used, but the host IP and the port where the ProxySQL is located are configured, when the application initiates a request, the ProxySQL middleware is accessed firstly, then the high-availability database master base IP located in the group where the request is located is positioned, and the port is forwarded to the corresponding database at the back end through the request;
s3, the health state of the back-end database is checked regularly by a relative Monitor module of ProxySQL, and once the main database crashes to the standby database, the ProxySQL can quickly identify the main-standby switch and send a new service request to a new main database;
s4, master and slave database IPs, ports, user passwords and switching modes which need to be managed are defined in the Replication-manager, and multiple sets of master and slave database IPs, ports, user passwords and switching modes can be automatically managed or switched.
2. The method of claim 1 for implementing a financial-level two-place three-center high availability MySQL database, wherein: the Replication-manager deploys the middleware with the same configuration in each machine room of the two places and the three centers.
3. The method of claim 1 for implementing a financial-level two-place three-center high availability MySQL database, wherein: the high-availability database adopts lossless enhancement semi-synchronization and adopts a main-standby framework and a secondary-standby framework to ensure the final consistency of data, and when a user submits a transaction, a signal of successful completion of the transaction is returned to the user only by waiting for at least one secondary database to receive a log copy, so that the reliability of the database is ensured.
4. The method of claim 1 for implementing a financial-level two-place three-center high availability MySQL database, wherein: the ProxySQL automatically identifies a master-slave framework through relevant configuration, when the master library is crashed, the ProxySQL automatically identifies and automatically forwards an application request to a new master library, and the ProxySQL deployed on each application server is deployed by multiple machine rooms and multiple activities.
Applications Claiming Priority (2)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN2020113562197 | 2020-11-26 | ||
| CN202011356219 | 2020-11-26 |
Publications (1)
| Publication Number | Publication Date |
|---|---|
| CN112685234A true CN112685234A (en) | 2021-04-20 |
Family
ID=75458316
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN202110237817.0A Pending CN112685234A (en) | 2020-11-26 | 2021-03-04 | Financial-level two-place three-center high-availability MySQL database implementation method |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN112685234A (en) |
Citations (10)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| US20080215743A1 (en) * | 2007-03-02 | 2008-09-04 | Mark Frederick Wahl | System and method for validation of middleware failover behavior |
| CN103838755A (en) * | 2012-11-23 | 2014-06-04 | 景幂机械（上海）有限公司 | Remote heterogeneous disaster tolerant system of database |
| CN104503965A (en) * | 2014-10-16 | 2015-04-08 | 杭州斯凯网络科技有限公司 | High-elasticity high availability and load balancing realization method of PostgreSQL (Structured Query Language) |
| CN104579791A (en) * | 2015-01-26 | 2015-04-29 | 浪潮电子信息产业股份有限公司 | Method for achieving automatic K-DB main and standby disaster recovery cluster switching |
| CN104850471A (en) * | 2015-05-12 | 2015-08-19 | 浪潮电子信息产业股份有限公司 | Itanium platform-based method for realizing two places and three centers of DB2 database |
| CN105224637A (en) * | 2015-09-24 | 2016-01-06 | 珠海许继芝电网自动化有限公司 | A kind of based on PostgreSQL database active and standby/the comprehensive method of cluster application |
| CN109039741A (en) * | 2018-08-02 | 2018-12-18 | 郑州云海信息技术有限公司 | A kind of method and system for realizing sea of clouds management platform High Availabitity |
| CN111090527A (en) * | 2019-12-28 | 2020-05-01 | 辽宁振兴银行股份有限公司 | Mysql router-based MGR read-write separation and load balancing method |
| CN111708843A (en) * | 2020-06-18 | 2020-09-25 | 辽宁振兴银行股份有限公司 | Cross-data-center MySQL multi-activity implementation method based on MGR |
| CN111813602A (en) * | 2020-07-10 | 2020-10-23 | 厦门靠谱云股份有限公司 | Database remote backup and recovery method and system based on message middleware |
-
2021
- 2021-03-04 CN CN202110237817.0A patent/CN112685234A/en active Pending
Patent Citations (10)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| US20080215743A1 (en) * | 2007-03-02 | 2008-09-04 | Mark Frederick Wahl | System and method for validation of middleware failover behavior |
| CN103838755A (en) * | 2012-11-23 | 2014-06-04 | 景幂机械（上海）有限公司 | Remote heterogeneous disaster tolerant system of database |
| CN104503965A (en) * | 2014-10-16 | 2015-04-08 | 杭州斯凯网络科技有限公司 | High-elasticity high availability and load balancing realization method of PostgreSQL (Structured Query Language) |
| CN104579791A (en) * | 2015-01-26 | 2015-04-29 | 浪潮电子信息产业股份有限公司 | Method for achieving automatic K-DB main and standby disaster recovery cluster switching |
| CN104850471A (en) * | 2015-05-12 | 2015-08-19 | 浪潮电子信息产业股份有限公司 | Itanium platform-based method for realizing two places and three centers of DB2 database |
| CN105224637A (en) * | 2015-09-24 | 2016-01-06 | 珠海许继芝电网自动化有限公司 | A kind of based on PostgreSQL database active and standby/the comprehensive method of cluster application |
| CN109039741A (en) * | 2018-08-02 | 2018-12-18 | 郑州云海信息技术有限公司 | A kind of method and system for realizing sea of clouds management platform High Availabitity |
| CN111090527A (en) * | 2019-12-28 | 2020-05-01 | 辽宁振兴银行股份有限公司 | Mysql router-based MGR read-write separation and load balancing method |
| CN111708843A (en) * | 2020-06-18 | 2020-09-25 | 辽宁振兴银行股份有限公司 | Cross-data-center MySQL multi-activity implementation method based on MGR |
| CN111813602A (en) * | 2020-07-10 | 2020-10-23 | 厦门靠谱云股份有限公司 | Database remote backup and recovery method and system based on message middleware |
Non-Patent Citations (2)
| Title |
|---|
| YU PING 等: "Design and implementation of a MySQL database backup and recovery system", 《PROCEEDING OF THE 11TH WORLD CONGRESS ON INTELLIGENT CONTROL AND AUTOMATION》, 5 March 2015 (2015-03-05), pages 5410 - 5415 * |
| 杨芳萍 等: "数据库集群中间件MySQL Proxy探讨", 《电子制作》, 4 December 2013 (2013-12-04), pages 86 * |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| US10713135B2 (en) | Data disaster recovery method, device and system | |
| EP3694148A1 (en) | Configuration modification method for storage cluster, storage cluster and computer system | |
| JP6317856B2 (en) | Smooth controller change in redundant configuration between clusters | |
| JP5102901B2 (en) | Method and system for maintaining data integrity between multiple data servers across a data center | |
| CN101227315B (en) | Dynamic state server colony and control method thereof | |
| CN106776121B (en) | Data disaster recovery device, system and method | |
| CN105512266A (en) | Method and device for achieving operational consistency of distributed database | |
| CN102710752B (en) | Calamity is for storage system | |
| CN113515499B (en) | Database service method and system | |
| CN102622427A (en) | Method and system for read-write splitting database | |
| CN103840961A (en) | Dual-computer hot backup system | |
| US11573737B2 (en) | Method and apparatus for performing disk management of all flash array server | |
| CN108810150B (en) | Data replication method of application-level disaster recovery backup system of cooperative office system | |
| CN108964986B (en) | Application-level double-active disaster recovery system of cooperative office system | |
| CN110727709A (en) | Cluster database system | |
| WO2017041616A1 (en) | Data reading and writing method and device, double active storage system and realization method thereof | |
| CN104660386A (en) | DB2 disaster recovery high-availability improving method based on Itanium platform | |
| CN115794499B (en) | Method and system for dual-activity replication data among distributed block storage clusters | |
| CN113342261A (en) | Server and control method applied to same | |
| CN115878384A (en) | Distributed cluster based on backup disaster recovery system and construction method | |
| CN105938446B (en) | The data supported based on RDMA and hardware transactional memory replicate fault-tolerance approach | |
| CN111708843A (en) | Cross-data-center MySQL multi-activity implementation method based on MGR | |
| CN112732492A (en) | Extraction backup method and system based on cloud database | |
| CN112685234A (en) | Financial-level two-place three-center high-availability MySQL database implementation method | |
| CN111090637A (en) | Financial-level high-availability MySQL database implementation method based on A10 |
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 |