勉強用のMySQLをいじっている時に、「⁠もとに戻せない操作しちゃったけど戻せない……」ということはありませんか？　MySQLのDDL（DROP TABLEやALTER TABLEなどの、定義を変更するためのステートメントのことです）はトランザクション非対応なので、一度DROPしてしまうとロールバックすることはできません。それどころか、DDLが実行される直前に「暗黙のコミット」が走る作りになっているので、トランザクションの最中にDDLを実行してしまうと、そこまでの操作はコミットされてしまうのです。そしてデフォルトでオートコミット……うっかりの種はどこにでも転がっています。

今回は、「⁠勉強用のMySQLのテーブルをコピーしてバックアップする」「⁠ついでにデータディレクトリーの構造を勉強する」がテーマです。

デモンストレーション環境について

今回利用している環境は、第2回　MySQLにはじめてのデータを入れてみるで利用したものを「そのまま」利用しています。第2回で登場したテーブルの名前が出てきますので、今回のコマンドをコピー＆ペーストで試す際には第2回のコマンドも一通り実行しておいてください。

いくつかのテーブルをコピーする方法

まずはテーブルをコピーしてみましょう。といっても、ここでは「本番環境で求められるような一貫性の取れたバックアップ」の話はしません。トラフィックが全く流れてこない状態で、さくっとテーブルをコピーするためのいくつかの方法です。

CREATE TABLE .. LIKEとINSERT INTO .. SELECTを利用したコピー

mysql> use vmstat
mysql> CREATE TABLE vmstat_bak LIKE vmstat;
mysql> INSERT INTO vmstat_bak SELECT * FROM vmstat;

CREATE TABLE vmstat_bak LIKE vmstatは「vmstatテーブルと同じカラム定義とテーブル属性でvmstat_bakというテーブルを作る」という意味のステートメントです。同じカラム定義とテーブル属性を持っているvmstat_bakテーブルに、INSERT INTO vmstat_bak SELECT * FROM vmstatとすることで、「vmstatテーブルから全ての行の全てのカラムをSELECTした結果をvmstat_bakテーブルにINSERT」し、vmstat_bakテーブルはvmstatテーブルの（その時点での）完全なコピーになります。切り戻す場合はRENAME TABLE vmstat TO vmstat_old, vmstat_bak TO vmstatステートメントを実行することで、定義情報、データ全てがコピー取得時の状態に戻せます。筆者はこの方法を好んで使いますが、時にはもう少し良い方法があったりもします。

CREATE TABLE .. AS SELECT ..を利用したコピー

mysql> CREATE TABLE vmstat_bak_2 AS SELECT * FROM vmstat;

CREATE TABLE vmstat_bak2 AS SELECTクエリーは、「SELECTクエリーの結果をvmstat_bak_2テーブルとして固定化する」という意味のステートメントです。1ステートメントで済むのでこちらの方が簡単ですか？　このステートメントは、「SELECTクエリーの結果を固定化する」ものなので、「SELECT結果に含まれない情報」はvmstat_bak_2テーブルにはコピーされません。端的には、オートインクリメントの値やインデックスはテーブルの属性であってクエリーの結果には含まれないので、コピーされません。SHOW CREATE TABLEステートメントでテーブル定義を確認してみると、コピー先のテーブルにはPRIMARY KEYが定義されていないことが確認できます。

mysql> SHOW CREATE TABLE vmstat\G
mysql> SHOW CREATE TABLE vmstat_bak_2\G

このテーブルの属性情報はコピーされないというのは良し悪しで、「テーブル定義を変更するのでコピーを取っておこう」という用途には使えません。ただし、「テーブル定義は変更せず、データを削除したり更新したりするだけ」であればこのコピーには問題はありません。また、インデックスは存在しないほうがテーブルへのデータの挿入は高速なため、「単にデータだけで良いコピー」が欲しい場合はこちらのほうが高速にコピー可能です。このコピーからデータを切り戻す場合は、TRUNCATE TABLE vmstatステートメントで一度vmstatテーブルの中身を空にしてから、INSERT INTO vmstat SELECT * FROM vmstat_bak_2ステートメントで行を全てコピーしなおします。

mysqldumpコマンドを利用してコピー

本当はこれはテーブルの「コピー」ではなく、「バックアップ」に類するものです。mysqldumpコマンドは論理バックアップを取得するための、MySQL付属のコマンドラインクライアントです。引数としてデータベース名（vmstatデータベース）とテーブル名（vmstatテーブル）を渡すことで、指定したテーブルの論理バックアップを取得できます。

