CN112231286A - Method for quickly recovering historical data of database - Google Patents

Method for quickly recovering historical data of database Download PDF

Info

Publication number
CN112231286A
CN112231286A CN202010884718.7A CN202010884718A CN112231286A CN 112231286 A CN112231286 A CN 112231286A CN 202010884718 A CN202010884718 A CN 202010884718A CN 112231286 A CN112231286 A CN 112231286A
Authority
CN
China
Prior art keywords
database
data
metadata set
metadata
storage unit
Prior art date
Legal status (The legal status is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the status listed.)
Pending
Application number
CN202010884718.7A
Other languages
Chinese (zh)
Inventor
李建辉
孙朝阳
魏兴华
陈栋
李春
罗春
吴炎
臧冰凌
张文件
Current Assignee (The listed assignees may be inaccurate. Google has not performed a legal analysis and makes no representation or warranty as to the accuracy of the list.)
Hangzhou Woqu Technology Co ltd
Original Assignee
Hangzhou Woqu Technology Co ltd
Priority date (The priority date is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the date listed.)
Filing date
Publication date
Application filed by Hangzhou Woqu Technology Co ltd filed Critical Hangzhou Woqu Technology Co ltd
Priority to CN202010884718.7A priority Critical patent/CN112231286A/en
Publication of CN112231286A publication Critical patent/CN112231286A/en
Pending legal-status Critical Current

Links

Images

Classifications

    • G—PHYSICS
    • G06—COMPUTING; CALCULATING OR COUNTING
    • G06F—ELECTRIC DIGITAL DATA PROCESSING
    • G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
    • G06F16/10—File systems; File servers
    • G06F16/18—File system types
    • G06F16/1805—Append-only file systems, e.g. using logs or journals to store data
    • G06F16/1815—Journaling file systems
    • G—PHYSICS
    • G06—COMPUTING; CALCULATING OR COUNTING
    • G06F—ELECTRIC DIGITAL DATA PROCESSING
    • G06F11/00—Error detection; Error correction; Monitoring
    • G06F11/07—Responding to the occurrence of a fault, e.g. fault tolerance
    • G06F11/14—Error detection or correction of the data by redundancy in operation
    • G06F11/1402—Saving, restoring, recovering or retrying
    • G06F11/1446—Point-in-time backing up or restoration of persistent data
    • G06F11/1458—Management of the backup or restore process
    • G—PHYSICS
    • G06—COMPUTING; CALCULATING OR COUNTING
    • G06F—ELECTRIC DIGITAL DATA PROCESSING
    • G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
    • G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
    • G06F16/23—Updating
    • G06F16/2379—Updates performed during online database operations; commit processing

Abstract

The invention relates to a method for recovering data of a database, and discloses a method for rapidly recovering historical data of the database, which comprises the steps of firstly segmenting different storage units for storage equipment for storing the database, and recording a data set of position information of the storage units as a metadata set; when the database runs, continuously or periodically writing data into a storage device of the database, and updating the metadata set; when the data damage time point stored in the database is similar to the creation time of the metadata set, the retrieval time of the historical data set is the same as the time of forming the logical volume; when the data damage time point stored in the database is far different from the metadata set creation time, retrieving the historical data set creation time, comparing the historical data set creation time with the backup log, and finding out the metadata set creation time point; the invention retrieves the lost historical data within 1 second, the accuracy of the historical data can reach the second level and even the transaction level, thereby greatly reducing the interruption time of the service and reducing the economic loss caused by data loss.

Description

