MySQLでのバックアップ方法のまとめ

いまいちまとめきれていませんが。誤りなどありましたら、お知らせ下さい。

バックアップの種類

取得範囲による分類

全体バックアップ
- データベース全体をバックアップする
増分バックアップ
- 前回のバックアップの後に変更された部分のみをバックアップする
差分バックアップ
- 前回の全体バックアップの後に変更された部分をバックアップする

サービスレベルによる分類

オフラインバックアップ
- データベースを停止した状態でのバックアップ
- もっとも安全な方法
オンラインバックアップ
- データベースを起動した状態でのバックアップ
- ロックと一貫性の問題が生じる

バックアップ形式による分類

物理バックアップ
- ファイルをバイナリのままバックアップ
論理バックアップ
- SQL文に変換してバックアップ
- 物理バックアップよりもバックアップ、そして特にリストアに時間がかかる

ロックと一貫性の問題

オンラインバックアップの場合、バックアップ中にデータが更新される可能性があります。その場合、例えば、以下のような時系列で処理が実行された場合、

テーブルAがバックアップされる
テーブルAとBが更新される
テーブルBがバックアップされる

テーブルAとBはバックアップされたデータの時点が異なります。Aが注文、Bが在庫とすると、注文は未計上だが在庫が減少した状態になり、一貫性がありません。

このようなバックアップをリストアするとデータが一貫しておらず困ったことになります。一貫性が保証されたバックアップを取得すべきです。

一貫性を保証するには全テーブルをロックすればよいが、そうするとバックアップ中にデータベースの機能が使えなくなり（少なくとも更新は全くできない）、オンラインバックアップの意義が薄れます。

また、他のセッションがロックをかけていた場合、その処理が終わるまでロック待ちとなり、バックアップが開始されません。

InnoDBテーブルについては、ロックせずに一貫したバックアップを取得する方法があります。

何をバックアップするか？

データベースファイル（の中のデータ）
エラーログファイル、スロークエリファイル
バイナリログファイル
設定ファイルmy.cnf

主なバックアップ対象は、1. です。これがないと復旧は不可能です。他は必要に応じてバックアップします。2. があると問題解決に役立つかもしれません。

リストアとリカバリ

リストア
- バックアップ時点まで復旧すること
リカバリ
- 障害直前の状態まで復旧すること
- トランザクションログ（MySQLではバイナリログと呼ばれる）が必要

mysqldump

特徴

MySQL標準の定番ツール
オンラインバックアップ
論理バックアップ

注意点

MyISAMテーブルの一貫したバックアップを取得するには--lock-all-tablesが必要
- 権限テーブルなどはMyISAMテーブル（少なくともMySQL 5.6、MariaDB 10.0までは）

オプション

--lock-all-tables ... MySQLのすべてのテーブルをロックする
- 全ダンプの期間、グローバル読み取りロックを取得する
- 指定すると--single-transactionおよび--lock-tablesは自動的にオフになる
--opt ... 以下の組み合わせ。MySQL 5.1以降はデフォルトで有効
- --add-drop-table
- --add-locks
- --create-options
- --disable-keys
- --extended-insert
- --lock-tables
- --quick
- --set-charset
--lock-tables ... ダンプされる各データベースに対して、ダンプするすべてのテーブルをダンプ前にロックする
- 複数のデータベース内にあるテーブルを同時にロックすることはできない
--single-transaction ... トランザクション分離モードをREPEATABLE READに設定しSTART TRANSACTIONする
- InnoDBテーブルをロックせずにバックアップできる
- 一貫した状態でダンプされるのはInnoDBテーブルのみ
- バックアップ中に他の接続からALTER TABLE、CREATE TABLE、DROP TABLE、RENAME TABLE、TRUNCATE TABLEを実行してはいけない
- --lock-tablesとは排他的
--master-data ... バイナリログファイルの名前と場所を出力に書き込む
- 値が2の場合、CHANGE MASTER TOステートメントはSQLコメントとして書き込まれる
--flush-logs ... ダンプを始める前にバイナリログをフラッシュする
--all-database ... すべてのデータベース内のすべてのテーブルをダンプする

マニュアル

実行例

データベース領域全体を安全第一にバックアップする：

$ mysqldump -uroot --lock-all-tables --master-data=2 --flush-logs \
  --all-databases > /backup/mysql-dump.sql

データベース全体がロックされる。

ロックせずにデータベース領域全体をバックアップする：

$ mysqldump -uroot --single-transaction --master-data=2 --flush-logs \
  --all-databases > /backup/mysql-dump.sql

InnoDBテーブルは一貫したバックアップを取得できる。バックアップ中にMyISAMテーブルを更新してはいけない。

文字コードがCP932のデータベース領域全体を安全第一にバックアップする：

$ mysqldump -uroot --lock-all-tables --master-data=2 --flush-logs \
  --hex-blob --default-character-set=cp932 \
  --all-databases > /backup/mysql-dump.sql

文字コードがCP932のデータベース領域全体をロックせずにバックアップする：

$ mysqldump -uroot --single-transaction --master-data=2 --flush-logs \
  --hex-blob --default-character-set=cp932 \
  --all-databases > /backup/mysql-dump.sql

バックアップ中にMyISAMテーブルを更新してはいけない。