Mysqldump

Fiche express
Type	Sauvegarde logique MySQL/MariaDB (export SQL)
Format de sortie	Flux d'instructions SQL (CREATE/INSERT) rejouable
Voir aussi	Mysql · Mysql config editor · Mysqlcheck

mysqldump est l'outil standard de sauvegarde logique de MySQL/MariaDB : il génère un fichier texte contenant les instructions SQL nécessaires pour recréer intégralement la structure et le contenu d'une ou plusieurs bases. Ce fichier peut ensuite être rejoué tel quel avec le client mysql.

Avertissement sécurité : passer le mot de passe collé à -p (-pMotDePasse) ou en clair dans un script l'expose dans l'historique shell et dans la liste des processus (ps aux). Pour des sauvegardes automatisées, préférer mysql_config_editor (--login-path) ou un fichier ~/.my.cnf à permissions restreintes.

Sauvegarde

Une seule base :

mysqldump -u root -p --databases ma_base > /root/dump_base.sql

Une base avec identifiants explicites :

mysqldump -u USER -pMOTDEPASSE --databases ma_base > ./fichier.sql

Toutes les bases du serveur :

mysqldump -u USER -pMOTDEPASSE --all-databases > ./toutes_les_bases.sql

Une seule table :

mysqldump -u USER -pMOTDEPASSE --databases ma_base --table ma_table > ./fichier.sql

Options utiles pour une sauvegarde cohérente

• --single-transaction — indispensable pour des tables InnoDB : effectue le dump dans une transaction unique (snapshot cohérent), sans verrouiller les tables ni bloquer les écritures concurrentes.
• --lock-tables — verrouille les tables pendant le dump ; utile pour MyISAM (qui ne supporte pas --single-transaction), mais bloquant pour les écritures le temps de l'opération.
• --routines / --triggers / --events — inclut respectivement les procédures/fonctions stockées, les triggers et les événements planifiés (non inclus par défaut pour les deux premiers selon la version).

Exemple recommandé pour une base InnoDB :

mysqldump -u root -p --single-transaction --routines --triggers --databases ma_base > /root/dump_base.sql

Restaurer une base

Depuis le shell :

mysql -u USER -p ma_base < ./fichier.sql

Depuis le client interactif, une fois connecté et la base sélectionnée (USE ma_base;) :

source /root/fichier.sql

Scripts d'automatisation

Sauvegarde de toutes les bases (hors bases système)

#!/bin/sh
DATE=$(date +%y_%m_%d)
DIR=/APPDIR/script/maintenance/backup
LOG=root
PASS=

LISTEBDD=$( echo 'show databases' | mysql -u ${LOG} -p${PASS} )

for SQL in $LISTEBDD
do
    if [ "$SQL" != "information_schema" ] && [ "$SQL" != "mysql" ] && [ "$SQL" != "Database" ] && [ "$SQL" != "performance_schema" ]; then
        mysqldump -u ${LOG} -p${PASS} "$SQL" | gzip > "${DIR}/${SQL}_mysql_${DATE}.sql.gz"
    fi
done

# Purge des sauvegardes de plus de 8 jours
find ${DIR}/ -name "*.sql*" -mtime +8 -exec rm -vf {} \;

Planification :

crontab -e
00 20 * * * /script/maintenance/backup_mysql.sh >> /tmp/mysqldump.log

Sauvegarde combinée base + fichiers d'un vhost

#!/bin/bash
USER=root
PASSWORD=
DATABASE=
# nom du répertoire vhost, sous /var/www/html/vhost/
VHOST=
HOME_DIR="/APPDIR/script/maintenance"
TMP_DIR="/tmp/backup_${VHOST}"
DATE=$(date +"%Y%m%d_%H%M%S")

rm -rf "${TMP_DIR}/"
mkdir -p "${TMP_DIR}/"

mysqldump -u ${USER} -p${PASSWORD} ${DATABASE} > "${TMP_DIR}/${VHOST}.sql"
cp -rp "/var/www/html/${VHOST}/" "${TMP_DIR}/${VHOST}/"
tar -czf "${HOME_DIR}/BACKUP_${VHOST}_${DATE}.tgz" "${TMP_DIR}/"

find ${HOME_DIR} -iname "BACKUP_${VHOST}*" -type f -ctime +5 -delete

Les variables USER/PASSWORD/DATABASE/VHOST sont volontairement vides dans ce squelette : à renseigner avant utilisation (ou mieux, à remplacer par un --login-path — voir Mysql config editor). Pas de --single-transaction dans ce script : à ajouter si les tables concernées sont en InnoDB.

Voir aussi

• Mysql config editor — pour ne pas stocker le mot de passe en clair dans les scripts
• Mysqlcheck — vérification avant/après restauration
• Reset password