Backup and Recovery for MySQL

Backup and Recovery for MySQL

For some enterprises, the data is the core element of the enterprise, once the data loss will be a fatal blow to the enterprise, so as a DBA to regularly do the backup work for the data in case of failure, you can immediately recover, so as to ensure the integrity of enterprise data. Depending on the requirements, we can divide the following types of backup:

Backup type for MySQL

1. Depending on whether the MySQL server is online, the backup type can be divided as follows:

Hot spare: Using this method, the MySQL server can be online, and read and write operations are not affected

Win Bei: This way MySQL server can also be online, read operation is not affected, but the write operation is affected, need to manually read the lock (shared lock).

Cold: This is the way the MySQL server must stop running, i.e. offline backup, so that read and write operations are unaffected

2, according to the backup is the table of data or files, can be divided into:

Physical backup: This is a way to copy files directly (that is, using the CP command)

Logical backup: This is the way to export data to a text file (with the data redirection feature) so you can compile directly with the editor

3, according to the collection range of backup data, this can be divided into:

Full backup: Just back up all the data

Incremental backup: Backs up only data that has changed since the last full or incremental backup

Differential backup: Backs up only the data that has changed since the last full backup, which is slower compared to incremental backups, but restores faster.

These kinds of backup types have no correspondence with each other and can be used in combination with each other according to requirements.

Features of physical and logical backups

Physical Backup: This mode of backup is fast, and the restore is relatively fast, belonging to the file system level

Logical backup: This is the only way to back up data, so you need to manually establish the table structure during the restore process, which is slow to back up and loses the precision of floating point numbers. The data that is backed up takes up more storage space and can be compressed to save space. However, because the data is stored directly in this document, it can be processed directly using the Text tool, and its portability ability is strong. However, it is not appropriate to make a full backup of the large database.

Comparison of incremental and differential backups is

In the above has been described in the concept of incremental backup and differential backup, if still do not understand, here I give a more intuitive graphical interface, we can intuitively see, the difference between the two ways.

650) this.width=650; "title=" Beifei.png "alt=" wkiol1r6_qstctr-aad3kcex7os982.jpg "src=" http://s3.51cto.com/wyfs02/ M01/54/2b/wkiol1r6_qstctr-aad3kcex7os982.jpg "/>

Backed-up objects

In general, we need to back up the following data:

Data: This is not much to say, this is the core thing to backup

Binary log files: If the file that stores data on the MySQL server is corrupted, then the binary log file can be used to recover the data after the backup. Therefore, this generally we also want to back up, generally we will use the binary log with incremental or differential backup way to back up.

Transaction log files: If a transaction thread is running and the transaction log file is full, the immediate transaction is not committed, the transaction log synchronizes the data to disk, so there is likely to be half of the transaction in the transaction log (that is, only a subset of the data is written to disk). Once the server is down, you can use the transaction log to undo it. Keep data unaffected. Therefore, the transaction log is generally backed up.

MySQL configuration file: Because the MySQL configuration file can define various server variables, therefore, different MySQL server may define the same properties, therefore, generally we also want to make a backup of the MySQL configuration file.

Backup policy

In general, we need one months to do a full backup, make an incremental or differential backup once a week, or make a full backup once a week, or make an incremental or differential backup every day.

Therefore, it is common to use full backup + incremental or full backup + differential backup to combine backup data and other important files (mentioned above)

Backup tools

The backup workers we need have the following:

650) this.width=650; "title=" Beifengju.png "src=" http://s3.51cto.com/wyfs02/M01/54/2C/wKioL1R7CqyBZ1yqAAIaWJ_ Xvpc622.jpg "alt=" Wkiol1r7cqybz1yqaaiawj_xvpc622.jpg "/>

Where mysqldump is a logical backup tool for MySQL clients, it supports warm backups to the MyISAM storage engine, and supports hot standby for INNODB storage engines

Select INTO OutFile is a logical backup tool that requires the table structure to be created manually

Mysqlhotcopy is a physical backup tool that supports warm backups only for the MyISAM storage engine

Snapshot: Using the LVM snapshot feature for almost hot-standby functions

Third-party backup tools:

Ibbackup is a commercial backup tool

Xtrabackup is an open source Backup tool

Full backup of data with mysqldump tools

Because Mysqldump is a logical backup tool, backups are slow, so the volume of data cannot be too large when you use the tool to back up data.

mysqldump syntax format for use

1. Back up a single database or one of the other tables

Mysqldump db_name [Tb1_name] [Tb2_name]

Description: Use this method to manually create a database name or table structure

2. Backing up multiple databases

MySQL Options ...

Options

--all-databases: Backing up all databases without creating a database name

--databases Db1_name,db2_name. : Backing up multiple specified databases without creating a database name

--lock-all-tables: Lock All tables

--flush-logs: Execution Log Flush

--MASTER-DATA={0|1|2}

0: Indicates that binary log files and paths are not logged

1: Record location in Chnage MASTER to, can be used to start the server directly after recovery;

2: The position is recorded as change MASTER to, but the line is commented out by default;

--events: The statement that backs up the creation event in the database

--routines: Backing up storage functions and stored procedures

--triggers: Backup Trigger

If the table type in the specified library is InnoDB, you can use--single-transaction to start the hot standby;

This article is from the "Linux Learning path" blog, so be sure to keep this source http://xslwahaha.blog.51cto.com/4738972/1584802

Backup and Recovery for MySQL