MySQLのバイナリログとフルバックアップから復旧させる方法
実現したいこと
- データベースがぶっ壊れても復旧できるようにしたい
- サーバーには4つのDBが入っているが、うち2つだけを対象にしたい
ざっくり調べて理解したこと
バイナリログを残す設定にする方法
- 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
- mysqlを再起動
フルバックアップ保存する方法
ここでは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';
バイナリログを流し込む方法
- まずはバイナリファイルを人間の読める形に展開する
# 「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