0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

@numazaki345

MySQL バックアップとリストア完全ガイド

0
Posted at

はじめに

MySQLの運用において、バックアップは「取るだけでなく、復元できて初めて意味がある」ものです。
本記事は『MySQL運用・管理 実践入門』第6章の内容をもとに、バックアップの種類から実際のリストア手順、ポイントインタイムリカバリ（PITR）まで実務で使える知識をまとめています。

1. バックアップの種類と方法

論理バックアップ vs 物理バックアップ

論理バックアップ 物理バックアップ
仕組み データをSQL形式でエクスポート データファイルをそのままコピー
メリット 一部テーブルのみリストア可、異なるバージョンへの移行が容易 バックアップ・リストアともに高速
デメリット 時間がかかる バージョン違いのサーバーへのリストア不可、部分リストアが難しい

オンライン vs オフラインバックアップ

  • オンラインバックアップ：DBを稼働したままバックアップ。InnoDBはロックなし、MyISAMはロックあり
  • オフラインバックアップ：DB停止後にバックアップ。確実だが停止が必要

フルバックアップ vs 増分バックアップ

MySQLは差分バックアップ非対応（フルまたは増分のみ）。

メリット デメリット
フルバックアップ リストアが簡単、管理しやすい 時間・容量が大きい
増分バックアップ 時間・容量を節約できる リストアが複雑になる

MySQLで使える主なバックアップツール

ツール 形式 手法 種類
mysqldump 論理 オンライン フル／増分
mysqlpump 論理 オンライン フル／増分
MyDumper 論理 オンライン フル／増分
MySQLShell ダンプユーティリティ 論理 オンライン フル／増分
MySQL物理ファイルコピー 物理 オフライン フル／増分
Percona XtraBackup 物理 オンライン フル／増分

2. 論理バックアップ：フルバックアップとリストア

mysqldump

古くから使われているMySQL公式の論理バックアップツール。シングルスレッドで動作するため、大規模DBでは時間がかかる点に注意。

フルバックアップ取得例

mysqldump \
  -h 127.0.0.1 \
  -P 3306 \
  -u root \
  -p \
  --single-transaction \
  --default-character-set=utf8mb4 \
  --source-data=2 \
  --routines --triggers --events \
  --hex-blob \
  --all-databases | gzip > source.dump.gz

主要オプション

オプション 意味
--single-transaction 一貫性のあるバックアップを取得（必須。ないとDB全体がロックされる）
--source-data レプリケーション用バイナリログ位置を出力
--all-databases すべてのスキーマを対象
--routines ストアドプロシージャ・ファンクションを含める
--triggers トリガーを含める
--hex-blob バイナリデータを16進数で出力
--default-character-set 文字コードを指定

リストア

cat source.dump | mysql -h restore_hostname -P 3306 -u root -p

MyDumper

OSSの並列バックアップツール。mydumperでバックアップ、myloaderでリストアする。

フルバックアップ取得例

mydumper \
  --host 127.0.0.1 \
  --port 3306 \
  --user username \
  --password password \
  --threads 4 \
  --triggers --events --routines \
  --chunk-filesize 100 \
  --compress \
  --outputdir /backup \
  --verbose 3 \
  --database db0,db1

並列オプションの使い分け

オプション 単位 特徴
--rows 行数 主キーが整数型のテーブルのみ分割可。1テーブルが大きい場合に有効
--chunk-filesize MB テーブル単位で並列化。均等なダンプファイルに分割される

リストア

myloader \
  --host restore_hostname \
  --port 3306 \
  --user username \
  --password password \
  --threads 2 \
  --queries-per-transaction 100 \
  --enable-binlog \
  --directory /backup \
  --verbose 3

終了コードが 0 であれば正常にリストア完了。

MySQLShell ダンプユーティリティ

MySQL公式の現行推奨ツール。util.dumpInstance() でバックアップ、util.loadDump() でリストアする。LOAD DATA LOCAL INFILEを使うため、INSERT系より高速。

# フルバックアップ
mysqlsh -u root -p -h 127.0.0.1 -P 3306 -e "util.dumpInstance(\"/backup\")"

