Sauvegarder MySQL vers S3 et vérifier la restauration chaque nuit¶

Cette page monte la chaîne entière : ce qu'un mysqldump contient et ce qu'il laisse dehors, le script qui produit la sauvegarde et l'envoie sur S3, la restauration faite une fois à la main, puis la même vérification rejouée chaque nuit sans personne. MariaDB suit exactement le même chemin.

Ce qu'une sauvegarde MySQL doit contenir¶

mysqldump écrit du SQL : les CREATE TABLE et les INSERT des bases qu'on lui nomme. Ce qui vit au niveau du serveur n'y est pas :

les comptes et leurs droits, qui sont dans la base mysql ;
les autres bases du même serveur, si vous n'avez pas passé --databases ;
la configuration du serveur (my.cnf, /etc/mysql/conf.d/).

Deux choses s'oublient plus discrètement encore, parce que mysqldump ne les prend pas par défaut : les procédures et fonctions stockées et les événements du planificateur. Les déclencheurs, eux, sont pris par défaut. Une base restaurée sans ses procédures se lit parfaitement et ne calcule plus rien.

Le moteur de stockage est écrit dans le dump, sur chaque CREATE TABLE, sous la forme ENGINE=InnoDB. Il doit donc exister sur le serveur où vous restaurez : un dump de tables MyISAM se rejoue sur un MySQL 8, mais un dump produit avec un moteur tiers s'arrête sur la première table. Comptez vos moteurs une fois sur la production, avec select engine, count(*) from information_schema.tables group by engine, et vous saurez à quoi vous avez affaire.

Les options qui comptent¶

Option	Ce qu'elle change
`--single-transaction`	un instantané cohérent des tables InnoDB, sans verrouiller la production
`--routines --events`	ajoute les procédures, les fonctions et les événements, absents sans elle
`--triggers`	déjà actif par défaut : ne le désactivez pas
`--databases`	met un `CREATE DATABASE` et un `USE` dans le dump, qui recrée donc la base lui-même
`--set-gtid-purged=OFF`	retire le `SET @@GLOBAL.gtid_purged` que porte un dump pris sur un serveur en GTID

--single-transaction ne protège que ce qui est transactionnel. Sur des tables MyISAM, il ne fait rien du tout : la seule cohérence possible passe par --lock-tables, qui bloque les écritures pendant le dump. Une base qui mélange les deux moteurs ne peut pas être sauvegardée de façon cohérente en une seule commande — c'est une bonne raison de finir de migrer vers InnoDB.

--set-gtid-purged=OFF ne sert que si votre serveur tourne avec GTID activé. Dans ce cas le dump commence par fixer la position de réplication, ce qu'un serveur d'essai qui a déjà exécuté des transactions refuse de rejouer.

Le script de sauvegarde¶

#!/bin/bash
set -euo pipefail

ts=$(date +%Y%m%d_%H%M%S)
dest=s3://sauvegardes-facturation/mysql
cnf=/etc/mysql/sauvegarde.cnf

mysqldump --defaults-extra-file="${cnf}" \
  --single-transaction --routines --events --triggers \
  --databases facturation \
  | gzip > "/backups/facturation_${ts}.sql.gz"

mysql --defaults-extra-file="${cnf}" -N -B \
  -e "select user, host from mysql.user where user not like 'mysql.%'" \
  | while read -r u h; do
      mysql --defaults-extra-file="${cnf}" -N -B \
        -e "show grants for '${u}'@'${h}'"
    done | sed 's/$/;/' > "/backups/droits_${ts}.sql"

aws s3 cp "/backups/facturation_${ts}.sql.gz" "${dest}/"
aws s3 cp "/backups/droits_${ts}.sql" "${dest}/"

Le mot de passe est dans sauvegarde.cnf, sous une section [client], et pas sur la ligne de commande : un -pmotdepasse est lisible par n'importe qui dans la liste des processus pendant toute la durée du dump.

La boucle show grants produit un fichier de droits rejouable. SHOW GRANTS rend ses lignes sans point-virgule final, d'où le sed : sans lui, le fichier se rejoue comme une seule instruction et échoue.

Sans pipefail, un dump raté ressort en succès

Dans mysqldump | gzip, le shell ne regarde que le code de sortie du dernier maillon. gzip réussit à compresser une sortie vide, donc le script rend 0 et le cron est content. set -o pipefail — inclus dans le set -euo pipefail ci-dessus — fait échouer la ligne entière dès que mysqldump échoue. C'est la première cause de sauvegardes vides qui passent au vert pendant des mois.

