MySQL道普請便り

第4回 テーブルをコピーするついでにデータディレクトリーの中身を知る

この記事を読むのに必要な時間:およそ 6 分

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つのテーブルをある時点に切り戻しても,他のテーブルが合わせて更新されていると矛盾が生じる)でしたが,これらを発展させて「データベース全体の一貫性を保ちつつ全てのテーブルをコピー」したものがすなわち本番環境でも使えるバックアップの手順になります。

著者プロフィール

yoku0825

GMOメディア株式会社のDBA。日本MySQLユーザ会員。主に地雷担当。主食はMySQLだがPercona Server,MariaDB,InfiniDBにMroongaストレージエンジンなど,mysqldでありさえすれば雑食。

Twitter:@yoku0825