CN106844363B

CN106844363B - Method and equipment for physical hot standby and data recovery of database

Info

Publication number: CN106844363B
Application number: CN201510881424.8A
Authority: CN
Inventors: 王波
Original assignee: Alibaba Group Holding Ltd
Current assignee: Alibaba Group Holding Ltd
Priority date: 2015-12-03
Filing date: 2015-12-03
Publication date: 2021-01-29
Anticipated expiration: 2035-12-03
Also published as: CN106844363A

Abstract

The application aims at providing a method and equipment for physical hot standby and data recovery of a database, which are used for copying a binary log to a backup set in real time until the backup is finished; locking each data table of the database in sequence and then backing up the data table to the backup set; and generating a log site information file based on the execution site executed in the binary log corresponding to the stored data in each data table during locking, and storing the log site information file in the backup set, so that a large global lock required during backup of the database can be removed, the influence of the backup on the whole database system is reduced, and the flexibility of physical hot backup of the database is effectively improved.

Description

Method and equipment for physical hot standby and data recovery of database

Technical Field

The application relates to the field of computers, in particular to a technology for performing physical hot standby and data recovery on a database.

Background

With the continuous development of computer technology, the backup of the database becomes an important means for saving the database information. For example, the online relational database system (MySQL) performs regular backups on the main database, so that when the online MySQL database system fails and data is damaged or lost, a backup set can be used for recovery, and loss is reduced. But when the physical backup of the non-transaction engine data in the online MySQL database system is carried out, the whole online MySQL database is blocked, and normal business can not be carried out.

In the prior art, a physical backup of non-transaction engine data in an online MySQL database system is performed by adding a global large lock to the online MySQL database system by using a backup tool (such as a Percona-xtrackup) in the prior art, and then releasing the global large lock after all data files in the online MySQL database system are backed up; when the non-transaction engine data in the online MySQL database system is damaged or lost, the non-transaction engine data is directly copied to a target path needing to be recovered, and then the online MySQL database system is directly started. Because a global read lock is added to the online MySQL database system through a backup tool (such as a Percona-XBackup), the whole database becomes a read-only database, the update operation of the database is blocked completely, which is unacceptable for normal business, so that the backup has a large influence on the normal business of the online MySQL database system, and the database with more data of a non-transaction engine cannot be backed up on a main database, thereby resulting in poor flexibility of data backup of the non-transaction engine.

Therefore, in the prior art, a backup tool in the prior art is used for adding a global large lock to the online MySQL database system and backing up the non-transaction engine data, so that the updating operation of the whole database is blocked, the normal service of the online MySQL database system is affected, and the flexibility of backing up the non-transaction engine data is poor.

Disclosure of Invention

The technical problem to be solved by the application is to provide a method and equipment for performing physical hot standby and data recovery on a database, so as to solve the problem that in the prior art, after a sampling backup tool applies a global large lock to an online MySQL database system and backups are performed, the updating operation of the whole database is completely blocked, the normal service of the online MySQL database system is influenced, and thus the flexibility of non-transaction engine data backup is poor.

To solve the above technical problem, according to an aspect of the present application, there is provided a method for performing physical hot standby on a database, including:

copying the binary log to a backup set in real time until the backup is finished;

locking each data table of the database in sequence and then backing up the data table into the backup set;

and generating a log site information file corresponding to the execution site executed in the binary log based on the stored data of each data table during locking, and saving the log site information file to the backup set.

According to another aspect of the present application, there is also provided a method for data recovery of a database, including:

the method comprises the steps of obtaining a backup set generated in a physical hot standby stage, wherein the backup set comprises a binary log copied in the physical hot standby stage, each data table of a database backed up after locking and a log site information file generated on the basis that stored data of each data table are corresponding to an execution site executed in the binary log when locking;

copying all the data tables in the backup set to a recovery target path;

and updating all the data tables in the recovery target path based on the binary log and the log site information file.

According to another aspect of the present application, there is also provided an apparatus for performing physical hot standby on a database, including:

the backup binary log device is used for copying the binary log to a backup set in real time until the backup is finished;

the backup data table device is used for locking each data table of the database in sequence and then backing up the data table to the backup set;

and the generating device is used for generating a log site information file corresponding to the execution site executed in the binary log based on the stored data of each data table during locking and saving the log site information file into the backup set.

According to another aspect of the present application, there is also provided an apparatus for data recovery of a database, including:

the acquisition device is used for acquiring a backup set generated in a physical hot standby stage, wherein the backup set comprises the binary log copied in the physical hot standby stage, each data table of a database backed up after locking and a log site information file generated on the basis that stored data of each data table are corresponding to an execution site executed in the binary log when locking;

the copying device is used for copying all the data tables in the backup set to a recovery target path;

and the updating device is used for updating all the data tables in the recovery target path based on the binary log and the log site information file.

Compared with the prior art, according to the method and the device for the physical hot standby of the database, the binary logs are copied to the backup set in real time until the backup is finished; locking each data table of the database in sequence and then backing up the data table into the backup set; and generating a log site information file based on the execution site executed in the binary log corresponding to the stored data in each data table during locking, and storing the log site information file in the backup set, so that a large global lock required during backup of the database can be removed, the influence of the backup on the whole database system is reduced, and the flexibility of physical hot backup of the database is effectively improved.

