Use Xtrabackup to back up the mysql database _ MySQL

Xtrabackup is an open source backup tool developed by percona to implement Database Backup. it is different from ibbackup. Ibbackup is a paid backup tool. in terms of its backup performance, ibbackup is inferior to Xtrabackup. I use Xtrabackup to back up databases

Xtrabackup is an open source backup tool developed by percona, which is different from ibbackup. Ibbackup is a paid backup tool. in terms of its backup performance, ibbackup is inferior to Xtrabackup. Both ibbackup and Xtrabackup support both online physical full backup and online physical incremental backup for the Innodb storage engine. for MyISAM storage engine, only warm backup is supported.

Follow these steps to back up a mysql database using Xtrabackup:

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 hot backup innodb and xtradb Storage Engine databases. Xtradb is an enhanced version of Innodb, which is superior to Innodb in performance and has the following 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

Its latest software is available from the http://www.percona.com/software/percona-xtrabackup. 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.

After the installation is complete, a/usr/bin/innobackupex script file is generated, which encapsulates xtrabackup and can process Innodb and Myisam simultaneously. However, a read lock is required when processing Myisam.

3. about innobackupex

Innobackupex

Innobackupex is more powerful. It integrates xtrabackup and other functions. it not only supports full backup/recovery, but also supports incremental backup and recovery based on time.

Innobackupex backup principle

Innobackupex first calls xtrabackup to back up innodb data files. after xtrabackup is complete, innobackupex will view the xtrabackup_susponded file. then, execute "flush tables with read lock" to back up other files.

Innobackupex recovery principle

Innobackupex first reads my. cnf: check that the directory corresponding to the variable (datadir, innodb_data_home_dir, innodb_data_file_path, innodb_log_group_home_dir) exists. after confirming that the directory exists, copy the myisam table and index first, then copy innodb tables, indexes, and logs

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'; (if an error occurs, do not add it)

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 will be saved to a time-based directory.

While backing up, innobackupex will also create 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 prepared) 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. 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

InnoDB: FTS optimize thread exiting.

InnoDB: Starting shutdown...

InnoDB: Shutdown completed; log sequence number 1640470

020124 06:22:05 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. when restoring data, the mysql server can be offline)

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 '/tmp/backup/2002-01-24_06-09-50'

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

Innobackupex: Copying '/tmp/backup/2002-01-24_06-09-50/ib_logfile0' to '/mydata/data/ib_logfile0'

Innobackupex: Copying '/tmp/backup/2002-01-24_06-09-50/ib_logfile1' to '/mydata/data/ib_logfile1'

Innobackupex: Finished copying back files.

020124 06:29:22 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/

If you do not modify the owner and Group of the data directory and all the files in the directory, mysqld cannot be started. The following error message is displayed:

Starting MySQL... The server quit without updating PID file [FAILED]/data/localhost. pid ).

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.

"Prepare" (prepare) incremental backup is somewhat different from "prepare" full backup, especially note 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 ".

So the operation becomes: # innobackupex -- apply-log -- redo-only BASE-DIR

Then run: # 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;

Finally, execute the following command during all (full backup and incremental backup) recovery:

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

/Path/to/The BACKUP-DIR is the directory where the full backup is located

5. "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@www.magedu.com "cat->/backups/'date too f_0000h-0000m-0000s'.tar"

In addition, you can use the -- parallel option to copy multiple files in parallel when performing a local backup. This option is used to specify the number of threads started during replication. Of course, you 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 when backing up data. This function cannot be used to copy multiple files in a database. The method is as follows: # innobackupex -- parallel/path/to/backup

At the same time, innobackupex backup data files can also be stored to remote hosts, which can be achieved using the -- remote-host option:

# Innobackupex -- remote-host = root@www.magedu.com/path/IN/REMOTE/HOST/to/backup

6. import or export a single table

By default, InnoDB tables cannot be transplanted between mysql servers by directly copying table files, even if the innodb_file_per_table option is used. The Xtrabackup tool can be used to implement this function. However, you need to enable the innodb_file_per_table option on the mysql server of the "export" table (strictly speaking, the table to be "exported" is before it is created, the innodb_file_per_table option is enabled on the mysql server. the innodb_file_per_table and innodb_expand_import options are also enabled on the "import" table server.

(1) "export" table

The export table is in the backup prepare phase. Therefore, once the full backup is completed, you can use the -- export option to export a table during the prepare process:

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

This command creates a file ending with. exp for the tablespace of each innodb table. these files ending with. exp can be imported to other servers.

(2) "import" table

To import an innodb table from another server on the mysql server, you must first create a table with the same structure as the original table on the current server before importing the table:

Mysql> create table mytable (...) ENGINE = InnoDB;

Then, delete the TABLESPACE of the TABLE: mysql> alter table mydatabase. mytable discard tablespace;

Next, we will export the mytable of the mytable table from the server of the "export" table. ibd and mytable. copy the exp file to the data directory of the current server and run the following command to "import": mysql> alter table mydatabase. mytable import tablespace;

7. use Xtrabackup to partially back up the database

Xtrabackup can also be used for partial backup, that is, to back up only one or more specified databases or tables in a database. To use this function, you must enable the innodb_file_per_table option, that is, each table is saved as an independent file. At the same time, it does not support the -- stream option, that is, it does not support transmitting data to other programs through pipelines for processing.

In addition, the restoration of some backups is different from the restoration of all data backups, that is, you cannot simply use the -- copy-back option to copy some of the prepared backups back to the data directory directly, instead, we need to restore the data by importing the table. Of course, in some cases, some backups can be restored directly through -- copy-back, but most of the data restored in this way will cause data inconsistency, this method is not recommended in any case.

(1) create partial backup

There are three ways to create partial backup: regular expressions (-- include), enumeration table files (-- tables-file), and list databases to be backed up (-- databases ).

(A) use -- include

When using -- include, you must specify the full name of the table to be backed up, such as databasename. tablename, for example:

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

(B) use -- tables-file

The parameter of this option must be a file name. each line in this file contains the complete name of the table to be backed up; for example:

# Echo-e 'mageedu. tb1 \ nmageedu. tb2 '>/tmp/tables.txt

# Innobackupex -- tables-file =/tmp/tables.txt/path/to/backup

(C) use -- databases

The parameter accepted by this option is the data name. if you want to specify multiple databases, they must be separated by spaces. when you specify a database, you can also specify only one of the tables. In addition, this option can also take a file as the parameter, each row of the file is an object to be backed up. For example:

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

(2) prepare partial backup

The prepare backup process is similar to the table export process. use the -- export option:

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

During the execution of this command, innobackupex will call the xtrabackup command to remove the missing table from the data dictionary. Therefore, many warnings about the "table does not exist" class are displayed. At the same time, information about creating the. exp file for the table in the backup file is displayed.

(3) restore partial backup the process of restoring partial backup is the same as that of importing a table. Of course, you can also directly copy the backup in the prepared state to the data directory for restoration. However, the data directory must be consistent at this time.