mysqldumpコマンドはバックアップを標準出力に出力してしまうので、リダイレクトでファイルに出力させ、その中身を見てみてください。CREATE TABLEステートメントとINSERT INTOステートメントが並んでいるのが確認できます。このように、「そのテーブルと同じものをもう一度作るために必要なSQL」を出力してくれるのがmysqldumpコマンドです。mysqlコマンドの標準出力にsqlファイルを与えるか、mysqlコマンドからsource vmstat.sqlとすることで、vmstatテーブルを新しく作成することができます（この方法の場合、別のテーブル名でリストアするには、sqlファイルを編集する必要があります）。

$ mysqldump vmstat vmstat > vmstat.sql
$ less vmstat.sql
$ mysql vmstat < vmstat.sql

MySQLを停止してcpコマンドでコピー

MySQLの全てのデータは（デフォルトのまま変更していなければ）全てdatadirと呼ばれるディレクトリーの中に格納されています（yumでMySQLをインストールした場合、datadirは/var/lib/mysqlに設定されています）。であれば、datadirをまるごとコピーして、何らかの変更を加えた後にコピーしておいたdatadirを戻せばリストアできるのではないか……？ それは正解ですが、いくつか注意点があります（mysqldumpと同じく、これは「バックアップ」であって「コピー」ではありませんね）。

一つ目は、datadirはまるごとコピーしなければならないこと。datadirに含まれるファイルはテーブルに格納されているデータだけではなく、トランザクションを制御するためのファイルやテーブルの定義を保存しているファイルなど、いくつもの種類のファイルがあります。このため、「欲しいのはテーブルのコピーなので、欲しいテーブルの定義ファイルとデータファイルだけコピーすればいい」という訳にはいきません（MyISAMストレージエンジンはトランザクションをサポートしないのでこれが出来たりするんですが、混乱のもとになるので触れません）。常にデータベース全体をコピーすることになります。1つのテーブルだけ切り戻したい時でも、データベース全体を切り戻さないといけなくなります。

二つ目は、MySQLを停止してコピーしなければならないこと。そのテーブルに対して（あるいは、データベース全体に対して）更新が無ければ、そのままコピーを取っても良さそうな気がしますが、MySQLはトラフィックがなくてもバックグラウンドで処理をしており、データファイルを読み書きしています。そのため、一見cpコマンドは問題なく終了しているように見えても、datadirとしては不完全な状態となり、切り戻した場合にMySQLが起動しなくなることがあります。

この2つの点を押えておけば、cpコマンドによるコピーはとても簡単です。切り戻しの時も同様に、MySQLを停止後、データディレクトリーをコピーしなおせばOKです。

$ sudo service mysqld stop
$ sudo cp -ipr /var/lib/mysql /var/lib/mysql.bak
$ sudo service mysqld start

FLUSH TABLES .. FOR EXPORTを利用したコピー

これはMySQL 5.6から導入された新機能で、かつInnoDB限定、innodb_file_per_tables= 1が必要です。更にシェルの操作も必要とするので、少なくとも練習用のコピーに使う用途には不向きでしょう。この方法でコピーするには、まずコピー先のテーブルを作って、ALTER TABLE .. DISCARD TABLESPACEというステートメントでコピー先テーブルのデータファイルを削除しないといけません。

mysql> CREATE TABLE vmstat_bak_3 LIKE vmstat;
mysql> ALTER TABLE vmstat_bak_3 DISCARD TABLESPACE;
mysql> FLUSH TABLE vmstat FOR EXPORT;

このステートメントを実行したあと、そのターミナルを開いたまま、別のターミナルを開いてシェルから操作します。

$ cd /var/lib/mysql
$ cd vmstat
$ cp -ip vmstat.cfg vmstat_bak_3.cfg
$ cp -ip vmstat.ibd vmstat_bak_3.ibd

ここまでコピーしたら、もとのターミナルで続きのステートメントを実行します。

mysql> UNLOCK TABLES;
mysql> ALTER TABLE vmstat_bak_3 IMPORT TABLESPACE;

これでコピーができました。SHOW CREATE TABLEやSELECTでvmstat_bak_3テーブルがvmstatテーブルと同じものになっていることが確認できます。でも、何をしてるんだか全然わかりませんね。このコマンドで何が起こっているのかを知るためには、まずMySQLのdatadirの中にどんなものが入っているのかを知っていく必要があります。

MySQLのdatadirの中には何が入っているか

さて、繰り返しになりますが、今回のデモ環境はMySQLの公式リポジトリーからyumコマンドでインストールしてありますので、datadirは/var/lib/mysqlに設定されています。まずは単純にlsコマンドでどんなファイルがあるかを見てみましょう。

