Mysql 备份工具XtraBackup增量备份

安装：http://donald-draper.iteye.com/admin/blogs/2314832
全量备份：http://donald-draper.iteye.com/admin/blogs/2315812
准备：
创建表
DROP TABLE IF EXISTS `role`;
CREATE TABLE `role` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;
INSERT INTO `test`.`role` (`id`, `name`, `age`) VALUES ('1', 'jack', '23');
用到的命令顺序
增量备份：
innobackupex --user=donald --password=123456  /home/test/base --no-timestamp --parallel=4

innobackupex --user=donald --password=123456  --incremental /home/test/base1 --incremental-basedir=/home/test/base --no-timestamp --parallel=4

innobackupex --user=donald --password=123456  --incremental /home/test/base2 --incremental-basedir=/home/test/base1 --no-timestamp --parallel=4
备份恢复：
innobackupex --apply-log --redo-only --use-memory=100M /home/test/base
innobackupex --apply-log --redo-only --use-memory=100M /home/test/base --incremental-dir=/home/test/base1/
innobackupex --apply-log --redo-only --use-memory=100M /home/test/base --incremental-dir=/home/test/base2/
#Once you merge the base with all the increments, you can prepare it to roll back the uncommitted transactions，回滚未提交的事物
innobackupex --apply-log --use-memory=100M /home/test/base
准备恢复：
innobackupex --copy-back --rsync /home/test/base
下面是实验：
修改mysql数据目录权限：

[donald@test home]$ ls -al
total 16
drwxr-xr-x.  8 root   root    96 Aug  5 16:21 .
dr-xr-xr-x. 17 root   root  4096 Jul 26 14:32 ..
drwx------. 18 donald user  4096 Aug  5 11:38 donald=
drwxr-xr-x   5 mysql  mysql 4096 Aug  5 16:27 mysqldata
drwxr-xr-x   5 mysql  mysql  119 Jul 26 15:51 mysqldata1
drwxrwxrwx.  5 mysql  mysql 4096 Aug  5 16:19 mysqldata2
drwxrwxr-x.  2 mysql  mysql   64 Jul 26 16:05 mysqllog
drwxr-x---   4 donald user    30 Aug  5 15:00 test
[donald@test home]$ sudo chmod -R 777 mysqldata
[sudo] password for donald:
[donald@test home]$ ls -al
total 16
drwxr-xr-x.  8 root   root    96 Aug  5 16:21 .
dr-xr-xr-x. 17 root   root  4096 Jul 26 14:32 ..
drwx------. 18 donald user  4096 Aug  5 11:38 donald=
drwxrwxrwx   5 mysql  mysql 4096 Aug  5 16:27 mysqldata
drwxr-xr-x   5 mysql  mysql  119 Jul 26 15:51 mysqldata1
drwxrwxrwx.  5 mysql  mysql 4096 Aug  5 16:19 mysqldata2
drwxrwxr-x.  2 mysql  mysql   64 Jul 26 16:05 mysqllog
drwxr-x---   4 donald user    30 Aug  5 15:00 test

全量备份：
[donald@test test]$ innobackupex --user=donald --password=123456  /home/test/base --no-timestamp --parallel=4
....
xtrabackup: Creating suspend file '/home/test/base/xtrabackup_log_copied' with pid '15191'
xtrabackup: Transaction log of lsn (13194631350) to (13194631350) was copied.
160805 16:59:19  innobackupex: All tables unlocked

innobackupex: Backup created in directory '/home/test/base'
innobackupex: MySQL binlog position: filename 'mysql-bin.000001', position 120
160805 16:59:19  innobackupex: Connection to database server closed
160805 16:59:19  innobackupex: completed OK!
[donald@test test]$ cd base
[donald@test base]$ ls
backup-my.cnf  ibdata1  mysql  performance_schema  test  xtrabackup_binlog_info  xtrabackup_checkpoints  xtrabackup_info  xtrabackup_logfile
[donald@test base]$ cat xtrabackup_checkpoints
backup_type = full-backuped
from_lsn = 0
to_lsn = 13194631350
last_lsn = 13194631350
compact = 0

增量备份1：

插入数据
INSERT INTO `test`.`role` (`id`, `name`, `age`) VALUES ('2', 'mark', '24');
[donald@test base]$ innobackupex --user=donald --password=123456  --incremental /home/test/base1 --incremental-basedir=/home/test/base --no-timestamp --parallel=4
...
xtrabackup: The latest check point (for incremental): '13194631350'
xtrabackup: Stopping log copying thread.
.>> log scanned up to (13194631350)