Further, according to the method and the device for data recovery of the database in the embodiment of the present application, a backup set generated in a physical hot standby stage is obtained, where the backup set includes a binary log copied in the physical hot standby stage, each data table of the database backed up after locking, and a log location information file generated based on an execution location where data stored in each data table at the time of locking corresponds to the execution location in the binary log; copying all the data tables in the backup set to a recovery target path; and updating all the data tables in the recovery target path based on the binary log and the log site information file, so that when the database needs to perform data recovery on the data tables, the data recovery is performed on a single data table in sequence based on the binary log and all the execution events related to the backed-up data tables, and the flexibility of performing data recovery on the data tables is effectively improved on the premise of performing physical hot standby on the database.

Drawings

Other features, objects and advantages of the present application will become more apparent upon reading of the following detailed description of non-limiting embodiments thereof, made with reference to the accompanying drawings in which:

FIG. 1 illustrates a flow diagram of a method for physical hot-standby of a database, according to an aspect of the subject application;

FIG. 2 illustrates a flow diagram of a method for backing up all data tables in accordance with a preferred embodiment of an aspect of the subject application;

FIG. 3 illustrates a flow diagram of a method for data recovery of a database in accordance with another aspect of the subject application;

FIG. 4 illustrates a flowchart of a method for restoring a backup set in accordance with a preferred embodiment of an aspect of the subject application;

FIG. 5 illustrates an apparatus for physical hot-standby of a database, according to an aspect of the subject application;

FIG. 6 illustrates an apparatus for data recovery of a database in accordance with another aspect of the subject application.

The same or similar reference numbers in the drawings identify the same or similar elements.

Detailed Description

The present application is described in further detail below with reference to the attached figures.

FIG. 1 illustrates a flow diagram of a method for physical hot-standby of a database, according to an aspect of the subject application. The method includes step S11, step S12, and step S13.

Step S11 copies the binary log to the backup set in real time until the backup is finished; step S12, sequentially locking and backing up each data in the database to the backup set; the step S13 generates a log location information file corresponding to the execution location executed in the binary log based on the stored data of each data table at the time of locking, and saves the log location information file in the backup set.

In a preferred scenario, the present application is used for performing physical hot-standby and data recovery on a MySQL database (a relational database system). The data table is preferably a MyISAM table (i.e. a data table of a non-transactional storage engine in MySQL data system). In the following, taking the MyISAM table in the MySQL database as the data table of the database as an example, the physical hot standby of the database is described in detail.

For example, a binary log (binlog) is backed up and written into a backup set during a backup process; sequentially and singly adding table-level read locks to all MyISAM tables in a MySQL database in a backup process and then backing up the data tables, wherein the backed-up data tables are written into a backup set, a log site information file is generated on the basis of execution sites executed in a binary log corresponding to stored data when each data table is locked, and the log site information file is written into the backup set, so that in the process of carrying out physical hot standby on all MyISAM tables in the database, the large global lock required in the backup of the database is removed, the influence of the backup on the whole database system is reduced, and the flexibility of carrying out physical hot standby on the database is effectively improved; and when the data tables in the backup set are restored, sequentially and correspondingly updating all MyISAM tables according to the binary log binlog from the execution site of the corresponding binary log binlog when the table-level read lock is added until all MyISAM tables are updated to the data position consistent with the site in the binary log in the database.

In step S12, each data table in the database is locked and backed up in sequence, where the data information in the MyISAM table includes a MyISAM table index MYD, a MyISAM table structure frm, and MyISAM table data MYI. Of course, those skilled in the art should understand that the data information included in the MyISAM table described herein is merely a preferred embodiment of one aspect of the present application, and that the data information included in the MyISAM table, which may be present or later come, may be incorporated by reference herein, as applicable.

First, the step S11 copies the binary log to the backup set in real time until the backup is completed, specifically, the step S11 includes: copying the binary log in real time by using an independent thread; and stopping the independent thread after all the data table backups are completed.

In the embodiment of the application, a binary log binlog in a database is backed up through a backup command, a file is backed up in real time through an independent thread, a backup thread of the binary log is performed on the database containing A, B, C three MyISAM tables, and the specific backup thread is shown as the backup thread of the binary log binlog in table 1.

Table 1 backup thread for binary log binlog

