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的環境。

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

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

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

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

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

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

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/23718752/viewspace-2135283/，如需轉載，請註明出處，否則將追究法律責任。