2026年5月31日

サーバーのMySQLバックアップを自動化する完全手順

バックアップ・監視

はじめに

MySQL/MariaDBの本番運用では「論理バックアップ（mysqldump）」「物理バックアップ（xtrabackup/mariabackup）」「バイナリログ」の3層を組み合わせることが推奨されます。それぞれリストア速度・粒度・容量のトレードオフが異なるためです。

本記事では実運用で頻出するスクリプト構成、世代管理、Point-in-Time Recoveryまで一気通貫で解説します。

症状・背景

mysqldumpだけで運用していてリストアに半日かかる
バイナリログを取得しておらず障害発生時刻までしか戻せない
バックアップが本当に復旧できるか検証していない
日次・週次・月次の世代管理が手作業で漏れがある

手順・設定方法

ステップ1: 専用バックアップユーザー作成

# 最小権限のバックアップユーザー
mysql -uroot -p <<'SQL'
CREATE USER 'backup'@'localhost' IDENTIFIED BY 'StrongPass!2026';
GRANT SELECT, RELOAD, PROCESS, LOCK TABLES, SHOW VIEW, EVENT, TRIGGER,
      REPLICATION CLIENT, REPLICATION SLAVE ON *.* TO 'backup'@'localhost';
FLUSH PRIVILEGES;
SQL
# パスワードをmy.cnfに格納（cliからの漏洩防止）
sudo install -m 600 /dev/null /etc/mysql/backup.cnf
sudo tee /etc/mysql/backup.cnf >/dev/null <<EOF
[client]
user=backup
password=StrongPass!2026
EOF

ステップ2: 論理バックアップスクリプト

#!/bin/bash
# /usr/local/sbin/mysql-backup.sh
set -euo pipefail
DEST=/var/backups/mysql
DATE=$(date +%Y%m%d-%H%M)
mkdir -p "$DEST"
# 全DBを単一トランザクションでダンプ（InnoDB前提）
mysqldump --defaults-extra-file=/etc/mysql/backup.cnf \
  --single-transaction --quick --routines --triggers --events \
  --master-data=2 --all-databases | gzip -9 > "$DEST/full-$DATE.sql.gz"
# 30世代を超えたものを削除
ls -1t "$DEST"/full-*.sql.gz | tail -n +31 | xargs -r rm

ステップ3: 物理バックアップとバイナリログ

# xtrabackup（Percona）で高速物理バックアップ
sudo apt-get install -y percona-xtrabackup-80
xtrabackup --defaults-file=/etc/mysql/my.cnf --user=backup \
  --password=StrongPass!2026 --backup --target-dir=/var/backups/xtra/$(date +%F)
xtrabackup --prepare --target-dir=/var/backups/xtra/$(date +%F)
# バイナリログを有効化（my.cnf）
echo -e "[mysqld]\nlog_bin=/var/log/mysql/binlog\nbinlog_expire_logs_seconds=604800\nserver_id=1" \
  | sudo tee /etc/mysql/conf.d/binlog.cnf
sudo systemctl restart mysql

ステップ4: cron登録とリストア検証

# crontab: 毎日2時に論理、土曜3時に物理
sudo tee /etc/cron.d/mysql-backup <<'EOF'
0 2 * * * root /usr/local/sbin/mysql-backup.sh
0 3 * * 6 root /usr/local/sbin/mysql-xtra-backup.sh
EOF
# 月1回、復元検証用のテスト復元（別サーバー or Docker）
gunzip -c /var/backups/mysql/full-20260514-0200.sql.gz \
  | mysql -h test-host -u root -p testrestore
# バイナリログでPoint-in-Time Recovery
mysqlbinlog --start-datetime="2026-05-14 02:01:00" \
  --stop-datetime="2026-05-14 14:30:00" /var/log/mysql/binlog.000123 \
  | mysql -u root -p

注意事項

--single-transactionはInnoDB専用。MyISAMが混在する場合は--lock-tablesが必要だが書き込みが止まる
ダンプファイルは必ずgzipまたはzstdで圧縮。テキストSQLは圧縮効率が極めて高い
バイナリログのディスク容量に注意。binlog_expire_logs_secondsで世代管理する
リストア検証していないバックアップは「存在しないバックアップ」と同等。最低月1回は復元テスト

まとめ

1. 3層バックアップ: 論理＋物理＋バイナリログの組み合わせがベストプラクティス

2. --single-transaction: InnoDBで一貫性のあるホットバックアップを実現

3. xtrabackup: 物理バックアップで大規模DBでも高速リストア

4. バイナリログ: PITRで任意時点まで復旧可能

5. リストア検証: バックアップは検証して初めて完了。月1で必ずテスト

Post Views: 13