MySQL のバックアップと復元 ― mysqldump・物理バックアップの基本

データベースの運用において、バックアップは最も重要な作業の一つです。障害やヒューマンエラーによるデータ損失に備え、定期的なバックアップ体制を構築しておく必要があります。

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

MySQL のバックアップ手法は大きく 2 種類に分かれます。論理バックアップは SQL 文としてデータをエクスポートする方式で、可搬性が高い反面、リストアに時間がかかります。物理バックアップはデータファイルそのものをコピーする方式で、大規模データベースでも高速に復元できるのが強みです。

mysqldump の基本操作

最もよく使われるバックアップツールが mysqldump です。コマンドラインから実行でき、データベース単位・テーブル単位で柔軟にバックアップを取得できます。

# データベース全体のバックアップ
mysqldump -u root -p mydb > mydb_backup.sql

# 特定テーブルのみバックアップ
mysqldump -u root -p mydb users orders > tables_backup.sql

# 全データベースのバックアップ
mysqldump -u root -p --all-databases > all_backup.sql

--single-transaction オプションを付けると、InnoDB テーブルに対してロックをかけずに一貫性のあるバックアップが取得できます。本番環境では必須ともいえるオプションです。

mysqldump -u root -p --single-transaction --routines --triggers mydb > mydb_backup.sql

--routines はストアドプロシージャ、--triggers はトリガーもバックアップに含めるためのオプションで、これらを忘れるとリストア後に動作が変わってしまうことがあります。

リストア（復元）の実行

mysqldump で取得したバックアップの復元は、mysql コマンドでファイルを読み込むだけです。

# データベースを復元
mysql -u root -p mydb < mydb_backup.sql

# データベースが存在しない場合は先に作成
mysql -u root -p -e "CREATE DATABASE mydb;"
mysql -u root -p mydb < mydb_backup.sql

大きなバックアップファイルをリストアする際は、あらかじめ SET GLOBAL max_allowed_packet や innodb_buffer_pool_size を調整しておくと、処理が安定します。

物理バックアップの方法

物理バックアップの代表的な手法は、MySQL を停止してデータディレクトリをコピーする方法です。

# MySQL を停止
sudo systemctl stop mysql

# データディレクトリをコピー
sudo cp -r /var/lib/mysql /backup/mysql_$(date +%Y%m%d)

# MySQL を再起動
sudo systemctl start mysql

停止が許容されない環境では、Percona XtraBackup のようなホットバックアップツールを使うことで、稼働中の InnoDB テーブルを無停止でバックアップできます。

バックアップ運用のポイント

バックアップは「取って終わり」ではありません。定期的にリストアテストを行い、復元可能であることを確認しておくことが極めて重要です。

バックアップの自動化については、シェルスクリプトと cron を組み合わせるのが一般的です。日次で mysqldump を実行し、圧縮してリモートストレージに転送するといった構成がよく採用されています。