Comment sauvegarder vos bases de données MySQL

Shared FAQ page chrome (breadcrumb, dates, read time, «Continuer la lecture», enquiry block, category labels): see INDEX-AND-CHROME.md. Translate once.

Body

Introduction

Créer une copie physique des répertoires du moteur MySQL en vue d'une utilisation future ne fonctionne pas toujours, et la restauration est difficile et pleine de procédures compliquées. Nous allons voir deux méthodes qui permettent de sauvegarder et de restaurer vos bases de données correctement.

#1. mysqldump

MySQL et MariaDB sont livrés avec un utilitaire appelé mysqldump, qui permet de sauvegarder facilement vos bases hébergées. Le backup SQL produit par cet utilitaire est l'ensemble complet des instructions SQL qui reproduisent la base source ; à la restauration, il recrée les objets de la base dans la même instance MySQL/MariaDB ou dans une autre à partir de ces instructions. Le backup produit est portable : vous pouvez ouvrir le fichier dans n'importe quel éditeur de texte et modifier les instructions SQL si nécessaire.

Utiliser mysqldump pour toutes les bases

La commande mysqldump peut sauvegarder toutes les bases déployées sur le serveur MySQL avec la syntaxe ci-dessous. Vous devez utiliser les identifiants de l'utilisateur administrateur MySQL. Une fois lancée, elle vous demande le mot de passe de l'administrateur et le backup est écrit à l'emplacement que vous indiquez pour le fichier de sortie.

mysqldump -u root -p --all-databases > all-DB-dump-$( date '+%Y-%m-%d_%H-%M-%S' ).sql

Utiliser mysqldump pour une seule base.

Vous pouvez utiliser les identifiants de l'administrateur ou ceux de l'utilisateur de cette base précise. Vérifiez que l'utilisateur de la base dispose des droits GRANT SELECT, SHOW VIEW, RELOAD, REPLICATION CLIENT, EVENT, TRIGGER sur la base. Les commandes ci-dessous, lancées depuis un shell MySQL, créent l'utilisateur et lui accordent les droits nécessaires aux opérations de backup.

CREATE USER 'bkpuser'@'localhost' IDENTIFIED BY '<password>';
GRANT SELECT, SHOW VIEW, RELOAD, REPLICATION CLIENT, EVENT, TRIGGER ON db_name.* TO 'bkpuser'@'localhost';

Remplacez bkpuser par le nom d'utilisateur que vous voulez pour l'utilisateur de backup, password par le vrai mot de passe de cet utilisateur et db name par le nom de la base. Vous pouvez ensuite utiliser bkpuser et ses identifiants pour sauvegarder la base db_name avec la syntaxe ci-dessous.

mysqldump -u [username] -p[password] -h [host] [databaseName] > [backup-name].sql

OU

mysqldump -u bkpuser -p<password> db_name > db_name.sql

Utiliser mysqldump pour une seule table

Vous pouvez utiliser mysqldump avec la syntaxe ci-dessous pour sauvegarder une seule table de la base.

mysqldump -u [username] -p[password] -h [host] [databaseName] [tableName] > [backup-name].sql

OU

mysqldump -u bkpuser -p<password> db_name table_name > table_name.sql

Restaurer le backup produit.

Le client mysql permet de restaurer ou d'importer le fichier .sql dans une base. La commande ci-dessous, depuis un shell MySQL, crée une base vide pour la restauration.

mysql> create database db_name;
Query OK, 1 row affected (0.08 sec)

Utilisez le client mysql avec la syntaxe ci-dessous pour lancer la restauration depuis le fichier .sql.

mysql -u [username] -p [databaseName] < backupFile.sql

L'utilitaire mysqldump permet aussi de sauvegarder une base depuis un serveur distant ; la syntaxe est la suivante.

mysqldump --host hostaddress -P portnumber -u username -ppassword dbname > dbname.sql

#2 Avec phpMyAdmin

phpMyAdmin est livré avec cPanel et Plesk et permet d'exporter et d'importer une base depuis le navigateur.

Connectez-vous à phpMyAdmin relié à votre base, cliquez sur l'onglet Export, choisissez le format du fichier de backup, sélectionnez SQL dans la liste déroulante et cliquez sur le bouton « Go ». Le backup est produit et téléchargé dans la même session de navigateur.

Sélectionnez l'onglet « Import », cliquez sur le bouton « Choose File », parcourez votre machine locale jusqu'au fichier de backup et cliquez sur « Go » pour restaurer un backup depuis phpMyAdmin.