Percona-xtrabackup Backup Recovery

3 Xtrabackup Use manual 3.1 Using the Innobackupex script

Innobackupex is the encapsulation of Perl scripts for Xtrabackup, and the extension of functionality. 3.1.1 Backup prep work

Permissions and Connections

Xtrabackup need to connect to the database and DataDir operation permissions.

Xtrabackup or Innobackupex design to 2 types of user rights during use:

1. System user, used to invoke Innobackupex or Xtrabackup

2. Database users, users in the database

Connect to a service : Innobackupex or Xtrabackup connect to the database service through-user and-password

$ Innobackupex--user=dbuser--password=secret/path/to/backup/dir/

$ Innobackupex--user=luke--password=us3th3f0rc3--stream=tar./| BZIP2-

$ xtrabackup--user=dvader--password=14my0urf4th3r--backup--target-dir=/data/bkps/

Additional connection Options :

Option	Description
–port	The port to is connecting to the database server with TCP/IP.
–socket	The connecting to the local database.
–host	The host to use is connecting to the database server with TCP/IP.

Required Permissions : The connection to the service is to perform a backup, and Read,write and execute permissions are required on the datadir. The following permissions are required in the database:

Ÿreload and Lock tables permissions to perform flush tables with READ LOCK.

Ÿreplication client in order to get binary log location

Ÿcreate tablespace permissions to import tables, user-table-level recovery

Ÿsuper permissions are used in slave environments to start and close slave threads

mysql>CREATE USERby ' S3cret ';

mysql>GRANTLOCKon *.*to ' bkpuser ' @ ' localhost ';

privileges;

3.1.2 Fully prepared and fully-recovered 3.1.2.1 use Innobackupex to create a full preparation

Create a fully prepared

$ Innobackupex--user=dbuser--password=dbuserpass/path/to/backup-dir/

The following information is output

Innobackupex:backup created in directory '/path/to/backup-dir/2013-03-25_00-00-09 '

Innobackupex:mysql binlog position:filename ' mysql-bin.000003 ', position 1946

111225 00:00:53 innobackupex:completed ok!

From the information you will find that the backup was created in/path/to/backup-dir/2013-03-25_00-00-09

 

internal mechanism : at the time of the backup Innobackupex will call Xtrabackup to back up the InnoDB table and copy all the table definitions to the other engine's table (myisam,merge,csv,archive).

Other options :

--no-timestamp, specifies that the backup of this option is backed up directly in Backup-dir, and no timestamp folder is created.

--default-file, specifies the configuration file that is used to configure the Innobackupex line selection. 3.1.2.2 use Innobackupex to prepare for full preparation

After you create a complete copy, the data is not immediately restored, you need to rollback the uncommitted transaction, roll forward the commit transaction, and keep the database file consistent.

Innobackupex use-apply-log to make a standby backup

$ Innobackupex--apply-log/path/to/backup-dir

Success is output:

111225 1:01:57 Innodb:shutdown completed; Log sequence Number 1609228

111225 01:01:57 innobackupex:completed ok!

Once successful, the backup can be used to restore the database.

internal mechanism : reads the configuration file in the backup folder, then innobackupex the committed transaction, rolls back the uncommitted transaction, and then writes the data to the Backed-up data file (InnoDB file) and rebuilds the log file. This step implicitly calls 2 times xtrabackup–prepare. With more about Xtrabackup can look after the chapters.

Other options:

--user-memory: Specifies the memory to be used during the preliminary phase, which is faster in memory, and defaults to 10MB

$ Innobackupex--apply-log--use-memory=4g/path/to/backup-dir 3.1.2.3 restore backup using Innobackupex service MySQL Stop
Backing up data files

Mysql> SELECT @ @datadir;
+--------------------------+
| @ @datadir |
+--------------------------+
| /data/server/mysql/data/|
+--------------------------+
1 row in Set (0.00 sec)

Cd/data/server/mysql
MV Data Data_bak
Rebuilding the Data Directory
mkdir data
Recovery:
Innobackupex--copy-back/backup/mysql/2016-03-01_01-48-01

Chown-r Mysql:mysql Data
Service MySQL Start

3.1.3 Incremental backup and restore

Incremental backups are designed to reduce space usage and backup time.

The implementation of an incremental backup relies on the LSN (log sequence number) above the InnoDB page, and each modification of the database causes an LSN to increase.

An incremental backup replicates all data pages after the specified LSN.

First, the database is fully prepared using the Percona-xtrabackup tool, and then the data is incrementally backed up each time the data in the database is updated, and each incremental backup is based on the previous backup. Recovery, in turn, restores the data for each incremental backup to full standby, and finally uses the merged data to recover the data.
3.1.3.1 Create an incremental backup

Create a fully prepared

You need a full backup before you create incremental backups, or incremental backups are meaningless.

$ innobackupex/data/backups

This creates a timestamp folder under/data/backups, such as/data/backups/2013-03-31_23-01-18, and then a backup file within the folder.

Check the xtrabackup-checkpoints under the backup folder for information:

