• HOME
  • MySQLのフルバックアップを自動化する方法｜mysqldump＋cron・世代管理・リストア確認まで

2026年5月31日

  • シェア
  • ツイート

    • 2026年5月31日

    MySQLのフルバックアップを自動化する方法｜mysqldump＋cron・世代管理・リストア確認まで

    MySQLのフルバックアップは、mysqldump --single-transaction で整合性の取れたダンプを取り、パスワードはオプションファイルに置き、cronで毎日実行して古い世代を自動削除する、という組み立てが基本です。この記事では、専用ユーザーの作成からバックアップスクリプト、cron登録、世代管理、そして「本当に戻せるか」を確かめるリストアテストまでを順に設定できるようにします。

    全体の設計：何を、どこまで自動化するか

    自動化する前に、次の4点を決めておくと後で迷いません。

    • 対象：WordPressなどアプリのデータベースをデータベース単位で取るのか、サーバー上の全データベースを取るのか
    • 頻度と保持期間：1日1回で7日分、など。保持期間は「障害に気づくまでの日数」より長くします
    • 保存先：同じディスクだけではディスク故障やサーバー侵害で一緒に失われます。最低でも別サーバーかオブジェクトストレージへコピーします
    • 確認方法：ファイルが作られたことではなく、別のデータベースに戻して中身を確認できたことを「成功」とみなします

    この記事では、データベースごとに1ファイル（gzip圧縮）を作る方式にします。--all-databases で1ファイルにまとめると、特定のサイトだけ戻したいときに巨大なファイルから切り出す手間がかかり、mysql システムスキーマまで含むため別バージョンのサーバーへ戻すときに衝突しやすいからです。

    バックアップ専用のMySQLユーザーを作る

    rootのような強い権限のアカウントをスクリプトで使うと、認証情報が漏れたときの被害が大きくなります。読み取りとダンプに必要な権限だけを持つユーザーを作ります。

    CREATE USER 'backup'@'localhost' IDENTIFIED BY 'your-strong-password';
GRANT SELECT, SHOW VIEW, TRIGGER, LOCK TABLES, EVENT, PROCESS ON *.* TO 'backup'@'localhost';
-- バイナリログ位置を記録する(--source-data)場合は RELOAD も追加
-- GRANT RELOAD ON *.* TO 'backup'@'localhost';

    PROCESS は、MySQL 8.0.21以降のmysqldumpがテーブルスペース情報を読むために要求します。付けたくない場合は --no-tablespaces オプションを指定すれば不要です。ストアドルーチンをダンプするには全体への SELECT（またはMySQL 8.0.20以降の SHOW_ROUTINE）が必要で、上の SELECT ON *.* で満たしています。

    パスワードはオプションファイルに置く

    mysqldump -p'password' のようにコマンドラインへパスワードを書くと、実行中に ps コマンドで他のユーザーから見えたり、シェルの履歴に残ったりします。MySQL自身も「コマンドラインでのパスワード指定は安全でない」と警告を出します。代わりに、所有者だけが読めるオプションファイルを用意します。

    sudo install -d -m 700 -o root -g root /etc/mysql-backup
sudo tee /etc/mysql-backup/backup.cnf > /dev/null <<'EOF'
[client]
user=backup
password=your-strong-password
EOF
sudo chmod 600 /etc/mysql-backup/backup.cnf

    このファイルは --defaults-extra-file=/etc/mysql-backup/backup.cnf で読み込みます。このオプションはコマンドの最初の引数に置かないとエラーになる点がつまずきやすいところです。[client] セクションに書くと mysql と mysqldump の両方が読むので、後のリストアテストでも同じファイルを使えます。

    Oracle版MySQLには mysql_config_editor で ~/.mylogin.cnf に資格情報を保存し --login-path で呼び出す方法もあります。ただしこれは難読化であって暗号化ではないため、ファイル権限で守る必要がある点は同じです。

    動作確認として、まず手動で接続できるかを確かめます。

    sudo mysql --defaults-extra-file=/etc/mysql-backup/backup.cnf -e "SHOW DATABASES;"

    mysqldumpの主なオプションと --single-transaction の意味

    InnoDBのテーブルに対して --single-transaction を付けると、ダンプ開始時点のスナップショットを1つのトランザクション内で読み出します。サイトを止めず、テーブルロックもかけずに、ある一時点で整合したデータを得られるのが利点です。

    注意点が2つあります。1つ目は、MyISAMなどトランザクション非対応のエンジンにはこの効果が及ばないことです。古いWordPressのテーブルがMyISAMのまま残っていないか、次のクエリで確認しておきます。

    SELECT table_schema, table_name, engine
