xtrabackup 增量备份（InnoDB）

mysql> select * from users;+----+-----------+----------+--------------+| id | name   | password | address   |+----+-----------+----------+--------------+| 1 | zhang   | 1234   | NULL     || 2 | wang   | 4321   | 湖北武汉   || 3 | li    | 5678   | 北京海淀   || 4 | zhan   | 1234   | NULL     || 5 | wan    | 4321   | 湖北武汉   || 7 | for    | 777   | kkk     || 8 | ck    | 132   | kkk     || 9 | kk    | 567   | ddd     || 10 | 10t    | 123456  | kkkkdkdkd  || 11 | 全备份  | 1234   | full     |+----+-----------+----------+--------------+10 rows in set (0.00 sec)

[root@centos01 backup]# innobackupex --defaults-file=/etc/my.cnf --user=root --password=123456 /backup/full_data/

mysql> select * from users;+----+-----------+----------+--------------+| id | name   | password | address   |+----+-----------+----------+--------------+| 1 | zhang   | 1234   | NULL     || 2 | wang   | 4321   | 湖北武汉   || 3 | li    | 5678   | 北京海淀   || 4 | zhan   | 1234   | NULL     || 5 | wan    | 4321   | 湖北武汉   || 7 | for    | 777   | kkk     || 8 | ck    | 132   | kkk     || 9 | kk    | 567   | ddd     || 10 | 10t    | 123456  | kkkkdkdkd  || 11 | 全备份  | 1234   | full     || 12 | 第一次  | 123   | first    |+----+-----------+----------+--------------+11 rows in set (0.00 sec)

[root@centos01 backup]# innobackupex --user=root --password=123456 --defaults-file=/etc/my.cnf --incremental /backup/zl_data/ --incremental-basedir=/backup/full_data/2016-05-02_10-24-53/

mysql> select * from users;+----+-----------+----------+--------------+| id | name   | password | address   |+----+-----------+----------+--------------+| 1 | zhang   | 1234   | NULL     || 2 | wang   | 4321   | 湖北武汉   || 3 | li    | 5678   | 北京海淀   || 4 | zhan   | 1234   | NULL     || 5 | wan    | 4321   | 湖北武汉   || 7 | for    | 777   | kkk     || 8 | ck    | 132   | kkk     || 9 | kk    | 567   | ddd     || 10 | 10t    | 123456  | kkkkdkdkd  || 11 | 全备份  | 1234   | full     || 12 | 第一次  | 123   | first    || 13 | 第二次  | 456   | second    |+----+-----------+----------+--------------+12 rows in set (0.00 sec)

[root@centos01 backup]# mkdir /backup/zl_data2[root@centos01 backup]# innobackupex --user=root --password=123456 --defaults-file=/etc/my.cnf --incremental /backup/zl_data2/ --incremental-basedir=/backup/zl_data/2016-05-02_10-31-50/

[root@centos01 backup]# cd full_data/2016-05-02_10-24-53/[root@centos01 2016-05-02_10-24-53]# pwd/backup/full_data/2016-05-02_10-24-53[root@centos01 2016-05-02_10-24-53]# cat xtrabackup_checkpoints backup_type = full-backupedfrom_lsn = 0to_lsn = 24104664last_lsn = 24104674[root@centos01 2016-05-02_10-31-50]# cat xtrabackup_checkpoints backup_type = incrementalfrom_lsn = 24104664to_lsn = 24104694last_lsn = 24104694[root@centos01 2016-05-02_10-31-50]# pwd/backup/zl_data/2016-05-02_10-31-50[root@centos01 2016-05-02_10-37-12]# cat xtrabackup_checkpoints backup_type = incrementalfrom_lsn = 24104694to_lsn = 24104724last_lsn = 24104724[root@centos01 2016-05-02_10-37-12]# pwd/backup/zl_data2/2016-05-02_10-37-12

[root@centos01 backup]# innobackupex --apply-log --redo-only --defaults-file=/etc/my.cnf /backup/full_data/2016-05-02_10-24-53/

[root@centos01 backup]# innobackupex --apply-log --redo-only --defaults-file=/etc/my.cnf /backup/full_data/2016-05-02_10-24-53/ --incremental-dir=/backup/zl_data/2016-05-02_10-31-50/

[root@centos01 backup]# innobackupex --apply-log --redo-only --defaults-file=/etc/my.cnf /backup/full_data/2016-05-02_10-24-53 --incremental-dir=/backup/zl_data2/2016-05-02_10-37-12/

 此时所有的redo执做者还原在完全备份中了，所以只要还原，完全备份就可以：

[root@centos01 backup]# service mysqld stopShutting down MySQL.. SUCCESS! [root@centos01 backup]# rm -rf /alidata/server/mysql/data/*

[root@centos01 backup]# innobackupex --copy-back --defaults-file=/etc/my.cnf /backup/full_data/2016-05-02_10-24-53

[root@centos01 backup]# chown -R mysql.mysql /alidata/server/mysql/data/[root@centos01 backup]# ls -l /alidata/server/mysql/data/total 26664drwxr-xr-x. 2 mysql mysql   4096 May 2 10:46 2016-04-30_15-25-34drwxr-xr-x. 2 mysql mysql   4096 May 2 10:46 ceshi-rw-r-----. 1 mysql mysql 27262976 May 2 10:43 ibdata1drwxr-xr-x. 2 mysql mysql   4096 May 2 10:46 imagedrwxr-xr-x. 2 mysql mysql   4096 May 2 10:46 mysqldrwxr-xr-x. 2 mysql mysql   4096 May 2 10:46 performance_schemadrwxr-xr-x. 2 mysql mysql   4096 May 2 10:46 sxdrwxr-xr-x. 2 mysql mysql   4096 May 2 10:46 testdrwxr-xr-x. 2 mysql mysql   4096 May 2 10:46 test2-rw-r--r--. 1 mysql mysql    24 May 2 10:46 xtrabackup_binlog_pos_innodb-rw-r--r--. 1 mysql mysql    79 May 2 10:46 xtrabackup_checkpoints

[root@centos01 backup]# service mysqld startStarting MySQL... SUCCESS![root@centos01 backup]# mysql -uroot -p123456 -e "select * from ceshi.users;"+----+-----------+----------+--------------+| id | name   | password | address   |+----+-----------+----------+--------------+| 1 | zhang   | 1234   | NULL     || 2 | wang   | 4321   | 湖北武汉   || 3 | li    | 5678   | 北京海淀   || 4 | zhan   | 1234   | NULL     || 5 | wan    | 4321   | 湖北武汉   || 7 | for    | 777   | kkk     || 8 | ck    | 132   | kkk     || 9 | kk    | 567   | ddd     || 10 | 10t    | 123456  | kkkkdkdkd  || 11 | 全备份  | 1234   | full     |+----+-----------+----------+--------------+

 好像失败了！！！

mysql> show create table users;+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Table | Create Table                                                                                                                                    |+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| users | CREATE TABLE `users` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(20) NOT NULL, `password` varchar(100) NOT NULL, `address` varchar(200) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `name` (`name`)) ENGINE=MyISAM AUTO_INCREMENT=12 DEFAULT CHARSET=utf8 |+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)

mysql> use test2Database changedmysql> show tables ;+-----------------+| Tables_in_test2 |+-----------------+| articles    |+-----------------+1 row in set (0.00 sec)mysql> show create table articles;+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Table  | Create Table                                                                                 |+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| articles | CREATE TABLE `articles` ( `id` int(11) NOT NULL AUTO_INCREMENT, `content` longtext NOT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8 |+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)

mysql> select * from articles;+----+--------------+| id | content   |+----+--------------+| 11 | hahahaha   || 12 | xixixi    || 13 | aiaiaiaiaiai || 14 | hohohoho   |+----+--------------+4 rows in set (0.00 sec)mysql> insert into articles (id,content) values (15,'全备');Query OK, 1 row affected (0.16 sec)mysql> select * from articles;+----+--------------+| id | content   |+----+--------------+| 11 | hahahaha   || 12 | xixixi    || 13 | aiaiaiaiaiai || 14 | hohohoho   || 15 | 全备     |+----+--------------+5 rows in set (0.00 sec)

innobackupex --defaults-file=/etc/my.cnf --user=root --password=123456 /backup/full_data/

mysql> insert into articles (id,content) values (16,'增一');Query OK, 1 row affected (0.14 sec)mysql> select * from articles;+----+--------------+| id | content   |+----+--------------+| 11 | hahahaha   || 12 | xixixi    || 13 | aiaiaiaiaiai || 14 | hohohoho   || 15 | 全备     || 16 | 增一     |+----+--------------+6 rows in set (0.00 sec)

[root@centos01 backup]# innobackupex --defaults-file=/etc/my.cnf --user=root --password=123456 --incremental /backup/zl_data/ --incremental-basedir=/backup/full_data/2016-05-02_11-13-54/

mysql> use test2;Database changedmysql> insert into articles (id,content) values (17,'增二');Query OK, 1 row affected (0.10 sec)mysql> select * from articles;+----+--------------+| id | content   |+----+--------------+| 11 | hahahaha   || 12 | xixixi    || 13 | aiaiaiaiaiai || 14 | hohohoho   || 15 | 全备     || 16 | 增一     || 17 | 增二     |+----+--------------+7 rows in set (0.00 sec)

# mkdir /backup/zl_data2/

[root@centos01 backup]# innobackupex --defaults-file=/etc/my.cnf --user=root --password=123456 --incremental /backup/zl_data2/ --incremental-basedir=/backup/zl_data/2016-05-02_11-18-06/

[root@centos01 backup]# innobackupex --apply-log --redo-only --defaults-file=/etc/my.cnf /backup/full_data/2016-05-02_11-13-54/

[root@centos01 backup]# innobackupex --apply-log --redo-only --defaults-file=/etc/my.cnf /backup/full_data/2016-05-02_11-13-54/ --incremental-dir=/backup/zl_data/2016-05-02_11-18-06/

[root@centos01 backup]# innobackupex --apply-log --redo-only --defaults-file=/etc/my.cnf /backup/full_data/2016-05-02_11-13-54/ --incremental-dir=/backup/zl_data2/2016-05-02_11-21-30/

[root@centos01 backup]# service mysqld stop[root@centos01 backup]# rm -rf /alidata/server/mysql/data/*[root@centos01 backup]# innobackupex --copy-back /backup/full_data/2016-05-02_11-13-54[root@centos01 backup]# chown -R mysql.mysql /alidata/server/mysql/data/

[root@centos01 backup]# service mysqld startStarting MySQL... SUCCESS! 

mysql> select * from articles;+----+--------------+| id | content   |+----+--------------+| 11 | hahahaha   || 12 | xixixi    || 13 | aiaiaiaiaiai || 14 | hohohoho   || 15 | 全备     || 16 | 增一     || 17 | 增二     |+----+--------------+7 rows in set (0.00 sec)成功了！！！！！！证明MyISAM不支持增量备份。

原标题：xtrabackup 增量备份（InnoDB）

关键词：