CN115794491A - Database file backup method, device, equipment and storage medium - Google Patents

Abstract

The invention provides a database file backup method, a device, equipment and a storage medium, wherein the method comprises the following steps: responding to the timed backup task instruction, and traversing and querying all files in the target database; carrying out consistency comparison on each table data file in the target database and the table data files in the target backup folder in a one-to-one correspondence manner, and marking the table data files with inconsistent comparison results in the target database as files to be backed up; the target backup folder is the latest backup folder in the backup library; the method comprises the steps of copying a target backup folder to obtain a current backup folder, naming the current backup folder based on current system time, copying all table data files marked as files to be backed up, replacing corresponding table data files in the current backup folder, and adding the current backup folder into a backup library. The invention simplifies the file comparison and file backup process, thereby improving the efficiency of database file backup.

Description

Database file backup method, device, equipment and storage medium

technical field

The invention relates to the technical field of data backup, in particular to a database file backup method, device, equipment and storage medium.

Background technique

The existing database file backup and recovery scheme usually needs to compare the obtained current database file with the image copy file to obtain the incremental number, then back up the incremental data and output the file, and then compare the backup output file with the original image copy file For comparison, the backup output files are merged into the image copy, and the image copy is subsequently updated to the database. In the above-mentioned existing solution, the content change comparison and storage method of the database file are relatively complicated, and the efficiency is not high.

Contents of the invention

The present invention aims to provide a database file backup method, device, equipment and storage medium to solve the above technical problems, thereby simplifying file comparison and file backup processes, thereby improving database file backup efficiency.

In order to solve the above technical problems, the invention provides a database file backup method, comprising:

In response to the scheduled backup task instruction, traverse all files in the query target database;

Each table data file in the target database is correspondingly compared with the table data files in the target backup folder one by one, and the table data files with inconsistent comparison results in the target database are marked as files to be backed up; wherein , the target backup folder is the latest backup folder in the backup repository;

Copying the target backup folder to obtain a current backup folder, naming the current backup folder based on the current system time, copying all table data files marked as files to be backed up and copying the current backup folder Replace the corresponding table data file in , and add the current backup folder to the backup library.

Further, the traversal query of all files in the target database includes:

When the table structure file of the target database is inquired, if it is judged that the table structure file is damaged, the backup table structure file in the target backup folder is copied, and the table structure in the target database is copied. file to replace.

Further, the traversal query of all files in the target database includes:

When it is queried and judged that the table data file in the target database is damaged, the current backup task is ended and the file recovery program is started.

Further, before traversing and querying all files in the target database in response to the scheduled backup task instruction, the method further includes:

When it is judged that there is no backup folder in the backup repository, a full backup is performed on the target database to obtain a first backup folder, and the first backup folder is added to the backup repository.

Further, it also includes:

When it is judged that the number of backup folders in the backup library is greater than a preset threshold, the oldest backup folder in the backup library is deleted.

Further, the manner of judging that the table structure file is damaged includes:

When judging whether the file size of the table structure file is zero, it is determined that the table structure file is damaged.

Further, the one-to-one consistency comparison is performed between each table data file in the target database and the table data file in the target backup folder, and the table data files in the target database with inconsistent comparison results are marked as Files to be backed up, including:

Calculate respectively the MD5 value of each table data file in the target database and the table data file in the target backup folder;

Based on the MD5 value of each file, each table data file in the target database is compared with the table data file in the target backup folder in one-to-one correspondence;

When judging that the MD5 value of the target table data file in the target database is inconsistent with the MD5 value of the corresponding table data file in the target backup folder, mark the target table data file as a file to be backed up.

The present invention also provides a database file backup device, comprising:

A task response module, configured to traverse all files in the query target database in response to a scheduled backup task instruction;

A file comparison module, configured to perform a one-to-one consistency comparison between each table data file in the target database and the table data file in the target backup folder, and mark the table data files with inconsistent comparison results in the target database is the file to be backed up; wherein, the target backup folder is the latest backup folder in the backup library;

A file backup module, configured to copy the target backup folder to obtain a current backup folder, name the current backup folder based on the current system time, copy all table data files marked as files to be backed up and The corresponding table data file in the current backup folder is replaced, and the current backup folder is added to the backup repository.

