MySQLの積上げ式バックアップファイル作成

Ubuntu 24.04 において、標準ライブラリのみで「毎日 0:00 に前日分のデータをバックアップし、3週間で自動削除する」仕組みをまとめました。

1. セキュリティ設定（パスワードの隠蔽）

Pythonスクリプトにパスワードを書かないよう、MySQLの設定ファイルを作成します。

設定ファイルの作成:

nano ~/.my.cnf

内容:

[client]
user = あなたのユーザー名
password = "あなたのパスワード"

権限変更（自分だけが読めるようにする）:

chmod 600 ~/.my.cnf

2. Pythonスクリプト (mysql_backup.py)

追加ライブラリ（pip等）は一切不要です。標準機能だけで動作します

import subprocess
import datetime
import os
import glob

# --- 設定項目 ---
DB_NAME = "あなたのデータベース名"
BACKUP_DIR = "/home/user/mysql_backups" # 保存先（絶対パス）
RETENTION_DAYS = 21                    # 3週間（21日）
TIME_COLUMNS = ['created_at', 'timestamp', 'date'] # 日時カラム名の候補

def run_mysql_query(query):
    """MySQLコマンドを直接呼び出して結果を取得する"""
    cmd = ["mysql", "-NB", "-e", query, DB_NAME]
    result = subprocess.run(cmd, capture_output=True, text=True, check=True)
    return result.stdout.strip().split('\n')

def run_backup():
    # 準備：昨日の日付と範囲を計算
    yesterday = datetime.date.today() - datetime.timedelta(days=1)
    start_dt = f"{yesterday} 00:00:00"
    end_dt = f"{yesterday} 23:59:59"
    
    if not os.path.exists(BACKUP_DIR):
        os.makedirs(BACKUP_DIR)
    
    backup_path = os.path.join(BACKUP_DIR, f"backup_{yesterday}.sql")

    # 1. テーブル一覧を取得
    tables = run_mysql_query("SHOW TABLES")
    
    with open(backup_path, "w") as f:
        f.write(f"-- Daily Backup: {yesterday}\n")

    for table in tables:
        if not table: continue
        
        # 2. テーブルのカラムを確認
        cols_info = run_mysql_query(f"SHOW COLUMNS FROM {table}")
        cols = [line.split('\t')[0] for line in cols_info if line]
        
        # 候補のカラム名がテーブルにあるか探す
        time_col = next((c for c in TIME_COLUMNS if c in cols), None)

        # 3. バックアップ実行（mysqldump）
        cmd = [
            "mysqldump",
            DB_NAME,
            table,
            "--no-create-info",   # テーブル作成文は含めない（追記用）
            "--insert-ignore",    # 重要：重複データは無視してリストア
            "--complete-insert",  # カラム名を指定したINSERT文にする
            "--single-transaction"
        ]

        if time_col:
            cmd.append(f"--where={time_col} >= '{start_dt}' AND {time_col} <= '{end_dt}'")
            print(f"Backup: {table} ({yesterday})")
        else:
            print(f"Skip: {table} (No time column)")
            continue

        # ファイルに追記
        with open(backup_path, "a") as f:
            subprocess.run(cmd, stdout=f, check=True)

    print(f"Done: {backup_path}")

    # 4. 3週間以上前のファイルを削除
    cutoff = datetime.datetime.now() - datetime.timedelta(days=RETENTION_DAYS)
    for f in glob.glob(os.path.join(BACKUP_DIR, "backup_*.sql")):
        if datetime.datetime.fromtimestamp(os.path.getmtime(f)) < cutoff:
            os.remove(f)
            print(f"Deleted old backup: {f}")

if __name__ == "__main__":
    run_backup()

3. Node-RED での設定

毎日 0:00 にこの Python スクリプトを起動するように設定します。

Injectノード:
- 繰り返し設定：指定時刻（at a specific time）
- 時刻：00:00
Execノード:
- コマンド：python3
- 引数：/home/user/mysql_backup.py（スクリプトのフルパス）
Debugノード:
- Execノードの出力を接続して、実行結果を確認。

4. リストア（復元）の考え方

この方式で取ったバックアップは、以下のように**「必要な分だけ積み上げる」**ことができます。

1日目だけ戻したい場合:
mysql -u ユーザー名 -p DB名 < backup_2023-10-01.sql
3日分まとめたい場合:
1日目、2日目、3日目のファイルを順番に読み込ませるだけです。
–no-create-info のおかげで、2日目を流し込んでも1日目のデータは消えません。
データが重複している場合:
–insert-ignore のおかげで、既に存在するデータ（Primary Keyが同じもの）は自動的にスキップされ、エラーにならずに新しいデータだけが追加されます。