Présentation et installation
L’outil mysqldump permet l’extraction et la sauvegarde de données ou de la base de données elle-même, c’est-à-dire que la sauvegarde peut inclure la définition du schéma de la BDD (tables, relations etc.).
Pour l’installer, comme il y est intégré c’est mysql-client qu’il faut installer.
sudo apt install mysql-client
Droits nécessaires
Il est nécessaire d’avoir les privilèges suivants :
SELECT: Pour que le dump intègre les tables.SHOW VIEW: Pour que le dump intègre les vues.TRIGGER: Pour que le dump intègre les triggers.- EVENT : Pour que le dump intègre les évènements les EVENTS de l’Event Scheduler.
Commandes de sauvegarde et comportements par défaut
Commande de base
Voici la description d’une commande de base permettant de sauvegarder une BDD :
mysqldump -uroot -p nom_bdd > backup.sql
- On passe par l’utilisateur root. L’utilisateur doit avoir les privilèges suffisant pour réaliser le dump souhaité.
- Le paramètre
-paura pour effet de demander le mot de passe à l’exécution de la commande. On ne met pas le mot de passe en clair directement dans nos commandes. - On indique ensuite le nom de la BDD à sauvegarder.
- On dirige la sortie vers un fichier SQL.
Comportements par défaut
Par défaut, mysqldump applique certains traitements aux dump. Pour les neutraliser il faut ajouter des paramètres spécifiques à la commande. Les paramètres appliqués par défaut sont les suivants :
| Paramètre | Effet |
|---|---|
--add-drop-table | Avant chaque CREATE TABLE, un DROP TABLE est ajouté. |
--add-locks | Verrouille les tables lors de la restauration. Permet une restauration plus rapide. |
--create-options | Intègre toutes les « attributs » MySQL aux CREATE TABLE. |
--disable-keys | Les indexes seront recréés après l’insertion de l’intégralité des données. Permet une restauration plus rapide. Effectif uniquement sur les tables MyISAM. |
--dump-date | Ajoute un commentaire à la fin du fichier avec la date du dump : -- Dump completed on YYYY-MM-DD |
--extended-insert | Les insertions de données sont écrites en multi-lignes, et pas un INSERT par enregistrement.Fourni un fichier dump moins lourd. Permet une restauration plus rapide. Risque de provoquer une erreur sur les colonnes potentiellement chargées (blob, longtext, json). |
--lock-table | Puisque MyISAM ne supporte pas les transactions, cet attributs a pour effet de verrouiller toutes les tables à la création du dump. Toutefois, si on utilise InnoDB, il vaut mieux désactiver cette option et utiliser --single-transaction. |
--quick | Chargement des lignes une-à-une au lieu de la table entière d’un seul coup. |
--set-charset | Ajoute l’instruction SET NAMES au dump. |
Sauvegarder une BDD distante
Si on ne fourni pas le paramètre -host, c’est une BDD locale que la commande tentera de sauvegarder. Ce paramètre permet donc la sauvegarde d’une BDD distante. Le port peut également être indiqué dans le paramètre --port.
mysqldump -host 10.222.333.44 -uroot --port 3306 -p nom_bdd > backup.sql mysqldump -host nom-de-domaine.com -uroot --port 3306 -p nom_bdd > backup.sql
Sauvegarder plusieurs BDD en une commande
# Sauvegader une BDD mysqldump nom_bdd > backup.sql # Sauvegarder plusieurs BDD mysqldump --databases nom_bdd_1 nom_bdd_2 > backup.sql # Sauvegarder toutes les BDD mysqldump --all-databases > backup.sql
Activer la compression GZIP
# Sauvegarder compression GZIP mysqldump nom_bdd | gzip > backup.sql.gz
Sauvegarder uniquement le schéma, ou uniquement les données
# Sauvegarder uniquement le schéma, sans les données donc mysqldump nom_bdd --no-data > backup.sql # # Sauvegarder uniquement les données, sans le schéma donc # # Retire les instructions CREATE TABLE mysqldump nom_bdd --no-create-info > backup.sql # Retire également l'instruction CREATE DATABASE mysqldump nom_bdd --no-create-info --no-create-db > backup.sql # Retire également la création des triggers mysqldump nom_bdd --no-create-info --no-create-db --skip-triggers > backup.sql # Retire également la création des fonctions et procédures stockées mysqldump nom_bdd --no-create-info --no-create-db --skip-triggers --skip-routines > backup.sql # Retire également la création des évènements de l'Event Scheduler mysqldump nom_bdd --no-create-info --no-create-db --skip-triggers --skip-routines --skip-events > backup.sql # # Autres éléments # # Sauvegarder les EVENTS de l'Event Scheduler mysqldump nom_bdd --no-data --events > backup.sql # Sauvegarder les fonctions et procédures stockées mysqldump nom_bdd --no-data --routines > backup.sql
Filtrer les données à sauvegarder
Filtrer les tables, par inclusion ou par exclusion
# # Sauvegarder uniquement certaines tables # mysqldump nom_bdd nom_table > backup.sql mysqldump nom_bdd nom_table_1 nom_table_2 > backup.sqlf # # Exclure des tables # mysqldump nom_bdd --ignore-table=nom_table > backup.sql mysqldump nom_bdd --ignore-table=nom_table_1 --ignore-table=nom_table_2 > backup.sql
Filtre les données
mysqldump nom_bdd nom_table --where="creation_date between '2024-12-01' and '2024-12-31'" > backup.sql
Sources :
– Informations pour la plupart issues de dev.mysql.com et mysqldump.guru