Sauvegarde & restauration — MariaDB Kohinos

La priorité #1 du projet est l'intégrité financière. Toute perte de données BDD correspond à une perte de monnaie locale : opérations effacées, soldes désynchronisés, mandats SEPA orphelins, fonds SSA non redistribuables. Pour une association citoyenne comme Fédération Kohinos, c'est une rupture de confiance que rien ne rachète. Conséquence : un backup non vérifié n'est pas un backup. Cette page décrit la stratégie 3-2-1, la chaîne d'automatisation, et surtout la procédure de restauration testée mensuellement.

Stratégie 3-2-1

Règle de prudence éprouvée pour les données critiques : au moins 3 copies des données, sur 2 médias différents, dont 1 hors site.

Couche	Localisation	Média	Rôle
Copie #1 (live)	Serveur prod (Raspberry Pi / VPS)	SSD système	BDD opérationnelle
Copie #2 (proche)	Disque externe USB ou NAS local	HDD / SSD second	Restauration rapide LAN
Copie #3 (hors site)	Hetzner Storage Box · MinIO self-host · NAS Synology distant	S3 ou rsync chiffré	Résilience incendie / vol / ransomware

Le hors-site doit être physiquement séparé du serveur prod et chiffré (cf. callout dédié). Un backup sur le même disque que la prod n'est pas un backup : c'est une copie locale, vulnérable au même incident matériel.

Cibles & fréquences

Fréquence	Type	Outil	Rétention
Continue (par transaction)	Binary log MariaDB	log_bin + binlog_format=ROW	14 jours sur disque
Horaire	Rotation binlog + archivage	Script cron + rsync	14 jours, point-in-time recovery
Quotidienne	Dump SQL complet	mysqldump + zstd + age	30 jours local · 90 jours offsite
Hebdomadaire	Snapshot disque / LVM	lvcreate -s ou ZFS snapshot	8 semaines
Mensuelle	Copie froide vérifiée hors site	Dump chiffré + checksum SHA-256	10 ans (obligation comptable)

La rétention 10 ans du backup mensuel découle de la politique de conservation comptable (cf. docs/rgpd/GUIDE_RETENTION.md). Les autres niveaux suivent une rotation pragmatique : assez profonde pour rattraper un incident détecté tardivement, assez courte pour ne pas saturer le stockage.

Procédure de dump quotidien

Le pivot de la stratégie. Un dump SQL complet, cohérent (un point dans le temps), compressé et chiffré :

# /usr/local/sbin/kohinos-backup-daily.sh
#!/usr/bin/env bash
set -euo pipefail

DB_NAME="kohinos"
DB_USER="kohinos_backup"            # user dédié, droits SELECT + LOCK TABLES + RELOAD
BACKUP_DIR="/var/backups/kohinos"
OFFSITE_DIR="/mnt/offsite/kohinos"   # rsync ou s3fs MinIO
STAMP="$(date +%Y%m%d-%H%M%S)"
RECIPIENT_AGE="age1xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx"  # clé publique age

# 1. Dump cohérent (transaction unique sur InnoDB) + routines + triggers + events
#    --single-transaction : snapshot consistant SANS lock (InnoDB)
#    --quick              : streame ligne par ligne (RAM-friendly Raspberry Pi)
#    --hex-blob           : sécurise les BLOB binaires (IBAN chiffrés, fichiers attachés)
#    --routines           : sauvegarde les procédures stockées
#    --triggers --events  : sauvegarde triggers + scheduler events
#    --default-character-set=utf8mb4 : préserve l'encodage (emojis, accents)
mysqldump \
    --defaults-file=/etc/kohinos/backup.cnf \
    --single-transaction \
    --routines \
    --triggers \
    --events \
    --quick \
    --hex-blob \
    --default-character-set=utf8mb4 \
    "${DB_NAME}" \
  | zstd -19 -T0 \
  | age -r "${RECIPIENT_AGE}" \
  > "${BACKUP_DIR}/${DB_NAME}-${STAMP}.sql.zst.age"

# 2. Checksum (vérifie l'intégrité au repos, et lors du transfert offsite)
sha256sum "${BACKUP_DIR}/${DB_NAME}-${STAMP}.sql.zst.age" \
    > "${BACKUP_DIR}/${DB_NAME}-${STAMP}.sha256"

