mysqldumpのバックアップと復元テスト｜失敗検知・隔離環境での照合

mysqldumpのバックアップは、圧縮ファイルができただけでは完了と判断できません。取得に失敗して途中までしかSQLが出ていなくても、後ろのgzipが正常終了することがあるからです。

取得コマンドの失敗を拾う、隔離したMySQLへ戻す、戻したデータとDBオブジェクトを照合する。この三つを分けて記録すると、「昨日のファイルはあるが、使えるか分からない」という状態を減らせます。ここではBashとMySQL 8.4を使い、InnoDBのappdbを同じバージョンの別サーバーへ復元します。

取得前に決めるのは、止められる変更と復元先

--single-transactionは、InnoDBの一貫した読み取りを利用する指定です。通常の行の更新を続けながら取得できますが、取得中のテーブル構造変更まで安全になるわけではありません。バックアップの時間帯にはALTER TABLE、DROP TABLE、TRUNCATE TABLEなどのDDLを実行しない運用にします。MyISAMなどが混在するDBでは、同じ指定だけで全テーブルの整合性を確保できません。

接続先の確認とエンジンの確認は、取得ジョブを組む前に行います。

SELECT @@hostname, @@server_uuid, VERSION();
SELECT TABLE_NAME, ENGINE
FROM information_schema.TABLES
WHERE TABLE_SCHEMA='appdb' AND TABLE_TYPE='BASE TABLE';

復元先は、本番と接続経路を分けた検証専用のMySQLにします。「mysql testdb < dump.sql」とDB名を変えるだけでは隔離になりません。dumpにUSE appdbが含まれていれば接続後に切り替わります。ビューやルーチンにもappdb.itemsのような修飾名が残り得るため、ここでは別サーバーに同じappdbという名前で戻します。

検証サーバーには本番アプリの接続設定、メール送信、外部連携、アプリ側のcronを持ち込まないでください。MySQLのイベントも自動実行させないよう、検証サーバーの起動設定をevent_scheduler=DISABLEDにします。これは起動時の設定です。稼働中にSETでDISABLEDへ切り替えることはできません。詳しくは公式のEvent Scheduler設定で確認できます。

失敗したdumpを完成ファイルにしない保存スクリプト

認証情報はコマンドの引数に直接書かず、実行ユーザーだけが読める設定ファイルに保存します。次は/etc/mysql/backup-client.cnfの書式例です。実際の接続先と専用アカウントに置き換え、ファイルの権限を600にします。

[client]
user=backup_reader
password="REPLACE_WITH_YOUR_PASSWORD"
host=SOURCE_DB_HOST
port=3306
protocol=TCP
default-character-set=utf8mb4

必要な権限は、テーブルだけを取るか、ビュー・トリガー・ルーチン・イベントも含めるかで変わります。下の例はすべてを含めるため、権限不足を無視して先へ進めないでください。特にルーチン取得の権限要件はMySQL 8.4の–routines説明に合わせて管理者と確認します。一般的な設定操作はMySQLのユーザー作成・権限付与で確認できます。

次をbackup.shとして保存します。Linux上のBash、mysqldump、gzip、sha256sum、mktempが必要です。保存先はWeb公開ディレクトリの外に用意し、既存ディレクトリも実行ユーザーだけが扱える権限にしてください。スクリプトのumaskは、既存の広い権限を直す機能ではありません。

#!/usr/bin/env bash
set -euo pipefail
umask 077

