Database disaster recovery method, device, equipment, medium and product

Abstract

The invention relates to the technical field of database disaster recovery, and provides a database disaster recovery method and device, equipment, a medium and a product.The method comprises the steps that when a database cluster needs to recover data and a first disaster recovery slave database is in a replication delay state, updating of a first master database is stopped, setting the first disaster recovery slave database to be in a direct data playback state; obtaining a difference duration according to a first time node of the database cluster requiring data recovery and a second time node of the first main database stopping data updating; and when the delay duration of the delayed copying reaches the difference duration, stopping data playback of the first disaster recovery slave database, and setting the first disaster recovery slave database as the second master database. When the first master database breaks down or is operated by mistake, due to the fact that the first disaster recovery slave database and the first master database have delayed copying, the first disaster recovery slave database does not apply the data in time, data playback only needs to be accelerated, and the data can be quickly recovered to the moment needing recovery.

Description

Technical Field

[0001] The present invention relates to the technical field of database disaster recovery, and in particular to a database disaster recovery method, device, equipment, medium and product. Background Art

[0002] In order to handle a large number of concurrent accesses, large websites need to implement distributed load balancing on the website, which is far from enough. At the data business layer and data access layer, if the traditional data structure is still used, or if only one server is used to handle so many database connection operations, the database will inevitably crash, especially if the data is lost. The consequences are even more disastrous. In the existing master-slave MySQL (My Structured Query Language, relational database management system) architecture, when the master database executes a delete command by mistake, since the slave database and the master database data are synchronized in real time, the deleted transaction will be executed quickly on the slave database. When restoring, it needs to be restored based on backup sets and other methods, and the accidentally deleted data cannot be quickly restored. Summary of the invention

[0003] The purpose of the present invention is to provide a database disaster recovery method, device, equipment, medium and product, which are used to solve the problem in the master-slave MySQL architecture in the prior art that when the master database erroneously executes a delete command, since the data of the slave database and the master database are synchronized in real time, the deleted transaction will be executed on the slave database very quickly, and recovery needs to be based on a backup set or the like, and the problem of being unable to quickly recover the accidentally deleted data.

[0004] In order to solve the above technical problems, an embodiment of the present invention provides a database disaster recovery method, wherein the method includes:

[0005] When the database cluster needs to restore data and the first disaster recovery slave database is in a delayed replication state, stop updating the first master database, and set the first disaster recovery slave database to switch from the delayed replication state to a direct data playback state; wherein the data playback is an operation of copying and reading the data of the first master database; the database cluster includes the first disaster recovery slave database and the first master database;

[0006] Obtain a first time node and a second time node; wherein the first time node is a time node when the database cluster needs to restore data; and the second time node is the current time when the first master database stops updating data;

[0007] Obtaining a difference duration according to the first time node and the second time node;

[0008] When the delay duration of delayed replication between the first disaster recovery slave database and the first primary database reaches the difference duration, the data playback of the first disaster recovery slave database is stopped, and the first disaster recovery slave database is set as the second primary database.

[0009] Optionally, the method further comprises:

[0010] Acquire a first delay table of the first master database; wherein the first delay table includes a first timestamp, and the first timestamp is used to record the current time when the first master database is in a normal state of data writing when the first delay table is periodically updated;

[0011] Obtaining a second delay table of the first disaster recovery slave database; wherein, when the first disaster recovery slave database synchronizes data with the first master database, the second delay table is synchronously updated according to the first delay table; the second delay table includes a second timestamp corresponding to the first timestamp;

[0012] The delay length is determined according to the connection status of the first primary database, the first timestamp, and the second timestamp.

[0013] Optionally, the method, wherein when the running state of the first primary database is a normal connection, determining the delay length according to the connection state of the first primary database, the first timestamp, and the second timestamp, includes:

[0014] The delay length is determined according to the first timestamp and the second timestamp.

[0015] Optionally, the method, wherein when the running state of the first primary database is abnormal connection, determining the delay length according to the connection state of the first primary database, the first timestamp and the second timestamp, includes:

