Make a little progress every day-mysql -- Percona XtraBackup (innobackupex), perconaxtrabackup

Make a little progress every day-mysql -- Percona XtraBackup (innobackupex), perconaxtrabackup
I. Introduction

Percona XtraBackup is an open-source and free MySQL Database Hot Backup Software. It can back up InnoDB and XtraDB storage engine databases in a non-blocking manner (Table locks are also required for MyISAM backup ). XtraBackup supports all Percona servers, MySQL, MariaDB, and Drizzle.

XtraBackup advantages:
1. No need to stop the database for InnoDB Hot Backup
2. Incremental backup of MySQL
3. stream compression to transfer to other servers
4. Easy to create master-slave Synchronization
5. The server load is not increased when MySQL is backed up.

After XtraBackup is installed, there are actually several tools:
Innobackupex:
This is actually a perl script encapsulation of the following three tools. You can back up tables MyISAM, InnoDB, and XtraDB.
Xtrabackup:
A binary file compiled by C can only back up InnoDB and XtraDB data.
Xbcrypt:
Used to encrypt or decrypt the backup data.
Xbstream:
Used to decompress Or compress compressed files in xbstream format.
We recommend that you use innobackupex encapsulated in perl for database backup, because it is easier to use. Therefore, we will only introduce the use of innobackupex. Other use references: http://www.percona.com/doc/percona-xtrabackup/2.1/manual.html

This article mainly introducesInnobackupex Tool

Note: Make a full backup after each restoration.

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

First, ensure that the dependency package is installed.

[Root @ localhost Packages] # yum-y install perl-devel libaiolibaio-devel perl-Time-HiRes perl-DBD-MySQL

Then install percona-xtrabackup

[Root @ localhost ~] # Rpm-ivhpercona-xtrabackup-2.2.11-1.el6.x86_64.rpm

Warning: percona-xtrabackup-2.2.11-1.el6.x86_64.rpm: Header V4 DSA/SHA1 Signature, keyID cd2efd2a: NOKEY

Preparing... ######################################## ### [100%]

1: percona-xtrabackup ##################################### ###### [100%]

 

 

Ii. Command Format

Innobackupex parameter directory.

Iii. Common Parameters

-- Defaults-fileMy. cnf file, the only limitation is that it has to be the first option passed; and in this my. the cnf must contain parameters such as datadir to specify the path of the data file!

 -- HostSpecify host name/IP Address

-- PortSpecify the port to be connected. If the default value is 3306, ignore this parameter.

 -- SocketThe location of the connection set byte. The default value is/var/lib/mysql. sock.

-- Use-memory = 4GThis parameter is used to control the memory used for backup. The default value is 100 MB! It is generally used with -- apply-log. It is specified during backup, but it does not seem to have been used !!

 -- Apply-logAfter the database is backed up, the backup data cannot be immediately used for restoration, because the newly backed up data contains unsubmitted data and needs to be rolled back. It is called undo data in oracle! Also, completed transactions are not written into the data file in the redo log file. The data needs to be redone! Redo in oracle! This parameter is used to do these tasks to ensure consistency of data files! Before restoring the database, you must apply this parameter to the backup data file! (Innobackup will reproduce the transaction entries in the redo log file, redo committed transactions and roll back uncommitted transactions !)

 -- Copy-backCopy the backup data back to the datadir of the server. It depends on the datadir parameter in my. cnf. In addition, the datadir directory must be empty during restoration and the mysql database must be shuffled!

-- No-timestampInnobackupex -- defaults-file =/etc/my. cnf -- user = root -- no-timestamp/tmp/backup/full (when -- no-timestamp is used, the following full directory must be kept up and cannot be created in advance, it is automatically created by innobackupex. Otherwise, innobackupex: Error: Failed to create backup directory/tmp/backup/full/: File exists at/usr/bin/innobackupex line 3899 is reported .)