# 3. Réplication offsite (rsync sur Hetzner Storage Box ou s3 sync vers MinIO)
rsync -a --partial --append-verify \
    "${BACKUP_DIR}/${DB_NAME}-${STAMP}.sql.zst.age" \
    "${BACKUP_DIR}/${DB_NAME}-${STAMP}.sha256" \
    "${OFFSITE_DIR}/"

# 4. Rotation locale (30 jours)
find "${BACKUP_DIR}" -name "${DB_NAME}-*.sql.zst.age" -mtime +30 -delete
find "${BACKUP_DIR}" -name "${DB_NAME}-*.sha256"      -mtime +30 -delete

# 5. Log structuré (lu par Monolog channel 'backup')
logger -t kohinos-backup "OK ${DB_NAME} ${STAMP} ($(stat -c%s "${BACKUP_DIR}/${DB_NAME}-${STAMP}.sql.zst.age") octets)"

Le fichier /etc/kohinos/backup.cnf contient les credentials d'un utilisateur dédié au backup (lecture seule + verrous nécessaires) :

# /etc/kohinos/backup.cnf (chmod 600, root:root)
[client]
user     = kohinos_backup
password = <mot_de_passe_long_aléatoire>
host     = 127.0.0.1
port     = 3306

Création de l'utilisateur côté MariaDB :

CREATE USER 'kohinos_backup'@'localhost'
    IDENTIFIED BY '<mot_de_passe_long_aléatoire>';

GRANT SELECT, LOCK TABLES, SHOW VIEW, EVENT, TRIGGER,
      RELOAD, PROCESS, REPLICATION CLIENT
    ON *.* TO 'kohinos_backup'@'localhost';

FLUSH PRIVILEGES;

Procédure de restauration (testée)

La restauration doit être testée tous les mois sur un environnement de staging isolé. Un backup jamais restauré est une fiction de sécurité.

Étapes

# 1. Préparer une BDD cible vierge (jamais directement sur la prod !)
mysql -e "CREATE DATABASE kohinos_restore_$(date +%Y%m%d)
          DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;"

# 2. Décrypter + décompresser + importer
age -d -i /etc/kohinos/backup-key.txt \
    /var/backups/kohinos/kohinos-20260518-030000.sql.zst.age \
  | zstd -d \
  | mysql kohinos_restore_$(date +%Y%m%d)

# 3. Vérifier le checksum AVANT de toucher au fichier
sha256sum -c kohinos-20260518-030000.sha256

# 4. Vérifications structurelles
mysql kohinos_restore_$(date +%Y%m%d) -e "SHOW TABLES;" | wc -l
mysql kohinos_restore_$(date +%Y%m%d) -e "
  SELECT TABLE_NAME, TABLE_ROWS
  FROM information_schema.TABLES
  WHERE TABLE_SCHEMA = 'kohinos_restore_$(date +%Y%m%d)'
    AND TABLE_NAME IN ('account', 'operation', 'flux', 'sepa_mandate', 'user')
  ORDER BY TABLE_NAME;"

# 5. Vérification métier (invariant financier) — DOIT terminer sans erreur
DATABASE_URL="mysql://restore:pwd@127.0.0.1/kohinos_restore_$(date +%Y%m%d)" \
    php bin/console kohinos:accounts:check --env=prod

Invariant financier post-restore

La commande kohinos:accounts:check recalcule le solde de chaque compte à partir des opérations et le compare au champ balance persisté. La somme des opérations doit égaler la balance pour chaque Account. Si l'égalité est rompue, le backup est corrompu ou incohérent : ne pas le promouvoir en prod.

# Contrôle additionnel manuel (somme nette par compte)
mysql kohinos_restore_$(date +%Y%m%d) -e "
  SELECT a.id, a.balance,
         COALESCE(SUM(o.amount), 0) AS sum_ops,
         a.balance - COALESCE(SUM(o.amount), 0) AS delta
  FROM account a
  LEFT JOIN operation o ON o.account_id = a.id
  GROUP BY a.id
  HAVING delta <> 0;"
# Résultat attendu : 0 ligne. Toute ligne = corruption ou backup partiel.

Binary log & Point-In-Time Recovery

Le dump quotidien fige un instant T. Pour rattraper les heures entre T et l'incident, MariaDB enregistre chaque écriture dans le binary log. Configuration minimale :

