Shulou(Shulou.com)05/31 Report--

Today, I will talk to you about how to use xtrabackup, a sharp weapon for mysql physical backup. Many people may not know much about it. In order to make you understand better, the editor has summarized the following contents for you. I hope you can get something according to this article.

Installation

Cd / opt/

Tar-zxvf percona-xtrabackup-2.4.7-Linux-x86_64.tar.gz

Ln-s / opt/percona-xtrabackup-2.4.7-Linux-x86_64 / usr/local/xtrabackup

Introduction to Xtrabackup tools

After installing XtraBackup, there are actually several tools:

Innobackupex: this is actually a perl script wrapper for the following three tools that can back up MyISAM, InnoDB, and XtraDB tables. However, you need to add a read lock when dealing with Myisam.

Xtrabackup: a binary file compiled by C that can only back up InnoDB and XtraDB data.

Xbcrypt: used to encrypt or decrypt backed-up data.

Xbstream: used to extract or compress compressed files in xbstream format.

How to use innobackupex

For complete options, please execute innobackupex-help. Here we only introduce full backup and incremental backup and restore using common options.

-- configuration file path of defaults-file database

-- apply-log applies logs on the basis of backup to fix the inconsistencies in data processing crash state into a consistent state through apply-log.

Copy-back copies data, indexes, and logs from the backup directory to the initial location specified in the my.cnf file.

-- no-timestamp does not automatically generate a time directory when creating a backup. You can customize the backup directory name.

-- databases is used to specify the database to be backed up. How to use multiple library files: "database1 database2"

-- incremental performs incremental backup on the basis of full backup, followed by incremental backup storage directory path

-- the full backup path directory required for incremental-basedir=DIRECTORY incremental backup or the directory path of the last incremental backup

-- the directory path of incremental-dir=DIRECTORY incremental backup storage

-- redo-only is used to prepare incremental backup content to merge data into a full backup directory, together with-incremental-dir incremental backup directory.

-- if force-non-empty-directories is a backup and restore of a specific library, you don't need to delete the entire mysql directory, just the specific library and related files. If you add this parameter during the restore, there will be no error.

Full backup and recovery

Full backup

# mkdir-p / data/mysql_backup/full_backup/

# / usr/local/xtrabackup/bin/innobackupex-defaults-file=/data/mysql/mysql3306/my3306.cnf-user=root-password=root / data/mysql_backup/full_backup/

# [root@mytest7 2017-06-19 July 17-15-48] # pwd

/ data/mysql_backup/full_backup/2017-06-1911 17-15-48

[root@mytest7 2017-06-19 July 17-15-48] # ll

Total 1048628

-rw-r- 1 root root 433 Jun 19 17:16 backup-my.cnf

-rw-r- 1 root root 584 Jun 19 17:16 ib_buffer_pool

-rw-r- 1 root root 1073741824 Jun 19 17:16 ibdata1

Drwxr-x--- 2 root root 4096 Jun 19 17:16 mysql

Drwxr-x--- 2 root root 89 Jun 19 17:16 percona

Drwxr-x--- 2 root root 8192 Jun 19 17:16 performance_schema

Drwxr-x--- 2 root root 129 Jun 19 17:16 reptest

Drwxr-x--- 2 root root 8192 Jun 19 17:16 sys

-rw-r- 1 root root 66 Jun 19 17:16 xtrabackup_binlog_info

-rw-r- 1 root root 113 Jun 19 17:16 xtrabackup_checkpoints

-rw-r- 1 root root 611 Jun 19 17:16 xtrabackup_info

-rw-r- 1 root root 2560 Jun 19 17:16 xtrabackup_logfile

Full recovery

# kill-9 `pidof mysqld`

# mv / data/mysql/mysql3306/data / data/mysql/mysql3306/data_bak

# mkdir-p / data/mysql/mysql3306/data

# / usr/local/xtrabackup/bin/innobackupex-- apply-log / data/mysql_backup/full_backup/2017-06-1911-15-48-- user=root-- password=root

# / usr/local/xtrabackup/bin/innobackupex-- defaults-file=/data/mysql/mysql3306/my3306.cnf-- copy-back / data/mysql_backup/full_backup/2017-06-19 July 17-15-48 /

# chown-R mysql.mysql / data/mysql/mysql3306/data/

# / usr/local/mysql/bin/mysqld-- defaults-file=/data/mysql/mysql3306/my3306.cnf &

Compressed backup

# / usr/local/xtrabackup/bin/innobackupex-defaults-file=/data/mysql/mysql3306/my3306.cnf-user=root-password=root-no-timestamp-stream=tar. / | gzip > / data/mysql_backup/full_backup/db_$ (date +% F) .tar.gz

-- compressed backup applications need to be decompressed before they can be applied, and the method of use is the same as that of general backups.

# mkdir-p / data/mysql_backup/full_backup/db_$ (date +% F)

