Sauvegarder et restaurer MySql

sauvegarde mysql

Pour Sauvegarder et restaurer des données Mysql en ligne de commande on utilise les utilitaires mysqldump et mysql.
De préférence on utilise le compte admin créé lors de l’installation de MySql : ici c’est l’utilisateur root.

Sauvegarde MySql

Il y a différents types de sauvegarde mysql.
On peut sauvegarder toutes les bases de données, une seule base de données ou encore certaines tables d’une ou de plusieurs bases.

Pour sauvegarder toutes les bases de données on utilise la commande suivante :

mysqldump --user=mon_user --password=mon_password --all-databases > fichier_destination.sql

Pour sauvegarder une base de données précise :

mysqldump --user=mon_user --password=mon_password --databases nom_de_la_base > fichier_destination.sql

Pour sauvegarder plusieurs bases de données :

mysqldump --user=mon_user --password=mon_password --databases nom_de_la_base_1 nom_de_la_base_2 > fichier_destination.sql

Pour sauvegarder une table précise :

mysqldump --user=mon_user --password=mon_password --databases nom_de_la_base --tables nom_de_la_table > fichier_destination.sql

Pour sauvegarder plusieurs tables :

mysqldump --user=mon_user --password=mon_password --databases nom_de_la_base --tables nom_de_la_table_1 nom_de_la_table_2 > fichier_destination.sql

Pour tous ces types de sauvegarde mysql les fichiers générés sont en .sql. Cela dit on peut mettre l’extension que l’on veut comme .txt par exemple. Ca n’a pas d’importance.
Les fichiers sont lisibles en clair et contiennent toutes les commandes SQL pour pouvoir faire une restauration mysql.

Cela dit les fichiers .sql sont volumineux. Pour gagner de la place, on va les compresser à la volée lors de la sauvegarde.

Compresser la sauvegarde mysql

Pour compresser les données, nous allons utiliser gunzip après avoir invoqué mysqldump.

Cela donnera des commandes du type :

mysqldump <commandes> | gzip > fichier_destination.sql.gz

Exemple avec la commande de sauvegarde complète :

mysqldump --user=mon_user --password=mon_password --all-databases | gzip > fichier_destination.sql.gz

A noter qu’avec cette méthode, la commande de sauvegarde mysql produit un seul fichier compressé. le fichier source n’existe plus.

Restauration MySql

Pour la restauration mysql on n’utilise plus la commande mysqldump. On utilise la commande mysql.
La commande mysql va aller lire un fichier puis le restaurer en fonction de son contenu et de ses instructions.

La restauration mysql peut se faire pour toutes les bases de données ou pour un base de données précise.

Exemple sans distinction de base de données :

mysql --user=mon_user --password=mon_password < fichier_source.sql

Autre exemple pour restaurer dans une base de données précise :

mysql --user=mon_user --password=mon_password nom_de_la_base < fichier_source.sql

Maintenant que tout est clair, on peut programmer une tâche CRON pour sauvegarder à intervalle régulier nos bases de données.

16 commentairesLaisser un commentaire

  • Bonjour

    Je cherche justement à créer un user qui aura juste les droits de sauvegarde/restauration avec la commande mysqldump.

    Quels droits dois je donner a celui-ci?

  • Bonsoir tuxmika,

    Je pense qu’il suffit de créer des utilisateurs linux dans un premier temps et ensuite de leurs donner les spécifiques à mysql.

  • Bonjour, Y a t-il avec cette méthode une limitation quand à la taille maximale du fichier de DUMP ainsi obtenu ?
    Pour ma part j’utilise “mysql workbench” qui a créé un dump de 41Go et qu’il ne peut vraisemblablement pas restaurer …

  • Bonsoir PM,

    Quand les bases sont si grandes il vaut mieux s’abstenir de passer par des outils tiers pour utiliser directement la ligne de commande sur le serveur.
    On ne subit pas ainsi les contraintes de la couche réseau ou autres limitations des outils.

    a+

  • Salut,

    Sur certains serveurs tu n’as pas la place de décompresser une archive sauvegardée. Tu peux donc lire le fichier compressé et importer en direct :

    zcat fichier_source.sql.gz | mysql –user=mon_user –password=mon_password
    ou : cat fichier_source.sql.gz | gunzip | mysql –user=mon_user –password=mon_password

    Tu peux aussi te servir de PV (progress viewer) pour avoir une bare d’avancement et un temps de fin estimatif :

    non compressé : pv fichier_source.sql | mysql –user=mon_user –password=mon_password
    compress : pv fichier_source.sql.gz | gunzip | mysql –user=mon_user –password=mon_password

  • Merci pour ce billet et les commentaires, c’est exactement ce dont j’ai besoin pour commencer la semaine afin de basculer une base MySQL en “InnoDB File-Per-Table” à “True” 🙂

  • Super franchement, j’ai pu dump et recharger ma base de données en quelques minutes, vraiment pratique !

  • bonjour,
    je me demandais s’il était possible de sauvegarder seulement certaines données d’une table
    je m’explique, j’ai une table project dans ma base de donnée (myBDD) et je souhaiterais sauvegarder que l’un des projet présent dans cette table
    merci et bonne journée

  • Bonsoir veyretout,

    Non tu ne peux pas.
    Une solution serait de faire un SELECT des données que tu souhaites, et ensuite tu INSERT ces données dans une table dédiée à la sauvegarde.
    Et enfin tu sauvegardes la table en question.

    A bientôt

  • Malheureusement ça ne fonctionne pas sur serveur distant mais uniquement en local.
    Pour ma part aucune commande n’a fonctionné, j’ai pourtant fait une 40 aine de site mais sur aucun d’eux, ne se trouve un tuto pour serveur distant.

  • Bonsoir dudul,

    les commandes expliquées sont en effet pour un serveur local. Dans le sens “sur lequel on se trouve”.
    Si vous voulez faire un dump sur un serveur distant il faut d’abord initier une connexion SSH. Comme ceci par exemple :

    ssh -l user adresse_de_mon_serveur_distant “mysqldump …” > fichier_destination.sql

  • Salut, merci pour ces info ! j’ai my sql 5.6.4
    qd je fais mon dump il me met , pourtant c bien une commande ancestral !! comment est-ce possible que je n’ai pas cela ?
    Meric

    Command ‘mysql’ not found, but can be installed with:

    apt install mysql-client-core-5.7
    apt install mariadb-client-core-10.1

  • il vous faut installer mysql-client / mariadb-client comme indiqué
    C’est ce package qui fourni la commande

  • Top, merci pour cet article, il est très clair, simple, rien à redire.
    Avant je faisais mes sauvegardes en PHP et j’avais des timeout dont je n’arrivais pas à me défaire, maintenant ça va 3 fois plus vite et surtout ça ne coupe pas ! Merci

  • bonjours j’ai du mal a sauvegarder mes données de mysql sur mon MacBook pro comment je devrais faire quand je fais exporter ca passe pas

  • Bonjour,
    J’ai téléchargé sur le serveur distant OVH une sauvegarde de base de données “fichier dump nommé : collectiw.dump”
    Il est situé à cette arborescence sur mon PC :
    C:/Users/pierr/Downloads/base de données Collectif Justice/collectiw.dump
    Comment créer cette base de donnée en local dans le MyAdmin de mon serveur local Wampserver64
    J’ai essayé une requête Import adossé à ce fichier mais ça ne fonctionne pas.
    Merci par avance pour votre aide

Vous avez aimé cet article ? Réagissez !

Votre email ne sera pas publié. Les champs requis sont marqués d'une astérisque *