In the embodiment of the present application, the thread for backing up the binary log binlog in the database is: starting a binary log binlog backup thread at 9:00, and inserting a1 into the A table in the database at 9:01, wherein the binlog corresponds to an execution site 25; at 9:02, insert B1 in table B of the database, corresponds to execution site 30 in binlog; c1 is inserted into the C table in the database at 9:03, a binlog corresponds to the execution site 35, a table-level read lock is added to the A table at 9:03, data files related to the A table, including an A table index MYD, an A table structure frm and A table data MYI, are read from the database, and the data files related to the A table are backed up to corresponding files in a backup set at 9:03-9: 04; 04, completing the backup of the A table, wherein the execution site information in the binary log corresponding to the adding of the table-level read lock to the A table is written into a log site information file A.info (mysql-bin.000001, 20), so that the binary log is applied from the execution site 20 to correspondingly restore the A table in the recovery process, and the table-level read lock of the A table is released to enable the A table to continue normal service after the backup of the A table is completed; according to the operation of releasing the table-level read lock after the table-adding level read lock of the table A, the backup of the related data file of the table A, the recording of the corresponding execution site information file of the binary log during the locking and the backup of the table A are completed in the embodiment, the same operation is sequentially performed on the table B and the table C, and after the backup of the data files related to A, B, C MyISAM tables and the binary log binlog in the database is completed, the backup process of the binary log binlog is stopped at a ratio of 9:13 until the backup is completed.

Further, the step S12 is to lock and backup each data of the database to the backup set, specifically, the step S12 is executed in a loop: locking one of the data tables; acquiring an execution site of the binary log when the data table is locked; backing up the locked data table to the backup set; unlocking the locked data table after the backup is finished; until all the data tables are backed up.

In an embodiment of the present application, fig. 2 shows a flowchart of a method for backing up all data tables according to a preferred embodiment of an aspect of the present application, wherein fig. 2 includes the following steps: step S121, adding a table-level read lock to a single MyISAM table, and recording an execution site of a binary log binlog during the table-level read lock adding; step S122, backing up data information and binary logs in the MyISAM table, and generating a log site information file based on execution sites of the binary logs when the data table is locked; step S123, after the backup is finished, the single MyISAM table is unlocked from the table-level read lock; and step S124, judging whether all MyISAM tables are completely backed up, if so, finishing the backup, otherwise, returning to the step S121.

Specifically, in step S121, a table-level READ LOCK is applied to the MyISAM table through a LOCK command LOCK table _ name READ statement, an execution site of a binary log is obtained from a view database log site command SHOW MASTER STATUS statement, and the execution site of the binary log is recorded in a log site information file table _ name.info in step S122; further, in step S122, according to the backup command, the data index MyISAM table index MYD, the data directory MyISAM table structure frm, and the data content MyISAM table data MYI related to the locked specific table name table _ name MyISAM table are backed up; in step S123, after the MyISAM table is completely backed up, releasing the table-level read lock of the MyISAM table through the UNLOCK command UNLOCK table statement to UNLOCK the MyISAM table; in step S124, it is determined whether all the MyISAM tables have been backed up, if yes, the backup is completed, otherwise, the next MyISAM table is continuously obtained, and steps S121 to S123 are repeated, and when all the MyISAM tables have been backed up, the binary log binlog backup thread is stopped.

In this application, after copying the MyISAM table and the binary log binlog of the non-transactional storage engine in MySQL of the relational database system according to the method for performing physical hot standby on the database provided by the present application, if all MyISAM tables in the backup set are to be restored to the data in each MyISAM table in the database corresponding to the log site corresponding to the completed backup, when all MyISAM tables in the backup set need to be restored, all MyISAM tables in the backup set are restored by using the method flow diagram for performing data restoration on the database as shown in fig. 3.

FIG. 3 illustrates a flow diagram of a method for data recovery of a database in accordance with another aspect of the subject application. The method includes step S31, step S32, and step S33.

The step S31 is to obtain a backup set generated in the physical hot standby stage, where the backup set includes a binary log copied in the physical hot standby stage, each data table of the database backed up after locking, and a log location information file generated based on the execution location where the stored data of each data table corresponds to the execution location in the binary log when locking; the step S32 copies all the data tables in the backup set to a restoration target path; in step S33, all the data tables in the restoration target path are updated based on the binary log and the log location information file.

In an embodiment of the application, when a data table in a backup set is restored, a backup set generated in a physical hot standby stage is acquired, where the backup set includes a binary log copied in the physical hot standby stage, each data table of a database backed up after locking, and a log site information file generated based on an execution site executed in the binary log corresponding to data stored in each data table when locking, and corresponding updates, in the backup set copied to a restoration path, all MyISAM tables in the backup set are sequentially updated according to the binary log binlog and the log site information file, starting from the execution site of the binary log binlog corresponding to table-level read lock, until all MyISAM tables in the backup set are updated to a data position consistent with the binary log site corresponding to the database when backup is completed.

Further, the step S13 generates a log location information file based on the execution location where each data table stored at the time of locking corresponds to the execution location executed in the binary log, specifically, the step S33 executes in a loop: step S331 (not shown), determining a recovery execution site of the corresponding data table in the copied binary log based on an execution site to which data stored in the data table in the log site information file at the time of locking corresponds to the execution in the binary log; step S332 (not shown), obtaining all execution events from the recovery execution site to the target execution site based on the copied binary log, and executing the execution events related to the corresponding data table therein to update the corresponding data table in the recovery target path; step S333 (not shown) until the update of all the data tables in the restoration target path is completed.

Preferably, in step S332 (not shown), the target execution site includes: and executing the execution site of the binary log when all the data tables are backed up. For example, in the above embodiment of the present application, at 9:13, after the backup of A, B, C MyISAM tables in the database is completed, the execution site 50 of the binary log corresponding to the last table of A, B, C MyISAM tables in the database when performing table-level read lock is the target execution site.

