A database disaster recovery method and device
By building a heterogeneous MySQL database cluster in a distributed database system, the problem of total collapse caused by system-level failures was solved, achieving efficient disaster recovery capabilities and rapid service restoration.
Patent Information
- Authority / Receiving Office
- CN · China
- Patent Type
- Applications(China)
- Current Assignee / Owner
- NETSUNION CLEARING CORP
- Filing Date
- 2024-11-29
- Publication Date
- 2026-06-09
Smart Images
Figure CN122173334A_ABST
Abstract
Description
Technical Field
[0001] This article relates to database disaster recovery technology, and more particularly to a database disaster recovery method and device. Background Technology
[0002] With the rapid development of Internet technology, more and more data is stored in databases. To ensure that data is not lost, data owners usually adopt a distributed database architecture, deploying the database in multiple data centers to achieve data disaster recovery.
[0003] In related technologies, distributed databases are often designed with a multi-center architecture. This design has a certain degree of disaster recovery capability, but it still cannot cope with failure scenarios caused by system-level failures of distributed databases. Summary of the Invention
[0004] This application provides a database disaster recovery method and apparatus that can cope with failure scenarios caused by distributed database system-level failures, thereby improving the database disaster recovery capability.
[0005] On the one hand, this application provides a database disaster recovery method, including:
[0006] The structure and authorization information of the distributed database cluster are obtained and written into the MySQL database cluster of the system database management system; wherein, the storage layer of the distributed database cluster is built using the MySQL architecture; the geographical location and primary / backup role of each center in the MySQL database cluster correspond one-to-one with each center of the distributed database.
[0007] The MySQL database cluster synchronizes data from the distributed database cluster based on the structure information and authorization information written into the MySQL database cluster.
[0008] When a database cluster switch command is received or a failure of the distributed database cluster is detected, the MySQL database cluster will replace the distributed database cluster to provide services to the application.
[0009] On the other hand, this application provides a database disaster recovery device, including: a memory and a processor, wherein the memory is used to store an executable program;
[0010] The processor is used to read and execute the executable program to implement the database disaster recovery method described above.
[0011] Compared with related technologies, this application includes pre-constructing MySQL database clusters with geographical locations and primary / backup roles corresponding one-to-one with each center of the distributed database. When no database cluster switch is required, the MySQL database cluster synchronizes data from the distributed database cluster based on the structure and authorization information of the distributed database cluster written into the MySQL database cluster. When a database cluster switch instruction is received or a failure of the distributed database cluster is detected, and a database cluster switch is required, the MySQL database cluster replaces the distributed database cluster to provide services to the application. Therefore, it realizes the response to failure scenarios caused by system-level failures of the distributed database, and greatly improves the disaster recovery capability of the database.
[0012] Other features and advantages of this application will be set forth in the following description, and will be apparent in part from the description, or may be learned by practicing the application. Other advantages of this application can be realized and obtained by means of the solutions described in the description and the accompanying drawings. Attached Figure Description
[0013] The accompanying drawings are used to provide an understanding of the technical solutions of this application and constitute a part of the specification. They are used together with the embodiments of this application to explain the technical solutions of this application and do not constitute a limitation on the technical solutions of this application.
[0014] Figure 1 A flowchart illustrating a database disaster recovery method provided in an embodiment of this application;
[0015] Figure 2 This is a schematic diagram of the architecture of a distributed database in the prior art;
[0016] Figure 3 This is a schematic diagram of the architecture of a heterogeneous database cluster formed by a distributed database cluster and a MySQL database cluster, provided for an embodiment of this application.
[0017] Figure 4 This application provides a schematic diagram illustrating the process of synchronizing data between a MySQL database cluster and a distributed database cluster in a heterogeneous database cluster, as illustrated in an embodiment of the present application.
[0018] Figure 5 A schematic diagram illustrating the process of synchronizing data from the distributed database cluster to a MySQL database cluster in another heterogeneous database cluster, as provided in an embodiment of this application.
[0019] Figure 6 This is a schematic diagram illustrating how a MySQL database cluster in a heterogeneous database cluster provides services to an application, as provided in an embodiment of this application.
[0020] Figure 7 This is a schematic diagram illustrating how a MySQL database cluster in a heterogeneous database cluster provides services to an application, as provided in an embodiment of this application. Detailed Implementation
[0021] This application describes several embodiments, but these descriptions are exemplary and not restrictive, and it will be apparent to those skilled in the art that many more embodiments and implementations are possible within the scope of the embodiments described herein. Although many possible combinations of features are shown in the drawings and discussed in the detailed description, many other combinations of the disclosed features are also possible. Unless specifically limited, any feature or element of any embodiment may be used in combination with, or may replace, any feature or element of any other embodiment.
[0022] This application includes and contemplates combinations of features and elements known to those skilled in the art. The embodiments, features, and elements disclosed in this application may also be combined with any conventional features or elements to form a unique inventive scheme as defined by the claims. Any feature or element of any embodiment may also be combined with features or elements from other inventive schemes to form another unique inventive scheme as defined by the claims. Therefore, it should be understood that any feature shown and / or discussed in this application may be implemented individually or in any suitable combination. Therefore, the embodiments are not limited except by the limitations imposed by the appended claims and their equivalents. Furthermore, various modifications and changes may be made within the scope of the appended claims.
[0023] Furthermore, in describing representative embodiments, the specification may have presented methods and / or processes as a specific sequence of steps. However, the method or process should not be limited to the specific order of steps described herein, to the extent that it does not depend on such a specific order. As will be understood by those skilled in the art, other sequences of steps are also possible. Therefore, the specific order of steps set forth in the specification should not be construed as a limitation of the claims. Moreover, the claims concerning the method and / or process should not be limited to the steps performed in the written order, and those skilled in the art will readily understand that these orders can be varied and still remain within the spirit and scope of the embodiments of this application.
[0024] This application provides a database disaster recovery method, such as... Figure 1 As shown, it includes:
[0025] Step 101: Obtain the structure information and authorization information of the distributed database cluster, and write the obtained structure information and authorization information of the distributed database cluster into the MySQL database cluster; wherein, the storage layer of the distributed database cluster is built using the MySQL architecture; the geographical location and primary / backup role of each center in the MySQL database cluster correspond one-to-one with each center of the distributed database;
[0026] Step 102: Synchronize data between the MySQL database cluster and the distributed database cluster based on the structure information and authorization information of the distributed database cluster written into the MySQL database cluster;
[0027] Step 103: When a database cluster switch instruction is received or a failure of the distributed database cluster is detected, the MySQL database cluster replaces the distributed database cluster to provide services to the application.
[0028] For example, the authorization information of a distributed database cluster consists of usernames and passwords used for self-management during the establishment of the distributed database cluster, as well as the corresponding permission information. The structural information includes database structure information and table structure information. The MySQL database cluster needs to use this same database and table structure and permission information as the distributed database cluster to recognize the data synchronized to itself from the distributed database cluster. Otherwise, the MySQL database cluster cannot recognize the data imported into itself. In other words, the structural information and authorization information are for the purpose of ensuring that the data has the corresponding database and table structure and permissions when it is synchronized from the distributed database cluster to the MySQL database cluster.
[0029] For example, the MySQL database cluster can replace the distributed database cluster to provide services to the application, and the switch can be initiated immediately by clicking on the interface. This is suitable for scenarios where a switchover drill is being conducted proactively.
[0030] For example, after the MySQL database cluster replaces the distributed database cluster to provide services to the application, the distributed database cluster is no longer used.
[0031] The architecture of a distributed database with three locations and three centers is as follows: Figure 2 As shown, the three centers of this distributed database are located in city 1, city 2 and city 3 respectively. The master data node is located in city 1, so the master center is located in city 1. The application obtains services from the master center. That is, the computing nodes of city 1, city 2 and city 3 are all connected to the master data node located in city 1. The master data node synchronizes data with the backup data nodes that belong to the same master center as itself, as well as the backup data nodes located in city 2 and city 3.
[0032] In related technologies, distributed databases that use MySQL as the storage layer combined with self-developed or open-source middleware proxies are called middleware-based distributed databases. Because the underlying storage is MySQL, many companies choose a distributed database architecture with MySQL as the storage layer to mitigate compatibility risks if the original database is also running MySQL. Distributed databases with a three-site, three-center architecture design already possess a certain degree of disaster recovery capability, but they cannot handle system-level failures or complete crashes. To prevent system-level failures in distributed databases, the original MySQL database cluster needs to be synchronized as a disaster recovery database, ensuring the ability to switch back to the original MySQL database cluster at any time.
[0033] The database disaster recovery method provided in this application pre-constructs a MySQL database cluster that corresponds one-to-one with the geographical location and primary / backup role of each center in a three-site, three-center distributed database. Specifically, the databases in the MySQL database cluster are deployed in cities 1, 2, and 3, with the primary database set up in city 1. The three-site, three-center distributed database cluster and the MySQL database cluster form a heterogeneous database cluster. Figure 3 As shown.
[0034] The database disaster recovery method provided in this application pre-constructs MySQL database clusters with geographical locations and primary / backup roles corresponding one-to-one with each center of the distributed database. When no database cluster switch is required, the MySQL database cluster synchronizes data from the distributed database cluster based on the structure and authorization information of the distributed database cluster written into the MySQL database cluster. When a database cluster switch instruction is received or a failure of the distributed database cluster is detected, and a database cluster switch is required, the MySQL database cluster replaces the distributed database cluster to provide services to the application. Therefore, it realizes the response to failure scenarios caused by system-level failures of the distributed database, and greatly improves the disaster recovery capability of the database.
[0035] In one exemplary instance, the step of synchronizing data from the distributed database cluster to the MySQL database cluster includes:
[0036] First, perform data synchronization between the master database in the MySQL database cluster and the master data node in the distributed database cluster.
[0037] Secondly, data synchronization is performed between the logical master database of each center in the MySQL database cluster and the master database of the main center in the MySQL database cluster.
[0038] For example, in a MySQL database cluster, the master database in the master center can be configured with a master-slave relationship using the command "change master to" (a synchronization configuration), which points to the master data node in the distributed database cluster to synchronize data.
[0039] For example, such as Figure 4 As shown, the specific process of synchronizing data between the MySQL database cluster and the distributed database cluster is as follows: The primary database of the MySQL database cluster, i.e., city 1, is synchronized with the primary data node of the distributed database cluster; the logical primary databases of each center in the MySQL database cluster, i.e., city 2 and city 3, are synchronized with the primary database of city 1; the backup databases of city 1 in the MySQL database cluster are synchronized with the primary database of city 1, and the backup databases of city 2 and city 3 are synchronized with the logical primary database of their respective cities, i.e., the backup database of city 2 is synchronized with the logical primary database of city 2, and the backup database of city 3 is synchronized with the logical primary database of city 3.
[0040] In one exemplary instance, the process of synchronizing data between the MySQL database cluster and the distributed database cluster also includes:
[0041] Monitor the data synchronization status information of the MySQL database cluster from the distributed database cluster;
[0042] When a problem is detected in data synchronization based on the obtained data synchronization information, an alarm is triggered using a preset method.
[0043] In one exemplary instance, the synchronization status includes at least one of the following: read status information for reading data from the distributed database cluster, write status information for writing the read data to the MySQL database cluster, and data synchronization delay information for the MySQL database cluster synchronizing data from the distributed database cluster.
[0044] For example, the read status information for reading data from the distributed database cluster includes: SQLthread; the write status information for writing the read data to the MySQL database cluster includes: IOthread; and the data synchronization latency information for the MySQL database cluster synchronizing data from the distributed database cluster includes: seconds_behind_master. Data synchronization problems are determined by monitoring whether the status of the SQL thread and IO thread is "yes" and whether the value of seconds_behind_master is 0.
[0045] For example, the preset alarm method can be to send an alarm to a designated maintenance person via SMS, telephone, or email, or it can be to send an alarm to the on-duty personnel via an alarm.
[0046] Before obtaining a database cluster switchover command or detecting a failure in the distributed database cluster in an exemplary instance, the method further includes:
[0047] First, monitor whether the address information of the master data node in the distributed database has changed;
[0048] Secondly, when a change in the address information of the master data node is detected, and it is determined based on the updated address information of the master data node that the master center where the updated master data node is located has switched to the updated master center, the identification information of the updated master center is obtained.
[0049] Next, based on the obtained identification information, the master center of the MySQL database cluster is switched to the updated master center, and the master database of the MySQL database cluster is re-determined in the updated master center;
[0050] Finally, the data of the newly determined master database in the MySQL database cluster is synchronized from the updated master data node in the distributed database cluster, and the data of the logical master database of each center in the MySQL database cluster is synchronized from the newly determined master database in the MySQL database.
[0051] For example, such as Figure 5 As shown, when a change in the address information of the master data node is detected, and it is determined based on the updated master data node's address information that the master center where the updated master data node is located has switched to the updated master center, i.e., city 2, then the master center of the MySQL database cluster is also switched to city 2. The master database of the MySQL database cluster is then re-determined in city 2, and data synchronization is performed between the master center of the MySQL database cluster (city 2) and the master data node of the master center of the distributed database cluster (city 2). Similarly, the logical master databases of each center in the MySQL database cluster (city 1 and city 3) are synchronized with the master database of city 2. The standby databases of city 2 in the MySQL database cluster are synchronized with the master database, and the standby databases of city 1 and city 3 are synchronized with the logical master databases of their respective cities. In other words, the standby database of city 2 is synchronized with the logical master database of city 2, and the standby database of city 3 is synchronized with the logical master database of city 3.
[0052] In one exemplary instance, the step of replacing the distributed database cluster with the MySQL database cluster to provide services to the application includes:
[0053] The domain name of the application connection of the distributed database cluster service is pointed to the virtual IP address (VIP) address corresponding to the main center in the MySQL database cluster, and write operations are started in the main center of the MySQL database cluster; wherein, one center in the MySQL database cluster corresponds to one VIP address, and the master database in the main center provides services to the application by binding its own IP address to the VIP address of the main center.
[0054] For example, if a distributed database cluster is detected to be malfunctioning and continues to malfunction, and fails to recover within a first preset time period, and if the business failure duration also exceeds a second preset time period, the application's Domain Name System (DNS) will be redirected to the MySQL cluster VIP, and write operations on the MySQL cluster will be enabled. Once the business can connect, SMS and email reminders will be sent to switch to the disaster recovery system.
[0055] For example, such as Figure 6 As shown, when a database cluster switching instruction is received or a fault in the distributed database cluster is detected, the domain name of the application connection of the distributed database cluster service is pointed to the main center in the MySQL database cluster, i.e., the VIP address corresponding to city 1, and write operations are started in the main center in the MySQL database cluster, i.e., city 1. The master database located in city 1 continues to provide services to the application.
[0056] In one exemplary instance, after initiating write operations in the master center of the MySQL database cluster, the method further includes:
[0057] When a failure of the primary database in the MySQL database cluster is detected, the binding relationship between the VIP address of the primary database cluster and the IP address of the primary database is unbound, and the standby database of the primary database is used as the updated primary database. The IP address of the updated primary database is then bound to the VIP address of the primary database.
[0058] For example, the status of the MySQL database cluster can be detected in real time. When the primary database fails, the standby database in the primary center will be automatically promoted to the primary database, realizing failover and link replication repair within the MySQL database cluster.
[0059] In one exemplary instance, after initiating write operations in the master center of the MySQL database cluster, the method further includes:
[0060] First, when it is detected that both the primary and standby databases in the MySQL cluster fail, a new primary center is selected as the updated primary center, and the VIP address of the updated primary center is obtained.
[0061] Secondly, the domain name connected to the application is pointed to the VIP address of the updated main center in the MySQL database cluster, and write operations are started on the updated main center.
[0062] For example, such as Figure 7 As shown, when both the primary and standby databases in the MySQL cluster are detected to be faulty, a new primary center is selected, assuming city 2 is chosen as the updated primary center. The VIP address of the updated primary center is obtained, the domain name connected by the application is pointed to the updated primary center in the MySQL database cluster, i.e., the VIP address of city 2, and write operations are started on the updated primary center, i.e., city 2.
[0063] When the master node in a MySQL database cluster changes, the backup relationship of the MySQL database cluster will also change. That is, the master database in city 2 will synchronize data to the standby database, the master database in city 2 will synchronize data to the logical master databases in cities 1 and 3, the logical master database in city 2 will synchronize data to the standby database in city 2, and the logical master database in city 3 will synchronize data to the standby database in city 3.
[0064] In one exemplary instance, after initiating the write operation to the main center for the update, the process further includes:
[0065] The application is returned main center switching information so that the application switches the main center for obtaining services to the updated main center according to the main center switching information; wherein, the main center switching information includes the identification information of the main center to which it is switched.
[0066] For example, such as Figure 7 As shown, the application switches its service acquisition center to the updated main center (City 2) based on the main center switching information. With the application's service acquisition center aligned with the main center, the application's service request response time is significantly reduced, enabling faster service delivery.
[0067] This application embodiment also provides a database disaster recovery device, including: a memory and a processor, wherein the memory is used to store executable programs;
[0068] The processor is used to read and execute the executable program to implement the database disaster recovery method described in any of the above embodiments.
[0069] This application also provides a disaster recovery management system, a tool developed using Python and the Django framework, which can monitor and manage the disaster recovery system, and performs the following functions:
[0070] The disaster recovery management system can monitor the synchronization status of distributed databases and MySQL clusters in real time, and monitor the values of MySQL SQL thread, IO thread, and seconds_behind_master. When delays or interruptions occur, it will send SMS alerts to ensure that inconsistencies in data synchronization can be detected in a timely manner.
[0071] The disaster recovery management system obtains real-time information about the master node of the distributed database. If a failure is detected in the distributed database master node, it obtains the latest switchover location and reassigns the MySQL cluster to the new node, ensuring correct synchronization. For example, after the distributed database master node switches to City 2, the disaster recovery management system is responsible for synchronizing and switching the MySQL cluster to the new distributed database node. The disaster recovery management system also monitors the status of the MySQL cluster. When a master database failure occurs, it automatically promotes the standby database to master, achieving failover and replication repair within the MySQL cluster.
[0072] When the disaster recovery management system detects a persistent failure in the distributed database cluster, and the distributed database fails to recover within 5 minutes (which can be defined), and application system monitoring confirms that business failures have persisted for more than 5 minutes (which can be defined), the disaster recovery management system will redirect the DNS of the application's connection domain to the MySQL cluster VIP and enable write operations on the MySQL cluster. Once the business can connect, it will send SMS and email notifications to switch to the disaster recovery system. Alternatively, users can directly click the switch button on the interface, which will initiate the switch immediately without waiting for the business to continue failing.
[0073] It will be understood by those skilled in the art that all or some of the steps, systems, or apparatuses disclosed above, and their functional modules / units, can be implemented as software, firmware, hardware, or suitable combinations thereof. In hardware implementations, the division between functional modules / units mentioned above does not necessarily correspond to the division of physical components; for example, a physical component may have multiple functions, or a function or step may be performed collaboratively by several physical components. Some or all components may be implemented as software executed by a processor, such as a digital signal processor or microprocessor, or as hardware, or as an integrated circuit, such as an application-specific integrated circuit (ASIC). Such software may be distributed on a computer-readable medium, which may include computer storage media (or non-transitory media) and communication media (or transient media). As is known to those skilled in the art, the term computer storage media includes volatile and non-volatile, removable and non-removable media implemented in any method or technology for storing information (such as computer-readable instructions, data structures, program modules, or other data). Computer storage media include, but are not limited to, RAM, ROM, EEPROM, flash memory or other memory technologies, CD-ROM, digital versatile disc (DVD) or other optical disc storage, magnetic cartridges, magnetic tape, disk storage or other magnetic storage devices, or any other medium that can be used to store desired information and can be accessed by a computer. Furthermore, it is well known to those skilled in the art that communication media typically contain computer-readable instructions, data structures, program modules, or other data in modulated data signals such as carrier waves or other transmission mechanisms, and may include any information delivery medium.
Claims
1. A database disaster recovery method, characterized in that, include: The structure and authorization information of the distributed database cluster are obtained and written into the MySQL database cluster; wherein the storage layer of the distributed database cluster is built using the MySQL architecture; the geographical location and primary / backup role of each center in the MySQL database cluster correspond one-to-one with each center of the distributed database. The MySQL database cluster synchronizes data from the distributed database cluster based on the structure information and authorization information of the distributed database cluster written into the MySQL database cluster. When a database cluster switch command is received or a failure of the distributed database cluster is detected, the MySQL database cluster will replace the distributed database cluster to provide services to the application.
2. The method according to claim 1, characterized in that, The step of writing the obtained structure information and authorization information of the distributed database cluster into the MySQL database cluster includes: Synchronize data between the master database in the main center of the MySQL database cluster and the master data node in the main center of the distributed database cluster; Synchronize data between the logical master database of each center in the MySQL database cluster and the master database of the main center in the MySQL database cluster.
3. The method according to claim 1, characterized in that, The process of synchronizing data from the distributed database cluster to the MySQL database cluster also includes: Monitor the data synchronization status information of the MySQL database cluster from the distributed database cluster; When a problem is detected in data synchronization based on the obtained data synchronization information, an alarm is triggered using a preset method.
4. The method according to claim 3, characterized in that, The synchronization status includes at least one of the following: read status information for reading data from the distributed database cluster, write status information for writing the read data to the MySQL database cluster, and data synchronization delay information for the MySQL database cluster synchronizing data from the distributed database cluster.
5. The method according to claim 1, characterized in that, Before obtaining the database cluster switchover command or detecting a fault in the distributed database cluster, the method further includes: Monitor whether the address information of the master data node in the distributed database has changed; When a change in the address information of the master data node is detected, and it is determined based on the updated address information of the master data node that the master center where the updated master data node is located has been switched to the updated master center, the identification information of the updated master center is obtained. Based on the obtained identification information, the master center of the MySQL database cluster is switched to the updated master center, and the master database of the MySQL database cluster is re-determined in the updated master center; The process involves synchronizing the data of the newly determined master database in the MySQL database cluster from the updated master data node in the distributed database cluster, and synchronizing the data of the logical master database of each center in the MySQL database cluster from the newly determined master database in the MySQL database cluster.
6. The method according to claim 1, characterized in that, The provision of services to the application by replacing the distributed database cluster with the MySQL database cluster includes: The domain name of the application connection of the distributed database cluster service is pointed to the VIP address corresponding to the main center in the MySQL database cluster, and write operations are started in the main center of the MySQL database cluster; wherein, one center in the MySQL database cluster corresponds to one VIP address, and the master database in the main center provides services to the application by binding its own IP address to the VIP address of the main center.
7. The method according to claim 6, characterized in that, After enabling write operations in the main data center of the MySQL database cluster, the following steps are also included: When a failure of the primary database in the MySQL database cluster is detected, the binding relationship between the VIP address of the primary database cluster and the IP address of the primary database is unbound, and the standby database of the primary database is used as the updated primary database. The IP address of the updated primary database is then bound to the VIP address of the primary database.
8. The method according to claim 6, characterized in that, After enabling write operations in the main data center of the MySQL database cluster, the following steps are also included: When both the primary and standby databases in the MySQL cluster are detected to be faulty, a new primary center is selected as the updated primary center, and the VIP address of the updated primary center is obtained. The domain name connected to the application is pointed to the VIP address of the updated main center in the MySQL database cluster, and write operations are started on the updated main center.
9. The method according to claim 8, characterized in that, After initiating the write operation of the updated main center, the process further includes: The application is returned main center switching information so that the application switches the main center for obtaining services to the updated main center according to the main center switching information; wherein, the main center switching information includes the identification information of the main center to which it is switched.
10. A database disaster recovery device, characterized in that, include: A memory and a processor, wherein the memory is used to store an executable program; The processor is used to read and execute the executable program to implement the database disaster recovery method as described in any one of claims 1-9.