Serveurs Base de données

MYSQL : Sauvegarder et restaurer ses bases de données

Cet article a été mis à jour, vous consultez ici une archive de cet article!
Table des matières
  1. Introduction
  2. Sauvegarde
  3. Restauration
  4. Copier à chaud une base à une autre

Introduction



Dans cet article, on va voir comment sauvegarder et restaurer ses bases de données.
Autant un site web, on peut faire un .tar.bz2 des fichiers, autant avec MySQL ou MariaDB, on va utiliser des commandes spécifiques !

Sauvegarde



Pour sauvegarder sa base de données, on va utiliser la commande mysqldump !

Pour sauvegarder une base de donnée précise, l'utilisateur doit avoir les pleins droits sur la base et toutes les tables (exemple la base linuxtricksdb qui est gérée par ltuser) :

Code BASH :
mysqldump -u ltuser -p linuxtricksdb > backup_linuxtricksdb.sql


Vous pouvez passer dans un script le mot de passe de l'utilisateur (même si pas très sécure) en collant le mot de passe à l'option -p et ajouter des variables comme bash le permet (exemple la date) :

Code BASH :
mysqldump -u ltuser -pltpassword linuxtricksdb > backup_linuxtricksdb_$(date +%Y-%m-%d--%H-%M-%S).sql


Vous pouvez ne sauvegarder que certaines tables de la base de données si vous le souhaitez :

Code BASH :
mysqldump -u ltuser -p linuxtricksdb table1 table2 table3 > backup_linuxtricksdb.sql


Vous pouvez aussi sauvegarder toutes les bases avec l'option --all-databases (Attention, ça sauvegarde même les tables mysql contenant les utilisateurs et la configuration !)

Code BASH :
mysqldump -u root -p --all-databases > backup-full_dbs.sql


La sauvegarde étant des instructions SQL, c'est du texte. C'est donc parfois volumineux et facilement compressible !

Voici un exemple pour compresser à la volée en une seule commande :

Avec Gzip :
Code BASH :
mysqldump -u ltuser -p linuxtricksdb | gzip -c > backup_linuxtricksdb.sql.gz
mysqldump -u root -p --all-databases | gzip -c > backup-full_dbs.sql.gz



Ou Bzip2 :
Code BASH :
mysqldump -u ltuser -p linuxtricksdb | bzip2 -c > backup_linuxtricksdb.sql.bz2
mysqldump -u root -p --all-databases | bzip2 -c > backup-full_dbs.sql.bz2



Restauration



Voici la partie restauration. On peut restaurer pour :
- Tester la sauvegarde en restaurant sur une autre base
- Restaurer car on a besoin de restaurer donc sur la même base

Quand on a sauvegardé qu'une base, pour la restaurer, la base doit déjà exister (si c'est sur une nouvelle installation) (cf https://www.linuxtricks.fr/wiki/sql-guide-des-commandes-de-base ) :

Code BASH :
mysql -u root -p linuxtricksdb < backup_linuxtricks.sql


Pour restaurer un dump où on n'a sauvegardé que certaines tables, c'est la même manipulation. Seules les tables sauvegardées seront restaurées.

Pour restaurer une sauvegarde de --all-databases : (Attention, ça restaure même les tables mysql contenant les utilisateurs et la configuration !)

Code BASH :
mysql -u root -p < backup-full_dbs.sql


Pour restaurer une base sauvegardée gzippée :

Code BASH :
gunzip -c backup_linuxtricksdb.sql.gz | mysql -u root -p linuxtricksdb


Pour restaurer une base sauvegardée bzippée :

Code BASH :
bunzip2 -c backup_linuxtricksdb.sql.bz2 | mysql -u root -p linuxtricksdb


Et pour la sauvegarde avec all-databases :

Code BASH :
gunzip -c backup-full_dbs.sql.bz2 | mysql -u root -p


Code BASH :
bunzip2 -c backup-full_dbs.sql.bz2 | mysql -u root -p


A noter, la restauration avec --all-databases peut provoquer des erreurs de connexion pour certains utilisateurs.
Il suffit donc de se connecter en root au moteur de base de données et d'exécuter :

Code SQL :
FLUSH PRIVILEGES;


Copier à chaud une base à une autre



Il est tout à fait possible de copier à chaud une base vers une autre en combinant mysql et mysqldump.
Pratique pour créer une base de test à partir de la prod :

Code BASH :
mysqldump -u root -p linuxtricksdbsource | mysql -u root -p linuxtricksdbcible