Shulou(Shulou.com)06/01 Report--

I. description and installation of Xtrabackup

I. description and installation of Xtrabackup

Full backup and recovery of Xtrabackup

Full and incremental backup and recovery of Xtrabackup

IV. Complete and incremental part of Xtrabackup and recovery

I. description and installation of Xtrabackup

Introduction and explanation of 1.Xtrabackup

Percona XtraBackup is the only open source free MySQL hot backup software in the world, which can perform non-blocking

Backup of InnoDB and XtraDB databases. With Percona XtraBackup, you can achieve the following benefits:

Complete backups quickly and reliably

Uninterrupted transaction processing during backup

Save disk space and network bandwidth

Automatic backup verification

Due to faster recovery time, longer uptime

Percona XtraBackup provides MySQL hot backup for all versions of Percona Server,MySQL and MariaDB. It executes

Streaming, compression and incremental MySQL backup.

Percona XtraBackup works with MySQL,MariaDB and Percona Server. It supports fully non-blocking backups

The InnoDB,XtraDB and HailDB storage engines of. In addition, it can simply back up the following storage engines

Pause writes at the end of the backup: MyISAM,Merge and Archive, including partition tables, triggers, and

Database options.

2. Download and install Xtrabackup

2.1. Download address

Https://www.percona.com/downloads/XtraBackup/LATEST/

2.3. Installation

Yum install percona-xtrabackup-24-2.4.7-1.el7.x86_64.rpm

Additional need to install some dependent perl packages

2.4. View installation results

# rpm-ql percona-xtrabackup-24-2.4.7-1.el7.x86_64/usr/bin/innobackupex/usr/bin/xbcloud/usr/bin/xbcloud_osenv/usr/bin/xbcrypt/usr/bin/xbstream/usr/bin/xtrabackup/usr/share/doc/percona-xtrabackup-24-2.4.7/usr/share/doc/percona-xtrabackup-24-2.4.7/COPYING/usr/share/man/man1/innobackupex.1.gz/usr/share/man/ Man1/xbcrypt.1.gz/usr/share/man/man1/xbstream.1.gz/usr/share/man/man1/xtrabackup.1.gz

2. Full backup of Xtrabackup

1.Xtrabackup is fully prepared.

1.1. Start to be ready.

Create a backup directory

# mkdir / data/backups

Pre-preparation data view:

MariaDB [ckldb] > select * from jone;+-+-+ | id | name | +-+-+ | 1 | wukaka | | 2 | side | | 3 | ckl | 4 | kk | | 5 | zld | | 6 | ned | 7 | stark | +-+-+ 7 rows in set (0.00 sec)

1.2. Start backup

# innobackupex-user=root / data/backups/ 170430 23:04:14 innobackupex: Starting the backup operationIMPORTANT: Please check that the backup run completes successfully. At the end of a successful backup run innobackupex prints "completed OK!". 170430 23:04:14 version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup;port=3306 Mysql_socket=/tmp/mysql.sock' as 'root' (using password: NO). 170430 23:04:14 version_check Connected to MySQL server170430 23:04:14 version_check Executing a version check against the server...170430 23:04:14 version_check Done.170430 23:04:14 Connecting to MySQL server host: localhost, user: root, password: not set, port: 3306 Socket: / tmp/mysql.sockUsing server version 10.1.22-MariaDBinnobackupex version 2.4.7 based on MySQL server 5.7.13 Linux (x86: 64) (revision id: 6f7a799) xtrabackup: uses posix_fadvise () .xtrabackup: cd to / data/mysql/3306/dataxtrabackup: open files limit requested 0 Set to 1024xtrabackup: using the following InnoDB configuration:xtrabackup: innodb_data_home_dir = / data/mysql/3306/dataxtrabackup: innodb_data_file_path = ibdata1:10M:autoextendxtrabackup: innodb_log_group_home_dir = / data/mysql/3306/dataxtrabackup: innodb_log_files_in_group = 2xtrabackup: innodb_log_file_size = 67108864.170430 23:04:24 Executing UNLOCK TABLES170430 23:04:24 All tables unlocked170430 23:04:24 Backup created In directory'/ data/backups/2017-04-309 23-04-14/'MySQL binlog position: filename 'mysql-bin.000009' Position '745, GTID of the last change' 0-1-62 170430 23:04:24 [00] Writing backup-my.cnf170430 23:04:24 [00]... done170430 23:04:24 [00] Writing xtrabackup_info170430 23:04:24 [00]... donextrabackup: Transaction log of lsn (1634473) to (1634473) was copied.170430 23:04:25 completed OK!