Specifically, in step S331 (not shown), if an a table of A, B, C three MyISAM tables is taken as a current table, the execution site 20 of the binary log at the time of locking corresponding to the a table is acquired from the a.info log site information file (mysql-bin.000001, 20) as the recovery execution site of the binary log binlog; then, in step S332 (not shown), updating the a table based on all execution event events corresponding to the a table from the execution site 20 when the table addition level read lock is executed to the target execution site 50 in the binary log binlog between the recovery execution site and the target execution site; further, in step S333 (not shown), the above-mentioned updating performed on the a table is repeatedly performed, and the B table and the C table are respectively updated correspondingly based on all the execution events, until the updating of the A, B, C three MyISAM tables in the restoration target path is completed.

Next, step S332 (not shown) is performed to obtain all the execution events from the recovery execution site to the target execution site based on the copied binary log, and execute the execution event related to the corresponding data table therein to update the corresponding data table in the recovery target path, specifically, step S332 (not shown) includes: obtaining all execution events from the recovery execution site to the target execution site in the copied binary log, wherein the execution events have corresponding identifications with the data table; and circularly executing and reading one of the execution events, judging whether the corresponding data table is related to according to the corresponding identifier of the execution event, if so, updating the corresponding data table in the recovery target path according to the execution event, and if not, reading the next execution event until all the execution events from the recovery execution site to the target execution site are read.

It should be noted that the identifier of the backed-up data table includes, but is not limited to, the table name of the backed-up data table and the directory path of the backed-up data table, and of course, the identifier that can be used to indicate the backed-up data table, which may occur now or in the future, may be included in the present application by reference, as applicable.

Further, in step S332 (not shown), the execution event further includes an execution event type, execution data information, and an execution site. The types of execution events include, but are not limited to, an execution insert event, an execution delete event, and an execution update event, and any types of execution events that may occur in the present and future, such as may be applicable to the present application, may be included in the present application by reference.

The following explains the information related to the execution event in detail by taking the table name of the data table as an example. Of course, those skilled in the art should appreciate that the identification of the backed up data table herein by the table name of the data table as an execution event is merely a preferred embodiment of one aspect of the present application and that the identification of the backed up data table, both now and later that may occur, may be incorporated by reference into the present application as applicable.

In the above embodiment of the present application, all events executed on the backed-up data table are shown in table 2, where table 2 is shown in the related information of all executed events in the backup process.

Table 2 related information of all execution events in the backup process

Executing events	Information relating to execution events
		event1	Table: a; the execution event type: inserting; executing data information: a 1; site: 10
event2	Table: b; the execution event type: inserting; executing data information: b 2; site: 15
		event3	Table: c; the execution event type: inserting; executing data information: c 3; site: 20
event4	Table: a; the execution event type: inserting; executing data information: a 1; site: 25
		event5	Table: b; the execution event type: inserting; executing data information: b 2; site: 30
event6	Table: c; the execution event type: inserting; executing data information: c 3; site: 35
		event7	Table: a; the execution event type: inserting; executing data information: a 1; site: 40
event8	Table: b; the execution event type: inserting; executing data information: b 2; site: 45
		event9	Table: c; the execution event type: inserting; executing data information: c 3; site: 50

In the embodiment of the application, event1 indicates that an insert event is performed on the A table, the inserted data is a1, and the event1 in the middle of bytes 0-10 indicates that an insert data a1 is performed on the A table; beginning at byte 10 is event2, where event2 indicates that an insert event is performed on the B table, the inserted data is B1, and the middle of bytes 10-15, event2 indicates that insert data is performed on the B table B1; beginning at byte 15 is event3, where event3 indicates that an insert event is performed on the C table, the inserted data is C1, and the middle of bytes 15-20, event3 indicates that insert data is performed on the C table, C1; the fact that an event4 starts from byte 20 indicates that an insert event is performed on the A table, the inserted data is a2, and the event4 in the middle of bytes 20-25 indicates that an insert data a2 is performed on the A table; beginning at byte 25 is event5, where event5 indicates that an insert event is performed on the B table, the inserted data is B2, and the middle event5 of bytes 25-30 indicates that insert data is performed on the B table B2; beginning at byte 30 is event6, where event6 indicates that an insert event is performed on the C table, the inserted data is C2, and the event6 in the middle of bytes 30-35 indicates that insert data is performed on the C table C2; the event7 starting from byte 35 indicates that an insert event is performed on the A table, the inserted data is a3, and the event7 in the middle of bytes 35-40 indicates that an insert data a3 is performed on the A table; beginning at byte 40 is event8, where event8 indicates that an insert event is performed on the B table, the inserted data is B3, and the middle of bytes 40-45 is event8, which indicates that insert data is performed on the B table B3; beginning at byte 45 is event9, where event9 indicates that an insert event is performed on the C table, the inserted data is C3, and the middle of bytes 45-50, event9 indicates that insert data is performed on the C table, C3.

