Shulou(Shulou.com)06/01 Report--

This article mainly introduces the relevant knowledge of how to use xtrabackup, the content is detailed and easy to understand, the operation is simple and fast, and has a certain reference value. I believe you will gain something after reading this article on how to use xtrabackup. Let's take a look at it.

Xtrabackup is an online hot backup tool based on InnoDB developed by CTO Vadim of Percona company. It has the characteristics of open source, free, supporting online hot backup, fast backup recovery, small disk space, and supports a variety of backup forms in different situations.

Xtrabackup installation

This article is based on the CentOS6.6 system, so you can download the corresponding version of the rpm package and install it directly.

[root@localhost xtrabackup] # yum-y install perl perl-devel libaio libaio-devel perl-Time-HiRes perl-DBD-MySQL / / install dependency package

[root@localhost xtrabackup] # rpm-ivh percona-xtrabackup-2.2.4-5004.el6.x86_64.rpm warning: percona-xtrabackup-2.2.4-5004.el6.x86_64.rpm: Header V4 DSA/SHA1 Signature, key ID cd2efd2a: NOKEY Preparing... # 1:percona-xtrabackup # implementation of Xtrabackup backup

1. Full backup

# innobackupex-user=DBUSER-password=DBUSERPASS / path/to/BACKUP-DIR/

"if you want to use a user with minimum privileges for backup, you can create such a user based on the following command:"

Mysql > CREATE USER 'feiyu'@'localhost' IDENTIFIED BY' s 3cretbacks makes MySQL > REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'feiyu';mysql > GRANT RELOAD, LOCK TABLES, REPLICATION CLIENT ON *. * TO' feiyu'@'localhost';mysql > FLUSH PRIVILEGES

When using innobakupex backup, it invokes xtrabackup to back up all InnoDB tables, copies all related files (.frm) about the table structure definition, and related files for MyISAM, MERGE, CSV, and ARCHIVE tables, as well as files related to triggers and database configuration information. These files are saved to a directory with time commands.

While backing up, innobackupex also creates the following files in the backup directory:

❝

(1) xtrabackup_checkpoints-backup type (such as full or incremental), backup status (such as prepared status) and LSN (log serial number) scope information

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

(2) xtrabackup_binlog_info-the location of the binary log files currently in use by the mysql server and the binary log events up to the moment of backup.

(3) xtrabackup_binlog_pos_innodb-the current position of the binary log file and the binary log file used for the InnoDB or XtraDB table.

(4) xtrabackup_binary-the executable file of xtrabackup used in backup

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

When using innobackupex for backup, you can also use the-no-timestamp option to prevent the command from automatically creating a directory named by time; in this way, the innobackupex command will create a BACKUP-DIR directory to store the backup data.

2. Prepare a full backup

In general, after the backup is complete, the data cannot be used for restore operations because the backed up data may contain transactions that have not yet been committed or transactions that have been committed but have not been synchronized to the data file. Therefore, the data file is still in an inconsistent state at this time. The main function of "preparation" is to make the data file consistent by rolling back the uncommitted transaction and synchronizing the committed transaction to the data file.

The-apply-log option of the innobakupex command can be used to achieve the above functions. Such as the following command:

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

If executed correctly, the last few lines of information 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 implementing preparation, innobackupex can also use the-use-memory option to specify the amount of memory it can use, which is usually 100m by default. If enough memory is available, you can allocate more memory to the prepare process to improve its completion speed.

3. Restore data from a full backup

The-copy-back option of the innobackupex command is used to perform the restore operation, which performs the restore process by copying all data-related files to the mysql server DATADIR directory. Innobackupex uses backup-my.cnf to get information about the DATADIR directory.

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

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

Innobackupex: Starting to copy InnoDB log filesinnobackupex: in'/ backup/2012-04-07 July 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 above information.

When the data is restored to the DATADIR directory, you also need to make sure that the owner and group of all data files are the correct users, such as mysql, otherwise, you need to modify the owner and group of the data file before starting mysqld. Such as:

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

4. Use innobackupex for incremental backup

Each InnoDB page contains a LSN message, and whenever the relevant data changes, the LSN of the related page will automatically grow. This is the basis on which InnoDB tables can be backed up incrementally, that is, innobackupex is achieved by backing up pages that have changed since the last full backup.

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

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

(to perform an incremental backup with a full backup, basedir refers to the directory of the full backup, and to perform an incremental backup of the last incremental backup, specify the directory of the last incremental backup)

Where BASEDIR refers to the directory where the full backup is located, and after the execution of this command, the innobackupex command creates a new time-named directory in the / backup directory to hold all incremental backup data. In addition, when performing an incremental backup again after performing an incremental backup, its-incremental-basedir should point to the directory where the last incremental backup was located.

It is important to note that incremental backups can only be applied to InnoDB or XtraDB tables, and for MyISAM tables, incremental backups are actually performed as full backups.

There are some differences between prepare incremental backups and collating full backups, especially the following:

❝

(1) transactions that have been committed need to be "replayed" on each backup (including full and individual incremental backups). After replay, all backup data will be merged into the full backup. (2) "rollback" uncommitted transactions based on all backups. (the following is to merge an incremental backup to a full backup, and then only specify a full backup at the time of restore)

Therefore, the operation becomes:

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

Then execute:

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

Then comes 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, INCREMENTAL-DIR-1 refers to the directory of the first incremental backup, INCREMENTAL-DIR-2 refers to the directory of the second incremental backup, and so on, that is, if there are multiple incremental backups, do the same each time.

5. Xtrabackup "stream" and "backup compression" functions

Xtrabackup supports the "stream" function for backup data files, that is, the backup data can be transferred to tar programs for archiving through STDOUT, instead of being saved directly to a backup directory by default. To use this feature, simply use the-stream option. Such as:

# innobackupex-- stream=tar / backup | gzip > / backup/ `date +% Flavor% Hume% MMI% S`.tar.gz

You can even back up data to another server using commands like the following:

# innobackupex-- stream=tar / backup | ssh user@www.feiyu.com "cat-> / backups/ `date +% qualified% Hmure% Mmure% S`.tar"

"in addition, when performing a local backup, you can use the-parallel option to copy multiple files in parallel." This option is used to specify the number of threads to start on replication. Of course, to take advantage of the convenience of this feature when actually doing backups, you also need to enable the innodb_file_per_table option or the shared tablespaces are stored in multiple ibdata files through the innodb_data_file_path option. Replication of multiple files in a database cannot take advantage of this feature. Its simple usage is as follows:

# innobackupex-- parallel / path/to/backup

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

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

6. Import or export sheet

By default, InnoDB tables cannot be migrated between mysql servers by copying table files directly, even if the innodb_file_per_table option is used. This can be achieved using the Xtrabackup tool, but at this point the mysql server that needs to "export" the table has the innodb_file_per_table option enabled (strictly speaking, the mysql server enabled the innodb_file_per_table option before the table to be "exported" was created), and the server that "imports" the table has both the innodb_file_per_table and innodb_expand_import options enabled.

(1) the export table exports the table during the prepare phase of the backup, so once the full backup is complete, you can export a table with the-export option during the prepare process:

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

This command creates a file that ends in .exp for the tablespace of each innodb table, and these files ending in .exp can be used to import to other servers. (2) Import table to import an innodb table from another server on the mysql server, you need to create a table on the current server that is consistent with the structure of the original table, and then import the table:

Mysql > CREATE TABLE mytable (...) ENGINE=InnoDB

Then delete the tablespace for this table:

Mysql > ALTER TABLE mydatabase.mytable DISCARD TABLESPACE

Next, copy the mytable.ibd and mytable.exp files of the mytable table from the server of the Export table to the data directory of the current server, and then "import" them with the following command:

Mysql > ALTER TABLE mydatabase.mytable IMPORT TABLESPACE

7. Use Xtrabackup to partially back up the database

Xtrabackup can also implement partial backups, that is, only one or some specified databases or some or some tables in a database can be backed up. However, to use this feature, the innodb_file_per_table option must be enabled, that is, each table is saved as a separate file. At the same time, it does not support the-stream option, that is, it does not support piping data to other programs for processing.

In addition, restoring a partial backup is different from restoring a backup of all data, that is, you cannot simply copy some of the backups of prepared back to the data directory using the-copy-back option, but by importing the direction of 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 inconsistencies, so this method is not recommended in any case.

(1) create a partial backup

There are three ways to create a partial backup: regular expressions (- include), enumerated table files (- tables-file), and lists the databases to back up (- databases).

(a) when using-include using-include, you are required to specify the full name of the table to be backed up, that is, like databasename.tablename, such as:

# innobackupex-- include=' ^ feiyu [.] tb1' / path/to/backup

(B) the parameter using-tables-file this option needs to be a file name, and each line in this file contains the full name of the table to be backed up, such as:

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

(C) use-databases this option to accept parameters as data names, which need to be separated by spaces if you want to specify multiple databases; at the same time, when specifying a database, you can specify only one of the tables. In addition, this option can also accept a file as a parameter, with each behavior in the file having an object to back up. Such as:

# innobackupex-databases= "feiyu testdb" / path/to/backup

(2) preparing partial backup the process of prepare partial backup 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 invokes the xtrabackup command to remove the missing table from the data dictionary, so there are many warnings about the table does not exist class. At the same time, information about creating .exp files for tables that exist in the backup file is also displayed.

(3) the process of restoring a partial backup is the same as that of importing a table. Of course, you can also restore by directly copying the backup of the prepared state to the data directory, and do not require the data directory to be in a consistent state at this time.

The following is an actual demonstration of the complete backup process:

← # 14#root@localhost / tmp/full-backup → innobackupex-- user=root / tmp/full-backup/ # full backup InnoDB Backup Utility v1.5.1 house xtrabackup; Copyright 2003, 2009 Innobase Oyand Percona LLC and/or its affiliates 2009-2013. All Rights Reserved. . Xtrabackup: The latest check point (for incremental): '2987626'xtrabackup: Stopping log copying thread.. > > log scanned up to (2987626) xtrabackup: Creating suspend file' / tmp/full-backup/2015-06-25005-58-26 7858'xtrabackup xtrabackupposed logically copied 'with pid' 7858'xtrabackup: Transaction log of lsn (2987626) to (2987626) was copied.150625 05:58:30 innobackupex: All tables unlockedinnobackupex: Backup created in directory'/ tmp/full-backup/2015-06-25005-58-26'innobackupex : MySQL binlog position: filename 'mysql-bin.000001' Position 2383150625 05:58:30 innobackupex: Connection to database server closed150625 05:58:30 innobackupex: completed Oklahoma MySQL > insert into tutors (tname) values ('stu00011') # insert data Query OK into the database, 1 row affected (0.03 sec) mysql > insert into tutors (tname) values ('stu00012'); Query OK, 1 row affected (0.00 sec) ← # 246#root@localhost / tmp → innobackupex-- incremental / tmp/full-backup/-- incremental-basedir=/tmp/full-backup/2015-06-25 trades 05-58-26 / # do incremental backup InnoDB Backup Utility v1.5.1 house xtrabackup; Copyright 2003, 2009 Innobase Oyand Percona LLC and/or its affiliates 2009-2013. All Rights Reserved. . Xtrabackup: xtrabackup: Creating suspend file'/ tmp/full-backup/2015-06-250600-48pm xtrabackupposed logically copied 'with pid' 8663'xtrabackup: Transaction log of lsn (2988209) to (2988209) was copied.150625 06:00:53 innobackupex: All tables unlockedinnobackupex: Backup created in directory'/ tmp/full-backup/2015-06-25mm 06-00-48'innobackupex: MySQL binlog position: filename 'mysql-bin.000001' Position 2924150625 06:00:53 innobackupex: Connection to database server closed150625 06:00:53 innobackupex: completed Oklahoma MySQL > insert into tutors (tname) values ('stu00014') # insert data Query OK again, 1 row affected (0.02 sec) mysql > insert into tutors (tname) values ('stu00015'); Query OK, 1 row affected (0.00 sec) ← # 247#root@localhost / tmp → innobackupex-- incremental / tmp/full-backup/-- incremental-basedir=/tmp/full-backup/2015-06-25 colors 06-00-48 # do incremental backup InnoDB Backup Utility v1.5.1 Muvxtrabackup; Copyright 2003, 2009 Innobase Oyand Percona LLC and/or its affiliates 2009-2013. All Rights Reserved . Xtrabackup: xtrabackup: Creating suspend file'/ tmp/full-backup/2015-06-250602-41According to xtrabackupposed logically copied 'with pid' 9259'xtrabackup: Transaction log of lsn (2988781) to (2988781) was copied.150625 06:02:45 innobackupex: All tables unlockedinnobackupex: All tables unlockedinnobackupex'/ tmp/full-backup/2015-06-250602-41'innobackupex: MySQL binlog position: filename 'mysql-bin.000001' Position 3465150625 06:02:46 innobackupex: Connection to database server closed150625 06:02:46 innobackupex: completed OK! ← # 266#root@localhost / tmp/full-backup/2015-06-25005-58-26 → cat xtrabackup_checkpoints # check whether the log serial number is consistent backup_type = log-appliedfrom_lsn = 0to_lsn = 2987626last_lsn = 2987626compact = 0 ← # 267#root@localhost / tmp/full-backup/2015-06-25005-58-26 → cd.. / 2015-06-25-06-00-48 / ← # 268#root@localhost / tmp/full-backup/2015- 06-25-06-00-48 → cat xtrabackup_checkpointsbackup_type = incrementalfrom_lsn = 2987626to_lsn = 2988209last_lsn = 2988209compact = 0 ← # 269#root@localhost / tmp/full-backup/2015- 06-25-00-48 → cd.. / 2015-06-25 06-02-41 / ← # 270#root@localhost / tmp/full-backup/2015- 06-25 → 06-02-41 → cat xtrabackup_checkpointsbackup_type = incrementalfrom_lsn = 2988209to_lsn = 2988781last_lsn = 2988781compact = 0 ← # 248#root@localhost / tmp → innobackupex-- apply-log-- redo-only / tmp/full-backu2015-06-25 October 05-58-26 / # prepare InnoDB Backup Utility v1.5.1-xtrabackup Copyright 2003, 2009 Innobase Oyand Percona LLC and/or its affiliates 2009-2013. All Rights Reserved. [notice (again)] If you use binary log and don't use any hack of group commit, the binary log position seems to be:InnoDB: Last MySQL binlog file position 0 2241, file name. / mysql-bin.000001xtrabackup: starting shutdown with innodb_fast_shutdown = 1InnoDB: Starting shutdown...InnoDB: Shutdown completed Log sequence number 2987626150625 06:04:03 innobackupex: completed OK! ← # 249#root@localhost / tmp → innobackupex-- apply-log-- redo-only / tmp/full-backu2015-06-25-05-58-26 /-incremental-dir=/tmp/full-backup/2015-06-25-00-48 / # merge the first incremental backup files. Innobackupex: Copying'/ tmp/full-backup/2015-06-25 minutes 06-00-48 Management to'/ tmp/full-backup/2015-06-25 minutes 05-58-26/management/admin.frm'150625 06:05:28 innobackupex: completed OK! ← # 251#root@localhost / tmp → innobackupex-- apply-log-- redo-only / tmp/full-backu2015-06-25 minutes 05-58-26 /-- incremental-dir=/tmp/full-backup/2015 -06-25-06-02-41 / # merge the second incremental backup files. Innobackupex: Copying'/ tmp/full-backup/2015-06-25 minutes 06-02-41 Management innobackupex to'/ tmp/full-backup/2015-06-25 minutes 05-58-26/management/admin.frm'150625 06:07:10 innobackupex: Copying'/ tmp/full-backup/2015-06-25 minutes 06-02-41 Management Completed OK! ← # 258#root@localhost ~ → rm-rf / mydata/data1/* # Delete the data file directory ← # 259#root@localhost ~ → innobackupex-- copy-back / tmp/full-backup/2015-06-25upload 05-58-26 / # restore. Innobackupex: Starting to copy InnoDB log filesinnobackupex: in'/ tmp/full-backup/2015-06-25005-58-26'innobackupex: back to original InnoDB log directory'/ mydata/data1'innobackupex: Finished copying back files.150625 06:12:29 innobackupex: completed OK! ← # 276#root@localhost / mydata/data1 → chown-R mysql.mysql. / * # this is the end of the article on "how to use xtrabackup". Thank you for reading! I believe you all have a certain understanding of the knowledge of "how to use xtrabackup". If you want to learn more, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.