正文
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不支持增量备份。
