【AlmaLinux 9】MySQL初心者講座 第8回：mysqldumpによるバックアップ・復旧と完全自動化（最終回）

こんにちは！「LINUX工房」管理人の「リナックス先生」です。
全8回の「MySQL初心者講座」、ついに今回が最終回となります。第1回でAlmaLinux 9にMySQLをインストールしたあの日から、皆さんは本当に多くの知識を吸収してきましたね。

これまで学んできたCRUD操作やテーブル結合、インデックスチューニングは、すべてWebサービスを「快適に動かす」ための技術でした。しかし、システム運用において最も恐ろしいのは「遅いこと」ではなく、「データが消えてなくなること」です。

「間違えてWHERE句なしのDELETEを実行してしまった」「サーバーのSSDが突然クラッシュした」。こんな絶望的な状況から会社とユーザーを救えるのは、日頃から仕込んでおいた「バックアップ」だけなのです。

---

1. バックアップの基礎知識：「物理」と「論理」の違い

データベースのバックアップには、大きく分けて「物理バックアップ」と「論理バックアップ」の2種類があります。まずはこの違いを正確に理解しましょう。

1-1. 物理バックアップとは？

MySQLがデータを保存しているディレクトリ（通常は /var/lib/mysql/）の中にあるファイルを、OSの cp や tar コマンドでそのままコピーする方法です。
メリット： 大容量のデータベースでもバックアップと復旧が非常に高速です。
デメリット： コピー中にデータが書き込まれるとファイルが破損するため、原則としてMySQLサービスを停止（ダウンタイムの発生）する必要があります。また、OSやMySQLのバージョンが異なるサーバーへ復元する際に互換性の問題が起きやすいです。

1-2. 論理バックアップとは？（本講座で扱う手法）

データベースの中身を読み取り、それを復元するための「SQL文（CREATE TABLEやINSERT文の羅列）」としてテキストファイルに書き出す方法です。
メリット： 稼働中のデータベースを止めずに実行できます。出力結果は単なるテキスト（SQL）なので、異なるバージョンのMySQLや、別のサーバーへのお引越し（マイグレーション）が極めて容易です。
デメリット： 出力時と復元時にSQLを解析・実行するため、データ量が数百GBクラスになると非常に時間がかかります。

一般的なWebサイトや中規模システムにおいて、ダウンタイムなしで安全にバックアップを取るには、論理バックアップ（mysqldump）を使用するのが大原則です。

---

2. mysqldumpの基本構文と必須オプション（–single-transaction）

MySQLには、論理バックアップを取得するための標準ツール mysqldump コマンドが同梱されています。

2-1. 基本的な構文

mysqldump -u [ユーザー名] -p [データベース名] > [出力先ファイル名.sql]

例えば、第1回で作成した linux_koubou_db をバックアップするには、以下のように入力します。

mysqldump -u root -p linux_koubou_db > /tmp/linux_koubou_db_backup.sql

実行後に出力されたSQLファイルを覗いてみると、DROP TABLE IF EXISTS（もしテーブルがあれば消す）、CREATE TABLE（テーブルを作る）、そして INSERT INTO（データを入れる）というSQL文がびっしりと書き込まれていることがわかります。

2-2. プロが必ず使う最強のオプション「–single-transaction」

標準のまま mysqldump を実行すると、バックアップ中にデータが書き換わって矛盾が起きないように、MySQLはすべてのテーブルをロック（書き込み禁止）してしまいます。これでは、バックアップが終わるまでユーザーはWebサイトにデータを保存できなくなります。

これを回避するために、InnoDBストレージエンジンを使用している場合（AlmaLinux 9のMySQL 8.0ではデフォルトです）、必ず --single-transaction というオプションを付与します。

mysqldump -u root -p --single-transaction linux_koubou_db > /tmp/backup.sql

この魔法のオプションをつけることで、トランザクションの機能を利用し、「バックアップを開始した瞬間のスナップショット」を、テーブルをロックすることなく（＝サービスを止めることなく）安全に取得できるようになります。

---

3. 実践：データベースのバックアップと圧縮（gzip）

テキストデータであるSQLファイルは、データ量が増えると平気で数GBのサイズになります。ディスク容量を節約するため、バックアップは同時に圧縮（zip化）するのが基本です。

3-1. パイプ（|）を使ったリアルタイム圧縮

Linuxの gzip コマンドとパイプ | を組み合わせることで、ディスクに巨大なSQLファイルを書き出すことなく、メモリ上で圧縮しながら保存することができます。

mysqldump -u root -p --single-transaction --routines --triggers linux_koubou_db | gzip > /var/backups/mysql/linux_koubou_db_$(date +%Y%m%d).sql.gz

解説：

• --routines：ストアドプロシージャや関数もバックアップに含めます。
• --triggers：トリガーも含めます（デフォルトで有効ですが明示を推奨）。
• $(date +%Y%m%d)：ファイル名に今日の日付（例：20260510）を自動的に付与します。