The present invention also provides a terminal device, including a processor and a memory storing a computer program. When the processor executes the computer program, the database file backup method described in any one is implemented.

The present invention also provides a non-transitory computer-readable storage medium, on which a computer program is stored, and when the computer program is executed by a processor, the database file backup method described in any one is realized.

Compared with the prior art, the present invention has the following beneficial effects:

The present invention provides a database file backup method, device, equipment and storage medium. The method includes: responding to the timing backup task instruction, traversing and querying all files in the target database; storing each table data file in the target database Carry out a one-to-one consistency comparison with the table data files in the target backup folder, and mark the inconsistent table data files in the target database as files to be backed up; wherein, the target backup folder is in the backup library The latest backup folder; the target backup folder is copied to obtain the current backup folder, the current backup folder is named based on the current system time, and all table data files marked as files to be backed up are copied and copied The corresponding table data file in the current backup folder is replaced, and the current backup folder is added to the backup repository. The invention simplifies the process of file comparison and file backup, thereby improving the efficiency of database file backup.

Description of drawings

Fig. 1 is one of schematic flow charts of the database file backup method provided by the present invention;

Fig. 2 is the second schematic flow chart of the database file backup method provided by the present invention;

Fig. 3 is a schematic diagram of the file restoration process of the database file backup method provided by the present invention;

Fig. 4 is a schematic structural diagram of the database file backup device provided by the present invention.

Detailed ways

The technical solutions in the embodiments of the present invention will be clearly and completely described below in conjunction with the accompanying drawings in the embodiments of the present invention. Obviously, the described embodiments are only some, not all, embodiments of the present invention. Based on the embodiments of the present invention, all other embodiments obtained by persons of ordinary skill in the art without creative efforts fall within the protection scope of the present invention.

Referring to Fig. 1, the embodiment of the present invention provides a kind of database file backup method, may comprise steps:

S1. In response to the scheduled backup task instruction, traverse and query all files in the target database;

S2. Perform a one-to-one consistency comparison between each table data file in the target database and the table data file in the target backup folder, and mark the table data files with inconsistent comparison results in the target database as files to be backed up ; Wherein, the target backup folder is the latest backup folder in the backup library;

S3. Copy the target backup folder to obtain the current backup folder, name the current backup folder based on the current system time, copy all table data files marked as files to be backed up, and copy the current backup folder The corresponding table data files in the folder are replaced, and the current backup folder is added to the backup library.

In the embodiment of the present invention, a scheduled backup task can be set, and the file backup program is executed every preset period. Before adding the backup file, compare each file in the database with the corresponding file in the latest backup folder one by one. The files that are consistent do not need to be backed up, and the files that are inconsistent are marked as files to be backed up. When traversing all files in the target database and determining at least one file to be backed up, create the current backup folder (copy the latest backup folder in the backup library), name the folder with the current time, and mark it as the file to be backed up Copy the table data file of the backup file and replace the corresponding file in the current backup folder. After replacing the backup file, add the current backup folder to the backup library to complete the backup.

The database file backup method provided by the embodiment of the present invention does not need to perform incremental judgment and backup on the entire backup folder, but instead judges each table data file separately and refines the backup, which simplifies the process of comparing changes in file content and effectively improves the database file backup method. Efficiency of file backup.

In the embodiment of the present invention, further, the traversal of all files in the query target database includes:

When the table structure file of the target database is inquired, if it is judged that the table structure file is damaged, the backup table structure file in the target backup folder is copied, and the table structure in the target database is copied. file to replace.

It should be noted that for the queried table structure file, when it is judged that the table structure file is damaged, it is necessary to obtain the corresponding backup table structure file according to the latest backup folder to copy and replace the table structure file of the database. .

In the embodiment of the present invention, further, the traversal of all files in the query target database includes:

When it is queried and judged that the table data file in the target database is damaged, the current backup task is ended and the file recovery program is started.