In an embodiment of the present application, fig. 4 shows a flowchart illustrating a method for restoring a backup set according to a preferred embodiment of an aspect of the present application, wherein fig. 4 includes the following steps: step S41, reading the execution sites of the binary log binlog and all the execution event events in the binlog when adding the table level read lock corresponding to a MyISAM table in the backup set; step S42, reading one execution event in all execution event events in the binlog; step S43, determining whether the execution event is to update the MyISAM table, if yes, performing step S44, otherwise, performing step S42; step S44, updating the MyISAM table according to the execution event; step S45, judging whether there are any events in the binlog which are not read, if yes, executing step S42, otherwise executing step S46; step S46, completing the update of the MyISAM table; step S47, determine whether there is another MyISAM table that is not updated, if yes, execute step S41, otherwise, complete the update of the backup set.

In the foregoing embodiment of the present application, specifically, in step S41, if the tabulation level read lock corresponding to the a table in the backup set is read, the execution site 20 of the binlog and all the execution event events in the binlog are read, where all the execution event events in the binlog are all the execution events { event4, event5, event6, event7, event8, event9} between the recovery execution site 20 of the binlog and the target execution site 50 when the tabulation level read lock corresponding to the a table is read; in step S42, if one of all the execution events { event4, event5, event6, event7, event8, event9} in the binlog is read as event 4; in step S143, determining whether event4 updates the a table according to the table name a of the a table in the execution event, if event4 is insertion data a2 for the a table, executing step S44 to update the a table according to event 4; if the event5 is read in step S42, in step S43, it is determined whether the event5 updates the a table according to the table name a of the a table in the execution event, and it is known that the event5 does not perform the execution event on the a table, then all the execution events { event4, event5, event6, event7, event8, event9} in the binlog are read for the next execution event; in step S45, if all the execution events { event4, event5, event6, event7, event8, event9} in the binlog have been read, the a table update is completed, and it is determined in step S46 whether there are any other MyISAM tables in the backup set that have not been updated, and if yes, steps S41 to S46 are continuously performed on the B table and the C table, respectively, until the A, B, C three tables in the backup set are completely updated.

In the above-described embodiments of the present application. For the a table, only data a1 in the a.myd in the backup set needs to be started from the execution site (mysql-bin.000001, 20) corresponding to the binary log when the table-level read lock is added to the a table as the recovery execution site through the recovery tool, and the insertion data a2 and a3 are executed according to all the execution events in the binlog from the recovery execution site 20 to the target execution site 50; for the B table, only data B1 and B2 in the b.myd in the backup set need to be inserted by a recovery tool from the execution site (mysql-bin.000001, 35) corresponding to the binary log when the table-level read lock is added to B, and according to all execution events in the binlog from the recovery execution site 35 to the target execution site 50, B3 is executed; for the C table, there are data C1, C2, C3 in the c.myd in the backup set, and it is necessary to start from the execution site (mysql-bin.000001, 20) corresponding to the binary log when adding table-level read lock to C through the recovery tool, and it is not necessary to update the C table according to the binlog from the recovery execution site 50 to the target execution site 50. When A, B, C three MyISAM tables are updated based on the log site information file and the binary log binlog, the data corresponding to the three tables are { A: a1, a2, a3}, { B: B1, B2, B3}, and { C: C1, C2, C3}, so that the data information in the three MyISAM tables in the backup set is restored to a form consistent with the data information directly available in the database.

In the above embodiment of the present application, first, when performing physical hot-standby on a MyISAM table of a non-transactional storage engine in MySQL of an online relational database system, after a single table-level read lock needs to be added to each MyISAM table in the online MySQL database, copying a data file in the MyISAM table, where the data file in the MyISAM table includes a data index MyISAM table index MYD, a data directory MyISAM table structure frm, and data content MyISAM table data MYI, and simultaneously recording a corresponding binary log locus when performing table-level read lock on the MyISAM table, continuously copying a binary log binlog during the physical hot-standby, writing a data file related to the copied MyISAM table and the binary log into a backup set, so that when performing physical hot-standby on related data in the MyISAM table, the MyISAM table needs to be added with a large lock when being removed, and ensuring that all other MyISAM tables except the physical hot-standby data in the MyISAM table can be normally backed up, therefore, the influence of the physical hot standby process of the database on the database system is reduced, and the backup flexibility is effectively improved; when data recovery is needed to be carried out on MyISAM tables in MySQL of a relational database system, the backed-up binary log binlog is applied to each MyISAM table from the corresponding recovery execution site, and corresponding data recovery is carried out on all MyISAM tables in a backed-up backup set copied to a recovery target path in sequence, so that the data in all MyISAM tables in the backup set are recovered to sites consistent with the data in the MyISAM tables in online MySQL.

FIG. 5 illustrates an apparatus for physical hot-standby of a database, according to an aspect of the subject application. The device 1 comprises backup binary log means 11, backup data table means 12 and generating means 13.

The binary log backup device 11 copies the binary log to the backup set in real time until the backup is finished; the backup data table device 12 sequentially locks each data of the database and then backs up the data into the backup set; the generating device 13 generates a log location information file corresponding to the execution location executed in the binary log based on the stored data of each data table during locking, and saves the log location information file to the backup set.