xtrabackup: Creating suspend file '/home/test/base1/xtrabackup_log_copied' with pid '15639'
xtrabackup: Transaction log of lsn (13194631350) to (13194631350) was copied.
160805 17:03:55  innobackupex: All tables unlocked

innobackupex: Backup created in directory '/home/test/base1'
innobackupex: MySQL binlog position: filename 'mysql-bin.000001', position 120
160805 17:03:55  innobackupex: Connection to database server closed
160805 17:03:55  innobackupex: completed OK!
[donald@test base]$ cd ..
[donald@test test]$ cd base1
[donald@test base1]$ ls
backup-my.cnf  ibdata1.meta  performance_schema  xtrabackup_binlog_info  xtrabackup_info
ibdata1.delta  mysql         test                xtrabackup_checkpoints  xtrabackup_logfile
[donald@test base1]$ cat xtrabackup_checkpoints
backup_type = incremental
from_lsn = 13194631350
to_lsn = 13194631350
last_lsn = 13194631350
compact = 0

增量备份2：
插入数据
INSERT INTO `test`.`role` (`id`, `name`, `age`) VALUES ('3', 'doanld', '25');
[donald@test test]$ innobackupex --user=donald --password=123456  --incremental /home/test/base2 --incremental-basedir=/home/test/base1 --no-timestamp --parallel=4
...
xtrabackup: The latest check point (for incremental): '13194634030'
xtrabackup: Stopping log copying thread.
.>> log scanned up to (13194634030)

xtrabackup: Creating suspend file '/home/test/base2/xtrabackup_log_copied' with pid '15848'
xtrabackup: Transaction log of lsn (13194634030) to (13194634030) was copied.
160805 17:07:54  innobackupex: All tables unlocked

innobackupex: Backup created in directory '/home/test/base2'
innobackupex: MySQL binlog position: filename 'mysql-bin.000001', position 624
160805 17:07:54  innobackupex: Connection to database server closed
160805 17:07:54  innobackupex: completed OK!
[donald@test test]$ cd base2
[donald@test base2]$ ls
backup-my.cnf  ibdata1.meta  performance_schema  xtrabackup_binlog_info  xtrabackup_info
ibdata1.delta  mysql         test                xtrabackup_checkpoints  xtrabackup_logfile
[donald@test base2]$ cat xtrabackup_checkpoints
backup_type = incremental
from_lsn = 13194631350
to_lsn = 13194634030
last_lsn = 13194634030
compact = 0

备份恢复：
[donald@test test]$ innobackupex --apply-log --redo-only --use-memory=100M /home/test/base

InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy
and Percona LLC and/or its affiliates 2009-2013.  All Rights Reserved.

This software is published under
the GNU GENERAL PUBLIC LICENSE Version 2, June 1991.
.....
[notice (again)]
  If you use binary log and don't use any hack of group commit,
  the binary log position seems to be:
InnoDB: Last MySQL binlog file position 0 1371, file name mysql-bin.000019

xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 13194634923
160805 17:16:37  innobackupex: completed OK!

增量恢复1：
[donald@test Desktop]$ innobackupex --apply-log --redo-only --use-memory=100M /home/test/base --incremental-dir=/home/test/base1/
...
innobackupex: Copying '/home/test/base1/performance_schema/session_connect_attrs.frm' to '/home/test/base/performance_schema/session_connect_attrs.frm'
innobackupex: Copying '/home/test/base1/performance_schema/session_account_connect_attrs.frm' to '/home/test/base/performance_schema/session_account_connect_attrs.frm'
160805 17:44:29  innobackupex: completed OK!

增量恢复2：
[donald@test Desktop]$ innobackupex --apply-log --redo-only --use-memory=100M /home/test/base --incremental-dir=/home/test/base2/
...
innobackupex: Copying '/home/test/base2/performance_schema/events_statements_summary_by_digest.frm' to '/home/test/base/performance_schema/events_statements_summary_by_digest.frm'
innobackupex: Copying '/home/test/base2/performance_schema/session_connect_attrs.frm' to '/home/test/base/performance_schema/session_connect_attrs.frm'
innobackupex: Copying '/home/test/base2/performance_schema/session_account_connect_attrs.frm' to '/home/test/base/performance_schema/session_account_connect_attrs.frm'
160805 17:46:40  innobackupex: completed OK!

回滚事物
[donald@test Desktop]$ innobackupex --apply-log --use-memory=100M /home/test/base
...
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 13194640304
160805 17:48:19  innobackupex: completed OK!

