mysqlでのバックアップ／リカバリについて

調べたことをメモメモ

コールドバックアップ

コールドバックアップとは、インスタンス停止して行うバックアップのこと。

コールドバックアップの方法

データファイルをtarなどで固めてどこか別の場所に置いておくだけ。

コールドバックアップのリカバリ方法

データファイルをデータディレクトリに戻してリストア。そこからバイナリログを適用してロールフォワードリカバリ。

バイナリログとは

• トランザクションのログ。mysqlではバイナリ形式で格納されているためバイナリログと呼ばれる。
• バイナリログを確認するにはmysqlbinlogコマンドを使う。
  • バイナリログ中の#はコメント行。
  • バイナリログ中の#atから次の#atまでが一組。一組の単位をイベントと呼ぶ。
  • SET TIMESTAMPはSQL文を実行したときの現在時刻を設定。
  • SYSDATE文を使用したときに、バックアップ時とロールフォワードリカバリ時で値が変わってしまわないようにSET TIMESTAMPが使われている。
• バイナリログはデフォルトでは作成されない。作成するには、my.cnfにこんなふうに設定する

log-gin=/path/to/binary-log/prefix
sync_binlog=1

バイナリログのスイッチ

新しいファイル名は、拡張子の数字に+1されたものになる。つまり数字が大きい方が新しいログ。

FLUSH LOGS;

で明示的にバイナリログをスイッチできる

mysqladimin -u username -p flush-logs

こういう方法もある。

ロールフォワードリカバリ

ロールフォワードリカバリをするには、インスタンスを起動しておく必要がある。インスタンスをリモートからの接続禁止状態で起動するには以下のようにする。*1

mysqld_safe --defaults-file=/path/to/my.cnf --skip-networking &

インスタンス起動後、こんな感じでロールフォワードリカバリ。

mysqlbinlog --disable-log-bin backupfile1 backupfile2 ... > /backup/recover.sql
mysql -u username -p < /backup/recover.sql

disable-log-binを付けると、バイナリログの適用時にはバイナリログが作成されないようになる。通常、バイナリログ適用時にはバイナリログは作成しないようにする。

オンラインバックアップの方法

オンラインバックアップとは、インスタンスを起動している状態で行うバックアップのこと。いくつか方法あるみたいだけど、一番メジャーなmysqldumpでのバックアップについて調べた。

mysqldumpとは

• mysql標準のオンラインバックアップツール。
• バックアップデータはSQL文がそのまま格納されたテキスト形式

mysqldumpでのオンラインバックアップの方法

InnoDBを使っている場合は下記のようにしてバックアップファイルを作る。権限データベースはMyISAMテーブルなので、single-transactionとalldatabasesを指定しているときにはユーザの追加などはしない方がいいらしい。

mysqldump -u username -p --single-transaction --master-data=2 --flush-logs --hex-blob --default-character-set=cp932 --all-databases > /path/to/backup

mysqldumpオプション

single-transaction

ストレージエンジンがInnoDB以外の時は一貫性が失われる可能性があるので使わない方がいい。

master-data

=2を付けないとリスト時に支障のあるSQL分が実行されてしまうらしい。これもInnoDB以外のときは使わない。

flush-logs

バイナリログを明示的に切り替える。

default-character-set

省略時にはutf8になる。

hex-blob

シフトJIS系列の文字コード(sjis/cp932)を指定した場合には付けた方がいいらしい。

all-databases

全てのデータベースのバックアップを取るときに指定。特定のデータベースをバックアップしたいときには、代わりにデータベース名を書く。

MyISAMテーブルのバックアップ

MyISAMテーブルを適切にバックアップするには、バックアップの最中にデータベース全体に共有ロックをかけ、一切の更新をブロックする必要がある。
single-transactionとmaster-data=2の代わりに--lock-all-tablesを使い共有ロックをかける。

オンラインリカバリ

これだけでリストア。あとは上記で書いたようにバイナリログを適用するだけ。

mysql -u username -p < backupfile

特定のデータベースをリカバリする場合はこっち

mysql -u username -p databasename< backupfile

参考

現場で使える MySQL (DB Magazine SELECTION)

*1:defaults-fileの指定はいらないかも