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

开发者社区> 兰春> 正文

InnoDB可传输表空间

简介:
+关注继续查看

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.
    
  3. 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.
    
  4. 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

    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

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

相关文章
jQuery中的cache : false到底有什么用?
jQuery中的cache : false到底有什么用?
4 0
Mybaits结果集之集合,Javabean中嵌套List的解决方案
Mybaits结果集之集合,Javabean中嵌套List的解决方案
5 0
Java 的核心目的和并发编程
Java 的核心目的和并发编程
6 0
请注意,key是mysql的关键字,建表的时候尽量少用
请注意,key是mysql的关键字,建表的时候尽量少用
6 0
高效能程序员的修炼札记:安全基础,保护用户数据
高效能程序员的修炼札记:安全基础,保护用户数据
4 0
重学Java(五):操作符
重学Java(五):操作符
8 0
Eclipse的Java build path,可以将一个项目依赖于另外一个项目
Eclipse的Java build path,可以将一个项目依赖于另外一个项目
5 0
推荐,Java Emoji Converter(Emoji表情转换工具)
推荐,Java Emoji Converter(Emoji表情转换工具)
4 0
mysql innodb引擎下的行锁由于意外没有被释放,导致后面的请求无法继续,怎么办?
mysql innodb引擎下的行锁由于意外没有被释放,导致后面的请求无法继续,怎么办?
4 0
+关注
兰春
数据库技术爱好者,专注于MySQL领域的运维与运营,擅长性能调优,系统瓶颈分析,热爱数据领域的一切
65
文章
0
问答
文章排行榜
最热
最新
相关电子书
更多
文娱运维技术
立即下载
《SaaS模式云原生数据仓库应用场景实践》
立即下载
《看见新力量:二》电子书
立即下载