CN106802895B

CN106802895B - Method and device for repairing database backup data

Info

Publication number: CN106802895B
Application number: CN201510837138.1A
Authority: CN
Inventors: 刘良友; 田英鹤; 赵建伟; 林晓斌
Original assignee: Alibaba Group Holding Ltd
Current assignee: Alibaba China Co Ltd
Priority date: 2015-11-26
Filing date: 2015-11-26
Publication date: 2020-06-02
Anticipated expiration: 2035-11-26
Also published as: CN106802895A

Abstract

The method comprises the steps that whether execution log site information of a main library is consistent with execution log site information of a standby library or not is compared in real time, and when the execution log site information of the main library is consistent with the execution log site information of the standby library, readable and unwritable locking processing is carried out on the main library; comparing whether the execution log site information of the master library and the execution log site information of the standby library are consistent or not when the locking processing of the master library is finished in real time, and stopping the copying work of the standby library from the master library when the execution log site information of the standby library is consistent; requesting the master library to backup data to be repaired, and unlocking the master library after the backup of the data to be repaired is completed; and acquiring the data to be repaired, repairing the standby database based on the data to be repaired, and starting the copying work of the standby database from the master database after the repairing is finished. The aim that data can be repaired without suspending service in a repair process within seconds and the accurate and safe repair effect are achieved in a cloud computing scene.

Description

Method and device for repairing database backup data

Technical Field

The application relates to the field of computers, in particular to a technology for repairing database backup data.

Background

With the rapid development of database technology, the application of the database is very wide and the database is deep into various fields. In a database system, a standby database is often established for a database instance main database for the purposes of disaster recovery, read-write separation and the like, and data inconsistency between the main database and the standby database may be caused due to the reasons that a host computer is down, the standby database is inadvertently set to be writable, the design of the database is defective or the manual operation is improper, and the like.

At present, in order to repair inconsistent data segments, many users have their own repair methods, and some vendors provide a script tool for repair, for example, compare each row in a data block (chunk) of a main library and a spare library, and for a found main inconsistent row and a spare inconsistent row, execute a loop in a data list (repalce intro) to generate a full execution log (binlog) of the row in the main library and synchronize to the spare library, which repairs the spare library based on data of the main library, insert a replacement (replace) on the main library for a row existing in the main library but not existing in the spare library, and delete the row existing in the spare library but not existing in the main library until all inconsistent rows in the data block are repaired.

On the one hand, in the whole data block repair process, a lock (for update lock) of the data block needs to be held, and the larger the delay of the standby bank is, the longer the lock time of the main bank is, and the larger the influence on the line is. In a cloud computing scene, a large number of users who do not know specific service scenes and use habits are faced, and errors which are difficult to make up can be caused by adopting the tool. On the other hand, there is also the following risk: if the data has messy codes, the repairing action can not complete repairing, and even the data can be repaired wrongly; the repaired table must have a unique primary key, otherwise the repaired row cannot be accurately located, which may cause a repair object error, either in the form of replacement (replace intro) error data or deletion (delete) error data, to be irretrievable. In addition, under the cloud computing condition, once the main-standby relationship changes, the restoration performed by the existing script tool directly causes a data source error.

Disclosure of Invention

The application aims to provide a method and equipment for repairing database backup data, and the technical problem to be solved is how to accurately and safely repair user data when user services are not suspended in a cloud computing scene.

According to one aspect of the application, a method for repairing database backup data is provided, which comprises the following steps:

comparing whether the execution log site information of the master library is consistent with the execution log site information of the standby library in real time, and if so, performing readable and unwritable locking processing on the master library;

comparing whether the execution log site information of the master library and the execution log site information of the standby library are consistent or not when the locking processing of the master library is finished in real time, and stopping the copying work of the standby library from the master library when the execution log site information of the standby library is consistent;

requesting the master library to backup data to be repaired, and unlocking the master library after the backup of the data to be repaired is completed;

and acquiring the data to be repaired, repairing the standby database based on the data to be repaired, and starting the copying work of the standby database from the master database after the repairing is finished.

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

the locking device is used for comparing whether the execution log site information of the master library is consistent with the execution log site information of the standby library in real time, and if so, performing readable and unwritable locking processing on the master library;

the backup library stopping device is used for comparing whether the execution log site information of the master library is consistent with the execution log site information of the backup library in real time when the locking processing is finished, and stopping the copying work of the backup library from the master library when the execution log site information of the master library is consistent with the execution log site information of the backup library;

the unlocking device is used for requesting the main library to backup the data to be repaired and unlocking the main library after the backup of the data to be repaired is completed;

and the repairing device is used for acquiring the data to be repaired, repairing the standby database based on the data to be repaired, and starting the copying work of the standby database from the main database after the repairing is finished.

Compared with the prior art, according to the method and the equipment provided by the embodiment of the application, when the execution log site information of the main library is consistent with the execution log site information of the standby library, the primary and standby synchronization is realized, and the readable and unwritable locking processing is performed on the main library; then, when the execution log site information of the standby library reaches the execution log site information of the main library when the locking processing is finished, the primary and standby secondary synchronization is realized, and the copying work of the standby library from the main library is stopped; subsequently, requesting the main library to backup data to be repaired, wherein the backed-up data is the data required by the standby library, and after the data to be repaired is backed up, unlocking the main library, so that a user can write data normally; and acquiring the data to be repaired, repairing the standby database based on the data to be repaired, repairing according to data blocks or data files, completing the repair without analyzing the data line by line, and then starting the copying work of the standby database from the main database. The repairing process is controlled within seconds, the influence on the online service is small, and the main and standby synchronization and the like enable the data required to be repaired on the repaired site, so that the accurate repairing effect is achieved; further, the locking time for performing readable and unwritable locking processing on the master library is recorded, and when the locking time exceeds the set timeout time, the current repairing operation is stopped, so that the condition of semi-repairing and semi-non-repairing does not exist, and the aim of repairing safety is fulfilled.

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 an apparatus for repairing database backup data according to an aspect of the subject application;