Here, the device 1 includes, but is not limited to, a user device, a network device, or a device formed by integrating a user device and a network device through a network. The user equipment includes, but is not limited to, a mobile electronic product, which may employ any operating system, such as an android operating system, an iOS operating system, and the like. The network device includes an electronic device capable of automatically performing numerical calculation and information processing according to preset or stored instructions, and the hardware includes but is not limited to a microprocessor, an Application Specific Integrated Circuit (ASIC), a programmable gate array (FPGA), a Digital Signal Processor (DSP), an embedded device, and the like. Including, but not limited to, the internet, a wide area network, a metropolitan area network, a local area network, a VPN network, a wireless Ad Hoc network (Ad Hoc network), etc. Preferably, the device 1 may also be a script program running on a device formed by integrating the user device with a network device via a network. Of course, those skilled in the art will appreciate that the above-described apparatus 1 is merely exemplary, and that other existing or future existing apparatus 1, as may be suitable for use in the present application, are also intended to be encompassed within the scope of the present application and are hereby incorporated by reference.

The above devices continuously operate, and herein, those skilled in the art should understand that "continuously" means that the above devices continuously copy the binary log into the backup set in real time respectively, or according to the set or real-time adjusted operating mode requirement, until the backup is finished; the backup data table device 12 continuously and sequentially locks each data table of the database and then backups the data table into the backup set; the generating device 13 generates log location information files continuously based on the execution location executed in the binary log corresponding to the stored data in each data table during locking and stores the log location information files in the backup set, so that a global large lock required during backup of the database can be removed, the influence of the backup on the whole database system is reduced, and the flexibility of physical hot standby of the database is effectively improved.

In the data table backup device 12, each data table in the database is locked and backed up in sequence, where the data information in the MyISAM table includes a MyISAM table index MYD, a MyISAM table structure frm, and MyISAM table data MYI. Of course, those skilled in the art should understand that the data information included in the MyISAM table described herein is merely a preferred embodiment of one aspect of the present application, and that the data information included in the MyISAM table, which may be present or later come, may be incorporated by reference herein, as applicable.

Further, the binary log backup device 11 uses an independent thread to copy the binary log in real time; and stopping the independent thread after all the data table backups are completed.

Table 1 backup thread for binary log binlog

Further, the backup data table device 12 is configured to: and (3) circularly executing: locking one of the data tables; acquiring an execution site of the binary log when the data table is locked; backing up the locked data table to the backup set; unlocking the locked data table after the backup is finished; until all the data tables are backed up.

In the embodiment of the present application, a specific execution flow of the backup data table device 12 is shown in fig. 2, that is, all steps S121 to S124 of the backup data table are embodied in the backup data table device 12. Where fig. 2 shows a schematic flow chart of a method for backing up all data tables according to a preferred embodiment of an aspect of the present application, fig. 2 includes the following steps: step S121, adding a table-level read lock to a single MyISAM table, and recording an execution site of a binary log binlog during the table-level read lock adding; step S122, backing up data information and binary logs in the MyISAM table, and generating a log site information file based on execution sites of the binary logs when the data table is locked; step S123, after the backup is finished, the single MyISAM table is unlocked from the table-level read lock; and step S124, judging whether all MyISAM tables are completely backed up, if so, finishing the backup, otherwise, returning to the step S121.

FIG. 6 illustrates an apparatus for data recovery of a database in accordance with another aspect of the subject application. The device 2 comprises acquisition means 21, copying means 22 and updating means 23.

The obtaining device 21 obtains a backup set generated in a physical hot standby stage, where the backup set includes the binary log copied in the physical hot standby stage, each data table of the database backed up after locking, and a log location information file generated based on the execution location where the stored data of each data table corresponds to the execution location in the binary log when locking; the copying device 22 copies all the data tables in the backup set to a recovery target path; the updating device 23 updates all the data tables in the restoration target path based on the binary log and the log site information file.

In the embodiment of the application, when the data tables in the backup set are restored, a binary log copied in a physical hot standby stage, a backup set generated by backing up each data table in a database after locking, and a log site information file generated based on an execution site executed in the binary log corresponding to data stored in each data table during locking are acquired, and corresponding updating is performed on all MyISAM tables in the backup set copied to a restoration path in sequence according to the binary log binlog and the log site information file from the execution site of the corresponding binary log binlog during table-level reading until all MyISAM tables in the backup set are updated to a data position consistent with the corresponding binary log site when the backup in the database is completed.

Further, the updating device 23 is configured to: and (3) circularly executing: a determining unit 231 (not shown) for determining a recovery execution site of the corresponding data table in the copied binary log based on an execution site to which data stored in the data table in the log site information file at the time of locking corresponds to the execution site in the binary log; a data table updating unit 232 (not shown) for obtaining all the execution events from the recovery execution site to the target execution site based on the copied binary log, and executing the execution event related to the corresponding data table therein to update the corresponding data table in the recovery target path; an update completing device 233 (not shown) until the update of all the data tables in the restoration target path is completed.

