Comment sauvegarder et restaurer vos bases MySQL

Les bases de données MySQL figurent parmi les systèmes de gestion de bases de données relationnelles (SGBD-R) les plus répandus. Leur popularité s'explique par leur facilité d'installation, leur robustesse et leur compatibilité avec les technologies web modernes.

Dans cet article, nous vous présentons un guide complet pour :

• sauvegarder vos bases de données,
• sécuriser vos informations,
• restaurer vos bases en cas de problème.

Sauvegarder vos bases de données MySQL

Utilisez la commande suivante pour sauvegarder une base de données dans un fichier dump SQL :

mysqldump -u <username> -p <database_name> > dump.sql

Pour sauvegarder plusieurs bases de données, ajoutez leurs noms à la commande :

mysqldump -u <username> -p --databases <db1> <db2> > dump.sql

Si vous souhaitez sauvegarder toutes les bases de données de votre serveur:

mysqldump -u root -p --all-databases > all_databases.sql

💡Conseil: Créez un fichier dump par base de données. Cela vous permet une meilleure gestion et facilite les restaurations ciblées.

Options courantes pour optimiser vos sauvegardes

Certaines options peuvent être utiles pour adapter vos sauvegardes à vos besoins spécifiques :

• --triggers : Inclut les triggers (déclencheurs) dans le dump.
• --single-transaction : Évite le verrouillage des tables lors de la sauvegarde. Idéal pour de grosses bases de données.
• --routines : Sauvegarde les routines (procédures stockées et fonctions).

Restaurer vos bases de données MySQL

En cas de perte ou de corruption, voici comment restaurer vos bases de données à partir d'un fichier dump.

Réinstaller MySQL (facultatif)

Si votre serveur MySQL a été supprimé ou corrompu, réinstallez-le avec :

apt install mysql-server

Recréer vos utilisateurs et privilèges

Maintenant votre base de données réinstallée, vous devez recréer vos bases de données, vos utilisateurs et leur accorder des privilèges.

1. Ouvrez un terminal MySQL

💡Conseil: Sur la majorité des systèmes, vous pouvez ouvrir le terminal mysql et vous connecter en lançant cette commande avec les droits superadmin: sudo mysql -u root

mysql

1. Créez votre base de donnée

CREATE DATABASE `my_database`;

1. Créez un nouvel utilisateur

CREATE USER 'new_user'@'localhost' IDENTIFIED BY 'password';

1. Accordez des privilèges à cet utilisateur

Pour toutes les bases de données :

GRANT ALL PRIVILEGES ON *.* TO 'new_user'@'localhost';

Pour une base de données spécifique :

GRANT ALL PRIVILEGES ON <database_name>.* TO 'new_user'@'localhost';

1. Forcez l'application immédiate des privilèges

FLUSH PRIVILEGES;

Restaurer vos bases de données

Utilisez la commande suivante pour restaurer une base de données à partir d'un fichier dump :

mysql -u <user> -p <database_name> < dump.sql

MySQL Cheatsheet: commandes utiles pour la gestion de bases de données

Afficher les privilèges d'un utilisateur

SHOW GRANTS FOR 'username'@'localhost';

Lister les utilisateurs MySQL

SELECT user, host FROM mysql.user;

Lister vos bases de donnés MySQL

SHOW DATABASES;

Afficher les utilisateurs connectés

SELECT current_user();

Supprimer un utilisateur MySQL

DROP USER 'username'@'localhost';

Supprimer des privilèges utilisateur

REVOKE ALL PRIVILEGES ON *.* FROM 'username'@'localhost';

Mettre à jour un mot de passe utilisateur MySQL

ALTER USER 'username'@'localhost' IDENTIFIED BY 'password';