La base de données contient tout ce qui fait vivre votre site ou votre application : clients, commandes, comptes, contenus. Si elle est perdue, les fichiers du site ne servent plus à rien. Dans ce tutoriel, vous mettez en place une sauvegarde automatique chaque nuit de vos bases MySQL/MariaDB et PostgreSQL, avec vérification de chaque fichier, suppression automatique des anciennes copies et test de restauration.

Niveau : intermédiaire (savoir utiliser un terminal Linux). Durée : environ 45 minutes. Prérequis : un serveur Linux (Ubuntu, Debian…) avec un accès administrateur, et MySQL/MariaDB et/ou PostgreSQL 14 ou plus récent. Le script de ce tutoriel a été testé avec MariaDB 11 et PostgreSQL 16 : sauvegarde, restauration complète, rotation et comportement en cas d'erreur.

Les commandes s'exécutent sur votre serveur. Remplacez les noms de bases et les mots de passe par les vôtres.

Le principe

  1. Chaque nuit, un script exporte chaque base dans un fichier daté.
  2. Il vérifie que le fichier est lisible avant de le garder.
  3. Il supprime les copies de plus de 14 jours.
  4. Une copie part hors du serveur, car une sauvegarde stockée sur le même serveur disparaît avec lui.

C'est l'application de la règle de sauvegarde 3-2-1 à vos bases de données.

Étape 1 : créer un utilisateur dédié aux sauvegardes

Le script n'a pas besoin des droits d'administrateur : un utilisateur qui peut seulement lire limite les dégâts si ses identifiants fuitent.

MySQL / MariaDB (dans la console sudo mysql) :

CREATE USER 'sauvegarde'@'localhost' IDENTIFIED BY 'un-mot-de-passe-long';
GRANT SELECT, LOCK TABLES, SHOW VIEW, TRIGGER, EVENT ON *.* TO 'sauvegarde'@'localhost';

PostgreSQL (dans la console sudo -u postgres psql) :

CREATE ROLE sauvegarde LOGIN PASSWORD 'un-autre-mot-de-passe-long';
GRANT pg_read_all_data TO sauvegarde;

Le rôle pg_read_all_data donne un accès en lecture à toutes les tables. Il existe depuis PostgreSQL 14.

Étape 2 : ranger les mots de passe hors du script

Les mots de passe ne doivent jamais apparaître dans le script ni sur la ligne de commande. Les deux outils lisent un fichier réservé à l'utilisateur qui lance la sauvegarde (ici root) :

sudo nano /root/.my.cnf
[mysqldump]
user=sauvegarde
password=un-mot-de-passe-long

La section [mysqldump] (et non [client]) est importante : seul l'outil de sauvegarde utilisera ces identifiants, et la commande sudo mysql continuera de vous connecter en administrateur pour les autres tâches, dont la restauration.

echo 'localhost:5432:*:sauvegarde:un-autre-mot-de-passe-long' | sudo tee /root/.pgpass
sudo chmod 600 /root/.my.cnf /root/.pgpass

Les droits 600 empêchent tout autre utilisateur du serveur de lire ces fichiers. PostgreSQL ignore d'ailleurs un fichier .pgpass trop ouvert.

Étape 3 : écrire le script de sauvegarde

Créez le fichier /usr/local/bin/sauvegarde-bdd.sh et adaptez les noms de bases :

#!/usr/bin/env bash
# Sauvegarde quotidienne des bases MySQL/MariaDB et PostgreSQL, avec vérification et rotation.
set -euo pipefail

DOSSIER="${DOSSIER:-/var/backups/bdd}"
GARDER_JOURS=14
MYSQL_BASES="boutique"          # bases MySQL/MariaDB, séparées par des espaces
PG_BASES="gestion"              # bases PostgreSQL, séparées par des espaces
PG_HOTE="${PG_HOTE:-localhost}"
PG_UTILISATEUR="sauvegarde"
DATE=$(date +%Y-%m-%d_%H%M)

mkdir -p "$DOSSIER"
chmod 700 "$DOSSIER"
umask 077
# En cas d'échec, on ne laisse jamais un fichier à moitié écrit.
trap 'rm -f "$DOSSIER"/*.part' EXIT

# MySQL / MariaDB : identifiants lus dans ~/.my.cnf
for BASE in $MYSQL_BASES; do
  FICHIER="$DOSSIER/mysql_${BASE}_${DATE}.sql.gz"
  mysqldump --single-transaction --routines --triggers --no-tablespaces "$BASE" | gzip > "$FICHIER.part"
  gzip -t "$FICHIER.part"
  mv "$FICHIER.part" "$FICHIER"
  echo "$(date '+%F %T') OK $FICHIER ($(du -h "$FICHIER" | cut -f1))"
done

# PostgreSQL : mot de passe lu dans ~/.pgpass
for BASE in $PG_BASES; do
  FICHIER="$DOSSIER/pg_${BASE}_${DATE}.dump"
  pg_dump -Fc -h "$PG_HOTE" -U "$PG_UTILISATEUR" -f "$FICHIER.part" "$BASE"
  pg_restore --list "$FICHIER.part" > /dev/null
  mv "$FICHIER.part" "$FICHIER"
  echo "$(date '+%F %T') OK $FICHIER ($(du -h "$FICHIER" | cut -f1))"
done

# Rotation : on supprime les sauvegardes de plus de GARDER_JOURS jours.
find "$DOSSIER" -type f \( -name '*.sql.gz' -o -name '*.dump' \) -mtime +"$GARDER_JOURS" -delete

Si vous n'utilisez qu'un seul des deux moteurs, laissez la liste de l'autre vide (PG_BASES="" par exemple). Avec les versions récentes de MariaDB, la commande peut s'appeler mariadb-dump au lieu de mysqldump : remplacez-la dans le script si mysqldump est introuvable (elle lit la même section [mysqldump]).

