MySQL :: MySQL 5.6 リファレンスマニュアル :: 14.5.5 テーブルスペースの別のサーバーへのコピー (トランスポータブルテーブルスペース)

MySQL 5.6 リファレンスマニュアル / ... / テーブルスペースの別のサーバーへのコピー (トランスポータブルテーブルスペース)

14.5.5 テーブルスペースの別のサーバーへのコピー (トランスポータブルテーブルスペース)

このセクションでは、Transportable Tablespace 機能を使用して、file-per-table テーブルスペース (.idb ファイル) をあるデータベースサーバーから別のデータベースサーバーにコピーする方法について説明します。

他の InnoDB テーブルコピー方式について詳しくは、セクション14.6.2「別のマシンへの InnoDB テーブルの移動またはコピー」を参照してください。

InnoDB file-per-table テーブルスペースを別のデータベースサーバーにコピーすることをお勧めするのには、多くの理由があります。

本番サーバーに余計な負荷を掛けずにレポートを実行するため。
新しいスレーブサーバーに、あるテーブルとまったく同じデータをセットアップするため。
問題や誤りが発生したあとに、テーブルのバックアップ版をリストアするため。
mysqldump コマンドの結果をインポートするよりも、データを移動させる方が速いため。データの再挿入とインデックスの再構築を行うよりも、データがすぐに使用できるためです。
システム要件により適したストレージ媒体を持つサーバーに file-per-table テーブルスペースを移動するため。たとえば、アクセス頻度の高いテーブルを SSD デバイスに置いたり、大規模なテーブルを大容量の HDD デバイスに置いたりする場合です。

テーブルスペースのコピーの制限と使用方法に関する注意 (トランスポータブルテーブルスペース)

テーブルスペースのコピー手順は、innodb_file_per_table が ON (MySQL 5.6.6 以降ではデフォルトです) に設定されている場合にのみ可能です。共有のシステムテーブルスペースにあるテーブルは休止できません。
テーブルが休止されると、影響を受けたテーブルでは読み取り専用のトランザクションのみが許可されます。
テーブルスペースをインポートする場合、ページサイズはインポートするインスタンスのページサイズに一致する必要があります。
DISCARD TABLESPACE がパーティション化されたテーブルでサポートされていないということは、トランスポータブルテーブルスペースも未サポートであることを意味します。パーティション化されたテーブルで ALTER TABLE ... DISCARD TABLESPACE を実行すると、次のエラーが返されます。ERROR 1031 (HY000): Table storage engine for 'part' doesn't have this option.
DISCARD TABLESPACE は、foreign_key_checks が 1 に設定されている親/子 (主キー/外部キー) 関係を持つテーブルスペースをサポートしていません。親子関係のテーブルのテーブルスペースを破棄する前に、foreign_key_checks=0 を設定します。
ALTER TABLE ... IMPORT TABLESPACE は、インポートされたデータに対して外部キー制約を課しません。テーブル間に外部キー制約がある場合、すべてのテーブルを同じ (論理上の) 時点でエクスポートしてください。
ALTER TABLE ... IMPORT TABLESPACE では、テーブルスペースをインポートするための .cfg メタデータファイルは必要ありません。ただし、.cfg ファイルなしでインポートした場合は、メタデータのチェックは実行されず、次に類似した警告が発行されます。
```
Message: 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)
```
.cfg ファイルなしでインポートする機能は、スキーマの不一致が予想されない場合に、使い勝手が高まる可能性があります。また、.cfg ファイルなしでインポートする機能は、メタデータが .ibd ファイルから収集できないクラッシュリカバリシナリオで役立つ可能性があります。
MySQL 5.6 以降では、両方のサーバーが GA (一般提供) ステータスであり、両者のバージョンが同じシリーズである場合に、テーブルスペースファイルの別のサーバーからのインポートが機能します。そうでないと、インポート先のサーバーにファイルが作成されていなければなりません。
レプリケーションシナリオでは、マスターとスレーブの両方で innodb_file_per_table が ON に設定されている必要があります。
Windows では、InnoDB はデータベース、テーブルスペース、およびテーブル名を内部的に小文字で格納します。Linux や UNIX など、大文字と小文字を区別するオペレーティングシステムでインポートの問題を回避するには、すべてのデータベース、テーブルスペース、およびテーブルを小文字名を使用して作成します。これを遂行する便利な方法は、データベース、テーブルスペース、またはテーブルを作成する前に、my.cnf または my.ini ファイルの [mysqld] セクションに次の行を追加することです。
```
[mysqld]
lower_case_table_names=1
```

