MySQLのlvmを使ったバックアップについて(備忘録)

MySQL Linux

前提

MySQLのデータディレクトリ専用にlvmの論理ドライブとしてパーティションを切ってある。(フォーマットはext3)

手順

以下の２手順を同一トランザクション内で実行する必要がある。

  • lvmスナップショットを撮る
  • master のバイナリログの参照位置を確認する。*1

そのため、この作業中は、flush tables with read lock; でデータベースを共有ロックする。*2
それを踏まえて基本的なプロセスは

  1. flush tables with read lock; でデータベースをロックする
  2. バイナリログをフラッシュする(オプション)
  3. lvm でMySQLのデータディレクトリをスナップショットで固める
  4. show master status でバイナリログの位置を確認。
  5. unlock tables; でロックの解除
  6. dump で固めたスナップショットをダンプする
  7. スナップショットを解除
  8. 過去のバイナリ（ダンプしたファイルに含まれている）を削除 *3

問題は、過去のバイナリログがどこまで削除していいかだ。
手順1.でフラッシュして新しくできたバイナリログを残せば後は削除していいはずだが。
ちょっと調べてみよう。

show master status;で、マスタが今いる位置を確認できる。

mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000047 |   436743 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

同時に、mysqlbinlog を使って生成されるログの一番最後を見てみると、

# at 436604
#101125 13:46:09 server id 1  end_log_pos 436743        Query   thread_id=532   exec_time=0     erro
r_code=0

と、最後のバイナリログ番号(# at の行)は、436604 で、次の行に有るend_log_pos が 436743 となっている。この 436743 が次に使われるバイナリログの番号を示している。

つまりマスタのPosition 436743 はこのend_log_pos の番号を常に示す。

リストア後、show master status でポジションを確認したときに表示されたposition番号からリカバリを行えば良く、且つこの番号を持つバイナリログは、手順1でフラッシュを行った時に新しく作成されたバイナリログに含まれるはずなので、このログ以後のログがあればリカバリは成功する。

かくして、フラッシュを行ったときに生成されたログを除いたそれ以前のものを削除していいことになるだろう。

リストア/リカバリ時の注意

ちなみにリストア/リカバリするときは、必ずバイナリログの生成をストップさせること。さもないと、バイナリログからのリカバリのプロセス自体がバイナリログに記録されてしまう。
バイナリログの生成をストップさせるには、my.cnfのlog-binをコメントアウトする。また、このままだとMySQLがエラーで起動できなくなるので、binlog_formatもコメントアウト。

#log-bin=mysql-bin
#binlog_format=mixed

リストア/リカバリの手順は、

  1. リストア
  2. リカバリ
  3. サービス再開
リストア:
  1. Apache を停止してMySQLが更新されるのを防ぐ
  2. MySQLを停止
  3. 残ってるバイナリログをどこかへ退避させる。
  4. 最後のダンプイメージをリストアする。*4 *5
  5. MySQLを起動 *6
リカバリ:
  1. MySQL を停止
  2. my.cnf の log-bin と binlog_format をコメントアウト (バイナリログの生成停止)*7
  3. 退避したバイナリログをMySQLのディレクトリに戻す。*8 *9
  4. MySQL を起動
  5. バイナリログを使ってPosition 以後のクエリーをREDOする。*10
サービスの再開
  1. MySQL を停止
  2. my.cnf の log-bin と binlog_format をアンコメント (バイナリログの生成再開)
  3. MySQL を起動
  4. Apache をStartさせる。

以上。

参考

システム・バックアップを基礎の基礎から (3/3)：Linux管理者への道（5） - ＠IT
LVMによる自動バックアップ・システムの構築 (1/3)：Linux管理者への道（6） - ＠IT
漢(オトコ)のコンピュータ道: MySQLバックアップ頂上決戦！！ LVMスナップショット vs InnoDB Hot Backup
Bigegg Lab - MySQLオンラインバックアップ手順 （LINUX環境 弊社推奨手順） ...
http://labs.unoh.net/2007/08/mysqlbinlog.html

*1:show master status

*2:データベースの更新を完全にロックしてしまうので、負荷の高いMySQLサーバでは対策が必要な様子。でかたま研究所 on Twitter: "@norisuke3 という事もあり、私が提案するのは多少遅い古いサーバでも全く構わない(Masterとの同期遅延が酷くならないならOK)ので、Backup取得専用のSlaveを1台用意し、そこからロック掛けてスナップショットなりDB毎にmysqldumpする事です。", http://stnard.jp/2010/04/26/151/

*3:削除は直接せず PURGE MASTER LOGS を使う。

*4:ここでリストアされるバイナリログは、最後のスナップショット以前のものなので不要

*5:restore -rf [dump したファイル]

*6:ここでクラシュリカバリが走って、リストアした状態の整合性が取れる。

*7:ここでshow master status としても、バイナリログを出力しないモードなので、Position値は確認できない。

*8:実際、戻さなくても作業はできるが、一応。

*9:mysql-bin.index は除く。

*10:mysqlbinlog [バイナリログファイル名] --start-possion [show master status の Position 値] | mysql -p