FROM information_schema.tables
WHERE engine <> 'InnoDB' AND table_schema NOT IN ('mysql','information_schema','performance_schema','sys');

    2つ目は、ダンプ中に ALTER TABLE や TRUNCATE TABLE などのDDLが走ると整合性が崩れたりダンプが失敗したりすることです。プラグインの更新やスキーマ変更を行う時間帯とは重ならないようにスケジュールします。

    あわせて使うオプションは次のとおりです。

    • --routines と --events：ストアドプロシージャ・関数とイベントは既定では含まれないため明示します（トリガーは既定で含まれます）
    • --hex-blob：バイナリ列を16進で出力し、文字コード変換による破損を避けます
    • --default-character-set=utf8mb4：絵文字を含むWordPressのデータを正しく扱うため
    • --source-data=2：バイナリログの位置をコメントとして記録します。フルバックアップ以降の変更をバイナリログで追いかけるポイントインタイムリカバリを行う場合に使います（MySQL 8.0.26以降の名称で、それ以前は --master-data）

    データベース名を --databases を付けずに1つだけ指定すると、ダンプに CREATE DATABASE と USE が入りません。これにより、後で別名のデータベースへ戻してテストしやすくなります。

    バックアップスクリプトを作る

    以下は、システムスキーマを除く全データベースをそれぞれ圧縮して保存し、成功したものだけを正式なファイル名にするスクリプトです。

    #!/bin/bash
# /usr/local/sbin/mysql-backup.sh
set -euo pipefail
umask 077

CNF=/etc/mysql-backup/backup.cnf
DEST=/var/backups/mysql
KEEP_DAYS=7
STAMP=$(date +%Y%m%d-%H%M)

mkdir -p "$DEST"

DBS=$(mysql --defaults-extra-file="$CNF" -N -B -e "SHOW DATABASES" \
  | grep -Ev '^(information_schema|performance_schema|mysql|sys)$')

for DB in $DBS; do
  OUT="$DEST/${DB}_${STAMP}.sql.gz"
  TMP="$OUT.part"
  mysqldump --defaults-extra-file="$CNF" \
    --single-transaction --routines --events --hex-blob \
    --default-character-set=utf8mb4 \
    "$DB" | gzip -c > "$TMP"

  # 圧縮ファイルの整合性と、ダンプが最後まで書かれたかを確認
  gzip -t "$TMP"
  if ! zcat "$TMP" | tail -n 1 | grep -q 'Dump completed'; then
    echo "ERROR: dump of $DB looks incomplete" >&2
    exit 1
  fi
  mv "$TMP" "$OUT"
  echo "OK: $OUT ($(du -h "$OUT" | cut -f1))"
done

# 保持期間を過ぎたファイルを削除
find "$DEST" -name '*.sql.gz' -type f -mtime +"$KEEP_DAYS" -delete
find "$DEST" -name '*.part' -type f -mtime +1 -delete

    ポイントを補足します。

    • set -o pipefail がないと、mysqldump | gzip の途中で mysqldump が失敗しても gzip の終了コード0が採用され、壊れたファイルが「成功」として残ります。パイプを使うバックアップでは最重要の1行です
    • umask 077 で、作成されるダンプを root 以外が読めないようにします。ダンプにはユーザーのメールアドレスやパスワードハッシュが含まれます
    • 書き込み中は .part という名前にし、検証が通ってから mv で改名します。途中で止まったファイルを正常なバックアップと取り違えないためです
    • mysqldumpは既定でダンプの最終行に -- Dump completed on ... というコメントを出すので、これを完了の目印に使っています

    権限を設定して手動で一度実行し、出力とファイルを確認します。

    sudo install -m 700 -o root -g root mysql-backup.sh /usr/local/sbin/mysql-backup.sh
sudo /usr/local/sbin/mysql-backup.sh
ls -lh /var/backups/mysql/

    cronで定期実行する

    /etc/cron.d/ にファイルを置くと、ユーザー名の列を含めて管理できます。

    # /etc/cron.d/mysql-backup