[0016] Obtain a third timestamp in a first persistent log file; wherein the first persistent log is copied from a second persistent log that is synchronously updated when the first master database updates the first timestamp of the first delay table; the second persistent log includes a fourth timestamp corresponding to the first timestamp, and the third timestamp corresponds to the fourth timestamp;

[0017] The delay length is determined according to the third timestamp and the second timestamp.

[0018] Optionally, the method further comprises:

[0019] When the first master database can operate normally, the first master database is set as a second disaster recovery slave database.

[0020] Optionally, in the method, after setting the first primary database as the second disaster recovery slave database, the method further comprises:

[0021] Change the SQL_Delay value of the delayed replication time of the second disaster recovery slave database.

[0022] In order to achieve the above object, an embodiment of the present invention further provides a database disaster recovery device, wherein the device comprises:

[0023] A first processing module is used for stopping the update of the first master database and setting the first disaster recovery slave database to switch from the delayed replication state to the direct data playback state when the database cluster needs to restore data and the first disaster recovery slave database is in a delayed replication state; wherein the data playback is an operation of copying and reading the data of the first master database; the database cluster includes the first disaster recovery slave database and the first master database;

[0024] A first acquisition module is used to acquire a first time node and a second time node; wherein the first time node is a time node when the database cluster needs to restore data; and the second time node is a current time when the first master database stops updating data;

[0025] A second acquisition module, used to acquire a difference duration according to the first time node and the second time node;

[0026] The second processing module is used to stop the data playback of the first disaster recovery slave database and set the first disaster recovery slave database as the second master database when the delay duration of delayed replication between the first disaster recovery slave database and the first master database reaches the difference duration.

[0027] In order to achieve the above-mentioned purpose, an embodiment of the present invention also provides a network device, including: a processor, a memory, and a program or instruction stored in the memory and executable on the processor; wherein, when the processor executes the program or instruction, the database disaster recovery method as described above is implemented.

[0028] In order to achieve the above object, an embodiment of the present invention further provides a readable storage medium on which a program or instruction is stored, wherein the program or instruction, when executed by a processor, implements the steps in the database disaster recovery method as described above.

[0029] In order to achieve the above object, an embodiment of the present invention further provides a computer program product, which includes computer instructions, and when the computer instructions are executed by a processor, the steps of the database disaster recovery method described above are implemented.

[0030] The beneficial effects of the above technical solution of the present invention are as follows:

[0031] In the above scheme, due to the delayed replication between the first disaster recovery slave database and the first master database, there is a large time difference between the data of the first disaster recovery slave database read operation and the data of the first master database write operation. When data recovery is required, the first master database update is stopped, and the first disaster recovery slave database is switched to a direct data playback state, thereby speeding up data playback. When the data node that needs to be recovered is reached, data playback is stopped, and the first disaster recovery slave database is set as the master database. This can avoid the situation where the master database mistakenly executes a delete command. Since the data of the slave database and the master database are synchronized in real time, the deleted transaction will be quickly executed on the slave database. Recovery needs to be based on a backup set or the like, and the problem of being unable to quickly recover accidentally deleted data can be avoided. BRIEF DESCRIPTION OF THE DRAWINGS

[0032] Figure 1 A schematic diagram of a database disaster recovery method according to an embodiment of the present invention;

[0033] Figure 2 A schematic diagram of a database disaster recovery device according to an embodiment of the present invention. DETAILED DESCRIPTION

[0034] The following will be combined with the drawings in the embodiments of the present invention to clearly and completely describe the technical solutions in the embodiments of the present invention. Obviously, the described embodiments are only part of the embodiments of the present invention, not all of the embodiments. Based on the embodiments of the present invention, all other embodiments obtained by ordinary technicians in this field without creative work are within the scope of protection of the present invention.