---

4. 万が一の事態！データを「リストア（復旧）」する手順

「バックアップを取って満足する」のは二流のエンジニアです。いざという時に「確実に復旧（リストア）できる」ことをテストして初めて、それはバックアップと呼べます。

4-1. 圧縮されたバックアップからの復元

誤ってテーブルを削除してしまったと仮定し、先ほど取得した .sql.gz ファイルからデータを元の状態に戻します。
復元は mysqldump ではなく、通常の mysql コマンドを使用します。圧縮ファイルを zcat（解凍しながら出力するコマンド）で展開し、それをMySQLに流し込みます。

zcat /var/backups/mysql/linux_koubou_db_20260510.sql.gz | mysql -u root -p linux_koubou_db

4-2. リストア時の「あるある」エラーと対処法

復元時に最もよく遭遇するのが ERROR 1153 (08S01): Got a packet bigger than 'max_allowed_packet' bytes というエラーです。
これは、バックアップデータの中に非常に大きな文字列（長文のブログ記事や、画像データのバイナリなど）が含まれており、MySQLが「一度に受け取れるデータ量の上限」を超えてしまった場合に発生します。

この場合、MySQLのコマンドラインで一時的に上限を引き上げてからリストアを行います。

# 一時的に最大パケットサイズを256MBに引き上げる
mysql -u root -p -e "SET GLOBAL max_allowed_packet=268435456;"
# その後、再度リストアを実行
zcat backup.sql.gz | mysql -u root -p linux_koubou_db

---

5. .my.cnfを活用した「パスワードなし」の安全な実行方法

ここまで手動でバックアップを取ってきましたが、最終目標は「自動化」です。しかし、mysqldump -u root -pパスワード のように、コマンドやシェルスクリプトの中にパスワードを直接書き込む（ハードコードする）のは、セキュリティ上非常に危険です。ps コマンドで他のユーザーからパスワードが丸見えになってしまいます。

5-1. .my.cnfファイルの作成

そこで、MySQLのクライアント設定ファイルである ~/.my.cnf を作成し、そこに認証情報を隠します。バックアップを実行するOSユーザー（通常はroot）のホームディレクトリに作成します。

sudo vi /root/.my.cnf

[mysqldump]
user = root
password = "ここに強固なパスワードを記載"

[client]
user = root
password = "ここに強固なパスワードを記載"

5-2. 権限の厳格化

このファイルはパスワードそのものなので、絶対に他のユーザーから見えないようにパーミッション（権限）を 600 に制限します。

sudo chmod 600 /root/.my.cnf

これで、コマンドを打つ際に -u や -p を一切指定しなくても、自動的に .my.cnf を読み込んでログインしてくれるようになります。

---

6. シェルスクリプトによる「自動バックアップ＆世代管理」の構築

準備が整いました。いよいよ、プロの現場で実際に稼働している「バックアップ・シェルスクリプト」を作成します。
単にバックアップを取るだけでなく、「古いバックアップを自動で削除する（世代管理）」機能をつけることで、サーバーのディスク容量パンクを防ぎます。

6-1. バックアップスクリプトの全容

sudo vi /usr/local/bin/mysql_backup.sh

以下のプロ仕様のコードをコピーして貼り付けてください。

#!/bin/bash
# ==========================================
# MySQL 自動バックアップ＆世代管理スクリプト
# ==========================================

# 設定変数
DB_NAME="linux_koubou_db"
BACKUP_DIR="/var/backups/mysql"
DATE_SUFFIX=$(date +'%Y%m%d_%H%M')
FILE_NAME="${DB_NAME}_${DATE_SUFFIX}.sql.gz"
BACKUP_PATH="${BACKUP_DIR}/${FILE_NAME}"
LOG_FILE="/var/log/mysql_backup.log"
KEEP_DAYS=7  # 何日分のバックアップを保持するか（世代管理）

# ログ出力関数
log() {
    echo "$(date +'%Y-%m-%d %H:%M:%S') - $1" >> "${LOG_FILE}"
}

log "INFO: バックアップ処理を開始します。対象DB: ${DB_NAME}"

# バックアップ先ディレクトリが存在しなければ作成
mkdir -p "${BACKUP_DIR}"

# mysqldumpの実行 (.my.cnfを使用するため認証情報の指定不要)
mysqldump --single-transaction --routines --triggers "${DB_NAME}" | gzip > "${BACKUP_PATH}"

# 実行結果の成否チェック
if [ $? -eq 0 ]; then
    log "SUCCESS: バックアップ成功 -> ${BACKUP_PATH}"
else
    log "ERROR: バックアップに失敗しました！"
    # ここにSlackやメール通知のコマンド（curl等）を入れると完璧です
    exit 1
fi

