MySQLにおけるテーブルスペースの移行によるデータ移動

目次- MySQLにおけるテーブルスペースの移行によるデータ移動

背景
環境構築
移行手順
- 1. ソーステーブルの準備
- 2. .ibd、.cfg、.cfpファイルをdatabase1からdatabase2へコピー
- 3. database1でテーブルのロック解除
- 4. database2でテーブルスキーマの作成
- 5. .ibdファイルの削除
- 6. テーブルスペースをデータディレクトリにコピー
- 7. テーブルスペースのインポート
まとめ

背景

通常、InnoDBテーブルをあるMySQLインスタンスから別のインスタンスへ移動または複製する必要が生じることがあります。この目的には多くの方法があります。5.6以前では、主に物理的または論理的なバックアップを用いて実現されていました。しかし、MySQL 5.6.6以降では、テーブルスペース単位での高速移行機能（OracleのTTSと類似）が導入され、直接的にテーブルスペースを別のサーバーにコピーすることが可能になりました。これは特に大規模なテーブルに対して非常に効率的です。この方法は、従来のエクスポート・インポート手法よりも高速であり、Linuxの標準コマンド（cp、scp、rsync）を使用してデータファイルをターゲットにコピーするだけで済みます。

テーブルスペースの移行は、メジャーバージョンが異なる場合でも可能です。

環境構築

database1とdatabase2という2つのデータベースサーバーを準備
両方のサーバーでMySQL 5.7.32を稼働させている
両方のデータベースは静的暗号化が適用されている
database1にある暗号化テーブル「test.enc_data_test」をdatabase2に移行する

▼▼▼
mysql> select @@version, @@version_comment\G
*************************** 1. row ***************************
@@version: 5.7.32-log
@@version_comment: MySQL Community Server (GPL)
1 row in set (0.00 sec)

mysql> show create table test.enc_data_test\G
*************************** 1. row ***************************    
Table: enc_data_test
Create Table: CREATE TABLE `enc_data_test` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(16) DEFAULT NULL, `exec_time` datetime DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=165520 DEFAULT CHARSET=utf8 ENCRYPTION='Y'
1 row in set (0.00 sec)

移行手順

1. ソーステーブルの準備

enc_data_testテーブルの変更内容がディスクに反映されていることを確認し、サーバー実行中にテーブルのコピーを作成するために、database1上で以下のコマンドを実行します。

▼▼▼
mysql> flush table enc_data_test for export;Query OK, 0 rows affected (0.00 sec)

このコマンド実行後、MySQLデータディレクトリ内に追加のファイル（.cfg および .cfp）が生成されます。

▼▼▼
-rw-r----- 1 mysql mysql 61 Jun 17 09:24 db.opt
-rw-r----- 1 mysql mysql 454 Jun 17 09:43 enc_data_test.cfg
-rw-r----- 1 mysql mysql 100 Jun 17 09:43 enc_data_test.cfp
-rw-r----- 1 mysql mysql 8626 Jun 17 09:37 enc_data_test.frm
-rw-r----- 1 mysql mysql 98304 Jun 17 09:43 enc_data_test.ibd

2. .ibd、.cfg、.cfpファイルをdatabase1からdatabase2へコピー

ステップ1を完了した後、テーブルファイル（.ibd、.cfg、.cfp）をターゲットサーバーdatabase2に転送します。

▼▼▼
[mysql@db01 test]$ scp -r enc_data_test.ibd enc_data_test.cfp enc_data_test.cfg 192.168.0.22:/home/mysqlmysql@192.168.0.22's password:enc_data_test.ibd 100% 96KB 6.9MB/s 00:00enc_data_test.cfp 100% 100 37.5KB/s 00:00enc_data_test.cfg 100% 454 148.5KB/s 00:00

3. database1でテーブルのロック解除

テーブルファイルをターゲットサーバー(database2)にコピーした後、database1でテーブルのロックを解除し、操作を再開できるようにします。

▼▼▼
mysql> unlock tables;Query OK, 0 rows affected (0.00 sec)

4. database2でテーブルスキーマの作成

ターゲットデータベースdatabase2で空のテーブルを作成します。