[0035] The present invention aims at the problem that when a master database in a master-slave MySQL architecture in the prior art mistakenly executes a delete command, since the data of the slave database and the master database are synchronized in real time, the deleted transaction will be quickly executed on the slave database, and recovery needs to be based on a backup set or the like, and the mistakenly deleted data cannot be quickly recovered. The present invention provides a database disaster recovery method, device, equipment, medium and product.

[0036] like Figure 1 As shown, an embodiment of the present invention provides a database disaster recovery method, wherein the method includes:

[0037] S10, when the database cluster needs to recover data and the first disaster recovery slave database is in a delayed replication state, stop updating the first master database, and set the first disaster recovery slave database to switch from the delayed replication state to a direct data playback state; wherein the data playback is an operation of copying and reading the data of the first master database; the database cluster includes the first disaster recovery slave database and the first master database;

[0038] It should be noted that, in order to avoid real-time synchronization of data between the master and slave databases, data accidentally deleted on the master database will be quickly executed on the slave database, and recovery needs to be based on backup sets and other methods, which makes it impossible to quickly recover the accidentally deleted data. In an embodiment of the present invention, delayed replication is set in the first disaster recovery slave database so that the data copied by the first disaster recovery slave database lags behind the data updated by the first master database by a preset delayed replication time. Therefore, when the database cluster needs to restore data, the update of the first master database is stopped, and the first disaster recovery slave database is switched to the state of directly replaying the data, so that the data can be quickly replayed to the time node where data recovery is required to perform master-slave switching, so as to achieve the purpose of quickly recovering accidentally deleted data.

[0039] S20, obtaining a first time node and a second time node; wherein the first time node is a time node when the database cluster needs to restore data; and the second time node is the current time when the first master database stops updating data;

[0040] It should be noted that the first time node is T rec , the second time node is T master_stop .

[0041] S30, obtaining a difference duration according to the first time node and the second time node;

[0042] It should be noted that the difference duration is T diff_rec , where the formula for the difference duration is as follows:

[0043] T diff_rec =T master_stop -T rec .

[0044] S40, when the delay duration of delayed replication between the first disaster recovery slave database and the first primary database reaches the difference duration, stop the data playback of the first disaster recovery slave database, and set the first disaster recovery slave database as the second primary database;

[0045] It should be noted that the delay duration is continuously calculated during the data playback of the first disaster recovery slave database. When the delay duration reaches the difference duration, the data playback is stopped, and the first disaster recovery slave database is set as the second master database, promoted to the master database, and provides services externally. The delay duration is T delay .

[0046] In this embodiment, since there is delayed replication between the first disaster recovery slave database and the first master database, there is a large time difference between the data of the first disaster recovery slave database read operation and the data of the first master database write operation. When data recovery is required, the update of the first master database is stopped, and the first disaster recovery slave database is switched to a direct data playback state, thereby speeding up the data playback. When the data node that needs to be recovered is reached, the data playback is stopped, and the first disaster recovery slave database is set as the master database. This can avoid the problem that when the master database erroneously executes a delete command, since the data of the slave database and the master database are synchronized in real time, the deleted transaction will be quickly executed on the slave database, and recovery needs to be based on a backup set or the like, and the erroneously deleted data cannot be quickly recovered.

[0047] Optionally, the method further comprises:

[0048] Acquire a first delay table of the first master database; wherein the first delay table includes a first timestamp, and the first timestamp is used to record the current time when the first master database is in a normal state of data writing when the first delay table is periodically updated;

[0049] It should be noted that the first delay table is updated periodically using the super management user to record the first timestamp, and the first timestamp is T master .

[0050] Obtaining a second delay table of the first disaster recovery slave database; wherein, when the first disaster recovery slave database synchronizes data with the first master database, the second delay table is synchronously updated according to the first delay table; the second delay table includes a second timestamp corresponding to the first timestamp;

[0051] It should be noted that when the first disaster recovery slave database copies and reads the data of the first master database, the second delay table is synchronously updated, and the first timestamp is copied to the second timestamp, wherein the second timestamp is T slave .

[0052] Determining the delay length according to the connection state of the first primary database, the first timestamp, and the second timestamp;

