Xtrabackup Incremental Backup (InnoDB)

Mysql> SELECT * from users;+----+-----------+----------+--------------+| ID | Name      | password | address      |+----+-----------+----------+--------------+|  1 | Zhang     | 1234     | NULL         | |  2 | Wang      | 4321     | hubei Wuhan     | |  3 | Li        | 5678     | Beijing Haidian     | |  4 | Zhan      | 1234     | NULL         | |  5 | Wan       | 4321     | hubei Wuhan     | |  7 | For       | 777 |      KKK          | |  8 | CK        | |      KKK |          |  9 | KK        | 567      | ddd |          | | 10t |       123456 |   kkkkdkdkd    | | 11 | full backup    | 1234     |         ---+-----------+----------+--------------+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     | hubei Wuhan     | |  3 | Li        | 5678     | Beijing Haidian     | |  4 | Zhan      | 1234     | NULL         | |  5 | Wan       | 4321     | hubei Wuhan     | |  7 | For       | 777 |      KKK          | |  8 | CK        | |      KKK |          |  9 |  KK        | 567      | ddd |          | | 10t |       123456 |   kkkkdkdkd    | | 11 | full backup    | 1234     |         | | 12 | First time    | 123      |        |+----+-----------+----------+--------------+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 |  Wuhan, Hubei | | 3 | Li | 5678 |  Beijing Haidian | | 4 | Zhan | 1234 |  NULL | | 5 | Wan | 4321 |  Wuhan, Hubei | | 7 | for | 777 |  KKK | | 8 | CK | 132 |  KKK | | 9 | KK | 567 | DDD | | 10 | 10t | 123456 | KKKKDKDKD | | 11 | Full Backup | 1234 | Full | | 12 | First Time | 123 | First | | 13 | Second time | 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/

At this point all redo practitioners are restored in a full backup, so as long as the restore, a full backup can:

[[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/se Rver/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  2 10:46 mysqldrwxr-xr-x. 2 mysql mysql     4096 may  2 10:46 performance_schemadrwxr-xr-x. 2 m Ysql MySQL     4096  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  2 10:46 xtrabackup_binlog_pos_ Innodb-rw-r--r--. 1 mysql mysql  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     | hubei Wuhan     | |  3 | Li        | 5678     | Beijing Haidian     | |  4 | Zhan      | 1234     | NULL         | |  5 | Wan       | 4321     | hubei Wuhan     | |  7 | For       | 777 |      KKK          | |  8 | CK        | |      KKK |          |  9 | KK        | 567      | ddd |          | | 10t |       123456 |   kkkkdkdkd    | | 11 | full backup    | 1234     |         ---+-----------+----------+--------------+

Seems to have failed!!!

Mysql> Show CREATE TABLE users;+-------+----------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------+| Table |                                                                                                                                                                                                                                        Create Table |+-------+--------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------+| Users | CREATE TABLE ' users ' (' id ' int (one) ' is not null auto_increment, ' name ' varchar () ' is not NULL, ' Password ' varchar ' is not NULL, ' address ' varchar ($) DEFAULT NULL, PRIMARY key (' id '), UNIQUE key ' name ' (' name ')) Engin E=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 (one) 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      |+----+--------------+| | hahahaha     | | | | aiaiaiaiaiai | | |       HOHOHOHO |+     ----+ --------------+4 rows in Set (0.00 sec) mysql> INSERT into articles (Id,content) VALUES (15, ' full '); Query OK, 1 row affected (0.16 sec) mysql> SELECT * from articles;+----+--------------+| ID |  Content      |+----+--------------+| | hahahaha     | | | | xixixi       | |     | aiaiaiaiaiai | | | Fully prepared         |+----+--------------+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, ' Add one '); Query OK, 1 row affected (0.14 sec) mysql> select * from articles;+----+--------------+| ID |  Content      |+----+--------------+| | hahahaha     | | | | xixixi       | |     | aiaiaiaiaiai | | | Fully Prepared         | | 16 | Add one         |+----+--------------+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, ' increase two '); Query OK, 1 row affected (0.10 sec) mysql> SELECT * from articles;+----+--------------+| ID |  Content      |+----+--------------+| | hahahaha     | | | | xixixi       | |     | aiaiaiaiaiai | | | Fully Prepared         | | 16 | Add one         | | 17 | increase TWO         |+----+--------------+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 Pro Tected] backup]# Innobackupex--copy-back/backup/full_data/2016-05-02_11-13-54[[email protected] backup]# chown-r mysql.mysql/alidata/server/mysql/data/

Mysql> SELECT * from articles;+----+--------------+| ID |  Content      |+----+--------------+| | hahahaha     | | | | xixixi       | |     | aiaiaiaiaiai | | | Fully Prepared         | | 16 | Add one         | | 17 | increase TWO         |+----+--------------+7 rows in Set (0.00 SEC) SUCCEEDED!!!!!! proves that MyISAM does not support incremental backups.

Xtrabackup Incremental Backup (InnoDB)