CN106354583A - Hot backup method and system for MySQL database - Google Patents
Hot backup method and system for MySQL database Download PDFInfo
- Publication number
- CN106354583A CN106354583A CN201610764307.8A CN201610764307A CN106354583A CN 106354583 A CN106354583 A CN 106354583A CN 201610764307 A CN201610764307 A CN 201610764307A CN 106354583 A CN106354583 A CN 106354583A
- Authority
- CN
- China
- Prior art keywords
- backup
- data base
- data
- mysql
- log
- 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
Classifications
-
- 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
Landscapes
- Engineering & Computer Science (AREA)
- Theoretical Computer Science (AREA)
- Quality & Reliability (AREA)
- Physics & Mathematics (AREA)
- General Engineering & Computer Science (AREA)
- General Physics & Mathematics (AREA)
- Information Retrieval, Db Structures And Fs Structures Therefor (AREA)
Abstract
The invention relates to the technical field of backup and recovery of databases, in particular to a hot backup method for a MySQL database. The hot backup method includes the steps: firstly checking a log sequence number of the MySQL database when backup begins, and then performing backup for data files of the MySQL database; simultaneously and circularly checking redo logs of the MySQL database in the backup process of the data files, performing backup for modification of the data files in the backup process, recoding logs into a file when log sequence numbers in redo logs are larger than log sequence numbers beginning backup, and storing the file to a shared storage device; recording a log sequence number after backup is finished to prepare for next backup. The invention further provides a hot backup system for the MySQL database. Compared with the prior art, the technical scheme is rapid in backup and recovery and overcomes the shortcoming that running MySQL database must be stopped before backup, and MySQL database services are less affected.
Description
Technical field
The present invention relates to the backup of data base and recovery technology field, more particularly, to a kind of Hot Spare of mysql data base
Method and system.
Background technology
At present, the main backup mode of mysql data base is backup logic and physical backup.
Backup logic mainly passes through to inquire about the table structure data of mysql data base, is converted into corresponding sql language
Sentence, stores in a text.It belongs to Hot Spare, and during backup, data base still can use, but since it is desired that takies mysql
Database server resource is inquired about, so when data volume is larger, the speed of backup logic is excessively slow, and can be to data base
Business affect greatly.Because being to back up into sql sentence, being to re-execute a sql sentence when recovering, recovering speed
Degree is slow.
Physical backup is mainly directly from disk copy data file as backup.Its backup rate is fast, and resume speed is fast,
But belong to cold standby it is impossible to ensure the concordance of data, so the running stopping data base during backup, being needed to keep Backup Data
Concordance.
Content of the invention
It is an object of the invention to solve existing physical backup technology to need to stop the technical problem of data base.
To achieve these goals, the present invention provides a kind of heat backup method of mysql data base, comprises the following steps:
S101: when backup starts, first check the log serial number of mysql data base, then back up mysql data base's
Data file；
S201: during backup data files, circular test mysql data base redoes log simultaneously, to backing up
In journey, the modification of data file is backed up；
S105: after the completion of backup, record log serial number now is that backup is prepared next time.
Preferably, by network, data file is deposited with storing by way of removing repeated data block to share in s101 step
Storage equipment.
Preferably, the daily record of log serial number when middle log serial number is more than backup beginning will be redo log in s201 step
Recorded a file, store shared storage device.
Preferably, after s101 step, also include s102 step: backing up selection based on last backup is
No carry out incremental backup.
Preferably, in s102 step, select not carrying out incremental backup, then enter step s103: backup mysql data base
Data file, subsequently into step s105.
Preferably, in s102 step, select to carry out incremental backup, then enter step s104: backup data files are Sino-Japan
Will serial number is more than the data page of basis backup, subsequently into step s105.
To achieve these goals, the present invention also provides a kind of hot backup system of mysql data base, comprising:
Physical backup unit, for backing up the data file of mysql data base；
Daily record monitoring unit, for redoing log of circular test mysql data base in backup procedure；
Recovery unit, for when mysql data base delays machine, the data of backup being transferred to the machine from shared storage device
In data base directory, according to redoing log of backup, data file is recovered.
Preferably, data file is stored shared storage in the way of removing repeated data block by described physical backup unit
Equipment.
Preferably, under incremental backup situation, log serial number in data file is then compared base by described physical backup unit
The big data page backup of plinth backup.
Preferably, occur under data modification situation, when log serial number is more than backup beginning by described daily record monitoring unit
Log serial number redo log backup.
With respect to prior art, beneficial effects of the present invention are as follows:
Compared with common physical backup method, technical solution of the present invention remains physical backup speed soon, resume speed
Fast advantage, the shortcoming overcoming necessary mysql data base out of service before it backs up.Compared with backup logic, skill of the present invention
Art scheme backup rate is fast, and less due to interacting with mysql database server, and occupancy server resource is few, thus right
The impact of mysql data bank service is less.
Technical solution of the present invention stores Backup Data using the mode removing repeated data block and can to carry out increment standby
Part, dramatically saves on memory space needed for backup.
Brief description
Fig. 1 is the method flow schematic diagram of one embodiment of the invention.
Fig. 2 is the system structure diagram of one embodiment of the invention.
Specific embodiment
For the ease of understanding to the present invention, below in conjunction with the accompanying drawings and specific embodiment is retouched further to the present invention
State.It should be noted that in the case of not colliding, can appoint between following each embodiments and between each technical characteristic
Meaning combination forms new embodiment.
As shown in figure 1, a kind of heat backup method of mysql data base, comprising:
Step s101: when backup starts, first check the log serial number of mysql data base, then back up mysql data
The data file in storehouse；
Step s201: during backup data files, circular test mysql data base redoes log simultaneously, to standby
During part, the modification of data file is backed up, and will redo log middle log serial number and be more than log serial number when backup starts
Log recording to a file, store shared storage device；
Step s105: after the completion of backup, record log serial number now is that backup is prepared next time.
In a specific embodiment, in s101 step by network by data file to remove repeated data block by way of
Store shared storage device.
In another specific embodiment, after s101 step, also include s102 step: using last backup as
Basis backup chooses whether to carry out incremental backup.
In s102 step, if selecting not carrying out incremental backup, enter step s103: the number of backup mysql data base
According to file, subsequently into step s105；If selecting to carry out incremental backup, enter step s104: daily record in backup data files
Serial number is more than the data page of basis backup, subsequently into step s105.
As shown in Fig. 2 a kind of hot backup system of mysql data base, comprising:
Physical backup unit 10, for backing up the data file of mysql data base；
Daily record monitoring unit 20, for redoing log of circular test mysql data base in backup procedure；
Recovery unit 30, for when mysql data base delays machine, the data of backup being transferred to this from shared storage device
In machine data base directory, according to redoing log of backup, data file is recovered.
In a specific embodiment, data file is stored in the way of removing repeated data block by physical backup unit 10
To shared storage device.
In a specific embodiment, under incremental backup situation, physical backup unit 10 is by daily record sequence in data file
The row number data page backup bigger than basis backup.
In a specific embodiment, occur under data modification situation, log serial number is more than standby by daily record monitoring unit
When part starts, log serial number redoes log backup.
The announcement of book and teaching according to the above description, those skilled in the art in the invention can also be to above-mentioned embodiment party
Formula is changed and is changed, and therefore, the invention is not limited in specific embodiment disclosed and described above, to the present invention's
In the scope of the claims that some modifications and changes should also be as fall into the present invention.Although additionally, using in this specification
Some specific terms, but these terms are merely for convenience of description, do not constitute any restriction to the present invention.
Claims (10)
1. a kind of heat backup method of mysql data base is it is characterised in that comprise the following steps:
S101: when backup starts, first check the log serial number of mysql data base, then back up the data of mysql data base
File；
S201: during backup data files, circular test mysql data base redoes log simultaneously, in backup procedure
The modification of data file is backed up；
S105: after the completion of backup, record log serial number now is that backup is prepared next time.
2. the heat backup method of mysql data base as claimed in claim 1 is it is characterised in that pass through network in s101 step
Data file is stored shared storage device in the way of removing repeated data block.
3. the heat backup method of mysql data base as claimed in claim 1 will be it is characterised in that will reform day in s201 step
In will, log serial number is more than the log recording of log serial number when backup starts to a file, stores shared storage and sets
Standby.
4. described mysql data base as arbitrary in claims 1 to 3 heat backup method it is characterised in that s101 step it
Afterwards, also include s102 step: backup chooses whether to carry out incremental backup based on last backup.
5. the heat backup method of mysql data base as claimed in claim 4 is it is characterised in that in s102 step, select not
Carry out incremental backup, then enter step s103: the data file of backup mysql data base, subsequently into step s105.
6. the heat backup method of mysql data base as claimed in claim 4 is it is characterised in that in s102 step, select into
Row incremental backup, then enter step s104: in backup data files, log serial number is more than the data page of basis backup, Ran Houjin
Enter step s105.
7. a kind of hot backup system based on mysql data base is it is characterised in that include:
Physical backup unit, for backing up the data file of mysql data base；
Daily record monitoring unit, for redoing log of circular test mysql data base in backup procedure；
Recovery unit, for when mysql data base delays machine, the data of backup being transferred to native data from shared storage device
In library directory, according to redoing log of backup, data file is recovered.
8. the hot backup system of mysql data base as claimed in claim 7 will be it is characterised in that described physical backup unit will
Data file stores shared storage device in the way of removing repeated data block.
9. the hot backup system of mysql data base as claimed in claim 7 is it is characterised in that under incremental backup situation, institute
State physical backup unit then by data page backup bigger than basis backup for log serial number in data file.
10. it is characterised in that there is data modification situation in the hot backup system of mysql data base as claimed in claim 7
Under, when log serial number is more than backup beginning by described daily record monitoring unit, log serial number redoes log backup.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN201610764307.8A CN106354583B (en) | 2016-08-30 | 2016-08-30 | A kind of heat backup method and system of MySQL database |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN201610764307.8A CN106354583B (en) | 2016-08-30 | 2016-08-30 | A kind of heat backup method and system of MySQL database |
Publications (2)
| Publication Number | Publication Date |
|---|---|
| CN106354583A true CN106354583A (en) | 2017-01-25 |
| CN106354583B CN106354583B (en) | 2019-09-17 |
Family
ID=57855981
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN201610764307.8A Active CN106354583B (en) | 2016-08-30 | 2016-08-30 | A kind of heat backup method and system of MySQL database |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN106354583B (en) |
Cited By (13)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN108599982A (en) * | 2018-03-14 | 2018-09-28 | 华为技术有限公司 | Data reconstruction method and relevant device |
| CN108874592A (en) * | 2018-06-20 | 2018-11-23 | 焦点科技股份有限公司 | A kind of data cold standby method and system for Log-structured storage engines |
| CN108958972A (en) * | 2018-06-25 | 2018-12-07 | 郑州云海信息技术有限公司 | A kind of method of mysql DB Backup |
| CN109144790A (en) * | 2018-09-30 | 2019-01-04 | 广州鼎甲计算机科技有限公司 | The synthesized backup method and device of MySQL database |
| CN109241185A (en) * | 2018-08-27 | 2019-01-18 | 武汉达梦数据库有限公司 | A kind of method and data synchronization unit that data are synchronous |
| CN109542682A (en) * | 2018-11-16 | 2019-03-29 | 上海达梦数据库有限公司 | A kind of data back up method, device, equipment and storage medium |
| CN109947592A (en) * | 2017-12-20 | 2019-06-28 | 腾讯科技（深圳）有限公司 | A kind of method of data synchronization, device and relevant device |
| CN110209527A (en) * | 2018-11-30 | 2019-09-06 | 腾讯科技（深圳）有限公司 | Data reconstruction method, device, server and storage medium |
| CN111158958A (en) * | 2019-12-31 | 2020-05-15 | 广州鼎甲计算机科技有限公司 | Backup method, system, device and storage medium capable of quickly recovering data |
| CN111240897A (en) * | 2020-01-07 | 2020-06-05 | 腾讯科技（深圳）有限公司 | Data processing method and related equipment |
| CN111382012A (en) * | 2020-03-03 | 2020-07-07 | 广州鼎甲计算机科技有限公司 | Backup method and device for MySQL cloud database, computer equipment and storage medium |
| CN111625397A (en) * | 2020-04-14 | 2020-09-04 | 北京捷通华声科技股份有限公司 | Service log backup method, cluster, device, electronic equipment and storage medium |
| CN113434339A (en) * | 2021-06-29 | 2021-09-24 | 湖南方心科技股份有限公司 | Data encryption transmission system and method based on combined cycle backup for intelligent computing center |
Citations (2)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN102156720A (en) * | 2011-03-28 | 2011-08-17 | 中国人民解放军国防科学技术大学 | Method, device and system for restoring data |
| US20140258223A1 (en) * | 2013-03-08 | 2014-09-11 | Oracle International Corporation | Adaptive high-performance database redo log synchronization |
-
2016
- 2016-08-30 CN CN201610764307.8A patent/CN106354583B/en active Active
Patent Citations (2)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN102156720A (en) * | 2011-03-28 | 2011-08-17 | 中国人民解放军国防科学技术大学 | Method, device and system for restoring data |
| US20140258223A1 (en) * | 2013-03-08 | 2014-09-11 | Oracle International Corporation | Adaptive high-performance database redo log synchronization |
Non-Patent Citations (1)
| Title |
|---|
| 易剑等: "Oracle数据库备份策略研讨", 《科技广场》 * |
Cited By (21)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN109947592A (en) * | 2017-12-20 | 2019-06-28 | 腾讯科技（深圳）有限公司 | A kind of method of data synchronization, device and relevant device |
| CN109947592B (en) * | 2017-12-20 | 2022-11-18 | 腾讯科技（深圳）有限公司 | Data synchronization method, device and related equipment |
| CN108599982B (en) * | 2018-03-14 | 2021-02-12 | 华为技术有限公司 | Data recovery method and related equipment |
| CN108599982A (en) * | 2018-03-14 | 2018-09-28 | 华为技术有限公司 | Data reconstruction method and relevant device |
| CN108874592A (en) * | 2018-06-20 | 2018-11-23 | 焦点科技股份有限公司 | A kind of data cold standby method and system for Log-structured storage engines |
| CN108874592B (en) * | 2018-06-20 | 2020-04-10 | 焦点科技股份有限公司 | Data cold standby method and system for Log-structured storage engine |
| CN108958972A (en) * | 2018-06-25 | 2018-12-07 | 郑州云海信息技术有限公司 | A kind of method of mysql DB Backup |
| CN109241185A (en) * | 2018-08-27 | 2019-01-18 | 武汉达梦数据库有限公司 | A kind of method and data synchronization unit that data are synchronous |
| CN109241185B (en) * | 2018-08-27 | 2021-03-30 | 武汉达梦数据库股份有限公司 | Data synchronization method and data synchronization device |
| CN109144790A (en) * | 2018-09-30 | 2019-01-04 | 广州鼎甲计算机科技有限公司 | The synthesized backup method and device of MySQL database |
| CN109542682A (en) * | 2018-11-16 | 2019-03-29 | 上海达梦数据库有限公司 | A kind of data back up method, device, equipment and storage medium |
| CN109542682B (en) * | 2018-11-16 | 2021-03-30 | 上海达梦数据库有限公司 | Data backup method, device, equipment and storage medium |
| CN110209527A (en) * | 2018-11-30 | 2019-09-06 | 腾讯科技（深圳）有限公司 | Data reconstruction method, device, server and storage medium |
| CN111158958A (en) * | 2019-12-31 | 2020-05-15 | 广州鼎甲计算机科技有限公司 | Backup method, system, device and storage medium capable of quickly recovering data |
| CN111240897A (en) * | 2020-01-07 | 2020-06-05 | 腾讯科技（深圳）有限公司 | Data processing method and related equipment |
| CN111240897B (en) * | 2020-01-07 | 2023-04-14 | 腾讯科技（深圳）有限公司 | Data processing method and related equipment |
| CN111382012A (en) * | 2020-03-03 | 2020-07-07 | 广州鼎甲计算机科技有限公司 | Backup method and device for MySQL cloud database, computer equipment and storage medium |
| CN111382012B (en) * | 2020-03-03 | 2020-12-29 | 广州鼎甲计算机科技有限公司 | Backup method and device for MySQL cloud database, computer equipment and storage medium |
| CN111625397A (en) * | 2020-04-14 | 2020-09-04 | 北京捷通华声科技股份有限公司 | Service log backup method, cluster, device, electronic equipment and storage medium |
| CN111625397B (en) * | 2020-04-14 | 2023-09-12 | 北京捷通华声科技股份有限公司 | Service log backup method, cluster, device, electronic equipment and storage medium |
| CN113434339A (en) * | 2021-06-29 | 2021-09-24 | 湖南方心科技股份有限公司 | Data encryption transmission system and method based on combined cycle backup for intelligent computing center |
Also Published As
| Publication number | Publication date |
|---|---|
| CN106354583B (en) | 2019-09-17 |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| CN106354583A (en) | Hot backup method and system for MySQL database | |
| CN110532247B (en) | Data migration method and data migration system | |
| CN101582920B (en) | Method and device for verifying and synchronizing data blocks in distributed file system | |
| CN106445738B (en) | Database backup method and device | |
| CN103530290B (en) | Data migration method and system between database | |
| US8527459B2 (en) | System and method for data replication between heterogeneous databases | |
| CN102024016B (en) | Rapid data restoration method for distributed file system (DFS) | |
| CN104281533B (en) | A kind of method and device of data storage | |
| CN105446825B (en) | Database testing method and device | |
| CN105701190A (en) | Data synchronizing method and device | |
| CN105824846B (en) | Data migration method and device | |
| CN105989059A (en) | Data record checking method and device | |
| CN105573859A (en) | Data recovery method and device of database | |
| CN103198122B (en) | Restart the method and apparatus of memory database | |
| CN106802895A (en) | For repair data storehouse for database data method and apparatus | |
| CN102521114A (en) | File system log storage system under virtualization environment | |
| CN105376277A (en) | Data synchronization method and device | |
| CN103605657A (en) | Method and device for reconstructing index online | |
| CN105955845A (en) | Data recovery method and device | |
| CN102200944A (en) | Test environment cloning method and system for enterprise resource planning (ERP) system | |
| CN107391544A (en) | Processing method, device, equipment and the computer storage media of column data storage | |
| CN104199963A (en) | Method and device for HBase data backup and recovery | |
| CN102226892B (en) | Disk fault tolerance processing method and device thereof | |
| CN103984309A (en) | Cigarette production system with disaster tolerance function and disaster tolerance exercise method thereof | |
| CN104636218B (en) | Data reconstruction method and device |
Legal Events
| Date | Code | Title | Description |
|---|---|---|---|
| C06 | 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 | ||
| CP02 | Change in the address of a patent holder | ||
| CP02 | Change in the address of a patent holder |
Address after: Room 901, 243 Science Avenue, Guangzhou High-tech Industrial Development Zone, Guangdong 510000 Patentee after: Guangzhou Dingjia Computer Technology Co.,Ltd. Address before: 510660 Unit 701-9, Comprehensive Building of Science and Technology Innovation Base, 80 Lanyue Road, Science City, Guangzhou High-tech Industrial Development Zone, Guangdong Province Patentee before: Guangzhou Dingjia Computer Technology Co.,Ltd. |
|
| PE01 | Entry into force of the registration of the contract for pledge of patent right | ||
| PE01 | Entry into force of the registration of the contract for pledge of patent right |
Denomination of invention: Hot backup method and system for MySQL database Effective date of registration: 20200204 Granted publication date: 20190917 Pledgee: Guangzhou Rural Commercial Bank Co.,Ltd. Huaxia Branch Pledgor: Guangzhou Dingjia Computer Technology Co.,Ltd. Registration number: Y2020440000023 |
|
| PC01 | Cancellation of the registration of the contract for pledge of patent right | ||
| PC01 | Cancellation of the registration of the contract for pledge of patent right |
Date of cancellation: 20230703 Granted publication date: 20190917 Pledgee: Guangzhou Rural Commercial Bank Co.,Ltd. Huaxia Branch Pledgor: Guangzhou Dingjia Computer Technology Co.,Ltd. Registration number: Y2020440000023 |