データリカバリー
Russell Dyer著
http://dev.mysql.com/tech-resources/articles/point_in_time_recovery.html

mysqldumpの様なユーティリティはMySQLのデータやテーブル・スキーマ
をバックアップするのに有益です。バックアップは通常一日
一回または決められた間隔で行われます。しかし、もしデータが最後の
バックアップの後、数時間後に紛失した場合、そのデータは
回復できないかも知れません。連続してデータを守る幾つか
の方法があります。中途のデータを回復するのに使う
ことが出来る方法１つはバイナリー・ログを使用することです。
バイナリー・ログはサーバーが実行する際に全てのSQLの
トランザクションを記録します。管理者の中には
サーバーの性能が落ちることを心配してロギングを最少化しようとする
人がいます。平均するとバイナリー・ログをオンにしてもサーバーは
1%程度しか性能が落ちません。これがあれば１分刻みで
データの回復ができます。

バイナリーログの設定

バイナリーログはサーバーで実際に実行されたか試みられた
SQLトランザクション全てをバイナリー・ファイルに記録
されます。 mysqlbinlogユーティリティを使えばバイナリーログ
ファイルの内容を抽出し、SQL ステートメントを再び
容易に実行できます。バイナリー・ロギングをオンにするため
には以下の１行を[mysqld]グループのサーバー・オプションファイルに追加して
ください。
(例えば /etc/my.cnf または c:\my.iniこれはシステムに依存します。）

log-bin = /var/log/mysql/bin.log

どのパスを使うかは使用しているファイル・システムや設定
によります。ログが格納されるディレクトリを生成して、ディレクトリ
の所有者と許可を設定する必要があります。データを
バックアップするパスとログファイルのパスは違うハード
ドライブにしておくのをお勧めします。 上記のファイル名
の接尾辞ログは自動的に6桁の番号に置き換えられます。バイナリー
ログは特定のデータベースに限定できますし、ログから特定の
データベースを削除できます。詳細なログのチューニング に
関してはバイナリーログのドキュメントを参照してください。
オプション・ファイルに上記の追加をしてから、この変化
を反映させるためにMySQLサーバーを再スタートする
必要があります。

mysqldumpのようなものを使って正常なバックアップを
する他、バックアップでバイナリー・ログの始めを
指定したいと思うかもしれません。これはバックアップ
を実行する時ログをフラッシュすることで出来ます。以下
のcronのような一行か似たようなすケージュールの
ユーティリティを使用します。

mysqladmin -u root -pmypwd flush-logs

この行では、mysqladminユーティリティはサーバーのログを
フラッシュするのに使用されます。mysqlのクライアント
からFLUSH LOGSステートメントを実行することでも
同じことができます。

簡単な暫定的回復

毎日のバックアップとバイナリーログを実行すし、MySQLのサーバー
からデータを回復するのはめんどくさいプロシージャではありますが
簡単なことです。例として毎日午前零時にmysqldumpを使用して
データをバックアップしているとしましょう。更に、ある日
（例えば2005年の4月20日の午前10時に）データが紛失された
としましょう。回復するためにはまづMySQLのサーバーを停止
し、再スタートする際他のユーザにアクセスを禁じてください。
それにはこうします。

mysqld --socket=/tmp/mysql_restore.sock --skip-networking

--socketオプションはUnixシステムの異なった
ソケットファイルを一時的に指定するものです。Windows では
パスやファイル名なしで、ネームド・パイプ（例えばMySQL_restore)を
使用します。

mysqld-nt --enable-named-pipe \
--socket=MySQL_restore --skip-networking

NTベースでないWindows ではネームド・パイプは適用できませんので
TCP/IPを使用してください。この場合--portオプションを使って
3306以外のポート番号でサーバーをスタートさせてください。プリビレッジ
がない使用されていないどのポートを使用しても構いません。

--skip-networking オプションはTCP/IPでユーザが
サーバーやソケット・ファイルやネームド・パイプにアクセスできない
ようにします。localhostからの接続のみを許可します。これで
あってもサーバーに接続しているコンソールからアクセスは
できます。またはセキュアー・シェルからの接続も可能です。
異なったソケット名を使ったオプションはオーサライズされない
ユーザやサーバ上のAPIのスクリプトがデータが回復されるまで
データベースにアクセスできなくなります。

サーバーを完全に掌握したら、データを回復している最中
に他のユーザがサーバーにアクセスするという問題を考慮せず
作業できます。最初は以下のように昨夜のバックアップ
からのダンプ・ファイルを回復します。

mysql -u root -pmypwd --socket=/tmp/mysql_restore.sock \
< /var/backup/20050420.sql

実際のパスやファイルの名前はサーバー毎に異なります。以下は
昨夜の段階でのデータを回復します。これはその日の最初での
ものではありません。ダンプ・ファイルが生成されてから、
トランザクションを回復するには mysqlbinlogユーティリティ
を使用します。毎夜バックアップし、毎日ログを
フラッシュするのであれば以下のようにしてバイナリーログ
を回復できます。

mysqlbinlog /var/log/mysql/bin.123456 \
| mysql -u root -pmypwd \
--socket=/tmp/mysql_restore.sock

この例ではmysqlbinlogの結果はパイプされてmysqlのクライアント
に渡されて処理されます。これが終了するとテンポラリー
ソケットファイルとネットワークの制限なしでMysQLサーバー
を再スタートします。

