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)

 

[[email protected] 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)

 

[[email protected] 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)

[[email protected] backup]# mkdir /backup/zl_data2[[email protected] 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/

 

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

 

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

 

[[email protected] 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/

 

[[email protected] 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執做者還原在完全備份中了，所以只要還原，完全備份就可以：

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

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

 

[[email protected] backup]# chown -R mysql.mysql /alidata/server/mysql/data/[[email protected] 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

 

[[email protected] backup]# service mysqld startStarting MySQL... SUCCESS![[email protected] 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)

 

[[email protected] 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/

 

[[email protected] 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/

 

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

 

[[email protected] 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/

 

[[email protected] 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/

 

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

 

[[email protected] 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）