Mysql Data backup solution

Source of this article: http://www.lustlost.com /? P = 101

Mysql supports logical backup and physical backup Based on Backup recovery methods.

Logical backup is a backup SQL statement. The backup SQL statement is executed during restoration to reproduce the database data.

Physical backup is used to back up data files. Compared to the data files under cp, physical backup is not as simple as cp.

The two types of backup have their own advantages and disadvantages. Generally, physical Backup recovery is faster, occupies a large space, logical backup is slower, and occupies less space.

The following three common filing methods are introduced:

Mysqldump Backup Tool

Mysqldump is a backup tool provided by mysql, so it is also the most commonly used backup tool for mysql databases. InnoDB-based hot backup is supported. However, because it is a logical backup, it is not very fast and suitable for scenarios where the backup data volume is small.

Mysqldump full backup + binary log-> time point recovery

Warm backup:

When using the MyISAM engine, you can only use warm backup. In this case, to prevent data writing, add a read lock first.

At this time, you can enter the database to manually add a read lock. This is troublesome. There is a lock option in mysqldump.

Mysqldump -- databases mydatabase -- lock-all-tables -- flush-logs>/tmp/backup-'date + % F-% H-% M'. SQL

If you back up a table, you only need to add the table name after the database name.

Note that the -- flush-logs option should be added to restore the backup file, and then restore the time point based on the binary log.

Restoration Method of Time Point

Mysqlbinlog mysql-bin.000000x>/tmp/PointTime. SQL

Run the mysql command to import the SQL script.

Hot Standby:

If the InnoDB engine is used, you do not need to lock the database. You can add an option to perform Hot Backup: -- single-transaction

Mysqldump -- databases mydb -- single-transaction -- flush-logs -- master-data = 2>/tmp/backup-'date + % F-% H-% M'. SQL

Notes

Disable binary logs at recovery time

Mysql> set SQL _log_bin = 0;

This is based on the logic backup method. When the log is restored, SQL statements are executed to insert data. However, it is meaningless to insert data to the log during restoration.

LVM-based Snapshot Backup

In physical backup, there is a file system-based physical backup LVM snapshot), you can also directly use commands such as tar to package. However, only cold backup can be performed.

Different storage engines have different backup levels. MyISAM can back up data to the table level, while InnoDB can only back up the entire database without enabling each table file.

The following describes how to use the snapshot feature of LVM for backup.

To ensure security, first apply a read lock to the database

Mysql> flush tables with read lock;

Refresh the binary log to facilitate time point recovery.

Mysql> flush logs;

Create a snapshot volume

Lvcreate-L 1G-s-n data-snap-p-r/dev/myvg/mydata

Finally, release the read lock in the database.

Unlock tables;

Mount snapshot volumes for backup

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

Then, package and Back Up Files under/mnt/snap.

When restoring, disable mysqld, back up the binary log, and restore the original backup file, then, use the binary log to restore to the time point when an error occurs. Do not forget to temporarily disable the binary log when the binary log is restored)

Recommended xtrabackup provided by percona)

Supports InnoDB physical hot backup, full backup, Incremental backup, and fast data migration across different databases.

To enable xtrabackup to support more function extensions, configure the functions of one file per table in InnoDB.

Add this entry to mysqld Of my. cnf: innodb_file_per_table = 1

If this option is not enabled, separate tables cannot be backed up.

However, if this option is not enabled before, you can use mysqldump to export data for a single table and enable this option. After the option is restored, it is a single table and a file.

First download xtrabackup

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

You can directly download and install the rpm package.

Xtrabackup has full backup. Incremental backup and partial backup ensure innodb_file_per_table and innodb_expand_import are enabled)

Full backup of the entire database

Innobackupex -- user = root -- password = 123456/tmp/backup

At this time, a folder named after time will be generated under the/tmp/backup Directory, which contains the backup file

Here, the backup data cannot be used for restoration, because the backup data contains transactions that have not been committed or that have not been synchronized to the data file. Here, you need to use prepare to roll back the data file to make it consistent.

Innobackupex -- apply-log/tmp/backup/dir

This command can be used to restore data only after processing is completed.

Innobackupex -- copy-back/tmp/backup/dir

To restore the time point, you still need to use binary logs.

Incremental Backup

Incremental Backup supports Innodb. For MyISAM, only full backup is supported.

Innobackupex-incremental/tmp/backup/incremental -- incremental-basedir =/tmp/backup/dir

When performing an incremental backup -- incremental-basedir indicates the directory for the last incremental Backup

If you want to restore, perform prepare processing first.

Here, we will merge the backups.

Innobackupex -- apply-log -- redo-only/tmp/backup/dir

Innobackupex -- apply-log -- redo-only/tmp/backup/dir -- incremental-dir =/tmp/backup/incremental

The last backup is used for restoration.

For differential backup, you only need to point basedir to the full backup folder each time.

Finally, let's talk nonsense: to restore time points, binary logs are required. Therefore, it is vital to back up binary logs. Unless the data during the recovery time and last backup time does not matter to you...

This article from the "lustlost-lost in desire" blog, please be sure to keep this source http://lustlost.blog.51cto.com/2600869/974762