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

Introduction to Percona XtraBackup

It is an open source, free MySQL database hot backup tool, which can complete and add non-blocking to the InnoDB storage engine, while also supporting compression, encryption and streaming features.

Install the Generic version

1. Root@db01 / data/software # ls percona-xtrabackup-2.4.8-Linux-x86_64.tar.gz

Percona-xtrabackup-2.4.8-Linux-x86_64.tar.gz

2. Root@db01 / data/software # tar zxf percona-xtrabackup-2.4.8-Linux-x86_64.tar.gz

3. Root@db01 / usr/local # ln-s / data/software/percona-xtrabackup-2.4.8-Linux-x86_64 percona-xtrabackup-2.4.8

4. Set the environment variable PATH

Root@db01 ~ # grep 'PATH'. Bash _ profile

PATH=/usr/local/percona-xtrabackup-2.4.8/bin:/usr/local/sysbench-1.0.9/bin:/opt/mysql/bin:$PATH:$HOME/bin

Export PATH

Root@db01 ~ # xtrabackup-version

Xtrabackup version 2.4.8 based on MySQL server 5.7.13 Linux (x86 / 64) (revision id: 97330f7)

At the same time, you can also view the man page of the corresponding command in Percona XtraBackup, such as typing man xtrabackup.

Root@db01 ~ # man-w

/ usr/local/percona-xtrabackup-2.4.8/man:/opt/mysql/man:/usr/local/share/man:/usr/share/man/overrides:/usr/share/man/en:/usr/share/man

Experience the use of the command xtrabackup.

1. Create a complete set

Root@db01 / data1/xtrabackup_test # xtrabackup-defaults-file=/data1/3316/conf/my.cnf-login-path=mytest-target-dir=./pxb_full-backup

Create additional equipment

Root@db01 / data1/xtrabackup_test # xtrabackup-defaults-file=/data1/3316/conf/my.cnf-login-path=mytest-target-dir=./pxb_inc-backup-incremental-basedir=./pxb_full

2. Prepare stage

Root@db01 / data1/xtrabackup_test # xtrabackup-target-dir=./pxb_full-prepare-apply-log-only-use-memory=4G

Root@db01 / data1/xtrabackup_test # xtrabackup-target-dir=./pxb_full-prepare-use-memory=4G-incremental-dir=./pxb_inc

3. Restore backup

Root@db01 / data1/xtrabackup_test # xtrabackup-defaults-file=/data1/3316/conf/my.cnf-target-dir=./pxb_full-copy-back

The working process of xtrabackup above is briefly described as follows.

After xtrabackup starts, the redo thread is created first, and then the ibd thread. The redo thread is responsible for copying the transaction log. If it is added, it will copy according to the value of the file xtrabackup_ checkpointsto _ lsn in the last backup, while the ibd thread is responsible for copying the data file.

When the ibd thread copies the transaction data file, the redo thread will always monitor and copy the new transaction log. After the ibd thread has finished copying the transaction data file, the xtrabackup executes FLUSH TABLES WITH READ LOCK to acquire the global read lock, and the ibd thread begins to copy the non-transaction data file, during which the redo thread is still working. When the ibd thread finishes copying the non-transaction data file, the xtrabackup executes SHOW MASTER STATUS, obtains the binary log coordinates, executes FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS, refreshes the InnoDB storage engine log, and then the redo thread ends, executes UNLOCK TABLES, and releases the lock. Finally, write metadata.

In the Prepare phase, xtrabackup performs crash recovery operations (similar to the crash recovery of the InnoDB storage engine), roll forward the committed transactions and roll back the uncommitted transactions, and restore the database to the consistent state of FLUSH TABLES WITH READ LOCK.

Next, we will further proficient in the use of xtrabackup from three scenarios.

1. How to back up the slave database to deploy a new slave database.

Here is mainly the use of the parameter-slave-info, with this parameter, it will record the coordinates of the main database binary log, similar to the parameter of the command mysqldump-dump-slave, the command line is as follows.

Root@db02 / data1/xtrabackup_test # xtrabackup-defaults-file=/data1/3316/conf/my.cnf-login-path=mytest-target-dir=pxb_full-backup-slave-info

Note that if multithreaded replication is enabled from the database, xtrabackup exits with the following error.

The-- slave-info option requires GTID enabled for a multi-threadedslave.

2. Xtrabackup backup belongs to physical backup, which takes up more disk space than logical backup of mysqldump. In practice, backup files are generally compressed. Xtrabackup has two compression methods, one is to compress with tar and gzip, and the other is that it provides compression function.

2.1 tar and gzip mode

Root@db01 / data1/xtrabackup_test # mkdir pxb_full