FIG. 2 illustrates a flow diagram of repairing database backup data in accordance with a preferred embodiment of an aspect of the subject application;

FIG. 3 shows a schematic structural view of a prosthetic device 14 according to yet another preferred embodiment of an aspect of the present application;

FIG. 4 illustrates a flow diagram of a method for repairing database backup data according to yet another aspect of the subject application;

fig. 5 shows a schematic flow chart of the method of step S14 in a preferred embodiment according to yet another aspect of the present 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 an apparatus for repairing database backup data according to an aspect of the subject application. The apparatus includes a locking device 11, a standby library stopping device 12, an unlocking device 13, and a repairing device 14.

The locking device 11 is used for comparing whether the execution log site information of the master library is consistent with the execution log site information of the standby library in real time, and performing readable and unwritable locking processing on the master library when the execution log site information of the master library is consistent with the execution log site information of the standby library; the standby library stopping device 12 compares whether the execution log location information of the main library is consistent with the execution log location information of the standby library when the locking processing is finished in real time, and stops the copying work of the standby library from the main library when the execution log location information of the main library is consistent with the execution log location information of the standby library; the unlocking device 13 requests the master library to backup the data to be repaired, and unlocks the master library after the backup of the data to be repaired is completed; the repair device 14 obtains the data to be repaired, repairs the backup library based on the data to be repaired, and after the repair is completed, starts the copy operation of the backup library from the master library.

Here, the device 1 includes, but is not limited to, a user 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, any mobile electronic product, such as a smart phone, a PDA, and the like, capable of human-computer interaction with a user through a touch panel, and the mobile electronic product 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 the user device, or a device formed by integrating the user device and a network device, a touch terminal, or a network device and a touch terminal through a network, where the device 1 may be a third-party device independent from the master library device and the standby library device, or may be a script program running on the master library device, the standby library device, or a part of the master library device and a part of the standby library device. 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.

In a database system, in order to solve a single-point problem and to achieve load balancing of data instances, a main library and one or more standby libraries for copying the main library are often established, only the main library can be written in, and only the standby libraries can be read. The principle of master-slave replication is often accomplished by the backup library replicating the execution log (binlog) of the master library and then executing. For the replication relation, various database products have respective implementation methods, taking mysql (a relational database management system) as an example, a mysql master library writes an execution log when a transaction is submitted, controls the execution log to be refreshed to a disk 'landing' through a synchronous execution log (sync _ binlog) parameter, a standby library reads the execution log from the master library through an input/output (io) thread and records the execution log into a local relay log (relay log), and the local sql (structured query language) thread applies data of the relay log to a local database. For reasons such as efficiency, the database only stores data in the memory, and does not actually write data to the disk. If the database response is "commit successful", but the modification to the database by the transaction "commit successful" is not effective due to any problems of the database hang, the operating system, the database host, and the like, we consider that the data of the transaction is lost. The method is unacceptable for scenes with high data consistency requirements, such as banking business or financial business, and therefore, ensuring that the data is not lost is also an important measurement standard for database selection. In addition, due to the factors of double writing, log file damage and the like, even if the system operates in a complete high-reliability working mode, the primary and secondary data inconsistency can still be caused.

The device 1 in the embodiment of the present application is used for repairing database backup database data, and because the primary database and the secondary database are temporarily locked after synchronization, by stopping the backup database, the backup of the data to be repaired in the primary database and the backup database acquire the backed-up data to repair the data, and repair is performed according to data blocks or data files, and the data can be repaired without analyzing the data line by line. The repairing process is controlled within seconds, under a cloud computing scene, data can be repaired without suspending service, and the data required to be repaired is repaired on the repaired site through twice synchronization of the main and standby devices, so that an accurate repairing effect is achieved; furthermore, the master library is subjected to timeout control when being locked, the current repairing operation is stopped when the time exceeds the set timeout time, the condition that the repair is semi-repaired and not semi-repaired can be avoided, and the aim of safe repair is fulfilled.

It should be noted that one master library corresponds to one or more backup libraries, and the master library and the backup libraries may be distributed on the same device or may be distributed on different devices.

Specifically, the locking device 11 is configured to compare whether the execution log location information of the master library and the execution log location information of the standby library are consistent in real time, and if so, perform readable and unwritable locking processing on the master library.

The synchronization is that the execution log site information of the main library is consistent with the execution log site information of the corresponding standby library, the site information of the main library and the standby library is compared in real time to realize the first-time synchronization, namely the acquired execution log site information of the main library is compared with the acquired site information of the execution log of the standby library in real time, and if the acquired execution log site information of the main library and the acquired site information of the execution log of the standby library are consistent, the first-time synchronization is performed on the main library and the standby; after the primary and secondary databases are synchronized for the first time, the primary and secondary databases are located at the same position, the primary database is locked to make the user unable to write in the short time, the locking time can be several seconds, the influence on the online service in the short time is very small, and at this time, the intermediate layer is used for blocking the structured query language of the user. The locking action of the master library is controlled in an overtime mode, and the control mode is that another thread is opened to wait for the overtime time, and the locking action is abandoned after the time is up, and the repair is abandoned.

