Shulou(Shulou.com)05/31 Report--

This article mainly explains "what is the backup and recovery method of innobackupex partial table". Interested friends may wish to have a look at it. The method introduced in this paper is simple, fast and practical. Next, let the editor take you to learn "what is the backup and recovery method of some innobackupex tables?"

One of the functions of Percona XtraBackup is partial backup (partial backups), which allows users to back up specified tables or databases. Note that the table you want to back up must be in a separate tablespace, that is, your MySQL has the innodb_file_per_table setting turned on before the table is created.

One more thing to note: don't copy back what is backed up by prepared backup. Some backups use the import table (importing the tables) instead of the full library backup-- copy-back parameter.

About innobackupex can realize (incremental) backup and recovery of some tables or libraries.

There are three ways to make partial backups, which are:

1. Use a regular expression to indicate the name of the library and table to be backed up (parameter is-- include)

two。 The name of the table or library to be backed up is written in a text file (parameter is-- tables-file)

3. The backup table name or library name is fully written on the command line or in the file (parameter is:-- databases)

Method 1: use the-- include parameter

In this way, the database name and table name are matched by regular expressions. You need to write the complete database name and table name. If the database has a user name and password, use-- user and-- password to specify the relevant information. The format is as follows: databasename.tablename. Here is an example:

[root@tool202 2018-12-01 20-39-31] # innobackupex-- include='liuwenhe.liuwenhe' / data/

The above method is the same as other backup methods using the innobackupex command to create a timestamped folder, except that only those regular expression matching tables (liuwenhe.liuwenhe) are included.

Note that this command will eventually be passed to xtrabackup-- tables command execution

Method 2: use the-- tables-file parameter

In this way, all the full table names to be backed up are written in a text file, one full table name per line, and then the program reads the text file for backup. The full table name is databasename.tablename. If you need to back up all the tables of a library, you can write it as databasename.*.. It is important to note that there can be no spaces at the beginning and end of each line in the file! Here is an example:

[root@rongduan-renhang32 ~] # cat name.txt

Liuwenhe.liu

Liuwenhe.he

Liuhe.*

You can remove the spaces at the end of each line using the following command if necessary

[root@rongduan-renhang32 ~] # sed-e's / [] * $/ / g 'name.txt > name1.txt

two。 Then perform a backup:

[root@rongduan-renhang32] # innobackupex-- user=root-- password='V56788@1qaz'-- port=3306-- tables-file=/root/name.txt / data/

The above method is the same as other backup methods using the innobackupex command to create a timestamped folder, except that only the table names specified in those files are included.

This command will eventually be passed to the xtrabackup-- tables-file command for execution instead of-- tables, which will only create those database folders that need to be backed up.

Method 3: use the-- databases parameter

In this way, users can not only write all the database names and complete table names to be backed up together, separated by spaces, but also write the database name and complete table name in a file, one per line. It should be noted that if you need to back up the entire mysql library, it is different from the-- tables-file parameter and simply write the library name. Here is an example:

[root@tool202 liuwenhe] # innobackupex-user=root-password='V56788@1qaz'-port=3306-databases= "liuwenhe.liuwenhe mysql" / data

Or

[root@rongduan-renhang32 ~] # cat name.txt

Liuwenhe.liuwenhe

Mysql # # need not be written as mysql.* to distinguish from the-- tables-file parameter!

You can remove the spaces at the end of each line using the following command if necessary

[root@rongduan-renhang32 ~] # sed-e's / [] * $/ / g 'name.txt > name1.txt

two。 Then perform a backup:

[root@rongduan-renhang32] # innobackupex-- user=root-- password='V56788@1qaz'-- port=3306-- databases=/root/name.txt / data/

The above method is the same as other backup methods using the innobackupex command, creating a timestamped folder, and the final result will include the liuwenhe table in the liuwenhe database, as well as the entire mysql database.

Prepare a partial backup (Preparing Partial Backups)

Execute preparing partial backups, using the-- apply-log and-- export parameters, and include the timestamp folder generated in the previous step, as follows:

[root@tool202 2018-12-01 / 21-10-20] # innobackupex-- port=3306-- apply-log-- export / data/liuwenhe/2018-12-01 / 21-11-07 /

When you execute the command, there is a prompt that the table does not exist for those tables that do not need to be backed up. Don't worry, it's normal.

Finally, remember to observe the output below to make sure that the preparation for the backup is over.

181201 21:15:50 completed OK!

Actions to restore:

1. First create the table that needs to be recovered. Note that the format of the storage row of the table is the same as that of the source side. You can view the table's

Root@localhost: liuwenhe 21:33:36 > show table status like 'liuwenhe'\ G

* * 1. Row *

Name: liuwenhe

Engine: InnoDB

Version: 10

Row_format: Compact

Rows: 4

Avg_row_length: 4096

Data_length: 16384

Max_data_length: 0

Index_length: 0

Data_free: 0

Auto_increment: NULL

Create_time: 2018-12-01 19:43:48

Update_time: NULL

Check_time: NULL

Collation: utf8_general_ci

Checksum: NULL

Create_options: row_format=COMPACT

Comment:

1 row in set (0.00 sec)

If it is different, you can modify it in the following ways:

Root@localhost: liuwenhe 21:37:36 > alter table liuwenhe.liuwenhe Row_format=Compact

Query OK, 0 rows affected (0.34 sec)

Records: 0 Duplicates: 0 Warnings: 0

Note: tables created by mysql5.6 default to Row_format=Compact, while tables created by mysql5.7.18 default to dynamic format

Controlled by parameter innodb_default_row_format!

3. Note that unlike the offline tablespace of oracle, after discard is executed in mysql, the .ibd file of the corresponding table is gone.

Alter table liuwenhe.liuwenhe discard tablespace

4. Then the .ibd file of the table in the directory backed up by copy is transferred to the appropriate directory:

5. Then load it into the tablespace:

Alter table liuwenhe.liuwenhe import tablespace

At this point, the resumption of work is complete!

Summary: we can use innobackupex to achieve partial table backup and incremental backup and recovery, innodb tables can not be directly restored by copy table files; you can also use mysqlfrm tools to read the table structure of a table in the innobackupex backup, which is used to create the above table structure first, then the .ibd file of the discad tablespace,copy table, and finally import to restore individual tables from the full instance backup! Note that the prerequisite for restoring individual tables is that the parameter innodb_file_per_table is enabled.

The mysqlfrm tool can read the table structure, which can be installed using yum: yum install mysql-utilities-y

At this point, I believe you have a deeper understanding of "innobackupex partial table backup and recovery methods". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

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.