CN115994056A - Method and system for archiving and recovering relational database - Google Patents
Method and system for archiving and recovering relational database Download PDFInfo
- Publication number
- CN115994056A CN115994056A CN202310295562.2A CN202310295562A CN115994056A CN 115994056 A CN115994056 A CN 115994056A CN 202310295562 A CN202310295562 A CN 202310295562A CN 115994056 A CN115994056 A CN 115994056A
- Authority
- CN
- China
- Prior art keywords
- data
- archiving
- information
- archive
- recovery
- Prior art date
- Legal status (The legal status is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the status listed.)
- Granted
Links
Images
Classifications
-
- Y—GENERAL TAGGING OF NEW TECHNOLOGICAL DEVELOPMENTS; GENERAL TAGGING OF CROSS-SECTIONAL TECHNOLOGIES SPANNING OVER SEVERAL SECTIONS OF THE IPC; TECHNICAL SUBJECTS COVERED BY FORMER USPC CROSS-REFERENCE ART COLLECTIONS [XRACs] AND DIGESTS
- Y02—TECHNOLOGIES OR APPLICATIONS FOR MITIGATION OR ADAPTATION AGAINST CLIMATE CHANGE
- Y02D—CLIMATE CHANGE MITIGATION TECHNOLOGIES IN INFORMATION AND COMMUNICATION TECHNOLOGIES [ICT], I.E. INFORMATION AND COMMUNICATION TECHNOLOGIES AIMING AT THE REDUCTION OF THEIR OWN ENERGY USE
- Y02D10/00—Energy efficient computing, e.g. low power processors, power management or thermal management
Landscapes
- Information Retrieval, Db Structures And Fs Structures Therefor (AREA)
Abstract
The invention relates to the field of data processing, and discloses a method and a system for archiving and recovering a relational database, wherein the method comprises the following steps: the archiving method comprises a timing triggering archiving method and a manual triggering archiving method; the restoring method is used for restoring the archived data to the original position; the recovery method comprises the following steps: step 1, a WEB platform selects an archiving task; step 2, the WEB platform manually inputs query SQL; step 3, previewing archive data to be restored on a WEB platform; step 4, selecting whether to confirm the recovery data; if yes, the next step is carried out; if not, returning to the previous step; step 5, starting to recover the data; step 6, judging whether recovery is successful or not; if yes, the recovery is completed; if not, recording error DATA information, and returning to the step 5; according to the invention, by designing the archiving method and the recovery method, the historical data of the relational database can be quickly archived and recovered, and the matched data records can be calculated across a plurality of archiving files so as to quickly perform reverse recovery.
Description
Technical Field
The invention relates to archiving and recovering of data, in particular to a method and a system for archiving and recovering a relational database.
Background
Along with the development of socioeconomic performance, the socioeconomic production mode has been developed to digitization and intellectualization, and the relational database is widely used in the digitization application system of each industry. The digital application is expanded along with the accumulation of users and time, the history data is expanded gradually, the stability of the application is obviously affected by the gradually expanded history data, the maintenance cost and the complexity of the application are increased rapidly, the current common practice is to write script filing data respectively to form an SQL script file, or to file by utilizing the characteristics of each relational database.
Therefore, an archiving and recovering system generally applicable to each relational database is needed, and in the process of actual digital application operation, an operation of quickly recovering an archiving task is often needed, and part of data in archiving is often needed to be flexibly selected for filtering, inquiring and recovering.
In the existing archiving method, quick partial selective recovery is often not performed, a series of operations are often required by technicians, and the method is implemented by personnel with higher level programming expertise and higher labor cost.
The existing method can increase the cost by times with the increase of the implementation quantity, which is unfavorable for benign development and implementation of the system.
Disclosure of Invention
The invention aims to: a method and system for archiving and recovering relational databases are provided to solve the above problems in the prior art.
The technical scheme is as follows: a relational database archiving and retrieval method comprising:
the archiving method comprises a timing triggering archiving method and a manual triggering archiving method;
the restoring method is used for restoring the archived data to the original position;
the recovery method comprises the following steps:
step 1, a WEB platform selects a plurality of archiving tasks;
step 2, the WEB platform manually inputs query SQL;
step 3, previewing archive data to be restored on a WEB platform;
step 4, selecting whether to confirm the recovery data; if yes, the next step is carried out; if not, returning to the previous step;
step 5, starting to recover the data;
step 6, judging whether recovery is successful or not;
if yes, the recovery is completed;
if not, recording error DATA information, and returning to the step 5;
step 2, calculating matched data records across a plurality of archive files by manually inputting SQL query sentences;
step 5, the calculation module reads the archived data in an NIO mode and restores the matched data record to the original position;
by designing the archiving method and the recovering method, the historical data of the relational database can be quickly archived and recovered, meanwhile, matched data records are calculated through manual SQL query statement, reverse recovery is quickly carried out on archiving tasks, more importantly, multiple archiving tasks are supported, quick selection recovery is carried out through multiple dimensions, the operation and maintenance cost of the database is reduced, and the implementation efficiency is improved.
In a further embodiment, the timing triggered archiving method comprises:
step 110, a timing module triggers and starts at regular time;
step 120, reading corresponding archiving rules, wherein the archiving rules are preset;
step 130, the storage module extracts the related metadata information through JDBC;
step 140, the storage module extracts and stores the archive DATA through JDBC;
step 150, judging whether the record archiving is successful;
the archiving is completed;
if not, recording error DATA information, and returning to the step 4;
step 4, writing metadata information and archiving DATA information in a NIO mode;
by triggering the archiving task at regular time, the archiving task can be automatically completed according to the preset archiving rule, and when the data requiring special archiving is needed to be archived, the proposal can be adopted, so that the archiving task can be rapidly and conveniently completed, manual archiving is not needed, and the archiving cost and time are greatly reduced.
In a further embodiment, the manually triggered archiving method comprises:
step 210, editing archive rules by a WEB platform;
220, manually selecting rules by a WEB platform, and immediately archiving;
step 230, the storage module extracts the related metadata information through JDBC;
step 240, the storage module extracts and stores the archive DATA through JDBC;
step 250, judging whether the record archiving is successful;
the archiving is completed;
if not, recording error DATA information, and returning to the step 4;
step 4, writing metadata information and archiving DATA information in a NIO mode;
when some data need special archiving needs, can edit required archiving rules through the WEB platform, edit the rules according to own needs for archiving the rule more diversified has increased the maneuverability of archiving simultaneously, makes the data after archiving laminate the demand itself more.
A relational database archiving and retrieval system, comprising:
the data end comprises a relational database and is internally provided with historical data to be archived;
the processing end is used for archiving the data in the data end according to the archiving task, compressing the archived data to the storage end for storage, and simultaneously recovering one or more of the archived data in the storage end to the data end;
and the storage end is used for storing the archived data.
In a further embodiment, the processing end includes:
the archive rule module is used for managing and maintaining configuration information related to archive, such as archive aimed database connection Schema information, table data range conditions required to be archived aiming at which tables, archive triggering time, archive file stored catalogues and other information, and has multiple latitudes.
The WEB platform is used for changing relevant configuration information of the archiving rule module, a user carries out relevant configuration through the WEB platform, provides an archiving data preview function and carries out basic foolproof on the configuration information;
the timing module is used for triggering preset archiving tasks at fixed time according to the configuration information;
the storage module is used for writing metadata information and archive data, and compressing the data information and the archive data to the storage end for storage according to an archive task;
the computing module comprises a computing API interface and supports SQL statement direct call, and a computing engine is arranged in the computing module and is an implementation of the built-in API computing interface;
the computing module maintains a metadata cache, and metadata supports multi-version management;
the computing module supports the capability of executing computation across archiving tasks, can calculate matched data records by setting a lookup table, inquiring information such as filtering conditions (supporting a JDBC interface and inquiring through SQL sentences), and restore the data to a formal database, wherein the computing time is related to the size of the inquired target archiving task file, if the file is too large, the whole computing time is time-consuming and resource-consuming, so that the computing module supports a preview function, can selectively search a plurality of records for previewing, execute a recovery task after previewing has no problem, and continuously read the archiving file through NIO during the recovery task to recover the matched records.
In a further embodiment, the storage end uses a disk array to store data, so that the storage end has data redundancy capability and prevents single-point failure of data storage.
In a further embodiment, the storage module uses the NIO approach to write metadata information and archive DATA information, in such a way that metadata information and archive DATA information can be efficiently written.
The beneficial effects are that: the invention discloses a method and a system for archiving and recovering a relational database, which can rapidly archive and recover historical data of the relational database by designing an archiving method and a recovering method, calculate matched data records by manually inputting SQL query sentences, rapidly recover the archiving tasks, and more importantly support across multiple archiving tasks, rapidly select and recover the archiving tasks through multiple dimensions, reduce the operation and maintenance cost of the database and improve the implementation efficiency.
Drawings
FIG. 1 is a schematic diagram of a recovery method of the present invention.
Fig. 2 is a schematic diagram of a time triggered archiving method of the present invention.
Fig. 3 is a schematic diagram of a manually triggered archiving method of the present invention.
Fig. 4 is a schematic diagram of the system architecture of the present invention.
Detailed Description
The present application relates to a method and a system for archiving and recovering a relational database, and is explained in detail below through specific embodiments.
A method for archiving and recovering a relational database, as shown in fig. 1, comprising:
the archiving method comprises a timing triggering archiving method and a manual triggering archiving method;
the restoring method is used for restoring the archived data to the original position;
the recovery method comprises the following steps:
step 1, a WEB platform selects an archiving task;
step 2, the WEB platform manually inputs query SQL;
step 3, previewing archive data to be restored on a WEB platform;
step 4, selecting whether to confirm the recovery data; if yes, the next step is carried out; if not, returning to the previous step;
step 5, starting to recover the data;
step 6, judging whether recovery is successful or not;
if yes, the recovery is completed;
if not, recording error DATA information, and returning to the step 5;
step 2, inquiring information such as filtering conditions and the like by setting a lookup table, and calculating matched data records;
the method comprises the steps of calling a calculation engine aiming at an archive file to calculate a matching result through an SQL sentence input by a WEB platform, returning the WEB platform for previewing data to be restored, and enabling the calculation engine to support a standard JDBC interface and calculate aiming at the SQL sentence;
step 5, the calculation module reads the archived data in an NIO mode and restores the matched data record to the original position;
the calculation module reads the archived data in NIO mode, calculates and matches the data set to be recovered, and inserts the data set into the relational database circularly through JDBC, if the single line data is abnormal and reported wrong, records the wrong data and wrong information, and continues to execute circularly until the whole data set is inserted.
The traditional recovery method mainly comprises the steps of recovering the whole archive file, if partial data in the archive file is selectively recovered or partial data crossing a plurality of archive files is recovered, a DBA is often required to perform a series of operations, and direct calculation and search based on the archive data are not supported;
by designing the archiving method and the recovering method, the historical data of the relational database can be quickly archived and recovered, meanwhile, the query table is designed, the filtering condition information is queried, the matched data record is calculated, reverse recovery is quickly carried out on archiving tasks, more importantly, multiple archiving tasks are supported, quick selection and recovery are carried out through multiple dimensions, the operation and maintenance cost of the database is reduced, and the implementation efficiency is improved.
As shown in fig. 2, the method for timing-triggered archiving includes:
step 110, a timing module triggers and starts at regular time;
step 120, reading corresponding archiving rules, wherein the archiving rules are preset;
step 130, the storage module extracts the related metadata information through JDBC;
step 140, the storage module extracts and stores the archive DATA through JDBC;
step 150, judging whether the record archiving is successful;
the archiving is completed;
if not, recording error DATA information, and returning to the step 4;
and step 4, writing metadata information and archiving DATA information in a NIO mode.
As shown in fig. 3, the manual triggering archiving method includes:
step 210, editing archive rules by a WEB platform;
220, manually selecting rules by a WEB platform, and immediately archiving;
step 230, the storage module extracts the related metadata information through JDBC;
step 240, the storage module extracts and stores the archive DATA through JDBC;
step 250, judging whether the record archiving is successful;
the archiving is completed;
if not, recording error DATA information, and returning to the step 4;
and step 4, writing metadata information and archiving DATA information in a NIO mode.
A relational database archiving and retrieval system, as shown in fig. 4, comprising:
the data terminal comprises a relational database, wherein historical data to be archived is built in the relational database, and the relational database comprisesOracle、mysql、mssqlThe database system and the platform;
the processing end is used for archiving the data in the data end according to the archiving task, compressing the archived data to the storage end for storage, and simultaneously recovering one or more of the archived data in the storage end to the data end;
and the storage end is used for storing the archived data.
The processing end comprises:
the archiving rule module is used for managing and maintaining configuration information related to archiving, such as database connection Schema information for archiving, table data range conditions for which tables need to be archived, archiving triggering time, catalogues stored in archiving files and the like;
the WEB platform is used for changing relevant configuration information of the archiving rule module, a user carries out relevant configuration through the WEB platform, provides an archiving data preview function and carries out basic foolproof on the configuration information;
the timing module is used for triggering preset archiving tasks at fixed time according to the configuration information;
the storage module is used for writing metadata information and archive information, and compressing the data information and archive information to the storage end for storage according to an archive task;
the computing module comprises a computing API interface and supports SQL statement direct call, and a computing engine is arranged in the computing module and is an implementation of the built-in API computing interface;
the process by which the calculation engine performs the calculation is as follows:
step 310, analyzing SQL sentences;
step 320, loading an archive file in an external storage according to the table name information in the FROM clause and matching with metadata, and inquiring detail data related to the table;
step 330, aiming at the list result set, processing filtering conditions in a streaming mode, wherein the final data set is a calculation result, and finally, the data set is restored to the database;
the computing module maintains a metadata cache, and metadata supports multi-version management;
the computing module supports the capability of executing computation across archiving tasks, can calculate matched data records by setting information such as a lookup table, filtering conditions and the like (supporting JDBC interfaces and inquiring through SQL sentences), and restore the data to a formal database, wherein the computing time is related to the size of a file of the inquired target archiving task, if the file is too large, the whole computing time is time-consuming and resource-consuming, so that the computing module supports a preview function, can selectively search a plurality of records for previewing, execute a recovery task after previewing has no problem, and continuously read the archiving file through NIO during the recovery task to recover the matched records.
The storage end uses the disk array to store data, has the data redundancy capability, and prevents single-point faults of data storage.
The storage module writes the metadata information and archival DATA information using the NIO approach, by which the metadata information and archival DATA information can be efficiently written.
Description of working principle:
an automatic triggering archiving method comprises the following steps: the method comprises the steps that a timing module is triggered and started at regular time, corresponding archiving rules are read, the archiving rules are preset, a storage module extracts relevant metadata information through JDBC, the storage module extracts archiving DATA through JDBC and stores the archiving DATA, and the metadata information and the archiving DATA information are written in by using an NIO mode to judge whether archiving is recorded successfully or not;
the archiving is completed;
and if not, recording error DATA information, and extracting and storing the archive DATA by the storage module through JDBC, and writing the metadata information and the archive DATA information by using an NIO mode.
Manually triggered archiving method: the WEB platform edits the archiving rule, the WEB manually selects the rule, the immediately archiving storage module extracts relevant metadata information through the JDBC, the storage module extracts archiving DATA through the JDBC and stores the archiving DATA, and the metadata information and the archiving DATA information are written in by using an NIO mode to judge whether the archiving is recorded successfully or not;
the archiving is completed;
and if not, recording error DATA information, and extracting and storing the archive DATA by the storage module through JDBC, and writing the metadata information and the archive DATA information by using an NIO mode.
The recovery method comprises the following steps: the WEB platform selects an archiving task, manually inputs and inquires SQL, inquires filtering condition information by setting a query table, calculates matched data records, previews archiving data to be restored on the WEB platform, and selects whether to confirm restoring data;
the method comprises the steps that data recovery is started, a computing module reads archive data in an NIO mode, and matched data records are recovered to an original position;
if not, previewing the archive data to be restored on the WEB platform;
finally judging whether the recovery is successful or not;
if yes, the recovery is completed;
if not, the error DATA information is recorded and the recovery of the DATA is restarted.
The preferred embodiments of the present invention have been described in detail above with reference to the accompanying drawings, but the present invention is not limited to the specific details of the embodiments, and various equivalent changes can be made to the technical solutions of the present invention within the scope of the technical concept of the present invention, and all such equivalent changes belong to the protection scope of the present invention.
Claims (2)
1. A relational database archiving and retrieval method comprising:
the archiving method comprises a timing triggering archiving method and a manual triggering archiving method;
the restoring method is used for restoring the archived data to the original position;
the recovery method is characterized by comprising the following steps:
step 1, a WEB platform selects an archiving task;
step 2, the WEB platform manually inputs query SQL;
step 3, previewing archive data to be restored on a WEB platform;
step 4, selecting whether to confirm the recovery data; if yes, the next step is carried out; if not, returning to the previous step;
step 5, starting to recover the data;
step 6, judging whether recovery is successful or not;
if yes, the recovery is completed;
if not, recording error DATA information, and returning to the step 5;
step 2, calculating matched data records across a plurality of archive files by manually inputting SQL query sentences;
the method comprises the steps of calling a calculation engine aiming at an archive file to calculate a matching result through an SQL sentence input by a WEB platform, returning the WEB platform for previewing data to be restored, and enabling the calculation engine to support a standard JDBC interface and calculate aiming at the SQL sentence;
step 5, the calculation module reads the archived data in an NIO mode and restores the matched data record to the original position;
the method comprises the steps of reading archive data through an NIO mode by a computing module, computing and matching a data set to be recovered through the computing module, circularly inserting the data set into a relational database through JDBC, recording error data and error information if single-row data are abnormally reported to be wrong, and continuously circularly executing until the whole data set is inserted;
the timing triggering archiving method comprises the following steps:
step 110, a timing module triggers and starts at regular time;
step 120, reading corresponding archiving rules, wherein the archiving rules are preset;
step 130, the storage module extracts the related metadata information through JDBC;
step 140, the storage module extracts and stores the archive DATA through JDBC;
step 150, judging whether the record archiving is successful;
the archiving is completed;
if not, recording error DATA information, and returning to the step 4;
step 4, writing metadata information and archiving DATA information in a NIO mode;
the manual triggering archiving method comprises the following steps:
step 210, editing archive rules by a WEB platform;
220, manually selecting rules by a WEB platform, and immediately archiving;
step 230, the storage module extracts the related metadata information through JDBC;
step 240, the storage module extracts and stores the archive DATA through JDBC;
step 250, judging whether the record archiving is successful;
the archiving is completed;
if not, recording error DATA information, and returning to the step 4;
and step 4, writing metadata information and archiving DATA information in a NIO mode.
2. A system based on a relational database archiving and retrieval method in accordance with claim 1, comprising:
the data end comprises a relational database and is internally provided with historical data to be archived;
the processing end is used for archiving the data in the data end according to the archiving task, compressing the archived data to the storage end for storage, and simultaneously recovering one or more of the archived data in the storage end to the data end;
the storage end is used for storing the archived data;
the processing end comprises:
an archiving rule module for managing and maintaining configuration information related to archiving;
the WEB platform is used for changing the relevant configuration information of the archiving rule module;
the timing module is used for triggering preset archiving tasks at fixed time according to the configuration information;
the storage module is used for writing metadata information and archive files, and compressing the metadata information and the archive files to the storage end for storage according to the archive tasks;
the computing module comprises a computing API interface and supports SQL statement direct call, and a computing engine is arranged in the computing module and is an implementation of the built-in API computing interface;
the process by which the calculation engine performs the calculation is as follows:
step 310, analyzing SQL sentences;
step 320, loading an archive file in an external storage according to the table name information in the FROM clause and matching with metadata, and inquiring detail data related to the table;
step 330, aiming at the list result set, processing filtering conditions in a streaming mode, wherein the final data set is a calculation result, and finally, the data set is restored to the database;
the storage end uses a disk array to store data;
the storage module writes metadata information and archives DATA information using the NIO approach.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202310295562.2A CN115994056B (en) | 2023-03-24 | 2023-03-24 | Method and system for archiving and recovering relational database |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202310295562.2A CN115994056B (en) | 2023-03-24 | 2023-03-24 | Method and system for archiving and recovering relational database |
Publications (2)
| Publication Number | Publication Date |
|---|---|
| CN115994056A true CN115994056A (en) | 2023-04-21 |
| CN115994056B CN115994056B (en) | 2023-06-13 |
Family
ID=85995460
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN202310295562.2A Active CN115994056B (en) | 2023-03-24 | 2023-03-24 | Method and system for archiving and recovering relational database |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN115994056B (en) |
Citations (3)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN107391306A (en) * | 2017-07-27 | 2017-11-24 | 国家电网公司 | A kind of isomeric data library backup file access pattern method |
| US11226878B1 (en) * | 2021-03-02 | 2022-01-18 | International Business Machines Corporation | Accelerator-based database recovery |
| CN115237852A (en) * | 2022-07-28 | 2022-10-25 | 广州赛意信息科技股份有限公司 | Method, system, medium and device for archiving and restoring data based on MES system |
-
2023
- 2023-03-24 CN CN202310295562.2A patent/CN115994056B/en active Active
Patent Citations (3)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN107391306A (en) * | 2017-07-27 | 2017-11-24 | 国家电网公司 | A kind of isomeric data library backup file access pattern method |
| US11226878B1 (en) * | 2021-03-02 | 2022-01-18 | International Business Machines Corporation | Accelerator-based database recovery |
| CN115237852A (en) * | 2022-07-28 | 2022-10-25 | 广州赛意信息科技股份有限公司 | Method, system, medium and device for archiving and restoring data based on MES system |
Also Published As
| Publication number | Publication date |
|---|---|
| CN115994056B (en) | 2023-06-13 |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| CN101127034B (en) | Data organization, inquiry, presentation, documentation, recovery, deletion, refining method, device and system | |
| US5893119A (en) | Computer apparatus and method for merging system deltas | |
| US20060004890A1 (en) | Methods and systems for providing directory services for file systems | |
| US11409616B2 (en) | Recovery of in-memory databases after a system crash | |
| CN102982067A (en) | Traversing data in a repeatable manner | |
| CN104239443A (en) | Serialization data operation log storage method | |
| MXPA04010351A (en) | Data archive recovery. | |
| CN111400101B (en) | Data recovery method and system for deleting JFS2 file system data | |
| CN115994056B (en) | Method and system for archiving and recovering relational database | |
| CN1904881A (en) | Method for searching database filing data | |
| CN110866068B (en) | Advertisement data storage method and device based on HDFS | |
| CN113419897A (en) | File processing method and device, electronic equipment and storage medium thereof | |
| CN115374065B (en) | File cleaning method and system based on cloud platform log record monitoring | |
| CN113821476B (en) | Data processing method and device | |
| CN112559533B (en) | Archiving method and device of continuous database and electronic equipment | |
| US20030204516A1 (en) | Determination of relevant figures for database recovery processes | |
| JP3957464B2 (en) | Data update device | |
| KR20030063620A (en) | Method for recovering a damaged database data and computer readable medium storing thereof | |
| CN114063935B (en) | Method and device for processing data | |
| JPH0991182A (en) | Method and device for managing data base | |
| CN112612648B (en) | SQL Server database recovery method, terminal equipment and storage medium | |
| KR102724878B1 (en) | Automatic compression method and computer program for backup data based on the created list | |
| CN112463764B (en) | Method and system for online migration of database | |
| JP2001175519A (en) | Method and device for managing file and storage medium with program for file management stored thereon | |
| CN118363794A (en) | Method for flashing back recovery data according to time stamp |
Legal Events
| Date | Code | Title | Description |
|---|---|---|---|
| PB01 | Publication | ||
| PB01 | Publication | ||
| SE01 | Entry into force of request for substantive examination | ||
| SE01 | Entry into force of request for substantive examination | ||
| GR01 | Patent grant | ||
| GR01 | Patent grant |