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.

6 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 » 🙂

Vous avez aimé cet article ? Réagissez !

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