InnoDB可传输表空间-阿里云开发者社区

InnoDB可传输表空间

2016-08-19 5781

版权

本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。

本文涉及的产品

云数据库 RDS MySQL Serverless，0.5-2RCU 50GB

简介：

Copying File-Per-Table Tablespaces to Another Server
Transportable Tablespace Examples
Transportable Tablespace Internals

背景

大家先来回顾一下，如果要将innoDB中的一个表 Moving or Copying 到另外一台(machine|instance)上，如何做？

MySQL Enterprise Backup (no need consider)

Copying Data Files (Cold Backup Method)

Export and Import (mysqldump,mydumper,mysqlpump)

现在我们有第四种方案

Transportable Tablespaces

它有什么优势

To run reports without putting extra load on a production server

To set up identical data for a table on a new slave server

To restore a backed-up version of a table or partition after a problem or mistake

As a faster way of moving data around than importing the results of a mysqldump command. The data is available immediately, rather than having to be re-inserted and the indexes rebuilt

To move a file-per-table tablespace to a server with storage medium that better suits system requirements. For example, you may want to have busy tables on an SSD device, or large tables on a high-capacity HDD device

新备份（类似Myisam的备份）

    1. Myisam 只有flush table了，就可以随意copy
    2. 既然如此，为什么InnoDB不行呢？
        2.1 原因一： InnoDB的所有信息都有数据字典维护，数据字典在共享表空间中
        2.2 原因二： 你没有办法控制innoDB文件sync到disk
    3. 所以，现在是否可以利用5.6的新语法，flush table .. for export 功能来sync innoDB to disk呢

哪些需要注意

必须开启 innodb_file_per_table

当这个表处于quiesced状态,甚至不能被select

两边实例的page size 一致

5.7 版本之前，不支持分区表transport

外键相关的表，必须设置 foreign_key_checks=0 才能成功

ALTER TABLE ... IMPORT TABLESPACE 不需要.cfg metadata file . 但是，这样的话，MySQL就不会对schema进行verificate

5.6以及更高版本，import&export 版本必须在同一个series

在replication环境中，master & slave 都必须开启 innodb_file_per_table

对于InnoDB general tablespace，不支持discard & import tablespace

如果两边服务器的table row_format设置的不一样，会导致schema mismatch error

加密过的InnoDB tablespace 必须要拷贝.cfp 文件

Transportable Tablespace Examples

Ex1: Copying an InnoDB Partitioned Table From One Server To Another

Ex2: Copying an InnoDB Table From One Server To Another

这个例子演示了一个regular InnoDB table的传输过程

在source server A上，创建一个表

    mysql> use test;
    mysql> CREATE TABLE t(c1 INT) engine=InnoDB;

在destination server B上，也创建同样的表结构

    mysql> use test;
    mysql> CREATE TABLE t(c1 INT) engine=InnoDB;

在destination server B上，discard 该表（这一步是必须的）
```
    mysql> ALTER TABLE t DISCARD TABLESPACE;
```
在source server A上，执行 FLUSH TABLES ... FOR EXPORT , 该表这时候处于quiesce状态，只读，且创建.cfg metadata文件
```
    mysql> use test;
    mysql> FLUSH TABLES t FOR EXPORT;

此时，.cfg 文件在InnoDB的data directory中
```

拷贝.ibd & .cfg 从source server A 到在destination server B

    shell> scp /path/to/datadir/test/t.{ibd,cfg} destination-server:/path/to/datadir/test

在source server A，执行unlock tables 来释放FLUSH TABLES ... FOR EXPORT 加的locks
```
    mysql> use test;
    mysql> UNLOCK TABLES;
```

在destination server B上，导入tablespace

    mysql> use test;
    mysql> ALTER TABLE t IMPORT TABLESPACE;

Transportable Tablespace Internals

ALTER TABLE ... DISCARD TABLESPACE Internals

    1.1 The table is locked in X mode.
    1.2 The tablespace is detached from the table.

FLUSH TABLES ... FOR EXPORT Internals

    2.1 The table being flushed for export is locked in shared mode.
    2.2 The purge coordinator thread is stopped.
    2.3 Dirty pages are synchronized to disk.
    2.4 Table metadata is written to the binary .cfg file.

UNLOCK TABLES

    3.1 The binary .cfg file is deleted.
    3.2 The shared lock on the table or tables being imported is released and the purge coordinator thread is restarted.

ALTER TABLE ... IMPORT TABLESPACE

    4.1 Each tablespace page is checked for corruption.
    4.2 The space ID and log sequence numbers (LSNs) on each page are updated
    4.3 Flags are validated and LSN updated for the header page.
    4.4 Btree pages are updated.
    4.5 The page state is set to dirty so that it will be written to disk.