Gardez l'horodatage dans le nom du fichier : un fichier toujours écrasé au même nom ne laisse aucune chance de revenir à la veille. Pour la rétention, une règle de cycle de vie sur le bucket fait le travail sans script à maintenir.

Reste à le lancer chaque nuit. Un cron suffit, à deux conditions : que sa sortie d'erreur aille quelque part où quelqu'un la lit, et qu'une exécution qui s'éternise n'en croise pas une autre — flock -n sur un fichier verrou règle le second point en une ligne.

Restaurer une fois, à la main¶

Une sauvegarde n'est prouvée que restaurée. Faites-le une fois, entièrement, sur un poste jetable — c'est aussi la procédure que vous suivrez le jour où ça compte.

docker run -d --name mysql-essai -e MYSQL_ROOT_PASSWORD=essai mysql:8.4
until docker exec mysql-essai mysqladmin ping -uroot -pessai --silent; do
  sleep 1
done

gunzip -c /backups/facturation_20260918_010000.sql.gz \
  | docker exec -i mysql-essai mysql -uroot -pessai

Aucune base n'est nommée à la restauration : le dump a été pris avec --databases, il porte donc son propre CREATE DATABASE facturation.

Le client mysql s'arrête à la première erreur, et c'est ce qu'on veut. N'ajoutez pas --force dans un essai de restauration : il transforme un dump tronqué en base à moitié remplie qui a l'air de marcher.

Deux détails qui font perdre une soirée. Si votre client vient de MariaDB — c'est le cas dans les images Alpine — il vérifie le certificat du serveur et refuse de se connecter à un MySQL 8 qui présente son certificat auto-signé : --skip-ssl sur mysql, mysqldump et mysqladmin lève le blocage pour un essai local. Et le conteneur répond au ping avant d'avoir fini son initialisation, d'où l'attente ci-dessus plutôt qu'un sleep fixe.

Prenez le temps que ça a pris. C'est votre durée de restauration réelle, la seule à comparer au délai que vous avez promis.

Ce qu'on vérifie dans une base restaurée¶

Le dump s'est rejoué sans erreur ne veut pas dire que les données sont là. Quatre questions, dans cet ordre :

select count(*) from information_schema.tables where table_schema = 'facturation';
select count(*) from factures;
select count(*) from factures where created_at > now() - interval 2 day;
select auto_increment from information_schema.tables
  where table_schema = 'facturation' and table_name = 'factures';

Les tables sont là. Zéro table, c'est un dump vide qui s'est rejoué parfaitement.
Les lignes sont là. Comparez à l'ordre de grandeur de la production, pas à un chiffre exact : une base qui grossit, c'est normal.
Les données sont récentes. La question se pose en comptant les lignes d'hier, pas en lisant une date : un nombre se compare à un seuil, une date non. Zéro ici, c'est un job de sauvegarde qui a cessé de tourner.
Les compteurs ont suivi. Un AUTO_INCREMENT retombé à 1 provoque des collisions de clés dès la première écriture après la restauration.

Puis détruisez tout : docker rm -f mysql-essai.

Automatiser cette vérification¶

Ce qui précède coûte une heure ou deux, à chaque fois. C'est pour cette raison que ces tests, faits à la main, finissent par ne plus être faits.

RestoreProof rejoue exactement ces étapes en tâche planifiée, sur votre infrastructure : un runner récupère la sauvegarde, la restaure dans un conteneur jetable, pose les mêmes questions que ci-dessus, détruit tout, et signe le résultat. Les données ne sortent pas de chez vous.

Déclarez d'abord la sauvegarde comme source — le bucket, le préfixe, le motif *.sql.gz, et la stratégie le plus récemment modifié. Les clés d'accès ne se saisissent pas : le plan porte une référence, env://AWS_ACCESS_KEY_ID, que le runner résout dans son propre environnement. Voir les références de secrets et comment adapter le bloc fetch.

L'assistant MySQL Database écrit ensuite le plan, et ce plan est la manipulation que vous venez de faire à la main, ligne pour ligne :

À la main	Dans le plan
`aws s3 cp` depuis le bucket	`fetch`, qui prend le fichier le plus récent
`gunzip -c`	`unpack`
`docker run mysql:8.4`	`start_sandbox`
`mysql` sans `--force`	`restore_mysql`
les quatre requêtes	une sonde `mysql` par question
`docker rm -f`	le nettoyage, toujours exécuté

Le seul ajout est max_age: 26h, et c'est celui qu'une restauration ne peut pas déduire du contenu : il fait échouer l'exécution quand la sauvegarde la plus récente trouvée à la source est plus vieille que ce délai. Le plan complet, prêt à coller dans l'éditeur, est dans la recette bases de données.

