2026年5月28日

MySQLデータベースの自動バックアップ設定：方式の選び方、systemdタイマー、バイナリログで直前まで戻す方法

バックアップ・監視

MySQL の自動バックアップは、「毎日 mysqldump を取る」だけでも始められますが、それでは最後のバックアップから障害までの数時間〜1日分のデータが失われます。この記事では、まずバックアップ方式の選び方を整理し、mysqldump を systemd タイマーで毎日実行する設定を作ります。そのうえで、バイナリログを組み合わせて「誤って DELETE した直前の状態」まで戻すポイントインタイムリカバリ（PITR）の手順を説明します。

cron と mysqldump だけで世代管理する基本的な方法は別記事にあるため、ここでは方式の選び方、systemd での運用、PITR に重点を置きます。

バックアップ方式の選び方

MySQL のバックアップには、大きく分けて次の方式があります。

論理バックアップ（mysqldump）：SQL 文として書き出す。扱いやすく、バージョンの違うサーバーへも移しやすい。データが大きくなると、取得と復元に時間がかかる
論理バックアップ（MySQL Shell のダンプユーティリティ）：util.dumpInstance() などで並列に書き出し、util.loadDump() で読み込む。mysqldump より速いことが多いが、MySQL Shell の導入が必要
物理バックアップ：データファイルそのものをコピーする。Percona XtraBackup、MariaDB の mariadb-backup などのツールを使う。大きなデータベースでも復元が速い
スナップショット：LVM やクラウドのディスクスナップショット。手軽だが、取得のタイミングによっては整合性の確認が必要
マネージドサービスの自動バックアップ：Amazon RDS などでは、サービス側の機能で自動バックアップと PITR を設定できる

WordPress のような一般的なサイトであれば、まず mysqldump で始めて、データが大きくなり取得や復元の時間が問題になってきた段階で物理バックアップを検討する、という順番が現実的です。なお、mysqlpump は MySQL 8.0.34 で非推奨になり、8.4 で削除されています（2026年10月現在）。新しく組むなら選ばないほうがよいでしょう。

MariaDB を使っている場合、コマンド名は mariadb-dump（互換のため mysqldump も使える場合が多い）で、一部のオプション名が MySQL と異なります。この記事のオプションは MySQL 8.0 以降を前提にしているので、MariaDB では手元のマニュアルで対応する名前を確認してください。

バックアップ専用ユーザーとパスワードの置き場所

root でバックアップを取ると、スクリプトに強い権限のパスワードを置くことになります。読み取りに必要な権限だけを持つ専用ユーザーを作ります。

CREATE USER 'backup'@'localhost' IDENTIFIED BY 'your-strong-password';
GRANT SELECT, SHOW VIEW, TRIGGER, LOCK TABLES, EVENT, RELOAD, PROCESS, REPLICATION CLIENT
  ON *.* TO 'backup'@'localhost';

RELOAD と REPLICATION CLIENT は、後述の --flush-logs と --source-data でバイナリログの位置を記録するために使います。必要な権限は MySQL のバージョンや使うオプションによって変わることがあるので、権限不足のエラーが出たらメッセージに書かれた権限を確認して追加してください。

パスワードはコマンドラインに書かず、root だけが読めるオプションファイルに置きます。コマンドラインに書くと、ps の出力などからほかのユーザーに見える可能性があります。

# /etc/mysql-backup.cnf（chmod 600, 所有者 root）
[client]
user=backup
password=your-strong-password

バックアップスクリプト

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

DEST=/var/backups/mysql
KEEP_DAYS=14
STAMP="$(date +%Y%m%d-%H%M%S)"
OUT="$DEST/all-$STAMP.sql.gz"
trap 'rm -f "$OUT.part"' EXIT

mkdir -p "$DEST"

mysqldump --defaults-extra-file=/etc/mysql-backup.cnf \
  --single-transaction --source-data=2 --flush-logs \
  --routines --events --triggers --hex-blob \
  --default-character-set=utf8mb4 \
  --all-databases | gzip > "$OUT.part"

gzip -t "$OUT.part"
mv "$OUT.part" "$OUT"

find "$DEST" -maxdepth 1 -name 'all-*.sql.gz' -mtime +"$KEEP_DAYS" -delete

主なオプションの意味は次のとおりです。

--defaults-extra-file：先ほどのオプションファイルを読む。ほかのオプションより前に書く必要がある
--single-transaction：InnoDB のテーブルを、ロックをかけずに一貫した時点の状態で書き出す。MyISAM のテーブルには効かない
--source-data=2：ダンプした時点のバイナリログのファイル名と位置を、コメントとして先頭付近に書き込む。PITR の起点になる（8.0.26 より前は --master-data）
--flush-logs：ダンプの開始時に新しいバイナリログファイルに切り替え、境界を分かりやすくする
--routines --events --triggers：ストアドプロシージャ、イベント、トリガーも含める

set -o pipefail があるので、mysqldump が途中で失敗すれば、gzip が成功していてもスクリプトは失敗として止まります。これがないと「中身が途中で切れたバックアップ」が正常なものとして残ってしまいます。

