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

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

相关文章
阿里云服务器怎么设置密码?怎么停机?怎么重启服务器?
如果在创建实例时没有设置密码,或者密码丢失,您可以在控制台上重新设置实例的登录密码。本文仅描述如何在 ECS 管理控制台上修改实例登录密码。
10025 0
【译】MySQL服务博客 - InnoDB中的空间数据索引
MySQL 8.0的一个重要特性是对地理业务的支持。MySQL现在已拥有一类功能称之为空间参考系统(SRS),其中近500个是地理区域相关的。大多数函数还支持地理计算。
914 0
innodb表空间加密
表空间加密的注意事项
110 0
阿里云服务器端口号设置
阿里云服务器初级使用者可能面临的问题之一. 使用tomcat或者其他服务器软件设置端口号后,比如 一些不是默认的, mysql的 3306, mssql的1433,有时候打不开网页, 原因是没有在ecs安全组去设置这个端口号. 解决: 点击ecs下网络和安全下的安全组 在弹出的安全组中,如果没有就新建安全组,然后点击配置规则 最后如上图点击添加...或快速创建.   have fun!  将编程看作是一门艺术,而不单单是个技术。
10880 0
《网络空间欺骗:构筑欺骗防御的科学基石》一1.3 恶意策略、技术和常识
本文讲的是网络空间欺骗:构筑欺骗防御的科学基石一1.3 恶意策略、技术和常识,本节书摘来华章计算机《网络空间欺骗:构筑欺骗防御的科学基石》一书中的第1章,第1.3节, Cyber Deception: Building the Scientific Foundation 苏西尔·贾乔迪亚(Sushil Jajodia)V. S.苏夫拉曼尼(V. S. Subrahmanian)[美] 维平·斯沃尔(Vipin Swarup) 著 克利夫·王(Cliff Wang) 马多贺 雷程 译 译更多章节内容可以访问云栖社区“华章计算机”公众号查看。
1347 0
阿里云服务器如何登录?阿里云服务器的三种登录方法
购买阿里云ECS云服务器后如何登录?场景不同,阿里云优惠总结大概有三种登录方式: 登录到ECS云服务器控制台 在ECS云服务器控制台用户可以更改密码、更换系.
13839 0
阿里云ECS云服务器初始化设置教程方法
阿里云ECS云服务器初始化是指将云服务器系统恢复到最初状态的过程,阿里云的服务器初始化是通过更换系统盘来实现的,是免费的,阿里云百科网分享服务器初始化教程: 服务器初始化教程方法 本文的服务器初始化是指将ECS云服务器系统恢复到最初状态,服务器中的数据也会被清空,所以初始化之前一定要先备份好。
11881 0
阿里云ECS云服务器初始化设置教程方法
阿里云ECS云服务器初始化是指将云服务器系统恢复到最初状态的过程,阿里云的服务器初始化是通过更换系统盘来实现的,是免费的,阿里云百科网分享服务器初始化教程: 服务器初始化教程方法 本文的服务器初始化是指将ECS云服务器系统恢复到最初状态,服务器中的数据也会被清空,所以初始化之前一定要先备份好。
7347 0
+关注
兰春
数据库技术爱好者,专注于MySQL领域的运维与运营,擅长性能调优,系统瓶颈分析,热爱数据领域的一切
74
文章
0
问答
文章排行榜
最热
最新
相关电子书
更多
《2021云上架构与运维峰会演讲合集》
立即下载
《零基础CSS入门教程》
立即下载
《零基础HTML入门教程》
立即下载