It should be noted that, when it is judged that the table data file of the database is damaged during the backup process, the current backup task is terminated and the file recovery program is started. It should be noted that starting the file recovery program can trigger a file damage prompt to the user, but not directly and automatically perform file recovery, and further judge whether recovery is necessary through human intervention, effectively preventing the loss of user data due to system misjudgment problem, improving the reliability and stability of system backup and recovery.

In the embodiment of the present invention, further, before traversing all the files in the query target database in response to the scheduled backup task instruction, it also includes:

When it is judged that there is no backup folder in the backup repository, a full backup is performed on the target database to obtain a first backup folder, and the first backup folder is added to the backup repository.

It should be noted that before judging the table data file backup requirements for the data file, it is also necessary to judge whether there is a backup folder in the backup database. If not, it is necessary to create a new one and perform a full backup of the data files of the database.

In the embodiment of the present invention, it further includes:

When it is judged that the number of backup folders in the backup library is greater than a preset threshold, the oldest backup folder in the backup library is deleted.

It should be noted that since backups that are too old are of no practical use, a certain automatic deletion strategy can be set to delete the backup files of the old version in time, thereby saving storage resources such as computer disk space.

In the embodiment of the present invention, further, the manner of judging that the table structure file is damaged includes:

When judging whether the file size of the table structure file is zero, it is determined that the table structure file is damaged.

It should be noted that whether the table structure file is damaged can be determined by judging whether the file size of the table structure file is zero.

In the embodiment of the present invention, further, the one-to-one correspondence between each table data file in the target database and the table data file in the target backup folder is compared, and the comparison result in the target database Inconsistent table data files are marked as files to be backed up, including:

Calculate respectively the MD5 value of each table data file in the target database and the table data file in the target backup folder;

Based on the MD5 value of each file, each table data file in the target database is compared with the table data file in the target backup folder in one-to-one correspondence;

When judging that the MD5 value of the target table data file in the target database is inconsistent with the MD5 value of the corresponding table data file in the target backup folder, mark the target table data file as a file to be backed up.

It should be noted that, in the embodiment of the present invention, the consistency judgment of the table data file can be performed by means of MD5 verification, which is logically simple, accurate and efficient, thereby improving the efficiency and accuracy of the file consistency judgment.

Based on the above solution, in order to better understand the database file backup method provided by the embodiment of the present invention, the following detailed description:

It should be noted that the method of the embodiment of the present invention can ensure the safe production and operation of the system 7×24 hours, mainly through the database backup and recovery technology to ensure no data loss and stable operation in the event of a failure in the operation of the database system.

File MD5 is a file signature. If the file content has not changed, the calculated file MD5 is consistent. Therefore, it can be determined whether the data file has changed through MD5 verification.

In the embodiment of the present invention, mainly by the md5 of the file, judge whether the database structure file and the data file in the MySQL/data directory are consistent with the MD5 value of the database structure file and the data file in the backup folder to judge whether the file is Backup is required, the backup is a physical copy, and the cp command is used for backup.

The embodiment of the present invention judges whether a file needs to be backed up in a way different from the traditional backup technology. Through file MD5, the process of comparing file content changes is simplified. Compared with obtaining file increments or regular full backups, it saves computer memory resources, disk space, and CPU overhead, and reduces system invalid files.

At the same time, during the scheduled task backup process, the system will also judge whether it is damaged according to whether the database structure file in the MySQL/data directory is 0kb. If it is damaged, it will copy the corresponding file in the previous backup folder to replace the damaged file. , repair the problems caused by the damage of the database table structure file, and improve the stability and robustness of the system.

It should be noted that, in the embodiment of the present invention, the table structure file has a suffix of .frm to store the structure of the table; the table data file has a suffix of .ibd to store data relationships; Age, the content after the second line is the content, while the table structure file is equivalent to the header of excel, and the table data file is equivalent to the content after the second line of excel.

If it is judged that the data file in the MySQL/data directory corresponding to the database is damaged, the file recovery program will be triggered to remind the user whether file recovery is required. The user can log in to the operation and maintenance platform system and perform data file recovery through one-click repair.

For the storage of the backup folder, you can set the total backup folder to save up to 7 copies (it can be irrelevant to the time span). Backups that are too old are of no practical use. Deleting these old backup files can save computer disk space.

Referring to Fig. 2, the embodiment of the present invention can be realized through the following steps:

1. Start up and run the backup program, first determine whether there is a backup folder in the backup folder storage path (backup library), if not, you need to back up a full copy (stored to mysql/data) for subsequent scheduled tasks and mysql Files in the /data directory are judged by file MD5;

2. Perform scheduled backup tasks periodically (or perform backup tasks according to preset trigger conditions) to back up the latest modified files;

3. Traverse and query the files under the MySQL/data directory (target database), compare the MD5 value of the files with the corresponding files in the latest backup folder, and determine whether the files in the database have been modified;

4. When the table structure file (end of .frm) is found in the query, it is judged whether the table structure file is damaged (for example, the file size is determined to be damaged if it is 0kb), and if it is damaged, restore the table structure file through the latest backup file;

5. Determine whether each table data file in the target database is damaged. For example, you can query a piece of data through MySQL (select*from<table name>where 1limit 1) and determine whether the table data file is damaged. If the table data file is damaged, end the backup program, trigger the recovery task prompt, wait for the user to log in to the system, and choose whether to restore with one key (see Figure 3).

6. After traversing the MySQL/data directory files and determining which data files have changed in MD5, if there is at least one table data file that needs to be backed up, copy the latest backup folder, name it with the current time (20220907121212), and replace the file The data files with changed MD5 values are stored in the backup library to complete the backup.

7. When it is judged that the number of backup folders is greater than 7, delete the oldest backup, and wait for the next scheduled task to be executed after the execution is completed.

Please refer to Figure 3, the process of file recovery can be realized through the following steps:

1. Through the file damage prompt triggered by the above process, the user finds that the data file of the database table is damaged and logs in to the operation and maintenance platform;

2. View the list of database backup folders;

3. Select the backup folder version to be restored;

4. The system replaces the damaged table data files in the database with the table data files in the backup folder;

5. After the replacement is completed, start the backup program, and the repair is completed.

It can be understood that the reason why the embodiment of the present invention gives the recovery initiative to the user is to prevent the system from misjudging and causing loss of user data.

Compared with the prior art, the embodiments of the present invention have the following beneficial effects:

1. The present invention finds another way to compare the MD5 values of the files under MySQL/data with the corresponding files under the backup folder to judge whether the file content has been modified, and optimize the file content change comparison process. Compared with obtaining file increments or regular full backups, it saves computer memory resources, disk space, and CPU overhead, and reduces system invalid files.

2. At the same time, during the scheduled task backup process, the system will also determine whether the table structure file is damaged by judging whether the database structure file in the MySQL/data directory is 0kb. If it is damaged, copy and replace it with the corresponding file in the previous backup folder Damaged files, repair problems caused by corrupted database table structure files, and improve system stability and robustness.

It should be noted that the embodiments of the present invention are characterized in that:

1. Judging whether a file needs to be backed up in a manner different from that of the prior art. The method of file MD5 verification simplifies the process of comparing file content changes. Compared with obtaining file increments or regular full backups, it saves computer memory resources, disk space, and CPU overhead, and reduces system invalid files.

2. At the same time, during the backup process of scheduled tasks, the system automatically judges whether the database table structure files under the MySQL/data directory are damaged and repairs them, which improves the stability and robustness of the system and reduces the after-sales labor costs of the system.

It should be noted that, for the above method or process embodiment, for the sake of simple description, it is expressed as a series of action combinations, but those skilled in the art should know that the embodiment of the present invention is not limited by the described action sequence. Because according to the embodiment of the present invention, some steps may be performed in other order or simultaneously. Secondly, those skilled in the art should also know that the embodiments described in the specification are all optional embodiments, and the actions involved are not necessarily required by the embodiments of the present invention.

Referring to Fig. 4, the embodiment of the present invention also provides a database file backup device, including:

The task response module 1 is used to traverse all files in the query target database in response to the scheduled backup task instruction;

The file comparison module 2 is used to compare the consistency of each table data file in the target database with the table data files in the target backup folder one by one, and compare the table data files with inconsistent results in the target database. Mark as a file to be backed up; wherein, the target backup folder is the latest backup folder in the backup library;

