1. はじめに

MySQLデータベースのバックアップとリストアは、データ保護の観点から極めて重要です。本記事では、MySQL標準ツールであるmysqldumpについて、その動作原理から実践的な使用方法まで詳しく解説します。

2. バックアップ方式の分類

MySQLのバックアップ方式は大きく分けて以下の2種類があります：

2.1 コールドバックアップ

データベースを停止し、OSレベルで物理ファイルをコピーする方式です。MySQLには専用のコールドバックアップツールがないため、手動でファイルをコピーします。

2.2 ホットバックアップ

データベースを稼働したままバックアップを行う方式で、実際の運用環境では主にこちらが採用されます。ホットバックアップにはさらに以下の2種類があります： - 論理バックアップ：MySQL標準のmysqldumpを使用 - 物理バックアップ：Percona社提供のXtraBackupを使用 中小規模のデータベースや、それほど忙しくないシステムではmysqldumpが適しています。

3. mysqldumpの動作原理

mysqldumpはSQLレベルのバックアップ機構を採用しており、データテーブルをSQLスクリプトファイルとしてエクスポートします。MySQLバージョン間の移行にも適しており、最も一般的なバックアップ方法です。 基本的な動作フローは以下の通りです：

mysqldumpの基本的な実行フロー：
1. 対象テーブルの構造を取得
2. CREATE TABLE文を生成
3. 全レコードをINSERT文に変換
4. 上記文をファイルに出力

注意：デフォルト設定では全テーブルのバックアップ完了までトランザクションが維持されるため、
Undo表スペースを長期間占有します。効率化のためには、テーブルごとにコミットする
方法もありますが、一貫性バックアップは保証できなくなります。

4. 一貫性バックアップと非一貫性バックアップ

4.1 非一貫性バックアップの原理

--single-transactionオプションを使用しない場合：

mysqldump -uadmin -p'password' targetdb --triggers --routines --events > /backup/targetdb.sql

実行原理：

1. 現在のGTID情報を取得
2. バックアップ対象のテーブルを特定
3. 全テーブルにグローバル読み取りロックを設定
4. 各テーブルをSHOW CREATE TABLEでバックアップ
5. 読み取りロックを解除

特徴： - バックアップデータの一貫性は保証されない - ロック時間はバックアップ容量に比例 - MyISAMなどの非トランザクションエンジンに適している

4.2 一貫性バックアップの原理

--single-transactionオプションを使用した一貫性バックアップ：

mysqldump -uadmin -p'password' targetdb --master-data=2 --single-transaction \
    --triggers --routines --events --log-error=/backup/error.log > /backup/targetdb.sql

実行原理：

1. FLUSH TABLESを実行（ロックなしでデータをディスクにフラッシュ）
2. FLUSH TABLES WITH READ LOCKでグローバル読み取りロックを設定
3. トランザクション分離レベルをRRに設定
4. 一貫性スナップショットトランザクションを開始
5. GTID情報を取得
6. グローバル読み取りロックを解除
7. セーブポイントを作成
8. 各テーブルをバックアップ（テーブルごとにセーブポイントにロールバック）
9. セーブポイントを解放

特徴： - ロック時間が短い - InnoDBエンジンで一貫性が保証される - 非トランザクションエンジンには適用されない

5. 実践的なバックアップコマンド

5.1 本番環境向け推奨コマンド

GTIDなしの一貫性バックアップ：

mysqldump -uadmin -p'password' targetdb --set-gtid-purged=OFF \
    --single-transaction --triggers --routines --events \
    --log-error=/var/log/backup_error.log > /backup/targetdb.sql

GTIDありの一貫性バックアップ：

mysqldump -uadmin -p'password' targetdb --single-transaction \
    --master-data=2 --triggers --routines --events \
    --log-error=/var/log/backup_error.log > /backup/targetdb.sql

テーブル構造のみのバックアップ：

mysqldump -uadmin -p'password' --no-data --databases db1 db2 \
    --triggers --routines --events > /backup/structure.sql

指定テーブルのみのバックアップ：

mysqldump -uadmin -p'password' dbname --tables table1 table2 \
    --set-gtid-purged=OFF > /backup/tables.sql

5.2 高度なバックアップオプション

CSV形式でのエクスポート：

mysqldump -h127.0.0.1 -uadmin -p'password' mydatabase target_table \
    -t -T /tmp/exports/ --fields-terminated-by=',' --fields-enclosed-by='"'

条件付きデータエクスポート：

mysqldump -uadmin -p'password' dbname tablename \
    --where="status='active' AND created_at > '2023-01-01'" \
    --single-transaction --skip-extended-insert > /backup/filtered_data.sql

複数データベースのバックアップ：

mysqldump -uadmin -p'password' --databases db1 db2 db3 \
    --single-transaction --routines --triggers > /backup/multiple_dbs.sql

6. 主なパラメータ詳細

パラメータ	説明	使用例
--single-transaction	一貫性バックkupを実行	推奨オプション
--master-data=[1|2]	バイナリログ位置情報を記録	レプリケーション設定時
--routines	ストアドプロシージャを含める	ロジックのバックアップ
--triggers	トリガーを含める	自動化ロジックの保持
--events	イベントを含める	定期実行タスクの保持
--no-data	構造のみをバックアップ	スキーマの移行
--quick	バッファリングせずに出力	大容量データ向け
--compress	通信を圧縮	リモートバックアップ時

7. リストア方法

7.1 フルリストア

mysql -uadmin -p'password' -h127.0.0.1 -P3306 < /backup/targetdb.sql

7.2 ログ記録付きリストア

mysql> tee /tmp/restore.log;
mysql> source /backup/targetdb.sql
mysql> exit

7.3 単一テーブルのリストア

フルバックアップファイルから特定のテーブルのみをリストアする場合：

1. バックアップファイルから対象テーブル関連のステートメントを抽出

grep -i 'target_table' /backup/full_backup.sql > /tmp/table_backup.sql

2. INSERT文のみを抽出

grep 'INSERT INTO `target_table`' /tmp/table_backup.sql > /tmp/restore_table.sql

3. リストア実行

mysql -uadmin -p'password' targetdb < /tmp/restore_table.sql

8. ベストプラクティス

• 本番環境では必ず--single-transactionを使用
• バックアップファイルは圧縮して保存
• 定期的なリストアテストを実施
• エラーログを必ず記録
• 大規模データベースでは分割バックアップを検討
• 重要データは複数世代保持