Comment sauvegarder et restaurer une base de données MySQL

Si vous avez l'impression d'avoir déjà entendu cette question, vous n'êtes pas le seul. La perte de données est une préoccupation majeure pour les personnes qui gèrent et créent des bases de données. Elle peut être due à des erreurs commises par les utilisateurs, à des problèmes de matériel informatique ou à des attaques de pirates informatiques. La perte de données importantes dans une base de données MySQL peut causer beaucoup d'ennuis à une entreprise.

Savoir comment récupérer votre base de données MySQL à partir d'une copie sauvegardée peut donc vous sauver la mise.

Dans ce guide complet, nous vous montrerons comment sauvegarder et restaurer une base de données MySQL, en tapant des commandes sur l'ordinateur ou en utilisant des captures d'écran faciles à comprendre.

Comment sauvegarder une base de données MySQL ?

Dans cette section de notre article, nous avons listé une poignée de méthodes pour sauvegarder les fichiers de la base de données MySQL.

Méthode 1 : Sauvegarde de la base de données MySQL à l'aide de mysqldump

L'utilitaire mysqldump est une méthode éprouvée pour sauvegarder votre base de données MySQL. Il s'agit d'un outil en ligne de commande fourni avec chaque installation de MySQL, ce qui en fait un choix pratique pour les administrateurs de bases de données et les développeurs.

Vous devriez utiliser mysqldump si vous préférez les outils en ligne de commande et si vous souhaitez une solution flexible et scriptable. Cette méthode est très polyvalente et peut être facilement automatisée, ce qui la rend idéale pour les sauvegardes programmées. Il est préinstallé avec MySQL et génère des fichiers SQL lisibles par l'homme et portables.

Pour sauvegarder l'intégralité de votre base de données MySQL à l'aide de mysqldump, procédez comme suit :

Étape 1: Ouvrez votre terminal ou votre invite de commande. Naviguez jusqu'au répertoire bin de MySQL (e.g., cd /usr/local/mysql/bin).

Étape 2: Exécutez la commande suivante, en remplaçant [nom_de_base_de_données], [utilisateur] et [mot_de_passe] par le nom de votre base de données, l'utilisateur MySQL et le mot de passe, respectivement :

mysqldump -u [user] -p[password] [nom de la base de données] > [nom_du_fichier_de_sauvegarde].sql

Cette commande crée un fichier SQL contenant toutes les données et la structure de votre base de données MySQL.

Méthode 2 : Sauvegarder la base de données MySQL à l'aide de phpMyAdmin

Pour ceux qui préfèrent une interface utilisateur graphique (GUI), phpMyAdmin est un choix populaire. Cet outil basé sur le web offre un moyen intuitif de gérer et de sauvegarder vos bases de données MySQL.

Il est particulièrement utile pour les utilisateurs qui souhaitent une représentation visuelle de la structure de leur base de données et qui préfèrent les opérations de type "pointer et cliquer". L'interface intuitive facilite l'exportation de bases de données ou de tables spécifiques, et les utilisateurs peuvent personnaliser les paramètres d'exportation sans avoir à se souvenir d'une syntaxe de ligne de commande complexe.

Cette méthode est idéale pour les bases de données de petite ou moyenne taille et pour les utilisateurs qui préfèrent la facilité d'utilisation aux capacités de script avancées.

Voici comment sauvegarder une base de données MySQL à l'aide de phpMyAdmin :

Étape 1: Ouvrez votre navigateur web et accédez à l'interface phpMyAdmin. Connectez-vous avec vos identifiants MySQL.

Étape 2: Sélectionnez la base de données que vous souhaitez sauvegarder dans le panneau de gauche. Cliquez sur l'onglet "Exportation" en haut de l'écran.

Étape 3: Choisissez les options d'exportation que vous préférez. Pour une sauvegarde complète, vous pouvez laisser les paramètres par défaut. Faites défiler vers le bas et cliquez sur le bouton "Aller" pour lancer le processus de sauvegarde.

phpMyAdmin générera un fichier SQL contenant la sauvegarde de votre base de données, que vous pouvez enregistrer sur votre machine locale.

Méthode 3 : Sauvegarder la base de données MySQL avec Workbench

MySQL Workbench est un outil visuel de conception et de gestion de bases de données développé par Oracle. Il offre une interface conviviale pour la sauvegarde de vos bases de données MySQL.

