gmao/docker/backup.py

#!/usr/bin/env python3
"""Dump automatique de la base MariaDB.
Execute chaque nuit via cron dans le conteneur Flask.
Garde les 7 derniers dumps (rotation).
"""
import os
import sys
import subprocess
import gzip
import tarfile
from datetime import datetime
from pathlib import Path

DUMP_DIR = Path("/backups")
DUMP_DIR.mkdir(parents=True, exist_ok=True)

TIMESTAMP = datetime.now().strftime("%Y%m%d_%H%M%S")
DUMP_FILE = DUMP_DIR / f"gmao_db_{TIMESTAMP}.sql.gz"
BUNDLE_FILE = DUMP_DIR / f"gmao_backup_{TIMESTAMP}.tar.gz"
UPLOAD_DIR = Path(os.environ.get("UPLOAD_FOLDER", "/app/app_new/uploads"))

DB_HOST = os.environ.get("DB_HOST", "mariadb")
DB_PORT = os.environ.get("DB_PORT", "3306")
DB_NAME = os.environ.get("MARIADB_DATABASE", "gmao_db")
DB_USER = os.environ.get("MARIADB_USER", "gmao")
DB_PASS = os.environ.get("MARIADB_PASSWORD", "gmao123")
ROOT_PASS = os.environ.get("MARIADB_ROOT_PASSWORD", "root123")

# Utiliser pymysql pour exporter le schema + donnees
try:
    import pymysql
    from pymysql.cursors import DictCursor
    
    conn = pymysql.connect(
        host=DB_HOST, port=int(DB_PORT),
        user="root", password=ROOT_PASS,
        database=DB_NAME, charset="utf8mb4"
    )
    cursor = conn.cursor()
    cursor.execute("SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ")
    cursor.execute("START TRANSACTION WITH CONSISTENT SNAPSHOT")
    
    # Obtenir la liste des tables
    cursor.execute("SHOW TABLES")
    tables = [row[0] for row in cursor.fetchall()]
    
    with gzip.open(str(DUMP_FILE), "wt", encoding="utf-8") as f:
        f.write(f"-- Dump {DB_NAME} - {datetime.now().isoformat()}\n")
        f.write("SET FOREIGN_KEY_CHECKS=0;\n\n")
        
        for table in tables:
            # Schema
            cursor.execute(f"SHOW CREATE TABLE `{table}`")
            create_sql = cursor.fetchone()[1]
            f.write(f"DROP TABLE IF EXISTS `{table}`;\n")
            f.write(f"{create_sql};\n\n")
            
            # Donnees
            cursor.execute(f"SELECT * FROM `{table}`")
            rows = cursor.fetchall()
            if rows:
                cols = [d[0] for d in cursor.description]
                col_names = ", ".join(f"`{c}`" for c in cols)
                for row in rows:
                    values = []
                    for val in row:
                        if val is None:
                            values.append("NULL")
                        elif isinstance(val, (int, float)):
                            values.append(str(val))
                        else:
                            escaped = str(val).replace("\\", "\\\\").replace("'", "\\'")
                            values.append(f"'{escaped}'")
                    f.write(f"INSERT INTO `{table}` ({col_names}) VALUES ({', '.join(values)});\n")
                f.write("\n")
        
        f.write("SET FOREIGN_KEY_CHECKS=1;\n")
    
    conn.rollback()
    conn.close()

    # Une archive de reprise contient la base cohérente et les documents.
    with tarfile.open(BUNDLE_FILE, "w:gz") as archive:
        archive.add(DUMP_FILE, arcname=f"database/{DUMP_FILE.name}")
        if UPLOAD_DIR.exists():
            archive.add(UPLOAD_DIR, arcname="uploads", recursive=True)
        version_file = Path("/app/VERSION")
        if version_file.exists():
            archive.add(version_file, arcname="VERSION")

    # Validation minimale immédiate : l'archive doit être lisible et contenir
    # un dump SQL non vide. Une restauration complète reste à tester hors ligne.
    with tarfile.open(BUNDLE_FILE, "r:gz") as archive:
        sql_members = [m for m in archive.getmembers() if m.name.startswith("database/")]
        if not sql_members or sql_members[0].size <= 0:
            raise RuntimeError("Archive de sauvegarde invalide : dump SQL absent")
    
    # Rotation : garder les 7 derniers
    dumps = sorted(DUMP_DIR.glob("gmao_db_*.sql.gz"), key=lambda p: p.stat().st_mtime, reverse=True)
    for old in dumps[7:]:
        old.unlink()

    bundles = sorted(DUMP_DIR.glob("gmao_backup_*.tar.gz"), key=lambda p: p.stat().st_mtime, reverse=True)
    for old in bundles[7:]:
        old.unlink()
    
    size = DUMP_FILE.stat().st_size / 1024
    bundle_size = BUNDLE_FILE.stat().st_size / 1024
    print(f"{datetime.now()}: Sauvegarde validee: {BUNDLE_FILE} ({bundle_size:.0f} KB, {len(tables)} tables)")
    
except Exception as e:
    print(f"{datetime.now()}: ERREUR dump: {e}", file=sys.stderr)
    sys.exit(1)