CN110928899B - Universal database backup method and system - Google Patents
Universal database backup method and system Download PDFInfo
- Publication number
- CN110928899B CN110928899B CN201911196247.4A CN201911196247A CN110928899B CN 110928899 B CN110928899 B CN 110928899B CN 201911196247 A CN201911196247 A CN 201911196247A CN 110928899 B CN110928899 B CN 110928899B
- Authority
- CN
- China
- Prior art keywords
- database
- field
- names
- backup
- types
- 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.)
- Active
Links
Images
Classifications
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/24—Querying
- G06F16/242—Query formulation
- G06F16/2433—Query languages
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F11/00—Error detection; Error correction; Monitoring
- G06F11/07—Responding to the occurrence of a fault, e.g. fault tolerance
- G06F11/14—Error detection or correction of the data by redundancy in operation
- G06F11/1402—Saving, restoring, recovering or retrying
- G06F11/1446—Point-in-time backing up or restoration of persistent data
- G06F11/1448—Management of the data involved in backup or backup restore
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/24—Querying
- G06F16/245—Query processing
- G06F16/2455—Query execution
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F21/00—Security arrangements for protecting computers, components thereof, programs or data against unauthorised activity
- G06F21/60—Protecting data
- G06F21/602—Providing cryptographic facilities or services
-
- 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
- Engineering & Computer Science (AREA)
- Theoretical Computer Science (AREA)
- Physics & Mathematics (AREA)
- General Physics & Mathematics (AREA)
- General Engineering & Computer Science (AREA)
- Data Mining & Analysis (AREA)
- Computational Linguistics (AREA)
- Databases & Information Systems (AREA)
- Software Systems (AREA)
- Computer Security & Cryptography (AREA)
- Computer Hardware Design (AREA)
- General Health & Medical Sciences (AREA)
- Bioethics (AREA)
- Health & Medical Sciences (AREA)
- Quality & Reliability (AREA)
- Mathematical Physics (AREA)
- Storage Device Security (AREA)
- Information Retrieval, Db Structures And Fs Structures Therefor (AREA)
Abstract
The invention provides a general database backup method and system, which inquires a corresponding system table according to database types, reads table names needing backup, reads all field types and field values according to the table names, splices the table names into corresponding sentences, performs merging optimization on the sentences, compresses character strings and encrypts the character strings, and realizes data backup of different database types. The invention does not depend on the installation environment of the database, does not depend on the specific database type and version, has high code compatibility, can encrypt the backup file content of the database, ensures the safety of backup data, compresses the file content by adopting the GZIP compression technology, has higher compression ratio, and can greatly save the disk space of a server.
Description
Technical Field
The present invention relates to the field of database technologies, and in particular, to a method and a system for backing up a general database.
Background
In order to ensure the reliability of the application system and prevent the situation of data loss caused by database damage, the data of the application system needs to be backed up regularly.
The common database backup operation needs to rely on the installation environment of the database, the operation is performed by utilizing the backup command of the database, the compatibility of the corresponding application service and the scene of the database service deployed on different servers is not good enough, the backup file is not compressed and encrypted, the safety is not good enough, and the backup file is not easy to store.
Therefore, a general database backup method is needed to improve the compatibility of different servers.
Disclosure of Invention
The invention aims to provide a universal database backup method and system, which aim to solve the problems of dependence on installation environment and low safety of database backup in the prior art, realize independence on the installation environment of the database, improve code compatibility and ensure the safety of backup data.
In order to achieve the technical purpose, the invention provides a universal database backup method, which comprises the following steps:
s1, when a backup instruction is received, inquiring a corresponding system table according to the type of a database, and reading all table names to be backed up according to the system table;
s2, inquiring all field names, field types and field values in the table according to the table names;
s3, splicing the field names, the field types and the field values into corresponding sql sentences, and merging the sql sentences of the same type;
s4, compressing the generated sql statement, performing base64 encryption on the compressed byte stream to generate a character string, and writing the compressed and encrypted character string into a file.
Preferably, the system table includes:
the mysql database is information_schema.tabs, the dream database is all_object, and the Jin Cang database is sys_class.
Preferably, the look-up table is completed by a ResultSet object according to all field names, field types and field values in the table name.
Preferably, the compressing of the generated sql statement is accomplished by GZIP string compression technique.
The invention also provides a universal database backup system, which comprises:
the table name acquisition module is used for inquiring a corresponding system table according to the database type when receiving a backup instruction, and reading all table names needing to be backed up according to the system table;
the field acquisition module is used for inquiring all field names, field types and field values in the table according to the table names;
the sentence splicing module is used for splicing the field names, the field types and the field values into corresponding sql sentences and merging the sql sentences of the same type;
the compression encryption module is used for compressing the generated sql statement, generating a character string after base64 encryption of the compressed byte stream, and writing the compressed and encrypted character string into a file.
Preferably, the system table includes:
the mysql database is information_schema.tabs, the dream database is all_object, and the Jin Cang database is sys_class.
Preferably, the look-up table is completed by a ResultSet object according to all field names, field types and field values in the table name.
Preferably, the compressing of the generated sql statement is accomplished by GZIP string compression technique.
The effects provided in the summary of the invention are merely effects of embodiments, not all effects of the invention, and one of the above technical solutions has the following advantages or beneficial effects:
compared with the prior art, the method and the device have the advantages that the corresponding system table is queried according to the database types, the table names needing to be backed up are read, all field types and field values are read according to the table names and spliced into corresponding sentences, the sentences are combined and optimized, the character strings are compressed and encrypted, and data backup of different database types is realized. The invention does not depend on the installation environment of the database, does not depend on the specific database type and version, has high code compatibility, can encrypt the backup file content of the database, ensures the safety of backup data, compresses the file content by adopting the GZIP compression technology, has higher compression ratio, and can greatly save the disk space of a server.
Drawings
FIG. 1 is a flowchart of a general database backup method according to an embodiment of the present invention;
fig. 2 is a block diagram of a general database backup system according to an embodiment of the present invention.
Detailed Description
In order to clearly illustrate the technical features of the present solution, the present invention will be described in detail below with reference to the following detailed description and the accompanying drawings. The following disclosure provides many different embodiments, or examples, for implementing different structures of the invention. In order to simplify the present disclosure, components and arrangements of specific examples are described below. Furthermore, the present invention may repeat reference numerals and/or letters in the various examples. This repetition is for the purpose of simplicity and clarity and does not in itself dictate a relationship between the various embodiments and/or configurations discussed. It should be noted that the components illustrated in the figures are not necessarily drawn to scale. Descriptions of well-known components and processing techniques and processes are omitted so as to not unnecessarily obscure the present invention.
The following describes a general database backup method and system provided by the embodiment of the invention in detail with reference to the accompanying drawings.
As shown in fig. 1, the embodiment of the invention discloses a universal database backup method, which comprises the following steps:
s1, when a backup instruction is received, inquiring a corresponding system table according to the type of a database, and reading all table names to be backed up according to the system table;
s2, inquiring all field names, field types and field values in the table according to the table names;
s3, splicing the field names, the field types and the field values into corresponding sql sentences, and merging the sql sentences of the same type;
s4, compressing the generated sql statement, performing base64 encryption on the compressed byte stream to generate a character string, and writing the compressed and encrypted character string into a file.
The database backup mode in the embodiment of the invention does not depend on the installation environment of the database, can be used for different server scenes, and encrypts and compresses the backup content so as to realize the safety and easy storage of the database.
When receiving the backup command, the corresponding system table is queried according to the database type, for example, information_schema. Tabs under mysql database, all_object under dream database, sys_class under Jin Cang database, etc., and ALL table names needing backup are read. And according to all field types and field values in the table name lookup table, completing the table name lookup by executing the sql statement, using the sql statement to lookup the table name, placing the result in a resultantly set object, and using the inherent attribute of the resultantly set object to acquire all field names, field types and field values in the table. Creating a data table, deleting data, inserting data and other sql sentences according to the table name, the field name and the field value, for example, the table name is a table name, the field name is column na, the type is varchar, the content is "hello", the field name column b type is int, the content is 1, and creating the data table sentence: create table tablename (columnA varchar, columnB int); generating a deleted data statement: delete from tablename; generating an insert data statement: insert into tablename (columnA, columnB) values ('hello', 1).
Combining and optimizing the sentences, if each data write one inserted data sentence, for example:
insert into tablename (columnA, columnB) values ('hello', 1);
insert into tablename (columnA, columnB) values ('hello 2', 2);
the above mode occupies more space and can be combined into the following modes:
insert into tablename (columnA, columnB) values ('hello', 1) ('hello 2', 2);
by merging sql statements of the same type, the size of the string is reduced.
And compressing the generated sql statement by using a GZIP character string compression technology, and performing base64 encryption on the compressed byte stream to generate a character string. If the byte stream is not encrypted by base64, the character string is directly constructed, so that the situation of Chinese messy codes can occur after the character string is decompressed. Writing the compressed and encrypted character string into the file.
When the database is restored according to the backup file, the file content is read out row by row, decrypted and decompressed, and then the parsed sql statement is executed.
Through verification, under the situation of millions of data, the backup mode of mysql primary database is used, the size of the backup file is about 100M, and by using the backup mode of the invention, the backup file is about 10M, so that the space is saved by 9 times.
The embodiment of the invention inquires the corresponding system table according to the database types, reads the table names needing to be backed up, reads all field types and field values according to the table names, splices the table names into corresponding sentences, performs merging optimization on the sentences, compresses character strings and encrypts the character strings, and realizes data backup of different database types. The invention does not depend on the installation environment of the database, does not depend on the specific database type and version, has high code compatibility, can encrypt the backup file content of the database, ensures the safety of backup data, compresses the file content by adopting the GZIP compression technology, has higher compression ratio, and can greatly save the disk space of a server.
As shown in fig. 2, the embodiment of the present invention further discloses a general database backup system, where the system includes:
the table name acquisition module is used for inquiring a corresponding system table according to the database type when receiving a backup instruction, and reading all table names needing to be backed up according to the system table;
the field acquisition module is used for inquiring all field names, field types and field values in the table according to the table names;
the sentence splicing module is used for splicing the field names, the field types and the field values into corresponding sql sentences and merging the sql sentences of the same type;
the compression encryption module is used for compressing the generated sql statement, generating a character string after base64 encryption of the compressed byte stream, and writing the compressed and encrypted character string into a file.
When receiving the backup instruction, the table name acquisition module queries a corresponding system table according to the database type, for example, information_schema.tabs under the mysql database, all_object under the dream database, sys_class under the Jin Cang database, and the like, and reads ALL table names needing to be backed up.
The field acquisition module is completed by executing the sql statement according to all field types and field values in the table name lookup table, the table name is queried by the sql statement, the result is placed in a result set object, and all field names, field types and field values in the table are acquired by utilizing the inherent attribute of the result set object.
The statement splicing module splices sql statements such as creating a data table, deleting data, inserting data and the like according to table names, field names and field values, for example, table names are table names, field names are column names, types are varchar, contents are "hello", field names are column names, types are int, contents are 1, and created data table statements are generated: create table tablename (columnA varchar, columnB int); generating a deleted data statement: delete from tablename; generating an insert data statement: insert into tablename (columnA, columnB) values ('hello', 1).
Combining and optimizing the sentences, if each data write one inserted data sentence, for example:
insert into tablename (columnA, columnB) values ('hello', 1);
insert into tablename (columnA, columnB) values ('hello 2', 2);
the above mode occupies more space and can be combined into the following modes:
insert into tablename (columnA, columnB) values ('hello', 1) ('hello 2', 2);
by merging sql statements of the same type, the size of the string is reduced.
The compression encryption module compresses the generated sql statement by using a GZIP character string compression technology, and generates a character string after base64 encryption of the compressed byte stream. If the byte stream is not encrypted by base64, the character string is directly constructed, so that the situation of Chinese messy codes can occur after the character string is decompressed. Writing the compressed and encrypted character string into the file.
When the database is restored according to the backup file, the file content is read out row by row, decrypted and decompressed, and then the parsed sql statement is executed.
The foregoing description of the preferred embodiments of the invention is not intended to be limiting, but rather is intended to cover all modifications, equivalents, and alternatives falling within the spirit and principles of the invention.
Claims (8)
1. A method for backing up a universal database, the method comprising the steps of:
s1, when a backup instruction is received, inquiring a corresponding system table according to the type of a database, and reading all table names to be backed up according to the system table;
s2, inquiring all field names, field types and field values in the table according to the table names;
s3, splicing the field names, the field types and the field values into corresponding sql sentences, and merging the sql sentences of the same type;
s4, compressing the generated sql statement, performing base64 encryption on the compressed byte stream to generate a character string, and writing the compressed and encrypted character string into a file.
2. The method of claim 1, wherein the system table comprises:
the mysql database is information_schema.tabs, the dream database is all_object, and the Jin Cang database is sys_class.
3. The method of claim 1, wherein the look-up table is completed by a ResultSet object based on all field names, field types, and field values in the table.
4. The method for backing up a general database according to claim 1, wherein the compressing of the generated sql statement is performed by GZIP string compression technique.
5. A universal database backup system, the system comprising:
the table name acquisition module is used for inquiring a corresponding system table according to the database type when receiving a backup instruction, and reading all table names needing to be backed up according to the system table;
the field acquisition module is used for inquiring all field names, field types and field values in the table according to the table names;
the sentence splicing module is used for splicing the field names, the field types and the field values into corresponding sql sentences and merging the sql sentences of the same type;
the compression encryption module is used for compressing the generated sql statement, generating a character string after base64 encryption of the compressed byte stream, and writing the compressed and encrypted character string into a file.
6. The universal database backup system of claim 5, wherein the system table comprises:
the mysql database is information_schema.tabs, the dream database is all_object, and the Jin Cang database is sys_class.
7. The universal database backup system according to claim 5 wherein said lookup table is completed by a ResultSet object based on all field names, field types, and field values in the table name lookup table.
8. The universal database backup system according to claim 5, wherein the compressing of the generated sql statement is accomplished by GZIP string compression techniques.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN201911196247.4A CN110928899B (en) | 2019-11-29 | 2019-11-29 | Universal database backup method and system |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN201911196247.4A CN110928899B (en) | 2019-11-29 | 2019-11-29 | Universal database backup method and system |
Publications (2)
| Publication Number | Publication Date |
|---|---|
| CN110928899A CN110928899A (en) | 2020-03-27 |
| CN110928899B true CN110928899B (en) | 2023-06-20 |
Family
ID=69847802
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN201911196247.4A Active CN110928899B (en) | 2019-11-29 | 2019-11-29 | Universal database backup method and system |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN110928899B (en) |
Families Citing this family (4)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN113760600B (en) * | 2020-08-05 | 2023-09-22 | 北京京东振世信息技术有限公司 | Database backup method, database restoration method and related devices |
| CN114820226A (en) * | 2021-01-19 | 2022-07-29 | 上海勘测设计研究院有限公司 | Hydropower station data acquisition and transmission method, receiving and storage method |
| CN113835929B (en) * | 2021-09-24 | 2024-04-30 | 深圳追一科技有限公司 | Data acquisition method and device, electronic equipment and storage medium |
| CN116107600B (en) * | 2023-04-13 | 2023-07-18 | 麒麟软件有限公司 | Oracle database installation tool and rapid deployment method thereof |
Citations (12)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN102063491A (en) * | 2010-12-30 | 2011-05-18 | 浪潮集团山东通用软件有限公司 | Data structure capable of realizing data back-up and restoration of different types of databases and realization method |
| CN102693174A (en) * | 2012-06-06 | 2012-09-26 | 武汉大学 | Object broker database constraint-oriented backup method |
| CN103218402A (en) * | 2013-03-19 | 2013-07-24 | 上海宝信软件股份有限公司 | General database data structure, data migratory system and method thereof |
| CN104991836A (en) * | 2015-06-19 | 2015-10-21 | 小米科技有限责任公司 | Database recovery method and apparatus and database server |
| CN105574002A (en) * | 2014-10-11 | 2016-05-11 | 镇江鼎拓科技信息有限公司 | SQL database-data file transmission method |
| CN105630783A (en) * | 2014-10-27 | 2016-06-01 | 青岛金讯网络工程有限公司 | Database data migration system |
| CN106649837A (en) * | 2016-12-29 | 2017-05-10 | 成都三零盛安信息系统有限公司 | Database compatible method |
| CN107193871A (en) * | 2017-04-12 | 2017-09-22 | 腾讯科技（深圳）有限公司 | Database recovering method and device |
| CN107391306A (en) * | 2017-07-27 | 2017-11-24 | 国家电网公司 | A kind of isomeric data library backup file access pattern method |
| CN107391635A (en) * | 2017-06-30 | 2017-11-24 | 北京奇虎科技有限公司 | Data synchronous system and method |
| CN107480262A (en) * | 2017-08-05 | 2017-12-15 | 中山大学 | A kind of general management system of database based on webpage |
| CN109657006A (en) * | 2018-12-14 | 2019-04-19 | 万翼科技有限公司 | Method of data synchronization, device and computer readable storage medium |
-
2019
- 2019-11-29 CN CN201911196247.4A patent/CN110928899B/en active Active
Patent Citations (12)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN102063491A (en) * | 2010-12-30 | 2011-05-18 | 浪潮集团山东通用软件有限公司 | Data structure capable of realizing data back-up and restoration of different types of databases and realization method |
| CN102693174A (en) * | 2012-06-06 | 2012-09-26 | 武汉大学 | Object broker database constraint-oriented backup method |
| CN103218402A (en) * | 2013-03-19 | 2013-07-24 | 上海宝信软件股份有限公司 | General database data structure, data migratory system and method thereof |
| CN105574002A (en) * | 2014-10-11 | 2016-05-11 | 镇江鼎拓科技信息有限公司 | SQL database-data file transmission method |
| CN105630783A (en) * | 2014-10-27 | 2016-06-01 | 青岛金讯网络工程有限公司 | Database data migration system |
| CN104991836A (en) * | 2015-06-19 | 2015-10-21 | 小米科技有限责任公司 | Database recovery method and apparatus and database server |
| CN106649837A (en) * | 2016-12-29 | 2017-05-10 | 成都三零盛安信息系统有限公司 | Database compatible method |
| CN107193871A (en) * | 2017-04-12 | 2017-09-22 | 腾讯科技（深圳）有限公司 | Database recovering method and device |
| CN107391635A (en) * | 2017-06-30 | 2017-11-24 | 北京奇虎科技有限公司 | Data synchronous system and method |
| CN107391306A (en) * | 2017-07-27 | 2017-11-24 | 国家电网公司 | A kind of isomeric data library backup file access pattern method |
| CN107480262A (en) * | 2017-08-05 | 2017-12-15 | 中山大学 | A kind of general management system of database based on webpage |
| CN109657006A (en) * | 2018-12-14 | 2019-04-19 | 万翼科技有限公司 | Method of data synchronization, device and computer readable storage medium |
Non-Patent Citations (2)
| Title |
|---|
| 姚蕊.船载海洋环境数据库系统性能优化方法研究及应用.《中国优秀硕士学位论文全文数据库 信息科技辑》.2012,(第05期),全文. * |
| 张淑玉.SQL Server数据库的备份与恢复.《潍坊学院学报》.2017,全文. * |
Also Published As
| Publication number | Publication date |
|---|---|
| CN110928899A (en) | 2020-03-27 |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| CN110928899B (en) | Universal database backup method and system | |
| AU2019257524B2 (en) | Managing operations on stored data units | |
| CN106933703B (en) | Database data backup method and device and electronic equipment | |
| AU2014226446B2 (en) | Managing operations on stored data units | |
| CN104753540A (en) | Data compression method, data decompression method and device | |
| CN111008246B (en) | Database log synchronization method, device, computer equipment and readable storage medium | |
| CN112231407B (en) | DDL synchronization method, device, equipment and medium of PostgreSQL database | |
| CN107111460A (en) | Use the data de-duplication of block file | |
| AU2014226447B2 (en) | Managing operations on stored data units | |
| KR101588375B1 (en) | Method and system for managing database | |
| US7568156B1 (en) | Language rendering | |
| US8463759B2 (en) | Method and system for compressing data | |
| CN111125129A (en) | Data processing method and device, storage medium and processor | |
| CN113625952B (en) | Object storage method, device, equipment and storage medium | |
| CN116860509B (en) | PST file repairing method, device and storage medium | |
| CN113313540B (en) | Contract generation method and device, electronic equipment and readable storage medium | |
| CN118094491A (en) | Method for realizing PPT file editing and encryption | |
| CN116521430A (en) | File repairing method and system | |
| CN117271210A (en) | Database backup method and related device |
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 |