Vous devriez utiliser MySQL Workbench si vous préférez une application de bureau avec une interface graphique et des fonctions de gestion de base de données supplémentaires. Cet outil complet, développé par Oracle, offre un riche ensemble de fonctionnalités pour la conception, le développement et l'administration de bases de données. Il offre une interface conviviale pour la sauvegarde des bases de données.

Pour sauvegarder la base de données MySQL avec Workbench, procédez comme suit :

Étape 1: Lancez MySQL Workbench et connectez-vous à votre serveur MySQL. Dans la barre latérale gauche, trouvez la section "Gestion" et cliquez sur "Exportation de données". Dans la liste des bases de données, sélectionnez celle que vous souhaitez sauvegarder.

Étape 2: Choisissez l'endroit où vous souhaitez enregistrer le fichier de sauvegarde. Vous pouvez l'enregistrer dans un dossier de projet ou sous la forme d'un fichier unique.

Étape 3: Personnalisez les options de sauvegarde si nécessaire, en choisissant par exemple une sauvegarde complète ou partielle, en activant la compression et en ajustant d'autres paramètres.

Étape 4: Passez en revue les paramètres de sauvegarde que vous avez configurés. Si tout semble correct, cliquez sur le bouton "Démarrer l'exportation" pour lancer le processus de sauvegarde.

Étape 5: Workbench vous indiquera la progression de la sauvegarde. Une fois la sauvegarde effectuée, vous recevrez une confirmation indiquant qu'elle est terminée.

Méthode 4 : Sauvegarder la base de données MySQL via un instantané du système de fichiers

Si vous avez accès au système de fichiers de votre serveur, vous pouvez créer une sauvegarde en prenant un instantané du répertoire de données MySQL. Cette méthode est particulièrement utile si vous devez sauvegarder plusieurs bases de données ou de grandes bases de données qu'il serait fastidieux de sauvegarder à l'aide des méthodes de vidage SQL.

File System Snapshot est idéal pour les environnements où il est crucial de minimiser les temps d'arrêt, car il permet de créer rapidement une sauvegarde cohérente sans affecter de manière significative les performances de la base de données. Il est particulièrement utile dans les environnements d'entreprise où les bases de données sont volumineuses et nécessitent des capacités de sauvegarde et de restauration rapides.

Voici comment sauvegarder une base de données MySQL via un instantané du système de fichiers :

Étape 1: Tout d'abord, déterminez où se trouve votre répertoire de données MySQL. Il s'agit du dossier dans lequel MySQL stocke tous ses fichiers de base de données. L'emplacement par défaut varie en fonction de votre système d'exploitation :

• Sous Linux, c'est généralement `/var/lib/mysql/`
• Sous Windows, il s'agit généralement de `C:\Program Files\MySQL\MySQL Server X.X\data\` (replace X.X with your MySQL version)

Étape 2: Avant de prendre l'instantané, nous devons mettre MySQL temporairement en pause pour nous assurer que toutes les données sont dans un état cohérent. Dans l'invite MySQL, exécutez la commande suivante :

EFFACER LES TABLES AVEC UN VERROU DE LECTURE;

Cette commande vide toutes les tables et empêche MySQL d'effectuer d'autres modifications.

Étape 3: Il est maintenant temps de créer l'instantané du répertoire de données MySQL. La commande exacte dépend de votre système de fichiers ou de votre solution de stockage. Par exemple, si vous utilisez LVM (Logical Volume Manager) sous Linux, vous pouvez exécuter :

lvcreate --snapshot --name mysqldata_snapshot --size 1G /dev/mapper/mysqlvg-mysqldata

Cette commande crée un instantané de 1 Go de votre volume de données MySQL.

Ou, si vous utilisez des systèmes de stockage NetApp (en utilisant ONTAP CLI), vous pouvez exécuter la commande suivante :

snapshot create -serveur vs_nom -volume vol_name -snapshot snap_nom

Étape 4: Ainsi, après avoir créé l'instantané, nous devons déverrouiller MySQL pour qu'il puisse recommencer à traiter les modifications. Exécutez cette commande :

DÉVERROUILLER LES TABLES ;

Étape 5: Enfin, vous pouvez sauvegarder l'instantané que vous venez de créer en le copiant ou en le déplaçant vers un autre emplacement, tel qu'un autre serveur, un disque dur externe ou un espace de stockage en nuage. L'instantané contient tous les fichiers de votre base de données MySQL au moment où vous l'avez créé.

