mysqldump --single-transaction と整合性のあるバックアップ — 「取れた」と「戻せる」は別の話

3-2-1 ルールでは、バックアップを3つ持ち、2種類の媒体に分け、1つは別の場所に置くという考え方を整理した。ただ、それは「どこに置くか」の話で、中身が正しいかは別の問題である。

データベースのバックアップでよく使われるのが mysqldump だ。ところが、書き込みが続いているデータベースに対して素朴にダンプを取ると、ファイルは問題なく出来上がるのに、復元するとテーブル間のデータに食い違いが出ることがある。この記事では、その仕組みを手元の MySQL で小さく再現して確かめ、--single-transaction の役割を整理する。特定の製品の実装ではなく、一般的な仕組みの解説である。

補足: 実験は2026年10月17日に手元の MySQL 9.6（InnoDB）で実行した。件数は環境と実行タイミングで変わるため、数字は「傾向を確かめた一例」として読んでほしい。

「整合性がある」とはどういう状態か

WordPress のデータベースには、互いに関係を持つテーブルが多い。たとえば投稿と投稿メタ、注文と注文明細のように、片方にだけ行があると意味が通らない組み合わせである。

整合性のあるバックアップとは、ある1つの時点のデータベースを、そのまま切り取ったものを指す。複数のテーブルが、同じ瞬間の状態でそろっていることが条件になる。

問題は、mysqldump がテーブルを1つずつ順番に書き出すことにある。大きなデータベースでは、全体のダンプに数分以上かかる。その間にサイトが動いていれば、最初に書き出したテーブルと、最後に書き出したテーブルでは「時点」が違ってしまう。

実験: 書き込みを続けながらダンプを取る

2つのテーブルを用意した。

• orders（注文）: 親にあたる
• items（明細）: 注文1件につき必ず3行ぶら下がる。order_id で orders を参照する

最初に注文30万件・明細90万行を入れ、別の接続から「注文1件と明細3行を、1つのトランザクションで追加する」処理を繰り返し走らせた。つまりいつ切り取っても、明細は必ず注文に紐づいている状態を、書き込み側は守り続けている。

この状態で、ロックもトランザクションも指定せずに --skip-lock-tables でダンプを取った。実行すると、mysqldump は次の警告を出した。

In order to ensure a consistent backup of the database, pass
--single-transaction or --lock-all-tables or --source-data.

ダンプは最後まで正常に終わり、エラーは1つも出ない。そのファイルを別のデータベースに復元して、整合性を調べた。

明細は先にダンプされた orders より後に書き出されるため、その間に追加された注文の明細だけが「親のいないデータ」として混ざった。書き込み側は常に整合を保っているのに、ダンプの切り取り方が悪いせいで、存在しない状態が作られたことになる。

怖いのは、この壊れ方が復元するまで分からない点である。ダンプのファイルサイズも、終了コードも、見た目は正常だ。「バックアップが取れている」ことと「戻せる」ことは別だと実感する場面である。

--single-transaction が行うこと

--single-transaction を付けると、mysqldump はダンプの最初にトランザクションを開き、そのトランザクションの中ですべてのテーブルを読む。

InnoDB は、トランザクションの開始時点のデータを読み続ける仕組み（スナップショット）を持っている。そのため、ダンプの途中でほかの接続がデータを変更しても、mysqldump から見えるのは開始した瞬間の状態のままである。これで、テーブルをまたいだ「1つの時点」の切り取りになる。

もう1つの利点は、書き込みを止めないことだ。テーブルロックで書き込みを待たせる方法と違い、サイトを動かしたままダンプを取れる。

なお、この記事の実験では --single-transaction 側の復元後チェックまでは完了できなかった。ここでの説明は、MySQL の公式ドキュメントに書かれている仕組みに基づく。実際に使うときは、後述のとおり復元テストで自分の環境を確かめてほしい。

使えない・効かない場面

--single-transaction は万能ではない。条件を知っておく必要がある。

1. InnoDB のテーブルが対象であること。MyISAM のようにトランザクションを持たないテーブルは、スナップショットの対象にならない。古い環境やプラグインが MyISAM テーブルを作っていると、そのテーブルだけ整合性が保証されない。
2. ダンプ中にテーブル構造を変えないこと。ALTER TABLE や DROP TABLE など、構造を変える操作が重なると、ダンプが失敗したり、内容が不正確になったりすることがある。
3. 長時間のトランザクションは負荷になること。開始時点のデータを保持し続けるため、ダンプが長いほど、変更履歴の保持が増える。

自分のテーブルがどのエンジンかは、次のようにして確認できる。

SELECT table_name, engine
FROM information_schema.tables
WHERE table_schema = DATABASE();

実務での確認手順

1. エンジンを確認する。InnoDB 以外が混ざっていないかを見る。
2. ダンプのコマンドを決めて記録する。例として次の形がある。

mysqldump --single-transaction --set-gtid-purged=OFF \
  -u USER -p DBNAME > backup.sql

--set-gtid-purged=OFF は、GTID を使う環境で、復元先に GTID の情報を持ち込まないための指定だ。実験でも、これを付けないと復元時にエラーになった。環境によって要否が変わるので、自分の環境で確認する。

1. 別の場所に復元してみる。本番とは別のデータベースに戻し、件数と、テーブル間の対応（親のいない行がないか）を確認する。
2. 復元できたことを記録する。「いつ、どのダンプを、どこで戻せたか」を残しておくと、いざというときに判断しやすい。

3 が最も大事だ。ダンプのコマンドが正しくても、実際に戻して確かめた経験がなければ、「戻せる」とは言い切れない。

まとめ

• ダンプが最後まで終わることと、復元して整合性が保たれていることは別の話
• mysqldump はテーブルを順番に書き出すため、書き込み中はテーブルごとに時点がずれることがある。実験では、親のいない明細が 4,239 行できた
• --single-transaction は、1つのトランザクションの中で全テーブルを読み、1つの時点を切り取る。書き込みを止めない
• 効くのは InnoDB が前提で、MyISAM や、ダンプ中の構造変更では保証されない
• 最後は別の場所に復元して確かめるところまでを、バックアップの手順に含める