要求表的引擎是innodb 且是每个表是单独表空间
实验版本mysql 8.0.25
#---------------------------目标库(可以在单个mysqld程序中,没有必要时2个mysql)---------------------------
#1 创建一个测试库
CREATE DATABASE if not exists `test1` /*!40100 COLLATE 'utf8mb4_bin' */;
#2. 复制test库的表结构,可以用mysqldump 导出表结构或 直接用以下语句创建;
# 对于单个表 可以用create like
# 对于多个表最好用mysqldump 只导出表结构 mysqldump --no-data --compact my_db>createtb.sql
use test1;
create table demo like test.demo;
create table exam_core_result_hot like test.exam_core_result_hot;
#3.丢弃表空间,这时会删除表文件,看好了再操作!!!!
#使用该脚本可以批量执行
# mysql -e "show tables from my_db" | grep -v Tables_in_my_db | while read a; do mysql -e "ALTER TABLE my_db.$a DISCARD TABLESPACE"; done
ALTER TABLE demo DISCARD TABLESPACE;
ALTER TABLE exam_core_result_hot DISCARD TABLESPACE;
#4.拷贝表ibd 文件和cfg 文件到test1库目录
根据官方文档描述,导入时可以不需要cfg 文件
https://dev.mysql.com/doc/refman/8.0/en/innodb-table-import.html#innodb-table-import-example
ALTER TABLE ... IMPORT TABLESPACE does not require a .cfg metadata file to import a table.
However, metadata checks are not performed when importing without a .cfg file, and a warning similar to the following is issued:
Importing a table without a .cfg metadata file should only be considered if no schema mismatches are expected.
The ability to import without a .cfg file could be useful in crash recovery scenarios where metadata is not accessible.
#5.文件拷贝完成后 导入表空间 ,别忘了把属主改过来:chown mysql. *
#mysql -e "show tables from my_db" | grep -v Tables_in_my_db while read a; do mysql -e "ALTER TABLE my_db.$a import TABLESPACE"; done
alter table demo import tablespace;
alter table exam_core_result_hot import tablespace;
#6.导入成功后, 删除 cfg 文件 rm -rf *.cfg
#-----------------------------源库-------------------------------------------------------------------------
#1.导出表空间,此时表只能读不能写
FLUSH TABLES demo,exam_core_result_hot FOR EXPORT;
#2. 拷贝 demo,exam_core_result_hot表的ibd 文件和 cfg文件到test1
#3. 解锁 ,否则表不能访问
unlock tables;