▼▼▼
mysql> CREATE TABLE `enc_data_test` (  -> `id` int(11) NOT NULL AUTO_INCREMENT,  -> `name` varchar(16) DEFAULT NULL,  -> `exec_time` datetime DEFAULT CURRENT_TIMESTAMP,  -> PRIMARY KEY (`id`)  -> ) ENGINE=InnoDB AUTO_INCREMENT=165520 DEFAULT CHARSET=utf8 ENCRYPTION='Y';
Query OK, 0 rows affected (0.02 sec)

暗号化属性（ENCRYPTION='Y'）が正しく設定されていることを確認してください。もし設定されていない場合、インポート時に以下のエラーが発生します。

▼▼▼
mysql> alter table enc_data_test import tablespace;
ERROR 1808 (HY000): Schema mismatch (Encryption attribute in the file does not match the dictionary.)

5. .ibdファイルの削除

database2で空のテーブルを作成すると、.frm と .ibd ファイルが生成されます。このうち .ibd ファイルは削除し、database1からテーブルスペースをコピーする準備をしてください。

▼▼▼
[mysql@db02 test]$ ll
total 112
-rw-r----- 1 mysql mysql 61 Jun 17 09:24 db.opt
-rw-r----- 1 mysql mysql 8626 Jun 17 09:53 enc_data_test.frm
-rw-r----- 1 mysql mysql 98304 Jun 17 09:53 enc_data_test.ibd
mysql> alter table enc_data_test discard tablespace;
Query OK, 0 rows affected (0.13 sec)
[mysql@db02 test]$ ls -lrth
total 16K
-rw-r----- 1 mysql mysql 61 Jun 17 09:24 db.opt
-rw-r----- 1 mysql mysql 8.5K Jun 17 09:53 enc_data_test.frm

6. テーブルスペースをデータディレクトリにコピー

テーブルスペースファイル（database1から）をデータディレクトリにコピーします。

▼▼▼
[mysql@db02 test]$ cp -r /home/mysql/enc_data_test.ibd ./
[mysql@db02 test]$ cp -r /home/mysql/enc_data_test.cf* ./
[mysql@db02 test]$ ls -lrth
total 120K
-rw-r----- 1 mysql mysql 61 Jun 17 09:24 db.opt
-rw-r----- 1 mysql mysql 8.5K Jun 17 09:53 enc_data_test.frm
-rw-r----- 1 mysql mysql 96K Jun 17 10:01 enc_data_test.ibd
-rw-r----- 1 mysql mysql 100 Jun 17 10:01 enc_data_test.cfp
-rw-r----- 1 mysql mysql 454 Jun 17 10:01 enc_data_test.cfg

.cfpファイルが存在することを確認してください。もし存在しない場合、インポート処理が失敗し、以下のエラーが表示されます。

▼▼▼
mysql> alter table enc_data_test import tablespace;ERROR 1808 (HY000): Schema mismatch (Table is in an encrypted tablespace, but the encryption meta-data file cannot be found while importing.)

7. テーブルスペースのインポート

ターゲットデータベースで以下のコマンドを実行してインポートを行います。

▼▼▼
mysql> alter table enc_data_test import tablespace;
Query OK, 0 rows affected (0.02 sec)
mysql> select count(*)from enc_data_test;
+----------+| count(*) |+----
------+| 1000 |+----------+
1 row in set (0.00 sec)

このプロセスは通常のInnoDBテーブルスペースのエクスポート／インポートと似ていますが、以下の2点に注意が必要です：

.cfpファイルをターゲットサーバーにコピーする必要があります。
移行対象のテーブルは暗号化されている必要があります（ENCRYPTION = Y）。

まとめ

実際の運用環境において、特定の大容量テーブルを移行する際、業務停止時間が短縮できることを目的としています。従来のmysqldumpによるエクスポート・インポートでは効率が悪いため、テーブルスペースの移行を利用することで処理時間を短縮し、業務要件を満たすことが可能です。

タグ: MySQL InnoDB テーブルスペースデータ移行暗号化

6月28日 00:05 投稿

異端開発室

MySQLにおけるテーブルスペースの移行によるデータ移動