Method for quickly recovering historical data of database
Technical Field
The invention relates to a method for recovering historical data of a database, in particular to a method for rapidly recovering historical data of the database.
Background
Database native backup tools, such as RMAN by ORACLE, MYSQL, MYSQLDUMP, etc., are the most frequently used backup tools in the daily work of database administrators today. The native backup is easy to install and learn, so the native backup is widely used, but has several serious problems: in the time window between two backups, the data is completely in an unprotected state, and the risk of data loss exists. Whether a backup or restore process, requires exporting and importing the full library, taking hours or even days.
Patent headings: a high-reliability data backup and recovery method based on a cluster architecture is disclosed in the application number: CN201510657187.7, application date: 2015-01-13, a high-reliability data backup and recovery method based on cluster architecture, the technical features are: the online data backup and recovery method comprises an online data backup and recovery method and a historical data backup and recovery method, wherein the online data backup and recovery method comprises the following steps: creating a tablespace and dividing the range partition table according to time; realizing a backup function in the process of importing data into the database; writing the backup information into a system table; when the data of the day becomes historical data, the data is backed up in a historical data backup mode; and when the disk fails, reloading the data to realize the recovery of the online data. In the prior art, different backup recovery methods are adopted for two types of data, so that when a medium fault occurs, the high availability and high reliability of a single database node are improved, and the problems that the node data cannot be effectively recovered in a general cluster dual-computer hot-standby scheme, and the data is lost when the medium fault occurs to a mutual standby node are effectively solved.
For the prior art, a data backup and recovery method is provided, and data recovery is performed in a backup mode, so that data is in an unprotected state between backups, the risk of data loss exists, and a long time is consumed for data recovery.
Disclosure of Invention
The invention provides a method for quickly recovering historical data of a database, aiming at the defects that when the database in the prior art is backed up, data is in an unprotected state, the risk of data loss exists, and long time is consumed for recovering the data.
In order to solve the technical problem, the invention is solved by the following technical scheme:
a method for rapidly recovering historical data of a database comprises the following steps:
step 1: a user designates a storage device for storing a database;
step 2: segmenting different storage units for a memory of storage equipment for storing a database, recording position information of the storage units, and recording a data set of the position information of the storage units as a metadata set; because the position information of the storage unit is recorded as metadata and is not the data information of the actual storage unit, the occupied space is small;
and step 3: when the database runs, continuously or periodically writing data into the storage equipment of the database, updating the metadata set, and recording the information of the storage unit corresponding to the storage equipment which continuously or periodically writes data into the database;
and 4, step 4: periodically writing data and creating a metadata set;
firstly, locking a database and forbidding all metadata writing;
secondly, backing up the current metadata into a metadata set, wherein the metadata set records the states of all storage units at the current time;
thirdly, unlocking the database and receiving the written metadata set;
and 5: the database continuously writes data into a storage device of the database; retrieving the metadata set of the database and comparing the metadata set with the corresponding storage unit;
when the data is written into the storage unit corresponding to the metadata set, writing into other blank storage units, and executing the step 3 to update the metadata set;
when the data is not written into the storage unit corresponding to the metadata set, writing the metadata set into the corresponding storage unit;
the database modifies the written data, a metadata set is retrieved first, and when the storage unit corresponding to the modified data is not in the metadata set, the storage unit of the written data is directly modified, and the metadata set is updated;
when the modified data correspond to the storage unit in the metadata set, writing the modified data in the blank storage unit, and updating the metadata set; the database reads and modifies the storage unit written with the modified data according to the information of the metadata set;
step 6: copying a log file generated by a database, and backing up a log;
and 7: when the data stored in the database is damaged, using a metadata set created before the data damage time to retrieve a storage unit associated with the metadata set; the associated storage units form a history data set in the form of a logical volume;
and 8: when the data damage time point stored in the database is the same as the creation time of the metadata set, the retrieval time of the historical data set is the same as the time of forming the logical volume; when the data damage time point stored in the database is different from the metadata set creation time, the historical data set creation time is retrieved through step 7 and compared with the backup log of step 6, and the metadata set creation time point is found.
Preferably, the storage device comprises a local slot machine disk, a flash disk, NVMe, or remote SAN storage.
Preferably, the size of the memory unit is the same for the same memory device, and is one of 8KB, 4KB, 1KB and 512 Byte.
Preferably, the database is locked by a locking command, wherein the MYSQL library locking command is FLUSH TABLE WITH READ LOCK; the ORACLE library lock command is ALTER DATABASE OPEN READ ONLY, and the lock command is applicable to different versions of the same DATABASE. Through the locking of the database, other data are effectively prevented from being written in, and the database is protected.
Preferably, the method for accurately and quickly recovering the historical data by the database is suitable for the production database and is also suitable for the slave database.
Due to the adoption of the technical scheme, the invention has the remarkable technical effects that: segmenting different storage units for storage equipment for storing a database, and recording a data set of position information of the storage units as a metadata set; when the database runs, continuously or periodically writing data into a storage device of the database, and updating the metadata set; when the data damage time point stored in the database is similar to the creation time of the metadata set, the retrieval time of the historical data set is the same as the time of forming the logical volume; and when the data damage time point stored in the database is far different from the metadata set creation time, retrieving the historical data set creation time, and comparing the historical data set creation time with the backup log to find the metadata set creation time point.
The method is suitable for most databases with log functions, such as mainstream Oracle, MySQL, MariaDB, SQLServer, Postgresql and the like, and finds the historical data lost by the databases within 1 second, the accuracy of the historical data can reach the second level or even the transaction level, the interruption time of the service is greatly reduced, and therefore the economic loss caused by data loss is reduced.
Drawings
FIG. 1 is a flow chart of the operation of the present invention.
Fig. 2 is a schematic diagram of step 3 of the present invention.
Fig. 3 is a schematic diagram of step 4 of the present invention.
Fig. 4 is a schematic diagram of step 5 of the present invention.
Fig. 5 is a schematic diagram of step 8 of the present invention.
Fig. 6 is a schematic diagram of step 8 of the present invention.
Detailed Description
The present invention will be described in further detail with reference to the accompanying drawings and examples.
Example 1
A method for rapidly recovering historical data of a database comprises the following steps:
step 1: a user designates a storage device for storing a database;
step 2: segmenting different storage units for a memory of storage equipment for storing a database, recording position information of the storage units, and recording a data set of the position information of the storage units as a metadata set;
and step 3: when the database runs, continuously or periodically writing data into the storage equipment of the database, updating the metadata, and recording the information of the storage unit corresponding to the storage equipment which continuously or periodically writes the data into the database; because the position information of the storage unit is recorded as metadata and is not the data information of the actual storage unit, the occupied space is small;
and 4, step 4: periodically writing data and creating a metadata set; locking a database and forbidding all metadata writing; secondly, backing up the current metadata into a metadata set, wherein the metadata set records the states of all storage units at the current time; unlocking the database and receiving the written metadata set;
and 5: the database continuously writes data into a storage device of the database; retrieving a metadata set of the database, comparing the metadata set with the corresponding storage unit, writing data into other blank storage units when the storage unit corresponding to the metadata set has written the data, and executing the step 3 to update the metadata set; when the data is not written into the storage unit corresponding to the metadata set, writing the metadata set into the corresponding storage unit;
the database modifies the written data, a metadata set is retrieved first, and when the storage unit corresponding to the modified data is not in the metadata set, the storage unit of the written data is directly modified, and the metadata set is updated;
when the modified data correspond to the storage unit in the metadata set, writing the modified data in the blank storage unit, and updating the metadata set; the database reads and modifies the storage unit written with the modified data according to the information of the metadata set;
step 6: copying a log file generated by a database, and backing up a log;
and 7: when the data stored in the database is damaged, using a metadata set created before the data damage time to retrieve a storage unit associated with the metadata set; the associated storage units form a history data set in the form of a logical volume;
and 8: when the data damage time point stored in the database is the same as the creation time of the metadata set, the retrieval time of the historical data set is the same as the time of forming the logical volume; and when the data damage time point stored in the database is different from the metadata set creation time, retrieving the historical data set creation time through the step 7, comparing the historical data set creation time with the backup log in the step 6, finding out the metadata set creation time point, and retrieving the historical data set accurately to the second level or even the transaction level according to different databases.
The storage devices include local slot mechanical disks, flash memory disks, NVMe, or remote SAN storage.
The size of the memory unit is the same for the same memory device, and is one of 8KB, 4KB, 1KB and 512 Byte.
The method for accurately and quickly recovering the historical data of the database is suitable for producing the database.
Example 2
Unlike embodiment 1, the method for accurately and quickly recovering the historical data of the database is suitable for being placed in a slave database.
Example 3
On the basis of embodiment 1, the main stream databases include Oracle, MySQL, maridb, SQLServer, Postgresql and the like, the locking database LOCKs the database through a locking command, and the MySQL library locking command is FLUSH TABLE WITH READ LOCK; the ORACLE library locking command is ALTER DATABASE OPEN READ ONLY, the SQLServer library locking command is ALTER DATABASE dbname set single _ user, and the LOCK command of Postgresql library is LOCK DATABASE db _ name; the lock command applies to different versions of the same database.
Example 4
On the basis of embodiment 1, when the database runs, data is continuously or periodically written into the physical hard disk, at this time, metadata is synchronously updated, and the storage units where the written data is finally stored are recorded, as shown in fig. 2; periodically writing data and creating a metadata set; according to FIG. 3;
in the process of continuously writing data into the database, metadata is searched first, newly added data is written into a data-free storage unit, and the metadata is updated according to the method shown in 1 in the attached figure 4;
if the database needs to modify the written data, a metadata set needs to be searched first, and if a storage unit (named as A) which is expected to be modified is not in the metadata set, the storage unit is directly modified; if the first data is in the metadata set, the storage unit is not modified, an empty storage unit (called as a second) is additionally used for writing modified data, the metadata is updated, and a subsequent database reads and modifies the second data as the latest data according to the information of the metadata, so that the first data is not read any more. According to figures 2 and 3 of the accompanying drawings;
the historical data is originally stored on the hard disk and is not deleted or modified, so that the recovery time is extremely short, and if the data damage time is extremely close to the metadata set creation time, the historical data recovery is completed only by consuming the time for forming the logical volume; according to the illustration of fig. 5.
When the data damage time point stored in the database is the same as the creation time of the metadata set, the retrieval time of the historical data set is the same as the time of forming the logical volume, namely the historical data set is retrieved in 0.5 second; when the data damage time point stored in the database is different from the metadata set creation time, the metadata set creation time point is found by retrieving the historical data set creation time in step 7 and comparing the historical data set creation time point with the backup log in step 6, and the historical data set is retrieved accurately to the second level or even the transaction level according to different databases, as shown in fig. 6.