[0053] It should be noted that, according to the connection status of the first primary database, the first timestamp T master and the second timestamp T slave , determine the delay time T delay .

[0054] Optionally, the method, wherein when the running state of the first primary database is a normal connection, determining the delay length according to the connection state of the first primary database, the first timestamp, and the second timestamp, includes:

[0055] The delay length is determined according to the first timestamp and the second timestamp.

[0056] In this embodiment, after the first master database stops updating, the first timestamp T master No longer updated, the second timestamp T slave Since the first disaster recovery slave database is always in the data playback state and is constantly updating, the delay time is gradually decreasing. When the operation state of the first master database is normal connection, the delay time T delay The calculation formula is as follows:

[0057] T delay =|T master -T slave |.

[0058] Optionally, the method, wherein when the running state of the first primary database is abnormal connection, determining the delay length according to the connection state of the first primary database, the first timestamp and the second timestamp, includes:

[0059] Obtain a third timestamp in a first persistent log file; wherein the first persistent log is copied from a second persistent log that is synchronously updated when the first master database updates the first timestamp of the first delay table; the second persistent log includes a fourth timestamp corresponding to the first timestamp, and the third timestamp corresponds to the fourth timestamp;

[0060] The delay length is determined according to the third timestamp and the second timestamp.

[0061] In this embodiment, the first primary database updates the first delay table while updating the second persistent log. When the operation state of the first primary database is abnormal connection, the first timestamp T cannot be obtained. master Therefore, the third timestamp is obtained from the first persistent log file copied from the second persistent log to calculate the delay duration. The third timestamp is T log_ts, the delay time T delay The calculation formula is as follows:

[0062] T delay =|T log_ts -T slave |.

[0063] Optionally, the method further comprises:

[0064] When the first master database can operate normally, the first master database is set as a second disaster recovery slave database.

[0065] In this embodiment, when data is accidentally deleted from the first master database, there may be a problem of database corruption, and the first master database needs to be repaired. When the first master database can operate normally, the first master database is set as the second disaster recovery slave database, and the master database is converted to the disaster recovery slave database role for continued use.

[0066] Optionally, in the method, after setting the first primary database as the second disaster recovery slave database, the method further comprises:

[0067] Change the SQL_Delay value of the delayed replication time of the second disaster recovery slave database.

[0068] In this embodiment, when the first master database is set as the second disaster recovery slave database and the master database is converted to the disaster recovery slave database role for continued use, IO (Input / Output) synchronization and SQL (Structured Query Language) playback are enabled, and delayed replication is set, that is, the SQL_Delay value of the second disaster recovery slave database is changed, and the second master database data is continuously delayed to implement the disaster recovery function.

[0069] It should be noted that the embodiment of the present invention is directed to the database cluster, the database cluster includes the first master database and the first disaster recovery slave database, and the database cluster may also have other multiple slave databases, and delayed replication is set for the disaster recovery slave database to achieve rapid data recovery; the first delay table for recording delay is newly created on the first master database, the current timestamp is recorded as the first timestamp, and the recorded current timestamp is synchronized to the second persistent log file as the fourth timestamp, and the accurate delay duration is obtained for different connection states of the first master database; when data recovery is required, the first master database is set to stop updating, thereby stopping writing to the first delay table, and by comparing the delay duration of the first disaster recovery slave database, when the time point required for recovery is reached, the sql_thread (SQL thread) of the first disaster recovery slave database is stopped to stop the data playback, so as to achieve efficient data recovery and rapid fault backtracking. When the first master database in the current database cluster cannot provide services, the first disaster recovery slave database can also be promoted to provide services to the second master database.

[0070] When the first master database fails or erroneously operates, the first disaster recovery slave database does not apply the data in time due to delayed replication between the first disaster recovery slave database and the first master database. It only needs to speed up the data playback to quickly restore to the time when recovery is required. The conventional one-master-multiple-slave replication architecture cannot achieve rollback of accidentally deleted data through master-slave switching; it often needs to be restored to the time before the accidental deletion based on the backup set and the backup is restored to a new node. Compared with the backup recovery method, the recovery time can be greatly shortened; and after the disaster recovery slave database recovers the data, it can be immediately promoted to the main database to provide external services, and the user service will not be affected while repairing the original main database.