Backup_type = full-backuped

FROM_LSN =0

TO_LSN =1291135

 

Create the first incremental backup

Then use-incremental to create an incremental backup

$ Innobackupex--incremental/data/backups--incremental-basedir=basedir

Basedir points to the previous full standby,/data/backups/2013-03-31_23-01-18, after successful backup will be generated in the/data/backups under the timestamp directory, such as:/data/backups/2013-04-01_ 23-01-18, call this directory to be used after the incremental-dir-1 aspect.

Then view xtrabackup-checkpoints:

Backup_type = Incremental

FROM_LSN =1291135

TO_LSN =1352113

It can be found that, unlike the Backup_type, the INCREMENTAL,FROM_LSN is not 0.

and then create an incremental backup

Create an incremental backup on a incremental-dir-1 basis, recorded as Incremental-dir-2.

$ Innobackupex--incremental/data/backups--incremental-basedir=incremental-dir-1

Incremental Backup substitution method

You can create an incremental backup using the specified-INCREMENTAL-LSN instead of the-incremental-basedir method.

Innobackupex--incremental/data/backups--incremental-lsn=1291135

Innobackupex--incremental/data/backups--incremental-lsn=1358967

Note: Xtrabackup only affects xtradb or InnoDB tables, and other engine tables replicate only the entire file during an incremental backup, not the difference.
---The following is a specific incremental backup recovery case

The first step, fully prepared

[Root@serv01 databackup]# Innobackupex--user=root--password=123456/databackup/

Step two, view the data

mysql> use Larrydb;
Database changed
mysql> select * from class;
+------+-------+
| cid |  CNAME |
+------+-------+
|    1 | Linux |
|    2 | Dab   |
|    3 | Devel |
+------+-------+
3 rows in Set (0.00 sec)

mysql> select * from Stu;
+------+----------+------+
| Sid  | sname    | CID
| +------+----------+------+
|    1 | larry007 |    1 |
+------+----------+------+
1 row in Set (0.00 sec)

Step three, update the data

mysql> INSERT INTO Stu values (2, ' larry02 ', 1);
Query OK, 1 row Affected (0.00 sec)

mysql> select * from Stu;
+------+----------+------+
| Sid  | sname    | CID
| +------+----------+------+
|    1 | larry007 |    1 |
|    2 | Larry02  |    1 |
+------+----------+------+
2 rows in Set (0.00 sec)

Step fourth, incremental backups, full and first incremental backups, so there are two backup folders. Every incremental backup we have is for the last backup.

#--incremental: Incremental backup folder
#--incremental-dir: for which incremental backup
[root@serv01 databackup]# Innobackupex--user=root- password=123456--incremental/databackup/--incremental-dir/databackup/2013-09-10_22-12-50/

InnoDB Backup Utility V1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy and
Percona Inc 2009-2012.  All Rights Reserved.
..... Innobackupex:backup created in directory '/databackup/2013-09-10_22-15-45 '
innobackupex:mysql binlog position: FileName ' mysql-bin.000004 ', Position 353
130910 22:16:04 innobackupex:completed

[ROOT@SERV01 databackup]# ll Total
8
drwxr-xr-x 9 root root 4096 Sep 22:13 2013-09-10_22-12-50 drwxr-x
R-x. 9 root 4096 Sep 10 22:16 2013-09-10_22-15-45

Step Fifth, insert the data again

mysql> INSERT into Stu values (3, ' larry03 ', 1);
Query OK, 1 row Affected (0.00 sec)

mysql> select * from Stu;
+------+----------+------+
| Sid  | sname    | CID
| +------+----------+------+
|    1 | larry007 |    1 |
|    2 | Larry02  |    1 |
|    3 | Larry03  |    1 |
+------+----------+------+
3 rows in Set (0.00 sec)

Step sixth, incremental backup again

[ROOT@SERV01 databackup]# ll Total
8
drwxr-xr-x 9 root root 4096 Sep 22:13 2013-09-10_22-12-50 drwxr-x
R-x. 9 root 4096 Sep 22:16 2013-09-10_22-15-45
[root@serv01 databackup]# innobackupex--user=root---password=1234 --incremental/databackup/--incremental-dir/databackup/2013-09-10_22-15-45/

Step seventh, insert the data again

mysql> INSERT into Stu values (4, ' larry04 ', 1);
Query OK, 1 row Affected (0.00 sec)

mysql> select * from Stu;
+------+----------+------+
| Sid  | sname    | CID
| +------+----------+------+
|    1 | larry007 |    1 |
|    2 | Larry02  |    1 |
|    3 | Larry03  |    1 |
|    4 | LARRY04  |    1 |
+------+----------+------+
4 rows in Set (0.00 sec)

Step eighth, incremental backup again. Full-time, three incremental backups, so there are four backup folders

[Root@serv01 databackup]# Innobackupex--user=root--password=123456--incremental/databackup/--incremental-dir/ databackup/2013-09-10_22-19-21/


