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

The following content mainly brings you Xtrabackup introduction, advantages, installation and other introductions. The knowledge mentioned here is slightly different from books. It is summarized by professional technicians in the process of contacting users. It has certain experience sharing value and hopes to bring help to readers.

Xtrabackpack

Percona XtraBackup is an open source free MySQL database hot backup software that can backup databases of InnoDB and XtraDB storage engines without blocking (MyISAM backups also require table locks). XtraBackup supports all Percona Server, MySQL, MariaDB and Drizzle.

XtraBackup Benefits:

1. No need to stop database for InnoDB hot standby

2. Incremental backup MySQL

3. Stream compression and transmission to other Cloud Virtual Machine

4. It is easy to create master-slave synchronization

5, MySQL backup will not increase the server load

Xtrabackup installation

Here only describes the installation method under Ubuntu-12.04, please refer to http://www.percona.com/doc/percona-xtrabackup/2.1/installation.html for installation methods of other systems.

apt-key adv --keyserver keys.gnupg.net --recv-keys 1C4CBDCDCD2EFD2A

Add to/etc/apt/sources.list:

deb http://repo.percona.com/apt precise main

deb-src http://repo.percona.com/apt precise main

Perform update and installation operations

apt-get update

apt-get install percona-xtrabackup

Note: precise is the version code of Ubuntu-12.04. If it is another system version, it needs to be replaced.

Xtrabackup tool introduction

After installing XtraBackup, there are actually several tools:

innobackupex: This is actually a perl script package for the following three tools, which can backup MyISAM, InnoDB, XtraDB tables. However, when processing Myisam, you need to add a read lock.

xtrabackup: A binary file compiled from C that only backs up InnoDB and XtraDB data.

xbcrypt: Used to encrypt or decrypt data backed up.

xbstream: Used to decompress or compress compressed files in xbstream format.

It is recommended to use perl encapsulated innobackup ex for database backup because it is easier to use. So here is just the use of innobackupex. Other usage references: http://www.percona.com/doc/percona-xtrabackup/2.1/manual.html

How to use innobackupex

For full options, please run innobackupex -help, which only describes using common options for full backups and incremental backups and restores.

innobackupex Options Here only common parameters are described

-defaults-file The configuration file path of the database. It feels like it can be written without local backup. It has not been tested remotely.

-apply-log Prepare to start mysql service on a backup.

-copy-back Copies data, indexes, logs from the backup directory to the initial location specified in the my.cnf file.

-no-timestamp When creating backups, the time directory is not automatically generated. You can customize the backup directory name, for example: /backups/mysql/base

-databases Used to specify the database to be backed up, multiple library files Usage: "database1 database2"

-incremental Make incremental backups on top of full backups, followed by incremental backup storage directory paths

-incremental-basedir=DIRECTORY Full backup path directory required for incremental backup or directory path from last incremental backup

-incremental-dir=DIRECTORY directory path for incremental backup storage

-redo-only is used to prepare incremental backup contents to merge data into a full backup directory, used in conjunction with-incremental-dir incremental backup directories.

-force-non-empty-directories If it is a backup restore of a specific library, you do not need to delete the entire mysql directory, just the specific library and related files can be added. When restoring, this parameter will not report an error.

For other specific parameters, please refer to www.percona.com/doc/percona-xtrabackup/2.1/innobackupex/innobackupex_option_reference.html

Full backup and restore

I. All databases

Backup:

innobackupex --user=root --password=root --defaults-file=/etc/mysql/my.cnf /data/mysql_backup/full_backup

Reduction:

service mysql stop

rm -rf /var/lib/mysql

mkdir /var/lib/mysql

innobackupex --apply-log /data/mysql_backup/full_backup --use-memory=1G --user=root --password=root

innobackupex --copy-back /data/mysql_backup/full_backup

chown mysql:mysql -R /var/lib/mysql

service mysql start

View recovered database

II. Designated database

Backup:

Suppose we want to back up centos and aabb databases.

innobackupex --user=root --password=root --defaults-file=/etc/mysql/my.cnf --databases="centos aabb" /data/mysql_backup/

