【MySQL入門】mysqldumpをほんの少し理解する

本記事はMySQL5.6を想定しており、他のバージョンの場合ではオプション使用時の動作が異なる可能性があります。

MySQLのバックアップ方法としてよく目にするのがmysqldumpを使ったバックアップです。
例えば全データベースのバックアップを取りたい場合は、以下のようになります。

$ mysqldump -uroot --all-databases > dump.sql

確かにこれでもバックアップを取ることは可能です。ですが、いくつかの問題も抱えています。
mysqldumpのメリットとデメリットを踏まえた上で、それらの問題を回避できるよう利用することが大切です。

mysqldumpのメリットとデメリット

mysqldumpによるバックアップは「論理バックアップ」です。
mysqldumpの出力はCREATEやINSERTなどのSQLが記述されたテキストデータになります。
そのため、内容の判別が可能で、sedコマンドなどを用いて修正することもできます。
反面、リストア時のINSERTの実行に時間がかかるというデメリットがあります。

また、MySQLサーバーを停止する必要がない（「オンラインバックアップ」や「ホットバックアップ」と呼ばれます）ことは大きなメリットですが、それはつまりmysqldump実行中もデータベースを更新されてしまう可能性があるということです。
このことを考慮しなければ、整合性の崩れたダンプファイルとなってしまうかもしれません。

整合性を保ったバックアップを取るために

整合性を保ったバックアップを取るためには、テーブルをロックして更新されることを防ぐ必要があります。
実はmysqldumpはデフォルトで --lock-tables オプションが有効になっており、各データベースに対して個別にテーブルをロックするようになっています。
正確には --opt という、いくつかのオプションをまとめたオプションがデフォルトで有効になっており、そのまとめられたオプションの中の1つに --lock-tables が含まれている形です。

では、それで十分かというとそうではありません。
--lock-tables は複数のデータベース内にあるテーブルを同時にロックできないため、データベースが異なるテーブルのダンプは整合性が崩れる場合があります。
これに対し、 --lock-all-tables オプションを使うことで、MySQLサーバー全体にロックをかけられるため、複数のデータベース内にあるテーブルでも整合性を保つことができます。

ロックの問題点と対応策

ロックをかければ整合性を保つことはできますが、別の問題が出てきます。
それは、mysqldump実行中に更新処理ができなくなってしまうということです。
これは、サービスによっては、mysqldump実行中のサービス利用が不可能になることを意味します。

この問題への対応策の1つは --single-transaction というオプションを使うことです。
ダンプ開始時のスナップショットをとり、そこからデータを取得するため、ロックをかけずにダンプを実行できます。
ただし、このオプションが使用できるのはストレージエンジンがInnoDBの場合です。
MyISAMを使用している場合はこのオプションは無効であるため --lock-all-tables によるロックが必要になります。

基本的にはデフォルトでInnoDBが有効になっていると思います。
テーブルごとにどのストレージエンジンが使われているかを確認する方法は以下を参照してください。

MySQLストレージエンジンの確認方法 - Tomcky's blog

"リストア"だけでなく"リカバリ"も意識したバックアップ

さて、ダンプしたファイルを使えば以下のようにしてリストアすることが可能です。

$ mysql -uroot < dump.sql

しかしこれでは、バックアップを取得した時点までしかデータを戻すことができません。
バックアップを取得してから障害が発生するまでの間のデータを復旧するには、リストアするだけでは不十分です。
このような場合、一般的にはバイナリログをリストア後に適用して障害発生直前の状態までリカバリします（ロールフォワードリカバリと言います）。

ただし、バイナリログには全ての更新情報が記録されているため、バックアップ時点のバイナリログの位置がわからなければ、どこからの更新情報を適用すれば良いか判断ができません。
そこで、 --master-data=2 というオプションを付与することで、mysqldump実行時のバイナリログ位置情報をダンプファイルに含めます。
本来はスレーブサーバー設定時に使用するダンプファイルを作成するときに付与するオプションになります。

ここではリカバリ方法は説明しませんが、このオプションを付与しておけば、いざリカバリしようと思ったときにバイナリログの正確な適用ができずに困った、という状況は避けられるはずです。

また、 --flush-logs オプションを使用するのも有効です。
このオプションを付与することでダンプ実行時にバイナリログのローテーションが行われ、適用すべき開始位置が新しいログファイルの先頭からになるため、ロールフォワードリカバリの作業が単純になります。
--flush-logs 使用時に気をつけないといけないのは、複数のデータベースをダンプする場合、データベースの数と同じ回数だけローテーションが行われてしまうことです。
これを回避するには、 --lock-all-tables か --master-data 、または --single-transaction を、一緒に利用します。

まとめ

ここまでの内容を踏まえたmysqldumpが以下になります。

InnoDBの場合

$ mysqldump -uroot --all-databases --single-transaction --flush-logs --master-data=2 > dump.sql

MyISAMの場合

$ mysqldump -uroot --all-databases --lock-all-tables --flush-logs --master-data=2 > dump.sql

これで、最初のコマンドよりはしっかりとバックアップを取れるようになったはずです。
なお、MySQL5.1.8以降だと --events オプションを付与しないとWarningが表示されます。

$ mysqldump -uroot --all-databases --single-transaction --flush-logs --master-data=2 --events > dump.sql

これでバッチリですね（長かった。。。）

エキスパートのためのMySQL[運用+管理]トラブルシューティングガイド

Webエンジニアのための データベース技術[実践]入門 (Software Design plus)