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

Project scenario:

There are several large tables in an instance that develop history tables archived by programs, but occasionally need to provide queries for business. Since instances are backed up in full on a regular basis, it is not recommended to migrate these archived tables to a separate static instance to provide offline queries.

Then the task comes, that is, to back up a large table in a library that is more than 50G and restore it to another instance.

Solution:

1. The first thing that comes to mind must be mysqldump.

Advantages: you can back up the specified table. After the backup, you can directly execute the source command to import data on the designated restored instance, which is easy to operate.

Disadvantages: because the table of this operation is very large, and mysqldump is a single-threaded operation, the backup and recovery time is particularly long. For efficiency-based operations, this approach is not recommended.

two。 Use the hot standby tool innobackupex.

Advantages: the specified table can be backed up and restored, with fast operation time and high efficiency.

Pros: backup and restore operation commands are complex.

All work is about efficiency, so the specific operation method of option 2 is explained below.

The command to back up the specified table first is:

/ usr/local/percona-xtrabackup-2.4.5-Linux-x8664/bin/innobackupex-defaults-file=/data/mysql/3306/conf/my.cnf-host=127.0.0.1-port=3306-tmpdir=/tmp/-user=root-password='root@123'-databases='test.t1 test.t2'-slave-info-no-timestamp / home/backup/

After the backup is completed, it is found that the specified table is backed up under the backup path.

Then restore, specify the backup of the table, the restore operation needs to add the specified command-- export

As follows:

/ usr/local/percona-xtrabackup-2.4.5-Linux-x86_64/bin/innobackupex-apply-log-export / home/backup/

New .cfg and .exp files were found.

Restore the backed up table on the new instance.

a. View the table creation statement from the backup instance and create the same table on the new instance.

Mysql > show create table T1\ G

1. Row

Table: t1

Create Table: CREATE TABLE T1 (

Id int (11) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8

1 row in set (0.00 sec)

Mysql > show create table T2\ G

1. Row

Table: t2

Create Table: CREATE TABLE T2 (

Id int (11) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8

1 row in set (0.00 sec)

b. Discard tablespace the newly created table

Mysql > ALTER TABLE test.t1 DISCARD TABLESPACE

Query OK, 0 rows affected (0.05 sec)

Mysql > ALTER TABLE test.t2 DISCARD TABLESPACE

Query OK, 0 rows affected (0.04 sec)

It is found that the structure of the table changes as follows

c. Copy the restored .ibd, .exp files to the new instance after backup, and modify the permissions of the master group.

d. Import the table of discard on the new instance.

Mysql > ALTER TABLE test.t1 DISCARD TABLESPACE

Query OK, 0 rows affected (0.05 sec)

Mysql > ALTER TABLE test.t2 DISCARD TABLESPACE

Query OK, 0 rows affected (0.04 sec)

e. Before and after comparison, check whether the operation is successful.

Instance of backup

New instance

OK, and the whole process is complete.

In conclusion, the hot backup and recovery method, although the operation is complex, but compared with the mysqldump operation time is short and efficient, the whole process is time-consuming in copy file and alter table operation. There are other tools to improve backup and recovery efficiency, such as mysqldumper and mysqlpump (version 5.7), mysqlpump still operates on a single table in a single thread, and the efficiency comparison between mysqldumper and innobackupex will be verified later.

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.