原理：
XtraBackup对Innodb的备份之所以是热备，无需锁表，是基于Innodb自身的崩溃恢复机制，它首先复制所有的Innodb数据文件，这样复制出来的文件肯定是不一致的，然后对每个文件进行崩溃恢复处理，最终达到一致。就和MySQL在启动Innodb的时候一样，会通过比较数据文件头和redo log文件头信息来检查数据是否是一致的，如果不一致就尝试通过前滚（把redo log中所有提交的事务写入数据文件）和回滚（从数据文件中撤销所有redo log中未提交的事务引起的修改）来使数据达到最终一致。
XtraBackup在启动的时候会记录一个LSN（log sequence number），然后就把所有的Innodb数据文件复制出来，这样复制出来的数据文件是不一致的，但是XtraBackup会在后台运行一个进程把所有对redo log file的修改记录下来，只要有了这个数据，就能进行崩溃恢复。只所以要额外记录下来，是因为MySQL自身的redo log file是可重用的。
以上的操作是由xtrabackup二进制程序（比如xtrabackup_55）完成的，如果使用innobackupex 脚本，刚才的步骤完成以后，innobackupex就会去备份MyISAM表和.frm文件，这时要保证数据的一致性就会先锁表了，通过FLUSH TABLES WITH READ LOCK命令锁表然后把文件复制出来，再释放掉这个锁。
在恢复数据的时候，要经过prepare（recovery）和restore两个步骤。在prepare结束以后，Innodb的表恢复到了复制Innodb文件结束的时间点，这个时间点也就是锁表复制MyISAM表的起点，所以最终数据是一致的。一般我们在恢复的时候执行两次prepare，是因为第二次prepare会帮助我们生成redo log文件，从而加快MySQL数据库启动的速度。 SourceByrd's Weblog-https://note.t4x.org/database/xtrabackup-restore-test/
一、操作系统环境：
|
0 1 2 3 4 5 |
$ cat /etc/redhat-release CentOS Linux release 7.4.1708 (Core) $ uname -a Linux MySQL_01.t4x.org 3.10.0-693.el7.x86_64 #1 SMP Tue Aug 22 21:09:27 UTC 2017 x86_64 x86_64 x86_64 GNU/Linux $ ip addr | grep 1.1.1 inet 1.1.1.141/24 brd 1.1.1.255 scope global ens33 |
二、XtraBackup安装：
|
0 1 2 3 4 |
$ wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.8/binary/tarball/percona-xtrabackup-2.4.8-Linux-x86_64.tar.gz # tar zxf percona-xtrabackup-2.4.8-Linux-x86_64.tar.gz $ mv percona-xtrabackup-2.4.8-Linux-x86_64 /opt/ $ ln -s /opt/percona-xtrabackup-2.4.8-Linux-x86_64/ /usr/local/xtrabackup $ for i in `ls -l /usr/local/xtrabackup/bin/ | awk '{print $9}' | grep -v "^$"`;do ln -s /usr/local/xtrabackup/bin/$i /usr/local/bin/$i; done |
三、XtraBackup全备：
1.试验数据
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 |
> create database test; > show create database test; +----------+---------------------------------------------------------------+ | Database | Create Database | +----------+---------------------------------------------------------------+ | test | CREATE DATABASE `test` /*!40100 DEFAULT CHARACTER SET utf8 */ | +----------+---------------------------------------------------------------+ > create table t1(id int(4) NOT NULL AUTO_INCREMENT, name char(15) NOT NULL, PRIMARY KEY (`id`)); > show create table t1\G *************************** 1. row *************************** Table: t1 Create Table: CREATE TABLE `t1` ( `id` int(4) NOT NULL AUTO_INCREMENT, `name` char(15) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 > INSERT INTO t1(name) VALUES('Tom'); > INSERT INTO t1(name) VALUES('John'); > INSERT INTO t1(name) VALUES('Zane'); > select * from t1; +----+------+ | id | name | +----+------+ | 1 | Tom | | 2 | John | | 3 | Zane | +----+------+ |
2.全备检查
|
0 1 2 3 4 5 6 7 8 |
$ innobackupex --defaults-file=/etc/my.cnf --no-timestamp --user root --password admin --socket=/usr/local/mysql/tmp/mysql.sock /backup/$(date +%F)-bak #备份 $ cd /backup/2018-05-11-bak/ $ cat xtrabackup_checkpoints backup_type = full-backuped from_lsn = 0 to_lsn = 1232489326 last_lsn = 1232489342 compact = 0 recover_binlog_info = 0 |
恢复方法两种：SourceByrd's Weblog-https://note.t4x.org/database/xtrabackup-restore-test/
方法1：
方法2：
SourceByrd's Weblog-https://note.t4x.org/database/xtrabackup-restore-test/
四、XtraBackup增量备份：
1.全量备份
|
0 1 2 3 4 5 6 7 8 9 10 |
$ innobackupex --defaults-file=/etc/my.cnf --no-timestamp --user root --password admin --socket=/usr/local/mysql/tmp/mysql.sock /backup/$(date +%F)-bak > INSERT INTO t1(name) VALUES('Jim'); > INSERT INTO t1(name) VALUES('Koala'); $ cat 2018-05-11-bak/xtrabackup_checkpoints backup_type = full-backuped from_lsn = 0 to_lsn = 1232503175 last_lsn = 1232503191 compact = 0 recover_binlog_info = 0 |
2.增量备份
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 |
> INSERT INTO t1(name) VALUES('Ruby'); > INSERT INTO t1(name) VALUES('Kate'); $ innobackupex --defaults-file=/etc/my.cnf --no-timestamp --user root --password admin --socket=/usr/local/mysql/tmp/mysql.sock --incremental --incremental-basedir=/backup/2018-05-11-bak/ /backup/2018-05-11-bak-01 $ cat 2018-05-11-bak-01/xtrabackup_checkpoints backup_type = incremental from_lsn = 1232503175 to_lsn = 1232505623 last_lsn = 1232505639 compact = 0 recover_binlog_info = 0 > INSERT INTO t1(name) VALUES('Zhangfei'); > INSERT INTO t1(name) VALUES('Liubei'); > select * from t1; +----+----------+ | id | name | +----+----------+ | 1 | Tom | | 2 | John | | 3 | Zane | | 4 | Jim | | 5 | Koala | | 6 | Koala | | 7 | Jim | | 8 | Kate | | 9 | Ruby | | 10 | Zhangfei | | 11 | Liubei | +----+----------+ $ innobackupex --defaults-file=/etc/my.cnf --no-timestamp --user root --password admin --socket=/usr/local/mysql/tmp/mysql.sock --incremental --incremental-basedir=/backup/2018-05-11-bak-01 /backup/2018-05-11-bak-02 $ cat 2018-05-11-bak-02/xtrabackup_checkpoints backup_type = incremental from_lsn = 1232505623 to_lsn = 1232506671 last_lsn = 1232506680 compact = 0 recover_binlog_info = 0 |
五、XtraBackup增量恢复：
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 |
> drop table t1; $ innobackupex --defaults-file=/etc/my.cnf --user root --password admin --socket=/usr/local/mysql/tmp/mysql.sock --apply-log --redo-only /backup/2018-05-11-bak $ innobackupex --defaults-file=/etc/my.cnf --user root --password admin --socket=/usr/local/mysql/tmp/mysql.sock --apply-log --redo-only /backup/2018-05-11-bak --incremental-dir=/backup/2018-05-11-bak-01 $ innobackupex --defaults-file=/etc/my.cnf --user root --password admin --socket=/usr/local/mysql/tmp/mysql.sock --apply-log --redo-only /backup/2018-05-11-bak --incremental-dir=/backup/2018-05-11-bak-02 $ innobackupex --defaults-file=/etc/my.cnf --user root --password admin --socket=/usr/local/mysql/tmp/mysql.sock --apply-log /backup/2018-05-11-bak $ /etc/init.d/mysqld stop $ cd /data/3306 $ mv data databk01 $ mv /backup/2018-05-11-bak . $ mv 2018-05-11-bak/ data $ chown -R mysql.mysql data > select * from t1; +----+----------+ | id | name | +----+----------+ | 1 | Tom | | 2 | John | | 3 | Zane | | 4 | Jim | | 5 | Koala | | 6 | Koala | | 7 | Jim | | 8 | Kate | | 9 | Ruby | | 10 | Zhangfei | | 11 | Liubei | +----+----------+ $ cat /data/3306/data/xtrabackup_checkpoints backup_type = full-prepared from_lsn = 0 to_lsn = 1232506671 last_lsn = 1232506680 compact = 0 recover_binlog_info = 0 $ cat /backup/2018-05-11-bak/xtrabackup_checkpoints backup_type = full-backuped from_lsn = 0 to_lsn = 1232503175 last_lsn = 1232503191 compact = 0 recover_binlog_info = 0 $ cat /backup/2018-05-11-bak-01/xtrabackup_checkpoints backup_type = incremental from_lsn = 1232503175 to_lsn = 1232505623 last_lsn = 1232505639 compact = 0 recover_binlog_info = 0 $ cat /backup/2018-05-11-bak-02/xtrabackup_checkpoints backup_type = incremental from_lsn = 1232505623 to_lsn = 1232506671 last_lsn = 1232506680 compact = 0 recover_binlog_info = 0 |
参考文档：
1：https://www.percona.com/doc/percona-xtrabackup/2.1/xtrabackup_bin/xtrabackup_binary.htmlSourceByrd's Weblog-https://note.t4x.org/database/xtrabackup-restore-test/
SourceByrd's Weblog-https://note.t4x.org/database/xtrabackup-restore-test/
申明：除非注明Byrd's Blog内容均为原创,未经许可禁止转载!详情请阅读版权申明!