N'oubliez pas que les commandes spécifiques pour la création de l'instantané peuvent varier en fonction de votre système de fichiers ou de votre solution de stockage, mais le processus général reste le même : mettre MySQL en pause, créer l'instantané, déverrouiller MySQL et sauvegarder l'instantané.

Méthode 5 : Sauvegarde de la base de données MySQL via le journal binaire

Le journal binaire de MySQL est un ensemble de fichiers qui enregistrent toutes les modifications apportées à vos bases de données. Bien qu'il soit principalement utilisé pour la réplication et la récupération ponctuelle, vous pouvez également utiliser le journal binaire pour créer une sauvegarde de votre base de données MySQL.

L'enregistrement binaire est parfait pour les situations où vous devez enregistrer chaque modification de la base de données et éventuellement les refaire pour restaurer les données. Cette technique est généralement combinée à des sauvegardes complètes pour créer un plan de sauvegarde exhaustif. Elle est particulièrement utile lorsque vous devez restaurer des données à un certain moment ou dans des configurations où vous devez faire correspondre des données sur plusieurs serveurs.

Pour sauvegarder la base de données MySQL via le journal binaire, procédez comme suit :

Étape 1: Assurez-vous que la journalisation binaire est activée sur votre serveur MySQL. Créez une sauvegarde complète de votre base de données en utilisant l'une des méthodes décrites précédemment (par exemple, mysqldump, phpMyAdmin ou MySQL Workbench).

Étape 2: Vider les journaux binaires afin de créer un nouveau fichier journal pour votre période de sauvegarde :

FLUSH LOGS;

Étape 3 : Notez le nom et la position de l'image du journal binaire actuel à l'aide de la commande suivante :

AFFICHER DES VARIABLES TELLES QUE 'log_bin%';

Étape 4: Copiez les fichiers journaux binaires (par exemple, mysql-bin.000001, mysql-bin.000002, etc.) depuis le répertoire de données MySQL vers un emplacement sûr.\NStockez les fichiers journaux binaires avec la sauvegarde complète de votre base de données pour une restauration ultérieure.

Comment restaurer une base de données MySQL

Maintenant que vous avez appris les différentes façons de sauvegarder votre base de données MySQL, voyons comment procéder à la restauration de la sauvegarde MySQL en cas de perte ou de corruption des données.

Méthode 1 : Restaurer toutes les bases de données de MySQL

Si vous devez restaurer toutes vos bases de données MySQL à partir d'un fichier de sauvegarde, vous pouvez utiliser la commande suivante pour restaurer une base de données MySQL.

mysql -u [utilisateur] -p[password] < [nom_du_fichier_de_sauvegarde].sql

Cette commande permet de restaurer toutes les bases de données contenues dans le fichier de sauvegarde SQL spécifié.

Méthode 2 : Restaurer la base de données MySQL à partir de fichiers à l'aide de phpMyAdmin

Restaurer une base de données MySQL avec phpMyAdmin est aussi simple que de la sauvegarder. Voici comment récupérer une base de données MySQL :

Étape 1: Ouvrez votre navigateur web et accédez à l'interface phpMyAdmin. Connectez-vous avec vos identifiants MySQL. Cliquez sur l'onglet "Importation" en haut de l'écran.

Étape 2: Cliquez sur le bouton "Choisir un fichier" et sélectionnez le fichier de sauvegarde SQL que vous souhaitez restaurer.