$ cd /var/lib/mysql
$ ls -l
total 176152
-rw-rw---- 1 mysql mysql       56 Oct  2 19:05 auto.cnf
-rw-rw---- 1 mysql mysql 79691776 Oct  2 19:34 ibdata1
-rw-rw---- 1 mysql mysql 50331648 Oct  2 19:34 ib_logfile0
-rw-rw---- 1 mysql mysql 50331648 Oct  2 19:34 ib_logfile1
drwx------ 2 mysql mysql     4096 Oct  2 19:05 ls
drwx------ 2 mysql mysql     4096 Oct  2 19:05 mysql
srwxrwxrwx 1 mysql mysql        0 Oct  2 19:05 mysql.sock
drwx------ 2 mysql mysql     4096 Oct  2 19:05 performance_schema
drwx------ 2 mysql mysql     4096 Oct  2 19:34 vmstat
drwx------ 2 mysql mysql     4096 Oct  2 19:08 zipcode

auto.cnf

MySQL 5.6で追加されたファイルです。MySQLサーバを一意に識別するためのserver-uuidが記録されています。datadirの初期化時（具体的には、mysql_install_dbコマンドが実行された時）にこのファイルが存在しなければ作成され、以降はファイルに記録されたserver-uuidを利用するようになります（単なるテキストファイルですので、興味があればcatコマンドなどで中身を確認してみてください⁠）⁠。GTIDレプリケーション関連で利用されています。

ibdata1

InnoDBで利用する「共有（またはシステム）テーブルスペースファイル」です。InnoDBの内部情報（どのデータベースのどのテーブルのデータはどのibdファイルに格納されているか、など）とトランザクション制御のためのメタデータ情報、非同期でセカンダリーインデックスを更新するためのバッファなどが含まれます。また、MySQL 5.5以前でinnodb_file_per_tablesを有効にしていない場合、InnoDBテーブルのインデックスとデータもこのファイルに格納されます。

ib_logfile0, ib_logfile1

InnoDBで利用する「ログファイル」です。ログと言っても人間が読むためのログではなく、InnoDBがトランザクションの状況を記録するためのログ（いわゆるWrite Ahead Logging……WALファイル）ですので、直接読むことはできません。InnoDBは共有テーブルスペースファイル、ログファイル、テーブルスペースファイルの間で整合性が取れていない場合、起動することができません。うっかり消してしまうことのないように注意してください（そんな馬鹿な……と思われるかも知れませんが、「⁠大きなログファイルがあったから消したら起動しなくなった」という話がまれにあります⁠）⁠。

mysql.sock

MySQLサーバーにソケット接続するためのソケットファイルです（Windowsにはソケット接続が存在しないため、このファイルは存在しません⁠）⁠。削除してしまうとMySQLサーバーにソケット接続できなくなり、TCPでのみ接続可能になります。skip_name_resolveが有効になっている環境では、ソケットファイル経由の接続（"localhost"として扱われます）とTCP経由でのローカルホストへの接続（"127.0.0.1"として扱われます）は別のユーザーアカウントとして認識されます。

ls、mysql、performance_schema、vmstat、zipcodeディレクトリー

ls、vmstat、zipcodeは第2回で作成したデータベースの名前です（CREATE DATABASEステートメントで作成しています⁠）⁠。mysqlはMySQLの（主に）ユーザ管理用のテーブルが格納されているデータベースで、datadirの初期化時に作成されています。performance_schemaはMySQLのパフォーマンス統計を格納するためのデータベースで、これもやはりdatadirの初期化時に作成されています。datadirに直接置かれた「ディレクトリー」はMySQL上で「データベース」として認識されます（あるいは逆に、MySQL上で「データベース」として扱っているものはファイルシステム上では「ディレクトリー」として扱われます⁠）⁠。

試しに新しいデータベースをCREATE DATABASEステートメントで作成してみます。

mysql> CREATE DATABASE michibushin_4th;
Query OK, 1 row affected (0.02 sec)

$ ls -ld michibushin_4th
drwx------ 2 mysql mysql 4096 Oct  5 16:31 michibushin_4th

このように、新しくディレクトリーが作成されました。逆に、/var/lib/mysqlの下にシェルからmkdirコマンドでディレクトリを作ってみても、

$ mkdir mkdir_from_shell

mysql> SHOW DATABASES LIKE 'mkdir%';
+-------------------+
| Database (mkdir%) |
+-------------------+
| mkdir_from_shell  |
+-------------------+
1 row in set (0.00 sec)

このようにMySQL上では「データベース」として認識されます（もちろん、useコマンドで移動することができますし、CREATE TABLEでテーブルを作ることも可能です⁠）⁠。

勘の良い方は、「⁠SHOW DATABASEステートメントの結果にはinformation_schemaというデータベースがあるのに、ディレクトリーはないの？」ということに気付かれるかも知れません。information_schemaはMySQL内部の情報にSQLインターフェースでアクセスするための疑似テーブルであり、その実体は存在しません（SELECTされる都度、MySQLの内部の情報を検索し、値は一切格納しません。どのテーブルにどのような値が返るかはMySQLのコード内部に埋め込まれています⁠）⁠。

