Automatiser les sauvegardes MySQL sur un VPS Linux avec mysqldump et cron

Sauvegardes MySQL automatiques sur VPS Linux avec mysqldump et cron

Sur un VPS Linux ou un serveur dédié hébergeant des bases de données MySQL ou MariaDB, la sauvegarde manuelle est insuffisante. Une perte de données liée à une erreur d'administrateur, une saturation du disque SSD ou une corruption de table peut être irréversible. Ce guide explique comment automatiser les dumps SQL de vos bases MySQL avec mysqldump planifié par cron, tester l'import et la restauration, et archiver les dumps compressés vers un stockage distant depuis la ligne de commande Unix.

Ce tutoriel s'adresse aux administrateurs d'un VPS Ubuntu ou Debian qui hébergent une ou plusieurs bases MySQL pour leurs applications web, serveurs de jeu (FiveM, Minecraft) ou scripts automatisés. Sans configuration de sauvegardes automatiques, une interruption du service MySQL ou une requête destructrice peut entraîner la perte de toutes les données de votre base de données mysql.

Prérequis : package MySQL, adresse IP et accès SSH

Avant d'administrer les sauvegardes automatiques, vérifiez les prérequis depuis votre ligne de commande SSH :

• Package MySQL 8.x ou MariaDB 10.x installé et en cours d'exécution sur le serveur Linux (vérifiez avec systemctl status mysql).
• Connexion SSH active depuis l'adresse IP de votre poste vers le serveur VPS Linux.
• Un nouvel utilisateur MySQL dédié aux dumps avec les droits nécessaires (voir ci-dessous).
• Espace disque SSD suffisant pour stocker les dumps SQL (df -h pour vérifier).

Si vous n'avez pas encore installé MySQL sur votre serveur, consultez notre guide installer MySQL sur un VPS Linux.

Créer un nouvel utilisateur MySQL dédié aux sauvegardes

Ne sauvegardez jamais avec l'utilisateur root MySQL. Créez un nouvel utilisateur backup avec les droits minimaux pour l'export et les dumps de vos bases MySQL, en tapant la commande suivante depuis votre connexion SSH :

-- Se connecter au serveur MySQL en tant que root
mysql -u root -p

-- Creer le nouvel utilisateur backup
CREATE USER 'backup_user'@'localhost' IDENTIFIED BY 'MotDePasseBackup';
GRANT SELECT, SHOW VIEW, RELOAD, LOCK TABLES,
      REPLICATION CLIENT, EVENT, TRIGGER ON *.* TO 'backup_user'@'localhost';
FLUSH PRIVILEGES;

Pour créer et gérer des utilisateurs MySQL sur votre VPS Linux, consultez notre guide créer un utilisateur admin MySQL.

Stockez les identifiants de connexion dans un fichier de config MySQL protégé (~/.my.cnf) pour éviter de les exposer dans les logs du serveur Linux :

[mysqldump]
user=backup_user
password=MotDePasseBackup

chmod 600 ~/.my.cnf

Avec cette config, la commande suivante mysqldump lira automatiquement les identifiants de connexion sans les exposer dans les logs du serveur Linux. Une bonne pratique pour administrer vos bases de données mysql en production.

Script de dump MySQL automatique avec gzip

Créez un répertoire de sauvegarde et le script shell sur votre VPS Linux depuis la ligne de commande. Installez d'abord le package gzip si nécessaire (apt install gzip) :

sudo mkdir -p /var/backups/mysql
sudo chmod 750 /var/backups/mysql
nano /usr/local/bin/mysql-backup.sh

Contenu du script de dump MySQL automatique. Ce script crée une base de données mysql compressée à chaque exécution :

#!/bin/bash
BACKUP_DIR="/var/backups/mysql"
DATE=$(date +%Y-%m-%d_%H-%M)
RETENTION_DAYS=7

# Export dump SQL de toutes les bases MySQL avec compression gzip
mysqldump --single-transaction --quick --routines --events --triggers \
  --all-databases | gzip > "${BACKUP_DIR}/all-databases_${DATE}.sql.gz"

# Supprimer les dumps plus anciens que RETENTION_DAYS jours
find "${BACKUP_DIR}" -name "*.sql.gz" -mtime +${RETENTION_DAYS} -delete

chmod +x /usr/local/bin/mysql-backup.sh