-- Redo-onlyDuring incremental recovery, the committed transactions in the redo log file must be redone before the full backup and Incremental backup data files are restored! This parameter combines full backup and Incremental backup data files, but does not include the last Incremental backup data file! (-- Redo-only shocould be used when merging all incrementals should t thelast one)

-- CompressCompression option. This option is not compatible with -- stream = tar and only compatible with -- stream = xbstream. In addition, the encryption item encrypted is not compatible with -- stream = tar!

-- DecompressIf encryption or compression is used during backup, after xbstream-x is used to decompress the package, innodbackupex -- decompress/data/backup must be used to compress ibdata1.qp, file ending with qp! In addition, decompress also requires yum install qpress. x86_64 to install the qpress package!

-- Slave-infoThis option is useful whenbacking up a replication slave server. it also writes this information tothe "xtrabackup_slave_info" file as a "change master" command. A new slave for this master can be set up by starting a slaveserver on this backup and issuing a "CHANGE" xtrabackup_slave_info "file. when backing up a slave server, you can use this parameter to record the log and offset of the master! Used to create a new slave server! Generally, the master binary log file and offset point are recorded in the xtrabackup_binlog_info file during full backup!

 

 

Iv. Common examples 1. Full backup

[Root @ localhostback] # innobackupex -- defaults-file =/etc/my. cnf -- host 10.22.19.44 -- port = 3306 -- user = root -- password = 123456 -- socket =/tmp/mysql. sock/back

 

2. Incremental Backup Based on 2015-08-27_09-54-02

[Root @ localhostaaa] # innobackupex -- user = root -- password = 123456 -- defaults-file =/etc/my. cnf -- socket =/tmp/mysql. sock -- no-timestamp -- incremental-basedir =/back/2015-08-27_09-54-02 // back/001/

Incremental Backup Based on/back/001/

[Root @ localhostaaa] # innobackupex -- user = root -- password = 123456 -- defaults-file =/etc/my. cnf -- socket =/tmp/mysql. sock -- no-timestamp -- incremental-basedir =/back/001 // back/002/

You can view the backup information in the following file:

[Root @ localhostback] # cat 2015-08-27_09-54-02/xtrabackup_checkpoints

Backup_type = full-backuped

From_lsn = 0

To_lsns = 919852583

Last_lsns = 919852583

Compact = 0

[Root @ localhostback] # cat 001/xtrabackup_checkpoints

Backup_type = incremental

From_lsn = 919852583

To_lsns = 919854256

Last_lsns = 919854256

Compact = 0

[Root @ localhostback] # cat 002/xtrabackup_checkpoints

Backup_type = incremental

From_lsn = 919854256

To_lsns = 919858085

Last_lsns = 919858085

The relationship between the three backup sets can be seen through the lsn.

You can also use this command to back up data by specifying the lsn.

[Root @ localhostaaa] # innobackupex -- user = root -- password = 123456 -- defaults-file =/etc/my. cnf -- socket =/tmp/mysql. sock -- no-timestamp -- incremental-lsn = 919854256/back/002/

 

3. Back up and package tar:

 

[Root @ localhost back] # innobackupex -- defaults-file =/etc/my. cnf -- host 10.22.19.44 -- port = 3306 -- user = root -- password = 123456 -- socket =/tmp/mysql. sock -- stream = tar/back>/back/fullbackup.tar

 

Xbstream Mode

 

[Root @ localhost back] # innobackupex -- defaults-file =/etc/my. cnf -- host 10.22.19.44 -- port = 3306 -- user = root -- password = 123456 -- socket =/tmp/mysql. sock -- stream = xbstream/back>/back/fullbackup. xbstream

 

4. Backup, package, and compress the tar package

 

Gzip compression:

 

[Root @ localhostback] # innobackupex -- defaults-file =/etc/my. cnf -- host 10.22.19.44 -- port = 3306 -- user = root -- password = 123456 -- socket =/tmp/mysql. sock -- stream = tar/back/| gzip->/back/fullbackup2.tar.gz

 