# 世代管理（古いファイルの自動削除）
log "INFO: ${KEEP_DAYS}日以上前の古いバックアップファイルを削除します。"
find "${BACKUP_DIR}" -name "${DB_NAME}_*.sql.gz" -type f -mtime +${KEEP_DAYS} -exec rm -f {} \;

log "INFO: バックアップ処理が正常に完了しました。"
exit 0

6-2. 実行権限の付与

作成したスクリプトに実行権限（実行可能なプログラムとしての許可）を与えます。

sudo chmod +x /usr/local/bin/mysql_backup.sh

---

7. cronへの登録と、エラー検知時のAI連携プロンプト

最後に、この完璧なスクリプトを cron に登録し、毎日深夜のアクセスが少ない時間帯に自動実行させます。

7-1. crontabの設定

sudo crontab -e

以下の1行を末尾に追記します（毎日深夜3時0分に実行）。

0 3 * * * /usr/local/bin/mysql_backup.sh > /dev/null 2>&1

これで、あなたがぐっすり眠っている間も、サーバーは自立してデータベースの健康を守り続けてくれます。

7-2. AIを活用したリカバリプランの策定

もし、mysql_backup.log にエラーが記録されていた場合、原因の究明が急務です。そんな時は、AI（GeminiやChatGPT）にログを解析させ、リカバリの手順を出力させましょう。

「あなたは優秀なデータベース管理者（DBA）です。
MySQLのバックアップスクリプトが以下のエラーログを出力して失敗しました。

【エラーログの内容】
mysqldump: Error: 'Access denied for user 'root'@'localhost' (using password: NO)' when trying to dump tablespaces

1. このエラーが意味する根本的な原因は何ですか？
2. ~/.my.cnf の設定と権限を含め、AlmaLinux 9上で確認すべき項目を3つ挙げてください。
3. 問題解決後、手動でスクリプトを再実行して正常性を確認するためのコマンドライン手順を提示してください。」

AIは即座に「.my.cnfの形式が間違っているか、cron実行ユーザーのホームディレクトリが合っていない可能性があります」といった、的確なトラブルシューティングの道筋を提示してくれます。現代のインフラエンジニアにとって、AIは最強の「副操縦士」です。

---

8. 【上級編】バイナリログによるPoint-in-Time Recovery（PITR）の概念

講座の総仕上げとして、上級者向けの手法をご紹介します。

1日1回、深夜3時にバックアップを取っているシステムで、もし「今日の午後5時」にサーバーが壊れたらどうなるでしょうか？
昨日の深夜3時のデータには戻せますが、「今日の昼間にユーザーが書き込んだデータ」は永久に失われてしまいます。

これを防ぎ、「障害発生の1秒前」というピンポイントの時間までデータを完全に復旧させる技術が「Point-in-Time Recovery（PITR）」です。
PITRを実現するには、MySQLの「バイナリログ（Binary Log）」機能を有効にします。バイナリログには、「誰がいつ、どのデータをどう書き換えたか」というすべての更新履歴がリアルタイムで記録されます。

PITRの復旧フロー

1. 深夜3時の mysqldump データ（完全なスナップショット）をリストアする。
2. 残りの「深夜3時から午後5時」までの空白の時間は、mysqlbinlog コマンドを使ってバイナリログから更新履歴を取り出し、MySQLに「再再生（リプレイ）」させる。

この設定と運用は難易度が高いですが、「絶対にデータを1バイトも失ってはいけない」ミッションクリティカルなシステム（金融やECサイトなど）を設計する際には必須の知識となります。皆さんが次のステップ（中級・上級）に進む際は、ぜひこの「バイナリログ」について深く学んでみてください。

---

総まとめ：データベースエンジニアとしての新たな門出

全8回に及ぶ「MySQL初心者講座」、本当にお疲れ様でした！

第1回でターミナルの黒い画面に怯えながらMySQLをインストールしたあの日から、皆さんは途方もない成長を遂げました。適切なデータ型を用いたテーブル設計、複雑な条件を組み合わせたCRUD操作、JOINによる多重結合、集計関数による分析、EXPLAINを駆使したインデックスチューニング、そして今回の自動バックアップ構築。

これだけの知識と実践経験があれば、あなたはもう「データベース初心者」ではありません。自信を持ってWebアプリケーションのバックエンドを設計し、運用できる「データベースエンジニア（DBA）」の入り口に立っています。

データベースの世界は奥深く、次は「レプリケーション（リアルタイムのデータ複製）」や「シャーディング（データの分散配置）」といった、大規模トラフィックに立ち向かう高度なアーキテクチャがあなたを待っています。

この連載が、皆さんのエンジニアライフにおける確固たる「基盤（データベース）」となり、より創造的でスリリングな開発の旅へ出られることを心から願っています。

それでは、また新しい技術の講座でお会いしましょう！
LINUX工房の「リナックス先生」でした。