L'option --single-transaction est indispensable pour les tables InnoDB : elle réalise un dump de votre base de données mysql via un snapshot transactionnel sans bloquer les connexions ni les requêtes actives en production. L'option --quick réduit la consommation RAM du serveur en récupérant les lignes une par une plutôt qu'en chargeant toute la table en mémoire.

Tester le script depuis la ligne de commande

Avant de planifier la tâche cron, testez le script en SSH sur votre VPS Linux. Entrez la commande suivante :

/usr/local/bin/mysql-backup.sh
ls -lh /var/backups/mysql/

Vérifiez que le dump SQL .sql.gz est créé et non vide. Testez la décompression pour valider l'intégrité de l'export depuis la ligne de commande :

zcat /var/backups/mysql/all-databases_*.sql.gz | head -30

La sortie doit afficher des instructions SQL valides (CREATE DATABASE, CREATE TABLE, INSERT INTO). Un dump vide ou corrompu indique un problème de droits MySQL ou de mémoire disponible sur le serveur Linux.

Planifier le dump MySQL avec cron sur le serveur VPS

Ouvrez la crontab de root sur votre serveur Linux pour planifier l'export automatique de votre base de données mysql :

crontab -e

Ajoutez une ligne pour exécuter le script de dump MySQL chaque nuit à 2h00 :

# Sauvegarde automatique MySQL quotidienne a 2h00
0 2 * * * /usr/local/bin/mysql-backup.sh >> /var/log/mysql-backup.log 2>&1

La redirection enregistre la sortie et les erreurs dans un fichier de log. Pour surveiller les exécutions passées et détecter un dump manqué sur votre VPS Linux :

tail -50 /var/log/mysql-backup.log

Pour une gestion avancée des tâches automatiques et des scripts planifiés sur votre serveur Linux, consultez notre guide cron et systemd timers.

Dump sélectif d'une seule base de données MySQL

Pour un export sélectif d'une seule base de données mysql (application web, serveur de jeu FiveM ou Minecraft), modifiez le script pour cibler une base précise. Entrez la commande suivante :

mysqldump --single-transaction --quick --routines --triggers \
  nom_de_la_base_mysql | gzip > /var/backups/mysql/nom_de_la_base_${DATE}.sql.gz

Cette approche facilite l'import et la restauration sélective depuis la ligne de commande, sans affecter les connexions aux autres bases de données du serveur. C'est différent de PostgreSQL qui utilise pg_dump : avec MySQL et mysqldump, la restauration ne nécessite pas de redémarrer le serveur de base de données.

Tester l'import et la restauration MySQL depuis le dump

Un script de sauvegarde non testé en import n'est pas fiable. Sur votre VPS Linux, testez régulièrement la restauration depuis la ligne de commande SSH. Entrez la commande suivante pour restaurer votre base de données mysql :

# Importer et restaurer toutes les bases MySQL depuis le dump
zcat /var/backups/mysql/all-databases_DATE.sql.gz | mysql -u root -p

# Importer une base MySQL specifique depuis le dump SQL
zcat /var/backups/mysql/mabase_DATE.sql.gz | mysql -u root -p mabase

Effectuez ce test d'import sur un environnement de staging, sans interrompre les connexions à la base MySQL en production, avant de valider votre stratégie de sauvegarde automatique sur le serveur.

Envoyer les dumps hors du VPS Linux

Les dumps SQL stockés uniquement sur le même VPS Linux sont perdus en cas de panne matérielle. Utilisez rsync ou rclone pour transférer les dumps SQL vers un stockage distant après chaque export de votre base de données mysql. Notre guide sauvegarder un VPS avec rsync couvre ce transfert. Pour chiffrer les dumps avant envoi, consultez notre guide chiffrer les sauvegardes hors site.

Surveiller l'espace disque des dumps MySQL

Les dumps SQL compressés peuvent croître rapidement sur un VPS SSD hébergeant des bases MySQL volumineuses. Surveillez l'espace disque disponible depuis la ligne de commande Linux :

# Espace total du repertoire de dumps MySQL
du -sh /var/backups/mysql/

# Espace libre sur le VPS Linux
df -h /var/backups/mysql/

Pour anticiper la saturation du disque SSD avant qu'elle n'affecte les connexions à votre base de données mysql ou les requêtes de vos applications web, configurez des alertes de monitoring avec notre guide alertes de monitoring VPS Linux. La rotation automatique des anciens dumps (option -mtime dans le script) est indispensable pour administrer et maîtriser l'espace du serveur Linux.