Bzip2 compression:

 

[Root @ localhostback] # innobackupex -- defaults-file =/etc/my. cnf -- host 10.22.19.44 -- port = 3306 -- user = root -- password = 123456 -- socket =/tmp/mysql. sock -- stream = tar/back/| bzip->/back/fullbackup2.tar.bz2

 

Xbstream packaging and Compression

 

[Root @ localhostback] # innobackupex -- defaults-file =/etc/my. cnf -- host 10.22.19.44 -- port = 3306 -- user = root -- password = 123456 -- socket =/tmp/mysql. sock -- stream = xbstream -- compress/back>/back/fullbackup2.xbstream

 

Backup package and transfer to other servers for compression and storage

 

[Root @ localhost back] # innobackupex -- defaults-file =/etc/my. cnf -- host 10.22.19.44 -- port = 3306 -- user = root -- password = 123456 -- socket =/tmp/mysql. sock -- stream = tar/back/2>/back/log2.log | ssh 10.22.19.90 "bzip2->/root/mysql.tar.bz2"

Root@10.22.19.90's password:

 

5. Full backup database Restoration: application logs

[Root @ localhostback] # innobackupex -- apply-log/back/2015-08-27_09-51-14/

Restore full backup database

[Root @ localhostback] # innobackupex -- user = root -- password = 123456 -- copy-back -- use-memory = 512 M/back/2015-08-27_09-51-14/

View data directory permissions

[Root @ localhostback] # ll/usr/local/mysql | grep data

Drwxr-xr-x 14 root 4096 August 27 12:50 data

Drwx ------ 11 mysql 4096 July 15 13:13 data2

Modify Directory Permissions

[Root @ localhostback] # chown-R mysql: mysql/usr/local/mysql/data

[Root @ localhostback] # ll/usr/local/mysql | grep data

Drwxr-xr-x 14 mysql 4096 August 27 12:50 data

Drwx ------ 11 mysql 4096 July 15 13:13 data2

Start Database

[Root @ localhost ~] # Service mysqld start

Starting MySQL .. [OK]

 

Restore binary logs

 

[Root @ localhostmysql] # mysqlbinlog/root/mysql.000018>/tmp/abc. SQL

[Root @ localhostmysql] # mysql-uroot-p123456

Mysql> set SQL _log_bin = 0;

Query OK, 0 rows affected (0.00 sec)

Mysql> source/tmp/abc. SQL;

Restored successfully

 

6. Restore the database through Incremental Backup

 

Redo the last full backup database file

 

[Root @ localhostdata] # innobackupex -- apply-log -- redo-only/back/2015-08-27_09-54-02/

 

Execute redo for all databases except the last Incremental backup database and merge them into the full backup database.

 

[Root @ localhostdata] # innobackupex -- apply-log -- redo-only/back/2015-08-27_09-54-02/-- incremental-dir =/back/001/

 

Merge the last Incremental backup application log (without -- redo-only) into the full backup database

 

[Root @ localhostdata] # innobackupex -- apply-log/back/2015-08-27_09-54-02/-- incremental-dir =/back/002/

This is because, except for the last Incremental backup, the previous transaction should not be rolled back because it is possible that the previous transaction that has not been committed has been committed in the next backup, if rollback is performed, the data may be inconsistent and cannot be restored.

 

Full database recovery

 

[Root @ localhostdata] # innobackupex -- copy-back/2015-08-27_09-54-02/

 

Restore binary logs

 

[Root @ localhostmysql] # mysqlbinlog/root/mysql.000018>/tmp/abc. SQL

[Root @ localhostmysql] # mysql-uroot-p123456

Mysql> set SQL _log_bin = 0;

Query OK, 0 rows affected (0.00 sec)

Mysql> source/tmp/abc. SQL;

Restored successfully

 

Some restored content was not added due to time, and added before the weekend

 

Copyright Disclaimer: This article is an original article by the blogger and cannot be reproduced without the permission of the blogger.