By 兰春

Updated:

Contents
  1. 1. 背景
  2. 2. 它有什么优势
  3. 3. 哪些需要注意
  4. 4. Transportable Tablespace Examples
    1. 4.1. Ex1: Copying an InnoDB Partitioned Table From One Server To Another
    2. 4.2. Ex2: Copying an InnoDB Table From One Server To Another
  5. 5. Transportable Tablespace Internals
  6. 6. Question & Tips

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

背景

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

  1. MySQL Enterprise Backup (no need consider)
  2. Copying Data Files (Cold Backup Method)
  3. Export and Import (mysqldump,mydumper,mysqlpump)

现在我们有第四种方案

  1. Transportable Tablespaces

它有什么优势

  1. To run reports without putting extra load on a production server
  2. To set up identical data for a table on a new slave server
  3. To restore a backed-up version of a table or partition after a problem or mistake
  4. 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
  5. 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
  6. 新备份（类似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呢

哪些需要注意

  1. 必须开启 innodb_file_per_table
  2. 当这个表处于quiesced状态,甚至不能被select
  3. 两边实例的page size 一致
  4. 5.7 版本之前，不支持分区表transport
  5. 外键相关的表，必须设置 foreign_key_checks=0 才能成功
  6. ALTER TABLE … IMPORT TABLESPACE 不需要.cfg metadata file . 但是，这样的话，MySQL就不会对schema进行verificate
  7. 5.6以及更高版本，import&export 版本必须在同一个series
  8. 在replication环境中，master & slave 都必须开启 innodb_file_per_table
  9. 对于InnoDB general tablespace，不支持discard & import tablespace
  10. 如果两边服务器的table row_format设置的不一样，会导致schema mismatch error
  11. 加密过的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的传输过程

  1. 在source server A上，创建一个表

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

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

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

  3. 在destination server B上，discard 该表（这一步是必须的） 

     mysql> ALTER TABLE t DISCARD TABLESPACE;

  4. 在source server A上，执行 FLUSH TABLES … FOR EXPORT , 该表这时候处于quiesce状态，只读，且创建.cfg metadata文件 

     mysql> use test;
 mysql> FLUSH TABLES t FOR EXPORT;

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

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

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

  6. 在source server A， 执行unlock tables 来释放FLUSH TABLES … FOR EXPORT 加的locks 

     mysql> use test;  
 mysql> UNLOCK TABLES;

  7. 在destination server B上，导入tablespace

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

Transportable Tablespace Internals

  1. ALTER TABLE … DISCARD TABLESPACE Internals 

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

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

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

  2. 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成功吗

      1. 可以，但是这样就没办法认证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
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
  +-------+--------------------------------------------------------------------------------------------------------------------------------+
  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> 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

Contents
  1. 1. 背景
  2. 2. 它有什么优势
  3. 3. 哪些需要注意
  4. 4. Transportable Tablespace Examples
    1. 4.1. Ex1: Copying an InnoDB Partitioned Table From One Server To Another
    2. 4.2. Ex2: Copying an InnoDB Table From One Server To Another
  5. 5. Transportable Tablespace Internals
  6. 6. Question & Tips

幸福,不在于得到的多

而在于计较的少