Transportable Tablespace示例

Transportable Tablespace示例

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

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

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
(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端

1
2
3
4
5
6
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:

1
2
3
4
5
6
7
(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产生的锁

1
2
3
4
5
(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.在目标端,导入表空间

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
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功能不会对导入的数据实施外键约束。 如果表之间存在外键约束,则应在相同(逻辑)时间点导出所有表。 在这种情况下,您将停止更新表,提交所有事务,获取表上的共享锁,然后执行导出操作。

Powered by Hexo and Hexo-theme-hiker

Copyright © 2013 - 2018 Fan() All Rights Reserved.

访客数 : | 访问量 :

#