Specifically, the backup library stopping means 12 compares, in real time, whether the execution log location information of the master library is consistent with the execution log location information of the backup library when the locking processing is completed, and stops the copy operation of the backup library from the master library when the execution log location information of the master library is consistent with the execution log location information of the backup library.

In a specific embodiment, taking mysql as an example, the current location information of the master library can be obtained by displaying state information (show master status) of a master library execution log file in the master library through a command, and then waiting for the backup library to copy to the location, the master library and the backup library can realize the second-time synchronization within a very short time after the first-time synchronization is realized, at this time, data of the master library is required for the backup library to repair the data, stopping the backup library means stopping the backup library from copying from the master library, and after stopping the backup library, the data required by the backup library can be obtained and can be repaired at a corresponding repair location.

Specifically, the unlocking device 13 requests the master library to backup the data to be repaired, and performs unlocking processing on the master library after the backup of the data to be repaired is completed.

In the previous example, after the standby library is stopped, the main library backs up data to be repaired, where the data to be repaired refers to corresponding data corresponding to a repair data block or data file required by the standby library, and the data block or data file to be repaired is backed up locally in the main library, and the backup time is very short, so that the locking of the main library is not available, and the user can write data normally by unlocking the main library.

Specifically, the repairing device 14 obtains the data to be repaired, repairs the spare library based on the data to be repaired, and after the repair is completed, starts the copy operation of the spare library from the master library.

In a preferred embodiment, after the data is backed up by the main library, the locally backed-up data is uploaded to the transfer station, and the standby library downloads a corresponding data block from the transfer station to be overwritten to the standby library or performs data file overwriting. The data is restored according to the data blocks or the data files, the problem of line-by-line analysis of the data is avoided, the backup library is started to copy from the main library after restoration is completed, and normal copying is started between the main library and the backup library.

FIG. 2 illustrates a flow diagram of repairing database backup data in accordance with a preferred embodiment of an aspect of the subject application. Wherein, the reference numerals 1-10 are the sequence of the flow chart, the Master is the Master library, and the Slave is the corresponding Slave library. The process is as follows: the locking device 11 locks the master library after the primary and standby synchronization; then, the standby library stopping device 12 stops the standby library after the primary and standby are synchronized for the second time; subsequently, the unlocking device 13 unlocks the master library after the master library backs up the data; finally, the repair device 14 uploads the backed-up data in the main library to the transfer station, the backup library downloads the repair data from the transfer station, and the backup library is opened after the repair data is completed.

Here, the database engine can be divided into two types, one is transaction-incapable and one is transaction-capable, where for the transaction-incapable type, each table has a separate data file and index file, the most secure way to repair is to overwrite the data file, and for the transaction-capable type, transaction-capable and row-level lock are supported. Therefore, for the engine which does not support the transaction, the safe repair mode is to cover the data file, and for the engine which supports the transaction, the repair can be performed by using two execution statements (including deleting the data block and importing the backup data) in one transaction. The repair process is described below for a preferred embodiment for repairing database backup data for two engine types.

For the data engine type of the database being a transaction-unsupported type, the unlocking device 13 in the apparatus 1 includes a backup unit, as shown in fig. 3, and the repairing device 14 in the apparatus 1 includes: a table locking unit 141, an obtaining unit 142, an unlocking unit 143, and a table repairing unit 144; the backup unit 131 requests the master library to backup the data files and the index files of the data to be repaired; the lock table unit 141 performs lock table processing on the standby library; the obtaining unit 142 obtains the data to be repaired backed up from the master library; the unlocking unit 143 unlocks the standby library; the repair table unit 144 performs a repair table operation based on the data to be repaired.

Further, in the apparatus 1, the unlocking means 13 includes: and the backup unit is used for requesting the master library to backup the data files and the index files of the data to be repaired.

Here, the data engine type of the database is a transaction-unsupported type, each table has a separate data file and index file, and the safest way when repairing is to be the overwriting of the data file, so that the master database backs up the data file and the index file of the data to be repaired when backing up the data. It should be noted that the Myisam engine has an own additional repair (repair table) action, backups the data file of the data to be repaired when the primary library backs up, and the backup library executes the additional repair (repair table) action to reconstruct the index data of the repaired data.

Specifically, in the apparatus 1, the repair device 14 includes: the table locking unit 141 performs table locking processing on the standby library; the obtaining unit 142 obtains the data to be repaired backed up from the master library; the unlocking unit 143 unlocks the standby library; the repair table unit 144 performs a repair table operation based on the data to be repaired.

