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

This article will explain in detail how to achieve incremental backup and restore of MySQL through innobackupex. The editor thinks it is very practical, so I share it with you for reference. I hope you can get something after reading this article.

Innobackupex is a tool written by the Perl scripting language, which encapsulates the xtrabackup tool to support MyISAM engine

Xtrabackup is a hot backup tool provided by Percona, a well-known database software service company, which has the following advantages:

The backup set is efficient, complete, and available.

Transactions are not blocked during the execution of the backup task.

Save disk space and reduce the consumption of network bandwidth.

Backup set automatic verification mechanism.

Recovery is faster.

The latest version is 2.4.5, download address:

Https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.5/binary/redhat/6/x86_64/Percona-XtraBackup-2.4.5-re41c0be-el6-x86_64-bundle.tar

After decompression, there will be a percona-xtrabackup-24-2.4.5-1.el6.x86_64.rpm, install:

Rpm-ivh percona-xtrabackup-24-2.4.5-1.el6.x86_64.rpm

View the version:

[root@iZ252affh68Z scripts] # innobackupex-- version

Innobackupex version 2.4.5 Linux (x86 / 64) (revision id: e41c0be)

Create a backup file storage directory:

Mysql_full-used to store full backups

Mysql_incremental-used to store incremental backups

Full backup statement:

Innobackupex-defaults-file=/etc/my.cnf-host=10.51.xx.xx-user=xtrabk-password=onlyxxxxx-extra-lsndir=/mnt/backup/mysql_full-stream=tar / tmp | gzip > / mnt

/ backup/mysql_full/xtra_fullbak_2017-02-10.tar.gz

-- the extra-lsndir parameter is used to generate files needed for incremental backups in the full backup directory, such as:

[root@iZ252affh68Z mysql_full] # more xtrabackup_checkpoints

Backup_type = full-backuped

From_lsn = 0

To_lsn = 227008558675

Last_lsn = 227008558684

Compact = 0

Recover_binlog_info = 0

Incremental backup statement:

Innobackupex-defaults-file=/etc/my.cnf-host=10.51.xx.xx-user=xtrabk-password=onlyxxxxx-no-timestamp-incremental- incremental-basedir=/mnt/backup/mysql_full

-stream=xbstream / tmp > / mnt/backup/mysql_incremental/xtra_incrementalbak_2017-02-14.xbstream

-- the incremental parameter means an incremental backup

-- incremental-basedir specifies the directory for full backup. You need to find the to_lsn in xtrabackup_checkpoints, and the increment needs to be based on full backup.

-- stream incremental backup can only be specified as xbstream

Note: if the xtrabackup,xbstream is installed in rpm mode, it will be generated automatically under / usr/bin. If it is installed in source code, you need to copy the xbstream executable program to the / usr/bin directory.

Restore:

In this example, the above full and incremental backup files are copied to a different machine for recovery. The mysql software has been installed on the different machine, and the parameter files are placed under / etc/, named my.cnf.

Innobackupex-defaults-file=/etc/my.cnf-apply-log-redo-only / alidata1/mysqlbackup/mysql_full

-- the function of apply-log is to read configuration information from the specified options file and apply logs, etc.

-- redo-only: this parameter is required if there are other incremental backup sets that need to be restored, and its purpose is to apply only redo without rollback.

Incremental recovery:

Innobackupex-defaults-file=/etc/my.cnf-apply-log-redo-only / alidata1/mysqlbackup/mysql_full

Restore:

Innobackupex-defaults-file=/etc/my.cnf-copy-back / alidata1/mysqlbackup/mysql_full

When the restore is successful, it will prompt "completed OK!" Words

Modify permissions:

Chown-R mysql:mysql / alidata1/mysqlbackup/mysql_full

Start the database:

Mysqld_safe-- defaults-file=/etc/my.cnf &

On "how to achieve incremental backup and restore of MySQL through innobackupex" this article is shared here, I hope the above content can be of some help to you, so that you can learn more knowledge, if you think the article is good, please share it out for more people to see.

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.