Database Backup tool xtrabackup for MySQL backup

1 Overview

1.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.

PXB (Percona xtrabackup) is a physical backup, is based on a copy file backup, Backup can be remotely operated, based on the MySQL protocol remote connection, the host needs to be backed up without the installation of PXB, but the MySQL service does not start when performing the recovery data operation, So recovery can only be locally restored, and on the local server to install PXB

PXB when recovering incremental data, merge the first increment to the full amount first, then merge the second increment ... After the last increment is merged and the last increment is merged, the problematic transaction is rolled back, all the deltas are merged together, then copied to the data directory before the MySQL service is started. After the MySQL server starts, a small amount of data after the last increment is also restored based on the binary log file.

PXB implementation of single-table import and export, InnoDB to achieve the need to enable the single-table feature, because if this feature is not turned on, InnoDB and FBM copied to another MySQL, another MySQL is unable to load the table, because the new server does not recognize the replicated table space, The LSN of the server internal record (log sequence number) is not the same and is not recognized on the other machine. MyISAM does not have this problem, all tables copied to other MySQL can be identified.

When performing a hot spare, when a backup is started, it is possible that some transactions are executed in half, that is, a transaction in progress, so in order to ensure that the committed transaction is complete, PXB also backs up the transaction log so that it can know the state of the corresponding transaction and restore it to ensure that MySQL is able to be started.

Before starting, the commit of the commit (the completed transaction has not been committed and is also saved in the transaction log), based on the transaction log, will roll back the rollback, which is equivalent to a post-crash repair operation of the database, in order to start the database service properly.

It is important that you back up the transaction log at backup time.

If it is a full-volume + incremental backup, if a full-volume backup, some of the executing transactions cannot be rolled back at this time, because it may have been in the incremental backup has been commit, so when recovering, must be the last increment is restored, the problematic transaction will be rolled back

1.2 PXB Features

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

1.3 Installation

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

Installing PXB

Version is percona-xtrabackup-24-2.4.7-2.el7.x86_64.rpm

[[email protected] yum.repos.d] #yum-y install/root/percona-xtrabackup-24-2.4.7-2.el7.x86_64.rpm

Among them, the/usr/bin/innobackupex tool in the installed tool uses the Perl language to do the Xtrabackup two times encapsulation, supports the client remote connection way backup

2 Implementation of Backup

2.1 Full Backup

#注意, if you do not specify a database, it means that all libraries are backed up, a directory with the current date name is created under directory/path/to/backup-dir/, and all the files backed up are placed in that date directory, and backup and restore are concerned with the problem of the owner and the group. May back up the file belongs to the group and the owner of the root permissions, note that the group and owner of the restoration, no backup method specified, is a full-scale backup

The syntax is as follows

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

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.2 Preparation (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 = 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!

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.

2.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 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 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/

2.4 Incremental backup using Innobackupex

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;

After the last incremental command is complete, do a "rollback" operation on the full-volume backup.

# Innobackupex--apply-log Base-dir

2.5 xtrabackup "streaming" and "backup compression" functions

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

2.6 Importing or exporting 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;

2.7 Partial backup of a database using Xtrabackup

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.

3 examples

3.1 Local full-scale backup and recovery using Xtrabackup

Completely +binlog

Backup: Innobackupex--user--password=--host=/path/to/backup_dir

Preparation: Innobackupex--apply-log/path/to/backup_dir

Recovery: Innobackupex--copy-back

Note:--copy-back needs to be performed locally on the mysqld host, mysqld service cannot be started; innodb_log_file_size may need to be reset;

Examples are as follows

Data backup:

Create a backup directory

[[email protected] YUM.REPOS.D] #mkdir/mydata/backups

Perform a backup

[[email protected] YUM.REPOS.D] #innobackupex--user=root--password=pass1234--host=localhost/mydata/backups/

Finally see the following information, indicating that the backup is complete

Xtrabackup:transaction Log of LSN (65107278) to (65107278) was copied.

180115 17:27:11 completed ok!

When you are finished, generate a time-named directory that has the corresponding database file in the directory

/mydata/backups/2018-01-15_17-26-59

/mydata/backups/2018-01-15_17-26-59 directory to view the contents of the copy below,

[[email protected] 2018-01-15_17-26-59] #ls/mydata/backups/2018-01-15_17-26-59

backup-my.cnf MySQL Sunny xtrabackup_checkpoints xtrabackup_logfile

Ibdata1 Performance_schema xtrabackup_binlog_info xtrabackup_info zbxproxydb

In addition to the original library in the database, the other files are described below

BACKUP-MY.CNF:MYSLQ the configuration information for the server, which is critical to restarting the service in the future.

Xtrabackup-binlog_info: Log information about the binary file, which location of the binary where the backup time period is located

Xtrabackup-checkpoints: Checkpoints, which record backups of their own properties, such as LSN, where the Compact refers to no packaging, backup_type refers to the type of backup, Recover_binlog_ Info has no backup information that needs to be restored via Binlog

Xtrabackup-info: Records information about the backup tool itself, such as what tool to use for backup, what options to use to connect to the server, server version, backup time, recovery, and the need to rely on this information for recovery. PXB need to rely on this information when recovering data to determine how to recover

XTRABACKUP-LOGFILE:PXB log information When executed, this is binary information and cannot be viewed with cat.

There is also a transaction log, and if it is currently clean, if there are no uncommitted transactions, the transaction log may not replicate, but the general production environment is a transaction that is committed, so there is typically a transaction log

Note that for data security purposes, do not store the backed-up data on a local MySQL server, or the server crashes, which may result in the loss of backup data, which does not serve the purpose of data backup. Alternatively, the resulting backup file can be fully packaged and placed on another server after the backup.

Data recovery:

Restore the above backup information back to another machine 75, another machine to install PXB, because the recovery operation requires the help of PXB

Copy 71 of backed up data to 75

[[Email protected] ~] #scp-R/mydata/backups/2018-01-15_17-26-59/192.168.1.75:/root

Stop the MySQL service on the 75 server first

[[email protected] source] #systemctl Stop Mariadb.service

75 Installing the PXB

[[Email protected] ~] #yum-y install/root/percona-xtrabackup-24-2.4.7-2.el7.x86_64.rpm

Restore operation, prepare a clean MySQL database, that is, the/var/lib/mysql directory is not content, if there is content, need to empty, clear before the first to confirm whether the data is still useful

Use option--apply-log: This option applies the transaction log to the data file, the rollback of the transaction rollback, the committed commit, the synchronization of the data, the recovery of the data before the recovery is ready

Go to the 75 directory where you backed up your data

[[Email protected] ~] #cd/root/2018-01-15_17-26-59/

[[email protected] 2018-01-15_17-26-59] #innobackupex--apply-log.

See the following information for completion

Innodb:shutdown completed; Log sequence Number 65107505

180115 17:58:44 completed ok!

At this point, the preparatory work is completed before the recovery

Execute the following command, automatically load the configuration file, get relevant information, such as the path of the data directory, data recovery will be restored to the data directory

[[email protected] 2018-01-15_17-26-59] #innobackupex--copy-back.

Data recovery is successful when you see the following information

180115 19:52:17 completed ok!

Change a file's owner and owner group

[[email protected] MySQL] #chown-R mysql.mysql/var/lib/mysql/*

Restart Service

[[email protected] MySQL] #systemctl restart mariadb

To here the full amount of backup recovery completed, note, after completion, in 75 this one to do a full backup, in case of the subsequent possible 75 failure, you need to do a full backup on another MySQL server, the original 71 of the full amount of backup files are no longer available.

Note that the backup operation can be scripted, written as a scheduled task, and scheduled to complete. However, the recovery of the operation, it is recommended that manual execution, because there may be problems with the recovery process, there may be damage to the backup, there is trouble, so, the resumption of operations, recommendations are usually performed, to ensure that problems can be timely recovery

3.2 Full + incremental backup with binary files for point-in-time restores

Full + incremental +binlog

Backup: Full + delta + Delta + ...

Full + diff

Get ready:

Innobackupex--apply-log--redo-only BASEDIR

Innobackupex--apply-log--redo-only BASEDIR--incremental-dir=incremental-dir

Recovery:

Innobackupex--copy-back BASEDIR

Examples are as follows

Complete full-scale backup

[[email protected] MySQL] #innobackupex--user=root--host=localhost--password=pass1234/mydata/backups

The first incremental backup is as follows, the option--incremental indicates the location,--incremental-basedir indicates the base increment based on which

[[email protected] MySQL] #innobackupex--user=root--host=localhost--password=pass1234--incremental/mydata/backups --incremental-basedir=/mydata/backups/2018-01-15_20-10-02

A new file is generated, at which time the directory name is 2018-01-15_20-23-13

Check the following to see the xtrabackup_checkpoints file under 2018-01-15_20-23-13

[[email protected] 2018-01-15_20-23-13] #cat/mydata/backups/2018-01-15_20-23-13/xtrabackup_checkpoints

Backup_type = Incremental

FROM_LSN = 65109252

TO_LSN = 65110280

LAST_LSN = 65110280

Compact = 0

The second incremental backup, if the increment at this time is based on the full amount, then a differential backup, or an incremental backup if this is based on the first increment, or--incremental-basedir=/mydata/backups/2018-01-15_20-23-13.

This is a demonstration of the increment based on the first increment once again

[[email protected] backups] #innobackupex--user=root--host=localhost--password=pass1234--incremental/mydata/ Backups--incremental-basedir=/mydata/backups/2018-01-15_20-23-13

The new directory is generated under/mydata/backups 2018-01-15_21-34-33

Check the location information of the binary files

[[email protected] 2018-01-15_21-34-33] #cat/mydata/backups/2018-01-15_21-34-33/xtrabackup_binlog_info

master-log.0000052714

At this point, do not make a backup, but update the database, the binary files in the database will continue to record information

MariaDB [sunny]> Update students set major= "maths" where age=90;

Then check the binary log file

[[email protected] 2018-01-15_21-34-33] #mysqlbinlog-j 2714/mydata/log/master-log.000005

There is a record for

Update students set major= "maths" where age=90

But does not do the backup operation, assumes at this time the database crashes, currently the backup data does not have the last update age=90 the data, therefore, restores the data needs to take the binary log file to restore, saves the binary file record, skips before has the backup information, Start recording from 2714 bytes of the master-log.000005 file

[[email protected] 2018-01-15_21-34-33] #mysqlbinlog-j 2714/mydata/log/master-log.000005 >/tmp/mybinlog.sql

Recovering data

First, prepare a feeling of MySQL server, did not do any initialization configuration, directory/var/lib/mysql is empty.

This assumes that the IP is a 73 server

Stop MySQL Service

[[email protected] MySQL] #systemctl stop mariadb

Installing PXB

Yum-y install/root/percona-xtrabackup-24-2.4.7-2.el7.x86_64.rpm

Before data recovery, the data is merged into a full backup, consolidation, the first several times to add the option--redo-only, after the final merge, based on all the backups to "rollback" uncommitted transactions, note that the consolidation order is critical, the increment to use absolute path, the operation is as follows

Go to the full-volume backup directory and do the following:

[[email protected] 2018-01-15_20-10-02] #innobackupex--apply-log--redo-only.

Next, or in the full amount of the directory, start merging the first increment, indicating the incremental directory, note that the increment to use absolute path

[[email protected] 2018-01-15_20-10-02] #innobackupex--apply-log--redo-only./--incremental-dir=/mydata/backups/ 2018-01-15_20-23-13

After the first incremental merge is complete, merge the second increment

[[email protected] 2018-01-15_20-10-02] #innobackupex--apply-log--redo-only./--incremental-dir=/mydata/backups/ 2018-01-15_21-34-33

After the second incremental merge is complete, rollback the unfinished transaction, removing the--redo-only option

[[email protected] 2018-01-15_20-10-02] #innobackupex--apply-log.

At this point the backup is a full backup, with all the increments

Check

Check the last_lsn of the xtrabackup_checkpoints file in the full volume directory is already the last increment value, the command is as follows

[[email protected] 2018-01-15_20-10-02] #cat xtrabackup_checkpoints

Copy the combined full amount of backup data on 71 to 73 2018-01-15_20-10-02, note that the next recovery of the combined full-scale backup data is no longer available, because it has been merged and rolled back, can only be used for this time data recovery, after this data recovery, To redo a full backup of the new MySQL server, restart a backup sequence for the next recovery

[[email protected] 2018-01-15_20-10-02] #scp-R/mydata/backups/2018-01-15_20-10-02 192.168.1.73:/root

When the data copy is complete, it is 73 on this MySQL server

Into the directory of copies of the full amount of backup 2018-01-15_20-10-02

[[Email protected] ~] #cd/root/2018-01-15_20-10-02

[[email protected] 2018-01-15_20-10-02] #innobackupex--copy-back.

When you are finished, you can see that the data has been copied to/var/lib/mysql/, changing all the groups under the/var/lib/mysql/directory and all the master

[[email protected] MySQL] #chown-R mysql.mysql/var/lib/mysql/*

Start the service

[[email protected] MySQL] #systemctl start mariadb.service

At this point, after the service starts normally, you also need to do a point-in-time data restore to replay the binary log files and restore the data

Now check the database and enter the database

MariaDB [sunny]> SELECT * from students where age=90;

Age=90 's major has not been updated to maths

and saved binary files/tmp/mybinlog.sql

Scp/tmp/mybinlog.sql 192.168.1.73:/root

Note that this will move the mybinlog.sql under/root to/TMP so that all users can read the files in the directory, and then directly use the source in the 73mysql service to read the Mybinlog.sql file in/tmp.

Temporarily turn off the native 73 MySQL binary logging feature before resuming the binary log

MariaDB [sunny]> SET @ @session. Sql_log_bin=off;

Three ways to start restoring binary files

MariaDB [sunny]> Source/tmp/mybinlog.sql

Or

MariaDB [sunny]> \. /tmp/mybinlog.sql

or directly into the shell.

[[Email protected] ~] #mysql-uroot-ppass123456 </tmp/mybinlog.sql

Once you are done, turn on logging for the binary log again

MariaDB [sunny]> SET @ @session. Sql_log_bin=on;

At this point, the backup is complete, but make a full backup first and then bring the server online

[[Email protected] ~] #mkdir-P/mydata/backup73

[[Email protected] ~] #innobackupex--user=root--host=localhost--password=pass123456/mydata/backup73

To this, the backup is complete, the MySQL server can go online new recovery data

Database Backup tool xtrabackup for MySQL backup