mysql中表空間傳輸的範例分析

說明：MySQL(5.6.6以上)，innodb_file_per_table開啟。

1.1. 操作步驟：

0. 目標伺服器建立相同表格結構
1. 目的伺服器： ALTER TABLE t DISCARD TABLESPACE;
2. 來源伺服器： FLUSH TABLES t FOR EXPORT;
3. 從來源伺服器上拷貝t.ibd, t.cfg檔案到目的伺服器
4. 來源伺服器： UNLOCK TABLES;
5. 目的伺服器： ALTER TABLE t IMPORT TABLESPACE;

1.2. 示範
將多重實例的[mysql5711] 中burn_test 函式庫下的test_purge表，傳送到[mysql57112]中burn_test2 函式庫下的test_purge表

1.2.1. 準備工作

1. 在目標伺服器上建立表格空間

-- 來源伺服器[mysql5711]

mysql> select * from burn_test.test_purge;
---- ------
| a | b |
---- ------
| 1 | 10 |
| 3 | 30 |
| 4 | 40 |
| 5 | 50 |
| 6 | 60 |
| 7 | 70 |
| 8 | 80 |
| 10 | 100 |
---- -- ----
8 rows in set (0.01 sec)

-- 目標伺服器[mysql57112]
--
-- test_purge在目標伺服器上不存在，先建立該表
mysql> CREATE TABLE `test_purge` (
`a` int(11) NOT NULL AUTO_INCREMENT,
`b` int(11) DEFAULT NULL,
PRIMARY KEY (`a`),
UNIQUE KEY `b` (`b`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4;
Query OK, 0 rows affected (0.16 sec)

2. OK, 建立完成後來檢查

## 目標伺服器

[root@MyServer burn_test_2]> ll | grep test_purge
-rw-r-----. 1 mysql mysql 8578 Mar 21 10:31 test_purge.frm # 表結構
-rw-r-----. 1 mysql mysql 57344 Mar 21 10:31 test_purge.ibd # 表空間，需要透過DISCARD 將表空間檔案刪除
ALTER TABLE test_purge DISCARD TABLESPACE; 的意思是保留 test_purge.frm 文件，刪除 test_purge.ibd

3.通闢discard 刪除ibd檔案

#-- 目標伺服器

#my>> ; alter table test_purge discard tablespace;
Query OK, 0 rows affected (0.04 sec)
mysql> show tables;
------------------- ----
| Tables_in_burn_test_2 |
-----------------------
| test_backup1 |
| test_purge |
-----------------------
2 rows in set (0.00 sec)
mysql> select * from test_purge;
ERROR 1814 (HY000): Tablespace has been discarded for table 'test_purge'
[root@MyServer burn_test_2]> ll | grep test_purge
-rw-r-----. 1 mysql mysql 8578 Mar 21 10:31 test_purge.frm

1.2.2. 匯出表空間
1. 在來源伺服器上，通闢export 指令匯出表空間（同時加讀鎖定）

-- 來源伺服器

mysql> flush table test_purge for export; -- 其實是對這個表格加上讀鎖定
Query OK, 0 rows affected (0.00 sec)
2. 將會匯出的cfg檔和ibd檔， 拷貝到目標伺服器的資料庫下

## 來源伺服器

#[root@MyServer burn_test]> ll | grep test_purge
-rw-r-----. 1 mysql mysql 462 Mar 21 10:58 test_purge.cfg # export後，多出來的文件，裡面保存了一些元資料資訊
-rw-r-----. 1 mysql mysql 8578 Mar 4 15:41 test_purge.frm
-rw-r-----. 1 mysql mysql 57344 Mar 5 15:28 test_purge.ibd
[root@MyServer burn_test]> cp test_purge.cfg test_purge.ibd data/my_sql_sql /5.7.11_2/burn_test_2/ # 拷貝表空間和cfg文件，遠端請使用scp（本地多實例演示，這裡的庫名是不同的）
3. 匯出表空間後，盡快解鎖

#-- 來源伺服器

mysql> unlock tables; -- 盡快的解鎖
#Query OK, 0 rows affected (0.00 sec)
注意：一定要先拷貝cfg和ibd檔案，然後才能unlock，因為unlock 的時候，cfg檔會被刪除
# 來源伺服器上的日誌
[Note] InnoDB: Stopping purge # 其實stop purge，找測試的表for export 即可
[Note ] InnoDB: Writing table metadata to './burn_test/test_purge.cfg'
[Note] InnoDB: Table `burn_test`.`test_purge` flushed to disk
[Note] InnoDB: Deleting the meta-data file ' ./burn_test/test_purge.cfg' # unlock table後，該檔案自動被刪除
[Note] InnoDB: Resuming purge # unlock後，恢復purge執行緒
4. 在目標伺服器上修改cfg檔案和ibd檔案的權限

## 目標伺服器

#[root@MyServer burn_test_2]> chown mysql.mysql test_purge.cfg test_purge.ibd
5. 在目標伺服器上通闢 import 指令匯入表空間
-- 目標伺服器
--
mysql> alter table test_purge import tablespace; -- 匯入表空間
Query OK, 0 rows affected (0.24 sec)
mysql> select * from test_purge; -- 可以讀取到從來源伺服器拷貝過來的資料
---- ------
| a | b |
---- ----- -
| 1 | 10 |
| 3 | 30 |
| 4 | 40 |
| 5 | 50 |
| 6 | 60 |
| 7 | 70 |
| 8 | 80 |
| 10 | 100 |
---- ------
8 rows in set (0.00 sec)

# error.log中出現的資訊
InnoDB: Importing tablespace for table 'burn_test/test_purge' that was exported from host 'MyServer'

注意：
表的名稱必須相同 ，經過上述測試，庫名可以不同
該方法也可以用於分區表的備份和還原

以上是mysql中表空間傳輸的範例分析的詳細內容。更多資訊請關注PHP中文網其他相關文章！