For example, the Mysiam engine does not support the transaction, and when locking the master library, the Mysiam engine is represented as a lock table, that is, the whole table is directly locked, after data to be repaired in the master library is backed up and the backed-up data is uploaded to the transfer station in the repair process, because an additional repair (repair table) action is performed on the backup library in the repair process of the Mysiam engine, in order to prevent a user or a database administrator (dba) from performing the additional repair (repair table) action to influence the repair, the table locking operation needs to be performed on the backup library. The backup database can upload the backed-up data in the main database to the transfer station, and then download the backup data from the transfer station. The backup library unlocks the backup library after acquiring the backup data to be repaired, executes an additional repair (replay table) action, reconstructs index data of the repaired data and the like, ensures that the original data file in the backup library is completely covered, and acquires a new and correct data file. It should be noted that the Myisam engine can execute an additional repair (repair table) action, only data files need to be backed up when the primary library is backed up, the index file can be reconstructed when the backup library executes the additional repair action, for other engines which do not support transactions and do not have the additional repair action, the data files and the index files are backed up when the primary library is backed up, and the backup library performs file coverage to repair the table by acquiring the backed up data files and index files. Further, for example, the Myisam engine does not support transactions, and Myisam is a table-level lock in each file in which there is a separation in the Myisam table, directly locking the entire table, and during the locking period, no other process can write to the table. In addition, the early work flow for repairing Myisam is as follows: firstly, checking whether the repair is needed, including checking the relation between the addresses (ip) and ports (port) of the main and standby libraries to determine that the main and standby switching mechanism is not changed, checking the existence of database files (db) and data lists (table) of the main and standby libraries to determine that a certain document and a corresponding table in a certain database are complete, checking the change of data engines of the main and standby libraries, and the like to determine whether the repair is needed; if data restoration is needed, comparing data blocks in the main library with corresponding data blocks in the standby libraries in a synchronous state of the main library and the standby libraries, checking whether the data blocks are consistent, if the data blocks are not consistent, closing a main-standby switching function (HA), stopping disaster recovery, and preventing main-standby relations from changing in a restoration process, if one main library corresponds to a plurality of standby libraries, in the restoration process, the relation between the main library and the standby libraries A changes, the standby libraries A are replaced by the standby libraries B, and sql executed by the main library is executed into the standby libraries B to cause data errors in the standby libraries B; then, whether the repair is performed or not is checked in the synchronous state of the main database and the standby database, if the repair is not performed, an additional repair (repair table) action is executed on the standby database, and the repair can be performed only by avoiding locking a table for the data which can be repaired by the repair table. And after the repair table operation of the standby database is finished, comparing whether the execution log site information of the main database is consistent with the execution log site information of the standby database in real time, and performing subsequent repair processes.

As for the previous example, a repair process of database backup database data, which takes a Myisam engine as an example, is a preferred embodiment of the present application, and first, the relationship between addresses (ip) and ports (port) of a backup database, the existence of database files (db) and data lists (table), and the change of a data engine, etc. are checked; when no problem is detected, obtaining inconsistent data blocks from the main library for direct comparison under the condition that the copying of the main library and the standby library reaches the same site, if the comparison result is inconsistent, closing the main-standby switching function, and detecting whether the data between the main library and the standby library are consistent under the condition that the copying of the main library and the standby library reaches the same site, if the data between the main library and the standby library are inconsistent, Myisam needs to execute an additional repairing action (repair table) on the standby library, other databases do not execute the additional action, and whether the data needs to be repaired is re-detected, if the data needs to: in the locking device 11, the execution log site information of the master library and the execution log site information of the standby library are obtained, the two site information are compared, if the two site information are the same, the locking device 11 performs locking processing on the master library, and if the engine does not support the transaction, the engine is the lock table, and the whole table is directly locked. In the backup library stopping device 12, when the point of the execution log of the backup library reaches the point of the execution log when the locking processing of the master library is completed, the backup library stops the copy operation from the master library, and at this time, the inconsistent data blocks obtained on the master library and the backup library can be repaired at the point corresponding to the data to be repaired. The unlocking device 13 is used for requesting the master library to backup the data to be repaired, the master library performs local backup on the data file after acquiring the request, and the master library performs unlocking processing after the backup is completed. In the repair apparatus 14, the data to be repaired is acquired by uploading the backed-up data to the transfer station by the main library, downloading the backed-up data from the transfer station by the backup library, then, executing an operation of reconstructing index data of the repaired data by the backup library, and after the repair is completed, starting the backup library to enable the backup library to be normally copied from the main library. And finally, opening the main/standby switching function and recovering the normal state of the database.

Those skilled in the art should understand that the database backup database data repair process of Myisam is a preferred embodiment of the present application, and the method for repairing the database backup database data when the type of the data engine of the database is a non-supported transaction described in detail in the present application, and of course, any existing or future database suitable for the repair process of the present application may be included in the present application by reference.

For the data engine type of the database is a transaction-supporting type, in the apparatus 1 of the present application, the unlocking device 13 includes a backup unit, and the repairing device 14 includes an import unit; the backup unit is used for requesting the main library to perform logic backup on the data block where the data to be repaired is located; the import unit is configured to delete the transaction data to be repaired in the standby library, and import the data block in which the data to be repaired logically backed up by the main library is located into the standby library.

And taking the data engine type of the database as a support transaction type as an example to describe the database backup data repair process in detail.

Specifically, the unlocking device 13 includes: and the backup unit requests the main library to perform logic backup on the data block where the data to be repaired is located.

In a preferred embodiment, the local backup of the master library is a logical backup of a data block in which data to be repaired is located, wherein the logical backup is only an image of data content in the original database; after the logical backup, the main library unlocks the locked data blocks before unlocking, uploads the data to be repaired which is locally backed up to the transfer station, and the standby library downloads the backup from the transfer station.

Specifically, the prosthetic device 14 further includes: and the import unit deletes the transaction data to be repaired in the standby library and imports the data block where the data to be repaired logically backed up by the main library is located into the standby library.

In the previous example, the backup is downloaded from the transfer station and then the data of the backup is repaired by the downloaded logical backup, and the repairing action is to delete the original data and import the data of the backup of the main library into the backup of the backup database in the same transaction.