Question & Tips

flush tables .. for export需要注意什么
1. flush tables .. for export 会加锁，这时候，千万不能退出终端或session，否则加锁无效且.cfg文件自动删除。

如果没有.cfg文件，还能够import成功吗

可以，但是这样就没办法认证schema了

Level	Code	Message
Warning	1810	InnoDB: IO Read error: (2, No such file or directory) Error opening './test/t.cfg', will attempt to import without schema verification

1 row in set (0.00 sec)

如果discard了，还能select吗
1. 很不幸，是不可以被select的
  ERROR 1814 (HY000): Tablespace has been discarded for table 't'
discard 是什么意思，我就不能对其做任何操作了吗
1. discard的意思就是从数据库detached,会删除ibd文件，保留frm文件。
2. 也就意味着，你可以对frm文件操作，比如：rename table,drop table ，但是不能对ibd文件操作，比如：dml
这样传输表的速度快吗？对io负载大吗？
1. 这几步中，最慢的是import，其他几乎是瞬间完成。比较import做的事情也很多吗，anyway，都比mysqldump要快很多很多倍。
2. 至于io负载，当然是有的，但是还是要优于mysqlimport很多很多哇。
如果两边表结构不一致，可以导入过来吗？
1. 很遗憾，会报错
```
ERROR 1808 (HY000): Schema mismatch (Number of columns don't match, table has 5 columns but the tablespace meta-data file has 4 columns)
```
2. 所以，这里也有一个缺陷就是，如果你有ibd文件，还不一定能够恢复，你必须还要知道该表的表结构才行
特别注意: 必须使用.cfg来帮助认证schema，否则很可能导致MySQL卡死

原始表结构如下：

root:test> show create table t_2;

Table Create Table

t_2 CREATE TABLE t_2 (

id int(11) DEFAULT NULL

1 row in set (0.00 sec)

root:test> show create table t_2;
Table	Create Table
t_2	CREATE TABLE `t_2` (

    再没有原始表结构的前提下，自己随便创建了一个表结构
    root:test> show create table t_2;
    +-------+--------------------------------------------------------------------------------------------------------------------------------+
    | Table | Create Table                                                                                                                   |
    +-------+--------------------------------------------------------------------------------------------------------------------------------+
    | t_2   | CREATE TABLE `t_2` (
      `idsssss` int(11) DEFAULT NULL,
      `iddddddd` char(10) DEFAULT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
    +-------+--------------------------------------------------------------------------------------------------------------------------------+
    1 row in set (0.00 sec)

    root:test> ALTER TABLE t_2 IMPORT TABLESPACE;
    Query OK, 0 rows affected, 1 warning (0.00 sec)

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

    root:test> show create table t_2;
    +-------+--------------------------------------------------------------------------------------------------------------------------------+
    | Table | Create Table                                                                                                                   |
    +-------+--------------------------------------------------------------------------------------------------------------------------------+
    | t_2   | CREATE TABLE `t_2` (
      `idsssss` int(11) DEFAULT NULL,
      `iddddddd` char(10) DEFAULT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
    +-------+--------------------------------------------------------------------------------------------------------------------------------+
    1 row in set (0.00 sec)

    root:test>
    root:test> select * from t_2;

    ^CCtrl-C -- sending "KILL QUERY 32" to server ...
    Ctrl-C -- query aborted.
    ^CCtrl-C -- sending "KILL 32" to server ...

    root:(none)> show processlist;
    +----+------+-----------+------+---------+------+--------------+-------------------+
    | Id | User | Host      | db   | Command | Time | State        | Info              |
    +----+------+-----------+------+---------+------+--------------+-------------------+
    | 32 | root | localhost | test | Killed  | 1834 | Sending data | select * from t_2 |
    | 43 | root | localhost | test | Sleep   | 1419 |              | NULL              |
    | 46 | root | localhost | NULL | Query   |    0 | init         | show processlist  |
    +----+------+-----------+------+---------+------+--------------+-------------------+
    3 rows in set (0.00 sec)

    惨痛的教训： 只能通过kill mysql才能释放链接

参考文献

http://dev.mysql.com/doc/refman/5.7/en/tablespace-copying.html

InnoDB可传输表空间

背景

它有什么优势

哪些需要注意

Transportable Tablespace Examples

Ex1: Copying an InnoDB Partitioned Table From One Server To Another

Ex2: Copying an InnoDB Table From One Server To Another

Transportable Tablespace Internals

Question & Tips

热门文章

最新文章

相关电子书

相关实验场景