在MySQL中如果要遷移一個表導另外一個伺服器/環境中，常規的做法就是使用備份工具備份，比如mysqldump，然後複製備份到目標伺服器或者環境匯入。如果某一個表資料量很大，匯出dump檔案很大的情況下，使用匯出匯入工具其實會花費不少的時間.

怎麼樣提高效率呢，可以有一種想法就是直接複製資料檔案到目標環境，當然在早期版本中這麼做是不可取的，因為會有很多關聯資料在ibdata中，InnoDB的資料存在對應的資料字典資訊，是存放在共享表空間中，無法直接剝離出來，而在5.6/5.7中，就推出了一個很不錯的特性，就是遷移表空間，可以把這個配置資訊剝離出來，簡單來說就是把資料檔案直接複製到目標環境，在目標端掛載即可。

這樣一個操作的一個基本前提是使用了獨立表空間,開啟innodb_file_per_table.

跨版本傳輸表空間-源端操作

我們做一個有代表意義的測試，比如把某一個表從MySQL 5.6環境遷移到MySQL 5.7環境中。

我們選擇一個表users作為測試所用，資料量在2萬條左右。資料檔案情況：

-rw-rw---- 1 mysql mysql 8602 Feb 13 23:10 users.frm
-rw-rw---- 1 mysql mysql 11534336 Mar 12 22:55 users.ibd資料情況：

> select count(*) from users;
+----------+
| count(*) |
+----------+
| 20001 |
+----------+
1 row in set (0.01 sec)我們開始遷移資料，首先要生成一個cfg檔案，匯出配置資訊。

>flush tables users for export;
Query OK, 0 rows affected (0.00 sec)

這個命令值得一提的是，保持當前的視窗，不要關閉，如果關閉，cfg檔案就會自動刪除，可以看到命令執行後生成了cfg檔案。

-rw-rw---- 1 mysql mysql 599 Mar 13 08:17 users.cfg
-rw-rw---- 1 mysql mysql 8602 Feb 13 23:10 users.frm
-rw-rw---- 1 mysql mysql 11534336 Mar 12 22:55 users.ibd在flush table之後，這個表users就被鎖定了，DML操作是阻塞的，也就意味著遷移的過程中，是無法直接寫入資料的。

>insert into users values(20234312310,'aa');
ERROR 1099 (HY000): Table 'users' was locked with a READ lock and can't be updated表users的定義資訊如下，可以使用show create table users或者mysqldump --no-date test users這種方式得到。

DROP TABLE IF EXISTS `users`;
CREATE TABLE `users` (
`userid` int(11) unsigned NOT NULL,
`username` varchar(64) DEFAULT NULL,
PRIMARY KEY (`userid`),
KEY `username` (`username`),
KEY `idx_users` (`userid`,`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;而cfg檔案的格式有一些明顯的差別,可以透過strings一窺其中的概要資訊。

# strings users.cfg
mbionline.test.com
test/users
userid
        username
DB_ROW_ID
DB_TRX_ID
DB_ROLL_PTR
PRIMARY
userid
DB_TRX_ID
DB_ROLL_PTR
        username
        username
        username
userid
idx_users
userid
        username完成之後推出會話，設定unlock tables即可。

跨版本傳輸表空間-目標端操作

目標端的操作非常關鍵，目標端是MySQL 5.7的環境。

首先需要在目標端建立相應的空表。然後使用如下的語句把資料檔案截斷。

> alter table users discard tablespace;
Query OK, 0 rows affected (0.02 sec)手工複製資料檔案.ibd和配置檔案.cfg,複製到指定的目錄下即可。

cp /tmp/users.cfg /home/mysql/test
cp /tmp/users.ibd /home/mysql/test這個時候尤其需要注意檔案的許可權，複製完成，我們就可以透過import tablespace來進行資料檔案掛載。

> alter table users import tablespace;
ERROR 1808 (HY000): Schema mismatch (Table flags don't match, server table has 0x5 and the meta-data file has 0x1)或者下面的錯誤：

> alter table users import tablespace;
ERROR 1808 (HY000): Schema mismatch (Table has ROW_TYPE_DYNAMIC row format, .ibd file has ROW_TYPE_COMPACT row format.)透過錯誤資訊可以發現和表的一個屬性有關。我們先解決問題，新增屬性row_format

CREATE TABLE `users` (
`userid` int(11) unsigned NOT NULL,
`username` varchar(64) DEFAULT NULL,
PRIMARY KEY (`userid`),
KEY `username` (`username`),
KEY `idx_users` (`userid`,`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 row_format=compact;然後繼續嘗試Import tablespace操作。

[test]> select count(*)from users;
+----------+
| count(*) |
+----------+
| 20001 |
+----------+
1 row in set (0.00 sec)這樣遷移的過程就告一段落，我們很順利的把一個表從MySQL 5.6遷移到了5.7環境中。

回到剛剛碰到的問題，為什麼在5.6遷移至5.7會有報錯。

> alter table users import tablespace;
ERROR 1808 (HY000): Schema mismatch (Table has ROW_TYPE_DYNAMIC row format, .ibd file has ROW_TYPE_COMPACT row format.)原因就是Innodb_file_format在5.6中是Antelope，在MySQL 5.7中是Barracuda，主要是在表壓縮和行的動態格式上有所改變。更詳細的內容可以參考：

https://dev.mysql.com/doc/relnotes/mysql/5.7/en/news-5-7-7.html
小結

其實這個特性在Oracle中已經有耳熟能詳的的方案，TTS,支援跨平臺，轉換位元組順序，甚至可以支援基於增量備份的遷移方案，MySQL中的遷移方式和Oracle傳統的TTS有些相似。當然上面的操作還可以使用Percona的工具innobackupex 來完成，我們下一篇來進行演示。

怎麼能夠形象的表達這種遷移的感覺呢，我連超市裡看葡萄酒都能看成 read write。

MySQL傳輸表空間小結(r12筆記第2天)

相關文章