【MySQL】mysqldumpでバックアップ・リストアする方法

MySQL
2022.12.15

こんにちは、かつコーチです。

前回はレプリケーションの仕組みを解説しましたが、レプリケーションはあくまで「複製」であり、誤ってデータを消してしまった場合には複製先にも同じ削除が伝わってしまいます。

そのため、レプリケーションとは別にバックアップを取得しておくことが欠かせません。

今回は、MySQL標準のバックアップツールであるmysqldumpを使ったバックアップとリストアの方法を解説します。

mysqldumpとは

バックアップの基本的な考え方

mysqldumpとは、MySQLに標準で付属している、データベースの内容をSQL文の集まりとして書き出す論理バックアップツールです。

テーブル構造を再現するCREATE TABLE文と、データを復元するINSERT INTO文がテキストファイルとして出力され、そのファイルを実行するだけで元の状態に復元できます。

ファイルの中身がSQLというテキスト形式であるため、人が内容を確認しやすく、バージョン間の互換性も高いのが特徴です。

一方で、テーブルの物理ファイルをそのままコピーする物理バックアップ（XtraBackupなど）に比べると、リストアに時間がかかる傾向があります。

実務でよく使う基本コマンド

# 特定のデータベース1つをバックアップする
mysqldump -u root -p my_database > my_database_backup.sql

# 複数のデータベースをまとめてバックアップする
mysqldump -u root -p --databases db1 db2 > multi_db_backup.sql

# サーバー上の全データベースをバックアップする
mysqldump -u root -p --all-databases > all_databases_backup.sql

-uで接続ユーザー、-pでパスワード入力を求めるオプションを指定し、出力結果をリダイレクト（>）でファイルに書き出すのが基本的な使い方です。

実務で使うオプション

トランザクション整合性を保つ–single-transaction

InnoDBのテーブルをバックアップする際は、--single-transactionオプションを付けるのが基本です。

# InnoDBテーブルを整合性を保ったままバックアップする
mysqldump -u root -p --single-transaction --databases my_database > my_database_backup.sql

--single-transactionは、バックアップ開始時点のトランザクション分離レベルを利用して、テーブルをロックせずに一貫性のあるスナップショットを取得するオプションです。

これを付けないと、バックアップ中に他のクエリが書き込みできないよう、テーブルがロックされてサービスへの影響が出てしまいます。

レプリケーション用にバイナリログの位置を記録する

レプリケーションのレプリカを新規構築する際など、バックアップ取得時点のバイナリログ位置を記録しておきたい場合は--master-data（MySQL 8.0.26以降は--source-data）を使います。

# バックアップ取得時点のバイナリログ位置をコメントとして記録する
mysqldump -u root -p --single-transaction --source-data=2 --databases my_database > my_database_backup.sql

--source-data=2を指定すると、出力ファイルの先頭付近にCHANGE REPLICATION SOURCE TOのコメントが記録され、このバックアップからレプリカを構築する際の起点として使えます。

定期実行するバックアップスクリプトの例

#!/bin/bash
# daily_backup.sh：日次バックアップを取得し、7日より古いファイルを削除する
BACKUP_DIR="/var/backups/mysql"
DATE=$(date +%Y%m%d)

mysqldump -u backup_user -p"${MYSQL_BACKUP_PASSWORD}" \
    --single-transaction \
    --databases my_database \
    | gzip > "${BACKUP_DIR}/my_database_${DATE}.sql.gz"

find "${BACKUP_DIR}" -name "*.sql.gz" -mtime +7 -delete

gzipで圧縮しておくとファイルサイズを大きく抑えられ、findコマンドで古いバックアップを自動削除することで、ディスク容量の圧迫も防げます。

リストアの方法

バックアップファイルからの復元

# バックアップからデータベースを復元する（事前にDBを作成しておく）
mysql -u root -p -e "CREATE DATABASE IF NOT EXISTS my_database;"
mysql -u root -p my_database < my_database_backup.sql

# gzip圧縮されたバックアップから直接復元する
gunzip < my_database_backup_20260902.sql.gz | mysql -u root -p my_database

--all-databasesで取得したバックアップは、ファイル内にCREATE DATABASE文も含まれているため、リダイレクト先のデータベースを事前に作成しておく必要はありません。

❌Before：バックアップを取っただけで安心していた失敗

以前、私は毎晩mysqldumpでバックアップを取るcronジョブを設定し、「バックアップは万全」と思い込んでいた時期がありました。

# ❌ Before：バックアップは取っているが、リストアを試したことがない
mysqldump -u root -p --databases my_database > "/var/backups/mysql/backup_$(date +%Y%m%d).sql"

ある日、動作確認用にステージング環境へリストアしようとしたところ、ERROR 1064 (42000): You have an error in your SQL syntaxというエラーで復元が止まってしまいました。

原因を調べると、ディスク容量不足でバックアップファイルの書き込み途中でcronジョブが打ち切られており、何週間分もの「壊れたバックアップ」だけが溜まっていたことが判明しました。

✅After：リストア訓練までセットで運用する

# ✅ After：バックアップ後に自動でリストアテストまで行う
mysqldump -u root -p --single-transaction --databases my_database > "${BACKUP_DIR}/backup_$(date +%Y%m%d).sql"

# 検証用DBへリストアし、テーブル数などで正常性をチェックする
mysql -u root -p -e "DROP DATABASE IF EXISTS my_database_verify; CREATE DATABASE my_database_verify;"
mysql -u root -p my_database_verify < "${BACKUP_DIR}/backup_$(date +%Y%m%d).sql"

この一件以来、「バックアップを取ること」自体をゴールにせず、定期的に検証用データベースへリストアして復元できることまで確認するルールに変更しました。

バックアップは取得よりも、リストアできることの確認の方が重要だと痛感した経験です。

まとめ

この記事のポイント

  • mysqldumpはSQL文としてデータを書き出す論理バックアップツールで、内容の確認や環境間の互換性に強い
  • InnoDBテーブルのバックアップには--single-transactionでロックを避けつつ整合性を保つ
  • レプリカ構築用には--source-data（旧--master-data）でバイナリログ位置を記録できる
  • バックアップファイルは圧縮・世代管理し、定期的に古いものを削除する運用にする
  • バックアップは取得するだけでなく、定期的にリストアして復元できることまで確認する

次に読むべき記事

次回は「バイナリログの役割とポイントインタイムリカバリ」です。

mysqldumpによるバックアップと組み合わせることで、障害発生の直前まで復旧できる仕組みを解説します。

タグ: MySQL, 中級者向け, バックアップ

タイトルとURLをコピーしました