Claims (6)

1. A method for rapidly recovering historical data of a database is characterized in that: comprises the following steps
Step 1: a user designates a storage device for storing a database;
step 2: segmenting different storage units for the memory of the storage device in the step 1, recording the position information of the storage units, and recording a data set of the position information of the storage units as a metadata set;
and step 3: writing a data set; when the database runs, continuously or periodically writing data into the storage equipment of the database, updating the metadata set, and recording the information of the storage unit corresponding to the storage equipment which continuously or periodically writes data into the database;
and 4, step 4: periodically writing data and creating a metadata set;
and 5: the database continuously writes data into a storage device of the database;
step 6: copying a log file generated by a database, and backing up a log;
and 7: when the data stored in the database is damaged, using a metadata set created before the data damage time to retrieve a storage unit associated with the metadata set; the associated storage units form a history data set in the form of a logical volume;
and 8: when the data damage time point stored in the database is the same as the creation time of the metadata set, the retrieval time of the historical data set is the same as the time of forming the logical volume; when the data damage time point stored in the database is different from the metadata set creation time, the historical data set creation time is retrieved through step 7 and compared with the backup log of step 6, and the metadata set creation time point is confirmed.
2. The method for rapidly recovering database history data according to claim 1, wherein the step 4 comprises the steps of: firstly, locking a database and forbidding all metadata writing; secondly, backing up the current metadata into a metadata set, wherein the metadata set records the states of all storage units at the current time; and thirdly, unlocking the database and accepting the written metadata set.
3. The method for rapidly recovering database history data according to claim 1, wherein step 5 the database continuously writes data into the storage device of the database,
firstly, retrieving a metadata set of a database, comparing the metadata set with a corresponding storage unit, writing other blank storage units which are not written with data when the corresponding storage unit of the metadata set has written with the data, and executing the step 3 to update the metadata set; when the data is not written into the storage unit corresponding to the metadata set, writing the metadata set into the corresponding storage unit;
secondly, the database modifies the written data, a metadata set is retrieved firstly, and when the storage unit corresponding to the modified data is not in the metadata set, the storage unit of the written data is directly modified, and the metadata set is updated; when the modified data correspond to the storage unit in the metadata set, writing the modified data in the blank storage unit, and updating the metadata set; and the database reads and modifies the storage unit written with the modified data according to the information of the metadata set.
4. The method for rapidly recovering the historical data of the database according to claim 1, wherein: the storage devices include local slot mechanical disks, flash memory disks, NVMe, or remote SAN storage.
5. The method of claim 1, wherein the method comprises the following steps: the memory cell size is the same for the same memory device.
6. The method for rapidly recovering the historical data of the database according to claim 1, wherein: and the locking database locks the database through a locking command, and the locking command is suitable for different versions of the same database.
CN202010884718.7A 2020-08-28 2020-08-28 Method for quickly recovering historical data of database Pending CN112231286A (en)

