アナログCPU:5108843109

ゲームと音楽とプログラミング(酒と女とロックンロールのノリで)

('ω') < イザユケエンジニャー

MySQLのバイナリログとフルバックアップから復旧させる方法

実現したいこと

  • データベースがぶっ壊れても復旧できるようにしたい
  • サーバーには4つのDBが入っているが、うち2つだけを対象にしたい

ざっくり調べて理解したこと

  • MySQLにはバイナリログを残す仕組みがある
    • バイナリログは、トラブル時の復旧作業やレプリケーションに用いることができる
    • UPDATEとかINSERTとか、更新が発生するクエリが記録されている
  • データベースのフルバックアップと、それ以降のバイナリログがあれば復旧できる

やること

  • バイナリログを残す設定にする
  • 定期的にフルバックアップ保存&それ以前のバイナリログを削除する
  • 何かあればフルバックアップから復旧&バイナリログを流し込む

バイナリログを残す設定にする方法

  • my.cnfに設定を追加
    • ちなみに設定値はMySQLに「SHOW VARIABLES」クエリを打って確認することも可能です
vi /etc/my.cnf
[mysqld]
# バイナリログの出力先設定
# (この設定なら、「/var/lib/mysqlディレクトリに
#   「bin.000000」という名前のバイナリファイルができる。数字部分は連番。増える。)
log_bin = /var/lib/mysql/bin

# バイナリログを出力する対象のDB
# (この設定なら「test1」「test2」についてのみバイナリログが残る)
binlog_do_db = test1
binlog_do_db = test2

## 必要に応じて以下のような設定もできます

## バイナリログを保存する日数
## (これより古いと自動削除)
# expire_logs_days = 10

## バイナリログの最大サイズ
## (最大というか、これを超えると次のファイルに移る感じ?)
# max_binlog_size = 1024M

フルバックアップ保存する方法

ここでは2つのDBについて同時にバックアップを取りつつ、「--flush-logs」オプションを用いて現在のバイナリログをクローズして新しいバイナリログを生成してもらいます。
例えば最新のバイナリログが「bin.000001」であれば、バックアップ以降のログは「bin.000002」に記録されるようになります。

# ユーザーが「root」、バックアップするデータベースが「db1」「db2」
# 保存先が「backup.sql」 ※当然ですがフルパスなり相対パスなり適当に対応してください
mysqldump -u root -p --databases db1 db2 --flush-logs > backup.sql

バイナリログを削除する方法

※今回のケースでは、バックアップの直後に過去のバイナリログをすべて削除するのを想定
まず、最新のバイナリログを確認します。

ls -l /var/lib/mysql

これで一番新しいやつをメモ。

MySQLからでも確認可能です。

SHOW MASTER STATUS

こっちの場合は一番新しいログの名前が出てくるのでそれをメモ。

MySQLにて下記クエリを流す。

-- 「bin.000011」の部分はさっきメモした最新のバイナリログ。
-- この場合は「bin.000010」以前がすべて消えます。
PURGE MASTER LOGS TO 'bin.000011';

フルバックアップから復旧する方法

mysql -u root -p < backup.sql

バイナリログを流し込む方法

  • まずはバイナリファイルを人間の読める形に展開する
# 「bin.000011」の中身を「recover.sql」に展開
mysqlbinlog --no-defaults --disable-log-bin bin.000011 > recover.sql

# 複数のバイナリログが存在するときがネック。状況に応じて以下のような感じで

# ワイルドカードで一つのファイルにまとめて突っ込むとか
# (意図した順に記録されるかどうかは知りません)
mysqlbinlog --no-defaults --disable-log-bin bin.0* > recover.sql

# 並べてまとめて突っ込むとか
# (意図した順に記録されるかどうかは知りません)
mysqlbinlog --no-defaults --disable-log-bin bin.000011 bin.000012 > recover.sql

# 素直に(?)並べるとか
mysqlbinlog --no-defaults --disable-log-bin bin.000011 > recover_11.sql
mysqlbinlog --no-defaults --disable-log-bin bin.000012 > recover_12.sql
  • 展開したファイルを必要に応じて修正する
    • 間違えてDELETEしてしまったところを削除するとか。viとかでOK
  • 復元したDBに対してログを流し込む
mysql -u root -p < recover.sql