MySQL innodb表使用表空间ibd文件复制或迁移表

Wesley13
• 阅读 532

MySQL InnoDB引擎的表通过拷贝物理文件来进行单表或指定表的复制,可以想到多种方式,今天测试其中2种:

  • 将innodb引擎的表修改为Myisam引擎,然后拷贝物理文件

  • 直接拷贝innodb的表空间文件(前提是独立表空间(默认,通过show variables like 'innodb_file_per_table' 查看))进行复制

一、修改引擎

1.创建一张innodb引擎的表,并插入测试数据;

create table test_tb(id int primary key,c1 varchar(20)) ENGINE=InnoDB DEFAULT CHARSET=utf8;

MySQL innodb表使用表空间ibd文件复制或迁移表

  1. 修改引擎

    alter table test_tb engine=myisam;

MySQL innodb表使用表空间ibd文件复制或迁移表

3. 将物理文件拷贝至目标库

cd /data/mysql/mysql3307/data/

MySQL innodb表使用表空间ibd文件复制或迁移表

4.修改权限

chown -R mysql:mysql .

MySQL innodb表使用表空间ibd文件复制或迁移表

5. 查看结果

MySQL innodb表使用表空间ibd文件复制或迁移表

记录和源库一致。

6. 将源库及目标库的表引擎修改为innodb

alter table testdb.test_tb engine=innodb;

MySQL innodb表使用表空间ibd文件复制或迁移表

二、拷贝.idb物理表空间文件

1.  创建一张innodb的表,为了测试大表的情况,我创建了一张800W记录的表,占用940M空间

/*先创建快速生成连续数的表及存储过程*/

2. 在目标库创建相同的表名

mysql> use testdb2;

3. 删除目标表的表空间

alter table test_tb2 discard tablespace;

此时目标库的test_tb2表近剩下数据定义文件,表空间文件已删除

MySQL innodb表使用表空间ibd文件复制或迁移表

4. 拷贝源库的idb文件

MySQL innodb表使用表空间ibd文件复制或迁移表

  1. 修改表空间文件权限

MySQL innodb表使用表空间ibd文件复制或迁移表

6. 目标表导入表空间数据(记录较多的时候需要一点时间)

alter table test_tb2 import tablespace;

MySQL innodb表使用表空间ibd文件复制或迁移表

7. 查看导入结果

MySQL innodb表使用表空间ibd文件复制或迁移表

结果与源表一致

Tips:

  1. 以上2种处理方式都需要源表无写入更新等操作下进行,且需要flush tables 将数据刷新到物理磁盘的文件上。所以建议先锁表或停止业务,待拷贝文件后再恢复写入等操作。

  2. 此方法在某些场景下将可能导致数据库重启慎用!!!

本文分享自微信公众号 - 数据库干货铺(database_gjc)。
如有侵权,请联系 support@oschina.cn 删除。
本文参与“OSC源创计划”,欢迎正在阅读的你也加入,一起分享。

点赞
收藏
评论区
推荐文章
blmius blmius
1年前
MySQL:[Err] 1292 - Incorrect datetime value: ‘0000-00-00 00:00:00‘ for column ‘CREATE_TIME‘ at row 1
文章目录问题用navicat导入数据时,报错:原因这是因为当前的MySQL不支持datetime为0的情况。解决修改sql\mode:sql\mode:SQLMode定义了MySQL应支持的SQL语法、数据校验等,这样可以更容易地在不同的环境中使用MySQL。全局s
Peter20 Peter20
1年前
Mysql中MVCC的使用及原理详解
数据库默认隔离级别:RR(RepeatableRead,可重复读),MVCC主要适用于Mysql的RC,RR隔离级别创建一张存储引擎为testmvcc的表,sql为:CREATETABLEtestmvcc(idint(11)DEFAULTNULL,namevarchar(11)DEFAULTNULL)ENGINE\InnoDB
Wesley13 Wesley13
1年前
030 SSM综合练习06
1.权限操作涉及的三张表(1)用户表信息描述users!(https://oscimg.oschina.net/oscnet/a4a2b1f943cbc2db1c8ddd613e7ed00a9ae.png)sql语句:CREATETABLEusers(idVARCHAR2(32)DEFAU
Wesley13 Wesley13
1年前
MySQL索引的索引长度问题
MySQL的每个单表中所创建的索引长度是有限制的,且对不同存储引擎下的表有不同的限制。在MyISAM表中,创建组合索引时,创建的索引长度不能超过1000,注意这里索引的长度的计算是根据表字段设定的长度来标量的,例如:createtabletest(idint,name1varchar(300),name2varchar(300),nam
Wesley13 Wesley13
1年前
MySQL查询按照指定规则排序
1.按照指定(单个)字段排序selectfromtable_nameorderiddesc;2.按照指定(多个)字段排序selectfromtable_nameorderiddesc,statusdesc;3.按照指定字段和规则排序selec
Wesley13 Wesley13
1年前
mysql 常用函数
★1\表级锁情况SHOWSTATUSLIKE'table%';\行级锁情况SHOWSTATUSLIKE'innodb\_row\_lock%';\进程列表SHOWPROCESSLIST;SHOWSTATUS;★locate和instr和regexp
Wesley13 Wesley13
1年前
oracle:ORA
报ORA25153,这一定是临时表空间逻辑名还在,但是找不到临时文件的原因。解决方法:1、查询临时表空间状态状态selecttablespace\_name,statusfromdba\_tablespaces;TABLESPACE\_NAMESTATUS\SYSTEMONL
3A网络 3A网络
4个月前
开发一个不需要重写成 Hive QL 的大数据 SQL 引擎
开发一个不需要重写成HiveQL的大数据SQL引擎学习大数据技术的核心原理,掌握一些高效的思考和思维方式,构建自己的技术知识体系。明白了原理,有时甚至不需要学习,顺着原理就可以推导出各种实现细节。各种知识表象看杂乱无章,若只是学习
3A网络 3A网络
4个月前
理解 virt、res、shr 之间的关系(linux 系统篇)
理解virt、res、shr之间的关系(linux系统篇)前言想必在linux上写过程序的同学都有分析进程占用多少内存的经历,或者被问到这样的问题——你的程序在运行时占用了多少内存(物理内存)?通常我们可以通过t
京东云开发者 京东云开发者
2个月前
为什么mysql不推荐使用雪花ID作为主键
作者:毛辰飞背景在mysql中设计表的时候,mysql官方推荐不要使用uuid或者不连续不重复的雪花id(long形且唯一),而是推荐连续自增的主键id,官方的推荐是auto_increment,那么为什么不建议采用uuid,使用uuid究