Further, Innodb is an engine of the Mysql database, for example, that supports transaction and row-level locks, where row-level locks refer to locking only specified records, and other processes can still operate on records of the same table. Innodb stores its tables and indexes in a tablespace, which may contain several files or raw disk partitions. In addition, the early-stage workflow for repairing Innodb is as follows: firstly, checking whether the repair is needed, including checking the relation between the addresses (ip) and ports (port) of the main and standby libraries to determine that the main and standby switching mechanism is not changed, checking the existence of database files (db) and data lists (table) of the main and standby libraries to determine that a certain document and a corresponding table in a certain database are complete, checking the change of data engines of the main and standby libraries, and the like to determine whether the repair is needed; if data is needed to be repaired, comparing data blocks in the main library with corresponding data blocks in the standby libraries in a synchronous state of the main library and the standby libraries, checking whether the data blocks are consistent, if the data blocks are not consistent, closing the HA function of main-standby switching, stopping disaster recovery, and preventing the main-standby relationship from changing in the repairing process. And after the repair table operation of the standby database is finished, comparing whether the execution log site information of the main database is consistent with the execution log site information of the standby database in real time, and performing subsequent repair processes.

As for the previous example, a repair process of database backup database data, which is exemplified by an Innodb engine, is another preferred embodiment of the present application, first, the relationship between addresses (ip) and ports (port) of the backup database, the existence of database files (db) and data lists (table), and the change of the data engine are checked; when no problem is detected, obtaining inconsistent data blocks from the main library for direct comparison under the condition that the copying of the main library and the standby library reaches the same position, if the comparison result is inconsistent, closing the main-standby switching function, and detecting whether the data between the main library and the standby library are consistent under the condition that the copying of the main library and the standby library reaches the same position, if the data between the main library and the standby library are inconsistent, executing the following repairing process: in the locking device 11, the execution log site information of the master library and the execution log site information of the standby library are obtained, the two site information are compared, if the two site information are the same, the locking device 11 performs locking processing on the master library, and when the engine supports the transaction, the engine is the locked data block and directly locks the specified data block. In the backup library stopping device 12, when the point of the execution log of the backup library reaches the point of the execution log when the locking processing of the master library is completed, the backup library stops the copy operation from the master library, and at this time, the inconsistent data blocks obtained on the master library and the backup library can be repaired at the point corresponding to the data to be repaired. The unlocking device 13 is configured to request the master library to backup data to be repaired, the master library performs logical backup on the data blocks after acquiring the request, and after the backup is completed, the master library performs unlocking processing, that is, the data blocks are unlocked. In the repair device 14, the data to be repaired is obtained by uploading the backed-up data to the transfer station by the main library, downloading the backed-up data from the transfer station by the backup library, then repairing the backup database by the downloaded logical backup, deleting the original data and importing the data backed-up by the main library into the backup library in the same transaction, and after the repair is completed, opening the backup library to enable the backup from the main library to be normally performed. And finally, opening the main/standby switching function and recovering the normal state of the database.

It should be understood by those skilled in the art that the repair process of the database backup data in the above innardb is another preferred embodiment of the present application, and the method for repairing the database backup data when the data engine type of the database is a support transaction described in the present application is described in detail, and of course, databases suitable for the repair process of the present application, which may exist now or may appear in the future, may be included in the present application by reference.

It should be noted that the repair method when the data engine type of the database is the non-transaction type is the file overlay in the preferred embodiment, but those skilled in the art should understand that the repair method that is not the transaction type may also perform repair according to the repair flow that supports transactions, for example, the main library backup is a logical backup, and the backup database data repair is performed by deleting data blocks and importing backup data, but the method for performing file overlay has higher security.

Preferably, the device 1 further comprises: a closing device (not shown) for closing the active-standby switching function between the primary library and the standby library before the real-time comparison result indicates whether the execution log location information of the primary library and the execution log location information of the standby library are consistent; and a standby library opening device (not shown) configured to obtain the data to be repaired, repair the standby library based on the data to be repaired, and open a main/standby switching function between the main library and the standby library after the repair is completed.

Before comparing whether the execution log location information of the main library and the execution log location information of the standby library are consistent or not in real time, the main-standby switching function (HA) needs to be closed, a user writes data into the main library, and the HA is switched to the corresponding standby library when the main library HAs a certain error, so that the condition that the user writes data into the standby library in the repairing process is avoided by closing the HA function. After the repair is completed, the main and standby HA functions need to be opened, so that the main and standby HA can be normally switched.

Preferably, the device 1 further comprises: and the recording device (not shown) is used for recording the locked time of the readable and non-writable locking processing of the master library, and stopping the current repairing operation when the locked time exceeds the set timeout time.

In one embodiment, the master library needs to have timeout control when performing the readable and non-writable locking processing operation, and the control mode may wait for the timeout time by another open thread, and abandon the locking action and abandon the repair.

It will be understood by those skilled in the art that the control timeout method is merely exemplary, and other existing or future control timeout methods may be applied to the present invention, and are included within the scope of the present invention and are incorporated herein by reference.

Further, compared with the prior art, according to the embodiment of the application, in the process of the repair process, each line in the main and standby data blocks is not compared line by executing the sum check code, and the repair of the standby database data of the database is realized by covering or importing the data block through the data file, so that the problem that the accurate data repair can be completed without analyzing the data block line by line under the condition that the repaired object is wrong when the repaired table does not have a unique main key is solved.