Root@db01 / data1/xtrabackup_test # time xtrabackup-- defaults-file=/data1/3316/conf/my.cnf-- login-path=mytest-- target-dir=./pxb_full-- backup-- stream=tar 2 >. / pxb_full/pxb.log | gzip->. / pxb_full/pxb_full.tar.gz

Xtrabackup also provides a parameter for parallel backup-parallel. Whether it can speed up the above process, the test is as follows:

Root@db01 / data1/xtrabackup_test # time xtrabackup-defaults-file=/data1/3316/conf/my.cnf-login-path=mytest-target-dir=./pxb_full-backup-stream=tar-parallel=4 2 >. / pxb_full/pxb.log | gzip->. / pxb_full/pxb_full.tar.gz

Xtrabackup is prompted in STDERR as follows. The parameter-parallel does not support tar streams and will be ignored.

Xtrabackup: warning: the-- parallel option does not have anyeffect when streaming in the 'tar' format. You can use the 'xbstream' formatinstead.

2.2 built-in compression function, quicklz and xbstream

Root@db01 / data1/xtrabackup_test # mkdir pxb_full

Root@db01 / data1/xtrabackup_test # time xtrabackup-defaults-file=/data1/3316/conf/my.cnf-login-path=mytest-target-dir=./pxb_full-backup-stream=xbstream-compress=quicklz 2 >. / pxb_full/pxb.log >. / pxb_full/pxb_full.qp.xbstream

Use the parameters-- compress-threads and-- parallel to accelerate the above process.

Root@db01 / data1/xtrabackup_test # time xtrabackup-defaults-file=/data1/3316/conf/my.cnf-- login-path=mytest-- target-dir=./pxb_full-- backup-- stream=xbstream-- compress=quicklz-- compress-threads=4-- parallel=4 2 >. / pxb_full/pxb.log >. / pxb_full/pxb_full.qp.xbstream

3. How to deploy a slave database as quickly as possible. The process can be divided into three steps: backing up the data, transmitting the backup, and preparing the data to the Prepare phase. There are roughly four ways, which are tested as follows.

3. 1 tar stream, compressed by gzip, transmitted to the remote end via scp.

3.1.1 backup data

Root@db01 / data1/xtrabackup_test # mkdir pxb_full

Root@db01 / data1/xtrabackup_test # time xtrabackup-- defaults-file=/data1/3316/conf/my.cnf-- login-path=mytest-- target-dir=./pxb_full-- backup-- stream=tar 2 >. / pxb_full/pxb.log | gzip->. / pxb_full/pxb_full.tar.gz

Real 7m14.839s

User 7m7.201s

Sys 0m6.227s

3.1.2. Transfer backup

Root@db01 / data1/xtrabackup_test/pxb_full # time scp pxb_full.tar.gz 192.168.1.4:/data1/xtrabackup_test/pxb_full

Pxb_full.tar.gz 100% 2214MB 110.7MB/s 00:20

Real 0m19.992s

User 0m7.095s

Sys 0m4.136s

3.1.3 decompress backup

Root@db02 / data1/xtrabackup_test/pxb_full # time tar zxf pxb_full.tar.gz

Real 1m7.359s

User 0m59.890s

Sys 0m7.446s

Total time: 7m14.839s + 0m19.992s + 1m7.359s = 522.190s

3.2 quicklz compression, xbstream stream, transmitted to the remote end through scp.

3.2.1 backing up data

Root@db01 / data1/xtrabackup_test # time xtrabackup-defaults-file=/data1/3316/conf/my.cnf-- login-path=mytest-- target-dir=./pxb_full-- backup-- stream=xbstream-- compress=quicklz-- compress-threads=4-- parallel=4 2 >. / pxb_full/pxb.log >. / pxb_full/pxb_full.qp.xbstream

Real 0m19.780s

User 0m35.536s

Sys 0m7.299s

3.2.2 transfer backup

Root@db01 / data1/xtrabackup_test/pxb_full # time scp pxb_full.qp.xbstream 192.168.1.4:/data1/xtrabackup_test/pxb_full

Pxb_full.qp.xbstream 100% 3032MB112.3MB/s 00:27

Real 0m27.373s

User 0m10.102s

Sys 0m6.272s

3.2.3 extract and decompress backup

To extract the files compressed by the quicklz algorithm, install the tool qpress and place it in the same directory as the command xtrabackup. Download link: http://www.quicklz.com.

3.2.3.1 extraction

Root@db02 / data1/xtrabackup_test/pxb_full # mkdir for_repl

Root@db02 / data1/xtrabackup_test/pxb_full # time xbstream-extract-directory=./for_repl