このステップで最終のバックアップの時点データを回復します。
それから最後にログがフラッシュされてから実行された全てのSQL
ステートメントを再度実行します。最後のバイナリー・ログ
ファイルの名前を回復するにはログを含むディレクトリの
リストを参照してください。

マニュアルによる回復

前のセクションのシナリオは全てのサーバのデータを
回復したいという前提です。しかし、もしリカバリー
を行いたい理由があるSQLステートメントに
よってデータが予期せずに消去されてしまい、単にバイナリー
ログを使用した回復では同じことが起こる場合には
どうしましょうか。そのような場合は上記のように
mysqlbinlogを実行しますがmysqlにパイプするので
はなくテキストファイルに格納して、必要な
ものだけを選ぶ編集をします。たとえば以下のようです。

mysqlbinlog /var/log/mysql/bin.123456 \
> /tmp/mysql_restore.sql

このコマンドは/tmpディレクトリに簡単なテキスト
ファイルを生成します。 viやnotepad.exeで編集して
ください。ワードプロセッサは使わないでください。
ファイルにバイナリーのフォーマット用のコード
を付着させるかも知れませんから。そういうものがあると
mysqlに渡したときに問題が起こります。必要でないSQL
ステートメントを除去しリストアーのファイルをセーブして
以下のように処理します。

mysql -u root -pmypwd \
--socket=/tmp/mysql_restore.sock \
< /tmp/mysql_restore.sql

この際レスザンのサインはスタンダード入力をリディレクト
するのに使います。

リカバリー

MySQL4.1.4 からmysqlbinlogに幾つかのオプションが追加されました。
バイナリー・ログを使っての回復の微調整が容易になりマニュアル
での作業を必要としなくなりました。--start-date
と--stop-dateオプションなどです。例えば2005年4月20日
きっかりに大きなテーブルを除去するSQLステートメントを
実行したとします。昨夜のバックアップをリストアーして
mysqlbinlogを以下のように実行します。

mysqlbinlog --stop-date="2005-04-20 9:59:59"
/var/log/mysql/bin.123456 |
mysql -u root -pmypwd \
--socket=/tmp/mysql_restore.sock

これで--stop-dateオプションで与えられている日時まで
の全てのデータを回復します。何時間も経ってから
誤ったSQLステートメントを発見しなかったのであれば、その時点
からのすべての動きをリカバーしたいでしょう。以下の
ように出来ます。

mysqlbinlog --start-date="2005-04-20 10:01:00" \
/var/log/mysql/bin.123456 \
| mysql -u root -pmypwd \
--socket=/tmp/mysql_restore.sock

この例では10:01am以降からログされたSQLステートメントが実行されます。
昨夜のダンプファイルとmysqlbinlogの2行を実行することで
10:00amの一秒前までと10:01amの後の全てをリストアーします。
ログを見てきっちりとした時間を確かめするべきです。次の
セクションでどうするかを説明します。

位置によるリカバリー

特定の時間を使用する代わりに、mysqlbinlogの他の
２つのオプションを使えます。すなわち、--start-positionと
--stop-positionです。スタートとストップのオプションと同じ
ように動作しますが、ログの位置を指定します。
ログでの位置を使うことでもっと正確な回復ができる
かもしれません。これは特に問題があったSQLステートメント
が実行された時点でたくさんのトランザクションが起こった
場合に正確に指定できます。位置を確定するためには、誤った
トランザクションが実行された時期をstart・stop timeを
指定してmysqlbinlogを実行して結果をテキストファイルに
落とします。以下のようにします。

mysqlbinlog --start-date="2005-04-20 9:55:00" \
--stop-date="2005-04-20 10:05:00" \
/var/log/mysql/bin.123456 \
> /tmp/mysql_restore.sql

これは/tmpディレクトリーに誤ったSQLステートメントが
実行された辺りのSQLステートメントを含む小さなテキスト
ファイルを生成します。このファイルをエディター
で開き、繰り返したくないステートメントを除去します。
一旦リカバリーを止めたい時点とリカバリーを再び
始める時点が分かると、その位置を記録します。この位置
はlog-posの後に番号をつけます。バックアップ・ファイルを
リストアーした後、位置の番号を持って、以下を
実行します。

mysqlbinlog --stop-position="368312" \
/var/log/mysql/bin.123456 \
| mysql -u root -pmypwd \
--socket=/tmp/mysql_restore.sock

mysqlbinlog --start-position="368315" \
/var/log/mysql/bin.123456 \
| mysql -u root -pmypwd \
--socket=/tmp/mysql_restore.sock

最初のラインはstopの位置までの全てのトランザクション
を回復します。次のラインはスタートの位置から最後まで
の全てのトランザクションを回復します。ところでそれぞれ
のSQLステートメントが記録される前にmysqlbinlog
の出力はSET TIMESTAMPステートメントがありますので、
回復されたデータと関連するMySQLのログはトランザクション
が実行された時間とマッチします。

結論

バイナリー・ロギングでMySQLの標準インストレーションが
あればある特定の時点に回復できます。肝心なことは定期的
にバックアップを行い、ログをフラッシュすることです。頻度は
それぞれの特定のニーズやポリシーによります。こういう
用意があれば、問題があった時に迅速に正確に回復するプロセス
を熟知するだけです。ここで説明した方法を行うのに
必要な時間がない場合は、特にデータをリカバーする
際に他のユーザに使用を許さないという制限を考慮すると、
他の方法でデータを守る方法を考えリプリケーションやクラスター
なんどの方法を考えるべきです。