手順の例: あるサーバーから別のサーバーへのテーブルスペースのコピー (トランスポータブルテーブルスペース)

この手順では、MySQL の実行中のサーバーインスタンスから実行中の別のインスタンスへテーブルをコピーする方法について説明します。同じ手順は、微調整を加えると、同じインスタンスでテーブルの完全なリストアを実行するために使用できます。

ソースサーバーで、テーブルがまだ存在していない場合、テーブルを作成します。
```
mysql> use test;
mysql> CREATE TABLE t(c1 INT) engine=InnoDB;
```
目的サーバーで、テーブルが存在していない場合、テーブルを作成します。
```
mysql> use test;
mysql> CREATE TABLE t(c1 INT) engine=InnoDB;
```
目的サーバーで、既存のテーブルスペースを破棄します。(テーブルスペースをインポートする前に、InnoDB は受け取り側のテーブルにアタッチされたテーブルスペースを破棄します。)
```
mysql> ALTER TABLE t DISCARD TABLESPACE;
```
ソースサーバーでは、FLUSH TABLES ... FOR EXPORT を実行してテーブルを休止し、.cfg メタデータファイルを作成します。
```
mysql> use test;
mysql> FLUSH TABLES t FOR EXPORT;
```
メタデータ (.cfg) ファイルは InnoDB データディレクトリに作成されます。

注記

FLUSH TABLES ... FOR EXPORT は MySQL 5.6.6 以降で使用できます。このステートメントは、サーバー稼働中にバイナリテーブルのコピーができるように、名前付きテーブルへの変更をディスクにフラッシュします。FLUSH TABLES ... FOR EXPORT が実行されると、InnoDB はテーブルと同じデータベースディレクトリに .cfg ファイルを作成します。.cfg ファイルには、テーブルスペースファイルをインポートするときのスキーマの検証に使用されるメタデータが含まれます。
.ibd ファイルおよび .cfg メタデータファイルをソースサーバーから目的サーバーにコピーします。例:
```
shell> scp /path/to/datadir/test/t.{ibd,cfg} destination-server:/path/to/datadir/test
```
注記

.ibd ファイルおよび .cfg ファイルは、次の手順で示すように、共有ロックを解放する前にコピーする必要があります。
ソースサーバーでは、UNLOCK TABLES を使用して、FLUSH TABLES ... FOR EXPORT によって取得されたロックを解放します。
```
mysql> use test;
mysql> UNLOCK TABLES;
```
目的サーバーで、テーブルスペースをインポートします。
```
mysql> use test;
mysql> ALTER TABLE t IMPORT TABLESPACE;
```
注記

ALTER TABLE ... IMPORT TABLESPACE 機能は、インポートされたデータに対して外部キー制約を課しません。テーブル間に外部キー制約がある場合、すべてのテーブルを同じ (論理上の) 時点でエクスポートしてください。この場合、テーブルの更新を停止し、すべてのトランザクションをコミットし、テーブルで共有ロックを取得してから、エクスポート操作を実行します。

テーブルスペースのコピーの内部情報 (トランスポータブルテーブルスペース)

次の情報では、トランスポータブルテーブルスペースのコピー手順に関する内部情報とエラーログについて説明します。

ALTER TABLE ... DISCARD TABLESPACE が目的のインスタンスで実行された場合。

テーブルは X モードでロックされています。
テーブルスペースがテーブルから切り離されています。

FLUSH TABLES ... FOR EXPORT がソースインスタンスで実行された場合。

エクスポートのためにフラッシュされたテーブルが共有モードでロックされています。
パージコーディネータのスレッドが停止しています。
ダーティーページがディスクに同期しています。
テーブルのメタデータがバイナリの .cfg ファイルに書き込まれました。

この操作で予想されるエラーログメッセージです。

2013-07-18 14:47:31 34471 [Note] InnoDB: Sync to disk of '"test"."t"' started.
2013-07-18 14:47:31 34471 [Note] InnoDB: Stopping purge
2013-07-18 14:47:31 34471 [Note] InnoDB: Writing table metadata to './test/t.cfg'
2013-07-18 14:47:31 34471 [Note] InnoDB: Table '"test"."t"' flushed to disk

