Database backup Scenarios

Database backup Scenarios

Why do I need to back up data?

In the production environment, our database may encounter a variety of accidents resulting in data loss, probably divided into the following several.

• Hardware failure
• Software failure
• Natural disasters
• Hacker attacks
• Error operation (maximum ratio)

Therefore, in order to recover data after data loss, we need to regularly back up the data, the strategy to back up the data to be customized according to different scenarios, there are roughly a few reference values, we can customize to meet the specific circumstances of the data backup strategy

• Can tolerate how much data is lost
• How long does it take to recover data
• What data needs to be recovered

Backup type for two data

The backup type of the data is mainly divided into the following groups according to the content

• Full backup
• Partial backup

A full backup refers to backing up the entire data set ( that is, the entire database ), and a partial backup refers to the backup part of the DataSet ( For example : back up only one table )

And part of the backup is divided into the following two

• Incremental backup
• Differential backup

Incremental backup refers to data that has changed since the last backup ( incremental or complete ) ; Features: Save space, restore trouble
Differential backup refers to data features that have changed since the last full backup : Wasted space, restore is simpler than incremental backups

three MySQL How to back up your data

In the MySQl we back up the data in general there are several ways

• Hot backup
• Warm backup
• Cold backup

Hot backup refers to the database read and write operations are not affected when the database is backed up
Warm backup refers to a database read operation that can be performed while a database is being backed up, but cannot perform a write operation
Cold backup refers to the database can not read and write operations when the database is backed up, that is , the database to go offline

MySQLAlso consider whether the storage engine supports the backup in different ways

• MyISAM

Hot Spares X

Win Bei √

Cold Standby Yes

• InnoDB

Hot Standby √

Win Bei √

Cold Standby Yes

We're thinking about the data. After the database is running, you need to consider MySQL how the data in the database is backed up

Physical backup is usually through tar , cp such as the command to directly package the copy database data files to achieve the effect of backup
Logical backups are typically the use of specific tools to export data from a database and save backups (logical backups can lose data precision)

• Physical backup
• Logical backup

Four issues to consider for backup

Before customizing your backup strategy, there are a few things we need to consider

What do we need to back up? ?

In general, the data that we need to back up is divided into the following

• Data
• Binary log, InnoDB transaction log
• Code (stored procedures, stored functions, triggers, event schedulers)
• Server configuration file

Backup tools

Here we list several commonly used backup tools
mysqldump: Logical Backup tool for all storage engines, support Win Bei, full backup, partial backup, hot standby for InnoDB storage Engine
cp, tar 等归档复制工具: Physical backup tool for all storage engines, cold, full backup, partial backup
lvm2 snapshot: Almost hot standby with file System management tools for backup
mysqlhotcopy: A misnomer tool, almost cold, supports only MyISAM storage Engine
xtrabackup: A very powerful innodb/xtradb hot standby tool that supports full backup, incremental backup, percona provided by

Five design the right backup strategy

For different scenarios, we should make a different backup strategy to backup the database, in general, the backup strategy is generally the following three kinds of

• directly Cp,tar Copy Database Files
• mysqldump+ Copy Binlogs
• lvm2 Snapshot + Copy Binlogs
• Xtrabackup

Several of the above solutions for different scenarios

1. If the amount of data is small, you can copy the database file directly using the first method
2. If the amount of data is OK, you can use the second method to make a full backup of the database using Mysqldump, and then periodically back up the binary log to achieve an incremental backup effect
3. If the amount of data is general, and does not unduly affect the operation of the business, you can use a third way, using lvm2 a snapshot of the data file backup, and then regularly back up the binary log to achieve incremental backup effect
4. If the amount of data is large and does not unduly affect the operation of the business, you can use the fourth way to use xtrabackup regular xtrabackup incremental or differential backups after using a full backup
5. Comparison table of the major backup methods

Backup method	Backup speed	Recovery speed	Convenient level	Functional performance	Practical Scenarios	Engine support	Backup method
Cp	Fast	Fast	Generally, the flexibility is not high	Weak	Small amount of data	All	Cold
Mysqldump	Slow	Slow	General, can ignore the difference of storage engine	So so	Small amount of data	All	Win Bei, the InnoDB storage engine supports hot standby
LVM2 Snapshot	Fast	Fast	General, support almost hot standby, fast	So so	Backup of small and medium data volumes	All	Hot Spare
Xtrabackup	More quickly	More quickly	Implement InnoDB hot spares, storage engine requirements	Powerful	Larger-scale backup	InnoDB	Hot Spare

Design and implementation of six database backup tools

The database backup tool belongs to the Software Accessory tool and he should have the ability to schedule, by task, configurable, can be stored, and can be run for a long time.

6.1 By plan

is the execution of a specified task at a given time, and periodic execution

6.2 by Task

Refers to the database to be backed up some column operation definition, he should include the database related properties to be backed up, such as address, port, other parameters

6.3 Configurable

means that all running parameters of the tool can be modified, can be added, can be deleted

6.4 can be stored

Is the storage of a backed-up database file to a specified address, such as local or upload to a remote host

6.5 Tool Design

Develop a Windows desktop program that is attached to the supporting MSSP platform and is managed by the watchdog program. Can be deployed independently, can work according to the configuration file, and has its own working directory. and support configuration files, MongoDB database backup, for different types of databases, configuration file backup with plug-in design

6.6 Backup Methods

This time using the CP backup method, because he is simple and efficient, very suitable for the company's software products

Database backup Scenarios