1.3. View backup files

# ll / data/backups/2017-04-30mm 23-04-14/total 75800Mel RW Murray. 1 root root 418 Apr 30 23:04 backup-my.cnfdrwxr-x---. 2 root root 52 Apr 30 23:04 ckldb-rw-r-. 1 root root 77594624 Apr 30 23:04 ibdata1drwxr-x---. 2 root root 4096 Apr 30 23:04 mysqldrwxr-x---. 2 root root 20 Apr 30 23:04 performance_schemadrwxr-x---. 2 root root 20 Apr 30 23:04 test-rw-r-. 1 root root 28 Apr 30 23:04 xtrabackup_binlog_info-rw-r-. 1 root root 113 Apr 30 23:04 xtrabackup_checkpoints-rw-r-. 1 root root 494 Apr 30 23:04 xtrabackup_info-rw-r-. 1 root root 2560 Apr 30 23:04 xtrabackup_logfile# cd / data/backups/2017-04-30 binary 23-04-14 Universe # cat xtrabackup_binlog_info mysql-bin.000009 7450-1-6 binary log file log POS point timestamp LSN, log serial number The log sequence number of Innodb is a 64-bit integer # cat xtrabackup_checkpoints backup_type = full-backuped # backup type is full from_lsn = 0 # LSN start position to_lsn = 1634473 # LSN end location last_lsn = 1634473 # latest LSN location compact = 0recover_binlog_info = 0

1.4. Simulated deletion of files

# rm-rf / data/mysql/3306/data

two。 Prepare for recovery

After the backup is created, the data is not ready to be restored. Uncommitted transactions may be uncommitted or transactions in the log may be replayed. Doing these wait operations will keep the data files consistent, which is the purpose of the preparation phase. Once completed, the data is ready to use. To prepare a backup using innobackupex, you must use-- apply-log and the path to the backup directory as parameters:

2.1. Stop the database

# service mysqld stop

2.2. Recovery preparation

# innobackupex-- apply-log / data/backups/2017-04-30 23-04-14 take 170430 23:18:35 innobackupex: Starting the apply-log operationIMPORTANT: Please check that the apply-log run completes successfully. At the end of a successful apply-log run innobackupex prints "completed OK!" .InnoDB: Starting shutdown...InnoDB: Shutdown completed; log sequence number 1634856170430 23:18:46 completed OK!

2.3. Start recovery

# innobackupex-- copy-back / data/backups/2017-04-30 23-04-14 take 170430 23:20:31 innobackupex: Starting the copy-back operationIMPORTANT: Please check that the copy-back run completes successfully. At the end of a successful copy-back run innobackupex prints "completed OK!". 170430 23:20:42 [01] Copying. / ibtmp1 to / data/mysql/3306/data/ibtmp1170430 23:20:42 [01]... done170430 23:20:43 completed OK!

2.4. View recovered files

# lltotal 219148drwxr Murray. 2 root root 52 Apr 30 23:20 ckldb-rw-r-. 1 root root 77594624 Apr 30 23:20 ibdata1-rw-r-. 1 root root 67108864 Apr 30 23:20 ib_logfile0-rw-r-. 1 root root 67108864 Apr 30 23:20 ib_logfile1-rw-r-. 1 root root 12582912 Apr 30 23:20 ibtmp1drwxr-x---. 2 root root 4096 Apr 30 23:20 mysqldrwxr-x---. 2 root root 20 Apr 30 23:20 performance_schemadrwxr-x---. 2 root root 20 Apr 30 23:20 test-rw-r-. 1 root root 23 Apr 30 23:20 xtrabackup_binlog_pos_innodb-rw-r-. 1 root root 494 Apr 30 23:20 xtrabackup_info

Add permissions to start the database:

# chown-R mysql.mysql data/# service mysqld start

View the recovery results:

MariaDB [(none)] > use ckldbDatabase changedMariaDB [ckldb] > show tables;+-+ | Tables_in_ckldb | +-+ | jone | +-+ 1 row in set (0.00 sec) MariaDB [ckldb] > select * from jone +-+-+ | id | name | +-+-+ | 1 | wukaka | | 2 | side | 3 | ckl | 4 | kk | | 5 | zld | | 6 | ned | | 7 | stark | +-+-+ 7 rows in set (0.01sec)

Full and incremental backup and recovery of Xtrabackup

1. Complete execution. The above has been backed up. There is no need this time.