UNLOCK TABLES がソースインスタンスで実行された場合。

バイナリの .cfg ファイルが削除されました。
インポートされたテーブル (または複数のテーブル) の共有ロックが解放され、パージコーディネータのスレッドが再起動されました。

この操作で予想されるエラーログメッセージです。

2013-07-18 15:01:40 34471 [Note] InnoDB: Deleting the meta-data file './test/t.cfg'
2013-07-18 15:01:40 34471 [Note] InnoDB: Resuming purge

ALTER TABLE ... IMPORT TABLESPACE が目的のインスタンスで実行されると、インポートのアルゴリズムはインポートされたテーブルスペースごとに次の操作を実行します。

テーブルスペースの各ページに破損があるかどうかをチェックします。
各ページのスペース ID とログシーケンス番号 (LSN) が更新されます。
フラグが検証され、ヘッダーページの LSN が更新されます。
B ツリーページが更新されます。
ページの状態がディスクに書き込まれるように、この状態をダーティーに設定します。

この操作で予想されるエラーログメッセージです。

2013-07-18 15:15:01 34960 [Note] InnoDB: Importing tablespace for table 'test/t' that was exported from host 'ubuntu'
2013-07-18 15:15:01 34960 [Note] InnoDB: Phase I - Update all pages
2013-07-18 15:15:01 34960 [Note] InnoDB: Sync to disk
2013-07-18 15:15:01 34960 [Note] InnoDB: Sync to disk - done!
2013-07-18 15:15:01 34960 [Note] InnoDB: Phase III - Flush changes to disk
2013-07-18 15:15:01 34960 [Note] InnoDB: Phase IV - Flush complete

注記

テーブルスペースが破棄されたこと (目的のテーブルのテーブルスペースを破棄した場合) を伝える警告、および .ibd ファイルがないために統計値が計算できなかったことを伝えるメッセージも受け取る場合があります。

2013-07-18 15:14:38 34960 [Warning] InnoDB: Table "test"."t" tablespace is set as discarded.
2013-07-18 15:14:38 7f34d9a37700 InnoDB: cannot calculate statistics for table "test"."t" because the .ibd file is missing. For help, please refer to 
http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting.html

PREV HOME UP NEXT

User Comments

Posted by Valerii Kravchuk on July 9, 2013

Note (you can read about it at http://mysqlblog.fivefarmers.com/2012/11/07/smarter-innodb-transportable-tablespace-management-operations/) that for a long time already, since http://bugs.mysql.com/bug.php?id=66715 is fixed in 5.6.8, .cfg file is not necessary at least for some (not clearly identified) cases. Indeed, recent MySQL 5.6.x versions will import just .ibd, assuming its "clean" and table really has the same structure.

Posted by Ikechukwu Umejiofor on October 16, 2013

Given a Server A and an Innodb table(t500) symlinked thus:

CREATE TABLE `t500` (
`id` int(11) NOT NULL,
`c` char(20) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 DATA DIRECTORY='/var/lib/mysqlw/';

Server A mysql datadir=/var/lib/mysql/data

And you want to import this innodb table on server B but on a different DATA DIRECTORY clause option thus

so on server B you do:

CREATE TABLE `t500` (
`id` int(11) NOT NULL,
`c` char(20) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 DATA DIRECTORY='/var/lib/mysqla/';

Server B mysql datadir=/var/lib/mysql/data

import of t500 from Server A to B works just fine.

However, trying to import this same table from server A with definition

CREATE TABLE `t500` (
`id` int(11) NOT NULL,
`c` char(20) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 DATA DIRECTORY='/var/lib/mysqlw/';

to server B with definition:

CREATE TABLE `t500` (
`id` int(11) NOT NULL,
`c` char(20) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ;

will fail with the following errors:

ERROR 1808 (HY000): Schema mismatch (Table flags don't match, server table has 0x6 and the meta-data file has 0x41)

...I guess a symlinked innodb table from source should also be symlinked on import on the destination server!!!

By the way am using mysql 5.6.10 community version.

Posted by Ruben Cardenal on October 10, 2014

You'll want to issue an analyze after importing, in order to notify the data dictionary about the new indexes for that table. Otherwise, they won't be used and its cardinality will be reported as 0. At least as of 5.6.20.