[root@serv01 databackup]# ll Total
drwxr-xr-x. 9 root 4096 Sep 22:13 2013-09-10_22-12-50
drwxr-xr-x 9 root 4096 Sep 22:16 2013-09-10_22-15-45 drwxr-xr-x
. 9 Root Ro OT 4096 Sep 22:19 2013-09-10_22-19-21
drwxr-xr-x. 9 root 4096 Sep 10 22:22 2013-09-10_22-21-42

Step nineth, simulate data loss

mysql> drop database larrydb;
Query OK, 2 rows affected (0.02 sec)

The tenth step is to check all the data. You can see that incremental backups and fully-prepared files take up a large amount of disk size, with a clear total disk space footprint and incremental backups taking up less disk space

[Root@serv01 databackup]# Innobackupex--apply-log--redo-only/databackup/2013-09-10_22-12-50/

InnoDB Backup Utility V1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy and
Percona Inc 2009-2012.  All Rights Reserved.
..... xtrabackup:starting shutdown with Innodb_fast_shutdown = 1
130910 22:23:35 innodb:starting shutdown  ...
130910 22:23:36  innodb:shutdown completed log sequence number 2098700 130910 22:23:36 innobackupex  : Completed ok!

[Root@serv01 databackup]# du-sh. *
22M./2013-09-10_22-12-50
1.5M  ./2013-09-10_22-15-45
1.5M  ./2013-09-10_22-19-21
1.5M  ./2013-09-10_22-21-42

The 11th step is to merge the incremental backup data for the first time into a full backup

[Root@serv01 databackup]# Innobackupex--apply-log--redo-only--incremental/databackup/2013-09-10_22-12-50/-- incremental-dir=/databackup/2013-09-10_22-15-45/

InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy and
Percona Inc 2009-2012.  All Rights Reserved.
..... Innobackupex:copying '/databackup/2013-09-10_22-15-45/hello/db.opt ' to '/databackup/2013-09-10_22-12-50/hello/ Db.opt '
130910 22:32:26  innobackupex:completed ok!

The 12th step is to merge the incremental backup data for the second time into a full backup

[Root@serv01 databackup]# Innobackupex--apply-log--redo-only--incremental/databackup/2013-09-10_22-12-50/-- incremental-dir=/databackup/2013-09-10_22-19-21/

The 13th step is to merge the incremental backup data for the third time into a full backup

[Root@serv01 databackup]# Innobackupex--apply-log--redo-only--incremental/databackup/2013-09-10_22-12-50/-- incremental-dir=/databackup/2013-09-10_22-21-42/

14th step, restore need to stop MySQL, so we stopped MySQL

[Root@serv01 databackup]#/etc/init.d/mysqld Stop
 error! MySQL server PID file could not is found!
[Root@serv01 databackup]# pkill-9 MySQL

The 15th step is to recover the data. Note that the folder specified here is 2013-09-10_22-12-50

[Root@serv01 databackup]# Innobackupex--copy-back/databackup/2013-09-10_22-12-50/innodb Backup Utility V1.5.1-xtrabackup;  Copyright 2003, 2009 Innobase Oy and Percona Inc 2009-2012.

All Rights Reserved.

This software was published under the GNU general public LICENSE Version 2, June 1991.
           Important:please Check that the Copy-back run completes successfully.

At the end of a successful copy-back run Innobackupex prints "completed ok!". Original Data directory is not empty!

At/usr/bin/innobackupex Line 571.             #报以上错需要删除数据目录下的东西 [root@serv01 data]# pwd/usr/local/mysql/data [root@serv01 data]# ls game ib_logfile0 MySQL mysql-bin.000003 performance_schema Test Hello ib_logfile1 mysql-bin.000001 mysql-bin.000004 serv01.host.com . err Xtrabackup_binlog_pos_innodb ibdata1 mnt mysql-bin.000002 mysql-bin.index serv01.host.com.pid [root@se RV01 data]# RM-RF * #再次恢复数据 and change the owner and group of the database data directory [ROOT@SERV01 databackup]# Innobackupex--copy-back/databackup/2013-09-10_22-12-50/[root@serv01 data]# ll Total 18464 drwxr-xr-x. 2 root root 4096 Sep 22:37 game drwxr-xr-x. 2 root 4096 Sep 22:37 hello-rw-r-----. 1 root root 18874368 Sep 22:33 ibdata1 drwxr-xr-x. 2 root root 4096 Sep 22:37 larrydb drwxr-xr-x. 2 root root 4096 Sep 22:37 mnt drwxr-xr-x. 2 root 4096 Sep 22:37 MySQL drwxr-xr-x. 2 root root 4096 Sep 22:37 Performance_schema drwxr-xr-x. 2 root 4096 Sep 22:37 test-rw-r--r--. 1 root Sep 22:37 xtrabackup_binlog_pos_innodb [root@serv01 data]# chown mysql.mysql/usr/local/mysql/d
 ata/-R

Step 16th, start the service

[Root@serv01 data]#/etc/init.d/mysqld start
starting MySQL. success! 

Step 17th, log in to the database, and then view the data