MySQLの積上げ式バックアップファイル作成(テストファイル作成可能版)

オフライン環境の Ubuntu 24.04 で、Node-RED と Python を連携させた「積み上げ式バックアップシステム」の全手順を整理しました。

ステップ 1：MySQL の接続設定（セキュリティ対策）

Linux（Ubuntu）では my.ini ではなく .my.cnf というファイル名で作成します。

端末（CUI）でファイルを作成：codeBashnano ~/.my.cnf
以下の内容を書き込む（ご自身の環境に合わせて）：codeIni
保存（Ctrl+O -> Enter）して閉じる（Ctrl+X）。
重要：自分以外読めないように設定：codeBashchmod 600 ~/.my.cnf

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

ステップ 2：Python スクリプトの準備

標準ライブラリのみで動作する最終版です。このファイルを /home/user/mysql_backup.py として保存してください。codePython

import subprocess
import datetime
import os
import glob
import sys

# --- 設定項目 ---
DB_NAME = "あなたのDB名"
BACKUP_DIR = "/home/user/mysql_backups" # バックアップ保存先
RETENTION_DAYS = 21                     # 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(target_date):
    start_dt = f"{target_date} 00:00:00"
    end_dt = f"{target_date} 23:59:59"
    if not os.path.exists(BACKUP_DIR): os.makedirs(BACKUP_DIR)
    
    backup_path = os.path.join(BACKUP_DIR, f"backup_{target_date}.sql")
    tables = run_mysql_query("SHOW TABLES")
    
    with open(backup_path, "w") as f:
        f.write(f"-- Backup: {target_date}\n")

    for table in tables:
        if not table: continue
        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)

        if time_col:
            # 積み上げリストア用オプション付き mysqldump
            cmd = ["mysqldump", DB_NAME, table, "--no-create-info", "--insert-ignore", "--complete-insert", "--single-transaction", f"--where={time_col} >= '{start_dt}' AND {time_col} <= '{end_dt}'"]
            with open(backup_path, "a") as f:
                subprocess.run(cmd, stdout=f, check=True)
    print(f"Created: {backup_path}")

def cleanup():
    """古いファイルを削除"""
    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: {f}")

if __name__ == "__main__":
    # 引数があればその日を、なければ昨日を対象にする
    if len(sys.argv) > 1 and sys.argv[1].strip():
        try:
            target = datetime.datetime.strptime(sys.argv[1].strip(), "%Y-%m-%d").date()
        except:
            print("Format Error: YYYY-MM-DD"); sys.exit(1)
    else:
        target = datetime.date.today() - datetime.timedelta(days=1)

    run_backup(target)
    cleanup()

ステップ 3：Node-RED のフロー設定

Node-RED 上で 3 つのノードを配置し、線でつなぎます。

1. ノードの配置と接続

以下の順に左から並べて線でつなぎます。

[Injectノード(A)] ──(接続)──▶ [Execノード] ──(接続)──▶ [Debugノード]
[Injectノード(B)] ──(接続)──┘ (Execノードの同じ入力へ)

2. 各ノードの設定内容

① [Injectノード(A)]：毎日の自動実行用

名前：毎日0:00実行
ペイロード：文字列を選択し、中身は 空っぽ にする（これでPython側が自動的に「昨日」を選びます）。
繰り返し：指定した時刻 -> 00:00

② [Injectノード(B)]：テスト（日付指定）用

名前：テスト実行(日付指定)
ペイロード：文字列を選択。
中身：2024-01-01 （テストしたい日付を入力）
繰り返し：なし

③ [Execノード]：Pythonの呼び出し

コマンド：python3 /home/user/mysql_backup.py（絶対パスで書く）
引数：「msg.payloadをコマンド引数に追加する」にチェックを入れる（重要！）
※これにより、Injectノード(B)で入れた日付がPythonに渡されます。

④ [Debugノード]

そのまま接続。Execノードの出力（実行結果）を表示します。

まとめ：どう動くのか？

毎日 0:00 になると：
Inject(A) が動き、Python に「引数なし」で命令が行きます。Python は「昨日の 0:00~23:59 分のデータ」を抽出し、backup_YYYY-MM-DD.sql を作ります。
テストしたいときは：
Inject(B) の日付を書き換えてボタン（左側のポチ）を押します。するとその日付だけのバックアップファイルが作られます。
リストアするときは：
作成された SQL ファイルを順番に MySQL に読み込ませます。
- 1日目を入れる → データが入る
- 2日目を入れる → 1日目のデータを消さずに、2日目分が 「追加（スタック）」 される
- 重複があっても INSERT IGNORE 設定のおかげでエラーにならず、安全に積み上がります。
古くなると：
実行のたびに、21日以上前の古いファイルを Python が自動で削除します。