FIG. 4 is a flow chart illustrating a method for repairing backup data of a database according to yet another aspect of the present application. The method includes step S11, step S12, step S13, and step S14.

In step S11, comparing whether the execution log location information of the master library and the execution log location information of the backup library are consistent, and if so, performing readable and unwritable locking processing on the master library; in step S12, comparing in real time whether the execution log location information of the master library and the execution log location information of the standby library are consistent when the locking process is completed, and if so, stopping the copy operation of the standby library from the master library; in step S13, requesting the master library to backup data to be repaired, and unlocking the master library after the backup of the data to be repaired is completed; in step S14, the data to be repaired is acquired, the backup library is repaired based on the data to be repaired, and after the repair is completed, the copy operation of the backup library from the master library is started.

The method according to another embodiment of the present application is used for repairing database backup database data, and after two times of synchronization between the main database and the backup database, the main database is locked briefly, and the backup of the data to be repaired in the main database and the backup database are stopped to obtain the backed-up data for repairing the data, and the data is repaired according to data blocks or data files without analyzing the data line by line, so that the repair can be completed. The repairing process is controlled within seconds, under a cloud computing scene, data can be repaired without suspending service, and the data required to be repaired is repaired on the repaired site through twice synchronization of the main and standby devices, so that an accurate repairing effect is achieved; furthermore, the master library is subjected to timeout control when being locked, the current repairing operation is stopped when the time exceeds the set timeout time, the condition that the repair is semi-repaired and not semi-repaired can be avoided, and the aim of safe repair is fulfilled.

Specifically, step S11: and comparing whether the execution log site information of the main library is consistent with the execution log site information of the standby library in real time, and if so, performing readable and unwritable locking processing on the main library.

The synchronization is that the execution log site information of the main library is consistent with the execution log site information of the corresponding standby library, the site information of the main library and the standby library is compared in real time to realize the first-time synchronization, namely the acquired execution log site information of the main library is compared with the acquired site information of the execution log of the standby library in real time, and if the acquired execution log site information of the main library and the acquired site information of the execution log of the standby library are consistent, the first-time synchronization is performed on the main library and the standby; after the primary and secondary databases are synchronized for the first time, the primary and secondary databases are located at the same position, the primary database is locked to make the user unable to write in the short time, the locking time can be several seconds, the influence on the online service in the short time is very small, and at this time, the structured query language (sql) of the user is blocked by the intermediate layer. The locking action of the master library is controlled in an overtime mode, and the control mode is that another thread is opened to wait for the overtime time, and the locking action is abandoned after the time is up, and the repair is abandoned.

Specifically, step S12: and comparing whether the execution log site information of the master library and the execution log site information of the standby library are consistent when the locking processing of the master library is finished in real time, and stopping the copying work of the standby library from the master library when the execution log site information of the master library and the execution log site information of the standby library are consistent.

Specifically, step S13: requesting the master library to backup data to be repaired, and unlocking the master library after the backup of the data to be repaired is completed.

Specifically, step S14: and acquiring the data to be repaired, repairing the standby database based on the data to be repaired, and starting the copying work of the standby database from the master database after the repairing is finished.

FIG. 2 illustrates a flow diagram of repairing database backup data in accordance with a preferred embodiment of an aspect of the subject application. Wherein, the reference numerals 1-10 are the sequence of the flow chart, the Master is the Master library, and the Slave is the corresponding Slave library. The process is as follows: step S11, locking the master library after the primary and standby synchronization; next, in step S12, the backup library is stopped after the primary and backup synchronization is completed for the second time; subsequently, in step S13, the master library is unlocked after the master library backs up the data; finally, in step S14, the backed-up data in the main library is uploaded to the transfer station, the backup library downloads the repair data from the transfer station, and the backup library is opened after the repair data is completed.

For the data engine type of the database is a transaction-unsupported type, step S13 in the method of the present application requests the master library to backup the data file and the index file of the data to be repaired; as shown in fig. 5, the step S14 includes: step S141, step S142, step S143, and step S144, wherein in step S141, lock table processing is performed on the standby library; in step S142, obtaining the data to be repaired backed up from the master library; in step S143, performing an unlocking process on the standby library; the repair table unit 144 performs a repair table operation based on the data to be repaired.

Specifically, requesting the master library to backup the data to be repaired includes: and requesting the master library to backup the data file and the index file of the data to be repaired.

Specifically, the acquiring the data to be repaired includes: step S141, locking the table of the standby library; step S142, obtaining the data to be repaired backed up from the master library; step S143, unlocking the standby database; and step S144, performing repair table operation based on the data to be repaired.

For example, the Mysiam engine does not support the transaction, and when locking the master library, the Mysiam engine is represented as a lock table, that is, the whole table is directly locked, after data to be repaired in the master library is backed up and the backed-up data is uploaded to the transfer station in the repair process, because an additional repair (repair table) action is performed on the backup library in the repair process of the Mysiam engine, in order to prevent a user or a database administrator (dba) from performing the additional repair (repair table) action to influence the repair, the table locking operation needs to be performed on the backup library. The backup database can upload the backed-up data in the main database to the transfer station, and then download the backup data from the transfer station. The backup library unlocks the backup library after acquiring the backup data to be repaired, executes an additional repair (replay table) action, reconstructs index data of the repaired data and the like, ensures that the original data file in the backup library is completely covered, and acquires a new and correct data file.

