Use Xtrabackup to back up MySQL

This article is original and reprinted. Please keep the following link information: www.magedu.commageedu.blog.51cto.com 1. installation 1. Introduction Xtrabackup is a mysql database backup job provided by percona.

This article is original, reproduced please be sure to keep the following link information: http://www.magedu.comhttp: // mageedu.blog.51cto.com 1, installation 1, Introduction Xtrabackup is provided by percona mysql database backup Engineer

This article is original and reprinted. Be sure to keep the following link information:

I. Installation

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. Features:

(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 rpm package of the corresponding version to install it. here we will not describe the process.

II. Implementation of backup

1. Full backup

# 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;

When innobackupex is used for backup, you can also use the -- no-timestamp Option to prevent the command from automatically creating a directory named after time. As a result, the innobackupex command creates a BACKUP-DIR directory to store backup data.

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

If the execution is correct, the following lines of information are usually output:

Xtrabackup: starting shutdown with innodb_fast_shutdown = 1

120407 9:01:36 InnoDB: Starting shutdown...

120407 9:01:40 InnoDB: Shutdown completed; log sequence number 92036620

120407 09:01:40 innobackupex: completed OK!

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

If the execution is correct, the last few lines of the output information are usually as follows:

Innobackupex: Starting to copy InnoDB log files

Innobackupex: in '/backup/2012-04-07_08-17-03'

Innobackupex: back to original InnoDB log directory '/mydata/data'

Innobackupex: Finished copying back files.

120407 09:36:10 innobackupex: completed OK!

Make sure that "innobackupex: completed OK!" appears in the row at the top of the preceding information !".

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/

Iii. 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 website Space, 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.

"Prepare" (prepare) Incremental Backup differs from full backup. For servers in Hong Kong, pay special attention to the following:

(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;

Iv. "stream" and "backup compression" functions of Xtrabackup

Xtrabackup supports the "stream" function for the backup data files, that is, the backup data can be transmitted to the tar program through STDOUT for archiving, rather than being saved to a backup directory by default. To use this function, you only need to use the -- stream option. For example:

# Innobackupex -- stream = tar/backup | gzip>/backup/'date between 0000f_0000h-0000m-0000s'.tar.gz

You can even use the following command to back up data to other servers:

# Innobackupex -- stream = tar/backup | ssh user @ "cat->/backups/'date between 0000f_0000h-0000m-0000s'.tar"