Étape 3: Sélectionnez les options que vous préférez (par exemple, créer une nouvelle base de données, méthode d'importation, jeu de caractères, etc.)

Faites défiler vers le bas et cliquez sur le bouton " Aller " pour lancer le processus de restauration.

Méthode 3 : Récupérer la base de données MySQL à partir des fichiers avec Workbench

MySQL Workbench offre également un moyen simple de restaurer votre base de données MySQL à partir d'un fichier de sauvegarde.

Voici les étapes à suivre pour restaurer une base de données à l'aide de MySQL Workbench.

Étape 1: Ouvrez MySQL Workbench sur votre ordinateur et connectez-vous à votre serveur MySQL. Sur le côté gauche, vous verrez un panneau appelé "Navigateur". Regardez dans la section "Gestion" et cliquez sur "Importation/Restauration de données".

Étape 2: Une nouvelle fenêtre intitulée "Importation de données" s'ouvre. Dans cette fenêtre, vous devez indiquer à Workbench où se trouve votre fichier de sauvegarde. Si votre sauvegarde est un fichier unique, sélectionnez l'option "Importer à partir d'un fichier autonome". Si votre sauvegarde est un dossier, conservez l'option par défaut "Vider le dossier du projet". Cliquez sur le bouton "..." pour rechercher votre fichier ou dossier de sauvegarde.

Étape 3: Dans la même fenêtre "Importation de données", vous verrez des options comme ""Options d'importation"". Ici, vous pouvez choisir la base de données dans laquelle restaurer la sauvegarde, ou si vous voulez restaurer des tables spécifiques au lieu de la base de données entière. Configurez ces options en fonction de vos besoins.

Étape 4: Une fois que vous avez sélectionné votre fichier de sauvegarde et configuré les options d'importation, cliquez sur le bouton "Démarrer l'importation" pour commencer à restaurer votre base de données. Workbench vous indiquera alors la progression du processus de restauration. Toute erreur ou avertissement sera affiché dans une section du journal. Il suffit d'attendre qu'il soit terminé.

C'est tout ! Workbench restaurera votre base de données à partir du fichier ou du dossier de sauvegarde que vous avez sélectionné. Les étapes sont assez simples - connectez-vous au serveur, pointez Workbench vers votre sauvegarde, configurez les options et cliquez sur le bouton "Démarrer l'importation".

Méthode 4 : Récupérer la base de données MySQL via le journal binaire

Si vous disposez d'une sauvegarde complète de votre base de données MySQL et des fichiers journaux binaires correspondants, vous pouvez restaurer vos données jusqu'à un point précis dans le temps à l'aide de l'utilitaire mysqlbinlog.

Voici comment récupérer une base de données MySQL via le journal binaire :

Étape 1: Arrêtez le serveur MySQL pour éviter toute modification ultérieure de vos données. Restaurez la sauvegarde complète de votre base de données à l'aide de l'une des méthodes décrites précédemment.

Étape 2: Identifiez le fichier journal binaire et la position que vous souhaitez utiliser pour la récupération à un moment donné. Utilisez l'utilitaire mysqlbinlog pour appliquer les événements du journal binaire à votre base de données restaurée :

mysqlbinlog [fichier_log_binaire] --start-position=[start_position] --stop-position=[stop_position] | mysql -u [utilisateur] -p[mot_de_passe] [nom_de_la_base de données]

Étape 3: Remplacez [binary_log_file], [start_position], [stop_position], [user], [password], et [database_name] par les valeurs appropriées à votre environnement.

Cette commande appliquera les événements du journal binaire pour restaurer la base de données MySQL.

Bonus 1 : Comment restaurer une table spécifique dans une base de données MySQL

Supposons que vous disposiez d'un fichier SQL (un fichier de sauvegarde) contenant la table spécifique que vous souhaitez restaurer dans votre base de données MySQL. Suivez ces étapes pour récupérer la base de données MySQL à partir d'une table spécifique.

Étape 1: Pour effectuer une restauration de sauvegarde mysql pour une table spécifique, vous pouvez utiliser cette commande :

>mysql -u votre_nom_d'utilisateur -p votre_nom_de_base_de_données < path/to/backup.sql

Remplacer les éléments suivants :

• `votre_nom_d'utilisateur` avec votre nom d'utilisateur MySQL actuel
• `votre_nom_de_base_de_données` avec le nom de la base de données dans laquelle vous souhaitez restaurer la table
• `path/to/backup.sql` avec le chemin complet de votre fichier de sauvegarde SQL

Étape 2: Maintenant, si vous souhaitez restaurer une seule table à partir de ce fichier de sauvegarde, au lieu de restaurer la base de données entière, vous pouvez utiliser cette commande :

mysql -u votre_nom_d'utilisateur -p votre_nom_de_base_de_données --tables table_à_restaurer < path/to/backup.sql

Il suffit de les remplacer :

• `votre_nom_d'utilisateur` et `votre_nom_de_base_de_données` comme auparavant
• `table_to_restore` avec le nom de la table spécifique que vous voulez restaurer
• `path/to/backup.sql` avec le chemin d'accès à votre fichier de sauvegarde SQL

En résumé, l'option `--tables` vous permet de spécifier quelle(s) table(s) vous souhaitez restaurer à partir du fichier de sauvegarde, au lieu de restaurer la base de données entière.

Bonus 2 : Comment restaurer les fichiers de base de données sur PC

Dans certains cas, vous pouvez avoir besoin de récupérer vos fichiers de base de données MySQL directement à partir du disque dur de votre ordinateur, en particulier si vous ne disposez pas d'un fichier de sauvegarde. Cela peut se produire à la suite d'une suppression accidentelle, d'un formatage, d'une attaque de virus ou d'autres scénarios de perte de données.

Wondershare Recoverit est un puissant outil de récupération de données qui peut vous aider à récupérer les fichiers de base de données MySQL perdus ou supprimés de votre PC ou de périphériques de stockage externes.

Voici quelques raisons de considérer Wondershare Recoverit pour la récupération des bases de données MySQL :

• Interface conviviale : Recoverit est doté d'une interface intuitive et facile à utiliser qui guide les utilisateurs tout au long du processus de récupération, étape par étape, ce qui le rend accessible même aux personnes ayant une expertise technique limitée.
• Taux de réussite élevé : Avec un taux de réussite de 98 %, Recoverit offre une solution robuste pour récupérer les fichiers perdus ou supprimés, y compris les fichiers de base de données MySQL.
• Plus de 20 ans d'expérience : Wondershare travaille dans le domaine de la récupération de données depuis plus de deux décennies, perfectionnant sans cesse ses logiciels pour gérer les différents scénarios de perte de données.
• Prise en charge d'un grand nombre de types de fichiers : Recoverit peut récupérer plus de 1000+ types de fichiers, y compris toutes les extensions de fichiers de bases de données MySQL courantes telles que .frm, .ibd, .MYD, et .MYI.
• Scénarios de récupération multiples : Que vos fichiers aient été perdus à la suite d'une suppression accidentelle, d'un formatage du disque dur, d'une panne du système ou même d'une attaque de virus, Recoverit est conçu pour gérer diverses situations de perte de données.

Voici comment utiliser Wondershare Recoverit pour restaurer vos fichiers de base de données MySQL :

Étape 1: Installez Wondershare Recoverit sur votre ordinateur. Lancez le logiciel et sélectionnez l'emplacement où votre répertoire de données MySQL a été stocké (par exemple, le lecteur ou la partition).

Étape 2: Cliquez sur le bouton "Démarrer" pour lancer le processus d'analyse. Recoverit propose deux types d'analyse différents : Analyse rapide et Analyse approfondie. Une fois l'analyse terminée, Recoverit affiche une liste des fichiers récupérables.

Étape 3: Localisez vos fichiers de base de données MySQL (par exemple, .frm, .ibd ou d'autres extensions de fichiers) et sélectionnez-les. Vous pouvez même filtrer les résultats de la recherche en fonction des critères de taille et de type de fichier.

Étape 4: Prévisualiser les fichiers. Cliquez sur le bouton "Récupérer" pour enregistrer les fichiers récupérés dans un emplacement sûr de votre ordinateur.

Après avoir récupéré vos fichiers de base de données MySQL, vous pouvez tenter de les restaurer en utilisant les méthodes appropriées (par exemple, en les recopiant dans le répertoire de données MySQL ou en les important dans une nouvelle base de données).

Conclusion

Nous avons terminé notre guide sur la sauvegarde et la restauration de la base de données MySQL. La sauvegarde et la restauration de votre base de données MySQL est un aspect crucial de la gestion des données et de la reprise après sinistre. Que vous soyez un administrateur de base de données chevronné ou un développeur travaillant sur un petit projet, la mise en place d'une stratégie de sauvegarde fiable peut vous éviter d'innombrables maux de tête et des pertes de données potentielles.

Tout au long de ce guide, nous avons exploré différentes méthodes pour sauvegarder et restaurer votre base de données MySQL, depuis les outils en ligne de commande jusqu'aux interfaces graphiques. Nous avons abordé mysqldump, phpMyAdmin, MySQL Workbench, les instantanés du système de fichiers et les journaux binaires pour la récupération de MySQL. Et même des outils de récupération de données comme Wondershare Recoverit.

Grâce aux connaissances et aux techniques que vous avez acquises dans ce guide, vous serez mieux équipé pour faire face à tout scénario de perte de données et garder votre base de données MySQL saine et sauve.