two。 Incremental backup

2.1. Add data for the first time

MariaDB [ckldb] > insert into jone values (8Magnum lanester'), (9mcnggonku'); Query OK, 2 rows affected (0.39 sec) Records: 2 Duplicates: 0 Warnings: 0MariaDB [ckldb] > select * from jone +-+-+ | id | name | +-+-+ | 1 | wukaka | | 2 | side | 3 | ckl | 4 | kk | | 5 | zld | | 6 | ned | | 7 | stark | 8 | lanester | 9 | gonku | +-+-+ 9 rows in set (0.00 sec)

2.2. Perform the first incremental backup

# innobackupex-- incremental / data/backups/-- incremental-basedir=/data/backups/2017-04-30mm 23-04-14 170430 23:37:49 innobackupex: Starting the backup operationIMPORTANT: Please check that the backup run completes successfully. At the end of a successful backup run innobackupex prints "completed OK!" .xtrabackup: Transaction log of lsn (1636718) to (1636718) was copied.170430 23:37:56 completed OK!

View the backup results:

# ls / data/backups/2017-04-309 23-04-14 2017-04-30 * 23-37-4 cd / data/backups/2017-04-30 * 23-37-49backup_type = incrementalfrom_lsn = 1634473to_lsn = 1636718last_lsn = 1636718compact = 0recover_binlog_info = 0

2.3. Add the second data

