MySQLのデータバックアップなら専用コマンドのmysqldumpを

MySQLで管理しているデータベースのバックアップに使われる、mysqldumpコマンドの使い方について解説します。なお、mysqldumpコマンドは、MySQLをインストールすると使えるようになるクライアントコマンドの一つで、ターミナルなどから実行できます。

mysqldumpを使ったバックアップとリストアの基本

MySQLが管理するデータベースにデータを格納すると、特殊なフォーマットのファイルを作成しますが、そのファイルをそのままコピーしたとしても、データベースを使って復旧したり、別のサーバーに移植したりはできません。MySQLでリストアできる形式でバックアップを作っておく必要があります。なお、バックアップを作る方法は幾つかありますが、よく利用されるのは専用個コマンドのmysqldumpです。

mysqldumpコマンドの詳細を解説する前に、このコマンドを使ったMySQLにおけるバックアップとリストアの基本を説明します。

SQL形式でファイルに書き出す

MySQLでは、データベースやテーブルを新規に作ったり、そこにデータを格納するのにSQLを使います。つまり、既にあるMySQLのデータは、それを作れるSQLさえ保存しておけば、いつでも作り直せる訳です。そして、mysqldumpコマンドは、MySQLに格納されたデータベースから、それを作るためのSQLをファイルに書き出すコマンドです。そのため、mysqldumpコマンドで作成されたSQLは、MySQLデータベースのバックアップに用いられます。

ただし、ファイルを単にコピーするのと違い、データベースを解析して、SQLというテキストファイルに変換してファイルに書き出していることから、処理に時間がかかります。特に大規模なデータは、途中、データの書き換えが発生すると、データの整合性が崩れるといった不具合も発生しかねません。そのため、MySQLで大規模なデータベースを管理している場合は、データの整合性が保証される有償のEnterprise サブスクリプションに含まれるコマンドの利用が推奨されています。

mysqldumpコマンドの実行方法

今回紹介するmysqldumpコマンドは、LinuxやmacOSならターミナルで、また、Windowsならコマンドプロンプトから実行します。なお、このコマンドは、ターミナルなどからキーボードから一文字ずつ打ち込んでも実行できますが、多くの場合はバッチファイル内に組み込み、時刻指定で実行されています。

また、mysqldumpコマンドにはたくさんのオプションが用意されているので、それらを組み合わせて、MySQLの実行環境に合わせたバックアップ処理が可能です。なお、そういったオプションは、時刻指定で実行するバッチファイルの中でもよく利用されています。

mysqldumpのオプション指定方法

$ mysqldump
Usage: mysqldump [OPTIONS] database [tables]
OR     mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
OR     mysqldump [OPTIONS] --all-databases [OPTIONS]
For more options, use mysqldump --help

上記にあるように、オプションの詳細は、mysqldumpコマンドに「–help」オプションを付けて実行することでも表示できます。

なおMySQLのデーモンが実行中のサーバーでバックアップする場合、通常、下記のように使用します。なお、オプションの使い方については、後から詳しく解説します。

$ mysqldump -uuser -ppasswd db1 > backup.sql

mysqldumpコマンドで書き出したファイルのリストア

mysqldumpコマンドで書き出したファイルはSQL形式のため、そのままMySQLのクライアントで実行させれば、元のデータベースを復元可能です。
backup.sqlをリストアするコマンドの例

> mysql -uuser -ppassword < backup.sql

なお、mysqlコマンドで指定するアカウントには、リストアで生成されるデータベースに対する権限が必要です。そのため、リストアする前にMySQLの基本的な設定を完了させてから実行してください。また、この処理はSQLを解析してデータベースを作っているので、データベースが大きい場合はかなり時間が
かかるものだと考えてください。

mysqldumpコマンドのオプションは数が多い

先ほど、mysqldumpコマンドの実行方法を簡単に紹介しましたが、このコマンドにはたくさんのオプションが用意されており、–helpオプションで表示されるメッセージだけでは、どう使えば良いかが解りません。予め、下記のマニュアルサイトなどでオプションの機能をチェックしておくようにしましょう。

MySQL :: MySQL 5.6 リファレンスマニュアル :: 12.5 文字列関数

次から、たくさんあるオプションの中から、注目してほしいオプションについて紹介します。

最低限必要なアカウントの指定

MySQLで管理しているデータベースにアクセスするために、最低限必要となるのがアカウントです。そして通常は、-u オプションでアカウント名、そして、-pオプションでパスワードを指定します。

次の例は、アカウント名がuser、パスワードがpasswordというアカウントで、db1というデータベースをbackup.sqlというファイルに書き出す例です。

db1のバックアップ実行例

$ mysqldump -u user -p password db1 > backup.sql

また、データベース全体をバックアップするには、データベース名ではなく、–all-databases オプションを指定します。

データベース全体のバックアップ実行例

$ mysqldump -u user -p password --all-databases > backup.sql

このようにデータベース全体をバックアップするとかなり時間がかかることから、サービスが動作中の場合は、バックアップ中にデータが変更され、データベースの整合性が失われる可能性があります。そのため、バックアップ中は、サービスを停止したり、次に解説する整合性を保つためのオプションを追加して使います。