[0071] like Figure 2 As shown, in order to achieve the above-mentioned purpose, an embodiment of the present invention further provides a database disaster recovery device, wherein the device comprises:

[0072] The first processing module 201 is used to stop the update of the first master database and set the first disaster recovery slave database to switch from the delayed replication state to the direct data playback state when the database cluster needs to restore data and the first disaster recovery slave database is in a delayed replication state; wherein the data playback is an operation of copying and reading the data of the first master database; the database cluster includes the first disaster recovery slave database and the first master database;

[0073] The first acquisition module 202 is used to acquire a first time node and a second time node; wherein the first time node is a time node when the database cluster needs to restore data; and the second time node is a current time when the first master database stops updating data;

[0074] A second acquisition module 203 is used to acquire a difference duration according to the first time node and the second time node;

[0075] The second processing module 204 is used to stop the data playback of the first disaster recovery slave database and set the first disaster recovery slave database as the second master database when the delay duration of delayed replication between the first disaster recovery slave database and the first master database reaches the difference duration.

[0076] Optionally, the device comprises:

[0077] A third acquisition module is used to acquire a first delay table of the first master database; wherein the first delay table includes a first timestamp, and the first timestamp is used to record the current time when the first master database is in a normal state of data writing when the first delay table is periodically updated;

[0078] a fourth acquisition module, configured to acquire a second delay table of the first disaster recovery slave database; wherein, when the first disaster recovery slave database synchronizes data with the first master database, the second delay table is synchronously updated according to the first delay table; and the second delay table includes a second timestamp corresponding to the first timestamp;

[0079] The first determining module is used to determine the delay length according to the connection state of the first primary database, the first timestamp and the second timestamp.

[0080] Optionally, in the device, the first determining module includes:

[0081] The first determining unit is used to determine the delay length according to the first timestamp and the second timestamp.

[0082] Optionally, in the device, the first determining module includes:

[0083] a first acquisition unit, configured to acquire a third timestamp in a first persistent log file; wherein the first persistent log is obtained by copying a second persistent log that is synchronously updated when the first master database updates the first timestamp of the first delay table; the second persistent log includes a fourth timestamp corresponding to the first timestamp, and the third timestamp corresponds to the fourth timestamp;

[0084] The second determining unit is used to determine the delay duration according to the third timestamp and the second timestamp.

[0085] Optionally, the device further comprises:

[0086] The third processing module is used to set the first primary database as a second disaster recovery slave database when the first primary database can operate normally.

[0087] Optionally, the device further comprises:

[0088] The fourth processing module is used to change the delayed replication time SQL_Delay value of the second disaster recovery slave database.

[0089] It should be noted here that the above-mentioned device provided in the embodiment of the present invention can implement all the method steps implemented in the above-mentioned method embodiment, and can achieve the same technical effect. The parts and beneficial effects that are the same as the method embodiment in this embodiment will not be described in detail here.

[0090] In order to achieve the above-mentioned purpose, an embodiment of the present invention also provides a network device, including: a processor, a memory, and a program or instruction stored in the memory and executable on the processor; wherein, when the processor executes the program or instruction, the database disaster recovery method as described above is implemented.

[0091] In order to achieve the above object, an embodiment of the present invention further provides a readable storage medium on which a program or instruction is stored, wherein the program or instruction, when executed by a processor, implements the steps in the database disaster recovery method as described above.

[0092] In order to achieve the above object, an embodiment of the present invention further provides a computer program product, including computer instructions, which, when executed by a processor, implement the above Figure 1 The various processes of the method embodiment shown can achieve the same technical effect, and will not be described again here to avoid repetition.

[0093] Obviously, those skilled in the art can make various changes and modifications to the present invention without departing from the spirit and scope of the present invention. Thus, if these modifications and variations of the present invention fall within the scope of the claims of the present invention and their equivalents, the present invention is also intended to include these modifications and variations.

