MySQL Large database backup

The Xtrabackup tool is a backup tool that makes data available to InnoDB and supports full-light and incremental backups. Backup of MyISAM database is not supported

But Xtrabackup has a tool innobackupex that can support MyISAM, but it needs to be locked when it's backed up.

(flush tableswith read lock) reads the lock, (unlock tables) interprets the lock.

1. Using Innobackupex Backup

   
   

   First, you need to specify the [mysqld] in the my.cnf datadir

   Innodb_log_file_size = 5M

such as:datadir=/usr/local/mysql

2. Entire database backup

Innobackupex--defaults-file=/etc/my.cnf--user=root/home/backup

3. Backup to a single database

Innobackupex--defaults-file=/etc/my.cnf--user=root--database=backup_test/home/backup

4. Back up and compress

Innobackupex--defaults-file=/etc/my.cnf--user=root--stream=tar |gzip >/home/backup/' Date +%F-%T '. tar.gz

5. Entire data restore

1) Stop MySQL

/etc/init.d/mysqld stop

2) Backup Data directory

Mv/usr/local/mysql/usr/local/mysql.bak

3) Re-create the directory

Mkdir-p/usr/local/mysql

4) Change the file to belong to

Chown Mysql:mysql/usr/local/mysql

5) Back up data, 2 steps

Innobackupex--defaults-file=/etc/my.cnf--user=root--apply-log/home/backup/2015-11-29_16-50-36

Innobackupex--defaults-file=/etc/my.cnf--copy-back/home/backup/2015-11-29_16-50-36

6. Incremental backup

An incremental backup is an operation performed on the basis of the last full-scale backup.

Innobackupex--defaults-file=/etc/my.cnf--user=root--incremental-basedir=/home/backup/2015-11-29_16-50-36-- Incremental/home/backup

7. View the files for the backup record

Cat Xtrabackup_checkpoints

Backup_type = full-prepared

FROM_LSN = 0

LAST_LSN = 22223450

Compact = 0

8. Incremental backup Restore

Innobackupex--defaults-file=/etc/my.cnf--user=root--apply-log--redo-only/home/backup/2015-11-29_16-50-36

Innobackupex--defaults-file=/etc/my.cnf--user=root--apply-log--redo-only/home/backup/2015-11-29_16-55-49-- incremental-dir=/home/backp/2015-11-29_14-30-37

Innobackupex--defaults-file=/my.cnf--user=root--apply-log/data/backup/2015-11-29_16-50-36

And then the fifth step goes aside.

Reference http://blog.51yip.com/mysql/1650.html

Http://www.cnblogs.com/gomysql/p/3650645.html

This article is from "Grasshopper Boy" blog, please make sure to keep this source http://zxlwz.blog.51cto.com/6952946/1782483

MySQL Large database backup