CN106354583B - A kind of heat backup method and system of MySQL database - Google Patents
A kind of heat backup method and system of MySQL database Download PDFInfo
- Publication number
- CN106354583B CN106354583B CN201610764307.8A CN201610764307A CN106354583B CN 106354583 B CN106354583 B CN 106354583B CN 201610764307 A CN201610764307 A CN 201610764307A CN 106354583 B CN106354583 B CN 106354583B
- Authority
- CN
- China
- Prior art keywords
- backup
- log
- mysql database
- data
- serial number
- 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
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
Abstract
The present invention relates to the backup of database and recovery technology field more particularly to a kind of heat backup methods of MySQL database, comprising: when backing up beginning, first checks the log serial number of MySQL database, then the data file of backup MySQL database；During backup data files, circular test MySQL database is redo log simultaneously, the modification of data file in backup procedure is backed up, by the log recording for redoing log log serial number when middle log serial number is greater than backup beginning to a file, stores and arrives shared storage device；After the completion of backup, the log serial number recorded at this time is that backup is prepared next time.The present invention also provides a kind of hot backup systems of MySQL database.Compared with the existing technology, the shortcomings that technical solution of the present invention backup rate is fast, and resume speed is fast, overcomes necessary MySQL database out of service before backing up, and the influence to MySQL database business is smaller.
Description
Technical field
The present invention relates to the backup of database and recovery technology field more particularly to a kind of warm back-ups of MySQL database
Method and system.
Background technique
Currently, the main backup mode of MySQL database is backup logic and physical backup.
Backup logic mainly passes through the table structure and data of inquiry MySQL database, is converted into corresponding SQL language
Sentence is stored into a text file.It belongs to warm back-up, and database still can be used when backup, but since it is desired that occupy MySQL
Database server resource is inquired, so when the amount of data is large, the speed of backup logic is excessively slow, and can be to database
Business affect greatly.Because being backup into SQL statement, it is to re-execute a SQL statement when restoring, restores 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 cannot be guaranteed that the consistency of data, so the running for needing to stop database when backup keeps Backup Data
Consistency.
Summary of the invention
It is an object of the invention to solve existing physical backup technology to need the technical issues of stopping database.
To achieve the goals above, the present invention provides a kind of heat backup method of MySQL database, comprising the following steps:
S101: back up start when, first check the log serial number of MySQL database, then backup MySQL database
Data file；
S201: during backup data files, while circular test MySQL database is redo log, to backing up
The modification of data file is backed up in journey；
S105: after the completion of backup, the log serial number recorded at this time is that backup is prepared next time.
Preferably, data file is stored to shared deposit in S101 step network in a manner of removing repeated data block
Store up equipment.
Preferably, the log of log serial number when middle log serial number is greater than backup beginning will be redo log in S201 step
A file is recorded, shared storage device is arrived in storage.
Preferably, further include S102 step after S101 step: selection is backed up based on last backup is
No carry out incremental backup.
Preferably, it in S102 step, selects then to enter step S103: backup MySQL database without incremental backup
Data file, subsequently into step S105.
Preferably, in S102 step, selection carries out incremental backup, then enter step S104: backup data files are Sino-Japan
Will sequence number is greater than the data page of basis backup, subsequently into step S105.
To achieve the goals above, the present invention also provides a kind of hot backup systems of MySQL database, comprising:
Physical backup unit, for backing up the data file of MySQL database；
Log monitoring unit, in backup procedure circular test MySQL database redo log；
Recovery unit, for when MySQL database delay machine, the data of backup to be transferred to the machine from shared storage device
In data base directory, data file is restored according to redoing log for backup.
Preferably, data file is stored shared storage by the physical backup unit in a manner of removing repeated data block
Equipment.
Preferably, under incremental backup situation, log serial number in data file is then compared base by the physical backup unit
Plinth backs up big data page backup.
Preferably, occur under data modification situation, when log serial number is greater than backup beginning by the log monitoring unit
Log serial number redoes log backup.
Compared with the existing technology, beneficial effects of the present invention are as follows:
Compared with common physical backup method, it is fast that technical solution of the present invention remains physical backup speed, resume speed
Fast advantage overcomes the shortcomings that it backs up preceding necessary MySQL database out of service.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, so right
The influence of MySQL database business is smaller.
Technical solution of the present invention stores Backup Data using the mode of removal repeated data block and can to carry out increment standby
Part, memory space needed for dramatically saving backup.
Detailed description of the invention
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 the present invention, with reference to the accompanying drawing and specific embodiment further retouches the present invention
It states.It should be noted that can appoint between following each embodiments and between each technical characteristic in the case where not colliding
Meaning combination forms new embodiment.
As shown in Figure 1, a kind of heat backup method of MySQL database, comprising:
Step S101: when backing up beginning, the log serial number of MySQL database is first checked, then backup MySQL data
The data file in library；
Step S201: during backup data files, while circular test MySQL database is redo log, to standby
The modification of data file is backed up during part, will be redo log middle log serial number and is greater than log serial number when backup starts
Log recording to a file, shared storage device is arrived in storage；
Step S105: after the completion of backup, the log serial number recorded at this time is that backup is prepared next time.
In a specific embodiment, in S101 step network by data file in a manner of removing repeated data block
Store shared storage device.
Further include S102 step after S101 step in another specific embodiment: using last backup as
Basis backup chooses whether to carry out incremental backup.
In S102 step, if selection enters step S103: backing up the number of MySQL database without incremental backup
According to file, subsequently into step S105；If selection carries out incremental backup, S104 is entered step: log in backup data files
Sequence number is greater than the data page of basis backup, subsequently into step S105.
As shown in Fig. 2, a kind of hot backup system of MySQL database, comprising:
Physical backup unit 10, for backing up the data file of MySQL database；
Log monitoring unit 20, in backup procedure circular test MySQL database redo log；
Recovery unit 30, for when MySQL database delay machine, the data of backup to be transferred to this from shared storage device
In machine data base directory, data file is restored according to redoing log for backup.
In a specific embodiment, physical backup unit 10 stores data file in a manner of removing repeated data block
To shared storage device.
In a specific embodiment, under incremental backup situation, physical backup unit 10 is by log sequence in data file
Row number backs up big data page backup than basis.
In a specific embodiment, occur under data modification situation, log serial number is greater than standby by log monitoring unit
Log serial number redoes log backup when part starts.
According to the disclosure and teachings of the above specification, those skilled in the art in the invention can also be to above-mentioned embodiment party
Formula is changed and is modified, and therefore, the invention is not limited to the specific embodiments disclosed and described above, to of the invention
Some modifications and changes should also be as falling into the scope of the claims of the present invention.In addition, although being used in this specification
Some specific terms, these terms are merely for convenience of description, does not limit the present invention in any way.
Claims (3)
1. a kind of heat backup method of MySQL database, which comprises the following steps:
S101: when backing up beginning, the log serial number of MySQL database is first checked, then the data of backup MySQL database
File；
S102: backup chooses whether to carry out incremental backup based on last backup；
In S102 step, select then to enter step S103 without incremental backup: backing up the data file of MySQL database,
Subsequently into step S105；
In S102 step, selection carries out incremental backup, then enter step S104: log serial number is greater than in backup data files
The data page of basis backup, subsequently into step S105；
S105: after the completion of backup, the log serial number recorded at this time is that backup is prepared next time；
S201: during backup data files, while circular test MySQL database is redo log, in backup procedure
The modification of data file is backed up；
In S201 step, by the log recording of log serial number when redoing log middle log serial number greater than backup beginning to one
Shared storage device is arrived in a file, storage.
2. the heat backup method of MySQL database as described in claim 1, which is characterized in that pass through network in S101 step
Data file is stored into shared storage device in a manner of removing repeated data block.
3. a kind of hot backup system based on MySQL database characterized by comprising
Data file is removed the side of repeated data block for backing up the data file of MySQL database by physical backup unit
Formula is stored to shared storage device, and under incremental backup situation, the physical backup unit is then by logged sequence in data file
Number big data page backup is backed up than basis；
Log monitoring unit, in backup procedure circular test MySQL database redo log；Recovery unit is used for
When MySQL database delay machine, the data of backup are transferred in native data library directory from shared storage device, according to backup
Redo log data file restored, occur under data modification situation, the log monitoring unit is by log serial number
Log serial number redoes log backup when starting greater than 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 CN106354583A (en) | 2017-01-25 |
| CN106354583B true 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) |
Families Citing this family (13)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| 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 |
| 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 |
| 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 |
| CN109542682B (en) * | 2018-11-16 | 2021-03-30 | 上海达梦数据库有限公司 | Data backup method, device, equipment and storage medium |
| CN110209527B (en) * | 2018-11-30 | 2023-05-05 | 腾讯科技（深圳）有限公司 | Data recovery method, device, server and storage medium |
| CN111158958B (en) * | 2019-12-31 | 2020-11-13 | 广州鼎甲计算机科技有限公司 | Backup method, system, device and storage medium capable of quickly recovering data |
| CN111240897B (en) * | 2020-01-07 | 2023-04-14 | 腾讯科技（深圳）有限公司 | Data processing method and related equipment |
| CN111382012B (en) * | 2020-03-03 | 2020-12-29 | 广州鼎甲计算机科技有限公司 | Backup method and device for MySQL cloud database, computer equipment and storage medium |
| CN111625397B (en) * | 2020-04-14 | 2023-09-12 | 北京捷通华声科技股份有限公司 | Service log backup method, cluster, device, electronic equipment and storage medium |
| CN113434339B (en) * | 2021-06-29 | 2023-07-18 | 方心科技股份有限公司 | Data encryption transmission system and method based on combined cyclic backup for intelligent computing center |
Citations (1)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN102156720A (en) * | 2011-03-28 | 2011-08-17 | 中国人民解放军国防科学技术大学 | Method, device and system for restoring data |
Family Cites Families (1)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| US9418129B2 (en) * | 2013-03-08 | 2016-08-16 | Oracle International Corporation | Adaptive high-performance database redo log synchronization |
-
2016
- 2016-08-30 CN CN201610764307.8A patent/CN106354583B/en active Active
Patent Citations (1)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN102156720A (en) * | 2011-03-28 | 2011-08-17 | 中国人民解放军国防科学技术大学 | Method, device and system for restoring data |
Non-Patent Citations (1)
| Title |
|---|
| Oracle数据库备份策略研讨;易剑等;《科技广场》;20090331(第3期);第61页 |
Also Published As
| Publication number | Publication date |
|---|---|
| CN106354583A (en) | 2017-01-25 |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| CN106354583B (en) | A kind of heat backup method and system of MySQL database | |
| CN110532247B (en) | Data migration method and data migration system | |
| CN105389230B (en) | A kind of continuous data protection system and method for combination snapping technique | |
| CN106445738B (en) | Database backup method and device | |
| CN103488544B (en) | Detect the treating method and apparatus of slow dish | |
| US20120246116A1 (en) | System and method for data replication between heterogeneous databases | |
| CN103744906A (en) | System, method and device for data synchronization | |
| CN105824846B (en) | Data migration method and device | |
| WO2014169142A1 (en) | Partition level backup and restore of a massively parallel processing database | |
| WO2018098972A1 (en) | Log recovery method, storage device and storage node | |
| CN105573859A (en) | Data recovery method and device of database | |
| CN104301360A (en) | Method, log server and system for recording log data | |
| CN105701190A (en) | Data synchronizing method and device | |
| CN103530388A (en) | Performance improving data processing method in cloud storage system | |
| CN109189860A (en) | A kind of active and standby increment synchronization method of MySQL based on Kubernetes system | |
| CN110704242A (en) | Continuous data protection system and method based on Oracle log capture | |
| CN105302667A (en) | Cluster architecture based high-reliability data backup and recovery method | |
| CN105446825B (en) | Database testing method and device | |
| CN104378423A (en) | Metadata cluster distribution storage system and storage data reading and writing method | |
| WO2019020081A1 (en) | Distributed system and fault recovery method and apparatus thereof, product, and storage medium | |
| CN105376277A (en) | Data synchronization method and device | |
| CN104899071A (en) | Recovery method and recovery system of virtual machine in cluster | |
| CN110502523A (en) | Business datum storage method, device, server and computer readable storage medium | |
| CN111666266A (en) | Data migration method and related equipment | |
| CN105589887A (en) | Data processing method for distributed file system and distributed file system |
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 |
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. |
|
| CP02 | Change in the address of a patent holder | ||
| 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 |
|
| PE01 | Entry into force 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 |
|
| PC01 | Cancellation of the registration of the contract for pledge of patent right |