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

  在MySQL中如果要遷移一個表導另外一個服務器/環境中，常規的做法就是使用備份工具備份，比如mysqldump，然后拷貝備份到目標服務器或者環境導入。如果某一個表數據量很大，導出dump文件很大的情況下，使用導出導入工具其實會花費不少的時間.

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

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

>show variables like '%per_table%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_file_per_table | ON    |
+-----------------------+-------+
1 row in set (0.00 sec)

我們做一個有代表意義的測試，比如把某一個表從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的環境。

首先需要在目標端創建相應的空表。然后使用如下的語句把數據文件截斷。

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操作。

> alter table users discard tablespace;
Query OK, 0 rows affected (0.00 sec)可見整個過程是非常快的，執行完成之后，我們檢查一下表的情況。[test]> check table users;
+------------+-------+----------+----------+
| Table      | Op    | Msg_type | Msg_text |
+------------+-------+----------+----------+
| test.users | check | status   | OK       |
+------------+-------+----------+----------+
1 row in set (0.16 sec)查看表的數據進行驗證。

[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，主要是在表壓縮和行的動態格式上有所改變。更詳細的內容可以參考：

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

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