Sauvegarde et restauration de bases de données PostgreSQL et MySQL
La sauvegarde est l'une des procédures clés qui garantit la sécurité des données. Même avec un hébergement fiable, des erreurs d'application, des mises à jour incorrectes ou une suppression accidentelle de données peuvent survenir.
Dans ce guide, nous allons montrer comment créer et restaurer des sauvegardes des bases de données PostgreSQL et MySQL en utilisant les utilitaires de ligne de commande standard — pg_dump et mysqldump.
1. Sauvegarde PostgreSQL
1.1. Qu'est-ce que pg_dump
pg_dump est un utilitaire standard de PostgreSQL pour créer une sauvegarde logique de la base de données. Il enregistre à la fois la structure (DDL) et les données (DML) dans un fichier SQL texte qui peut être utilisé ultérieurement pour la restauration.
1.2. Syntaxe de la commande
pg_dump -U <utilisateur> -h <hôte> -p <port> -d <base_de_données> -F p -f <chemin_vers_fichier.sql>
1.3. Exemple de sauvegarde
pg_dump -U monutilisateur -h localhost -d mabasededonnées -F p -f /sauvegardes/mabasededonnées_$(date +%F).sql
Explication des paramètres :
- -U monutilisateur — nom d'utilisateur PostgreSQL ;
- -h localhost — hôte du serveur (IP peut être spécifiée) ;
- -d mabasededonnées — nom de la base de données ;
- -F p — format de sortie (plain — SQL régulier) ;
- -f — chemin vers le fichier où la sauvegarde sera enregistrée ;
- $(date +%F) — ajoute la date actuelle au nom du fichier (par exemple, 2025-11-03).
1.4. Sauvegarde compressée
Pour réduire la taille du fichier, vous pouvez utiliser la compression gzip :
pg_dump -U monutilisateur mabasededonnées | gzip > /sauvegardes/mabasededonnées_$(date +%F).sql.gz
2. Restauration de la base de données PostgreSQL
2.1. Utilisation de psql
Pour restaurer une base de données à partir d'une sauvegarde texte, utilisez :
psql -U monutilisateur -d mabasededonnées -f /sauvegardes/mabasededonnées_2025-11-03.sql
Si la sauvegarde est compressée :
gunzip -c /sauvegardes/mabasededonnées_2025-11-03.sql.gz | psql -U monutilisateur -d mabasededonnées
2.2. Si la base de données n'existe pas
Créez d'abord une nouvelle base de données :
createdb -U monutilisateur nouvelledatabase
psql -U monutilisateur -d nouvelledatabase -f /sauvegardes/mabasededonnées_2025-11-03.sql
3. Sauvegarde MySQL
3.1. Qu'est-ce que mysqldump
mysqldump est un utilitaire standard pour créer des dumps MySQL. Il enregistre les commandes SQL nécessaires pour recréer les tables et insérer les données.
3.2. Syntaxe de la commande
mysqldump -u <utilisateur> -p -h <hôte> <base_de_données> > <chemin_vers_fichier.sql>
3.3. Exemple de sauvegarde
mysqldump -u monutilisateur -p mabasededonnées > /sauvegardes/mabasededonnées_$(date +%F).sql
Explication des paramètres :
- -u monutilisateur — nom d'utilisateur MySQL ;
- -p — demande un mot de passe ;
- mabasededonnées — nom de la base de données ;
- > — redirige la sortie vers un fichier.
3.4. Dump compressé
mysqldump -u monutilisateur -p mabasededonnées | gzip > /sauvegardes/mabasededonnées_$(date +%F).sql.gz
4. Restauration de la base de données MySQL
4.1. Restauration à partir d'un fichier SQL
mysql -u monutilisateur -p mabasededonnées < /sauvegardes/mabasededonnées_2025-11-03.sql
4.2. Si la base de données n'existe pas
Créez une nouvelle base de données avant de restaurer :
mysql -u monutilisateur -p -e "CREATE DATABASE nouvelledatabase;"
mysql -u monutilisateur -p nouvelledatabase < /sauvegardes/mabasededonnées_2025-11-03.sql
4.3. Restauration à partir d'un dump compressé
gunzip -c /sauvegardes/mabasededonnées_2025-11-03.sql.gz | mysql -u monutilisateur -p mabasededonnées
5. Recommandations d'automatisation et de sécurité
5.1. Sauvegardes automatisées avec Cron
Pour des processus de sauvegarde réguliers sans intervention manuelle, configurez l'automatisation en utilisant cron.
Configuration de base de cron :
# Ouvrir l'éditeur de cron crontab -e
Ajoutez une ligne, par exemple :
0 2 * * * /usr/local/bin/db_backup.sh
Cette commande exécutera le script quotidiennement à 2h00 du matin.
Approche avancée en utilisant un script. Créez /usr/local/bin/db_backup.sh :
#!/bin/bash DATE=$(date +%F) BACKUP_DIR="/sauvegarde/$DATE" mkdir -p $BACKUP_DIR # Sauvegarde PostgreSQL pg_dump -U monutilisateur mabasededonnées | gzip > $BACKUP_DIR/postgres_mabasededonnées.sql.gz # Sauvegarde MySQL mysqldump -u monutilisateur -p mabasededonnées | gzip > $BACKUP_DIR/mysql_mabasededonnées.sql.gz # Supprimer les sauvegardes de plus de 7 jours find /sauvegarde/ -type f -mtime +7 -delete
Rendre le script exécutable :
chmod +x /usr/local/bin/db_backup.sh
Et ajoutez à cron :
0 2 * * * /usr/local/bin/db_backup.sh
5.2. Meilleures pratiques de sécurité et de stockage
- Stockage hors serveur : stockez les sauvegardes dans un stockage cloud (AWS S3, Google Cloud) ou sur des disques externes.
- Contrôle d'accès : restreignez l'accès au dossier /sauvegardes, car les fichiers peuvent contenir des données confidentielles.
chmod 700 /sauvegarde
chmod 600 /sauvegarde/*.sql.gz
- Vérification de l'intégrité : effectuez des restaurations de test mensuelles pour vérifier la fonctionnalité des sauvegardes.
- Confidentialité : les fichiers de sauvegarde contiennent le contenu des bases de données — traitez-les comme des informations confidentielles.
Conclusion
Utiliser pg_dump et mysqldump est un moyen facile, fiable et universel de sauvegarder les bases de données PostgreSQL et MySQL. Ces outils conviennent aussi bien pour les sauvegardes manuelles que pour les processus automatisés avec un minimum d'effort.
Les sauvegardes régulières sont la clé de la stabilité et de la sécurité des données de votre projet.