Priority Applications (1)

Application Number Priority Date Filing Date Title
CN202010884718.7A CN112231286A (en) 2020-08-28 2020-08-28 Method for quickly recovering historical data of database

Applications Claiming Priority (1)

Application Number Priority Date Filing Date Title
CN202010884718.7A CN112231286A (en) 2020-08-28 2020-08-28 Method for quickly recovering historical data of database

Publications (1)

Publication Number Publication Date
CN112231286A true CN112231286A (en) 2021-01-15

Family

ID=74116480

Family Applications (1)

Application Number Title Priority Date Filing Date
CN202010884718.7A Pending CN112231286A (en) 2020-08-28 2020-08-28 Method for quickly recovering historical data of database

Country Status (1)

Country Link
CN (1) CN112231286A (en)

Citations (4)

* Cited by examiner, † Cited by third party
Publication number Priority date Publication date Assignee Title
US20150286534A1 (en) * 2014-04-07 2015-10-08 International Business Machines Corporation Point in time recovery support for pending schema definition changes
CN107451013A (en) * 2017-06-30 2017-12-08 北京奇虎科技有限公司 Data reconstruction method, apparatus and system based on distributed system
US20190129976A1 (en) * 2017-11-01 2019-05-02 Electronics And Telecommunications Research Institute Apparatus for controlling synchronization of metadata on network and method for the same
CN110554834A (en) * 2018-06-01 2019-12-10 阿里巴巴集团控股有限公司 File system data access method and file system

