41から始めました

文字通り41歳から始めたブログです。DB(MySQL)を使ってお仕事してるので、DB周りの話を中心に最近始めたこととかをTwitterのノリで書いています。なお、本サイトにおいて示されている見解は私個人の見解であり、所属団体や組織を代表するものではありません。

MySQLでのテーブルコピー（InnoDBとMyISAM）

MySQL

MySQLでのテーブルコピーを考える

MySQLでテーブルコピー（同一インスタンス内とか別インスタンスとかあるけど）って意外とテストとかちょっとしたリカバリ作業用にしたくなりますよね。

というわけで、InnoDBとMyISAM関連だけですが書き出してみました。

コピーパターン

僕がパッと思いつく限りだとこの辺ですが、後から追記する可能性あり〼。

  • Mysql Enterprise Backup（MEB）
  • mysqldump/mysqlpump
  • INSERT...SELECT
  • CREATE TABLE...SELECT
  • OSファイルコピー
  • トランスポータブルテーブルスペース(TTS)
  • MEB + TTS
  • IMPORT TABLE
  • その他

MEB（InnoDB,MyISAM）

MySQL Enterprise Backup と呼ばれる商用の製品です。

以下、ドキュメントからの転記です

  • 実行中の MySQL データベース (InnoDB および MyISAM テーブルを含む) を、データベースの整合性のあるスナップショットを生成しながら、操作の中断を最小限に抑えてバックアップできます。
  • MySQL Enterprise Backup が InnoDB テーブルをコピーしている間は、InnoDB テーブルと MyISAM テーブルの両方に対する読み取りと書き込みを続行できます。
  • MyISAM およびその他の InnoDB 以外のテーブルのコピー中は、これらのテーブルに対する (書き込みではなく) 読み取りが許可されます。
  • さらに、MySQL Enterprise Backup では、圧縮バックアップファイルを作成したり、InnoDB テーブルのサブセットをバックアップしたりすることもできます。
  • MySQL のバイナリログと組み合わせると、ポイントインタイムリカバリを実行できます。

ユーザーガイドはこちらですが、EE版を契約した人には使わない手は無い代物だと思います。

mysqldump/mysqlpump（InnoDB,MyISAM）

皆さんご存じmysqldumpとあまりご存じじゃないmysqlpumpですｗ

mysqlpumpだけの機能だとか、mysqldumpとの違いなんかは既に僕ら大好きyoku0825さんがまとめてくださってるので多くは語らず。

あえて言うなら、mysqlpumpはMySQL5.7以降で使いましょう。

そして、mysqldumpと比べると新しい機能なのでまだまだ枯れてない部分もありそうです。

こういう話もありますので、最新を使いましょうね！

INSERT...SELECT（InnoDB,MyISAM）

create tableして、INSERT...SELECTする、こちらもおなじみのやり方ですね。

create tableして、csv,tsv形式のファイルを作ってload dataコマンドやmysqlimportコマンドという方法なら並列化できていいかもしれません。

CREATE TABLE...SELECT（InnoDB,MyISAM）

CREATE TABLE...SELECTもちょっとしたコピーテーブル作るのには便利ですよね。

でも、インデックスや制約を自動で作らないので、以下のような書き方でインデックスや制約は追加してあげましょう。

mysql> CREATE TABLE t2 (PRIMARY KEY (a),INDEX (b),foreign key f1 (a) references t1(a)) SELECT * FROM t1;

AUTO_INCREMENTあたりも保持しないので気を付けましょう。

OSファイルコピー（InnoDB,MyISAM）

これは2パターンある。MySQLを停止するパターンと停止しないで行うパターンです。

停止パターン

ドキュメントに例があります。

Not停止パターン

停止パターンとほぼ同じですがshutdownをしないので、メモリやらバイナリログにデータが残ってる可能性があるのでフラッシュやsync作業が必要かも。

https://dev.mysql.com/doc/refman/8.0/en/flush.html

トランスポータブルテーブルスペース（InnoDB）

InnoDBだとTTSが便利かつ高速。

ドキュメントの手順だと別のサーバーへのコピーとなっているが、同じインスタンス内の別スキーマでもOK。

注意点としては外部キーは持てないので、必要なら後から適用する必要があるところ。

MEB + TTS（InnoDB）

MEBのオプションとして先ほど挙げたTTSがバックアップとして使えるというものです。

詳しく知りたい方はドキュメントを参照してください。

IMPORT TABLE（MyISAM）

ここまではMySQL5.6以降であれば使えました。（え？5.5？もう僕は知らない子ですねぇ…）

そして、MySQL8.0では"IMPORT TABLE"というのができました。

まあ、MySQL8.0ではMyISAM自体非推奨であり、Perconaのブログでは

”MySQL 8.0: The end of MyISAM”

なんてタイトルの話があったりして、今更じゃね？という気もしないではないんですが…。

IMPORT TABLEはドキュメントを読むと以下の通りです。

  • MyISAMのテーブルをシリアル化された辞書情報（.sdi）メタデータファイルに含まれる情報に基づいてインポートします。
  • .sdiを読むためのIMPORT TABLEコマンドにはFILE権限とテーブルを作成するためのCREATE権限が必要です。
  • mysqldumpでエクスポートして、mysqlでインポートする方法よりも高速です。

参照：13.2.5 IMPORT TABLE Statement

作業手順としてはOSファイルコピーと似たような感じですが、んーーーー、使うメリットが今のところわかってません（苦笑）

その他

サードパーティ製のツールを使う方法です。

GUI、CUI色々ありますが、有名なのはPercona社のPercona XtraBackupですね。

xtrabackupのオプションの--tablesオプションを使えば対象テーブルのみをコピーしてくれるようです。

Percona XtraBackupの使い方については北川さんの記事がとても分かりやすいですが、他にも色々記事があるので使う場合はググって読んでみるといいです。

まとめ

MySQLでテーブルコピーに使えそうなものを雑に挙げてみました。

速度で行くとOSファイルコピーは最強でしょうね。ただ、権限的なものが強くなりますし、基本的には同一データベース名にコピーするものなので、開発者が気軽にできないところが難点でしょうか。

開発者が使うなら時間はかかりますが、mysqldumpやSQLでできるようなものがいいでしょう。

あとはTTSはそこまで有名じゃないですが、非常に便利だと思います。気になった方は試してみてはいかがでしょうか。

あ、そういえばロックの話全然書いてなかった…(;^_^A