データベースのダンプとリストア

ダンプとリストアとは

ダンプは、データベースの中身を CREATE TABLE と INSERT の SQL テキストとして書き出すことです。書き出したファイルを流し込んで元の状態に戻す作業をリストアと呼びます。

mysqldump や pg_dump が出力するのは、多くの場合ただのテキストファイルです。エディタで開けば中身が読めますし、grep で特定のテーブルを探すこともできます。この「人が読める SQL に落とす」性質が、次に説明する移植性の高さにつながります。

[[バックアップとリカバリ]] ではデータを守る手段の全体像を扱いますが、この記事はそのうち論理バックアップ、つまりダンプに絞って見ていきます。

論理バックアップと物理バックアップ

データファイルをそのままコピーする方式を物理バックアップと呼び、ダンプとは性質が大きく異なります。

ダンプは遅いが柔軟、物理バックアップは速いが硬い、と覚えると選び分けやすくなります。日々の障害対策は物理バックアップ側が主役で、ダンプは「別環境へ移す」「一部のテーブルだけ取り出す」「長期保管用に人が読める形で残す」といった用途で力を発揮します。

コマンドの形

# MySQL: データベース全体を1ファイルへ
mysqldump -u root -p --single-transaction appdb > appdb.sql

# PostgreSQL: 同じくテキストで書き出す
pg_dump -U postgres appdb > appdb.sql

リストアは、書き出した SQL をそのまま流し込むだけです。

mysql -u root -p appdb < appdb.sql
psql -U postgres -d appdb -f appdb.sql

リストア先のデータベースはあらかじめ空で作っておくのが基本です。既存データが入ったところへ流したときの挙動は、2つのツールで正反対になります。

• mysqldump — 既定(--opt 相当)で各テーブルの手前に DROP TABLE IF EXISTS を出力します。流し込むとテーブルごと作り直されるため、そこにあった既存データは消えます
• pg_dump(plain 形式・--clean なし) — DROP は出力されないので、既存の行と衝突して「すでに存在する」「重複キー」といったエラーになります。ただし psql は既定でエラーを読み飛ばして先へ進むため、-v ON_ERROR_STOP=1 を付けて確実に止めます

つまり mysqldump のダンプは、宛先を間違えると警告もなく現在のデータを置き換えます。リストアは Ctrl+C で止めても元には戻せません。流す前に接続先のホストとデータベース名を必ず読み合わせ、本番へ流すときは直前のバックアップを取ってから実行します。

整合性 — ダンプ中も更新は止まらない

素朴に全テーブルを順番に読むと、先に読んだテーブルとあとから読むテーブルで時点がずれます。注文テーブルは 10:00 時点、明細テーブルは 10:05 時点、という不整合が起こりえます。

そこで、一貫した1時点の状態を読み続けるオプションを使います。

• MySQL — --single-transaction を付けると、ダンプ全体が1つのトランザクションとして実行され、[[トランザクション分離レベル]] の REPEATABLE READ によって開始時点の状態を読み続けます。テーブルロックを取らないため、サービスを止めずに済みます
• PostgreSQL — pg_dump は既定で1つのトランザクション内から読むため、同様の一貫性が得られます

注意点は2つあります。--single-transaction が効くのは InnoDB のようなトランザクション対応エンジンだけであること、そしてダンプ中に ALTER TABLE などの DDL が走ると一貫性が保証されないことです。

巨大なデータベースでの注意

• ロック(MySQL 固有) — mysqldump で --single-transaction を付け忘れると全テーブルをロックし、その間の書き込みが止まります([[ロックとデッドロック]])。pg_dump にこのオプションは無く、既定でスナップショットを取るため書き込みは止まりません
• 長時間トランザクションの副作用 — 数百 GB のダンプは数時間かかることがあり、その間ずっと開始時点の版を保持し続けるため、更新の多いデータベースでは古い版が溜まってディスクを圧迫します
• リストアはもっと遅い — 全行を書き戻したうえで索引を張り直すため、取得より数倍かかるのが普通です(mysqldump は既定でまとめ書きの --extended-insert、pg_dump の plain 形式は COPY を使うので、1行ずつ INSERT するわけではありません)。復旧時間の見積もりは、実際にリストアを試して測る以外に確かめようがありません
• 逃がし方 — テーブル単位に分割する、参照用の複製サーバーからダンプする([[レプリケーション]])などで本番への影響を抑えます

開発環境へ持ち込むときのマスキング

ダンプの魅力は「本番と同じデータで開発・検証できる」ことですが、そのまま配ると本番の個人情報が開発者全員の手元にコピーされます。

• 氏名・メールアドレス・電話番号などはダミー値へ置換(マスキング)してから配布する
• 実装は「ダンプ後の SQL に UPDATE を流す」か「マスキング済みのビューからダンプする」のどちらかが手軽
• そもそも本番データが必要か問い直す — 件数だけ欲しいならテストデータ生成で足りることも多い
• 持ち出したダンプファイル自体も機密情報。保管時は暗号化し([[暗号化の基礎]])、不要になったら確実に削除する

初学者向けポイント

• ダンプは「取れた」だけでは不十分 — 空のデータベースへリストアしてアプリが起動するところまで確認する
• 構造だけ・データだけを分けて取得できる(mysqldump なら --no-data、pg_dump なら --schema-only / --data-only)。構造の差分管理は [[スキーママイグレーション]] の担当領域と重なる
• SQL テキストは繰り返しが多いため、圧縮すると大幅に小さくなる
• 文字コードがずれると復元後に文字化けする — ダンプ元と復元先の設定を揃える([[文字コードと文字化け]])

関連技術とのつながり

• [[バックアップとリカバリ]] — ダンプはその中の論理バックアップという一手段
• [[MySQL]] — mysqldump が標準で付属する
• [[PostgreSQL]] — pg_dump が標準で付属する
• [[トランザクションとACID]] — 一貫したダンプは1トランザクション内の読み取りで成立する
• [[スキーママイグレーション]] — 構造だけのダンプはスキーマ管理と接する
• [[ロックとデッドロック]] — ダンプ中のロックが書き込みを止めうる

Q: mysqldump や pg_dump が出力するものはどれ?
- [x] CREATE TABLE や INSERT が並んだ SQL のテキスト
- [ ] データベースのデータファイルをそのままコピーしたもの
- [ ] インデックスだけを抜き出した専用バイナリ
解説: ダンプ(論理バックアップ)は人が読める SQL テキストとして書き出すため、別バージョンや別サーバーへ移しやすくなります。

Q: 物理バックアップと比べたときのダンプの特徴として正しいのはどれ?
- [ ] 取得もリストアも高速だが、同じ製品・同じバージョンでしか戻せない
- [x] 取得もリストアも遅いが、移植性が高くテーブル単位でも取り出せる
- [ ] サイズが必ず実データより小さくなる
解説: ダンプは全行を読んで SQL に整形し、戻すときも全行の書き戻しと索引の再構築が要るため遅くなります。その代わり移植性と部分取得の柔軟さがあります。

Q: MySQL でサービスを止めずに一貫した時点のダンプを取るために使うオプションはどれ?
- [ ] --no-data
- [x] --single-transaction
- [ ] --schema-only
解説: --single-transaction はダンプ全体を1つのトランザクションとして実行し、REPEATABLE READ で開始時点の状態を読み続けます。--no-data は構造だけを取る mysqldump のオプションです。