config=${1:?absolute path to client.cnf}
backup_root=${2:?absolute path to private backup directory}
database=${3:?database name}
[[ "$config" = /* && "$backup_root" = /* && -f "$config" ]]
[[ "$database" =~ ^[A-Za-z0-9_]+$ ]]
mkdir -p "$backup_root"
run_dir=$(mktemp -d "$backup_root/$(date -u +%Y%m%dT%H%M%SZ).XXXXXX")
trap 'printf "FAILED: %s\n" "$run_dir" >&2' ERR

mysqldump --defaults-file="$config" \
  --single-transaction --quick --routines --triggers --events \
  --set-gtid-purged=OFF --no-tablespaces --hex-blob \
  --databases "$database" 2>"$run_dir/dump.stderr" \
  | gzip >"$run_dir/database.sql.gz.partial"
gzip -t "$run_dir/database.sql.gz.partial"
mv "$run_dir/database.sql.gz.partial" "$run_dir/database.sql.gz"
(cd "$run_dir" && sha256sum database.sql.gz > SHA256SUMS)
printf 'dump-complete; restore-test-pending\n' >"$run_dir/status.txt"
printf '%s\n' "$run_dir"

パスとDB名を渡して実行します。以下のパスは配置例です。

bash /opt/backup/backup.sh /etc/mysql/backup-client.cnf /private/backups appdb

処理・指定	この例での役割
set -euo pipefail	mysqldump側の失敗でもパイプ全体を失敗にする
–routines・–events・–triggers	データ以外の定義も取得対象にする。トリガーは既定でも取得されるが、意図を明示
–hex-blob	バイナリ列を16進表現で出力する
–set-gtid-purged=OFF	この隔離した論理復元では元サーバーのGTID履歴を設定しない
.partial → .gz	取得とgzip検査が成功した世代だけ完成名にする
status.txt	取得済みだが復元テスト待ち、と明記する

GTIDの指定は、レプリカ構築にもそのまま流用できる設定ではありません。また、--no-tablespacesを使うこの例は、テーブルスペースの構成まで再現する手順にはなっていません。レプリケーションや独自の配置を使うDBでは、復旧設計に合わせて取得対象を見直します。

失敗時はFAILEDと世代ディレクトリが標準エラーへ出ます。dump.stderrと.partialは調査用に残りますが、復元用の完成世代として扱いません。gzip -tやSHA256SUMSが通っても、確認できるのは圧縮ファイルの破損・変更の有無です。DBとして使えるかは次の復元で確かめます。

隔離サーバーの識別子を確かめてから、同名DBへ戻す

検証用の接続設定を/etc/mysql/restore-test.cnfへ別に作り、ホスト名・ポート・アカウントを検証環境のものにします。本番用設定をコピーしたままにしないでください。検証サーバーのUUIDは、先に管理情報と照合して控えます。次のスクリプト中の値は、その確認済みUUIDに置き換えます。

#!/usr/bin/env bash
set -euo pipefail
# 検証専用サーバーへの接続だけを記載した設定ファイル
restore_config=/etc/mysql/restore-test.cnf
backup_dir=/private/backups/REPLACE_WITH_RUN_DIRECTORY
expected_uuid=REPLACE_WITH_ISOLATED_SERVER_UUID

actual_uuid=$(mysql --defaults-file="$restore_config" -Nse 'SELECT @@server_uuid')
[[ "$actual_uuid" = "$expected_uuid" ]]
[[ $(mysql --defaults-file="$restore_config" -Nse "SELECT @@global.event_scheduler") = DISABLED ]]
[[ $(mysql --defaults-file="$restore_config" -Nse "SELECT COUNT(*) FROM information_schema.SCHEMATA WHERE SCHEMA_NAME='appdb'") = 0 ]]
(cd "$backup_dir" && sha256sum -c SHA256SUMS)
gzip -t "$backup_dir/database.sql.gz"
gzip -dc "$backup_dir/database.sql.gz" | mysql --defaults-file="$restore_config"

appdbが既にある場合、この例は停止します。前回の検証DBを消す判断まで自動化しないためです。初回と同じ条件で試すなら、新しい検証インスタンスを用意します。dumpには既存オブジェクトを置き換えるSQLが含まれるので、接続先が合っていることを確認せず入力しないでください。

--databases appdbで取得したファイルにはDBの作成・選択も入ります。復元先で別名のDBを指定して回避しようとせず、SQL形式dumpの読み込み方法に沿って入力します。途中でエラーになった場合は、出来上がった一部のテーブルを見て成功扱いにせず、エラー原因と検証環境を整えてやり直します。

件数だけでなく、文字・NULL・定義と動作を照合する

復元コマンドが正常終了したら、アプリで使う代表的なデータを確認します。次は検証用のitems、audit、item_names、item_countというオブジェクトの照合例です。実DBでは名前と確認列を置き換え、重要なテーブル・業務処理を選びます。

SELECT id,label,memo IS NULL AS memo_is_null,HEX(payload) AS payload_hex FROM appdb.items ORDER BY id;
SELECT COUNT(*) AS items_count FROM appdb.items;
SELECT COUNT(*) AS audit_count FROM appdb.audit;
SELECT * FROM appdb.item_names ORDER BY id;
CALL appdb.item_count();
SELECT TRIGGER_NAME FROM information_schema.TRIGGERS WHERE TRIGGER_SCHEMA='appdb';
SELECT EVENT_NAME,STATUS FROM information_schema.EVENTS WHERE EVENT_SCHEMA='appdb';
SELECT @@global.event_scheduler;

件数は、取得した時点と対応する値で比較します。稼働中の本番を後からSELECT COUNT(*)しても、その間に行が増減していれば一致しません。更新を止められる検証データで基礎確認をしたうえで、本番ではスナップショット時点の照合方法や、変更されない期間の集計を決めてください。

確認対象	確認すること
代表行	日本語・絵文字が読める、NULLが空文字になっていない、HEXでバイナリが一致する
ビュー・ルーチン	存在するだけでなくSELECT・CALLが動く。必要なDEFINERアカウントと権限も確認
トリガー	検証環境でテスト行を追加し、期待する副作用が出る
イベント	定義が戻っている一方、検証中に自動実行されていない
アプリ	外部送信を止めた検証設定で、読み取りと代表的な更新ができる

ルーチンやイベントの取得指定は公式のストアドプログラムのdump説明も参照してください。appdbだけのdumpには、MySQL全体のユーザー・権限やアプリのアップロードファイルは含まれません。別途必要なものがそろって初めて環境全体を戻せます。

この記事のコードは、MySQL 8.4.11の別々のDockerインスタンスで、同名appdbを使って確認しています。日本語・絵文字・NULL・バイナリ、ビュー・ルーチン・トリガー・イベント定義を照合し、復元先への追加が元DBを変更しないことも確認しました。存在しないDBを取得する失敗ケースでは、圧縮処理が動いても完成ファイルとstatus.txtが作られないことを確かめています。これは小さな静的データでの確認です。自分のDBの復旧時間とアプリ動作は、実際の量・構成で測ります。

定期実行は取得の自動化。復元記録と世代保持も別に用意する

手動で取得と復元が通ったら、バックアップ用ユーザーのcrontabへ実行行を入れます。次は毎日2時30分の配置例です。時刻はcronが動くサーバーのタイムゾーンで解釈されるので、実行環境で確認してください。

SHELL=/bin/bash
PATH=/usr/local/bin:/usr/bin:/bin
30 2 * * * /bin/bash /opt/backup/backup.sh /etc/mysql/backup-client.cnf /private/backups appdb >> /private/backups/cron.log 2>&1

cronを登録した後は、実行時刻に新しい世代ができること、失敗時の通知を担当者が受け取れることを確かめます。この行自体にはメール・監視サービスへの通知機能はありません。ログを残すだけで放置せず、終了失敗や新しい世代がない状態を、使っている監視へつないでください。処理が実行間隔を超える場合は、重複実行を防ぐ仕組みも必要です。

世代	取得	隔離復元	照合・アプリ	保持判断
例：20261015T173000Z…	成功・サイズとハッシュ記録	成功・開始/終了時刻を記録	確認した項目と未確認項目を記録	復元確認済みの世代として保持
例：翌日の世代	成功	未実施	未実施	前の確認済み世代を消さない

新しいファイルができたことだけを条件に、古い世代を削除しないでください。残す期間・必要容量・復元確認済みの世代数を先に決め、削除候補を確認してから世代整理を行います。保存先の障害に備えた別媒体のコピーや暗号化も、認証情報を含むバックアップの扱いとして設計します。

まずは一つの世代を別MySQLへ戻し、重要な読み書きができるところまで進めましょう。取得時刻より後の更新まで戻す必要がある場合は、バイナリログを使う復旧方式も含めて設計します。