Serveurs Base de données

MYSQL : Sauvegarder et restaurer ses bases de données

Table des matières

Introduction
A propos de la terminologie
Sauvegarde
Restauration
Copier à chaud une base à une autre

Introduction

Dans cet article, on va voir comment sauvegarder et restaurer ses bases de données.
Pour manipuler les extractions et importations de ses bases de données MySQL et MariaDB, on va utiliser des commandes spécifiques.

A propos de la terminologie

L'utilisation des mots "sauvegarde" et "restauration" sont un peu abusifs.

En effet, un dump de base de données est une représentation structurée des données et de la structure de la base de données dans un format spécifique, tel qu'un fichier texte ou binaire à un instant précis.
Il ne s'agit pas d'une copie exacte de l'état actuel de la base de données telle que la sauvegarde pourrait se définir.

C'est pour cela que le terme exact serait un export des données plutôt qu'une sauvegarde.

Cependant, c'est un bon début pour "sauvegarder" ses bases de données, et que cela convient pour la majorité des cas.

Evidemment, à l'inverse, on ne pourra pas parler de "restauration", mais d'un import.

J'utiliserai l'abus de langage "Sauvegarde" et "Restauration" pour ne pas vous perdre.

Sauvegarde

Pour sauvegarder sa base de données, on va utiliser la commande mysqldump !
Depuis MariaDB 10.5, c'est la commande mariadb-dump qu'on pourra utiliser (l'alias mysqldump existe toujours et renvoie vers mariadb-dump)

Il est possible d'avoir de l'aide sur la commande et surtout les valeurs définies des options grâce à la commande :

Code BASH :

mysqldump --help

Pour sauvegarder une base de donnée précise, l'utilisateur doit avoir les pleins droits sur la base et toutes les tables. Parmi les options :
-u : permet de passer le nom de l'utilisateur (sinon, c'est le nom de l'utilisateur connecté sur le système)
-p : demande de saisir le mot de passe de l'utilisateur concerné
arg1 : le premier argument est le nom de la base à sauvegarder

Code BASH :

mysqldump -u ltuser -p linuxtricksdb > backup_linuxtricksdb.sql

Vous pouvez passer dans un script le mot de passe de l'utilisateur (ce n'est pas conseillé évidemment) en collant le mot de passe à l'option -p. En plus, on va mettre la date dans le nom du fichier :

Code BASH :

mysqldump -u ltuser -pltpassword linuxtricksdb > backup_linuxtricksdb.sql

On va pouvoir exporter certaines tables de la base de données en les listant à la suite du nom de la base de données :

Code BASH :

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

Exclure certaines tables de l'export de la base de données :

Code BASH :

mysqldump -u ltuser -p linuxtricksdb --ignore-table=linuxtricksdb.cache > backup_linuxtricksdb.sql

Je vous propose quelques options intéressantes en plus comme :
-h bdd01.linuxtricks.lan : Permet de se connecter à un serveur de base de données distant (sans cette option c'est localhost)
--single-transaction : Permet de faire un export consistant de toutes les tables (lorsqu'elles utilisent le moteur InnoDB)

Bien que je n'utilise pas cette option, on peut aussi aussi sauvegarder toutes les bases avec l'option --all-databases, mais attention, ça sauvegarde même les tables de la base 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 de sauvegarde de la base linuxtricksdb compressée à la volée avec gzip (je n'ai pas mis d'options) :

Code BASH :

mysqldump -u ltuser -pltpassword linuxtricksdb | gzip -c > backup_linuxtricksdb.sql.gz

On pourra créer un petit script aussi pour sauvegarder ses bases de données :

Code BASH :

#!/bin/bash
DTE=$(date +%Y-%m-%d--%H-%M-%S)
BKP="/home/sauvegardes/"
NB="30"
for DB in $( mysql -N -e "SHOW DATABASES;")
do
    mysqldymp -u usersauvegarde $DB |gzip -c > "$BKP/$DB-$DTE.sql.gz
done
find "/home/sauvegardes/" -name "*.sql.gz" -mtime +"$NB" -exec rm -f "{}" \;

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