# tar-zxf / data/mysql_backup/full_backup/db_$ (date +% F). Tar.gz-C / data/mysql_backup/full_backup/db_$ (date +% F)

Single database backup does not make much sense. Xtrabackup recovery needs to ensure that datadir is empty and can back up single or multiple tables. The principle of recovery is very similar to innodb tablespace transfer.

Incremental backup and recovery

-- complete preparation first.

# / usr/local/xtrabackup/bin/innobackupex-defaults-file=/data/mysql/mysql3306/my3306.cnf-user=root-password=root / data/mysql_backup/full_backup/

# ll / data/mysql_backup/full_backup/2017-06-20, 15-21-57

Total 1048628

-rw-r- 1 root root 433 Jun 20 15:22 backup-my.cnf

-rw-r- 1 root root 584 Jun 20 15:22 ib_buffer_pool

-rw-r- 1 root root 1073741824 Jun 20 15:22 ibdata1

Drwxr-x--- 2 root root 4096 Jun 20 15:22 mysql

Drwxr-x--- 2 root root 89 Jun 20 15:22 percona

Drwxr-x--- 2 root root 8192 Jun 20 15:22 performance_schema

Drwxr-x--- 2 root root 129 Jun 20 15:22 reptest

Drwxr-x--- 2 root root 8192 Jun 20 15:22 sys

-rw-r- 1 root root 64 Jun 20 15:22 xtrabackup_binlog_info

-rw-r- 1 root root 113 Jun 20 15:22 xtrabackup_checkpoints

-rw-r- 1 root root 609 Jun 20 15:22 xtrabackup_info

-rw-r- 1 root root 2560 Jun 20 15:22 xtrabackup_logfile

# mysql-uroot-proot-S / data/mysql/mysql3306/tmp/mysql3306.sock

Mysql > select * from reptest.t1

+-+ +

| | id | name |

+-+ +

| | 1 | yyyy |

| | 2 | yyyy |

| | 3 | cc |

| | 4 | yyyy |

| | 5 | yyyy |

+-+ +

5 rows in set (0.05sec)

Mysql > insert into reptest.t1 values (11)

Query OK, 1 row affected (0.12 sec)

-- first incremental backup

# mkdir / data/mysql_backup/incre_backup

# / usr/local/xtrabackup/bin/innobackupex-incremental / data/mysql_backup/incre_backup-incremental-basedir=/data/mysql_backup/full_backup/2017-06-20 15-21-57-- user=root-- password=root-- socket=/data/mysql/mysql3306/tmp/mysql3306.sock

# ll / data/mysql_backup/incre_backup/2017-06-20 15-46-16

Total 108

-rw-r- 1 root root 427 Jun 20 15:46 backup-my.cnf

-rw-r- 1 root root 584 Jun 20 15:46 ib_buffer_pool

-rw-r- 1 root root 49152 Jun 20 15:46 ibdata1.delta

-rw-r- 1 root root 44 Jun 20 15:46 ibdata1.meta

Drwxr-x--- 2 root root 4096 Jun 20 15:46 mysql

Drwxr-x--- 2 root root 146 Jun 20 15:46 percona

Drwxr-x--- 2 root root 8192 Jun 20 15:46 performance_schema

Drwxr-x--- 2 root root 4096 Jun 20 15:46 reptest

Drwxr-x--- 2 root root 8192 Jun 20 15:46 sys

-rw-r- 1 root root 104 Jun 20 15:46 xtrabackup_binlog_info

-rw-r- 1 root root 117 Jun 20 15:46 xtrabackup_checkpoints

-rw-r- 1 root root 743 Jun 20 15:46 xtrabackup_info

-rw-r- 1 root root 2560 Jun 20 15:46 xtrabackup_logfile

