Percona-xtrabackup for database backup and disaster recovery

Directory 1, tool Introduction 2, tool installation 3, backup policy and preparation test data 4, full backup data 5, Incremental backup data 6, disaster recovery 7, Summary 1, tool introduction percona- the xtrabackup package contains two tools, one is xtrabackup and the other is innobackupex. innobackupex is encapsulated by per, and innodb

Directory 1, tool Introduction 2, tool installation 3, backup policy and preparation test data 4, full backup data 5, Incremental backup data 6, disaster recovery 7, Summary 1, tool introduction percona- the xtrabackup package contains two tools, one is xtrabackup and the other is innobackupex. innobackupex is encapsulated by per, and innodb

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/issueCentOS release 6.4 (Final)Kernel \r on an \m[root@mariadb software]# uname -r2.6.32-358.el6.x86_64

Install the dependency package and percona-xtrabackup:

[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:

[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; # create a mydb1 Database + databases + | Database | + ------------------ + | information_schema | mydb1 | mysql | performance_schema | test | + ------------------ + mysql> SELECT * FROM mydb1.tb1; # The table only contains 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 '123456';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 completed [root @ mariadb ~]. # Ls/backup/fullbackup/2015-04-15_16-30-19/backup-my.cnf mysql xtrabackup_binary xtrabackup_logfileibdata1 performance_schema validation test xtrabackup_checkpoints [root @ mariadb ~] # Cat/backup/fullbackup/2015-04-15_16-30-19/xtrabackup_checkpointsbackup_type = full-backupedfrom_lsn = 0to_lsn = 1644877last_lsn = 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 data entry + ---- + ------ + | 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 using mysql xtrabackup_binary xtrabackup_logfileibdata1.meta performance_schema xtrabackup_binlog_info [root @ mariadb ~] # Cat/backup/incremental/2015-04-15_16-42-00/strong = incrementalfrom_lsn = 1644877 # the value "to_lsn" to_lsn = 1645178last_lsn = 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/# Here "-- incremental-basedir" is directed directory of the first Incremental Backup [root @ mariadb ~] # Ls/backup/incremental/2015-04-15_16-49-07/backup-my.cnf mydb1 test using mysql xtrabackup_binary xtrabackup_logfileibdata1.meta performance_schema xtrabackup_binlog_info [root @ mariadb ~] # Cat/backup/incremental/2015-04-15_16-49-07/records = incrementalfrom_lsn = 1645178 # This is the first incremental "to_lsn" value to_lsn = 1645463last_lsn = 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 ('lucky',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 stopShutting 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_checkpointsbackup_type = full-preparedfrom_lsn = 0to_lsn = 1645463 # This is the last Incremental backup location where all the data has been merged last_lsn = 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 startStarting 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_infomysql-bin.0000311665 [root @ mariadb ~] # Mysqlbinlog -- start-position = 1665/var/log/mysql_log/mysql-bin.000031>/tmp/position. SQL # Use mysqlbinlog tool to export the SQL operation after the last Incremental Backup

Import data:

Mysql> source/tmp/position. sqlmysql> 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.