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-backuped from_lsn = 0 to_lsn = 24104664 last_lsn = 24104674 [root@centos01 2016-05-02_10-31-50]# cat xtrabackup_checkpoints backup_type = incremental from_lsn = 24104664 to_lsn = 24104694 last_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 = incremental from_lsn = 24104694 to_lsn = 24104724 last_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 stop Shutting 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 26664 drwxr-xr-x. 2 mysql mysql 4096 May 2 10:46 2016-04-30_15-25-34 drwxr-xr-x. 2 mysql mysql 4096 May 2 10:46 ceshi -rw-r-----. 1 mysql mysql 27262976 May 2 10:43 ibdata1 drwxr-xr-x. 2 mysql mysql 4096 May 2 10:46 image drwxr-xr-x. 2 mysql mysql 4096 May 2 10:46 mysql drwxr-xr-x. 2 mysql mysql 4096 May 2 10:46 performance_schema drwxr-xr-x. 2 mysql mysql 4096 May 2 10:46 sx drwxr-xr-x. 2 mysql mysql 4096 May 2 10:46 test drwxr-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 start Starting 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 test2 Database changed mysql> 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 changed mysql> 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 start Starting 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不支持增量备份。
联系方式QQ：326528263 EMAIL：clnking@163.com 网名：bass 分享技术 突破难点 创新思维
