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

You can use the Xtrabackup tool to recover data from only a few tables.

Experimental scenario:

There is a table T3 on the instance of port 3306, but not on the instance of port 3308. The purpose of the experiment is to restore the T3 table of port 3306 to the instance of port 3308.

Steps:

1. Make sure that the following parameters are set in the configuration file my.cnf of the instance on port 3306:

InnoDB_FAST_SHUTDOWN = 0-this parameter MySQL needs to complete all full purge and merge insert buffer operations when closed.

InnoDB_File_Per_Table = ON-this parameter changes InnoDB to a separate tablespace schema, and each table in each database generates a data space.

2. Backup

A) innobackupex-defaults-file=/usr/local/mysql/my.cnf-user=root-password=root-socket=/usr/local/mysql/mysql.sock-include='lxm.t3'-export / tmp/backup/

After the execution of the command, view the backup file

[root@single1 lxm] # pwd

/ tmp/backup/2016-08-309 16-34-50/lxm

[root@single1 lxm] # ls-l

Total 108

-rw-r-. 1 root root 10684 Aug 30 16:34 t3.frm

-rw-r-. 1 root root 98304 Aug 30 16:34 t3.ibd

B) apply logs to keep the backup consistent after the backup is completed

Innobackupex-- defaults-file=/usr/local/mysql/my.cnf-- user=root-- password=root-- socket=/usr/local/mysql/mysql.sock-- apply-log-- export / tmp/backup/2016-08-30th 16-34-50 /

After the execution of the command, view the backup file

[root@single1 lxm] # pwd

/ tmp/backup/2016-08-309 16-34-50/lxm

[root@single1 lxm] # ls-l

Total 128

-rw-r--r--. 1 root root 3378 Aug 30 16:36 t3.cfg

-rw-r-. 1 root root 16384 Aug 30 16:36 t3.exp

-rw-r-. 1 root root 10684 Aug 30 16:34 t3.frm

-rw-r-. 1 root root 98304 Aug 30 16:34 t3.ibd

You can see that there are two more files: t3.cfg and t3.exp. Exp files are available for percona server,cfg, for mariadb and mysql. Mariadb 10.0 can be import directly through ibd and frm files. After mysql 5.6, import,cfg can be used to validate the table structure without using cfg.

3. Create a table on the target instance that is identical to the T3 table in the source instance

The source instance views the table structure creation statement of the T3 table:

Mysql-S / usr/local/mysql/mysql.sock-P 3306-uroot-p

Mysql > show create table T3\ G

* * 1. Row *

Table: t3

Create Table: CREATE TABLE `t3` (

`Host`char (60) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT''

`User`char (16) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT''

`Password` char (41) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT''

. Omit.

) ENGINE=InnoDB DEFAULT CHARSET=utf8

1 row in set (0.14 sec)

Create table T3 for the target instance:

Mysql-S / usr/local/mysql3308/mysql.sock-P 3308-uroot-p

Mysql > use lxm

Database changed

Mysql > show tables

Empty set (0.00 sec)

Mysql > CREATE TABLE `t3` (

-> `Host`char (60) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT''

-> `User`char (16) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT''

-> `Password` char (41) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT''

->. Omit.

->) ENGINE=InnoDB DEFAULT CHARSET=utf8

Query OK, 0 rows affected (0.05 sec)

4. Target instance discard tablespace

Before the discard tablespace, the T3 table data file for mysql3308 is as follows:

[root@single1 lxm] # ls-l

Total 112

-rw-rw----. 1 mysql mysql 61 Aug 30 17:09 db.opt

-rw-rw----. 1 mysql mysql 10684 Aug 30 17:09 t3.frm

-rw-rw----. 1 mysql mysql 98304 Aug 30 17:09 t3.ibd

Discard tablespaces:

Mysql-S / usr/local/mysql3308/mysql.sock-P 3308-uroot-p

Mysql > ALTER TABLE T3 DISCARD TABLESPACE

Query OK, 0 rows affected (0.01 sec)

Mysql > show tables

+-+

| | Tables_in_lxm |

+-+

| | T3 |

+-+

1 row in set (0.00 sec)

After the discard tablespace, the T3 table data file for mysql3308 is as follows:

[root@single1 lxm] # ls-l

Total 16

-rw-rw----. 1 mysql mysql 61 Aug 30 17:09 db.opt

-rw-rw----. 1 mysql mysql 10684 Aug 30 17:09 t3.frm

5. Copy the t3.idb and t3.cfg files in the backup file to the data file path of the destination instance:

Cp-r / tmp/backup/2016-08-30mm 16-34-50/lxm/t3.cfg / usr/local/mysql3308/data/lxm/

Cp-r / tmp/backup/2016-08-30mm 16-34-50/lxm/t3.ibd / usr/local/mysql3308/data/lxm/

Change the subordinate group of t3.idb and t3.cfg files to mysql:

Chown mysql:mysql t3.cfg

Chown mysql:mysql t3.ibd

6. Import table

Mysql-S / usr/local/mysql3308/mysql.sock-P 3308-uroot-p

Mysql > ALTER TABLE T3 IMPORT TABLESPACE

Query OK, 0 rows affected (0.01 sec)

Mysql > show tables

+-+

| | Tables_in_lxm |

+-+

| | T3 |

+-+

1 row in set (0.00 sec)

Mysql > select count (1) from T3

+-+

| | count (1) | |

+-+

| | 14 |

+-+

1 row in set (0.00 sec)

Note: there are three ways to specify which tables to back up.

1.-- include: format regular expressions and back up those that match. This option is passed to xtrabackup-- tables, matching each table in each library one by one.

2.-- table-file: specify the table to back up in the file, and then pass in the file through this option. This option is passed to xtrabackup-- tables-file, and unlike the-- table option, only the library of the table to be backed up is created.

3.-- database: specify the list of databases. :-- the databasees option only affects non-innodb engine tables and frm files, and innodb data files are always backed up.

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.