# /etc/mysql/mariadb.conf.d/60-kohinos.cnf
[mysqld]
log_bin                = /var/log/mysql/mariadb-bin
log_bin_index          = /var/log/mysql/mariadb-bin.index
binlog_format          = ROW
binlog_row_image       = FULL
expire_logs_days       = 14
max_binlog_size        = 100M
sync_binlog            = 1
innodb_flush_log_at_trx_commit = 1
server_id              = 1

Application :

sudo systemctl restart mariadb
mysql -e "SHOW VARIABLES LIKE 'log_bin%';"
mysql -e "SHOW BINARY LOGS;"

Restauration point-in-time :

# Récupération à l'instant 2026-05-18 14:32:00 (juste avant l'incident)
# 1. Restaurer le dump du matin (voir section précédente)
# 2. Rejouer les binlogs jusqu'à l'horodatage
mysqlbinlog \
    --stop-datetime="2026-05-18 14:32:00" \
    /var/log/mysql/mariadb-bin.00001{2,3,4} \
  | mysql kohinos_restore_$(date +%Y%m%d)

Automatisation & supervision

Le script ci-dessus est planifié via cron (voir cron & tâches planifiées). Chaque exécution doit produire :

• une entrée Monolog sur le channel backup (succès / échec, taille, durée) ;
• une alerte GlitchTip (self-host) si exit code ≠ 0 ;
• un heartbeat vers une page de supervision (Uptime Kuma local par exemple) : l'absence de heartbeat est aussi grave que l'échec explicite.

# /etc/cron.d/kohinos-backup
# Dump quotidien à 03:00 (hors fenêtre de prélèvement SEPA)
0  3  *  *  *  root  /usr/local/sbin/kohinos-backup-daily.sh   >> /var/log/kohinos/backup.log 2>&1

# Snapshot LVM hebdomadaire dimanche 04:00
0  4  *  *  0  root  /usr/local/sbin/kohinos-backup-weekly.sh  >> /var/log/kohinos/backup.log 2>&1

# Backup mensuel hors site, 1er du mois 04:30
30 4  1  *  *  root  /usr/local/sbin/kohinos-backup-monthly.sh >> /var/log/kohinos/backup.log 2>&1

# Test de restauration mensuel, 15 du mois 02:00 (sur env staging)
0  2  15 *  *  root  /usr/local/sbin/kohinos-restore-drill.sh  >> /var/log/kohinos/restore-drill.log 2>&1

Côté application, déclarer le channel Monolog backup dans config/packages/monolog.yaml permet d'unifier la lecture des logs via les outils standards (cf. monitoring & observabilité).

Coût & cible Raspberry Pi

Une instance Kohinos modeste (≤ 500 adhérents) génère typiquement un dump quotidien compressé entre 50 et 200 Mo. Le stockage backup peut donc rester extrêmement frugal :

Composant	Coût indicatif	Capacité utile
HDD USB externe 2 To (local)	~50 €	~10 ans de backups quotidiens
Hetzner Storage Box 1 To	~3,50 €/mois	Réplication offsite chiffrée
MinIO self-host sur NAS Synology / 2e Pi	0 € (matériel existant)	S3-compatible, AGPL friendly
Accès SSH chez un membre de l'asso (offsite gratuit)	0 €	rsync nocturne

Aucun de ces choix ne crée de dépendance critique à un SaaS propriétaire. C'est cohérent avec la priorité souveraineté du projet : si un fournisseur disparaît ou augmente ses tarifs, l'association reste maîtresse de ses sauvegardes.

Checklist d'audit annuel

• ☐ Le dump quotidien a tourné chaque jour des 30 derniers jours (vérifier backup.log).
• ☐ Au moins 1 test de restauration mensuel réussi (invariant financier OK) dans les 12 derniers mois.
• ☐ La clé age de chiffrement est connue d'au moins 2 personnes physiques (trésorier + admin technique).
• ☐ Le backup offsite est réellement hors site (pas la même pièce, idéalement pas la même ville).
• ☐ Le backup mensuel à rétention 10 ans est lisible (déchiffrable + checksum OK).
• ☐ L'utilisateur kohinos_backup n'a que les droits minimaux.
• ☐ Les binlogs ne sont jamais purgés sans archivage.
• ☐ Le mot de passe BDD n'apparaît dans aucun commit Git (audit git log -p).