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

create table test_tb(id int primary key,c1 varchar(20)) ENGINE=InnoDB DEFAULT CHARSET=utf8；insert into test_tb select 1,'c1';insert into test_tb select 2,'c2';

alter table test_tb engine=myisam;show create table test_tb\G

cd /data/mysql/mysql3307/data/
cd testdbllcd ../testdb2/pwdllcp ../testdb/test_tb.* .ll

chown -R mysql:mysql .
5. 查看结果

alter table testdb.test_tb engine=innodb;alter table testdb2.test_tb engine=innodb;

/*先创建快速生成连续数的表及存储过程*/
-- 建表 CREATE TABLE `test_tb2` (  `id` int(11) DEFAULT NULL,  `aa` varchar(20) DEFAULT NULL,  `bb` varchar(20) DEFAULT NULL,  `cc` varchar(20) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--创建过程DELIMITER $$CREATE  PROCEDURE `sp_test_tb2`(cnt INT )BEGIN    DECLARE i INT  DEFAULT 1;    TRUNCATE TABLE test_tb2;    INSERT INTO test_tb2 SELECT concat(i,'a'),concat(i,'b'),concat(i,'c')  ;    WHILE i < cnt DO      BEGIN        INSERT INTO test_tb2 SELECT id + i,concat(id+i,'a'),concat(id+i,'b'),concat(id+i,'c') FROM test_tb2 WHERE id + i<=cnt;        SET i = i*2;      END;    END WHILE;END$$
DELIMITER ;-- 生成8000000条记录call sp_test_tb2(8000000);
select count(*) from test_tb2;

mysql> use testdb2;
 CREATE TABLE `test_tb2` (  `id` int(11) DEFAULT NULL, `aa` varchar(20) DEFAULT NULL, `bb` varchar(20) DEFAULT NULL, `cc` varchar(20) DEFAULT NULL, ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

alter table test_tb2 discard tablespace;

alter table test_tb2 import tablespace;
7. 查看导入结果