The file backup module 3 is used to copy the target backup folder to obtain the current backup folder, name the current backup folder based on the current system time, copy all table data files marked as files to be backed up and Replace the corresponding table data file in the current backup folder, and add the current backup folder to the backup library.

Further, the task response module 1 is specifically used for:

When the table structure file of the target database is inquired, if it is judged that the table structure file is damaged, the backup table structure file in the target backup folder is copied, and the table structure in the target database is copied. file to replace.

Further, the task response module 1 is specifically used for:

When it is queried and judged that the table data file in the target database is damaged, the current backup task is ended and the file recovery program is started.

Further, the database file backup device also includes a folder creation module, which is used for:

When it is judged that there is no backup folder in the backup repository, a full backup is performed on the target database to obtain a first backup folder, and the first backup folder is added to the backup repository.

Further, the database file backup device also includes a file deletion module, which is used for:

When it is judged that the number of backup folders in the backup library is greater than a preset threshold, the oldest backup folder in the backup library is deleted.

Further, the manner of judging that the table structure file is damaged includes:

When judging whether the file size of the table structure file is zero, it is determined that the table structure file is damaged.

Further, the file comparison module 2 is specifically used for:

Calculate respectively the MD5 value of each table data file in the target database and the table data file in the target backup folder;

Based on the MD5 value of each file, each table data file in the target database is compared with the table data file in the target backup folder in one-to-one correspondence;

When judging that the MD5 value of the target table data file in the target database is inconsistent with the MD5 value of the corresponding table data file in the target backup folder, mark the target table data file as a file to be backed up.

It can be understood that the above device embodiment corresponds to the method embodiment of the present invention, and a database file backup device provided by the embodiment of the present invention can realize the database file provided by any method embodiment of the present invention. backup method.

The present invention also provides a non-transitory computer-readable storage medium, on which a computer program is stored, and when the computer program is executed by a processor, the database file backup method described in any one is implemented.

It should be noted that the device embodiments described above are only illustrative, and the units described as separate components may or may not be physically separated, and the components shown as units may or may not be physically separated. A unit can be located in one place, or it can be distributed to multiple network units. Part or all of the modules can be selected according to actual needs to achieve the purpose of the solution of this embodiment. In addition, in the drawings of the device embodiments provided by the present invention, the connection relationship between the modules indicates that they have a communication connection, which can be specifically implemented as one or more communication buses or signal lines. It can be understood and implemented by those skilled in the art without creative effort.

Those skilled in the art can clearly understand that, for the sake of convenience and brevity, for the specific working process of the device described above, reference may be made to the corresponding process in the foregoing method embodiment, which will not be repeated here.

The terminal device may be a computing device such as a desktop computer, a notebook, a handheld computer, or a cloud server. The terminal device may include, but not limited to, a processor and a memory.

The so-called processor can be a central processing unit (Central Processing Unit, CPU), and can also be other general-purpose processors, digital signal processors (Digital Signal Processor, DSP), application specific integrated circuits (Application Specific Integrated Circuit, ASIC), off-the-shelf Programmable gate array (Field-Programmable Gate Array, FPGA) or other programmable logic devices, discrete gate or transistor logic devices, discrete hardware components, etc. The general-purpose processor may be a microprocessor or any conventional processor, etc. The processor is the control center of the terminal device, and connects various parts of the entire terminal device with various interfaces and lines.

The memory can be used to store the computer program, and the processor implements various functions of the terminal device by running or executing the computer program stored in the memory and calling data stored in the memory. The memory may mainly include a program storage area and a data storage area, wherein the program storage area may store an operating system, an application program required by at least one function, etc.; the data storage area may store data created according to the use of the mobile phone, etc. In addition, the memory may include high-speed random access memory, and may also include non-volatile memory, such as hard disk, internal memory, plug-in hard disk, smart memory card (Smart Media Card, SMC), secure digital (Secure Digital, SD) card , a flash memory card (Flash Card), at least one magnetic disk storage device, flash memory device, or other volatile solid-state storage devices.

