mysql-5.6.25 compile and install and use Xstrabackup Backup combat (ii)

The main way of MySQL backup is to use mysqldump for logical export backup, mysqldump is a tool that comes with MySQL server

Advantages:

Simple to use; backup generates a file format in SQL script format.
Disadvantages:

The results of the export may be inconsistent, and if forced, all tables must be locked (myisam,innodb,etc.), and the export needs to be processed by the SQL layer, which becomes very slow as the database becomes larger.

In view of the above characteristics, the industry's main hot spare tools for InnoDB are:

1) Backup utility in MySQL Business edition (charges apply);

2) Percona Company's Open source tools Xtrabackup tools.

The main features of the Xtrabackup tool are:

1) Hot spare InnoDB table (no lock required, MyISAM table requires read lock);

2) does not need to pass the SQL layer processing, the direct physical copy file, supports the incremental backup;

3) Support parallel backup;

4) Support straming

5) Open source.

OK, talk less, start the installation!!

1) Installation

First download from Percona's website a tar.gz file, directly decompression can (for example: installed in the/root/soft directory)
[Email protected] soft]# pwd
/root/soft
[[email protected] soft]# ls
Percona-xtrabackup-2.1.8-733-linux-x86_64.tar.gz
[Email protected] soft]# TAR-XVF percona-xtrabackup-2.1.8-733-linux-x86_64.tar.gz
Unzip into a directory:
Percona-xtrabackup-2.1.8-linux-x86_64
Add the/root/soft/percona-xtrabackup-2.1.8-linux-x86_64/bin to the path variable of the user's. bash_profil file, and the source or re-login user takes effect.
Verify that the relevant files have Execute permissions
[Email protected] bin]# ls-l
Total 114080
-rwxr-xr-x 1 root root 169217 Mar 3 08:02 Innobackupex
lrwxrwxrwx 1 root root 08:59 innobackupex-1.5.1-Innobackupex
-rwxr-xr-x 1 root root 2226551 Mar 3 08:02 xbcrypt
-rwxr-xr-x 1 root root 2300304 Mar 3 08:02 xbstream
-rwxr-xr-x 1 root root 13177944 Mar 3 08:02 xtrabackup
-rwxr-xr-x 1 root root 16599128 Mar 3 07:57 xtrabackup_55
-rwxr-xr-x 1 root root 82194272 Mar 3 xtrabackup_56

2) Full preparation and recovery

Fully prepared:

Execute the following statement for full provisioning:

Innobackupex--defaults-file=/etc/mysql/my.cnf--user=root--password=***/backup/mysql/data

The statement copies the data file (specified by the variable DataDir in the MY.CNF) to the backup directory (/backup/mysql/data), note: If you do not specify --defaults-file, the default value is/etc/my.cnf.

After the backup is successful, a timestamp directory is created under the backup directory (the directory created in this example is/backup/mysql/data/2013-10-29_09-05-25), where the backup file is stored.

Recovery:

Innobackupex--defaults-file=/etc/mysql/my.cnf--user=root--password=*** --use-memory=4g --apply-log/ backup/mysql/data/2013-10-29_09-05-25

Innobackupex--defaults-file=/etc/mysql/my.cnf--user=root--password=*** --copy-back /backup/mysql/data/ 2013-10-29_09-05-25

From what can be seen, a recovery is divided into two steps,

The 1th step is apply-log, in order to speed up, it is generally recommended to set --use-memory, after this step is completed, the directory/backup/mysql/data/2013-10-29_ The backup file under 09-05-25 is ready.

The 2nd step is Copy-back, which is to copy the backup files to the original data directory.

When the recovery is complete, be sure to check the data Catalog for the correct owner and permissions.

3) Incremental backup and recovery

Note:Innobackupex incremental backups are only for innodb such a support transaction engine, and for engines such as MyISAM, they are still fully-prepared.

Incremental backup:

Incremental backups need to be based on full provisioning, assuming that we already have a fully prepared (/backup/mysql/data/2013-10-29_09-05-25), incremental backup based on the full table.

Innobackupex--defaults-file=/etc/mysql/my.cnf--user=root--password=***--incremental-basedir=/backup/mysql/data /2013-10-29_09-05-25--incremental/backup/mysql/data

Where --incremental-basedir points to the fully-prepared directory,--incremental the directory that points to the incremental backup.

After the execution of the above statement succeeds, a timestamp subdirectory (in this case:/backup/mysql/data/2013-10-29_09-52-37) is created under the directory executed by--incremental, where all the files for the incremental backup are stored.

In the backup directory, there is a file xtrabackup_checkpoints records the backup information.

The full information is as follows:

Backup_type = full-backuped

FROM_LSN = 0

TO_LSN = 563759005914

LAST_LSN = 563759005914

Information based on this fully-prepared incremental backup is as follows:

Backup_type = Incremental

FROM_LSN = 563759005914

TO_LSN = 574765133284

LAST_LSN = 574765133284

As can be seen from the above, the FROM_LSN of an incremental backup is exactly equal to the fully prepared TO_LSN.

So, can we do incremental backups on the basis of incremental backups? The answer is yes, just take the--incremental-basedir to perform the last incremental backup of the directory, as follows:

Innobackupex--defaults-file=/etc/mysql/my.cnf--user=root--password=***--incremental-basedir=/backup/mysql/data /2013-10-29_09-52-37--incremental/backup/mysql/data

Its xtrabackup_checkpoints records the backup information as follows:

Backup_type = Incremental

FROM_LSN = 574765133284

TO_LSN = 574770200380

LAST_LSN = 574770200950

As you can see, the FROM_LSN of this incremental backup starts with the TO_LSN of the last incremental backup.

Recovery:

The recovery of an incremental backup is much more complex than full provisioning, and the first step is to redo the committed log under all backup directories, such as:

Innobackupex--apply-log--redo-only Base-dir

Innobackupex--apply-log--redo-only Base-dir--incremental-dir=incremental-dir-1

Innobackupex--apply-log base-dir --incremental-dir=incremental-dir-2

Where Base-dir refers to the full directory, incremental-dir-1 refers to the first incremental backup, incremental-dir-2 refers to the second incremental backup, and so on.

Note here: The last step of incremental backup does not have the--REDO-ONLY option! Also, you can use--use_memory to improve performance.

After the execution of the above statement succeeds, the final data is base-dir (that is, the fully-prepared directory).

When the first step is complete, we begin the second step: Roll back the unfinished log:

Innobackupex--apply-log Base-dir

After the above execution, the backup file in Base-dir is fully ready and the final step is to copy:

Innobackupex--copy-back Base-dir

Similarly, after the copy is finished, remember to check that the permissions for the data directory are correct.

This article is from the "ohgenlong16300blog.com" blog, make sure to keep this source http://ohgenlong16300.blog.51cto.com/499130/1659827

mysql-5.6.25 compile and install and use Xstrabackup Backup combat (ii)