# リストア
mysqlsh -u root -p -h 127.0.0.1 -P 3306 -e "util.loadDump(\"/backup\")"

リストア前に以下の設定が必要：

mysql> SET GLOBAL local_infile=ON;

3. 物理バックアップ：フルバックアップとリストア

MySQLサーバーの物理ファイルコピー

シンプルだが必ずオフラインが必要。

# データディレクトリの確認
mysql> SHOW VARIABLES LIKE 'datadir';

# サーバー停止
mysql> shutdown;

# ファイルコピー
cp -rp /var/lib/mysql /backup/20230211

リストア

# MySQL停止 → datadir配下を削除 → バックアップファイルを配置 → MySQL起動
内容
メリット シンプル、追加ソフト不要
デメリット MySQLサーバーの停止が必須、レプリカからのバックアップ時にレプリケーション遅延が発生しうる

Percona XtraBackup

InnoDBのクラッシュリカバリ機能を活用したオンライン物理バックアップツール。現場での事実上の標準。

バックアップの仕組み

  1. バックアップ開始
  2. LOCK INSTANCE FOR BACKUP 実行（DDLがブロックされる）
  3. Redoログのコピー開始（バックアップ完了まで継続）
  4. ibdファイルなど物理ファイルのコピー
  5. UNLOCK INSTANCE 実行
  6. バックアップ完了

注意：バックアップ中はDDL（ALTER TABLE等）がすべてブロックされる

フルバックアップ取得

xtrabackup \
  --backup \
  --target-dir=/backup \
  --user=root \
  --password=PASSWORD \
  --socket=/var/lib/mysql/mysql.sock

completed OK! が出力されれば完了。

リストア（2ステップ）

# ステップ1：リストア準備（一貫性の担保）
xtrabackup --prepare --target-dir=/backup

# ステップ2：ファイルを配置
xtrabackup --copy-back --target-dir=/backup

# 権限修正 → MySQL起動
chown -R mysql. /var/lib/mysql
systemctl start mysqld

現場でよく使うオプション構成

xtrabackup \
  --backup \
  --stream=xbstream \
  --user=root \
  --password=${PASS} \
  --ftwrl-wait-timeout=1800 \
  --ftwrl-wait-threshold=10 \
  --parallel=3 \
  --slave-info \
  --history \
  2>xtrabackup.log | pbzip2 -p${PARALLAEL_LEVEL} > xtrabackup_files.bz2

4. ポイントインタイムリカバリ（PITR）

PITRとは「任意の時点までデータを復元する」技術。誤ってDROPしてしまったテーブルの復元などに使う。

前提条件：フルバックアップ + バイナリログの両方が必要

全体フロー

1. 該当イベント（DROP等）のポジション or GTIDを特定
        ↓
2. フルバックアップからリストア
        ↓
3. バイナリログをDROP直前まで適用

ステップ1：該当イベントを特定

mysqlbinlog binlog.000033 | grep -B20 DROP

出力例から確認する値：

タイプ 確認する値
ポジション # at 10920 の直前の # at の値（例：10843）
GTID SET @@SESSION.GTID_NEXT = 'a625dd55-...:157' の値

注意：--stop-position には DROP イベント本文の直前のポジションを指定する。DROP自体のポジションを入れると DROP も実行されてしまう。

ステップ2：フルバックアップからリストア

Percona XtraBackupの場合：

systemctl stop mysqld
xtrabackup --prepare --target-dir=/backup
rm -rf /var/lib/mysql
xtrabackup --copy-back --target-dir=/backup
chown -R mysql. /var/lib/mysql
systemctl start mysqld

バックアップ完了時点のバイナリログ情報を確認：

cat xtrabackup_binlog_info
# 例：binlog.000031   157

ステップ3：バイナリログを適用

ポジション手法

mysqlbinlog \
  --start-position=157 \
  --stop-position=10920 \
  binlog.000031 binlog.000032 binlog.000033 | mysql -u root -p
  • --start-position：バックアップ完了時点のポジション
  • --stop-position：DROP直前のポジション（DROPイベント本文の手前の # at の値）

GTID手法