整合性を保つオプション

Webサービスで利用しているデータベースなど、24時間サービスを提供しているシステムでは、バックアップするからといってサービスを停める訳にはいきません。しかし、バックアップ中にデータベースが書き換わってしまうと、データベースの整合性が失われてしまい、リストアできなくなります。そのため、通常、mysqldumpコマンドを使ってバックアップするためには、整合性を保つためのオプションを指定します。なお、整合性を保つためのオプションは、–single-transactionと–lock-all-tablesの2つです。

2つあるのオプションの一つ目の–single-transactionオプションは、バックアップ中の整合性を補償するオプションで、データベースを指定してバックアップする際に使われます。

整合性を考慮した個別バックアップの実行例

$ mysqldump --single-transaction -u user -p password db1 > backup.sql

一方、–lock-all-tablesは、全てのテーブルにロックをかけて読み書きできなくする機能です。そのため、全てのデータベースを対象とするオプションの-Aといっしょに使われます。なお、このオプションを指定してバックアップを実行する場合は、Webサービスも制限を受けるので、エラーにならないような工夫が必要です。

整合性を考慮した全体バックアップの実行例

$ mysqldump --lock-all-tables -u user -p password -A > backup.sql

リモートで実行するためのオプション

データベース専用のサーバーを設定しているため、バックアップをネットワークを介して別のサーバーから実行するケースもあります。そのような場合でも、mysqldumpの利用が可能です。それは、mysqldumpには、MySQLが稼動しているホスト名を指定するオプションが用意されているからです。

mysqldumpのホスト名を指定するオプション

なお、-h オプションを省略すると、mysqldumpを実行したホストのMySQLが対象になります。また、ホスト名のほかに、iPアドレスを指定することも可能です。さらに、セキュリティ対策として、MySQLにアクセスするポートをデフォルトから変更している場合でも、–port オプションまたは-Pオプションでポート番号を指定できます。

このほかに、ネットワークを介して実行する場合、経路の途中でデータベースの内容を読み取られる可能性があるため、機密性の高いデータを扱う場合、SSLで暗号化する-sslオプションもあります。なお、SSLの使用については、MySQLのサーバー側の設定が必要です。暗号化して通信する場合は、必ずサーバー管理者と
相談してください。

リモートでバックアップする例

$ mysqldump --lock-all-tables -uuser -ppassword -h mysql.hosstname -P 13306 -ssl db1 > backup.sql

mysqldumpの使用例

先ほどもご紹介したようにmysqldumpは、人がターミナルで打ち込んで実行するよりも、バッチファイルに組み込んで時刻指定で実行されるコマンドです。次から、そういったmysqldumpを使ったバックアップ用途での使用例をご紹介します。

データを圧縮して転送する方法

mysqldumpを使ってリモートでバックアップを取る際、大量のデータがネットワークに流れることで、通信に遅延が生じ、バックアップに時間がかかったり、レスポンスが悪くなるなどサービスに影響することもあります。その場合、データを圧縮して転送すれば改善しますが、mysqldumpにはデータを圧縮する機能はありません。そこで、よく使われるのが、Linuxのシェルの機能とgzipなどの圧縮コマンドを組み合わせた使い方です。

具体的には、シェルのパイプ機能を使い、mysqldumpの出力を圧縮して、さらにsshコマンドでリモート保存先のサーバーに転送する方法です。

バックアップデータを圧縮して転送する例

mysqldump --lock-all-tables -u user -p password db1 | gzip | ssh backuphost 'zcat > /saved/backup.sql'

この例では、Linuxの圧縮コマンドのgzipと、暗号化してリモート転送するssh、さらに、gzipの圧縮を解凍するzcatを使っています。もちろん他のコマンドを組み合わせても実現でき、Windowsサーバーでも実現可能です。

処理の結果をメールで通知するには

時刻指定でMySQLのデータをバックアップする方法では、自動で実行するから人は何もしなくても良い、という訳ではありません。もし、処理が正常に終了しなかった場合は、原因を調べて正常に動作するように修復する必要があります。そこで、mysqldumpの処理結果を判断し、もし、不具合が発生していたらメールなどで管理者に知らせる処理を組み込むのが一般的です。やり方はいろいろありますが、シェルで組む方法が簡単なので、その例をご紹介します。

#!/bin/bash
echo "mysqldump execute Start"
mysqldump --single-transaction --user=www --password=www-www wordpress > log
if [ $? = 0 ]; then
	echo "Backup success"
else
	echo "Error !!"
fi

mysqldumpは、処理に成功した場合に0を、また、失敗した場合にその理由に対応した1や2といった数字を返します。上の例ではコマンド実行後の戻り値を使って、if文で分岐し、成功した場合のメッセージを、または失敗した場合はエラーメッセージを標準出力に表示します。

MySQLを管理するならmysqldumpをマスターしよう

MySQLに限らず、データベースのバックアップは重要です。何かがあった後では遅いので、確実にバックアップする仕組みを作りましょう。そして、MySQLでバックアップする仕組みを作るのに使われるのが、これまで紹介したmysqldumpです。MySQLを管理する立場の方は、このコマンドを使い方をマスターしましょう。