Mysql > insert into reptest.t1 values (12million million ww')

Query OK, 1 row affected (0.00 sec)

Mysql > select * from reptest.t1

+-+ +

| | id | name |

+-+ +

| | 1 | yyyy |

| | 2 | yyyy |

| | 3 | cc |

| | 4 | yyyy |

| | 5 | yyyy |

| | 11 | zz |

| | 12 | ww |

+-+ +

7 rows in set (0.00 sec)

-- second incremental backup (--incremental-basedir can be the last full backup or the first incremental backup of files)

# / usr/local/xtrabackup/bin/innobackupex-incremental / data/mysql_backup/incre_backup-incremental-basedir=/data/mysql_backup/full_backup/2017-06-20 15-21-57-- user=root-- password=root-- socket=/data/mysql/mysql3306/tmp/mysql3306.sock

# ll / data/mysql_backup/incre_backup/2017-06-20, 15-51-49

Total 188

-rw-r- 1 root root 427 Jun 20 15:52 backup-my.cnf

-rw-r- 1 root root 584 Jun 20 15:52 ib_buffer_pool

-rw-r- 1 root root 131072 Jun 20 15:51 ibdata1.delta

-rw-r- 1 root root 44 Jun 20 15:51 ibdata1.meta

Drwxr-x--- 2 root root 4096 Jun 20 15:51 mysql

Drwxr-x--- 2 root root 146 Jun 20 15:52 percona

Drwxr-x--- 2 root root 8192 Jun 20 15:52 performance_schema

Drwxr-x--- 2 root root 4096 Jun 20 15:52 reptest

Drwxr-x--- 2 root root 8192 Jun 20 15:52 sys

-rw-r- 1 root root 106 Jun 20 15:52 xtrabackup_binlog_info

-rw-r- 1 root root 117 Jun 20 15:52 xtrabackup_checkpoints

-rw-r- 1 root root 745 Jun 20 15:52 xtrabackup_info

-rw-r- 1 root root 2560 Jun 20 15:52 xtrabackup_logfile

-- restore to the first incremental backup

# kill-9 `pidof mysqld`

# mv / data/mysql/mysql3306/data / data/mysql/mysql3306/data_bak

# mkdir-p / data/mysql/mysql3306/data

-- complete application

# / usr/local/xtrabackup/bin/innobackupex-- apply-log-- redo-only / data/mysql_backup/full_backup/2017-06-20 15-21-57-- user=root-- password=root

-- merge the first incremental backup

# / usr/local/xtrabackup/bin/innobackupex-- apply-log-- redo-only / data/mysql_backup/full_backup/2017-06-20 15-21-57-- incremental-dir=/data/mysql_backup/incre_backup/2017-06-20 15-46-16-- user=root-- password=root

-- Application log (same as full recovery)

# / usr/local/xtrabackup/bin/innobackupex-- apply-log / data/mysql_backup/full_backup/2017-06-20 15-21-57-- user=root-- password=root

-- restore data (same as full recovery)

# / usr/local/xtrabackup/bin/innobackupex-- defaults-file=/data/mysql/mysql3306/my3306.cnf-- copy-back / data/mysql_backup/full_backup/2017-06-20 15-21-57

# chown-R mysql.mysql / data/mysql/mysql3306/data/

# / usr/local/mysql/bin/mysqld-- defaults-file=/data/mysql/mysql3306/my3306.cnf &

# mysql-uroot-proot-S / data/mysql/mysql3306/tmp/mysql3306.sock

Mysql > select * from reptest.t1

+-+ +

| | id | name |

+-+ +

| | 1 | yyyy |

| | 2 | yyyy |

| | 3 | cc |

| | 4 | yyyy |

| | 5 | yyyy |

| | 11 | zz |

+-+ +

6 rows in set (0.00 sec)

The data at this time is restored to the time of the first backup.

-- restore to the second incremental backup

# kill-9 `pidof mysqld`

# mv / data/mysql/mysql3306/data / data/mysql/mysql3306/data_bak2

# mkdir-p / data/mysql/mysql3306/data

-- complete application

# / usr/local/xtrabackup/bin/innobackupex-- apply-log-- redo-only / data/mysql_backup/full_backup/2017-06-20 15-21-57-- user=root-- password=root

-- merge the first incremental backup

# / usr/local/xtrabackup/bin/innobackupex-- apply-log-- redo-only / data/mysql_backup/full_backup/2017-06-20 15-21-57-- incremental-dir=/data/mysql_backup/incre_backup/2017-06-20 15-46-16-- user=root-- password=root

-- merge the second incremental backup

# / usr/local/xtrabackup/bin/innobackupex-- apply-log-- redo-only / data/mysql_backup/full_backup/2017-06-20 15-21-57-- incremental-dir=/data/mysql_backup/incre_backup/2017-06-20 15-51-49-- user=root-- password=root

-- Application log (same as full recovery)

# / usr/local/xtrabackup/bin/innobackupex-- apply-log / data/mysql_backup/full_backup/2017-06-20 15-21-57-- user=root-- password=root

-- restore data (same as full recovery)

# / usr/local/xtrabackup/bin/innobackupex-- defaults-file=/data/mysql/mysql3306/my3306.cnf-- copy-back / data/mysql_backup/full_backup/2017-06-20 15-21-57

# chown-R mysql.mysql / data/mysql/mysql3306/data/

# / usr/local/mysql/bin/mysqld-- defaults-file=/data/mysql/mysql3306/my3306.cnf &

Just merge one more incremental backup.

It is recommended to use xtrabackup full backup + binlog log backup to specify the backup scheme. Incremental backup may require the application of multiple files, which is prone to errors.

And can only be restored to the full backup point and incremental backup point, can not be restored to the middle point, while in the actual production, most of them are in the middle point.

After reading the above, do you have any further understanding of how to use xtrabackup, a sharp weapon for mysql physical backup? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.