SHELL=/bin/bash
PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin
30 3 * * * root flock -n /run/mysql-backup.lock nice -n 10 ionice -c2 -n7 /usr/local/sbin/mysql-backup.sh >> /var/log/mysql-backup.log 2>&1
    • cronは最小限の PATH で動くため、手動では動くのにcronでは mysqldump: command not found になることがあります。PATH を明示するか、コマンドをフルパスで書きます
    • flock -n は、前回のバックアップが長引いて次の実行と重なるのを防ぎます
    • nice と ionice で優先度を下げ、アクセスの少ない時間帯でもサイトの応答を圧迫しにくくします

    ログは /var/log/mysql-backup.log に追記されるので、logrotateの対象にもしておきます。失敗に気づけないバックアップは、ないのと同じです。スクリプトの終了コードが0以外のときにメールやチャットへ通知する仕組み、または「最新ファイルが26時間以上更新されていなければ警告する」監視を加えておくと安心です。

    別の場所へコピーする

    同じサーバーのディスクに置いたバックアップは、ディスク障害、誤操作による削除、不正侵入によるデータ破壊に対して無力です。スクリプトの最後、またはcronの別ジョブで、別サーバーやオブジェクトストレージへ転送します。

    rsync -a --delete-after /var/backups/mysql/ backup@backup-host:/srv/backups/db01-mysql/

    転送先では、元サーバーから削除・上書きができない形（転送先側で世代を持つ、オブジェクトストレージのバージョニングやオブジェクトロックを使うなど）にしておくと、ランサムウェアなどで元サーバーが乗っ取られた場合にも過去の世代が残ります。

    リストアテストで「戻せること」を確認する

    バックアップの価値は、戻せるかどうかで決まります。月に1回程度、最新のダンプを別名のデータベースへ戻して中身を比べます。テスト用データベースの作成と削除には管理者権限が要るため、ここでは管理者アカウント（Ubuntuなら sudo mysql）で実行します。

    LATEST=$(ls -1t /var/backups/mysql/wordpress_*.sql.gz | head -n 1)
sudo mysql -e "CREATE DATABASE restore_test CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;"
zcat "$LATEST" | sudo mysql restore_test

    復元できたら、主要テーブルの件数を本番と比べます。information_schema.tables の TABLE_ROWS はInnoDBでは概算値なので、比較には COUNT(*) を使います。

    SELECT (SELECT COUNT(*) FROM wordpress.wp_posts)    AS live_posts,
       (SELECT COUNT(*) FROM restore_test.wp_posts) AS restored_posts,
       (SELECT COUNT(*) FROM wordpress.wp_options)    AS live_options,
       (SELECT COUNT(*) FROM restore_test.wp_options) AS restored_options;

    ダンプ取得後に記事が追加されていれば本番側が多くなるのは正常です。大きく減っている、テーブルが欠けている、文字化けしている、といった差がないかを見ます。確認が終わったら DROP DATABASE restore_test; で削除します。データベース名を取り違えると本番を消してしまうため、DROP の前に対象名をもう一度確認してください。

    リストアにかかった時間も記録しておくと、障害時に「復旧まで何分かかるか」を事前に答えられます。

    データが大きくなったら

    mysqldumpはSQL文を1スレッドで出力する論理バックアップのため、数十GBを超えるとダンプにもリストアにも時間がかかるようになります（2026年10月現在）。その規模では、並列でダンプ・ロードできるMySQL Shellのダンプユーティリティや、データファイルを直接コピーする物理バックアップのPercona XtraBackupを検討します。また、MariaDBでは同等のコマンドが mariadb-dump で、一部のオプション名がMySQLと異なるため、--help で確認してから使ってください。

    まとめ

    • フルバックアップは mysqldump --single-transaction でサイトを止めずに取得する。InnoDB以外のテーブルとダンプ中のDDLには注意する
    • 権限を絞った専用ユーザーを作り、パスワードは権限600のオプションファイルに置いて --defaults-extra-file で読み込む
    • スクリプトでは pipefail、.part からの改名、Dump completed の確認で「壊れたファイルを成功扱いしない」仕組みを作る
    • cronでは PATH、flock、ログと通知を整え、find -mtime で世代を管理する
    • 別の場所へコピーし、定期的に別名データベースへ戻して件数を比べるリストアテストまで行って、初めて自動化が完成する
    Views: 123

    お気軽にご相談ください

    お見積りへ お問い合わせへ
    閉じる
    ▲