It should be noted that the Mysiam engine can execute an additional repair (repair table) action, only data files need to be backed up when the primary library is backed up, the index file can be reconstructed when the backup library executes the additional repair action, for other engines which do not support transactions and do not have the additional repair action, the data files and the index files are backed up when the primary library is backed up, and the backup library performs file coverage to repair the table by acquiring the backed up data files and index files.

Further, for example, the Myisam engine does not support transactions, and Myisam is a table-level lock in each file in which there is a separation in the Myisam table, directly locking the entire table, and during the locking period, no other process can write to the table. In addition, the early work flow for repairing Myisam is as follows: firstly, checking whether the repair is needed, including checking the relation between the addresses (ip) and ports (port) of the main and standby libraries to determine that the main and standby switching mechanism is not changed, checking the existence of database files (db) and data lists (table) of the main and standby libraries to determine that a certain document and a corresponding table in a certain database are complete, checking the change of data engines of the main and standby libraries, and the like to determine whether the repair is needed; if data is needed to be repaired, comparing data blocks in the main library with corresponding data blocks in the standby libraries in a synchronous state of the main library and the standby libraries, checking whether the data blocks are consistent, if the data blocks are not consistent, closing a main-standby switching function (HA), stopping disaster recovery, and preventing main-standby relations from changing in the repairing process, if one main library corresponds to a plurality of standby libraries, in the repairing process, the relation between the main library and the standby libraries A changes, the standby libraries A are replaced by the standby libraries B, and structured query statements (sql) executed by the main library are executed to the standby libraries B, so that data errors in the standby libraries B are caused; next, whether the repair is performed or not is checked in the synchronous state of the main and standby databases, if the repair is not performed, an additional repair (repair table) action is executed on the standby database, and the data that can be repaired by the additional repair (repair table) action can be repaired without performing table locking. And after the repair table operation of the standby database is finished, comparing whether the execution log site information of the main database is consistent with the execution log site information of the standby database in real time, and performing subsequent repair processes.

As for the previous example, a repair process of database backup database data, which takes a Myisam engine as an example, is a preferred embodiment of the present application, and first, the relationship between addresses (ip) and ports (port) of a backup database, the existence of data files (db) and data lists (table), and the change of a data engine are checked; when no problem is detected, obtaining inconsistent data blocks from the main library for direct comparison under the condition that the copying of the main library and the standby library reaches the same site, if the comparison result is inconsistent, closing the main-standby switching function, and detecting whether the data between the main library and the standby library are consistent under the condition that the copying of the main library and the standby library reaches the same site, if the data between the main library and the standby library are inconsistent, Myisam needs to execute an additional repairing action (repair table) on the standby library, other databases do not execute the additional action, and whether the data needs to be repaired is re-detected, if the data needs to: in step S11, the execution log location information of the master library and the execution log location information of the standby library are obtained, the two location information are compared, and if the two location information are the same, the master library is locked in step S11, and the engine is the lock table when the engine does not support the transaction, and the entire table is directly locked. In step S12, when the location of the execution log of the backup library reaches the location of the execution log when the locking processing is completed by the primary library, the backup library stops the copy operation from the primary library, and at this time, the inconsistent data blocks obtained from the primary library and the backup library may be repaired at the location corresponding to the data that needs to be repaired. In step S13, the master library is requested to backup data to be repaired, the master library performs local backup on the data file after acquiring the request, and after the backup is completed, the master library performs unlocking processing. In step S14, the data to be repaired is obtained by uploading the backed-up data to the transfer station by the main library, downloading the backed-up data from the transfer station by the backup library, then, the backup library performs an operation of reconstructing the index data of the repaired data, and after the repair is completed, the backup library is opened to enable the backup library to be copied from the main library normally. And finally, opening the main/standby switching function and recovering the normal state of the database.

For the data engine type of the database being a transaction-supporting type, in the method of the present application, the step S13 further includes: requesting the main library to perform logic backup on the data block where the data to be repaired is located; the method also comprises the following steps in step 14: and deleting the transaction data to be repaired in the standby library, and importing the data block where the data to be repaired logically backed up by the main library is located into the standby library.

Specifically, the backing up the data to be repaired by the master library further includes: and requesting the main library to perform logic backup on the data block where the data to be repaired is located.

Specifically, repairing the library based on the data to be repaired further includes: and deleting the transaction data to be repaired in the standby library, and importing the data block where the data to be repaired logically backed up by the main library is located into the standby library.

Further, Innodb is an engine of the Mysql database, for example, that supports transaction and row-level locks, where row-level locks refer to locking only specified records, and other processes can still operate on records of the same table. Innodb stores its tables and indexes in a tablespace, which may contain several files or raw disk partitions. In addition, the early-stage workflow for repairing Innodb is as follows: firstly, checking whether the repair is needed, including checking the relation between the addresses (ip) and ports (port) of the main and standby libraries to determine that the main and standby switching mechanism is not changed, checking the existence of database files (db) and data lists (table) of the main and standby libraries to determine that a certain document and a corresponding table in a certain database are complete, checking the change of data engines of the main and standby libraries, and the like to determine whether the repair is needed; if data is needed to be repaired, comparing data blocks in the main library with corresponding data blocks in the standby libraries in a synchronous state of the main library and the standby libraries, checking whether the data blocks are consistent, if the data blocks are not consistent, closing the HA function of main-standby switching, stopping disaster recovery, and preventing main-standby relations from changing in the repairing process. And after the repair table operation of the standby database is finished, comparing whether the execution log site information of the main database is consistent with the execution log site information of the standby database in real time, and performing subsequent repair processes.