MariaDB [ckldb] > insert into jone values (10 Warnings dragon`s), (11 sec'); Query OK, 2 rows affected (0.61 sec) Records: 2 Duplicates: 0 Warnings: 0MariaDB [ckldb] > select * from jone +-+-+ | id | name | +-+-+ | 1 | wukaka | | 2 | side | 3 | ckl | 4 | kk | | 5 | zld | | 6 | ned | | 7 | stark | 8 | lanester | 9 | gonku | 10 | dragon | | 11 | lanli | +- -+-+ 11 rows in set (0.00 sec)

2.4. Perform a second incremental backup

# innobackupex-- incremental / data/backups/-- incremental-basedir=/data/backups/2017-04-30 March 23-37-49 Universe 170430 23:41:46 innobackupex: Starting the backup operationIMPORTANT: Please check that the backup run completes successfully. At the end of a successful backup run innobackupex prints "completed OK!" .xtrabackup: Transaction log of lsn (1638894) to (1638894) was copied.170430 23:41:54 completed OK!

View the backup results:

# du-sh / data/backups/*224M / data/backups/2017-04-30mm 23-04-141.3M / data/backups/2017-04-30mm 23-37-491.1M / data/backups/2017-04-30mm 23-41-4 "cd / data/backups/2017-04-30mm 23-41-46max # cat xtrabackup_checkpoints backup_type = incrementalfrom_lsn = 1636718to_lsn = 1638894last_lsn = 1638894compact = 0recover_binlog_info = 0

3. Simulated deletion of database

# rm-rf / data/mysql/3306/data/

4. Incremental backup recovery

Preparing incremental backups using innobackupex is a little different

Complete. This may be a stage that requires more attention:

First, the transaction that must be committed on each backup. This merges cardinality backups with increments.

Then, you must roll back uncommitted transactions before you can make an out-of-the-box backup.

If you replay committed transactions and roll back uncommitted transactions on a basic backup, you will not be able to

Add increments. If you do this incrementally, you cannot add data from that moment

And the remaining increments.

4.1. Full recovery preparation

# innobackupex-- apply-log-- redo-only / data/backups/2017-04-303-04-14 /

4.1. First incremental backup recovery preparation

# innobackupex-- apply-log-- redo-only / data/backups/2017-04-30,23-04-14 /-- incremental-dir=/data/backups/2017-04-30,23-37-49

4.2. Second incremental backup recovery preparation

# innobackupex-- apply-log-- redo-only / data/backups/2017-04-30,23-04-14 /-- incremental-dir=/data/backups/2017-04-30,23-41-46 /

5. Start recovery

# innobackupex-- copy-back / data/backups/2017-04-309 23-04-14 /

6. View recovery data and files

# chown-R mysql.mysql / data/mysql/3306/data/# service mysqld start

View data

MariaDB [(none)] > use ckldbDatabase changedMariaDB [ckldb] > select * from jone +-+-+ | id | name | +-+-+ | 1 | wukaka | | 2 | side | 3 | ckl | 4 | kk | | 5 | zld | | 6 | ned | | 7 | stark | 8 | lanester | 9 | gonku | 10 | dragon | | 11 | lanli | +- -+-+ 11 rows in set (0.00 sec)

Data from both incremental backups have been restored

IV. Complete and incremental part of Xtrabackup and recovery

Percona XtraBackup provides partial backups, which means that you can only back up specific tables or

Database. The table you back up must be in a separate tablespace because it is created or changed after you

The innodb_file_per_table option is enabled on the server.

There is only one warning about partial backups: do not copy backups. Restore a partial backup

This should be done by importing the table, rather than using the traditional-copy-back option. Although there are some

Scenarios where you can restore by copying files, which may result in database inconsistencies

A case is not the recommended method.

There are three ways to create a partial backup to specify which part of the entire data to back up: general

Expression (--include), enumerate the tables in the file (--tables-file), or provide a list of databases (--database).

The regular expression supplied to this using the-- include option will match the fully qualified

The table name, including the database name, is in the format databasename.tablename.

1. A single library is complete.

# innobackupex-- include='ckldb' / data/backups/170501 00:07:31 innobackupex: Starting the backup operationIMPORTANT: Please check that the backup run completes successfully. At the end of a successful backup run innobackupex prints "completed OK!" .170501 00:07:35 Backup created in directory'/ data/backups/2017-05-01pm 00-07-31/'MySQL binlog position: filename 'mysql-bin.000001', position' 313' GTID of the last change '170501 00:07:35 [00] Writing backup-my.cnf170501 00:07:35 [00]... done170501 00:07:35 [00] Writing xtrabackup_info170501 00:07:35 [00]... donextrabackup: Transaction log of lsn (1634875) to (1634875) was copied.170501 00:07:35 completed OK!

View the backup file:

# cd / data/backups/2017-05-01 / 00-07-31 / [root@localhost 2017-05-01 / 00-07-31] # lsbackup-my.cnf ckldb ibdata1 xtrabackup_binlog_info xtrabackup_checkpoints xtrabackup_info xtrabackup_logfile

two。 Incremental backup of a single library

# innobackupex-- include='ckldb'-- incremental / data/backups/-- incremental-basedir=2017-05-01-00-07-31

3. Add data, second incremental backup

3.1. Increase data

MariaDB [ckldb] > insert into jone values (12 Warnings firegods), (13 sec sec); Query OK, 2 Warnings: 0MariaDB [ckldb] > select * from jone +-+-+ | id | name | +-+-+ | 1 | wukaka | | 2 | side | 3 | ckl | 4 | kk | | 5 | zld | | 6 | ned | | 7 | stark | 8 | lanester | | 9 | gonku | 10 | dragon | | 11 | lanli | | 12 | firegod | | 13 | sevenGod | +-+-+ 13 rows in set (0.00 sec)

3.2. Start the second incremental backup:

# innobackupex-- include='ckldb'-- incremental / data/backups/-- incremental-basedir=/data/backups/2017-05-01pm-10-00 /

4. Simulated deletion of database

MariaDB [(none)] > drop database ckldb; Query OK, 1 row affected (0.19 sec) MariaDB [(none)] > show databases +-+ | Database | +-+ | information_schema | | mysql | | performance_schema | | test | +-+ 4 rows in set (0.00 sec)

5. Prepare for recovery

5.1. Prepare to restore full readiness.

# innobackupex-- apply-log-- export / data/backups/2017-05-01pm-07-31 /

5.2. Prepare for the first incremental backup

# innobackupex-- apply-log-- redo-only / data/backups/2017-05-0120000-07-31 /-- incremental-dir=/data/backups/2017-05-01000010-00 /

5.3. Prepare for the second incremental backup

# innobackupex-- apply-log-- redo-only / data/backups/2017-05-01 / 00-07-31 /-- incremental-dir=/data/backups/2017-05-01 / 00-13-35 /

6. Start recovery

#\ cp-rf / data/backups/2017-05-01mm 00-07-31max * / data/mysql/3306/data/

Permission added:

# chown-R mysql.mysql / data/mysql/3306/data/# service mysqld start

View the data:

MariaDB [(none)] > use ckldbDatabase changedMariaDB [ckldb] > select * from jone +-+-+ | id | name | +-+-+ | 1 | wukaka | | 2 | side | 3 | ckl | 4 | kk | | 5 | zld | | 6 | ned | | 7 | stark | 8 | lanester | | 9 | gonku | 10 | dragon | | 11 | lanli | | 12 | firegod | 13 | sevenGod | +-+-+ 13 rows in set (0.06 sec)

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.