― Web Technology and Life ―

mysqldump+bin-logによるInnoDBのbackupの設定とPerl実装スクリプト

2013-08-29
開発者のためのmysqldumpに関するTipsを書いた勢いそのままで、慣れないmysqlのバックアップについてもまとめておきたいと思います。あんまりやらないので、久々にやろうとするとなんだっけ??ってなるので・・・。

MySQLのInnoDBバックアップの基本

今回は手法のmysqldumpとbin-logのスコープを覚書しておきたいと思います。

まず、バックアップの手法は以下の三つの軸で分類できると思います。

  • 量(フルバックアップ/増分バックアップ)
  • タイミング(リアルタイム/バッチ)
  • 場所(サーバー外/サーバー内)

mysqlのデータファイルの破損というリスクだけに備えるなら、場所は関係ありませんが、基本的にハードディスクがやられるリスクや天災によるリスクに備えるために、サーバー外へのデータの非難は必須事項となると思うので、「量」と「タイミング」によって、分類できます。

mysqldump

mysqldumpはバックアップの基本なので誰しも知っていると思うくらい汎用的なのがメリットです。復旧楽だし。

tr>
msqldump フルバックアップ 差分バックアップ
バッチ ×
リアルタイム × ×

欠点は以下の通りです。

  • リアルタイムでない
  • データ量が増えてくるだるくなってくるのでいつかは卒業
  • 後述するようにオプションが意外と気を使う

bin-log

bin-logによる書き出しデータをバックアップするのは、差分バックアップのほぼ一択的な手法で大変重宝します。

bin-log フルバックアップ 差分バックアップ
バッチ ×
リアルタイム ×

欠点は以下の通りです。

  • 一部しかバックアップできないので復旧が面倒
  • リアルタイムに書き出すとちょっとmysqlが遅くなる
  • リアルタイムに外部に転送することはできない

(やっぱり、外部転送も軸に入れた8次元マップを立体で表したい・・・)

レプリケーション

レプリケーションをバックアップとして利用するケースが多く、以下のようにリアルタイムであるがゆえに、障害対応時に活用しやすく重宝されますが、逆に重要な欠点は、リアルタイムであるがゆえに、「手が滑って・・・」というエンジニアやディレクターのリスクに対応できないということだと思います。また、レプリケーション遅延も見過ごされがちなようですが、このご時世やったことがないのでよくわかりませn。。。

レプリケーション フルバックアップ 差分バックアップ
バッチ × ×
リアルタイム

若干それますが、あまりに対応するのもどうかと思いますが、ディレクターや顧客が「あのデータ手が滑って削除にしちゃったんだけど、やっぱり戻せない??」っていうのを無視するのはどうかと思いますし、開発時とか運用当初はいまいち要件も定まっていないことが多いはずなので、そういう観点からもしっかりそのほかの手法も組み合わせるべきですよね?

実際の組み合わせ

運用状況に応じて以下のように組み合わせるのが一般的ではないでしょうか?

  • [Web型]mysqldump系 + bin-log + レプリケーション
  • [Web型]mysqldump系 + レプリケーション
  • [どうでもいい系,α版]mysqldump系 + bin-log
  • [バッチシステム]mysqldump系

バッチの場合は楽ですね。で、今回はたいしてお金ないんだけど、まぁしっかりバックアップっぽいことをやりたいケースへの対応として最低限の「mysqldump + bin-log」がスコープとなるわけですね。まぁ、レプリケーションは実践したことないんde・・・

mysqldump+bin-logの組み合わせ用の具体的な設定

柔軟に更新されるDBでなけば、普通に、「mysqldump --all-databases > ./backup.dump」で十分ですね。

ただ、Webとか毎秒更新されるし、トランザクションとか入っていると、トランザクション中のデータの一部しかのこってなかったら、バックアップの価値半減するよな、とか想定すると、めんどくさいすぐ忘れるオプションたちとか設定が必要になるということです。

以下の設定は、ubuntuのmysql5.1,5.5のdebパッケージインストールのものでしか実装していないので、他のサーバーをご利用の方はご注意を

バイナリログ(bin-log)書き出しとmy.cnfでの設定と意味

まず、bin-logへの書き出しについて、関連するmy.cnfへの設定と合わせてメモ。

log-bin               =mysql-bin
expire_logs_days      = 9
max_binlog_size       = 500M
sync-binlog           =1

ざっとこんな感じ。とりあえず、これだけで、bin-logが/var/lib/mysqlとかに書き出され始めて、同一サーバー内へのバックアップができるし、後述するmysqldumpによるトランザクションも保証したバックアップができるようになる。

log-bin=mysql-bin

bin-logの書き出しを有効にするオプション。後述する、ホットバックアップ用のmysqldumpのオプション--master-dataを利用するためにも必要。

expire_logs_days

書き出したbin-logの保存期間。mysqldumpするタイミングに合わせて設定したりするといいのかな?

max_binlog_size

bin-logの最大ファイルサイズ。これを超えると、ローテイトされる。あまりおおきくないほうがいいと思うけど、どうなんでしょ?

sync-binlog

bin-logを書き出すときに同時に、InnoDBの実体データにも書き込むかどうかの設定。デフォルトは0で、書き込まないになっていて、ちょっと早い。レプリケーションとっていたり、する場合は0でもいいのかも(よくわからんけど・・・)ですが、mysqldumpとbin-logの組み合わせだけの場合は、確実にInnoDBファイルの破損リスクヘッジとして、同一ハードディスク内での完全なバックアップをとるという意味で、1にすべきだと思います。

mysqldumpの実行

トランザクションデータも保証しつつ瞬時にバックアップをとるには以下のオプションを指定してmysqldumpを行う。