Patent Citations (4)

* Cited by examiner, † Cited by third party
Publication number Priority date Publication date Assignee Title
US20150286534A1 (en) * 2014-04-07 2015-10-08 International Business Machines Corporation Point in time recovery support for pending schema definition changes
CN107451013A (en) * 2017-06-30 2017-12-08 北京奇虎科技有限公司 Data reconstruction method, apparatus and system based on distributed system
US20190129976A1 (en) * 2017-11-01 2019-05-02 Electronics And Telecommunications Research Institute Apparatus for controlling synchronization of metadata on network and method for the same
CN110554834A (en) * 2018-06-01 2019-12-10 阿里巴巴集团控股有限公司 File system data access method and file system

Non-Patent Citations (1)

* Cited by examiner, † Cited by third party
Title
余向前 等: "基于快照技术实现数据库快速恢复的方法研究", 《通讯世界》 *

Similar Documents

Publication Publication Date Title
US10235375B1 (en) Persistent file system objects for management of databases
CN104850598B (en) A kind of real-time data base back-up restoring method
US8965850B2 (en) Method of and system for merging, storing and retrieving incremental backup data
US10949415B2 (en) Logging system using persistent memory
US7739547B2 (en) Failure recovery and error correction techniques for data loading in information warehouses
Gait The optical file cabinet: a random-access file system for write-once optical disks
US7310654B2 (en) Method and system for providing image incremental and disaster recovery
US10255235B2 (en) Database storage system based on jukebox and method using the system
US9009428B2 (en) Data store page recovery
CN103530290A (en) Method and system for data migration among databases
US20070185936A1 (en) Managing deletions in backup sets
US20070186066A1 (en) Fast verification of computer backup data
US9996557B2 (en) Database storage system based on optical disk and method using the system
US9542279B2 (en) Shadow paging based log segment directory
US20060015767A1 (en) Reducing data loss and unavailability by integrating multiple levels of a storage hierarchy
CN103914359A (en) Data recovery method and device
US8108356B2 (en) Method for recovering data in a storage system
EP3451173A1 (en) Restoring a database using a fully hydrated backup
CN111414320B (en) Method and system for constructing disk cache based on nonvolatile memory of log file system
CN113885809B (en) Data management system and method
CN114020686A (en) File snapshot synchronization method, system, device and medium based on difference log
CN112231286A (en) Method for quickly recovering historical data of database
CN110658981B (en) Method for prolonging service life of Flash
US7831564B1 (en) Method and system of generating a point-in-time image of at least a portion of a database
US8918364B1 (en) Online mirror state transitioning in databases

Legal Events

Date Code Title Description
PB01 Publication
PB01 Publication
SE01 Entry into force of request for substantive examination
SE01 Entry into force of request for substantive examination
RJ01 Rejection of invention patent application after publication
RJ01 Rejection of invention patent application after publication

Application publication date: 20210115