Xtrabackup backup MySQL database three: Innobackupex test a full-volume and two-incremental backup recovery test

# # View data for tables in the current library
([email protected]) [Test]>select Count (*) from T_innodb;
+----------+
| COUNT (*) |
+----------+
| 0 |
+----------+
1 row in Set (0.00 sec)


# # Performs an insert data operation, which executes after full provisioning
([email protected]) [Test]>call addtest (100000,0);


# # Perform a full library 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


# # After the full backup is complete, wait for the addtest to finish, check the amount of data
([email protected]) [Test]>select Count (*) from T_innodb;
+----------+
| COUNT (*) |
+----------+
| 100000 |
+----------+
1 row in Set (0.03 sec)


# # To 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--incremental-basedir=/ Backup/2014-06-19_20-53-41/backup


# # Create a new table in the library
CREATE TABLE ' t_innodb_1 ' (
' ID ' bigint (a) not NULL auto_increment,
' Name ' varchar (DEFAULT NULL),
' Password ' varchar DEFAULT NULL,
' UserStatus ' int (2) DEFAULT NULL,
PRIMARY KEY (' id ')
) Engine=innodb;
Insert into t_innodb_1 select * from T_innodb where id<101;


([email protected]) [Test]>select Count (*) from t_innodb_1;
+----------+
| COUNT (*) |
+----------+
| 100 |
+----------+
1 row in Set (0.00 sec)


# # To 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--incremental-basedir=/ Backup/2014-06-19_20-59-02/backup


# # two times after the incremental backup is complete, insert the 1000 data again
([email protected]) [Test]>call addtest (1000,0);
Query OK, 1 row affected (0.32 sec)


([email protected]) [Test]>select Count (*) from T_innodb;
+----------+
| COUNT (*) |
+----------+
| 101000 |
+----------+
1 row in Set (0.03 sec)


# # Stop MySQL Service
[[email protected] data]$/etc/init.d/mysql stop
Shutting down MySQL .... [OK]


# # before moving the data directory
[Email protected] data]$ mkdir. /bak
[Email protected] data]$ mv auto.cnf ib* mysql* p* test zabbix/. /bak


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


# # Prepare First increment, using--redo-only
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 Second increment, last increment does not need--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/


# # for a full backup, perform a restore
Innobackupex--defaults-file=/etc/my.cnf--copy-back/backup/2014-06-19_20-53-41/


# # Start the database and check if Mysqld-error.log has any exceptions
[[email protected] ~]#/etc/init.d/mysql start
Starting MySQL. Determine


# # Log in to check data, found normal
([email protected]) [Test]>select Count (*) from t_innodb_1;
+----------+
| COUNT (*) |
+----------+
| 100 |
+----------+
1 row in Set (0.00 sec)


([email protected]) [Test]>select Count (*) from T_innodb;
+----------+
| COUNT (*) |
+----------+
| 100000 |
+----------+
1 row in Set (0.00 sec)