Backup and recovery of MySQL Database based on innobackupex
Xtrabackup, an open source software developed by percona, is an open source alternative to innodb hot backup tool ibbackup (commercial software for a fee). Xtrabackup consists of xtrabackup and innobackupex, where the xtrabackup tool is used to back up the tables of the innodb and xtraDB engines, and the innobackupex tool is used to back up the tables of the myisam and innodb engines. This article describes how to use the innobackupex tool to do full and incremental backups.
I. installation
The mysql database has been installed before installing Xtrabackup, and the Xtrabackup version is 2.2.9. If you want to use another version, please refer to: https://www.percona.com/downloads/XtraBackup.
For convenience, install directly using 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
2. Complete 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/
Restore:
Shut down 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 million 14-16-50
Innobackupex-- defaults-file=/u01/app/mysql/my.cnf-- user=root-- password=***-- copy-back / u01/app/mysql/backup/2016-11-10 million 14-16-50
# apply binlog log
[root@mysql 2016-11-10 14-16-50] # cat xtrabackup_binlog_info
Mysql-bin.000012 311
Mysqlbinglog / u01/app/mysql/log/mysql-bin.000012-- start-position=311 | mysql- u root-paired pictures *
Modify permissions
Cd / u01/app/mysql/data
Chown-R mysql:dba
Start the database
/ etc/init.d/mysql start
III. 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 ())
-- do everything 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/
-- then make an incremental backup
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 million 14-46-43-- incremental-dir=/u01/app/mysql/backup/2016-11-10 million 14-49-09
-- apply backup
Innobackupex-- defaults-file=/u01/app/mysql/my.cnf-- user=root-- password=***-- use-memory=100m-- apply-log / u01/app/mysql/backup/2016-11-10 million 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-paired pictures *
