Use xtrabackup for MySQL backup

First, installation

1. Introduction

Xtrabackup is a MySQL database backup tool provided by Percona, officially described, which is the world's only one by one open source tools that enable hot provisioning of InnoDB and XTRADB databases. Characteristics:

(1) The backup process is fast and reliable;

(2) The backup process does not interrupt the executing transaction;

(3) Can save disk space and traffic based on functions such as compression;

(4) Automatic implementation of backup inspection;

(5) Fast restore speed;

2. Installation

The latest version of the software can be obtained from http://www.percona.com/software/percona-xtrabackup/. This article is based on the RHEL6.8 system, so just download the appropriate version of the RPM package to install it and no longer demonstrate its process.

Second, the implementation of backup

1. Full backup

# Innobackupex--user=dbuser--password=dbuserpass/path/to/backup-dir/

If you want to back up with a user with minimal permissions, you can create such a user based on the following command:

mysql> CREATE USER ' bkpuser ' @ ' localhost ' identified by ' S3cret ';mysql> REVOKE all privileges, GRANT OPTION from ' BKP User ';mysql> GRANT RELOAD, LOCK TABLES, REPLICATION CLIENT on *. * to ' bkpuser ' @ ' localhost ';mysql> FLUSH privileges;

When you use a Innobakupex backup, it calls Xtrabackup back up all InnoDB tables, copies all related files (. frm) about the table structure definition, and related files for the 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-named directory.

At the same time as the backup, Innobackupex also creates the following files in the backup directory:

(1) xtrabackup_checkpoints--the backup type (such as full or incremental), the backup state (such as whether it is already in the prepared state), and the LSN (log sequence number) range information;

Each InnoDB page (typically 16k size) contains a log sequence number, the LSN. LSN is the system version number of the entire database system, and each page-related LSN can indicate how the page has changed recently.

(2) Xtrabackup_binlog_info--mysql the binary log file currently in use by the server and the location of the binary log event up to the moment the backup was made.

(3) xtrabackup_binlog_pos_innodb--binary log files and the current position for binary log files for InnoDB or xtradb tables.

(4) Xtrabackup executable files used in xtrabackup_binary--backup;

(5) backup-my.cnf--configuration option information used for backup command;

When using Innobackupex for backup, you can also use the--NO-TIMESTAMP option to prevent commands from automatically creating a time-named directory, so that the Innobackupex command creates a Backup-dir directory to store the backup data.

2. Prepare (prepare) a full backup

In general, data cannot be used for recovery operations after the backup is complete, because the data that is backed up may contain transactions that have not yet been committed or that have been committed but have not been synchronized to the data file. Therefore, the data file still handles the inconsistent state at this time. The primary role of Prep is to keep the data file in a consistent state by rolling back uncommitted transactions and synchronizing committed transactions to data files.

The--apply-log option of the Innobakupex command can be used to implement the above functions. As in the following command:

# Innobackupex--apply-log/path/to/backup-dir

If executed correctly, the last few lines of information for its output are usually as follows:

xtrabackup:starting shutdown with innodb_fast_shutdown = 1120407 9:01:36 innodb:starting shutdown ... 120407 9:01:40 Innodb:shutdown completed; Log sequence number 92036620120407 09:01:40 innobackupex:completed ok!

In the process of "preparing," Innobackupex can typically use the--use-memory option to specify the amount of memory it can use, which is typically 100M by default. If there is enough memory available, you can partition some memory into the prepare process to improve its completion speed.

3. Recovering data from a full backup

Note: Recovery does not start MySQL

The--copy-back option of the Innobackupex command is used to perform recovery operations, which perform the recovery process by copying all data-related files to the MySQL server DataDir directory. Innobackupex backup-my.cnf to get information about the DataDir directory.

# Innobackupex--copy-back/path/to/backup-dir

If executed correctly, the last lines of its output information are usually as follows:

innobackupex:starting to copy InnoDB log filesinnobackupex:in '/backup/2017-04-07_08-17-03 ' innobackupex:back to Origin Al InnoDB log directory '/mydata/data ' innobackupex:finished copying back files.120407 09:36:10 innobackupex:completed O K!

Make sure that "innobackupex:completed ok!" appears on the top line of the information above.

When the data is restored to the DataDir directory, it is also necessary to ensure that all data files belong to the owner and the group are the correct users, such as MySQL, otherwise, before starting the mysqld, you need to modify the data file's owner and host groups beforehand. Such as:

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

4. Use Innobackupex for incremental backup

Each InnoDB page contains an LSN information, and the LSN of the associated page automatically grows whenever the relevant data changes. This is the basis for an incremental backup of the InnoDB table, that is, Innobackupex is implemented by backing up pages that have changed since the last full backup.

To implement the first incremental backup, you can use the following command:

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

Where Basedir refers to the directory where the full backup is located, the Innobackupex command creates a new time-named directory in the/backup directory to hold all the incremental backup data after the command execution is completed. Also, when incremental backups are performed after an incremental backup, their--incremental-basedir should point to the same directory as the last incremental backup.

It is important to note that incremental backups can only be applied to InnoDB or xtradb tables, and for MyISAM tables, a full backup is actually performed when performing an incremental backup.

The Prepare incremental backup is a bit different from organizing a full backup, especially to note that:

(1) You need to "replay" a transaction that has already been committed on each backup, including full and individual incremental backups. After replay, all backup data is merged into a full backup.

(2) "Roll Back" uncommitted transactions based on all backups.