Les deux premières questions se posent en nommant une table, les deux autres en écrivant la requête. Une sonde qui porte sa propre requête doit rendre un nombre : c'est à ce nombre que le seuil s'applique.

La version du bac à sable

mysql:8.4 doit reprendre la version majeure de votre serveur. Un dump pris sur un serveur plus récent ne se rejoue pas sur un serveur plus ancien. Pour MariaDB, le plan est le même : seule l'image change, en mariadb:11 ou mariadb:10.11.

Les seuils ne se recopient pas depuis cette page. Un essai restaure votre sauvegarde, compte ce qu'elle contient réellement, et propose chaque seuil 5 % sous la valeur mesurée, avec l'opérateur gte. Voir comment trouver les seuils.

L'écran de fin d'essai : chaque sonde MySQL avec la valeur mesurée et le seuil proposé cinq pour cent en dessous

Il reste à choisir une fréquence. Chaque nuit place l'exécution à 2 h du matin : votre script de sauvegarde tourne à 1 h, le dump a donc une heure quand il est éprouvé. L'autre déclenchement possible est un appel HTTP à la fin de ce script — le test porte alors exactement sur le fichier qui vient d'être produit.

Chaque exécution laisse un rapport horodaté, signé, qui nomme la sauvegarde éprouvée et ce que chaque sonde a mesuré.

Ce que cette chaîne ne prouve pas¶

Elle prouve qu'une sauvegarde récente se restaure dans un MySQL neuf et qu'elle contient les données attendues. Elle ne prouve rien sur les comptes et leurs droits, qui vivent dans un autre fichier : pour les éprouver, il faut rejouer le fichier de droits et se connecter avec un de ces comptes. Elle ne prouve pas non plus que votre application fonctionne sur cette base : pour cela, il faut la démarrer contre le bac à sable et ajouter une sonde http. Et elle ne dit rien de ce qui a été écrit depuis le dernier dump — cet écart, c'est votre RPO, et il se règle avec la fréquence des sauvegardes, pas avec les tests.

Le plan complet¶

À coller dans l'éditeur de plan, vue JSON (expert), en remplaçant l'identifiant de source, le nom de la base et celui de la table.

{
  "steps": [
    {
      "type": "fetch",
      "config": {
        "source": "source_profile",
        "source_id": "<identifiant de votre source>",
        "max_age": "26h",
        "output": "dump.sql.gz"
      }
    },
    {
      "type": "unpack",
      "config": { "format": "gzip", "input": "dump.sql.gz", "output": "dump.sql" }
    },
    {
      "type": "start_sandbox",
      "config": { "image": "mysql:8.4", "env": { "MYSQL_DATABASE": "facturation" } }
    },
    {
      "type": "restore_mysql",
      "config": { "database": "facturation", "input": "dump.sql" }
    }
  ],
  "probes": [
    {
      "name": "schema",
      "type": "mysql",
      "timeout": 60,
      "config": {}
    },
    {
      "name": "factures",
      "type": "mysql",
      "timeout": 60,
      "config": {
        "RESTOREPROOF_MYSQL_TABLE": "factures",
        "RESTOREPROOF_MYSQL_ASSERT": "gte",
        "RESTOREPROOF_MYSQL_EXPECTED_ROWS": "1"
      }
    },
    {
      "name": "factures_du_jour",
      "type": "mysql",
      "timeout": 60,
      "config": {
        "RESTOREPROOF_MYSQL_QUERY": "select count(*) from factures where created_at > now() - interval 2 day",
        "RESTOREPROOF_MYSQL_ASSERT": "gte",
        "RESTOREPROOF_MYSQL_EXPECTED_ROWS": "1"
      }
    },
    {
      "name": "auto_increment",
      "type": "mysql",
      "timeout": 60,
      "config": {
        "RESTOREPROOF_MYSQL_QUERY": "select auto_increment from information_schema.tables where table_schema = 'facturation' and table_name = 'factures'",
        "RESTOREPROOF_MYSQL_ASSERT": "gte",
        "RESTOREPROOF_MYSQL_EXPECTED_ROWS": "2"
      }
    }
  ]
}

La sonde schema n'a pas de configuration : sans table ni requête, elle vérifie la base entière et échoue si la restauration n'y a recréé aucune table.

Et ensuite¶

Bases de données pour les variables de la sonde mysql et pour le plan WordPress.
Choisir sa recette pour les sections communes à toutes les recettes.