< pxb_full.qp.xbstream real 0m4.760s user 0m0.410s sys 0m4.279s 3.2.3.2 解压 root@db02 /data1/xtrabackup_test/pxb_full # time xtrabackup --target-dir=./for_repl --decompress --remove-original --parallel=4 real 0m8.714s user 0m20.519s sys 0m9.200s 总耗时: 0m19.780s + 0m27.373s + 0m4.760s + 0m8.714s = 60.627s 3.3 tar流, 通过ssh传送到远端. 3.3.1 备份, 传送数据 root@db01 /data1/xtrabackup_test # mkdir pxb_full root@db01 /data1/xtrabackup_test # time xtrabackup --defaults-file=/data1/3316/conf/my.cnf --login-path=mytest --target-dir=./pxb_full --backup --stream=tar 2>

. / pxb_full/pxb.log | ssh 192.168.1.4 "tar xfi-directory=/data1/xtrabackup_test/pxb_full"

Real 1m0.779s

User 0m31.110s

Sys 0m10.708s

Total time: 1m0.779s = 60.779s

3. 4 quicklz compression, xbstream stream, transmitted to the remote end through ssh.

3.4.1 backup data

Root@db01 / data1/xtrabackup_test # mkdir pxb_full

Root@db01 / data1/xtrabackup_test # time xtrabackup--defaults-file=/data1/3316/conf/my.cnf-- login-path=mytest-- target-dir=./pxb_full-- backup--stream=xbstream-- compress=quicklz-- compress-threads=4 2 >. / pxb_full/pxb.log | ssh 192.168.1.4 "/ usr/local/percona-xtrabackup-2.4.8/bin/xbstream-extract-directory=/data1/xtrabackup_test/pxb_full-parallel=4"

Real 0m48.778s

User 0m44.296s

Sys 0m6.859s

3.4.2 decompression

Root@db02 / data1/xtrabackup_test # time xtrabackup-target-dir=./pxb_full-decompress-remove-original-parallel=4

Real 0m8.116s

User 0m20.610s

Sys 0m9.236s

Total time: 0m48.778s + 0m8.116s = 56.894s

It can be seen that the fourth method is the fastest, followed by the third, but both are streaming transmission, which requires a better network environment, otherwise it is the second method.

In addition, it is also mentioned that xtrabackup provides backup encryption function, which has two ways: key and key-file. Choose key-file to demonstrate here, and comb the whole backup recovery process at the same time.

1. Make key-file file

Root@db01 / data1/xtrabackup_test # openssl rand-base64 24

QEBPZrck0JQHYSnG8ScdW0UjAeKkOCFt

Root@db01 / data1/xtrabackup_test # echo-n 'qEBPZrck0JQHYSnG8ScdW0UjAeKkOCFt' > / usr/local/percona-xtrabackup-2.4.8/bin/keyfile

two。 Backup data

Root@db01 / data1/xtrabackup_test # mkdir pxb_full

Root@db01 / data1/xtrabackup_test # xtrabackup--defaults-file=/data1/3316/conf/my.cnf-- login-path=mytest-- target-dir=./pxb_full-- backup--stream=xbstream-- compress=quicklz-- compress-threads=4-- encrypt=AES256-- encrypt-key-file=/data/software/percona-xtrabackup-2.4.8-Linux-x86_64/bin/keyfile-- encrypt-threads=4-- parallel=4 2 >. / pxb_full/pxb.log >. / pxb_full/pxb_full.qp.encrypt.xbstream

3. Extract

Root@db01 / data1/xtrabackup_test/pxb_full # mkdir for_recovery

Root@db01 / data1/xtrabackup_test/pxb_full # xbstream-extract-directory=./for_recovery < pxb_full.qp.encrypt.xbstream

4. Decryption

Root@db01 / data1/xtrabackup_test/pxb_full # xtrabackup--target-dir=./for_recovery-encrypt-key-file=/data/software/percona-xtrabackup-2.4.8-Linux-x86_64/bin/keyfile-decrypt=AES256-remove-original-parallel=4

5. Decompression

Root@db01 / data1/xtrabackup_test/pxb_full # xtrabackup-target-dir=./for_recovery-decompress-remove-original-parallel=4

6. Prepare stage

Root@db01 / data1/xtrabackup_test/pxb_full # xtrabackup-target-dir=./for_recovery-prepare-use-memory=4G

7. Restore

Root@db01 / data1/xtrabackup_test/pxb_full # xtrabackup-defaults-file=/data1/3316/conf/my.cnf-target-dir=./for_recovery-copy-back-parallel=4

At this point, the whole MySQL Database Backup Methods series is over, which is a summary of my own. I also hope that those who see the article can gain something.

In addition, the National Day, the Mid-Autumn Festival, wish a happy holiday in advance!

If you are interested, please follow Subscription account's Database Best practices (DBBestPractice).

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.