Xtrabackup backup mysql database 3: innobackupex test a full and two Incremental Backup recovery test

# View table data in the current database
(Root @ localhost) [test]> select count (*) from t_innodb;
+ ---------- +
| Count (*) |
+ ---------- +
| 0 |
+ ---------- +
1 row in set (0.00 sec)


# Execute the insert data operation. This operation is completed after full backup.
(Root @ localhost) [test]> call addTest (100000,0 );


# Perform full-Database Backup
# Backup Folder: 2014-06-19_20-53-41
# Backup_type = full-backuped
# From_lsn = 0
# To_lsn = 3768612700
# Last_lsn = 3788082769
# Compact = 0
Innobackupex -- user = bkpuser -- password = s3cret -- defaults-file =/etc/my. cnf/backup


# Check the data volume after addTest completes the full backup
(Root @ localhost) [test]> select count (*) from t_innodb;
+ ---------- +
| Count (*) |
+ ---------- +
| 1, 100000 |
+ ---------- +
1 row in set (0.03 sec)


# Perform an incremental backup
# Backup Folder: 2014-06-19_20-59-02
# Backup_type = incremental
# From_lsn = 3768612700
# To_lsn = 3837968338
# Last_lsn = 3837968338
# Compact = 0
Innobackupex -- user = bkpuser -- password = s3cret -- defaults-file =/etc/my. cnf -- incremental-basedir =/backup/2014-06-19_20-53-41/backup


# Create a new table in the database
Create table 't_ innodb_1 '(
'Id' bigint (20) not null AUTO_INCREMENT,
'Name' varchar (50) default null,
'Password' varchar (150) default null,
'Userstatus' int (2) default null,
Primary key ('id ')
) ENGINE = InnoDB;
Insert into t_innodb_1 select * from t_innodb where id <101;


(Root @ localhost) [test]> select count (*) from t_innodb_1;
+ ---------- +
| Count (*) |
+ ---------- +
| 1, 100 |
+ ---------- +
1 row in set (0.00 sec)


# Perform an incremental backup
# Backup Folder: 2014-06-19_21-05-02
# Backup_type = incrementall
# From_lsn = 3837968338
# To_lsn = 838021951
# Last_lsn = 3838021951
# Compact = 0
Innobackupex -- user = bkpuser -- password = s3cret -- defaults-file =/etc/my. cnf -- incremental-basedir =/backup/2014-06-19_20-59-02/backup


# Insert 1000 data again after secondary Incremental Backup
(Root @ localhost) [test]> call addTest (1000,0 );
Query OK, 1 row affected (0.32 sec)


(Root @ localhost) [test]> select count (*) from t_innodb;
+ ---------- +
| Count (*) |
+ ---------- +
| 1, 101000 |
+ ---------- +
1 row in set (0.03 sec)


# Stop the MySQL Service
[Mysql @ rhel5 data] $/etc/init. d/mysql stop
Shutting down MySQL... [OK]


# Move the previous data directory
[Mysql @ rhel5 data] $ mkdir ../bak
[Mysql @ rhel5 data] $ mv auto. cnf ib * mysql * p * test zabbix/../bak


# Prepare full backup, use -- redo-only
Innobackupex -- defaults-file =/etc/my. cnf -- apply-log -- redo-only/backup/2014-06-19_20-53-41/


# Use -- redo-only for the first increment of Prepare
Innobackupex -- defaults-file =/etc/my. cnf -- apply-log -- redo-only -- incremental-dir =/backup/2014-06-19_20-59-02 // backup/2014-06-19_20-53-41/


# Prepare: The second increment. The last increment does not require -- redo-only.
Innobackupex -- defaults-file =/etc/my. cnf -- apply-log -- incremental-dir =/backup/2014-06-19_21-05-02 // backup/2014-06-19_20-53-41/


# Execute a Restore for the full backup
Innobackupex -- defaults-file =/etc/my. cnf -- copy-back/backup/2014-06-19_20-53-41/


# Start the database and check the mysqld-error.log for exceptions
[Root @ rhel5 ~] #/Etc/init. d/mysql start
Starting MySQL. [OK]


# Login check data, found normal
(Root @ localhost) [test]> select count (*) from t_innodb_1;
+ ---------- +
| Count (*) |
+ ---------- +
| 1, 100 |
+ ---------- +
1 row in set (0.00 sec)


(Root @ localhost) [test]> select count (*) from t_innodb;
+ ---------- +
| Count (*) |
+ ---------- +
| 1, 100000 |
+ ---------- +
1 row in set (0.00 sec)