[MySQL] 传输大表的快捷方法--Transportable Tablespace
来源:互联网 发布:手机淘宝9.9包邮 编辑:程序博客网 时间:2024/05/21 09:03
Example 1: Copying an InnoDB Table to Another Instance
This procedure demonstrates how to copy a regular InnoDB
table from a running MySQL server instance to another running instance. The same procedure with minor adjustments can be used to perform a full table restore on the same instance.
On the source instance, create a table if one does not exist:
mysql> use test;mysql> CREATE TABLE t(c1 INT) engine=InnoDB;
On the destination instance, create a table if one does not exist:
mysql> use test;mysql> CREATE TABLE t(c1 INT) engine=InnoDB;
On the destination instance, discard the existing tablespace. (Before a tablespace can be imported,
InnoDB
must discard the tablespace that is attached to the receiving table.)mysql> ALTER TABLE t DISCARD TABLESPACE;
On the source instance, run
FLUSH TABLES ... FOR EXPORT
to quiesce the table and create the.cfg
metadata file:mysql> use test;mysql> FLUSH TABLES t FOR EXPORT;
The metadata (
.cfg
) is created in theInnoDB
data directory.NoteFLUSH 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 instance is running. WhenFLUSH 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.Copy the
.ibd
file and.cfg
metadata file from the source instance to the destination instance. For example:shell> scp
/path/to/datadir
/test/t.{ibd,cfg} destination-server:/path/to/datadir
/testNoteThe
.ibd
file and.cfg
file must be copied before releasing the shared locks, as described in the next step.On the source instance, use
UNLOCK TABLES
to release the locks acquired byFLUSH TABLES ... FOR EXPORT
:mysql> use test;mysql> UNLOCK TABLES;
On the destination instance, import the tablespace:
mysql> use test;mysql> ALTER TABLE t IMPORT TABLESPACE;
NoteThe
ALTER TABLE ... IMPORT TABLESPACE
feature does not enforce foreign key constraints on imported data. If there are foreign key constraints between tables, all tables should be exported at the same (logical) point in time. In this case you would stop updating the tables, commit all transactions, acquire shared locks on the tables, and then perform the export operation.
Example 2: Copying an InnoDB Partitioned Table to Another Instance
This procedure demonstrates how to copy a partitioned InnoDB
table from a running MySQL server instance to another running instance. The same procedure with minor adjustments can be used to perform a full restore of a partitioned InnoDB
table on the same instance.
On the source instance, create a partitioned table if one does not exist. In the following example, a table with three partitions (p0, p1, p2) is created:
mysql> use test;mysql> CREATE TABLE t1 (i int) ENGINE = InnoDB PARTITION BY KEY (i) PARTITIONS 3;
In the
/
directory, there is a separate tablespace (datadir
/test.ibd
) file for each of the three partitions.mysql> \! ls
/path/to/datadir
/test/db.opt t1.frm t1#P#p0.ibd t1#P#p1.ibd t1#P#p2.ibdOn the destination instance, create the same partitioned table:
mysql> use test;mysql> CREATE TABLE t1 (i int) ENGINE = InnoDB PARTITION BY KEY (i) PARTITIONS 3;
In the
/
directory, there is a separate tablespace (datadir
/test.ibd
) file for each of the three partitions.mysql> \! ls
/path/to/datadir
/test/db.opt t1.frm t1#P#p0.ibd t1#P#p1.ibd t1#P#p2.ibdOn the destination instance, discard the tablespace for the partitioned table. (Before the tablespace can be imported on the destination instance, the tablespace that is attached to the receiving table must be discarded.)
mysql> ALTER TABLE t1 DISCARD TABLESPACE;
The three
.ibd
files that make up the tablespace for the partitioned table are discarded from the/
directory, leaving the following files:datadir
/testmysql> \! ls
/path/to/datadir
/test/db.opt t1.frmOn the source instance, run
FLUSH TABLES ... FOR EXPORT
to quiesce the partitioned table and create the.cfg
metadata files:mysql> use test;mysql> FLUSH TABLES t1 FOR EXPORT;
Metadata (
.cfg
) files, one for each tablespace (.ibd
) file, are created in the/
directory on the source instance:datadir
/testmysql> \! ls
/path/to/datadir
/test/db.opt t1#P#p0.ibd t1#P#p1.ibd t1#P#p2.ibdt1.frm t1#P#p0.cfg t1#P#p1.cfg t1#P#p2.cfgNoteFLUSH TABLES ... FOR EXPORT
statement ensures that changes to the named table have been flushed to disk so that binary table copy can be made while the instance is running. WhenFLUSH TABLES ... FOR EXPORT
is run,InnoDB
produces a.cfg
metadata file for the table's tablespace files in the same database directory as the table. The.cfg
files contain metadata used for schema verification when importing tablespace files.FLUSH TABLES ... FOR EXPORT
can only be run on the table, not on individual table partitions.Copy the
.ibd
and.cfg
files from the source instance database directory to the destination instance database directory. For example:shell> scp
/path/to/datadir
/test/t1*.{ibd,cfg} destination-server:/path/to/datadir
/testNoteThe
.ibd
and.cfg
files must be copied before releasing the shared locks, as described in the next step.On the source instance, use
UNLOCK TABLES
to release the locks acquired byFLUSH TABLES ... FOR EXPORT
:mysql> use test;mysql> UNLOCK TABLES;
On the destination instance, import the tablespace for the partitioned table:
mysql> use test;mysql> ALTER TABLE t1 IMPORT TABLESPACE;
Example 3: Copying InnoDB Table Partitions to Another Instance
This procedure demonstrates how to copy InnoDB
table partitions from a running MySQL server instance to another running instance. The same procedure with minor adjustments can be used to perform a restore of InnoDB
table partitions on the same instance. In the following example, a partitioned table with four partitions (p0, p1, p2, p3) is created on the source instance. Two of the partitions (p2 and p3) are copied to the destination instance.
On the source instance, create a partitioned table if one does not exist. In the following example, a table with four partitions (p0, p1, p2, p3) is created:
mysql> use test;mysql> CREATE TABLE t1 (i int) ENGINE = InnoDB PARTITION BY KEY (i) PARTITIONS 4;
In the
/
directory, there is a separate tablespace (datadir
/test.ibd
) file for each of the four partitions.mysql> \! ls
/path/to/datadir
/test/db.opt t1.frm t1#P#p0.ibd t1#P#p1.ibd t1#P#p2.ibd t1#P#p3.ibdOn the destination instance, create the same partitioned table:
mysql> use test;mysql> CREATE TABLE t1 (i int) ENGINE = InnoDB PARTITION BY KEY (i) PARTITIONS 4;
In the
/
directory, there is a separate tablespace (datadir
/test.ibd
) file for each of the four partitions.mysql> \! ls
/path/to/datadir
/test/db.opt t1.frm t1#P#p0.ibd t1#P#p1.ibd t1#P#p2.ibd t1#P#p3.ibdOn the destination instance, discard the tablespace partitions that you plan to import from the source instance. (Before tablespace partitions can be imported on the destination instance, the corresponding partitions that are attached to the receiving table must be discarded.)
mysql> ALTER TABLE t1 DISCARD PARTITION p2, p3 TABLESPACE;
The
.ibd
files for the two discarded partitions are removed from the/
directory on the destination instance, leaving the following files:datadir
/testmysql> \! ls
/path/to/datadir
/test/db.opt t1.frm t1#P#p0.ibd t1#P#p1.ibdNoteWhen
ALTER TABLE ... DISCARD PARTITION ... TABLESPACE
is run on subpartitioned tables, both partition and subpartition table names are allowed. When a partition name is specified, subpartitions of that partition are included in the operation.On the source instance, run
FLUSH TABLES ... FOR EXPORT
to quiesce the partitioned table and create the.cfg
metadata files.mysql> use test;mysql> FLUSH TABLES t1 FOR EXPORT;
The metadata files (
.cfg
files) are created in the/
directory on the source instance. There is adatadir
/test.cfg
file for each tablespace (.ibd
) file.mysql> \! ls
/path/to/datadir
/test/db.opt t1#P#p0.ibd t1#P#p1.ibd t1#P#p2.ibd t1#P#p3.ibdt1.frm t1#P#p0.cfg t1#P#p1.cfg t1#P#p2.cfg t1#P#p3.cfgNoteFLUSH TABLES ... FOR EXPORT
statement ensures that changes to the named table have been flushed to disk so that binary table copy can be made while the instance is running. WhenFLUSH TABLES ... FOR EXPORT
is run,InnoDB
produces a.cfg
metadata file for the table's tablespace files in the same database directory as the table. The.cfg
files contain metadata used for schema verification when importing tablespace files.FLUSH TABLES ... FOR EXPORT
can only be run on the table, not on individual table partitions.Copy the
.ibd
and.cfg
files from the source instance database directory to the destination instance database directory. In this example, only the.ibd
and.cfg
files for partition 2 (p2) and partition 3 (p3) are copied to thedata
directory on the destination instance. Partition 0 (p0) and partition 1 (p1) remain on the source instance.shell> scp t1#P#p2.ibd t1#P#p2.cfg t1#P#p3.ibd t1#P#p3.cfg destination-server:
/path/to/datadir
/testNoteThe
.ibd
files and.cfg
files must be copied before releasing the shared locks, as described in the next step.On the source instance, use
UNLOCK TABLES
to release the locks acquired byFLUSH TABLES ... FOR EXPORT
:mysql> use test;mysql> UNLOCK TABLES;
On the destination instance, import the tablespace partitions (p2 and p3):
mysql> use test;mysql> ALTER TABLE t1 IMPORT PARTITION p2, p3 TABLESPACE;
NoteWhen
ALTER TABLE ... IMPORT PARTITION ... TABLESPACE
is run on subpartitioned tables, both partition and subpartition table names are allowed. When a partition name is specified, subpartitions of that partition are included in the operation.
- [MySQL] 传输大表的快捷方法--Transportable Tablespace
- Oracle Transportable TableSpace(TTS) 传输表空间 说明
- Oracle Transportable TableSpace(TTS) 传输表空间 说明
- MySql 传输表空间 Transportable Tablespaces
- transportable tablespace
- transportable tablespace: Oracle 10.2.0.4 -> 11.2.0.3
- mysql5.6新特性--transportable tablespace
- mysql5.6新功能transportable tablespaces(可传输表空间)进行远程备份数据库
- asmcmd cp command on the test with transportable tablespace
- oracle 删除表空间(tablespace)及文件的方法
- oracle 删除表空间(tablespace)及数据文件的方法
- Oracle 传输表空间(Transportable Tablespaces) 示例(一) -- 跨操作系统迁移表空间 (endianness 格式相同)
- Oracle 传输表空间(Transportable Tablespaces) 示例(二) -- 跨操作系统迁移表空间(endianness格式不同)
- Oracle Bigfile Tablespace大文件表空间
- Oracle Bigfile Tablespace大文件表空间
- MySQL大表备份的简单方法
- offline tablespace的几种方法
- mysql使用传输表空间迁移大表测试
- spark-submit
- 同步阻塞与异步非阻塞适用场景
- C++第3次实验(基础班)—选择结构程序设计(参考答案)-项目4:多分数段函数求值
- 区间覆盖问题 (sdut oj)
- 百度深度学习实验室
- [MySQL] 传输大表的快捷方法--Transportable Tablespace
- Altium Designer 10对集成库的理解
- Jetty 热部署会丢失Session
- formdata异步上传图片(文件)
- 封装的 php 函数实例 获取客户端IP 获取在线IP 获取url 获取当前站点的访问路径根目录 数字转人民币
- maven常用命令
- theano中 founction的用法
- 关于webView拦截H5网络请求数据,替换本地数据问题
- windows 配置端口转发