As for the previous example, a repair process of database backup database data, which takes an inbb engine as an example, is another preferred embodiment of the present application, and first, the relationship between addresses and ports of the backup database, the existence of data files and data lists, and the change of the data engine are checked; when no problem is detected, obtaining inconsistent data blocks from the main library for direct comparison under the condition that the copying of the main library and the standby library reaches the same position, if the comparison result is inconsistent, closing the main-standby switching function, and detecting whether the data between the main library and the standby library are consistent under the condition that the copying of the main library and the standby library reaches the same position, if the data between the main library and the standby library are inconsistent, executing the following repairing process: in step S11, the execution log location information of the master library and the execution log location information of the backup library are obtained, the two location information are compared, if the two location information are the same, the master library is locked, and the engine directly locks the specified data block when supporting the transaction, that is, the engine is the locked data block. In step S12, when the location of the execution log of the backup library reaches the location of the execution log when the locking processing is completed by the primary library, the backup library stops the copy operation from the primary library, and at this time, the inconsistent data blocks obtained from the primary library and the backup library may be repaired at the location corresponding to the data that needs to be repaired. In step S13, the master library is requested to backup the data to be repaired, the master library performs logical backup on the data blocks after acquiring the request, and after the backup is completed, the master library performs unlocking processing, that is, unlocks the data blocks. In step S14, the data to be repaired is obtained by uploading the backed-up data to the transfer station by the primary library, downloading the backed-up data from the transfer station by the backup library, then repairing the backup database by the downloaded logical backup, the repairing action is to delete the original data and import the backed-up data of the primary library into the backup database in the same transaction, and after the repair is completed, the backup library is opened to enable the copy from the primary library to be performed normally. And finally, opening the main/standby switching function and recovering the normal state of the database.

Preferably, the method further comprises: before the executing log site information of the main library and the executing log site information of the standby library are compared to be consistent, closing the main-standby switching function between the main library and the standby library; and after the data to be repaired is obtained, repairing the standby database based on the data to be repaired, and after the repairing is finished, opening a main-standby switching function between the main database and the standby database.

Preferably, the method further comprises: and recording the locking time of the readable and non-writable locking processing of the master library, and stopping the current repairing operation when the locking time exceeds the set timeout time.

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 repairing database backup data, wherein the method comprises:

2. The method of claim 1, wherein the data engine type of the database is a non-transaction supported type.

3. The method of claim 2, wherein requesting the master library to backup data to be repaired comprises:

and requesting the master library to backup the data file and the index file of the data to be repaired.

4. The method of claim 2, wherein obtaining the data to be repaired comprises:

locking the table of the standby database;

acquiring data to be repaired backed up from the master library;

carrying out unlocking processing on the standby database;

and performing repair table operation based on the data to be repaired.

5. The method of claim 1, wherein the data engine type of the database is a support transaction type.

6. The method of claim 5, wherein requesting the master library to backup data to be repaired comprises: requesting the main library to perform logic backup on the data block where the data to be repaired is located;

repairing the standby database based on the data to be repaired further comprises: and deleting the transaction data to be repaired in the standby library, and importing the data block where the data to be repaired logically backed up by the main library is located into the standby library.

7. The method of any of claims 1-6, wherein the method further comprises:

before the executing log site information of the main library and the executing log site information of the standby library are compared to be consistent, closing the main-standby switching function between the main library and the standby library;

and after the data to be repaired is obtained, repairing the standby library based on the data to be repaired, and after the repairing is finished, opening a copy switching function between the main library and the standby library.

8. The method of claim 7, wherein the method further comprises:

and recording the locking time of the readable and non-writable locking processing of the master library, and stopping the current repairing operation when the locking time exceeds the set timeout time.

9. An apparatus for repairing database backup data, wherein the apparatus comprises:

10. The apparatus of claim 9, wherein the data engine type of the database is a non-transaction supported type.

11. The apparatus of claim 10, wherein the unlocking device comprises:

and the backup unit is used for requesting the master library to backup the data files and the index files of the data to be repaired.

12. The apparatus according to claim 10 or 11, wherein the repair device comprises:

the table locking unit is used for carrying out table locking processing on the standby database;

the acquisition unit is used for acquiring the data to be repaired backed up from the master library;

the unlocking unit is used for unlocking the standby warehouse;

and the repair table unit is used for performing repair table operation based on the data to be repaired.

13. The apparatus of claim 9, wherein the data engine type of the database is a support transaction type.

14. The apparatus of claim 13, wherein the unlocking device comprises: the backup unit is used for requesting the main library to perform logic backup on the data block where the data to be repaired is located;

the repair device further includes: and the importing unit is used for deleting the transaction data to be repaired in the standby library and importing the data block where the data to be repaired logically backed up by the main library is located into the standby library.

15. The apparatus of claim 9, wherein the apparatus further comprises:

a closing device, configured to close a master-slave switching function between the master library and the slave library before the real-time comparison result indicates whether the execution log location information of the master library and the execution log location information of the slave library are consistent;

and the standby library starting device is used for acquiring the data to be repaired, repairing the standby library based on the data to be repaired, and opening a main-standby switching function between the main library and the standby library after the repair is finished.

16. The apparatus of claim 9, wherein the apparatus further comprises:

and the recording device is used for recording the locking time of the readable and non-writable locking processing of the master library, and stopping the current repair operation when the locking time exceeds the set timeout time.