MySQL database backup types and common backup tools summary

1,Database backup Types

According to the database size backup, there are four types, respectively applied to different occasions, the following brief introduction:

1.1Full Backup

This is the way most people use it to back up the entire database, including all database objects, such as user tables, system tables, indexes, views, and stored procedures. But it takes more time and space, so it's generally recommended to do a full backup once a week.

1.2Transaction Log Backups

The transaction log is a separate file that records changes to the database and only requires a small amount of time to replicate the changes that have been made to the database since the last backup. To make the database robust, it is recommended to back up the transaction log hourly or even more frequently.

1.3Differential Backup

Also called an incremental backup. It is another way to back up only part of the database, it does not use the transaction log, instead it uses a new image of the entire database. It is smaller than the initial full backup because it contains only the databases that have changed since the last full backup. It has the advantage of faster storage and recovery. It is recommended to make a differential backup every day.

1.4file Backup

The database can consist of many files on the hard disk. If the database is very large and cannot be backed up in one night, you can use a file backup to back up a portion of the database nightly. This kind of backup is not very common because the database is generally not too large to have to use multiple file stores.

The state of the database can be divided into three types:

1. Cold backup, when the database is closed, can better guarantee the integrity of the database.

2. Hot backup, the database is running, this method relies on the database's [1] log file for backup.

3. Logical backup, using software to extract data from the database and write the results to a file.

2,Introduction to Backup Tools

MySQL is divided into logical and physical backups according to the backup recovery method. Logical backup is a backup SQL statement, in the recovery of the SQL statement to perform the backup of the database to reproduce the data, physical backup is the backup data file, the comparison image Point is the CP under the data file, but the real backup time is not naturally the CP so simple.

These 2 kinds of backup each have advantages and disadvantages, in general, physical backup recovery speed is relatively fast, occupy space is relatively large, logical backup speed is relatively slow, occupy less space.

Official address: http://dev.mysql.com/doc/refman/5.6/en/backup-and-recovery.html

2.1 mysqldumpTools

Mysqldump is the backup tool that comes with MySQL, the directory is under the bin directory:/usr/local/mysql/bin/mysqldump, supports INNODB-based hot backup. However, because it is a logical backup, the speed is not very fast, suitable for small backup data scenarios. Mysqldump full backup + binary log enables point-in-time recovery.

For MyISAM storage Engine table, only use warm backup, this time to prevent data write, so first add read lock. This time can also enter the database manual read lock, but this is more troublesome, can be in the mysqldump tool directly have a lock option, is--lock-all-tables, such as mysqldump--databases test-- Lock-all-tables--flush-logs >/tmp/backup_test_ ' date+%f-%h-%m '. sql.

If you are backing up a single table, add the table name directly after the library name test.

For the InnoDB Storage engine table, you can hot standby, do not have to lock the database operation, add an option can be hot backup,--single-transaction, for example: mysqldump--databases Test-- Single-transaction--flush-logs--master-data=2>/tmp/backup_test_ ' Date +%f-%h-%m '. sql.

PS: Note that when recovering, remember to close the binary log:

Mysql> set sql_log_bin=0;

Because this is based on the logical backup method, so the execution SQL will insert the data, will be logged into the binary log inside, because this thing to recover, so the inserted binary log basically no meaning, can be shut down, shorten the recovery time.

2.2based onLVMSnapshot backup

In physical backup, there is a physical backup based on the file system (LVM snapshot), or you can directly use the tar and other commands to package the entire database directory, but these can only be cold backup, different storage engine backup is not the same, MyISAM automatically back to the table level, InnoDB can only back up the entire database if it does not open a separate table space.

The following is an introduction to using the snapshot feature of LVM for security purposes, first to apply a read lock on the database

Mysql>flush TABLES with READ LOCK;

Refresh binary logs for Point-in-time recovery

Mysql>flush LOGS;

Then create the snapshot volume

Lvcreate–l 1g–s–n Data-snap–p–r/dev/myvg/mydata

Finally enter the database to release the read lock

UNLOCK TABLES;

Mount a snapshot volume for backup

Mount–r/dev/myvg/data-snap/mnt/snap

Then, the files under the/mnt/snap are packaged and backed up

When you restore, close mysqld, then back up the binary log and restore the original backup file, and then restore it to the wrong point in time via the binary log (do not forget to temporarily turn off the binary log when you restore the point in time by binary)

Between 2010 and 2012, MySQL database was deployed in the Amazon Cloud environment, they provided LVM snapshots, very convenient, using LVM snapshot, under the Amazon million Gigabit network, recovery is very fast.

2.3 TarPackage Backup

0, prepare the first data backup from the library, temporarily lock all tables, open windows 1

Mysql> flush tables with read lock;

Query OK, 0 rows Affected (0.00 sec)

Mysql> Show master status;

+------------------+----------+--------------+------------------+

| File | Position | binlog_do_db | binlog_ignore_db |

+------------------+----------+--------------+------------------+

|   mysql-bin.000003 |              194554 |                  | |

+------------------+----------+--------------+------------------+

1 row in Set (0.00 sec)

PS: This window cannot exit, keep until you know that the tar is finished.

--Reopen a shell window, go to data file directory tar Package
Open Window 2
[Email protected]_21_11 data]# TAR-ZCVF mysqla1.tar.gz Mysqla
TAR-ZCVF mysqla1.tar.gz Mysqla Compression complete

Switch to Window 1 , perform an unlock command
Mysql> Unlocktables;
Query OK, 0 rows Affected (0.00 sec)

Mysql>

Copy the tar package to another MySQL library server, overwrite the data directory, and then restart the MySQL database service.

2.4 Perconaprovided byXtrabackupTools

Support InnoDB physical hot backup, support full backup, incremental backup, and very fast, support INNODB storage caused by the migration of data between different databases, support copy mode of slave backup restore backup recovery

, in order for Xtrabackup to support more feature extensions, you can set up a stand-alone table space, open the innodb_file_per_table feature, and enable a separate table backup when enabled.

As: Http://www.percona.com/software/percona-xtrabackup,

Reference article address: http://blog.csdn.net/mchdba/article/details/11563027

Support Online hot standby and recovery

Large data volumes, backup recovery is faster than

Xtrabackup can achieve full backups, incremental backups, and partial backups.

Xtrabackup Backup principle

Xtrabackup is based on the InnoDB crash-recovery function. It replicates the data file of InnoDB, because it does not lock the table, the copied information is inconsistent, use crash-recovery when recovering, make the data recovery consistent.

InnoDB maintains a redo log, also known as transaction log, the transaction log, which contains all the changes to the InnoDB data. When InnoDB starts, it checks the data file and the transaction log, and it does a two-step operation:

Xtrabackup when backing up, a page by page copy of InnoDB data, and do not lock the table, at the same time, xtrabackup there is another thread monitoring transactions log, once the log changes, the changed log pages copied away. Why the rush to copy away? Because the transactions log file is limited in size, when it is full, it starts from scratch, so the new data may overwrite the old data.

During the prepare process, Xtrabackup uses the copied transactionslog to crash recovery the backed up InnoDB data file.

 --------------------------------------------------------------------------------------------- -------------------
< Copyright , the article is allowed to reprint, but must be linked to the source address, otherwise investigate legal liability !>
Original Blog Address: http://blog.itpub.net/26230597/viewspace-1460065/
Hara Douglas fir (MCHDBA)
---------------------------------------------------------------------------------------------------- ------------

MySQL database backup types and common backup tools summary