Percona Xtrabackup for database backup and disaster recovery

Percona Xtrabackup for database backup and disaster recovery

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

Modify the data again:
Mysql> insert into mydb1.tb1 (name, age) VALUES ('jason ', 30 );
Mysql> SELECT * FROM tb1;
+ ---- + ------- + ------ +
| Id | name | age |
+ ---- + ------- + ------ +
| 1 | tom | 10 |
| 2 | jack | 20 |
| 3 | jason | 30 |
+ ---- + ------- + ------ +

Perform the second Incremental Backup:
[Root @ mariadb ~] # Innobackupex -- user = bkuser -- password = 123456 -- incremental/backup/incremental/-- incremental-basedir =/backup/incremental/2015-04-15_16-42-00/
# "-- Incremental-basedir" indicates the directory of the first incremental backup.
[Root @ mariadb ~] # Ls/backup/incremental/2015-04-15_16-49-07/
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-49-07/xtrabackup_checkpoints
Backup_type = incremental
From_lsn = 1645178 # This is the first incremental "to_lsn" Value
To_lsns = 1645463
Last_lsns = 1645463
Compact = 0

Make another data modification to demonstrate the restoration of time points based on binary logs:
Mysql> insert into mydb1.tb1 (name, age) VALUES ('bucket', 40 );
Mysql> SELECT * FROM tb1;
+ ---- + ------- + ------ +
| Id | name | age |
+ ---- + ------- + ------ +
| 1 | tom | 10 |
| 2 | jack | 20 |
| 3 | jason | 30 |
| 4 | lucky | 40 |
+ ---- + ------- + ------ +

6. Disaster Recovery
6.1 data destruction
[Root @ mariadb ~] # Service mysqld stop
Shutting down MySQL... SUCCESS!
[Root @ mariadb ~] # Rm-rf/mnt/mydata/data/* # delete all data directory data

My understanding about xtrabackup data recovery:
Xtrabackup has a big difference in data recovery from logical backup such as mysqldump. xtrabackup needs to merge incremental data backup and full backup data for data recovery, the merge of each Incremental Backup can only replay committed transactions (redo), and the data recovery of the combined backup can only perform redo operations, after the incremental backups are merged, undo operations are performed on uncommitted transactions. After the incremental backups are merged, the "last_lsn" in the "xtrabackup_checkpoints" file of the full backup should be the value of the last Incremental backup. The redo process of these merge operations is the preparation before data recovery (prepare ).
We recommend that you copy all the files in full backup and Incremental backup as copies to avoid damage to the backup files due to misoperations.

6.2 prepare data recovery
[Root @ mariadb ~] # Innobackupex -- apply-log -- redo-only/backup/fullbackup/2015-04-15_16-30-19/
# Prepare full backup data
[Root @ mariadb ~] # Innobackupex -- apply-log -- redo-only/backup/fullbackup/2015-04-15_16-30-19/-- incremental-dir =/backup/incremental/2015-04-15_16-42-00/
# Prepare the first incremental data
[Root @ mariadb ~] # Innobackupex -- apply-log -- redo-only/backup/fullbackup/2015-04-15_16-30-19/-- incremental-dir =/backup/incremental/2015-04-15_16-49-07/
# Prepare the second incremental data
[Root @ mariadb ~] # Cat/backup/fullbackup/2015-04-15_16-30-19/xtrabackup_checkpoints
Backup_type = full-prepared
From_lsn = 0
To_lsn = 1645463 # This is the last Incremental backup location, and all data has been merged.
Last_lsns = 1645463
Compact = 0

6.3. Real Data Recovery

[Root @ mariadb ~] # Innobackupex -- copy-back/backup/fullbackup/2015-04-15_16-30-19/# only one "-- copy-back" option
[Root @ mariadb ~] # Ls/mnt/mydata/data/
Ibdata1 mydb1 mysql performance_schema test
[Root @ mariadb ~] # Chown-R mysql. mysql/mnt/mydata/data/# modify the owner and group of the restored data
[Root @ mariadb ~] # Service mysqld start
Starting MySQL... SUCCESS!
Mysql> SELECT * FROM mydb1.tb1; # Check Data
+ ---- + ------- + ------ +
| Id | name | age |
+ ---- + ------- + ------ +
| 1 | tom | 10 |
| 2 | jack | 20 |
| 3 | jason | 30 |
+ ---- + ------- + ------ +
# The lucky information has not been restored.

6.4 restore by using binary log files based on time points

View the binary log file and position information for the last Incremental Backup:
[Root @ mariadb ~] # Cat/backup/incremental/2015-04-15_16-49-07/xtrabackup_binlog_info
Mysql-bin.000031 1665
[Root @ mariadb ~] # Mysqlbinlog -- start-position = 1665/var/log/mysql_log/mysql-bin.000031>/tmp/position. SQL
# Use mysqlbinlog to export the SQL operation after the last Incremental Backup

Import data:
Mysql> source/tmp/position. SQL
Mysql> SELECT * FROM mydb1.tb1;
+ ---- + ------- + ------ +
| Id | name | age |
+ ---- + ------- + ------ +
| 1 | tom | 10 |
| 2 | jack | 20 |
| 3 | jason | 30 |
| 4 | lucky | 40 |
+ ---- + ------- + ------ +
# Restore all data

7. Summary
The innobackupex tool can be used to back up all databases in scenarios with a hybrid storage engine. When innodb tables are backed up, the innobackupex script will
The xtrabackup tool is automatically called to back up data. During data restoration, the mysql instance should be stopped, the data directory should be cleared, and prepare preparation should be done first, the second step is the real data restoration (-- copy-back). Do not forget to modify the permissions of the data directory after the data is restored to the data directory.
Of course, the innobackupex tool can also export and export single-database and single-table data. If Innodb tables are used, we recommend that you directly use the xtrabackup tool to back up data with various options more intuitively.
Have a question:
During full backup, innobackupex will back up non-innodb tables. When innobackupex is backed up, the output result will be "150412 23:17:22 innobackupex: Starting to lock all tables... "Locking all tables. I wonder if" locking all tables "includes innodb tables? If yes, xtrabackup is not a real hot backup tool.

MySQL management-using XtraBackup for Hot Backup

MySQL open-source backup tool Xtrabackup backup deployment

MySQL Xtrabackup backup and recovery

Use XtraBackup to implement MySQL master-slave replication and quick deployment [master-slave table lock-free]

Install and use Xtrabackup from Percona to back up MySQL

XtraBackup details: click here
XtraBackup: click here

This article permanently updates the link address: