Use Xtrabackup to back up and restore MySQL

Source: Internet
Author: User
Xtrabackup is a mysql database backup tool provided by percona. According to the official introduction, this is the only open-source database in the world that can perform hot backup for innodb and xtradb databases.

Xtrabackup is a mysql database backup tool provided by percona. According to the official introduction, this is the only open-source database in the world that can perform hot backup for innodb and xtradb databases.

Installation and introduction
Implementation of backup and Restoration
Full + incremental + binary log backup and Restoration
I. Installation and introduction

1. Introduction
Xtrabackup is a mysql database backup tool provided by percona. According to the official introduction, Xtrabackup is the only open-source tool in the world that can perform hot backup for innodb and xtradb databases.

(1) The backup process is fast and reliable;
(2) The backup process will not interrupt ongoing transactions;
(3) Saving disk space and traffic based on compression and other functions;
(4) automatic backup check;
(5) Fast Restoration;
2. Installation

The latest software is available. This article is based on the RHEL5.8 system. Therefore, you can directly download the corresponding version of the rpm package to install it:

1 rpm-ivh percona-xtrabackup-2.0.0-417.rhel5.i386.rpm

II. Implementation of backup and Restoration

1. Full backup

##### Use root directly in this article
Innobackupex -- user = root/backup/
##### Syntax
Innobackupex -- user = DBUSER -- password = DBUSERPASS/path/to/BACKUP-DIR/

If you want to use a user with the minimum permission for backup, you can create such users based on the following command:

Mysql> create user 'bkpuser' @ 'localhost' identified by 's3cret ';
Mysql> revoke all privileges, grant option from 'bkpuser ';
Mysql> grant reload, lock tables, replication client on *. * TO 'bkpuser' @ 'localhost ';
Mysql> flush privileges;

When innobakupex is used for backup, it will call xtrabackup to back up all InnoDB tables and copy all the files related to table structure definition (. frm), and files related to MyISAM, MERGE, CSV, and ARCHIVE tables. Files related to triggers and database configuration information are also backed up. These files are saved to a time command directory.
During backup, innobackupex creates the following files in the backup directory:

(1) xtrabackup_checkpoints -- Backup Type (such as full or incremental), backup status (such as whether it is already in prepared status), And LSN (log serial number) range information; each InnoDB page (usually 16 KB) contains a log serial number, that is, the LSN. The LSN is the system version number of the entire database system. The LSN related to each page can indicate how the page has changed recently.
(2) xtrabackup_binlog_info -- the binary log file currently in use by the mysql server and the location of the binary log event until the moment of backup.
(3) xtrabackup_binlog_pos_innodb -- the current position of the binary log file used for InnoDB or XtraDB tables.
(4) xtrabackup_binary -- The xtrabackup executable file used in backup;

(5) backup-my.cnf-the configuration option information used by the BACKUP command;

2. prepare (prepare) a full backup
Generally, after the backup is complete, the data cannot be used for restoration, because the backup data may contain uncommitted transactions or transactions that have been committed but not yet synchronized to the data file. Therefore, the data files are still inconsistent. The main function of "Preparation" is to roll back uncommitted transactions and synchronize committed transactions to data files, so that the data files are in a consistent state.

The -- apply-log option of the innobakupex command can be used to implement the above functions. Run the following command:

# Innobackupex -- apply-log/path/to/BACKUP-DIR

During the "Preparation" process, innobackupex can also use the -- use-memory option to specify the memory size that can be used. The default value is usually 100 MB. If enough memory is available, you can allocate more memory to the prepare process to speed up its completion.

3. Restore data from a full backup
The -- copy-back option of the innobackupex command is used to perform the Restoration Operation. It copies all data-related files to the DATADIR directory of the mysql server to execute the restoration process. Innobackupex gets information about the DATADIR directory through a backup-my.cnf.

# Innobackupex -- copy-back/path/to/BACKUP-DIR

After the data is restored to the DATADIR directory, make sure that the owner and group of all data files are correct, such as mysql. Otherwise, before starting mysqld, you must modify the owner and group of the data file in advance. For example:

# Chown-R mysql: mysql/mydata/data/

4. Use innobackupex for Incremental Backup
Each InnoDB page contains an LSN. When the related data changes, the LSN of the related page automatically increases. This is the foundation for InnoDB tables to perform Incremental backup, that is, innobackupex is implemented by backing up pages that have changed since the last full backup.

To implement the first Incremental backup, run the following command:

# Innobackupex -- incremental/backup -- incremental-basedir = BASEDIR

Here, BASEDIR refers to the directory where the full backup is located. After this command is executed, the innobackupex command creates a new directory named after time in the/backup Directory to store all Incremental backup data. In addition, when an incremental backup is performed again after the incremental backup is executed, its -- incremental-basedir should refer to the directory where the last incremental backup is located.

Note that Incremental Backup can only be applied to InnoDB or XtraDB tables. For MyISAM tables, full backup is actually performed during Incremental backup.

The "prepare" (prepare) Incremental Backup differs from the full backup. Note the following in particular:

(1) On each backup (including full and Incremental Backup), you must "replay" The committed transactions ". After "replay", all the backup data will be merged to the full backup.
(2) Roll Back uncommitted transactions based on all backups ".
Therefore, the operation becomes:

# Innobackupex -- apply-log -- redo-only BASE-DIR

Run the following command:

# Innobackupex -- apply-log -- redo-only BASE-DIR -- incremental-dir = INCREMENTAL-DIR-1

Next is the second increment:

# Innobackupex -- apply-log -- redo-only BASE-DIR -- incremental-dir = INCREMENTAL-DIR-2

Where the BASE-DIR refers to the directory where the full backup is located, while the INCREMENTAL-DIR-1 refers to the directory of the first Incremental backup, The INCREMENTAL-DIR-2 refers to the directory of the second Incremental backup, and so on, that is, if multiple incremental backups exist, the above operations must be performed each time;

Recommended reading:

MySQL open-source backup tool Xtrabackup backup deployment

MySQL Xtrabackup backup and recovery

Use XtraBackup to implement MySQL master-slave replication and quick deployment [master-slave table lock-free]

Install and use Xtrabackup from Percona to back up MySQL

XtraBackup details: click here
XtraBackup: click here

To continue reading the highlights of this article, please refer to page 2nd:

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.