PerconaXtrabackup for database backup and disaster recovery

Percona-xtrabackup contains two tools: xtrabackup and innobackupex. innobackupex is encapsulated by per.

Percona-xtrabackup contains two tools: xtrabackup and innobackupex. innobackupex is encapsulated by per.

Directory
1. Tool Introduction
2. Tool Installation
3. Backup policies and prepare test data
4. Full backup of data
5. Incremental Backup Data
6. Disaster Recovery
7. Summary

1. Tool Introduction
Percona-xtrabackup contains two tools: xtrabackup and innobackupex. innobackupex is encapsulated by per. xtraback is automatically called when innodb tables are backed up, therefore, the xtrabackup tool is used to back up InnoDB tables. xtrabackup can only back up innodb tables. This is a hot backup tool specially developed for innodb, innobackupex backs up tables of other engines such as myisam, the incremental innobackupex tool backs up all non-innodb tables and requests read locks.
Xtrabackup does not simply copy files when backing up innodb tables, but uses the New and Old LSN (log serial number) in the innodb Storage engine layer to identify whether the data page needs to be backed up.
The xtraback tool perfectly supports real hot backup for the innodb engine. The data files and transaction log files in the backed up data exist due to factors such as innodb cache, therefore, the data backed up is often inconsistent with the data in the transaction log. Therefore, you must redo the committed transactions in the transaction log during data recovery, undo operations are performed on uncommitted transactions, which are preparations for data recovery, namely, prepare.
2. Tool Installation
System Environment:
[Root @ mariadb ~] # Cat/etc/issue
CentOS release 6.4 (Final)
Kernel \ r on an \ m
[Root @ mariadb software] # uname-r
2.6.32-358. el6.x86 _ 64

Install the dependency package and percona-xtrabackup:
12 [root @ mariadb ~] # Yum-y install perl-devel libaio-devel perl-Time-HiRes perl-DBD-MySQL
[Root @ mariadb ~] # Rpm-ivh percona-xtrabackup-2.1.9-744.rhel6.x86_64.rpm # version 2.1.9 installed

By the way, install percona's tool set:
12 [root @ mariadb ~] # Yum-y install perl-IO-Socket-SSL # percona-toolkit dependency package
[Root @ mariadb ~] # Rpm-ivh percona-toolkit-2.2.13-1.noarch.rpm

3. Backup policies and prepare test data
The solution of adding Incremental backup to full backup is adopted. When xtrabackup is used to back up innodb tables, we recommend that mysql enable the "innodb_file_per_table = 1" variable so that each table has its own tablespace, otherwise, it is difficult to back up and restore a single table. We also recommend that you do not store binary log files in the same directory as the data files. You do not want to lose binary logs when data is lost.
Test data:
Mysql> select version ();
+ ------------ +
| VERSION () |
+ ------------ +
| 5.5.36-log |
+ ------------ +
1 row in set (0.00 sec)
Mysql> show databases; # creates a mydb1 database.
+ -------------------- +
| Database |
+ -------------------- +
| Information_schema |
| Mydb1 |
| Mysql |
| Performance_schema |
| Test |
+ -------------------- +
Mysql> SELECT * FROM mydb1.tb1; # The table has only one data entry.
+ ---- + ------ +
| Id | name | age |
+ ---- + ------ +
| 1 | tom | 10 |
+ ---- + ------ +

Create a backup data storage directory:
[Root @ mariadb ~] # Mkdir-pv/backup/{fullbackup, incremental}
# Fullbackup stores full backup data
# Incremental stores incremental Backup Data

Create a copy user:

Mysql> grant reload, lock tables, replication client on *. * TO 'bkuser' @ 'localhost' identified by '123 ';
Mysql> flush privileges;

4. Full backup of data
[Root @ mariadb ~] # Innobackupex -- user = bkuser -- password = 123456/backup/fullbackup/
#"150415 16:30:23 innobackupex: completed OK !" This information indicates that the backup is complete.
[Root @ mariadb ~] # Ls/backup/fullbackup/2015-04-15_16-30-19/
Backup-my.cnf mysql xtrabackup_binary xtrabackup_logfile
Ibdata1 performance_schema xtrabackup_binlog_info
Mydb1 test xtrabackup_checkpoints
[Root @ mariadb ~] # Cat/backup/fullbackup/2015-04-15_16-30-19/xtrabackup_checkpoints
Backup_type = full-backuped
From_lsn = 0
To_lsns = 1644877
Last_lsns = 1644877
Compact = 0

5. Incremental Backup Data
First make some data changes:
Mysql> insert into mydb1.tb1 (name, age) VALUES ('jack', 20 );
Mysql> SELECT * FROM tb1; # Add a piece of data
+ ---- + ------ +
| Id | name | age |
+ ---- + ------ +
| 1 | tom | 10 |
| 2 | jack | 20 |
+ ---- + ------ +

Perform the first Incremental Backup:
[Root @ mariadb ~] # Innobackupex -- user = bkuser -- password = 123456 -- incremental/backup/incremental/-- incremental-basedir =/backup/fullbackup/2015-04-15_16-30-19/
[Root @ mariadb ~] # Ls/backup/incremental/2015-04-15_16-42-00/
Backup-my.cnf: mydb1 test xtrabackup_checkpoints
Ibdata1.delta mysql xtrabackup_binary xtrabackup_logfile
Ibdata1.meta performance_schema xtrabackup_binlog_info
[Root @ mariadb ~] # Cat/backup/incremental/2015-04-15_16-42-00/xtrabackup_checkpoints
Backup_type = incremental
From_lsn = 1644877 # This is the "to_lsn" value for full backup
To_lsns = 1645178
Last_lsns = 1645178
Compact = 0