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).

(root@localhost mysql3306.sock)[sysbench]>show tables;
+--------------------+
| Tables_in_sysbench |
+--------------------+
| sbtest1            |
| sbtest10           |
| sbtest2            |
| sbtest3            |
| sbtest4            |
| sbtest5            |
| sbtest6            |
| sbtest7            |
| sbtest8            |
| sbtest9            |
+--------------------+
10 rows in set (0.00 sec)

(root@localhost mysql3306.sock)[sysbench]>show create table sbtest2\G
*************************** 1. row ***************************
       Table: sbtest2
Create Table: CREATE TABLE `sbtest2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `k` int(11) NOT NULL DEFAULT '0',
  `c` char(120) NOT NULL DEFAULT '',
  `pad` char(60) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `k_2` (`k`)
) ENGINE=InnoDB AUTO_INCREMENT=100001 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

(root@localhost mysql3306.sock)[sysbench]>select count(*) from sbtest2;
+----------+
| count(*) |
+----------+
|   100000 |
+----------+
1 row in set (0.07 sec)

(root@localhost mysql3306.sock)[sysbench]>show variables like '%innodb_file_per_table%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_file_per_table | ON    |
+-----------------------+-------+
1 row in set (0.00 sec)

 

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

(root@localhost mysql3306.sock)[tt]>CREATE TABLE `sbtest2` (
    ->   `id` int(11) NOT NULL AUTO_INCREMENT,
    ->   `k` int(11) NOT NULL DEFAULT '0',
    ->   `c` char(120) NOT NULL DEFAULT '',
    ->   `pad` char(60) NOT NULL DEFAULT '',
    ->   PRIMARY KEY (`id`),
    ->   KEY `k_2` (`k`)
    -> ) ENGINE=InnoDB AUTO_INCREMENT=100001 DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.02 sec)

(root@localhost mysql3306.sock)[tt]>select count(*) from sbtest2;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

(root@localhost mysql3306.sock)[tt]>show variables like '%innodb_file_per_table%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_file_per_table | ON    |
+-----------------------+-------+
1 row in set (0.00 sec)

 

Detach the tablespace of table "sbtest2"  on target.

(root@localhost mysql3306.sock)[zlm]>alter table sbtest2 discard tablespace;
Query OK, 0 rows affected (0.00 sec)

[root@zlm3 10:30:44 /data/mysql/mysql3306/data/tt]
#ls -l
total 16
-rw-r----- 1 mysql mysql   61 Jul  5 10:13 db.opt
-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.

(root@localhost mysql3306.sock)[sysbench]>flush table sbtest2 for export;
Query OK, 0 rows affected (0.00 sec)

[root@zlm2 10:36:01 /data/mysql/mysql3306/data/sysbench]
#ls -l|grep sbtest2
-rw-r----- 1 mysql mysql      559 Jul  5 10:35 sbtest2.cfg
-rw-r----- 1 mysql mysql     8632 Jul  4 09:26 sbtest2.frm
-rw-r----- 1 mysql mysql 30408704 Jul  4 09:26 sbtest2.ibd //A .cfg file has been created now.

2018-07-05T08:35:03.515902Z 9 [Note] InnoDB: Sync to disk of `sysbench`.`sbtest2` started.
2018-07-05T08:35:03.515929Z 9 [Note] InnoDB: Stopping purge
2018-07-05T08:35:03.516147Z 9 [Note] InnoDB: Writing table metadata to './sysbench/sbtest2.cfg'
2018-07-05T08:35:03.516276Z 9 [Note] InnoDB: Table `sysbench`.`sbtest2` flushed to disk

//error log shows the information after flush operation.
//table metadata has been written into the .cfg file.

 

Copy .ibd & .cfg file to target.

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

 

Release the lock resources on source instance.

(root@localhost mysql3306.sock)[sysbench]>unlock tables;
Query OK, 0 rows affected (0.00 sec)

[root@zlm2 10:37:57 /data/mysql/mysql3306/data/sysbench]
#ls -l|grep sbtest2
-rw-r----- 1 mysql mysql     8632 Jul  4 09:26 sbtest2.frm
-rw-r----- 1 mysql mysql 30408704 Jul  4 09:26 sbtest2.ibd

2018-07-05T08:38:09.256442Z 9 [Note] InnoDB: Deleting the meta-data file './sysbench/sbtest2.cfg'
2018-07-05T08:38:09.256458Z 9 [Note] InnoDB: Resuming purge

//The .cfg file will be deleted after execute "unlock tables;"

 

Check the files "sbtest2" table needs and give it the mysql privileges.

[root@zlm3 10:39:13 /data/mysql/mysql3306/data/tt]
#ls -l
total 29716
-rw-r----- 1 mysql mysql       61 Jul  5 10:13 db.opt
-rw-r----- 1 root  root       559 Jul  5 10:36 sbtest2.cfg
-rw-r----- 1 mysql mysql     8632 Jul  5 10:31 sbtest2.frm
-rw-r----- 1 root  root  30408704 Jul  5 10:36 sbtest2.ibd

//change the root.root to mysql.mysql

[root@zlm3 10:39:30 /data/mysql/mysql3306/data/tt]
#chown mysql.mysql sbtest2.*

[root@zlm3 10:39:41 /data/mysql/mysql3306/data/tt]
#ls -l
total 29716
-rw-r----- 1 mysql mysql       61 Jul  5 10:13 db.opt
-rw-r----- 1 mysql mysql      559 Jul  5 10:36 sbtest2.cfg
-rw-r----- 1 mysql mysql     8632 Jul  5 10:31 sbtest2.frm
-rw-r----- 1 mysql mysql 30408704 Jul  5 10:36 sbtest2.ibd

 

Import the tablespace of "sbtest2" table.

(root@localhost mysql3306.sock)[tt]>alter table sbtest2 import tablespace;
Query OK, 0 rows affected, 1 warning (2.68 sec)

(root@localhost mysql3306.sock)[tt]>show tables;
+--------------+
| Tables_in_tt |
+--------------+
| sbtest2      |
+--------------+
1 row in set (0.00 sec)

(root@localhost mysql3306.sock)[tt]>select count(*) from sbtest2;
+----------+
| count(*) |
+----------+
|   100000 |
+----------+
1 row in set (0.06 sec)

2018-07-05T08:40:03.820441Z 10 [Note] InnoDB: Importing tablespace for table 'sysbench/sbtest2' that was exported from host 'zlm2'
2018-07-05T08:40:03.820441Z 10 [Note] InnoDB: Phase I - Update all pages
2018-07-05T08:40:03.859485Z 10 [Note] InnoDB: Sync to disk
2018-07-05T08:40:04.936351Z 10 [Note] InnoDB: Sync to disk - done!
2018-07-05T08:40:04.962775Z 10 [Note] InnoDB: Phase III - Flush changes to disk
2018-07-05T08:40:04.975519Z 10 [Note] InnoDB: Phase IV - Flush complete
2018-07-05T08:40:04.975722Z 10 [Note] InnoDB: `tt`.`sbtest2` autoinc value set to 100001

//The error log shows details of this import operation.

 

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

(root@localhost mysql3306.sock)[tt]>alter table sbtest2 discard tablespace;
Query OK, 0 rows affected (0.01 sec)

(root@localhost mysql3306.sock)[tt]>alter table sbtest2 discard tablespace;
Query OK, 0 rows affected, 1 warning (0.00 sec)

(root@localhost mysql3306.sock)[tt]>show warnings;
+---------+------+-----------------------------------------------------+
| Level   | Code | Message                                             |
+---------+------+-----------------------------------------------------+
| Warning | 1812 | InnoDB: Tablespace is missing for table tt/sbtest2. |
+---------+------+-----------------------------------------------------+
1 row in set (0.00 sec)

2018-07-05T08:52:55.055225Z 11 [ERROR] InnoDB: Cannot delete tablespace 494 because it is not found in the tablespace memory cache.
2018-07-05T08:52:55.055226Z 11 [Warning] InnoDB: Cannot delete tablespace 494 in DISCARD TABLESPACE: Tablespace not found

//error log shows the ERROR & Warning because of the .ibd file has been deleted in first discard operation.

 

Copy those files to target again.

[root@zlm2 11:00:05 /data/mysql/mysql3306/data/sysbench]
#scp sbtest2.{ibd,cfg} zlm3:/data/mysql/mysql3306/data/tt/
root@zlm3's password: 
sbtest2.ibd                                                                                                        100%   29MB  29.0MB/s   00:01    
sbtest2.cfg: No such file or directory

//Because of "unlock tables" operation,the .cfg file has gone now.

 

Import the "sbtest2" tablespace again.

(root@localhost mysql3306.sock)[tt]>alter table sbtest2 import tablespace;
Query OK, 0 rows affected, 1 warning (2.34 sec)

(root@localhost mysql3306.sock)[tt]>show warnings;
+---------+------+--------------------------------------------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                                                                    |
+---------+------+--------------------------------------------------------------------------------------------------------------------------------------------+
| Warning | 1810 | InnoDB: IO Read error: (2, No such file or directory) Error opening './tt/sbtest2.cfg', will attempt to import without schema verification |
+---------+------+--------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

//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.