WordPress のデータベースだけを取りたい場合は、--all-databases の代わりにデータベース名を指定します。GTID を有効にしているサーバーでは、--set-gtid-purged の扱いについて警告が出ることがあります。レプリケーションに使わないダンプなら --set-gtid-purged=OFF を付ける選択肢もありますが、用途に合わせてマニュアルで確認してください。

systemd タイマーで毎日実行する

cron でも動かせますが、systemd タイマーには「実行時刻にサーバーが止まっていたら、起動後に取り逃した分を実行する」「ログが journal にまとまる」といった利点があります。

# /etc/systemd/system/mysql-backup.service
[Unit]
Description=MySQL backup

[Service]
Type=oneshot
ExecStart=/usr/local/bin/mysql-backup.sh
Nice=10
IOSchedulingClass=idle

# /etc/systemd/system/mysql-backup.timer
[Unit]
Description=Daily MySQL backup

[Timer]
OnCalendar=*-*-* 03:00:00
Persistent=true
RandomizedDelaySec=10min

[Install]
WantedBy=timers.target

systemctl daemon-reload
systemctl enable --now mysql-backup.timer
systemctl list-timers mysql-backup.timer   # 次回の実行予定を確認
systemctl start mysql-backup.service       # 手動で一度実行して試す
journalctl -u mysql-backup.service -n 50   # 結果を確認

Persistent=true が「取り逃した分を実行する」設定です。Nice と IOSchedulingClass は、バックアップ中にサイトの応答が遅くなるのを和らげるためのものです。失敗時に通知したい場合は、[Unit] に OnFailure= で通知用のサービスを指定する方法があります。

バイナリログで直前の状態まで戻す

毎日3時のダンプだけでは、たとえば15時に誤って大量の行を削除した場合、3時の状態にしか戻せません。その間の12時間分の更新を取り戻すのがバイナリログです。

MySQL 8.0 以降では、バイナリログは既定で有効です。保持期間は binlog_expire_logs_seconds で決まり、既定は30日です。MariaDB では既定で無効なことが多いため、設定ファイルで有効にする必要があります。現在の状態は次のように確認できます。

SHOW VARIABLES LIKE 'log_bin';
SHOW VARIABLES LIKE 'binlog_expire_logs_seconds';
SHOW BINARY LOGS;

バイナリログはデータベースと同じサーバーにあるため、サーバーごと失われると一緒に消えます。ダンプファイルと同じように、定期的に別の場所へコピーしておくことが前提です。

PITR の流れは次のとおりです。作業は本番ではなく、復元用のサーバーかステージング環境で行います。

障害の直前に取られたダンプを復元する
ダンプの先頭付近から、記録されたバイナリログの位置を読む
その位置から、問題の操作の直前までのバイナリログを適用する

# 2. ダンプに記録されたバイナリログの位置を確認
zcat all-20261018-030000.sql.gz | head -n 100 | grep -m1 -E "CHANGE (MASTER|REPLICATION SOURCE) TO"

# 3. 問題の操作の位置を探す（行ベースの変更を読める形で表示）
mysqlbinlog --base64-output=DECODE-ROWS -v binlog.000124 | less

# 位置を確かめてから、起点から問題の直前までを適用
mysqlbinlog --start-position=157 --stop-position=987654 binlog.000124 \
  | mysql --defaults-extra-file=/root/restore.cnf

--start-position と --stop-position の数値は例です。手順2と3で確認した実際の値に置き換えてください。複数のバイナリログファイルにまたがる場合は、ファイルを古い順に並べて1回の mysqlbinlog に渡します。このとき --start-position は最初のファイルにだけ適用されます。時刻で区切る --stop-datetime もありますが、同じ秒に複数の操作があると境界があいまいになるため、位置で指定するほうが正確です。GTID を有効にしている環境では、GTID の扱いによって適用がスキップされる場合があるので、MySQL のマニュアルにあるポイントインタイムリカバリの章もあわせて確認してください。

復元テストを定期的に行う

--all-databases のダンプを流し込むと、ユーザーや権限を含めてサーバー全体が上書きされます。復元のテストは、本番とは別のサーバーやステージング環境で行ってください。

月に1回など頻度を決めて、次の点を確認しておくと、本番の障害時に手順書どおりに動けます。

最新のダンプが最後まで読み込めるか
WordPress のサイトがそのデータベースで表示できるか
ダンプとバイナリログで、指定した時点まで戻せるか
復元にかかった時間（障害時にどれくらい止まるかの目安になる）

まとめ

方式は mysqldump から始め、大きくなったら MySQL Shell のダンプユーティリティや物理バックアップを検討する。mysqlpump は新規には選ばない
専用ユーザーと権限600のオプションファイルを使い、パスワードをコマンドラインやスクリプトに書かない
--single-transaction --source-data=2 --flush-logs と pipefail で、一貫性があり失敗に気づけるダンプを取る
systemd タイマーの Persistent=true で取り逃しを防ぎ、journal で結果を確認する
バイナリログも別の場所へ保管し、ダンプと組み合わせて障害の直前まで戻せるよう、ステージングで練習しておく