本文介紹如何使用MySQL傳輸表空間特性導入表,該特性允許導入表、分區。
【前置條件】
(1)MySQL必須是5.6及以上的版本
(2)必須是InnoDB表
(3)必須使用獨立表空間模式(innodb_file_per_table=ON),不支持共享表空間
(4)源庫與目標庫的page_size必須一致
(5)當表做導出操作時,該表只能進行只讀操作
(6)當foreign_key_check設置為1時,不支持父子(主鍵-外鍵)關係的表空間DISCARD。在為父-子表DISCARD表空間之前,設置foreign_key_check =0。分區的InnoDB表不支持外鍵。
(7)ALTER TABLE……IMPORT TABLESPACE不會對導入的數據強制外鍵約束。如果表之間存在外鍵約束,則所有表都應該在同一時間點(邏輯上)導出。分區的InnoDB表不支持外鍵。
(8)在複製場景中,innodb_file_per_table必須在master和slave上都設置為ON。
(9)最好設置lower_case_table_names=1(1表示表名以小寫形式存儲在磁盤上,名稱比較不區分大小寫。MySQL將所有表名轉換為小寫的存儲和查找,默認為0),避免表明大小寫的問題。
【適用場景】
(1)報表統計,避免給生產庫帶來壓力
(2)單表或單分區出問題時恢復
(3)某些場景下可以替換mysqldump,不必重新插入和重新構建索引,效率高
(4)遷移
【步驟解讀】
1. 在目標端創建表結構一樣的表
mysql> CREATE TABLE t(c1 INT) ENGINE=InnoDB;
2. 在目標端丟棄表空間
mysql> ALTER TABLE t DISCARD TABLESPACE;
獲取表上的X鎖
表空間與表分離,並刪除.ibd文件
3. 在源端FLUSH TABLES ... FOR EXPORT(使表數據靜態,並創建.cfg元數據文件)
mysql> FLUSH TABLES t FOR EXPORT;
獲取表的S鎖
停止purge線程
刷新髒頁
表的元數據寫入.cfg文件(二進制)
2019-10-23T05:08:55.932286Z 8 [Note] InnoDB: Sync to disk of `test`.`z` started. 2019-10-23T05:08:55.932321Z 8 [Note] InnoDB: Stopping purge 2019-10-23T05:08:55.932409Z 8 [Note] InnoDB: Writing table metadata to './test/z.cfg' 2019-10-23T05:08:55.932513Z 8 [Note] InnoDB: Table `test`.`z` flushed to disk
4. 將.ibd和.cfg文件從源端傳至目標端
shell> scp /datadir/test/t.{ibd,cfg} dest-server:/datadir/test
5. 在源端釋放表鎖
mysql> UNLOCK TABLES;
自動刪除.cfg文件
釋放導入的表上的共享鎖,並重新啟動purge線程
2019-10-23T05:11:22.213323Z 8 [Note] InnoDB: Deleting the meta-data file './test/z.cfg' 2019-10-23T05:11:22.213354Z 8 [Note] InnoDB: Resuming purge
6. 在目標端導入表空間
mysql> ALTER TABLE t IMPORT TABLESPACE;
檢查每個表空間頁是否損壞
更新個頁面上的空間ID和日誌序列號(LSNs)
更新頁頭的flags和LSN
更新Btree頁
將頁狀態設置為dirty以便將其寫入磁盤
2019-10-23T05:20:31.107180Z 12 [Note] InnoDB: Importing tablespace for table 'test/z' that was exported from host 'test02' 2019-10-23T05:20:31.107307Z 12 [Note] InnoDB: Phase I - Update all pages 2019-10-23T05:20:31.107680Z 12 [Note] InnoDB: Sync to disk 2019-10-23T05:20:31.112604Z 12 [Note] InnoDB: Sync to disk - done! 2019-10-23T05:20:31.113861Z 12 [Note] InnoDB: Phase III - Flush changes to disk 2019-10-23T05:20:31.123091Z 12 [Note] InnoDB: Phase IV - Flush complete 2019-10-23T05:20:31.123297Z 12 [Note] InnoDB: `test`.`z` autoinc value set to 0
注:.cfg文件非必須,不存在的時候提示warnning
root@db 12:01: [test]> alter table t import tablespace; Query OK, 0 rows affected, 1 warning (0.04 sec) root@db 12:02: [test]> show warnings; +---------+------+----------------------------------------------------------------------------------------------------------------------------------------+ | Level | Code | Message | +---------+------+----------------------------------------------------------------------------------------------------------------------------------------+ | Warning | 1810 | InnoDB: IO Read error: (2, No such file or directory) Error opening './test/t.cfg', will attempt to import without schema verification | +---------+------+----------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)