更にこの中のzipcodeディレクトリーの中身をのぞいてみましょう。

$ ls -l zipcode/
total 28796
-rw-rw---- 1 mysql mysql       67 Oct  2 19:07 db.opt
-rw-rw---- 1 mysql mysql     8624 Oct  2 19:08 prefecture_kana.frm
-rw-rw---- 1 mysql mysql    98304 Oct  2 19:08 prefecture_kana.ibd
-rw-rw---- 1 mysql mysql     8824 Oct  2 19:08 zipcode.frm
-rw-rw---- 1 mysql mysql 29360128 Oct  2 19:09 zipcode.ibd

このzipcodeディレクトリー（=データベース）の中には大きく、db.optファイル、frmファイル、ibdファイルがあります（MyISAMを利用している環境ではibdファイルの代わりにMYDファイルとMYIファイルになります⁠）⁠。

このうち、db.optファイルは「データベースの定義情報」（⁠CREATE DATABASEステートメントで指定した属性）を保存したファイルです。これはただのテキストファイルですので、やはり興味があればcatコマンドなどで中を見ることができます。

frmファイルは「テーブル定義情報」（⁠CREATE TABLEステートメントで指定した属性やカラムの名前、インデックスなど）を保存したファイルですが、直接読むことはできません（無理矢理catコマンドでなどで開くと、カラム名などそれっぽい文字列を見ることはできます⁠）⁠。

ibdファイルはInnoDBテーブルのデータが格納されるファイルです。単に（⁠「⁠共有」や「システム」のつかない）「⁠テーブルスペースファイル」と呼ばれます。テーブルとインデックスの中身が記録されており、InnoDBログファイルや共有テーブルスペースによってデータの書き込み状況が管理されています（そのため、このibdファイルだけをコピーしても、InnoDBログファイルや共有テーブルスペースとの整合性が取れないためテーブルとして認識することができません。また、コピーの場合だけでなくrmコマンドなどでこのファイルを削除してしまった場合も、やはりInnoDBログファイルや共有テーブルスペースとの整合性が壊れますので、MySQL上から正しく扱うことができなくなります⁠）⁠。

ibdファイルはInnoDBストレージエンジンの利用時のみ存在し、ストレージエンジンごとに違ったファイルが出来ます（MyISAMならMYDとMYI、ARCHIVEならARZ、CSVならCSMとCSV、など⁠）⁠。

「FLUSH TABLES .. FOR EXPORTを利用したコピー」でコピーしたファイルはこのibdファイルでした（一緒にコピーしていたcfgファイルは、FLUSH TABLES .. FOR EXPORTを実行した時にのみ作成され、普段は存在しません⁠）⁠。

まずCREATE TABLEステートメントでコピー先のテーブルを先に作りました。この時点で、frmファイルとibdファイルが作成されています。

ALTER TABLE .. DISCARD TABLESPACEステートメントは、「⁠このテーブルに紐づくibdファイルを削除する」というステートメントです。これにより新しく作ったテーブルからibdファイルが消去され、frmファイルだけが残ります。

FLUSH TABLES .. FOR EXPORTステートメントは、「⁠このテーブルのibdファイルをコピーしたいので、コピー先のサーバーで共有テーブルスペースやログファイルを協調させるための情報（これがcfgファイルの正体です）を作成してibdファイルをコピー可能な状態にしてくれ」というステートメントです。これはmysqlコマンドを終了するとロックが解除されcfgファイルも削除されてしまうため、別のターミナルを用意する必要がありました。

その後cpコマンドでibdファイルとcfgファイルをコピー先テーブルの名前に合わせ、ALTER TABLE .. IMPORT TABLESPACEステートメントを実行します。「⁠frmファイルとcfgファイル、ibdファイルが置いてあるので、上手くインポートしてくれ」というステートメントです。これによりMySQL内部でInnoDBの主ファイルと整合性を取りつつ、新しいibdファイルをテーブルとして認識できるようになります。

まとめ

駆け足にですが、今回は色々なテーブルのコピー方法と、データディレクトリーの中身について少し説明しました。今回のテーブルコピーの方法は（InnoDBの内部的な整合性は必要でしたが）データベース全体の整合性を意識したものはありません（1つのテーブルをある時点に切り戻しても、他のテーブルが合わせて更新されていると矛盾が生じる）でしたが、これらを発展させて「データベース全体の一貫性を保ちつつ全てのテーブルをコピー」したものがすなわち本番環境でも使えるバックアップの手順になります。