Claims

1. A database disaster recovery method, characterized in that: The method comprises: When the database cluster needs to restore data and the first disaster recovery slave database is in a delayed replication state, stop updating the first master database, and set the first disaster recovery slave database to switch from the delayed replication state to a direct data playback state; wherein the data playback is an operation of copying and reading the data of the first master database; the database cluster includes the first disaster recovery slave database and the first master database; Obtain a first time node and a second time node; wherein the first time node is a time node when the database cluster needs to restore data; and the second time node is the current time when the first master database stops updating data; Obtaining a difference duration according to the first time node and the second time node; When the delay duration of delayed replication between the first disaster recovery slave database and the first primary database reaches the difference duration, the data playback of the first disaster recovery slave database is stopped, and the first disaster recovery slave database is set as the second primary database.

2. The method according to claim 1, characterized in that The method further comprises: Acquire a first delay table of the first master database; wherein the first delay table includes a first timestamp, and the first timestamp is used to record the current time when the first master database is in a normal state of data writing when the first delay table is periodically updated; Obtaining a second delay table of the first disaster recovery slave database; wherein, when the first disaster recovery slave database synchronizes data with the first master database, the second delay table is synchronously updated according to the first delay table; the second delay table includes a second timestamp corresponding to the first timestamp; The delay length is determined according to the connection status of the first primary database, the first timestamp, and the second timestamp.

3. The method according to claim 2, characterized in that When the operation state of the first primary database is a normal connection, determining the delay length according to the connection state of the first primary database, the first timestamp, and the second timestamp includes: The delay length is determined according to the first timestamp and the second timestamp.

4. The method according to claim 2, characterized in that: When the operation state of the first primary database is abnormal connection, determining the delay length according to the connection state of the first primary database, the first timestamp, and the second timestamp includes: Obtain a third timestamp in a first persistent log file; wherein the first persistent log is copied from a second persistent log that is synchronously updated when the first master database updates the first timestamp of the first delay table; the second persistent log includes a fourth timestamp corresponding to the first timestamp, and the third timestamp corresponds to the fourth timestamp; The delay length is determined according to the third timestamp and the second timestamp.

5. The method according to claim 1, characterized in that The method further comprises: When the first master database can operate normally, the first master database is set as a second disaster recovery slave database.

6. The method according to claim 5, characterized in that After setting the first master database as the second disaster recovery slave database, the method further includes: Change the SQL_Delay value of the delayed replication time of the second disaster recovery slave database.

7. A database disaster recovery device, characterized in that: The device comprises: A first processing module is used for stopping the update of the first master database and setting the first disaster recovery slave database to switch from the delayed replication state to the direct data playback state when the database cluster needs to restore data and the first disaster recovery slave database is in a delayed replication state; wherein the data playback is an operation of copying and reading the data of the first master database; the database cluster includes the first disaster recovery slave database and the first master database; A first acquisition module is used to acquire a first time node and a second time node; wherein the first time node is a time node when the database cluster needs to restore data; and the second time node is a current time when the first master database stops updating data; A second acquisition module, used to acquire a difference duration according to the first time node and the second time node; The second processing module is used to stop the data playback of the first disaster recovery slave database and set the first disaster recovery slave database as the second master database when the delay duration of delayed replication between the first disaster recovery slave database and the first master database reaches the difference duration.

8. A network device comprising: A processor, a memory, and a program or instruction stored in the memory and executable on the processor; characterized in that when the processor executes the program or instruction, the database disaster recovery method according to any one of claims 1 to 6 is implemented.

9. A readable storage medium having a program or instruction stored thereon, characterized in that: When the program or instruction is executed by a processor, the steps in the database disaster recovery method according to any one of claims 1 to 6 are implemented.

10. A computer program product, characterized in that The method comprises computer instructions, which, when executed by a processor, implement the steps of the database disaster recovery method according to any one of claims 1 to 6.

Images