Preferably, in the data table updating unit 232 (not shown), the target execution site includes: and executing the execution site of the binary log when all the data tables are backed up. For example, in the above embodiment of the present application, at 9:13, after the backup of A, B, C MyISAM tables in the database is completed, the execution site 50 of the binary log corresponding to the last table of A, B, C MyISAM tables in the database when performing table-level read lock is the target execution site.

Specifically, in the determination unit 231 (not shown), if an a table of A, B, C three MyISAM tables is taken as a current table, an execution site 20 of the binary log at the time of locking corresponding to the a table is acquired from an a.info log site information file (mysql-bin.000001, 20) as a recovery execution site of the binary log binlog; then, in the data table updating unit 232 (not shown), updating the a table based on all execution event events corresponding to the a table from the execution site 20 when the table addition level read lock is executed to the target execution site 50 in the binary log binlog between the recovery execution site and the target execution site; further, in the update completing device 233 (not shown), the above-mentioned updating performed on the a table is repeatedly performed, and the B table and the C table are respectively updated correspondingly based on all the execution events until the updating of A, B, C three MyISAM tables in the restoration target path is completed.

Further, the update completing device 233 (not shown) is configured to: obtaining all execution events from the recovery execution site to the target execution site in the copied binary log, wherein the execution events have corresponding identifications with the data table; and circularly executing and reading one of the execution events, judging whether the corresponding data table is related to according to the corresponding identifier of the execution event, if so, updating the corresponding data table in the recovery target path according to the execution event, and if not, reading the next execution event until all the execution events from the recovery execution site to the target execution site are read.

Further, in the data table updating unit 232 (not shown), the execution event further includes an execution event type, execution data information, and an execution site. The types of execution events include, but are not limited to, an execution insert event, an execution delete event, and an execution update event, and any types of execution events that may occur in the present and future, such as may be applicable to the present application, may be included in the present application by reference.

Table 2 related information of all execution events in the backup process

In the embodiment of the present application, the specific execution flow in the device 2 is as shown in fig. 4, that is, all the steps S41 to S47 of restoring the backup set are implemented in the device 2. FIG. 4 is a flowchart of a method for restoring a backup set, according to a preferred embodiment of an aspect of the present application, wherein FIG. 4 includes the following steps:

step S41, reading the execution sites of the binary log binlog and all the execution event events in the binlog when adding the table level read lock corresponding to a MyISAM table in the backup set; step S42, reading one execution event in all execution event events in the binlog; step S43, determining whether the execution event is to update the MyISAM table, if yes, performing step S44, otherwise, performing step S42; step S44, updating the MyISAM table according to the execution event; step S45, judging whether there are any events in the binlog which are not read, if yes, executing step S42, otherwise executing step S46; step S46, completing the update of the MyISAM table; step S47, determine whether there is another MyISAM table that is not updated, if yes, execute step S41, otherwise, complete the update of the backup set.

Further, according to the method and the device for data recovery of the database in the embodiment of the present application, a backup set generated in a physical hot standby stage is obtained, where the backup set includes a binary log copied in the physical hot standby stage, a backup set generated by backing up each data table of the database after locking, and a log location information file generated based on an execution location where stored data of each data table corresponds to the execution location in the binary log when the data table is locked; copying all the data tables in the backup set to a recovery target path; and updating all the data tables in the recovery target path based on the binary log and the log site information file, so that when the database needs to perform data recovery on the data tables, the data recovery is performed on a single data table in sequence based on the binary log and all the execution events related to the backed-up data tables, and the flexibility of performing data recovery on the data tables is effectively improved on the premise of performing physical hot standby on the database.

It should be noted that the present application may be implemented in software and/or a combination of software and hardware, for example, implemented using Application Specific Integrated Circuits (ASICs), general purpose computers or any other similar hardware devices. In one embodiment, the software programs of the present application may be executed by a processor to implement the steps or functions described above. Likewise, the software programs (including associated data structures) of the present application may be stored in a computer readable recording medium, such as RAM memory, magnetic or optical drive or diskette and the like. Additionally, some of the steps or functions of the present application may be implemented in hardware, for example, as circuitry that cooperates with the processor to perform various steps or functions.

In addition, some of the present application may be implemented as a computer program product, such as computer program instructions, which when executed by a computer, may invoke or provide methods and/or techniques in accordance with the present application through the operation of the computer. Program instructions which invoke the methods of the present application may be stored on a fixed or removable recording medium and/or transmitted via a data stream on a broadcast or other signal-bearing medium and/or stored within a working memory of a computer device operating in accordance with the program instructions. An embodiment according to the present application comprises an apparatus comprising a memory for storing computer program instructions and a processor for executing the program instructions, wherein the computer program instructions, when executed by the processor, trigger the apparatus to perform a method and/or a solution according to the aforementioned embodiments of the present application.

It will be evident to those skilled in the art that the present application is not limited to the details of the foregoing illustrative embodiments, and that the present application may be embodied in other specific forms without departing from the spirit or essential attributes thereof. The present embodiments are therefore to be considered in all respects as illustrative and not restrictive, the scope of the application being indicated by the appended claims rather than by the foregoing description, and all changes which come within the meaning and range of equivalency of the claims are therefore intended to be embraced therein. Any reference sign in a claim should not be construed as limiting the claim concerned. Furthermore, it is obvious that the word "comprising" does not exclude other elements or steps, and the singular does not exclude the plural. A plurality of units or means recited in the apparatus claims may also be implemented by one unit or means in software or hardware. The terms first, second, etc. are used to denote names, but not any particular order.

