Transportable Tablespace示例

2017-08-22 阅读量

Transportable Tablespace示例

http://dev.mysql.com/doc/refman/5.7/en/innodb-transportable-tablespace-examples.html

例1:将InnoDB表从一个服务器复制到另一个服务器

此过程演示如何将InnoDB表从正在运行的MySQL服务器实例复制到另一个正在运行的实例.经过一些小调整相同过程可用于在同一实例上执行完整表恢复.
1.source端

(mysql@localhost) [test]> show table status like 'dept'\G
*************************** 1. row ***************************
           Name: dept
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 0
 Avg_row_length: 0
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2016-08-26 14:25:50
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options: 
        Comment: 
1 row in set (0.00 sec)

(mysql@localhost) [test]> select * from dept;
+--------+------------+----------+
| deptno | dname      | loc      |
+--------+------------+----------+
|     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |
+--------+------------+----------+
4 rows in set (0.01 sec)

2.target端

CREATE TABLE `dept` (
  `deptno` int(11) NOT NULL,
  `dname` varchar(14) DEFAULT NULL,
  `loc` varchar(13) DEFAULT NULL,
  PRIMARY KEY (`deptno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

3.在目标端discard tablespace.Before a tablespace can be imported, InnoDB must discard the tablespace that is attached to the receiving table

1	mysql> alter table dept discard tablespace;

4.在源端, run FLUSH TABLES … FOR EXPORT to quiesce the table and create the .cfg metadata file:

(mysql@localhost) [test]> flush tables dept for export;
查看error log有如下提示.mysql会先将dept表脏块刷新到磁盘,然后生成.cfg文件.并且以某种方式锁住dept表,通过information_schema.INNODB_TRX和information_schema.INNODB_LOCKS表查不到信息
2017-01-04 18:23:42 5174 [Note] InnoDB: Sync to disk of '"test"."dept"' started.
2017-01-04 18:23:42 5174 [Note] InnoDB: Stopping purge
2017-01-04 18:23:42 5174 [Note] InnoDB: Writing table metadata to './test/dept.cfg'
2017-01-04 18:23:42 5174 [Note] InnoDB: Table '"test"."dept"' flushed to disk
此时无法对dept表执行DML操作,只能执行只读操作

Note
FLUSH TABLES … FOR EXPORT is available as of MySQL 5.6.6. The statement ensures that changes to the named table have been flushed to disk so that a binary table copy can be made while the server is running. When FLUSH TABLES … FOR EXPORT is run, InnoDB produces a .cfg file in the same database directory as the table. The .cfg file contains metadata used for schema verification when importing the tablespace file.

5.拷贝.ibd文件和.cfg元数据文件到目标端

1	cp /data/mysqldata/3306/data/test/dept.{ibd,cfg} /data/mysqldata/3307/data/test

一定要在shared locks释放之前完成拷贝

6.在源端执行UNLOCK TABLES释放FLUASH TABLES .. FOR EXPORT产生的锁

(mysql@localhost) [test]> unlock tables;
Query OK, 0 rows affected (0.00 sec)

2017-01-04 18:34:28 5174 [Note] InnoDB: Deleting the meta-data file './test/dept.cfg'
2017-01-04 18:34:28 5174 [Note] InnoDB: Resuming purge

7.在目标端,导入表空间

mysql> alter table dept import tablespace;
Query OK, 0 rows affected (0.02 sec)

mysql> select * from dept;
+--------+------------+----------+
| deptno | dname      | loc      |
+--------+------------+----------+
|     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |
+--------+------------+----------+
4 rows in set (0.00 sec)

2017-01-04 18:35:46 16972 [Note] InnoDB: Importing tablespace for table 'test/dept' that was exported from host 'master'
2017-01-04 18:35:46 16972 [Note] InnoDB: Phase I - Update all pages
2017-01-04 18:35:46 16972 [Note] InnoDB: Sync to disk
2017-01-04 18:35:46 16972 [Note] InnoDB: Sync to disk - done!
2017-01-04 18:35:46 16972 [Note] InnoDB: Phase III - Flush changes to disk
2017-01-04 18:35:46 16972 [Note] InnoDB: Phase IV - Flush complete
告警日志里没显示阶段2是啥

注意:
ALTER TABLE … IMPORT TABLESPACE功能不会对导入的数据实施外键约束。如果表之间存在外键约束，则应在相同（逻辑）时间点导出所有表。在这种情况下，您将停止更新表，提交所有事务，获取表上的共享锁，然后执行导出操作。