バックアップ／リカバリ

コールドバックアップ

インスタンスを停止してＯＳコマンドによるファイルコピーを行う。以下の例ではcpコマンドだが、tarなどでももちろんかまわない。

$ cp -rp /var/lib/mysql/data /var/lib/mysql/backup

オンラインバックアップ

mysqldumpコマンドでデータベース全体をバックアップする。

# mysqldump --user=root --password=pass \
            --socket=/var/lib/mysql/mysql.sock \
            --single-transaction \
            --master-data=2 \
            --flush-logs \
            --hex-blob \
            --default-character-set=cp932 \
            --all-databases > /var/lib/mysql/backup/mysql-dump.sql

特定のデータベースを指定するには、--all-databasesをはずしてスキーマを指定すればよい。

# mysqldump --user=root --password=pass \
            --socket=/var/lib/mysql/mysql.sock \
            --single-transaction \
            --master-data=2 \
            --flush-logs \
            --hex-blob \
            --default-character-set=cp932 \
            demo_db > /var/lib/mysql/backup/demo_db-dump.sql

mysqldumpコマンドのオプション

option	説明
--single-transaction	ダンプ前にBEGIN SQL文を発行する。--master-data=2と組み合わせて指定することで、ロックをかけずに一貫性のあるバックアップを取得できる。但し、InnoDBの場合に限る。
--master-data	値が1の場合、CHANGE MASTER文でダンプ開始時のバイナリログの位置情報を記録する。値が2の場合、CHANGE MASTER文がコメントアウトされる。(デフォルト)
--flush-logs	ダンプ前にバイナリログをスイッチさせる。差分ファイルを適用する際、全体バックアップ直後のバイナリログから適用すればよいので、指定しておく。
--hex-blob	バイナリ型のデータをエスケープ処理を行わずに実際に格納された値の１６進数表記で保存する。これを指定しないとdefault-character-setがシフトJIS系の場合エスケープ処理に失敗し、バイナリデータが破壊される場合がある。
--default-character-set	バックアップデータの文字コード。my.cnfで指定しているdefault-character-setと同じコードを指定する。省略した場合、UTF-8に変換されるので文字化けの原因となる。
--lock-tables	ダンプ前に全テーブルをロックする。但し、データベース単位でロックする為、DB間の一貫性は保障しない。 MyISAMバックアップ時はこのオプションでロックをかける。InnoDBの場合は、--single-transactionを使用する。
-x、--lock-all-tables	ダンプ前に全データベースを共有ロックする。"FLUSH TABLES WITH READ LOCK" これは、すべてのセッションから一切の更新ができなくなる。"UNLOCK TABLES"コマンドで共有ロックを解除できる。

バイナリログによる差分バックアップ

バイナリログを作成するには、初期化パラメータファイルに"log-bin"を追加する。また、sync_binlog=1を追加すると、バイナリログがトランザクションコミット時に同期書き込みになる。
(デフォルトでは、コミット時にバイナリログファイルへ書き込みが行われない場合がある。)
以下の設定だと、/var/lib/mysql/binlogディレクトリ以下に「mysql-bin.000001」という名前でバイナリログが作成される。

[mysqld]
log-bin=/var/lib/mysql/binlog/mysql-bin
sync_binlog=1

バイナリログのスイッチ

スイッチが発生すると、拡張子の数字に+1されたものが新たに作成され、新しいファイルに書き込むようになる。
以下のように明示的にSQL文またはコマンドでスイッチさせるか、もしくはインスタンス起動時にスイッチする。

mysql> FLUSH LOGS;    <-- SQL文によるスイッチ

$ mysqladmin flush-logs -u ユーザー名 -p    <-- コマンドによるスイッチ

リストア及びロールフォワードリカバリ

バックアップ運用として、日曜日の0:00に全体バックアップ。日曜日以外の0:00にバイナリログのスイッチを行い増分バックアップを行うものとする。