转移mysql数据文件：
在执行此步前删除role表中的数据
[donald@test Desktop]$ cd /home/
[donald@test home]$ ls
donald=  mysqldata  mysqldata1  mysqldata2  mysqllog  test
[donald@test home]$ sudo service msyql stop
[sudo] password for donald:
Redirecting to /bin/systemctl stop  msyql.service
Failed to stop msyql.service: Unit msyql.service not loaded.
[donald@test home]$ sudo service mysql stop
Shutting down MySQL.... SUCCESS!
[donald@test home]$ mv mysqldata mysqldata3
mv: cannot move ‘mysqldata’ to ‘mysqldata3’: Permission denied
[donald@test home]$ sudo mv mysqldata mysqldata3
[donald@test home]$ ls -al
total 16
drwxr-xr-x.  8 root   root    97 Aug  5 17:50 .
dr-xr-xr-x. 17 root   root  4096 Jul 26 14:32 ..
drwx------. 18 donald user  4096 Aug  5 11:38 donald=
drwxr-xr-x   5 mysql  mysql  119 Jul 26 15:51 mysqldata1
drwxrwxrwx.  5 mysql  mysql 4096 Aug  5 16:19 mysqldata2
drwxrwxrwx   5 mysql  mysql 4096 Aug  5 17:50 mysqldata3
drwxrwxr-x.  2 mysql  mysql   64 Jul 26 16:05 mysqllog
drwxr-x---   6 donald
增量全备恢复：
[donald@test home]$ sudo innobackupex --copy-back --rsync /home/test/base

InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy
and Percona LLC and/or its affiliates 2009-2013.  All Rights Reserved.
........
innobackupex: back to '/home/mysqldata'

innobackupex: Starting to copy InnoDB log files
innobackupex: in '/home/test/base'
innobackupex: back to original InnoDB log directory '/home/mysqldata'
innobackupex: Copying '/home/test/base/ib_logfile1' to '/home/mysqldata/ib_logfile1'
innobackupex: Copying '/home/test/base/ib_logfile0' to '/home/mysqldata/ib_logfile0'
innobackupex: Finished copying back files.

160805 17:53:11  innobackupex: completed OK!

恢复mysql数据文件夹权限：
[donald@test home]$ ls -al
total 24
drwxr-xr-x.  9 root   root  4096 Aug  5 17:52 .
dr-xr-xr-x. 17 root   root  4096 Jul 26 14:32 ..
drwx------. 18 donald user  4096 Aug  5 11:38 donald=
drwxr-xr-x   5 root   root  4096 Aug  5 17:53 mysqldata
drwxr-xr-x   5 mysql  mysql  119 Jul 26 15:51 mysqldata1
drwxrwxrwx.  5 mysql  mysql 4096 Aug  5 16:19 mysqldata2
drwxrwxrwx   5 mysql  mysql 4096 Aug  5 17:50 mysqldata3
drwxrwxr-x.  2 mysql  mysql   64 Jul 26 16:05 mysqllog
drwxr-x---   6 donald user    54 Aug  5 17:07 test
[donald@test home]$ sudo chown -R mysql:mysql mysqldata
[donald@test home]$ ls -al
total 24
drwxr-xr-x.  9 root   root  4096 Aug  5 17:52 .
dr-xr-xr-x. 17 root   root  4096 Jul 26 14:32 ..
drwx------. 18 donald user  4096 Aug  5 11:38 donald=
drwxr-xr-x   5 mysql  mysql 4096 Aug  5 17:53 mysqldata
drwxr-xr-x   5 mysql  mysql  119 Jul 26 15:51 mysqldata1
drwxrwxrwx.  5 mysql  mysql 4096 Aug  5 16:19 mysqldata2
drwxrwxrwx   5 mysql  mysql 4096 Aug  5 17:50 mysqldata3
drwxrwxr-x.  2 mysql  mysql   64 Jul 26 16:05 mysqllog
drwxr-x---   6 donald user    54 Aug  5 17:07 test
[donald@test home]$ sudo service  mysql start
Starting MySQL.. SUCCESS!
查看数据库数据：
mysql> use test;
Database changed
mysql> select * from role;
+----+--------+-----+
| id | name   | age |
+----+--------+-----+
|  1 | jack   |  23 |
|  2 | mark   |  24 |
|  3 | doanld |  25 |
+----+--------+-----+
3 rows in set
总结：
注意msyql文件夹权限
注意备份恢复的第一步 the procedure is very straight-forward using the --redo-only option, starting with the base backup:添加 --redo-only选项
innobackupex --apply-log --redo-only --use-memory=100M /home/test/base
别忘了倒数第二步：
#Once you merge the base with all the increments, you can prepare it to roll back the uncommitted transactions，回滚未提交的事物
innobackupex --apply-log --use-memory=100M /home/test/base