The storage medium is a computer-readable storage medium, and the computer program is stored in the computer-readable storage medium. When the computer program is executed by a processor, the steps of the above-mentioned various method embodiments can be realized. Wherein, the computer program includes computer program code, and the computer program code may be in the form of source code, object code, executable file or some intermediate form. The computer-readable medium may include: any entity or device capable of carrying the computer program code, a recording medium, a U disk, a removable hard disk, a magnetic disk, an optical disk, a computer memory, and a read-only memory (ROM, Read-Only Memory) , Random Access Memory (RAM, Random Access Memory), electrical carrier signal, telecommunication signal, and software distribution medium, etc. It should be noted that the content contained in the computer-readable medium may be appropriately increased or decreased according to the requirements of legislation and patent practice in the jurisdiction. For example, in some jurisdictions, computer-readable media Excludes electrical carrier signals and telecommunication signals.

The above description is a preferred embodiment of the present invention, and it should be pointed out that for those skilled in the art, without departing from the principle of the present invention, some improvements and modifications can also be made, and these improvements and modifications are also considered Be the protection scope of the present invention.

Claims (10)

1. A database file backup method, characterized in that, comprising: In response to the scheduled backup task instruction, traverse all files in the query target database; Each table data file in the target database is correspondingly compared with the table data files in the target backup folder one by one, and the table data files with inconsistent comparison results in the target database are marked as files to be backed up; wherein , the target backup folder is the latest backup folder in the backup repository; Copying the target backup folder to obtain a current backup folder, naming the current backup folder based on the current system time, copying all table data files marked as files to be backed up and copying the current backup folder Replace the corresponding table data file in , and add the current backup folder to the backup library. 2. The database file backup method according to claim 1, wherein the traversal of all files in the query target database includes: When the table structure file of the target database is inquired, if it is judged that the table structure file is damaged, the backup table structure file in the target backup folder is copied, and the table structure in the target database is copied. file to replace. 3. The database file backup method according to claim 1, wherein the traversal of all files in the query target database includes: When it is queried and judged that the table data file in the target database is damaged, the current backup task is ended and the file recovery program is started. 4. The database file backup method according to claim 1, wherein, before responding to the timing backup task instruction, before traversing all files in the query target database, it also includes: When it is judged that there is no backup folder in the backup repository, a full backup is performed on the target database to obtain a first backup folder, and the first backup folder is added to the backup repository. 5. The database file backup method according to claim 1, further comprising: When it is judged that the number of backup folders in the backup library is greater than a preset threshold, the oldest backup folder in the backup library is deleted. 6. The method for backing up database files according to claim 2, wherein the method for judging that the table structure file is damaged comprises: When judging whether the file size of the table structure file is zero, it is determined that the table structure file is damaged. 7. The database file backup method according to any one of claims 1-6, characterized in that, performing one-to-one correspondence between each table data file in the target database and the table data file in the target backup folder Consistency comparison, and the table data files with inconsistent comparison results in the target database are marked as files to be backed up, specifically including: Calculate respectively the MD5 value of each table data file in the target database and the table data file in the target backup folder; Based on the MD5 value of each file, each table data file in the target database is compared with the table data file in the target backup folder in one-to-one correspondence; When judging that the MD5 value of the target table data file in the target database is inconsistent with the MD5 value of the corresponding table data file in the target backup folder, mark the target table data file as a file to be backed up. 8. A database file backup device, characterized in that, comprising: A task response module, configured to traverse all files in the query target database in response to a scheduled backup task instruction; A file comparison module, configured to perform a one-to-one consistency comparison between each table data file in the target database and the table data file in the target backup folder, and mark the table data files with inconsistent comparison results in the target database is the file to be backed up; wherein, the target backup folder is the latest backup folder in the backup library; A file backup module, configured to copy the target backup folder to obtain a current backup folder, name the current backup folder based on the current system time, copy all table data files marked as files to be backed up and The corresponding table data file in the current backup folder is replaced, and the current backup folder is added to the backup library. 9. A terminal device, comprising a processor and a memory storing a computer program, wherein the processor implements the database file backup method according to any one of claims 1 to 7 when executing the computer program. 10. A non-transitory computer-readable storage medium, on which a computer program is stored, characterized in that, when the computer program is executed by a processor, the database file backup method according to any one of claims 1 to 7 is realized.

Images