As a result, the operation becomes:

# Innobackupex--apply-log--redo-only Base-dir

Then execute:

# Innobackupex--apply-log--redo-only base-dir--incremental-dir=incremental-dir-1

And then the second increment:

# Innobackupex--apply-log--redo-only base-dir--incremental-dir=incremental-dir-2

Where Base-dir refers to the directory where the full backup is located, and Incremental-dir-1 refers to the first incremental backup of the directory, incremental-dir-2 refers to the second incremental backup of the directory, and so on, that is, if there are multiple incremental backups, Each time to perform the above operation;

5, Xtrabackup "stream" and "Backup compression" function

Xtrabackup supports the "stream" feature for backed-up data files, which means that the backed up data can be transferred to the TAR program through stdout instead of being saved directly to a backup directory by default. To use this feature, you only need to use the--stream option. Such as:

# Innobackupex--stream=tar/backup | gzip >/backup/' Date +%f_%h-%m-%s '. tar.gz

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

# Innobackupex--stream=tar/backup | SSH [email protected] "cat->/backups/' Date +%f_%h-%m-%s '. Tar"

In addition, when performing a local backup, you can also use the--PARALLEL option to replicate multiple files in parallel. This option specifies the number of threads to start when replication occurs. Of course, to take advantage of this feature when actually making a backup, you also need to enable the innodb_file_per_table option or the shared tablespace is stored in multiple Ibdata files through the Innodb_data_file_path option. Replication of multiple files for a database is not available to this feature. The simple way to use it is as follows:

# Innobackupex--parallel/path/to/backup

At the same time, Innobackupex backed up data files can also be stored to a remote host, which can be achieved using the--remote-host option:

# Innobackupex [email protected]/path/in/remote/host/to/backup

6. Import or export a single sheet

By default, the InnoDB table cannot be ported between MySQL servers by directly replicating table files, even if the innodb_file_per_table option is used. This is possible with the Xtrabackup tool, but the MySQL server that needs the export table now has the innodb_file_per_table option enabled (strictly speaking, the table to be "exported" is enabled by the MySQL server before it is created InnoDB _file_per_table option), and the server for the import table has both the innodb_file_per_table and Innodb_expand_import options enabled.

(1) "Export" table

The export table is in the prepare phase of the backup, so once the full backup is complete, you can export a table through the--export option during the prepare process:

# Innobackupex--apply-log--export/path/to/backup

This command creates a file that ends with. exp for each table space in the InnoDB table, and these files that end with. Exp can be used to import to other servers.

(2) "Import" table

To import a InnoDB table from another server on a MySQL server, you need to create a table on the current server that is consistent with the original table structure before you can import the table:

Mysql> CREATE TABLE mytable (...) Engine=innodb;

Then delete the table space for this table:

mysql> ALTER TABLE mydatabase.mytable DISCARD tablespace;

Next, copy the MYTABLE.IBD and Mytable.exp files from the server's MyTable table from the export table to the current server's data directory, and then use the following command to "import" them:

mysql> ALTER TABLE mydatabase.mytable IMPORT tablespace;

7. Use Xtrabackup to make a partial backup of the database

Xtrabackup can also implement partial backups, that is, to back up only one or some of the specified databases or some or some of the tables in a database. However, to use this feature, you must enable the innodb_file_per_table option, which is to save each table as a separate file. It also does not support the--stream option, which means that data is not supported to be piped to other programs for processing.

In addition, restoring a partial backup is also different from restoring all of the data, that is, you cannot directly copy back to the data directory by simply prepared a partial backup using the--copy-back option, but rather by importing the direction of the table to achieve the restore. Of course, in some cases, partial backups can also be restored directly through--copy-back, but the majority of the data that is restored in this way results in inconsistent data, so it is not recommended in any way.

(1) Create a partial backup

There are three ways to create a partial backup: a regular expression (--include), an enumeration table file (--tables-file), and a list of databases to back up (--databases).

(a) Use of--include

When using--include, it is required to specify the full name of the table to be backed up, i.e., the form databasename.tablename, such as:

# Innobackupex--include= ' ^mageedu[. Tb1 '/path/to/backup

(b) Use of--tables-file

The parameter for this option needs to be a file name with each row containing the full name of the table to be backed up, such as:

# echo-e ' mageedu.tb1\nmageedu.tb2 ' >/tmp/tables.txt# innobackupex--tables-file=/tmp/tables.txt/path/to/backup

(c) Use of--databases

This option takes a data name, and if you want to specify multiple databases, you need a space between them, and when you specify a database, you can specify only one of the tables. In addition, this option can also accept a file as a parameter, and each action in the file is an object to be backed up. Such as:

# Innobackupex--databases= "mageedu testdb"/path/to/backup

(2) Finishing (preparing) Partial backup

The process of prepare partial backups is similar to the process of exporting tables, using the--export option:

# Innobackupex--apply-log--export/pat/to/partial/backup

During the execution of this command, Innobackupex calls the Xtrabackup command to remove the missing table from the data dictionary, so there are many warning messages about the "table does not exist" class. It also shows information about creating an. exp file for the table that exists in the backup file.

(3) Restore partial backup

The process of restoring a partial backup is the same as importing a table. Of course, you can also restore directly to the data directory by copying a backup of the prepared state directly, not requiring the data directory to be in a consistent state at this time.

This article is from the "Wang Liming" blog, make sure to keep this source http://afterdawn.blog.51cto.com/7503144/1928077

Use xtrabackup for MySQL backup