MySQL innodb表使用資料表空間物理檔案複製或遷移表

標籤：mys   src   獨立   需要   variables   nod   ike   run   測試資料   

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； insert into test_tb select 1,‘c1‘;insert into test_tb select 2,‘c2‘;

 

 2. 修改引擎

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

3. 將物理檔案拷貝至目標庫

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

4.修改許可權

chown -R mysql:mysql .

5. 查看結果

記錄和源庫一致。

6. 將源庫及目標庫的表引擎修改為innodb

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

 

二、拷貝.idb物理資料表空間檔案

1.  建立一張innodb的表，為了測試大表的情況，我建立了一張800W記錄的表，佔用940M空間

/*先建立快速產生連續數的表及預存程序*/-- 建表 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;

2. 在目標庫建立相同的表名

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;

3. 刪除目標表的資料表空間

alter table test_tb2 discard tablespace;

此時目標庫的test_tb2表近剩下資料定義檔案，資料表空間檔案已刪除

4. 拷貝源庫的idb檔案

 5. 修改資料表空間檔案許可權

6. 目標表匯入資料表空間資料（記錄較多的時候需要一點時間）

alter table test_tb2 import tablespace;

7. 查看匯入結果

結果與源表一致

 

Tips:

以上2種處理方式都需要源表無寫入更新等操作下進行，且需要flush tables 將資料重新整理到物理磁碟的檔案上。所以建議先鎖表或停止業務，待拷貝檔案後再恢複寫入等操作。

 

MySQL innodb表使用資料表空間物理檔案複製或遷移表