Ce que fait chaque partie :

  • set -euo pipefail : le script s'arrête à la première erreur, y compris si mysqldump échoue au milieu d'un export compressé.
  • --single-transaction : copie cohérente des tables InnoDB sans bloquer le site pendant la sauvegarde.
  • --routines --triggers : les procédures stockées et les déclencheurs sont sauvegardés avec les données.
  • pg_dump -Fc : format personnalisé de PostgreSQL, compressé, qui permet de restaurer tout ou partie de la base.
  • Les fichiers .part : l'export est écrit sous un nom provisoire, vérifié (gzip -t, pg_restore --list), puis renommé. Si quelque chose échoue, le fichier incomplet est supprimé : vous ne prendrez jamais une sauvegarde cassée pour une bonne.
  • umask 077 et chmod 700 : seuls l'administrateur et le script peuvent lire les sauvegardes, qui contiennent les données de vos clients.

Rendez le script exécutable et lancez-le une première fois :

sudo chmod 700 /usr/local/bin/sauvegarde-bdd.sh
sudo /usr/local/bin/sauvegarde-bdd.sh
sudo ls -lh /var/backups/bdd

Étape 4 : l'automatiser chaque nuit

Ouvrez la planification de l'administrateur avec sudo crontab -e et ajoutez :

30 2 * * * /usr/local/bin/sauvegarde-bdd.sh >> /var/log/sauvegarde-bdd.log 2>&1

La sauvegarde tourne chaque nuit à 2 h 30, quand le site est le moins sollicité, et chaque résultat est noté dans le journal /var/log/sauvegarde-bdd.log. Pensez à le consulter régulièrement, ou faites-vous envoyer une alerte en cas d'échec.

Étape 5 : envoyer une copie hors du serveur

Si le serveur est piraté, supprimé par erreur ou perdu chez l'hébergeur, les sauvegardes locales disparaissent avec lui. L'outil rclone copie le dossier vers un stockage distant (stockage compatible S3, autre serveur, service en ligne). Après l'avoir configuré avec rclone config, ajoutez cette ligne à la fin du script :

rclone copy "$DOSSIER" distant:mes-sauvegardes/bdd --max-age 24h

Le paramètre --max-age 24h n'envoie que les fichiers du jour. Si possible, activez sur le stockage distant le verrouillage contre l'effacement pendant quelques jours : même un pirate qui prend le contrôle du serveur ne pourra pas détruire ces copies. Cette étape dépend de votre fournisseur de stockage : testez-la avec vos propres identifiants.

Étape 6 : tester la restauration

Une sauvegarde jamais restaurée n'est qu'un espoir. Restaurez dans une base de test, jamais directement dans la base de production :

MySQL / MariaDB :

sudo mysql -e "CREATE DATABASE boutique_test"
gunzip < /var/backups/bdd/mysql_boutique_2026-11-14_0230.sql.gz | sudo mysql boutique_test
sudo mysql -e "SELECT COUNT(*) FROM boutique_test.produits"

PostgreSQL :

sudo -u postgres createdb gestion_test
sudo -u postgres pg_restore -d gestion_test --no-owner /var/backups/bdd/pg_gestion_2026-11-14_0230.dump
sudo -u postgres psql -d gestion_test -c "SELECT COUNT(*) FROM eleves"

Comparez les nombres de lignes avec la base réelle, ouvrez quelques enregistrements, puis supprimez la base de test. Faites cet exercice une fois par mois et notez la procédure : le jour d'un vrai incident, vous n'aurez pas à la chercher.

Les erreurs fréquentes

  • « Access denied … PROCESS privilege » avec MySQL 8 : l'option --no-tablespaces du script évite ce droit supplémentaire.
  • Le script fonctionne à la main mais pas dans cron : cron utilise un environnement minimal. Vérifiez que le script est lancé par root (qui lit /root/.my.cnf et /root/.pgpass) et consultez le journal.
  • Le disque se remplit : réduisez GARDER_JOURS ou surveillez l'espace libre. Une base de plusieurs gigaoctets multipliée par 14 jours prend vite de la place.
  • Restaurer par-dessus la production : toujours dans une base de test d'abord, et seulement ensuite, si besoin, sur la base réelle avec le site en maintenance.

Questions fréquentes

La sauvegarde ralentit-elle mon site ?

Très peu avec --single-transaction et pg_dump, qui travaillent sur une image cohérente de la base sans la bloquer. Lancez-la la nuit pour être tranquille.

Mon hébergeur fait déjà des sauvegardes. Est-ce suffisant ?

Vérifiez leur fréquence, leur durée de conservation et surtout la procédure pour restaurer une seule base à une date précise. Une copie que vous contrôlez vous-même reste une sécurité précieuse.

Et les fichiers du site (images, documents envoyés) ?

Ils se sauvegardent à part, avec le même principe (copie datée, rotation, copie distante). La base de données et les fichiers vont ensemble : sauvegardez les deux à la même fréquence.

Faut-il chiffrer les sauvegardes ?

Oui dès qu'elles quittent le serveur : elles contiennent des données personnelles. rclone sait chiffrer les fichiers avant l'envoi. Voir notre article sur les données personnelles en Côte d'Ivoire.

Être accompagné

NEWTIV met en place et surveille les sauvegardes de vos serveurs, bases de données et sites web, avec copie distante chiffrée et tests de restauration réguliers. Si vous démarrez sur votre propre serveur, voir aussi notre tutoriel VPS avec Nginx et HTTPS. Demandez un devis, nous vous répondons sous 48 heures ouvrées.