12/10(日) 0:00 全体バックアップ (--flush-logsやFLUSH LOGSでログスイッチする)
(ダンプファイル:/backup/mysql-dump.sql、バイナリログ:/backup/mysql-bin.000017)
12/11(月) 0:00 ログスイッチ
(バックアップ:/backup/mysql-bin.000017、カレントバイナリログ:/var/lib/mysql/binlog/mysql-bin.000018)
12/12(火) 0:00 ログスイッチ
(バックアップ:/backup/mysql-bin.000018、カレントバイナリログ:/var/lib/mysql/binlog/mysql-bin.000019)
12/13(水) 10:00 メディア障害発生
(カレントバイナリログ:/var/lib/mysql/binlog/mysql-bin.000019)

復旧手順

まず、全体バックアップ(/backup/mysql-dump.sql)をリストアし、それ以降のバイナリログ(/backup/mysql-bin.000017、18、19)を順番に適用していく。

カレントのバイナリログ(/var/lib/mysql/binlog/mysql-bin.000019)をバックアップする。
バックアップ先:/backup/mysql-bin.000019
インスタンスをリモートからの接続禁止状態で起動

[mysqld]
#log-bin=/var/lib/mysql/binlog/mysql-bin    <-- コメントアウトし、バイナリログの出力を停止

$ cd /usr/local/mysql
$ ./bin/mysqld_safe --defaults-file=/etc/my.cnf --skip-networking --skip-grant-tables &

全体バックアップ(/backup/mysql-dump.sql)のリストア

$ mysql --user=root --password=pass \
        --socket=/var/lib/mysql/mysql.sock \
        --default-character-set=cp932 < /backup/mysql-dump.sql

バイナリログを適用し、ロールフォワードリカバリを行う

$ mysqlbinlog --disable-log-bin \
    /backup/mysql-bin.000017 /backup/mysql-bin.000018 /backup/mysql-bin.000019 > /backup/recovery.sql
$ mysql --user=root --password=pass \
        --socket=/var/lib/mysql/mysql.sock \
        --default-character-set=cp932 < /backup/recovery.sql

インスタンスの再起動

[mysqld]
log-bin=/var/lib/mysql/binlog/mysql-bin    <-- コメントアウトをはずしておくこと。

$ mysqladmin shutdown --user=root --password=pass \
        --socket=/var/lib/mysql/mysql.sock
$ cd /usr/local/mysql
$ ./bin/mysqld_safe --defaults-file=/etc/my.cnf &

mysqlbinlogコマンドのオプション

参考として、主なmysqlbinlogコマンドのオプションを以下にまとめる。

option	説明
--start-datetime	バイナリログからの開始日時を指定し、その時点からのログがsqlファイルに出力される。例) $ mysqlbinlog --start-datetime="2006-12-13 06:30:00" \ /backup/mysql-bin.000019 > /backup/recovery.sql
--stop-datetime	バイナリログからの終了日時を指定し、その時点までのログがsqlファイルに出力される。例) $ mysqlbinlog --stop-datetime="2006-12-13 08:00:00" \ /backup/mysql-bin.000019 > /backup/recovery.sql
--database	指定されたデータベースのログのみをsqlファイルに出力する。例) $ mysqlbinlog --database="demo_db" \ /backup/mysql-bin.000019 > /backup/recovery.sql
--disable-log-bin	バイナリログの出力を無効にする。
--start-position	指定された位置からログを読み始める。このオプションはコマンドラインで指定された最初のバイナリログファイルに適用される。例) $ mysqlbinlog --start-position=7743 \ /backup/mysql-bin.000017 /backup/mysql-bin.000018 > /backup/recovery.sql
--stop-position	指定された位置までログを読み込む。このオプションはコマンドラインで指定された最後のバイナリログファイルに適用される。例) $ mysqlbinlog --stop-position=15324 \ /backup/mysql-bin.000017 /backup/mysql-bin.000018 > /backup/recovery.sql

参考

MySQL 5.1 Reference Manual Chapter8.10.mysqlbinlog
MySQL 5.1 Reference Manual Chapter8.13.mysqldump

インデックスへ戻る