This will generate a directory with time in/data/mysql_backup, if you do not need time, you can use the option-no-timestamp.

If you want to backup to compressed files, you can use the following statement:

innobackupex --user=root --password=root --defaults-file=/etc/mysql/my.cnf --databases="centos aabb" --no-timestamp --stream=tar ./ | gzip - > centos-aabb.bz.tar.gz

Reduction:

If the full backup path is/data/mysql_backup/full_backup, if the full backup is a compressed file, it needs to be decompressed and restored.

service mysql stop

rm -rf /var/lib/mysql/ibdata*

rm -rf /var/lib/mysql/ib_logfile*

rm -rf /var/lib/mysql/centos

rm -rf /var/lib/mysql/aabb

innobackupex --apply-log /data/mysql_backup/centos_aabb_full_backup

innobackupex --copy-back --defaults-file=/etc/mysql/my.cnf /data/mysql_backup/centos_aabb_full_backup

chown -R mysql:mysql /var/lib/mysql

service mysql start

Incremental backup and restore

Here, the incremental backup method of the whole database is not much to talk about, just for a specific database to explain, in fact, the same is to remove the-databases option in the following parameters, interested can try it yourself.

Incremental backups are built on top of full backups, so make sure you have done a full backup first.

Full backup:

innobackupex --user=root --password=root --databases="centos" --no-timestamp /data/mysql_backup/centos_full_backup

The directory for a full backup is now/data/mysql_backup/centos_full_backup.

Incremental backup:

First incremental backup:

innobackupex --incremental /data/mysql_backup/inc1 --no-timestamp --incremental-basedir=/data/mysql_backup/centos_full_backup --user=root --password=root --defaults-file=/etc/mysql/my.cnf

The second incremental backup: you need to specify-incremental-basedir to the directory where you did the last incremental backup. You should know/data/mysql_backup/inc1 here.

innobackupex -incremental /data/mysql_backup/inc2 -no-timestamp -incremental-basedir=/data/mysql_backup/inc1 -user=root -password=root -defaults-file=/etc/mysql/my.cnf

Option-incremental is the directory specified for incremental backup-incremental-basedir option is the directory specified for the last incremental backup (or full backup if this is the first incremental backup).

Reduction:

An incremental backup restore is a bit different from a full restore. You must first use-apply-log -redo-only to operate on the full backup directory and all incremental backup directories, and then you can do the same as when restoring a full backup.

apply-log redo-only operations on each backup directory (including full backups)

innobackupex --apply-log --redo-only /data/mysql_backup/centos_full_backup --user=root --password=root

innobackupex --apply-log --redo-only /data/mysql_backup/centos_full_backup --incremental-dir=/data/mysql_backup/inc1 --user=root --password=root

innobackupex --apply-log --redo-only /data/mysql_backup/centos_full_backup --incremental-dir=/data/mysql_backup/inc2 --user=root --password=root

See an article in the recovery of incremental backup when mentioned in the last read incremental records do not use-redo-only parameter, after testing, using this parameter after the recovery of the database are running correctly, I use version 2.17 xtrabackup, as for the previous version has not been tested. Anyone who knows can share it here.

Here's what happens when you restore a full backup:

service mysql stop

rm -rf /var/lib/mysql/ibdata*

rm -rf /var/lib/mysql/ib_logfile*

rm -rf /var/lib/mysql/centos

innobackupex --apply-log /data/mysql_backup/centos_full_backup --use-memory=1G --user=root --password=root

##--use-memory enables you to define the memory size at which backups are read, allowing you to quickly prepare for restores.

innobackupex --copy-back --defaults-file=/etc/mysql/my.cnf /data/mysql_backup/centos_full_backup

chown mysql:mysql -R /var/lib/mysql

service mysql start

Now all incremental recovery is complete, you can log in to the database to view the recovered data

For the above introduction, advantages, installation and other introductions about Xtrabackup, if you still have more to know, you can continue to pay attention to our industry promotion, if you need to get professional answers, you can contact the pre-sales and after-sales on the official website, I hope this article can bring you some knowledge updates.

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.