Claims

1. A method for physical hot standby of a database, wherein the method comprises:

generating a log site information file corresponding to an execution site executed in the binary log based on the stored data of each data table during locking, and storing the log site information file into the backup set;

and locking each data table of the database in sequence and then backing up the data table to the backup set, wherein the method comprises the following steps of: locking one data table; acquiring an execution site of the binary log when the data table is locked; backing up the locked data table to the backup set; unlocking the locked data table after the backup is finished; until all the data tables are backed up.

2. The method of claim 1, wherein the copying the binary log into the backup set in real-time until the backup is completed comprises:

copying the binary log in real time by using an independent thread;

and stopping the independent thread after all the data table backups are completed.

3. The method of claim 1, wherein the backing up the locked data table comprises:

and backing up the data index, the data structure and the data content of the locked data table.

4. A method for data recovery of a database, wherein the method comprises:

the method comprises the following steps of obtaining a backup set generated in a physical hot standby stage, wherein the backup set is generated in the following mode: copying the binary log to a backup set in real time until the backup is finished; locking each data table of the database in sequence and then backing up the data table into the backup set; generating a log site information file corresponding to an execution site executed in the binary log based on the stored data of each data table during locking, and storing the log site information file into the backup set; and locking each data table of the database in sequence and then backing up the data table to the backup set, wherein the method comprises the following steps of: locking one data table; acquiring an execution site of the binary log when the data table is locked; backing up the locked data table to the backup set; unlocking the locked data table after the backup is finished; until all the data tables are backed up;

copying all the data tables in the backup set to a recovery target path;

5. The method of claim 4, wherein the updating all the data tables in the restoration target path based on the binary log and the log location information file comprises executing in a loop:

determining a recovery execution site of the corresponding data table in the copied binary log based on an execution site executed in the binary log by the data stored in the data table during locking in the log site information file;

acquiring all execution events from the recovery execution site to a target execution site based on the copied binary log, and executing the execution events related to the corresponding data table to update the corresponding data table in the recovery target path;

and updating all the data tables in the recovery target path until the data tables are updated.

6. The method of claim 5, the retrieving all execution events from the recovery execution site to a target execution site based on the copied binary log and executing the execution events related to the corresponding data table therein to update the corresponding data table in the recovery target path comprising:

obtaining all execution events from the recovery execution site to the target execution site in the copied binary log, wherein the execution events have corresponding identifications with the data table;

and circularly executing and reading one of the execution events, judging whether the corresponding data table is related to according to the corresponding identifier of the execution event, if so, updating the corresponding data table in the recovery target path according to the execution event, and if not, reading the next execution event until all the execution events from the recovery execution site to the target execution site are read.

7. The method of claim 6, wherein the performing an event further comprises:

execution event type, execution data information, and execution site.

8. The method of any of claims 5 to 7, wherein the target execution site is:

and executing the execution site of the binary log when all the data tables are backed up.

9. An apparatus for physical hot-standby of a database, wherein the apparatus comprises:

backup data table means for cyclically executing: locking one of the data tables; acquiring an execution site of the binary log when the data table is locked; backing up the locked data table to the backup set; unlocking the locked data table after the backup is finished; until all the data tables are backed up;

10. The apparatus of claim 9, wherein the backup binary log means is to:

copying the binary log in real time by using an independent thread;

11. The apparatus of claim 9, wherein the backup data table means is for:

12. An apparatus for data recovery of a database, wherein the apparatus comprises:

the acquisition device is used for acquiring the backup set generated in the physical hot standby stage, wherein the backup set is generated in the following way: copying the binary log to a backup set in real time until the backup is finished; locking each data table of the database in sequence and then backing up the data table into the backup set; generating a log site information file corresponding to an execution site executed in the binary log based on the stored data of each data table during locking, and storing the log site information file into the backup set; and locking each data table of the database in sequence and then backing up the data table to the backup set, wherein the method comprises the following steps of: locking one data table; acquiring an execution site of the binary log when the data table is locked; backing up the locked data table to the backup set; unlocking the locked data table after the backup is finished; until all the data tables are backed up;

13. The apparatus of claim 12, wherein the updating means is to:

and (3) circularly executing:

the determining unit is used for determining a recovery execution site of the corresponding data table in the copied binary log based on an execution site, executed in the binary log, of data stored in the data table in the log site information file during locking;

a data table updating unit, configured to obtain all execution events from the recovery execution site to a target execution site based on the copied binary log, and execute the execution event related to the corresponding data table therein to update the corresponding data table in the recovery target path;

and the updating completion device is used for completing the updating of all the data tables in the recovery target path.

14. The apparatus of claim 13, the data table update unit to:

15. The device of claim 14, wherein the execution event further comprises:

execution event type, execution data information, and execution site.

16. The apparatus of any of claims 13 to 15, wherein the target execution site is: