【澳门金沙vip】MySQL另类的备份恢复方法——innodb可传输表空间

 

Preface

 

   
There’re many ways in backing up or migrating data from one server to
another one.Logically,we can use mysqldump,mydumper,mypump to do that
kind of job.Physically,we can use Xtrabackup even cold copy way.What I’m
gonna introduce is a special method to transmit data between MySQL
servers which called “transportable tablespace”.

 

Introduction

 

   
What’s transportable tablespace?It is supported only on innodb engine
and based on export/import grammer of “alter table … ;” clause 
since MySQL 5.6.6 version.As we all know,innodb
supports putting data of tables in their own tablespaces instead of
shared system tablespace by setting parameter
“innodb_file_per_table=1”.
It’s different from the conception of
oracle database.Business tables in oracle can be stored togerther with
each other in the same tablespace while MySQL oblying the rule of “one
table one ibd”.That is,these “.ibd” files is what we really need to
transport.**

 

Scenarios

  • Transport
    a single table to report server without influencing loads on
    product.
  • Transport
    a single table to slave server for correcting the replication errors
    about the table.
  • Transport
    a single table to better storages such as ssd device for special
    purpose.
  • Restore a
    big table efficiently and swiftly as mysqldump needs to reinsert
    data and rebuild indexes.

 

Limitations

  • “innodb_file_per_table”
    should be set to “on”(the same to slave server if in replication
    structure).
  • Page size
    on instance of target server should be same as the one on source
    server.
  • It
    doesn’t support partition table and tables which contains fulltext
    indexes.
  • “foreign_key_checks”
    should be set to “0” if there’s a paraent-child relationship in a
    table.
  • It
    doesn’t check the foreign key constraints when importing,so all
    relevant tables should be exported at the same time.
  • Target
    instance must has the same version of series with the source
    instance.
  • it’s
    recommended to set “lower_case_table” to “1” to avoid import
    problems.

Example

 

Check
the table which you want to transport first(eg. ”sbtest2” in database
“sysbench” here).

 1 (root@localhost mysql3306.sock)[sysbench]>show tables;
 2 +--------------------+
 3 | Tables_in_sysbench |
 4 +--------------------+
 5 | sbtest1            |
 6 | sbtest10           |
 7 | sbtest2            |
 8 | sbtest3            |
 9 | sbtest4            |
10 | sbtest5            |
11 | sbtest6            |
12 | sbtest7            |
13 | sbtest8            |
14 | sbtest9            |
15 +--------------------+
16 10 rows in set (0.00 sec)
17 
18 (root@localhost mysql3306.sock)[sysbench]>show create table sbtest2\G
19 *************************** 1. row ***************************
20        Table: sbtest2
21 Create Table: CREATE TABLE `sbtest2` (
22   `id` int(11) NOT NULL AUTO_INCREMENT,
23   `k` int(11) NOT NULL DEFAULT '0',
24   `c` char(120) NOT NULL DEFAULT '',
25   `pad` char(60) NOT NULL DEFAULT '',
26   PRIMARY KEY (`id`),
27   KEY `k_2` (`k`)
28 ) ENGINE=InnoDB AUTO_INCREMENT=100001 DEFAULT CHARSET=utf8
29 1 row in set (0.00 sec)
30 
31 (root@localhost mysql3306.sock)[sysbench]>select count(*) from sbtest2;
32 +----------+
33 | count(*) |
34 +----------+
35 |   100000 |
36 +----------+
37 1 row in set (0.07 sec)
38 
39 (root@localhost mysql3306.sock)[sysbench]>show variables like '%innodb_file_per_table%';
40 +-----------------------+-------+
41 | Variable_name         | Value |
42 +-----------------------+-------+
43 | innodb_file_per_table | ON    |
44 +-----------------------+-------+
45 1 row in set (0.00 sec)

 

**Create
the structure of table sbtest2 in database “tt” of target
instance.**

 1 (root@localhost mysql3306.sock)[tt]>CREATE TABLE `sbtest2` (
 2     ->   `id` int(11) NOT NULL AUTO_INCREMENT,
 3     ->   `k` int(11) NOT NULL DEFAULT '0',
 4     ->   `c` char(120) NOT NULL DEFAULT '',
 5     ->   `pad` char(60) NOT NULL DEFAULT '',
 6     ->   PRIMARY KEY (`id`),
 7     ->   KEY `k_2` (`k`)
 8     -> ) ENGINE=InnoDB AUTO_INCREMENT=100001 DEFAULT CHARSET=utf8;
 9 Query OK, 0 rows affected (0.02 sec)
10 
11 (root@localhost mysql3306.sock)[tt]>select count(*) from sbtest2;
12 +----------+
13 | count(*) |
14 +----------+
15 |        0 |
16 +----------+
17 1 row in set (0.00 sec)
18 
19 (root@localhost mysql3306.sock)[tt]>show variables like '%innodb_file_per_table%';
20 +-----------------------+-------+
21 | Variable_name         | Value |
22 +-----------------------+-------+
23 | innodb_file_per_table | ON    |
24 +-----------------------+-------+
25 1 row in set (0.00 sec)

 

**Detach
the tablespace of table
sbtest2″  on target.**

1 (root@localhost mysql3306.sock)[zlm]>alter table sbtest2 discard tablespace;
2 Query OK, 0 rows affected (0.00 sec)
3 
4 [root@zlm3 10:30:44 /data/mysql/mysql3306/data/tt]
5 #ls -l
6 total 16
7 -rw-r----- 1 mysql mysql   61 Jul  5 10:13 db.opt
8 -rw-r----- 1 mysql mysql 8632 Jul  5 10:31 sbtest2.frm //The sbtest2.ibd file has been deleted.

 

**Flush
the “sbtest2” table on source.**

 1 (root@localhost mysql3306.sock)[sysbench]>flush table sbtest2 for export;
 2 Query OK, 0 rows affected (0.00 sec)
 3 
 4 [root@zlm2 10:36:01 /data/mysql/mysql3306/data/sysbench]
 5 #ls -l|grep sbtest2
 6 -rw-r----- 1 mysql mysql      559 Jul  5 10:35 sbtest2.cfg
 7 -rw-r----- 1 mysql mysql     8632 Jul  4 09:26 sbtest2.frm
 8 -rw-r----- 1 mysql mysql 30408704 Jul  4 09:26 sbtest2.ibd //A .cfg file has been created now.
 9 
10 2018-07-05T08:35:03.515902Z 9 [Note] InnoDB: Sync to disk of `sysbench`.`sbtest2` started.
11 2018-07-05T08:35:03.515929Z 9 [Note] InnoDB: Stopping purge
12 2018-07-05T08:35:03.516147Z 9 [Note] InnoDB: Writing table metadata to './sysbench/sbtest2.cfg'
13 2018-07-05T08:35:03.516276Z 9 [Note] InnoDB: Table `sysbench`.`sbtest2` flushed to disk
14 
15 //error log shows the information after flush operation.
16 //table metadata has been written into the .cfg file.

 

**Copy
.ibd & .cfg file to target.**

1 [root@zlm2 10:36:03 /data/mysql/mysql3306/data/sysbench]
2 #scp sbtest2.{ibd,cfg} zlm3:/data/mysql/mysql3306/data/tt/
3 root@zlm3's password: 
4 sbtest2.ibd                                                                                                        100%   29MB  29.0MB/s   00:00    
5 sbtest2.cfg                                                                                                        100%  559     0.6KB/s   00:00

 

**Release
the lock resources on source instance.**

 1 (root@localhost mysql3306.sock)[sysbench]>unlock tables;
 2 Query OK, 0 rows affected (0.00 sec)
 3 
 4 [root@zlm2 10:37:57 /data/mysql/mysql3306/data/sysbench]
 5 #ls -l|grep sbtest2
 6 -rw-r----- 1 mysql mysql     8632 Jul  4 09:26 sbtest2.frm
 7 -rw-r----- 1 mysql mysql 30408704 Jul  4 09:26 sbtest2.ibd
 8 
 9 2018-07-05T08:38:09.256442Z 9 [Note] InnoDB: Deleting the meta-data file './sysbench/sbtest2.cfg'
10 2018-07-05T08:38:09.256458Z 9 [Note] InnoDB: Resuming purge
11 
12 //The .cfg file will be deleted after execute "unlock tables;"

 

**Check
the files “sbtest2” table needs and give it the mysql
privileges.**

 1 [root@zlm3 10:39:13 /data/mysql/mysql3306/data/tt]
 2 #ls -l
 3 total 29716
 4 -rw-r----- 1 mysql mysql       61 Jul  5 10:13 db.opt
 5 -rw-r----- 1 root  root       559 Jul  5 10:36 sbtest2.cfg
 6 -rw-r----- 1 mysql mysql     8632 Jul  5 10:31 sbtest2.frm
 7 -rw-r----- 1 root  root  30408704 Jul  5 10:36 sbtest2.ibd
 8 
 9 //change the root.root to mysql.mysql
10 
11 [root@zlm3 10:39:30 /data/mysql/mysql3306/data/tt]
12 #chown mysql.mysql sbtest2.*
13 
14 [root@zlm3 10:39:41 /data/mysql/mysql3306/data/tt]
15 #ls -l
16 total 29716
17 -rw-r----- 1 mysql mysql       61 Jul  5 10:13 db.opt
18 -rw-r----- 1 mysql mysql      559 Jul  5 10:36 sbtest2.cfg
19 -rw-r----- 1 mysql mysql     8632 Jul  5 10:31 sbtest2.frm
20 -rw-r----- 1 mysql mysql 30408704 Jul  5 10:36 sbtest2.ibd

 

**Import
the tablespace of “sbtest2” table.**

 1 (root@localhost mysql3306.sock)[tt]>alter table sbtest2 import tablespace;
 2 Query OK, 0 rows affected, 1 warning (2.68 sec)
 3 
 4 (root@localhost mysql3306.sock)[tt]>show tables;
 5 +--------------+
 6 | Tables_in_tt |
 7 +--------------+
 8 | sbtest2      |
 9 +--------------+
10 1 row in set (0.00 sec)
11 
12 (root@localhost mysql3306.sock)[tt]>select count(*) from sbtest2;
13 +----------+
14 | count(*) |
15 +----------+
16 |   100000 |
17 +----------+
18 1 row in set (0.06 sec)
19 
20 2018-07-05T08:40:03.820441Z 10 [Note] InnoDB: Importing tablespace for table 'sysbench/sbtest2' that was exported from host 'zlm2'
21 2018-07-05T08:40:03.820441Z 10 [Note] InnoDB: Phase I - Update all pages
22 2018-07-05T08:40:03.859485Z 10 [Note] InnoDB: Sync to disk
23 2018-07-05T08:40:04.936351Z 10 [Note] InnoDB: Sync to disk - done!
24 2018-07-05T08:40:04.962775Z 10 [Note] InnoDB: Phase III - Flush changes to disk
25 2018-07-05T08:40:04.975519Z 10 [Note] InnoDB: Phase IV - Flush complete
26 2018-07-05T08:40:04.975722Z 10 [Note] InnoDB: `tt`.`sbtest2` autoinc value set to 100001
27 
28 //The error log shows details of this import operation.

 

**If
you detach an inexistent tablespace,it will show below
errors.**

 1 (root@localhost mysql3306.sock)[tt]>alter table sbtest2 discard tablespace;
 2 Query OK, 0 rows affected (0.01 sec)
 3 
 4 (root@localhost mysql3306.sock)[tt]>alter table sbtest2 discard tablespace;
 5 Query OK, 0 rows affected, 1 warning (0.00 sec)
 6 
 7 (root@localhost mysql3306.sock)[tt]>show warnings;
 8 +---------+------+-----------------------------------------------------+
 9 | Level   | Code | Message                                             |
10 +---------+------+-----------------------------------------------------+
11 | Warning | 1812 | InnoDB: Tablespace is missing for table tt/sbtest2. |
12 +---------+------+-----------------------------------------------------+
13 1 row in set (0.00 sec)
14 
15 2018-07-05T08:52:55.055225Z 11 [ERROR] InnoDB: Cannot delete tablespace 494 because it is not found in the tablespace memory cache.
16 2018-07-05T08:52:55.055226Z 11 [Warning] InnoDB: Cannot delete tablespace 494 in DISCARD TABLESPACE: Tablespace not found
17 
18 //error log shows the ERROR & Warning because of the .ibd file has been deleted in first discard operation.

 

**Copy
those files to target again.**

1 [root@zlm2 11:00:05 /data/mysql/mysql3306/data/sysbench]
2 #scp sbtest2.{ibd,cfg} zlm3:/data/mysql/mysql3306/data/tt/
3 root@zlm3's password: 
4 sbtest2.ibd                                                                                                        100%   29MB  29.0MB/s   00:01    
5 sbtest2.cfg: No such file or directory
6 
7 //Because of "unlock tables" operation,the .cfg file has gone now.

 

**Import
the “sbtest2” tablespace again.**

 1 (root@localhost mysql3306.sock)[tt]>alter table sbtest2 import tablespace;
 2 Query OK, 0 rows affected, 1 warning (2.34 sec)
 3 
 4 (root@localhost mysql3306.sock)[tt]>show warnings;
 5 +---------+------+--------------------------------------------------------------------------------------------------------------------------------------------+
 6 | Level   | Code | Message                                                                                                                                    |
 7 +---------+------+--------------------------------------------------------------------------------------------------------------------------------------------+
 8 | Warning | 1810 | InnoDB: IO Read error: (2, No such file or directory) Error opening './tt/sbtest2.cfg', will attempt to import without schema verification |
 9 +---------+------+--------------------------------------------------------------------------------------------------------------------------------------------+
10 1 row in set (0.00 sec)
11 
12 //There's a warning about importing without .cfg file which won't impact the result.

  

Summary

  • ***Transportable Tablespace(TT) of innodb
    provids a different way in backing up and restoring a single table
    between servers.


  • ***TT merely supports innodb engine which
    can store data in tablespaces of their own by setting
    “innodb_file_per_table=1”.


  • TT supports importing tablespace
    without .cfg file what brings about us much convenience in crash
    recovery
    .***


  • Notice that there will be shared read
    locks on the tables after execute “flush table … for export;” what
    really influences the tables need to be write.