A WAL batch management method, system, device and storage medium

Abstract

The application relates to a WAL batch management method, system and device and a storage medium. The method comprises a WAL removing method and a WAL restoring method. The WAL removing method comprises the following steps: querying a WWID, accessing a SAN storage corresponding to the WWID, checking a control file of a database in the SAN storage, obtaining a WAL of a current database checkpoint, and recording a WAL number; obtaining a WAL list stored in the SAN storage; performing backup and deleting the WAL in the SAN storage according to a specified number; the WAL restoring method comprises the following steps: querying the WWID, obtaining a WAL list stored in a temporary directory, and restoring the WAL from the temporary directory to the SAN storage according to a specified number. Compared with the prior art, the application adds the functions of protecting database recovery and protecting a copy link on the basis of a single WAL moving command, and the number of deleted WALs can be specified, so that the batch management function of the WAL is realized without affecting the database work.

Description

TECHNICAL FIELD

[0001] The present application relates to the technical field of database, in particular to a WAL batch management method, system and device and storage medium. BACKGROUND

[0002] PolarDB is a new generation of relational cloud-native database independently developed by Ali Cloud, which has the low-cost advantage of distributed design and the ease of use of centralized design. PolarDB adopts storage computing separation and soft and hard integrated design to meet the needs of large-scale application scenarios.

[0003] WAL is the abbreviation of Write Ahead Log, which is a common means in database systems to ensure the atomicity and durability of data operations.

[0004] The architecture of PolarDB is a multi-instance, shared storage architecture. Multiple instances and other components such as shared storage form a database cluster. Each database cluster uses a shared storage. Data files and WALs are stored together in shared storage. The size of the shared storage used by each database cluster has an upper limit, which can be expanded, but the expansion requires a process and time.

[0005] When a large number of DML (Data Manipulation Language) business occurs suddenly, a large number of WALs will occupy the shared storage space. When the shared storage space is full, the database is expected to be in a state of not writable and only readable, but it may also cause the database to enter a state of not starting, affecting the use of the database. At present, PolarDB lacks a method and function to handle a large number of WALs, so there is an urgent need for an implementation method that can manage WALs in batches for PolarDB. SUMMARY

[0006] The purpose of the present application is to overcome the defects of the prior art and provide a WAL batch management method, system, device and storage medium.

[0007] The purpose of the present application can be achieved by the following technical solutions:

[0008] A WAL batch management method includes a method for removing WAL and a method for restoring WAL, wherein the method for removing WAL includes the following steps:

[0009] S01, query WWID;

[0010] S02, according to the WWID of S01, access the SAN storage corresponding to the WWID, view the control file of the database in the SAN storage, obtain the WAL of the current database checkpoint, and record the WAL number.

[0011] S03, obtaining a list of WALs stored in the SAN storage according to the WWID of S01;

[0012] S04, performing backup and deleting the WALs in the SAN storage according to the specified number;

[0013] The method for recovering the WALs comprises the following steps:

[0014] S11, querying the WWID;

[0015] S12, obtaining a list of WALs stored in a staging directory according to the WWID of S11.

[0016] S13, recovering the WALs from the staging directory to the SAN storage according to the specified number.

[0017] Further, the step S04 comprises the following sub-steps:

[0018] creating a staging directory;

[0019] obtaining the specified number of WALs from the list of WALs obtained in S03 and forming a deletion list, wherein the WAL numbers in the deletion list are less than the WAL number of the database checkpoint obtained in S02;

[0020] copying the WALs in the deletion list to the staging directory;

[0021] deleting the WALs in the deletion list from the SAN storage.

[0022] Further, the step S13 comprises the following sub-steps:

[0023] obtaining a list of WAL files from the staging directory, wherein the WALs in the file list do not have suffixes;

[0024] according to the specified number, sequentially copying each WAL in the staging directory to the SAN storage, and adding a file suffix to the corresponding WAL file in the staging directory.

[0025] Further, for the case of needing to protect the WALs in the replication link, the method for removing the WALs comprises the following steps:

[0026] S21, querying the WWID;

[0027] S22, according to the WWID of S21, accessing the SAN storage corresponding to the WWID, checking the control file of the database in the SAN storage, obtaining the WAL of the current database checkpoint, and recording the WAL number thereof;

[0028] S23, obtaining the file in the SAN storage, and obtaining the database port;

[0029] S24, obtaining the database port according to S23, obtaining the WAL name currently used by the replication link, obtaining the WAL used by the replication link, and obtaining the minimum WAL in the replication link;

[0030] S25, obtaining the WAL list stored in the SAN storage according to the WWID of S21;

[0031] S26, backing up and deleting the WAL in the SAN storage according to the specified number.

[0032] Further, step S26 includes the following sub-steps:

[0033] Creating a staging directory;

[0034] Obtaining a specified number of WALs from the WAL list obtained in S25 and forming a deletion list, the WAL number in the deletion list being less than the WAL number of the database checkpoint obtained in step S22 and the minimum WAL number in the replication link obtained in step S24;

[0035] Copying the WAL in the deletion list to the staging directory;

[0036] Deleting the WAL in the deletion list from the SAN storage.

[0037] A WAL batch management system for implementing the WAL batch management method as described above, comprising a removal module and a recovery module;

[0038] The removal module is used for backing up and deleting the WAL in the SAN storage;

[0039] The recovery module is used for recovering the WAL to the SAN storage.

[0040] Further, the removal module performs the following operations:

[0041] S01, querying the WWID;

[0042] S02, according to the WWID of S01, accessing the SAN storage corresponding to the WWID, checking the control file of the database in the SAN storage, obtaining the WAL of the current database checkpoint, and recording the WAL number;

[0043] S03, according to the WWID of S01, obtaining the WAL list stored in the SAN storage;

[0044] S04, according to the specified number, backing up and deleting the WAL in the SAN storage;

[0045] The recovery module performs the following operations:

[0046] S11, query WWID;

[0047] S12, according to the WWID of S01, acquire the list of WAL stored in the staging directory.

[0048] S13, according to the specified number, restore the WAL from the staging directory to the SAN storage.

[0049] A WAL batch management device, comprising a memory and a processor, the memory stores a computer program, and the processor invokes the program instruction to execute a WAL batch management method as described above.

[0050] A computer readable storage medium comprising a computer program executable by a processor to implement a WAL batch management method as described above.

[0051] Compared with the prior art, the present application has the following beneficial effects:

[0052] 1. The present application implements batch processing of WAL based on PolarDB database, and can delete or restore WAL from SAN storage according to the specified number, so that batch processing can shorten the fault recovery time;

[0053] 2. The present application can identify and protect the WAL required for database recovery, and protect the WAL required for replication link from being moved away, so as to realize WAL batch management without affecting the normal work of the database;

[0054] 3. The number of batch processing WAL in the present application can be specified, or all can be processed;

[0055] 4. When restoring WAL, the present application can specify the interval time to prevent occupying a large amount of IO. BRIEF DESCRIPTION OF DRAWINGS

[0056] Figure 1 Flowchart for removing WAL method (1);

[0057] Figure 2 Flowchart for removing WAL method (2);

[0058] Figure 3 Flowchart for restoring WAL method. DETAILED DESCRIPTION

[0059] The present application will be described in detail below in combination with the drawings and specific embodiments. The present embodiment is implemented on the basis of the technical scheme of the present application, and gives detailed implementation mode and specific operation process, but the protection scope of the present application is not limited to the following embodiments.

[0060] This invention provides a method for batch management of WAL (Write-Ahead Log) based on the PolarDB database. This method adds functions to protect database recovery and protect the replication link on the basis of a single WAL migration command, and can also specify the number of WALs to be deleted.

[0061] Example 1

[0062] This embodiment provides a batch management method for WAL (Write-Ahead Logs), including two methods for removing WALs and one method for restoring WALs. The two methods for removing WALs are as follows:

[0063] 1. Method to remove WAL (1): When the database is unavailable, i.e. the database instance cannot start, the database recovery state will be protected and the WAL required for database recovery at startup will not be removed.

[0064] 2. Method for removing WAL (2): When the database is available, that is, when the database is in a state of being readable but not writable, the replication link will be protected and the WAL required by the replication link will not be removed.

[0065] The method to restore WAL is to restore the removed WAL to the shared storage when there is sufficient shared storage space.

[0066] like Figure 1 As shown, the method (1) for removing WAL includes the following steps:

[0067] S01. Query WWID (World Wild Identification, storage disk identifier) ​​via PolarStack;

[0068] S02. Based on the WWID in S01, access the SAN storage (Storage Area Network) corresponding to the WWID, view the control file of the database in the SAN storage, obtain the WAL of the current database checkpoint, and record its WAL number.

[0069] S03. Based on the WWID of S01, obtain the WAL list stored in the SAN storage;

[0070] S04. Back up and delete WALs in the SAN storage according to the specified quantity;

[0071] Specifically, step S04 includes the following sub-steps:

[0072] Create a temporary directory;

[0073] A specified number of WALs are retrieved from the WAL list obtained in S03 and a deletion list is formed. The WAL numbers in the deletion list are less than the WAL numbers of the database checkpoints obtained in S02.

[0074] Copy the WAL entries from the deletion list to the temporary directory;

[0075] Remove WAL from the deletion list from the SAN storage.

[0076] like Figure 2 As shown, method (2) for removing WAL includes the following steps:

[0077] S21. Query WWID using PolarStack;

[0078] S22. Based on the WWID in S21, access the SAN storage corresponding to the WWID, view the database control file in the SAN storage, obtain the WAL of the current database checkpoint, and record its WAL number.

[0079] S23. Obtain files from the SAN storage and obtain the database port;

[0080] S24. Based on the database port obtained in S23, the view inside the database specifies the WAL name currently being used by the replication link. Provide a command to get the WAL name currently being used by the replication link, get the WAL used by the replication link, and get the minimum WAL in the replication link.

[0081] S25. Based on the WWID in S21, obtain the WAL list stored in the SAN storage;

[0082] S26. Back up and delete the WAL in the SAN storage according to the specified number.

[0083] Specifically, step S26 includes the following sub-steps:

[0084] Create a temporary directory;

[0085] A specified number of WALs are obtained from the WAL list obtained in S25 and a deletion list is formed. The WAL numbers in the deletion list are less than the WAL numbers of the database checkpoints obtained in step S22 and the smallest WAL number in the replication link obtained in step S24.

[0086] Copy the WAL entries from the deletion list to the temporary directory;

[0087] Remove WAL from the deletion list from the SAN storage.

[0088] like Figure 3 As shown, the method for restoring WAL includes the following steps:

[0089] S11. Query WWID using PolarStack;

[0090] S12. Based on the WWID from S11, obtain the list of WALs stored in the temporary directory.

[0091] S13. Restore WAL from the temporary directory to the SAN storage according to the specified quantity.

[0092] Specifically, step S13 includes the following sub-steps:

[0093] Retrieve a list of WAL files from the temporary directory. The WAL files in this list do not have the _bak extension.

[0094] Based on the specified quantity, copy each WAL in the temporary storage directory to the SAN storage in sequence, and add the file extension _bak to the corresponding WAL file in the temporary storage directory.

[0095] In particular, when restoring WALs, the operation interval between each WAL can be specified to prevent excessive I / O.

[0096] Example 2

[0097] This embodiment provides a WAL batch management system for implementing the WAL batch management method described above, including a removal module and a recovery module; the removal module is used to back up and delete WALs in SAN storage; the recovery module is used to restore WALs to SAN storage.

[0098] To remove a module, perform the following operations:

[0099] S01. Query WWID using PolarStack;

[0100] S02. Based on the WWID in S01, access the SAN storage corresponding to the WWID, view the database control file in the SAN storage, obtain the WAL of the current database checkpoint, and record its WAL number.

[0101] S03. Based on the WWID of S01, obtain the WAL list stored in the SAN storage;

[0102] S04. Back up and delete WALs in the SAN storage according to the specified quantity;

[0103] The recovery module performs the following operations:

[0104] S11. Query WWID using PolarStack;

[0105] S12. Based on the WWID of S01, obtain the list of WALs stored in the temporary directory.

[0106] S13. Restore WAL from the temporary storage directory to the SAN storage according to the specified quantity.

[0107] This embodiment also provides a WAL batch management device and a computer-readable storage medium, wherein the device includes a memory and a processor, the memory stores a computer program, and the processor can execute a WAL batch management method as described in the above embodiment by calling the program instructions; at the same time, the computer-readable storage medium includes a computer program that can be executed by the processor to implement a WAL batch management method as described in the above embodiment.

