Innobackupex implementation of MySQL database backup and recovery

Xtrabackup is an open source software developed by Percona, which is an open source alternative to the InnoDB hot standby tool Ibbackup (paid commercial software). Xtrabackup consists of parts: Xtrabackup and Innobackupex, where xtrabackup tools are used to back up the InnoDB and XTRADB engine tables While the Innobackupex tool is used to back up the tables of the MyISAM and InnoDB engines, this article describes how to do full and incremental backups with the Innobackupex tool.

First, installation

The MySQL database has been installed before installing Xtrabackup, the Xtrabackup version is 2.2.9, if you want to use a different version, please refer to: Https://www.percona.com/downloads/XtraBackup.

For convenience, install directly with Yum:

Yum Install https://www.percona.com/downloads/XtraBackup/XtraBackup-2.2.9/binary/redhat/6/x86_64/ percona-xtrabackup-2.2.9-5067.el6.x86_64.rpm

Second, full backup and restore of the database

Innobackupex--defaults-file=/u01/app/mysql/my.cnf--user=root--password=***--port=3306--socket=/u01/app/mysql/ run/mysql.sock/u01/app/mysql/backup/

Restores:

To close the database:

Innobackupex--defaults-file=/u01/app/mysql/my.cnf--user=root--password=***--use-memory=100m--apply-log/u01/app /mysql/backup/2016-11-10_14-16-50

Innobackupex--defaults-file=/u01/app/mysql/my.cnf--user=root--password=***--copy-back/u01/app/mysql/backup/ 2016-11-10_14-16-50

#应用binlog日志

[email protected] 2016-11-10_14-16-50]# cat Xtrabackup_binlog_info

mysql-bin.000012311

mysqlbinglog/u01/app/mysql/log/mysql-bin.000012--start-position=311 |mysql-u root-p***

Modify Permissions

Cd/u01/app/mysql/data

Chown-r MYSQL:DBA

Start the database

/etc/init.d/mysql start

Three, incremental backup and restore

--Incremental backup

CREATE TABLE t_time (ID int auto_increment primary key,t_time datetime);

Insert into T_time (t_time) VALUES (now ());

--Make full preparation first

Innobackupex--defaults-file=/u01/app/mysql/my.cnf--user=root--password=***--port=3306--socket=/u01/app/mysql/ run/mysql.sock/u01/app/mysql/backup/

--Do incremental backups again

Innobackupex--defaults-file=/u01/app/mysql/my.cnf--user=root--password=***--incremental-basedir=/u01/app/mysql /backup/2016-11-10_14-46-43--incremental/u01/app/mysql/backup/

--Steps for incremental recovery

Innobackupex--apply-log--redo-only/u01/app/mysql/backup/2016-11-10_14-46-43

Innobackupex--apply-log--redo-only/u01/app/mysql/backup/2016-11-10_14-46-43--incremental-dir=/u01/app/mysql/ backup/2016-11-10_14-49-09

--Application Backup

Innobackupex--defaults-file=/u01/app/mysql/my.cnf--user=root--password=***--use-memory=100m--apply-log/u01/app /mysql/backup/2016-11-10_14-46-43

Innobackupex--defaults-file=/u01/app/mysql/my.cnf--user=root--password=***--copy-back/u01/app/mysql/backup/ 2016-11-10_14-46-43

mysqlbinglog/u01/app/mysql/log/mysql-bin.000014--start-position=482 |mysql-u root-p***

This article is from the "three countries Cold jokes" blog, please be sure to keep this source http://myhwj.blog.51cto.com/9763975/1872531

Innobackupex implementation of MySQL database backup and recovery