mysqldump 
    --user=root 
    --events 
    --all-databases 
    --opt 
    --flush-logs 
    --single-transaction 
    --master-data=2
 | gzip > ./mysqldump.gz

ユーザー、パス以外のオプションなど説明を以下の通り。

--events

--all-databasesと組み合わせた時に、このオプションを指定しないと、以下のような変な警告がでる。詳細不明、忘れた(苦笑)情報求ム。

-- Warning: Skipping the data of table mysql.event. Specify the --events option explicitly.

--all-databases

すべてのデータベースをdumpするという設定。mysqlデータベースとかもろもうざいものはいらないという場合はサービスに関係するDBだけ指定してもいいけど、bin-logとのからみがめんどいので、あくまでバックアップということで全部取る。

--opt

以下のオプションを一括指定できるショートカットオプション。5.1以降デフォルトで有効なので気にしなくてもいいかも。

  • --add-drop-table
  • --add-locks
  • --create-options
  • --disable-keys
  • --extended-insert
  • --lock-tables
  • --quick
  • --set-charset

--flush-log

mysqldumpと同時に、bin-logをローテーションしてあとで、リカバリしやすくしてくれるすぐれたオプション。

--single-transaction

トランザクションデータも保証してくれる素晴らしいオプション。これをつけないと昨今のmysqldumpを使用したバックアップに依存しているサービスはすべて死亡する?!

--master-data=2

msyqldumpのデータで復旧させたあと、bin-logのどのファイルの何行目から復旧させればいいかdumpファイルに書き込んでくれるすぐれたオプション。なぜ「2」とかは、こちらの記事の「--master-dataオプションについて」というセクションにだらだら書いたのでご参考に。

| gzip > ./mysqldump.gz

ファイルサイズ大きくなると外部転送がだるいので、なんらかの方法で圧縮しておくべしということで、gzip圧縮。

bin-logの外部へのバックアップ

レッドハット系だとどうか知りませんが、bin-logはroot権限が必要だったりするので、以下のような手順でバックアップする必要があります。

  1. mysqladmin -uroot flush-logs(どこまでバックアップしたかわかりやすいようにローテーションしておく)
  2. sudo cp /var/lib/mysql/mysql-bin.xxxx ./
  3. sudo chmod 666 ./mysql-bin.xxxx
  4. scp target_host:mysql-bin.xxxx ./

復旧

復旧できてこそのバックアップなので、バックアップはじめたら、適度なところで、別のサーバーで復旧のテストをしておきましょうというのが一番重要らしいです。

どれかのmysqlの本には、mysqldump -d -uroot mydbname > ./hoge.dumpとかしたメチャクチャバックアップが早くなった!!んだけど、いざ復旧しようとしたときに死亡するなんて事例もあるって書いてあって、マジで震えますね。。。

実際手順は簡単で、同一サーバー内で処理するなら、以下の通りです。

  1. gzip -dc ./mysqldump.gz | mysql -uroot
  2. gzip -dc mysqldump.gz | less(-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000059', MASTER_LOG_POS=106;みたいなのを発見)
  3. sudo mysqlbinlog --start-position=106 /var/lib/mysql/mysql-bin.000059 /var/lib/mysql/mysql-bin.000060 /var/lib/mysql/mysql-bin.000061 | mysql -uroot

サーバー外に飛ばしたやつ戻してごにょごにょとかすればいいので、そんなに変わらないと思います。(3で/var/lib/mysql/にないやつはsudoいらないっす)

gzipで圧縮しているファイルがたくさんあるときの一括解凍ワンライナーサンプル

  • ls -1 | xargs -n 1 gzip -d
  • find -type f -name "*.gz" | xargs -n 1 gzip -d

ごにょごにょしようとしたら、自分よくわからないので、調べました。。。

id:rx7さんの複数のtarファイルを一気に展開するを見ました。だいたい、この手のときにググるとid:rx7さんの記事出てきてマジいつも感謝です!

Perlによる実装

かなりのクソコードだけど、クイックハック的にですね(苦笑)、公開しておきます。

https://github.com/hirobanex/p5-mysql-backup-script/blob/master/script.pl

注意点としては、

  • plenvでPerl 5.18.0で実装
  • .ssh/configにhost名を登録しておく
  • sudoはパスワードなし実行
  • ubuntuがベース
  • cronで毎日実行
  • データ量が増えると使えるなくなるから実行時間をログっておいてある

とかですね。実際に使えるコードというよりは、細かいその他のTipsを盛り込んでいるので、参考までにーという感じと、こんな感じで何かほかにすることありますかね?って意味で晒しておきます。

ホントは細かいところを書きたかったですが、も気力が。。。

終わりに ~一歩先のバックアップ~

実際は、bin-logの外部バックアップというのはもうあまり必要なくて、MySQLバックアップ頂上決戦!! LVMスナップショット vs InnoDB Hot Backupなどに載っている、Xtra Backupは今時代実用されているので、こちらがホントはオススメのようです。

ただ、一般的な書籍では、まだbin-logやmysqldumpの説明が多くてXtra Backupの実践的な使い方の紹介が少なく(というかぼくは見たことない)、kindleとかで探せばいいのかもしれないけど、なかなかどうしたものかなという感じですね。。。

ではでは、おかしなことやこんなあるよっとかありましたら、ぜひぜひフィードバックくださいー!

おいらバックアップしないよ!って人も開発者として覚えておきたいmysqldumpのオレオレ実践Tipsを見てFBください。

MySQL Perl update_at : 2013-08-29T09:17:28
hirobanex.netの更新情報の取得
 RSSリーダーで購読する   
blog comments powered by Disqus