CN107247639A - A kind of efficient backup method of mysql databases - Google Patents
A kind of efficient backup method of mysql databases Download PDFInfo
- Publication number
- CN107247639A CN107247639A CN201710305107.0A CN201710305107A CN107247639A CN 107247639 A CN107247639 A CN 107247639A CN 201710305107 A CN201710305107 A CN 201710305107A CN 107247639 A CN107247639 A CN 107247639A
- Authority
- CN
- China
- Prior art keywords
- backup
- mysql databases
- mysql
- data
- backup method
- 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.)
- Pending
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
- G06F11/1446—Point-in-time backing up or restoration of persistent data
- G06F11/1448—Management of the data involved in backup or backup restore
- G06F11/1451—Management of the data involved in backup or backup restore by selection of backup contents
-
- 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/1458—Management of the backup or restore process
- G06F11/1461—Backup scheduling policy
-
- 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/1458—Management of the backup or restore process
- G06F11/1464—Management of the backup or restore process for networked environments
Abstract
The invention discloses a kind of efficient backup method of mysql databases, when it is backed up for mysql databases, using a point scheme for table backup, avoid carrying out the full table backup in disposable full storehouse, so effectively reduce the BACKUP TIME of full library backup scheme, the flexibility ratio of DB Backup is improved, and improves the efficiency of DB Backup.
Description
Technical field
The present invention relates to database technology, and in particular to the redundancy technique of database.
Background technology
Mysqldump is that mysql is used for the utility program that database is stored up in unloading.It mainly produces a SQL script, wherein
Include order CREATE TABLE INSERT necessary to from the beginning re-creating database etc..Carrying out mysql DB Backups
When, the full dose (storehouse) for carrying out database using following sentence is backed up：mysqldump-uroot-pxxx database>/
opt/data/backup/xxx.sql。
Existing DB Backup scheme can only carry out the full table backup in disposable full storehouse, generate .sql texts one big
Part, and BACKUP TIME is long, and recovery time is long, and inefficiency, flexibility ratio is too low.
The content of the invention
The problem of for existing mysql DB Backups scheme inefficiency, is, it is necessary to which a kind of efficient mysql databases are standby
Part scheme.
Therefore, the technical problems to be solved by the invention are to provide a kind of efficient backup method of mysql databases.
In order to solve the above-mentioned technical problem, the efficient backup method of mysql databases that the present invention is provided, it is standby by point table
Part mysql databases.
In this programme, methods described includes：
All tables of whole database are classified；
Backed up, recovered in units of table in units of table：
The backup of table structure is separated with data backup.
In this programme, first recover table structure in migration, carry out data importing respectively afterwards.
In this programme, the relative index before data importing in table first that data volume is big is deleted, and data are imported
Add original index again afterwards.
In this programme, data logging function is first closed before backing up or recovering, is opened in the completed.
In this programme, master-slave synchronisation is first closed before backing up or recovering, is then turned in the completed.
In this programme, a point table backup mysql databases are realized by shell scripts, and utilize linux crontab
Timed task, corresponding shell scripts are performed by set order.
In this programme, after Backup end, the full table in full storehouse is carried out to target database and is counted, and statistics is write
In log texts, contrasted during for database recovery.
In this programme, object table is backed up using Mysql mysqldump command statements, target database
Table structure is backed up.
The DB Backup scheme that the present invention is provided is backed up by point table, when can reduce the backup of full library backup scheme
Between, the flexibility ratio of DB Backup is improved, and improve the efficiency of DB Backup；Effectively solve asking present in prior art
Topic.
Embodiment
In order that the technical means, the inventive features, the objects and the advantages of the present invention are easy to understand, tie below
Instantiation is closed, the present invention is expanded on further.
When this programme is backed up for mysql databases, using a point scheme for table backup, it is to avoid carry out disposable complete
The full table backup in storehouse, so effectively reduces the BACKUP TIME of full library backup scheme, improves the flexibility ratio of DB Backup, and improve
The efficiency of DB Backup.
Well-behaved table backup scenario is with specific reference to different business diagnosis, and it is standby flexibly to carry out according to analysis result point table
Part.
For this programme, due to different systems, different results can be analyzed, only by business diagnosis, are extracted
It is being had little significance in system but be necessary table (such as Operation Log table, often take big quantity space), then in backup/extensive
When multiple by its specially treated (such as only backup, with when recover elsewhere, it might even be possible to different BACKUP TIMEs are set, lower
Frequency of your backups, it is such as weekly), to improve the efficiency of backup/restoration.
Accordingly, the scheme that this programme progress mysql databases are backed up is as follows：
1), with reference to business, code, all tables of whole database is classified, traffic table, non-traffic is broadly divided into
Table.
Here, business table is primarily referred to as program and needs to carry out additions and deletions to change to look into, to whole system all important tables.
It is not only needed to back up but also needs to recover；
And non-traffic table is primarily referred to as program and needs increase newly to operate, to the unessential table of whole system, such as grasp
Make the table of daily record etc.It is only needed back up, need not recovered, it is necessary to when nonproductive ring can be taken by operation maintenance personnel
Border is used.
2), backed up, and recovered in units of table in units of table.
Here backup and recovery is carried out in units of single table, is carried out in units of several tables as needed,
It is determined on a case-by-case basis.
3), table structure backup is separated with data backup.
Table structure can first be recovered during migration, data importing is carried out respectively afterwards, thus data are only concerned when recovering data
, improve efficiency.
On this basis, this programme also carries out following further improvement, to carry when carrying out mysql DB Backups
The efficiency of high DB Backup.
Delete index：Relative index before data importing in table first that data volume is big is deleted, and is imported in data
After add original index, thus can save mass data import the time；
Closing journal：Data logging function is first closed before migration, is opened again after the completion of migration；
Close master-slave synchronisation：Master-slave synchronisation is closed before migration, is then turned on after the completion of migration.
The scheme of mysql DB Backups is carried out for above-mentioned use point table backup, this programme can specifically pass through shell
Script realizes the backup scenario.
Specifically, using the above-mentioned realization that mysql DB Backup schemes are carried out using point table backup of shell script edits
Step；Simultaneously in the crontab timed tasks using linux, corresponding shell scripts are performed by set order, thus timing
It is automatically performed mysql DB Backups.
Here linux crontab timing functions are used, can realize and target database is determined according to set time
When back up.The object that Crontab timed tasks are performed is shell scripts, and shell scripts are specific scheme realizations.
In addition, when this programme is implemented, using Mysql mysqldump command statements to mesh in shell scripts
The work such as mark table is backed up, the backup of the table structure of target database.
Mysqldump is that mysql is used for the utility program that database is stored up in unloading, mainly a SQL script is produced, wherein wrapping
Containing various order CREATE TABLE INSERT necessary to from the beginning re-creating database etc..
After Backup end, the full table in full storehouse is carried out to target database and is counted, and statistics is write in log texts, supplied
Contrasted during database recovery, to judge database recovery success or not.
For such scheme once by a mysql database point table backed-up instances, to illustrate this programme.Its
Whole process is as follows：
1. carry out business diagnosis：Find out in the database to be backed up, there is no business, and occupy the table of mass data；It is right
Only back up in this non-traffic data, do not recover.
2. being backed up in units of table, recovered in units of table.
For relatively existing full library backup scheme, this example is backed up in units of table, and table here can be independent
Table, the set that can also be made up of multiple tables, and be right as whole one in each specific shell scripts are realized
As carrying out backup/restoration.
In addition, it is relative to carry out recovering to be and carry out backup in units of table here in units of table, in units of table
When being backed up, it will generate corresponding sql scripts；Then it is the sql pin for performing these backup generations when being recovered
Original realize is recovered.
3. the backup of table structure is separated with data backup, it can first recover table structure during migration, enter line number respectively again afterwards
According to importing.
Here migration is database recovery.
Database is made up of the data in table structure and table, table structure be exactly define the field of a table, type, major key,
External key, index, these basic attributes constitute the table structure of database；Table data are exactly the data in each table of correspondence.Together
When, mysql provides corresponding table structure, the command statement of table data backup.
Accordingly, when backup, table structure and table data are separately backed up；The corresponding sql pin using backup generation
When this progress recovers, first recover table structure, then just import data to recovery.
The operation is carrying out database recovery when progress, can cause table structure, table data separating, and operation is flexible.
On this basis, this example further carries out deletion index operation.
Specifically, the relative index in the big table of data volume is first deleted before data importing, while being led in data
Original index is added after entering, mass data thus can be saved and import the time.
On this basis, this example is further turned off daily record.
Specifically, closing data logging function, opened again after the completion of migration, it is ensured that in backup, recovery process, do not have
There is external interference, it is ensured that the uniformity of data..
On this basis, this example is further turned off master-slave synchronisation.
Specifically, closing master-slave synchronisation, it is then turned on after the completion of migration, it is ensured that in backup, recovery process, without outer
Disturb on boundary, it is ensured that the uniformity of data.
Therefore, this programme can be flexible to carry out a point table backup according to different business diagnosis, and can be significantly
Backup efficiency is improved, BACKUP TIME is reduced.
General principle, principal character and the advantages of the present invention of the present invention has been shown and described above.The technology of the industry
Personnel are it should be appreciated that the present invention is not limited to the above embodiments, and the simply explanation described in above-described embodiment and specification is originally
The principle of invention, without departing from the spirit and scope of the present invention, various changes and modifications of the present invention are possible, these changes
Change and improvement all fall within the protetion scope of the claimed invention.The claimed scope of the invention by appended claims and its
Equivalent thereof.
Claims (9)
1. a kind of efficient backup method of mysql databases, it is characterised in that this method passes through a point table backup mysql databases.
2. the efficient backup method of mysql databases according to claim 1, it is characterised in that methods described includes：
All tables of whole database are classified；
Backed up, recovered in units of table in units of table：
The backup of table structure is separated with data backup.
3. the efficient backup method of mysql databases according to claim 2, it is characterised in that first recover table in migration
Structure, carries out data importing respectively afterwards.
4. the efficient backup method of mysql databases according to claim 2, it is characterised in that first will before data importing
Relative index in the big table of data volume is deleted, and data add original index again after importing.
5. the efficient backup method of mysql databases according to claim 2, it is characterised in that before backing up or recovering first
Data logging function is closed, is opened in the completed.
6. the efficient backup method of mysql databases according to claim 2, it is characterised in that before backing up or recovering first
Master-slave synchronisation is closed, is then turned in the completed.
7. the efficient backup method of mysql databases according to claim 2, it is characterised in that realized by shell scripts
Divide table backup mysql databases, and using linux crontab timed tasks, corresponding shell pin are performed by set order
This.
8. the efficient backup method of mysql databases according to claim 1, it is characterised in that after Backup end, to target
Database carries out the full table statistics in full storehouse, and statistics is write in log texts, is contrasted during for database recovery.
9. the efficient backup method of mysql databases according to claim 2, it is characterised in that utilize Mysql's
Mysqldump command statements are backed up to object table, the table structure backup of target database.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN201710305107.0A CN107247639A (en) | 2017-05-03 | 2017-05-03 | A kind of efficient backup method of mysql databases |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN201710305107.0A CN107247639A (en) | 2017-05-03 | 2017-05-03 | A kind of efficient backup method of mysql databases |
Publications (1)
| Publication Number | Publication Date |
|---|---|
| CN107247639A true CN107247639A (en) | 2017-10-13 |
Family
ID=60017243
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN201710305107.0A Pending CN107247639A (en) | 2017-05-03 | 2017-05-03 | A kind of efficient backup method of mysql databases |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN107247639A (en) |
Cited By (1)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN110209534A (en) * | 2019-06-14 | 2019-09-06 | 四川长虹电器股份有限公司 | The system and method for automated back-up mysql database |
Citations (5)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN101110026A (en) * | 2007-08-03 | 2008-01-23 | 北京握奇数据系统有限公司 | Resource limited equipment and data access method |
| CN101853287A (en) * | 2010-05-24 | 2010-10-06 | 南京高普科技有限公司 | Data compression quick retrieval file system and method thereof |
| CN105022743A (en) * | 2014-04-24 | 2015-11-04 | 中兴通讯股份有限公司 | Index management method and index management device |
| CN105183852A (en) * | 2015-09-08 | 2015-12-23 | 网易（杭州）网络有限公司 | Database migration method and device |
| CN106445738A (en) * | 2016-09-13 | 2017-02-22 | 郑州云海信息技术有限公司 | Database backup method and device |
-
2017
- 2017-05-03 CN CN201710305107.0A patent/CN107247639A/en active Pending
Patent Citations (5)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN101110026A (en) * | 2007-08-03 | 2008-01-23 | 北京握奇数据系统有限公司 | Resource limited equipment and data access method |
| CN101853287A (en) * | 2010-05-24 | 2010-10-06 | 南京高普科技有限公司 | Data compression quick retrieval file system and method thereof |
| CN105022743A (en) * | 2014-04-24 | 2015-11-04 | 中兴通讯股份有限公司 | Index management method and index management device |
| CN105183852A (en) * | 2015-09-08 | 2015-12-23 | 网易（杭州）网络有限公司 | Database migration method and device |
| CN106445738A (en) * | 2016-09-13 | 2017-02-22 | 郑州云海信息技术有限公司 | Database backup method and device |
Non-Patent Citations (1)
| Title |
|---|
| WOSY836837: "MYSQL引入索引后 插入大量数据会很慢", 《百度经验》 * |
Cited By (1)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN110209534A (en) * | 2019-06-14 | 2019-09-06 | 四川长虹电器股份有限公司 | The system and method for automated back-up mysql database |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| CN105868343B (en) | Database migration method and system | |
| WO2019154394A1 (en) | Distributed database cluster system, data synchronization method and storage medium | |
| CN106445738B (en) | Database backup method and device | |
| CN105843707B (en) | Database quick recovery method and equipment | |
| US20070083563A1 (en) | Online tablespace recovery for export | |
| CN106777270A (en) | A kind of Heterogeneous Database Replication parallel execution system and method based on submission point time line locking | |
| CN105955970A (en) | Log analysis-based database copying method and device | |
| CN105049260B (en) | Blog management method and device | |
| CN103870600B (en) | A kind of tables of data backup method and device based on Oracle databases | |
| CN103914458A (en) | Mass data migration method and device | |
| CN101673374B (en) | Bill processing method and device | |
| CN110209728A (en) | A kind of Distributed Heterogeneous Database synchronous method, electronic equipment and storage medium | |
| CN106802905B (en) | Collaborative data exchange method of isomorphic PLM system | |
| DE112010003262T5 (en) | Synchronizing replicated sequential access storage components | |
| CN109508346B (en) | Cascade synchronous control method and system for DDL operation | |
| CN104021200A (en) | Data synchronizing method and device of database | |
| CN103984309A (en) | Cigarette production system with disaster tolerance function and disaster tolerance exercise method thereof | |
| CN103631967A (en) | Processing method and device for data table with autoincrement identification fields | |
| CN104765651A (en) | Data processing method and device | |
| CN115438122A (en) | Data heterogeneous synchronization system | |
| CN106155838A (en) | A kind of database back-up data restoration methods and device | |
| CN107247639A (en) | A kind of efficient backup method of mysql databases | |
| CN107193688A (en) | Data list structure amending method and device | |
| CN103971066A (en) | Verification method for integrity of big data migration in HDFS | |
| CN109101368A (en) | A kind of data processing method and 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 | ||
| RJ01 | Rejection of invention patent application after publication |
Application publication date: 20171013 |
|
| RJ01 | Rejection of invention patent application after publication |