mysqlbinlog \
  --include-gtids='a625dd55-a9ee-11ed-877e-fa163f3f76e7:34-156' \
  binlog.000007 binlog.000008 binlog.000009 | mysql -u root -p
  • バックアップ完了時点のGTIDセット（例：:1-33）の次から、DROP直前のGTID（:157）の一つ前（:156）までを指定

レプリケーション機能を使ったPITR（応用）

START REPLICA UNTIL を使うと、指定した地点でレプリケーションSQLスレッドが自動停止するため、途中経過を確認しながら復元できる。

ポジション手法

CHANGE REPLICATION SOURCE TO
  SOURCE_HOST='hostname',
  SOURCE_LOG_FILE='binlog.000031',
  SOURCE_LOG_POS=157;

START REPLICA UNTIL SOURCE_LOG_FILE='binlog.000033', SOURCE_LOG_POS=10843;

GTID手法

CHANGE REPLICATION SOURCE TO
  SOURCE_HOST='hostname',
  SOURCE_AUTO_POSITION=1;

START REPLICA UNTIL SQL_BEFORE_GTIDS='a625dd55-a9ee-11ed-877e-fa163f3f76e7:157';

5. バイナリログのバックアップ

フルバックアップだけではPITRは成立しない。バイナリログのバックアップがセットで必須。

自動削除の管理

binlog_expire_logs_seconds（デフォルト：2592000秒＝30日）を超えると自動削除される。

-- 手動削除
PURGE BINARY LOGS TO 'binlog.000010';

書き込みが多い環境では30日分のバイナリログがディスクを圧迫することがあるため、値を小さくすることも検討する。

バックアップ方法

物理ファイルコピー

バイナリログを削除される前にコピーする方法。シンプルだが、サーバーがダウンすると直近のイベントが欠ける恐れがある。

# バイナリログの保存先確認
mysql> SHOW VARIABLES LIKE 'log_bin_basename';

# ファイルコピー
cp /var/lib/mysql/binlog.* /binlog_backup/

ライブバックアップ（推奨）

レプリカと同じ仕組みでリアルタイムにバイナリログを受信し続ける。

mysqlbinlog \
  --host='remote_mysql_server' \
  --port=3306 \
  --user='user' \
  --password='password' \
  --read-from-remote-server \
  --connection-server-id=999 \
  --raw \
  --result-file=/binlog_backup/ \
  --stop-never \
  binlog.000035 &
注意点 内容
再接続処理なし プロセスが落ちたら手動で再起動が必要
ファイル名指定が必要 フェイルオーバー時に新しいバイナリログファイル名を確認して再指定
準同期レプリケーション非対応 常に非同期レプリケーションと同等

増分バックアップ戦略

週1回 フルバックアップ取得 → FLUSH LOGS でバイナリログをローテート
                ↓
       以降はバイナリログのみ保存
                ↓
  PITRは「フルバックアップ + ローテート以降のバイナリログ」で対応

RTO（Recovery Time Objective：目標復旧時間）とRPO（Recovery Point Objective：目標復旧時点）の要件に合わせてフルバックアップの頻度を設計する。

まとめ

セクション 要点
バックアップの種類 論理／物理、オンライン／オフライン、フル／増分の組み合わせを理解する
mysqldump --single-transaction は必須。シングルスレッドのため大規模DBには不向き
MyDumper 並列バックアップ・リストアが可能。--rows と --chunk-filesize で粒度を調整
MySQLShell MySQL公式の現行推奨。LOAD DATA LOCAL INFILEで高速リストア
Percona XtraBackup オンライン物理バックアップの事実上の標準。バックアップ中はDDLがブロックされる点に注意
PITR フルバックアップ＋バイナリログの両方が必要。ポジション手法とGTID手法を使い分ける
バイナリログのバックアップ ライブバックアップで常時取得するのが堅牢。フル取得時に FLUSH LOGS してローテートする

実務での推奨構成

  • 定期フルバックアップ（Percona XtraBackupまたはMySQLShell）
  • バイナリログのライブバックアップを常時稼働
  • この2つを組み合わせてPITRに対応
0
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?