[0108] The preferred embodiments of the present invention have been described in detail above. It should be understood that those skilled in the art can make numerous modifications and variations based on the concept of the present invention without creative effort. Therefore, all technical solutions that can be obtained by those skilled in the art based on the concept of the present invention through logical analysis, reasoning, or limited experimentation on the basis of existing technology should be within the scope of protection defined by the claims.

Claims

1. A WAL batch management method characterized by, The method for removing the WAL and the method for recovering the WAL, wherein the method for removing the WAL comprises the following steps: S01, querying the WWID; S02, according to the WWID in S01, accessing the SAN storage corresponding to the WWID, checking the control file of the database in the SAN storage, obtaining the WAL of the current database checkpoint, and recording the WAL number thereof; S03, according to the WWID in S01, obtaining the list of the WAL stored in the SAN storage; S04, according to the specified number, backing up and deleting the WAL in the SAN storage; The method for recovering the WAL comprises the following steps: S11, querying the WWID; S12, according to the WWID in S11, obtaining the list of the WAL stored in the temporary directory; S13, according to the specified number, recovering the WAL from the temporary directory to the SAN storage; For the case of needing to protect the WAL in the replication link, when the database is readable but not writable, the method for removing the WAL comprises the following steps: S21, querying the WWID; S22, according to the WWID in S21, accessing the SAN storage corresponding to the WWID, checking the control file of the database in the SAN storage, obtaining the WAL of the current database checkpoint, and recording the WAL number thereof; S23, obtaining the file in the SAN storage, and obtaining the database port; S24, according to the database port obtained in S23, obtaining the WAL name currently used by the replication link, obtaining the WAL used by the replication link, and obtaining the minimum WAL in the replication link; S25, according to the WWID in S21, obtaining the list of the WAL stored in the SAN storage; S26, according to the specified number, backing up and deleting the WAL in the SAN storage; Step S26 comprises the following sub-steps: creating a temporary directory; obtaining the specified number of WAL from the list of WAL obtained in S25 and forming a deletion list, wherein the WAL number in the deletion list is smaller than the WAL number of the database checkpoint obtained in step S22 and the minimum WAL number in the replication link obtained in step S24; copying the WAL in the deletion list to the temporary directory; deleting the WAL in the deletion list from the SAN storage.

2. The WAL batch management method of claim 1, wherein, Step S04 comprises the following sub-steps: creating a temporary directory; obtaining the specified number of WAL from the list of WAL obtained in S03 and forming a deletion list, wherein the WAL number in the deletion list is smaller than the WAL number of the database checkpoint obtained in S02; copying the WAL in the deletion list to the temporary directory; deleting the WAL in the deletion list from the SAN storage.

3. The method of claim 1, wherein, Step S13 comprises the following sub-steps: obtaining the list of WAL files in the temporary directory, wherein the WAL in the file list does not have a suffix; according to the specified number, sequentially copying each WAL in the temporary directory to the SAN storage, and adding a file suffix to the corresponding WAL file in the temporary directory.

4. A WAL batch management system characterized by comprising: The WAL batch management method for realizing any one of claims 1-3 comprises a removing module and a recovering module; the removing module is used for backing up and deleting the WAL in the SAN storage; The recovery module is configured to recover the WAL to the SAN storage.

5. The WAL batch management system of claim 4, wherein, The removing module performs the following operations: S01, querying a WWID; S02, according to the WWID in S01, accessing the SAN storage corresponding to the WWID, checking the control file of the database in the SAN storage, obtaining the WAL of the current database checkpoint, and recording the WAL number; S03, according to the WWID in S01, obtaining the list of WALs stored in the SAN storage; S04, according to the specified number, backing up and deleting the WAL in the SAN storage; The recovery module performs the following operations: S11, querying a WWID; S12, according to the WWID in S01, obtaining the list of WALs stored in the temporary directory; S13, according to the specified number, recovering the WAL from the temporary directory to the SAN storage.

6. A WAL batch management apparatus characterized by comprising: A computer program product comprising a memory storing a computer program and a processor configured to execute the program instructions to perform the method of any one of claims 1 to 3.

7. A computer readable storage medium characterized in that, A computer program product comprising a computer program configured to be executed by a processor to perform the method of any one of claims 1 to 3.

Images