Les deux principales méthodes de sauvegarde de la base de données consistent à utiliser le programme mysqldump ou à copier directement le fichier de la base de données (par exemple en utilisant cp, cpio ou tar, etc.). Cet article détaille le plan de sauvegarde de la base de données de la plateforme MySQL. Dans le cas où une table de base de données est perdue ou corrompue, il est important de sauvegarder votre base de données. Si une panne du système se produit, vous souhaitez absolument pouvoir restaurer vos tables dans l'état dans lequel elles se trouvaient au moment de la panne avec le moins de perte de données possible. Parfois, c'est l'administrateur MySQL qui fait des ravages. L'administrateur sait déjà que les tables sont corrompues et essayer de les éditer directement à l'aide d'un éditeur tel que vi ou Emacs n'est certainement pas une bonne chose pour les tables.
Les deux principales méthodes de sauvegarde de la base de données consistent à utiliser le programme mysqldump ou à copier directement le fichier de la base de données (par exemple en utilisant cp, cpio ou tar, etc.). Chaque méthode a ses avantages et ses inconvénients :
mysqldump fonctionne avec le serveur MySQL. La méthode de copie directe est effectuée en dehors du serveur et vous devez prendre des mesures pour vous assurer qu'aucun client ne modifie la table que vous copiez. Si vous souhaitez utiliser la sauvegarde du système de fichiers pour sauvegarder la base de données, le même problème se produira : si la table de la base de données est modifiée pendant le processus de sauvegarde du système de fichiers, le sujet du fichier de table sauvegardé sera incohérent et la table sera sans aucun sens pour la reprise future. La différence entre une sauvegarde du système de fichiers et une copie directe du fichier est qu'avec cette dernière, vous avez un contrôle total sur le processus de sauvegarde, vous pouvez donc prendre des mesures pour vous assurer que le serveur quitte la table sans être dérangé.
mysqldump est plus lent que la copie directe.
mysqldump génère des fichiers texte portables sur d'autres machines, même celles ayant des architectures matérielles différentes. La copie directe des fichiers ne peut pas être portée sur d'autres machines à moins que la table que vous copiez n'utilise le format de stockage MyISAM. Les tables ISAM ne peuvent être copiées que sur des machines dotées de structures matérielles similaires. Le format de stockage de table MyISAM introduit dans MySQL 3.23 résout ce problème car le format est indépendant de la machine, donc la copie directe du fichier peut être transplantée sur des machines avec des structures matérielles différentes. Tant que deux conditions sont remplies : l'autre machine doit également exécuter MySQL 3.23 ou version ultérieure, et le fichier doit être représenté au format MyISAM, et non au format ISAM.
Quelle que soit la méthode de sauvegarde que vous utilisez, si vous devez restaurer votre base de données, plusieurs principes doivent être suivis pour garantir les meilleurs résultats :
Mettez en œuvre des sauvegardes régulières. Établissez un plan et respectez-le.
Laissez le serveur effectuer la journalisation des mises à jour. Le journal des modifications vous aidera lorsque vous aurez besoin de récupérer des données après un crash. Après avoir utilisé le fichier de sauvegarde pour restaurer les données dans l'état dans lequel elles se trouvaient au moment de la sauvegarde, vous pouvez réappliquer les modifications apportées après la sauvegarde en exécutant une requête dans le journal de mise à jour, qui restaurera les tables de la base de données à leur état initial. l'état dans lequel ils se trouvaient au moment de l'accident.
En termes de sauvegarde du système de fichiers, le fichier de sauvegarde de la base de données représente un vidage complet, tandis que le journal de mise à jour représente un vidage incrémentiel.
Utilisez un schéma de dénomination cohérent et compréhensible pour les fichiers de sauvegarde. Des choses comme backup1, buckup2, etc. ne sont pas particulièrement significatives. Lors de votre récupération, vous perdrez du temps à comprendre le contenu des fichiers. Vous trouverez peut-être utile d'utiliser le nom et la date de la base de données pour former le nom du fichier de sauvegarde. Par exemple:
%mysqldump samp_db >/usr/archives/mysql/samp_db.1999-10-02
%mysqldump ménagerie >/usr/archives/mysql/menagerie.1999-10-02
Vous souhaiterez peut-être compresser les sauvegardes après les avoir générées. Les sauvegardes ont tendance à être volumineuses ! Vous devez également faire expirer vos fichiers de sauvegarde pour éviter qu'ils ne remplissent votre disque, tout comme vous faites expirer vos fichiers journaux.
Sauvegardez vos fichiers de sauvegarde avec une sauvegarde du système de fichiers. Si vous rencontrez un crash complet qui efface non seulement votre répertoire de données, mais également le lecteur de disque contenant les sauvegardes de votre base de données, vous allez avoir de réels problèmes.
Sauvegardez également votre journal des modifications.
Placez vos fichiers de sauvegarde sur un système de fichiers différent de celui utilisé pour votre base de données. Cela réduira la possibilité de remplir le système de fichiers contenant le répertoire de données suite à la génération de la sauvegarde.
Les techniques utilisées pour créer des sauvegardes sont également utiles pour copier une base de données sur une autre machine. Le plus souvent, une base de données est déplacée vers un serveur exécuté sur un autre hôte, mais vous pouvez également déplacer les données vers un autre serveur sur le même hôte.
1 Utilisez mysqldump pour sauvegarder et copier la base de données
Lorsque vous utilisez le programme mysqldumo pour générer un fichier de sauvegarde de base de données, par défaut, le contenu du fichier contient l'instruction CREATE qui crée la table en cours de sauvegarde et l'instruction INSERT qui contient les données de ligne de la table. En d'autres termes, la sortie produite par mysqldump peut ensuite être utilisée comme entrée dans mysql pour reconstruire la base de données.
Vous pouvez vider l'intégralité de la base de données dans un seul fichier texte comme suit :
%mysqldump samp_db >/usr/archives/mysql/samp_db.1999-10-02
Le début du fichier de sortie ressemble à ceci :
# MySQL Dump 6.0# # Hôte : localhost Base de données : samp_db
#--------------------------------------------#
Version du serveur 3.23.2-alpha-log## Structure de table en cas d'absence de table
#CREATE TABLE absence( student_id int(10) unsigned DEFAULT 0 NOT NULL, date date DEFAULT 0000-00-00 NOT NUL L,
PRIMARY KEY (student_id,date));## Sauvegarde des données pour la table d'absence #INSERT INTO absence VALUES (3,1999-09-03);INSERT INTO absence VALUE S (5,1999-09-03);INSERT INTO absence VALUES (10,1999-09-08);......
Le reste du fichier se compose de davantage d'instructions INSERT et CREATE TABLE. Si vous souhaitez compresser la sauvegarde, utilisez une commande similaire à la suivante :
%mysqldump samp_db | gzip >/usr/archives/mysql/samp_db.1999-10-02.gz
Si vous disposez d’une base de données volumineuse, les fichiers de sortie seront également volumineux et pourront être difficiles à gérer. Si vous le souhaitez, vous pouvez lister les noms de tables individuelles après le nom de la base de données sur la ligne de commande mysqldump pour vider leur contenu, ce qui divisera le fichier de vidage en fichiers plus petits et plus faciles à gérer. L'exemple suivant montre comment vider certaines tables de la base de données samp_db dans des fichiers séparés :
%mysqldump samp_db absence d'événement de score d'étudiant > graphbook.sql
%mysqldump président membre de samp_db > hist-league.sql
Si vous générez des fichiers de sauvegarde destinés à être utilisés pour actualiser périodiquement le contenu d'une autre base de données, vous souhaiterez peut-être utiliser l'option --add-drop-table. Cela indique au serveur d'écrire l'instruction DROP TABLE IF EXISTS dans le fichier de sauvegarde, puis, lorsque vous prenez le fichier de sauvegarde et le chargez dans la deuxième base de données, vous n'obtiendrez pas d'erreur si la table existe déjà.
Si vous sauvegardez une base de données afin de pouvoir la déplacer vers un autre serveur, vous n'avez même pas besoin de créer un fichier de sauvegarde. Assurez-vous que la base de données existe sur un autre hôte, puis utilisez un canal pour vider la base de données afin que MySQL puisse lire directement la sortie de mysqldump. Par exemple : vous souhaitez copier la base de données samp_db de l'hôte pit-viper.snake.net vers boa.snake.net. Vous pouvez le faire facilement comme ceci :
%mysqladmin -h boa.snake.net créer samp_db
%mysqldump samp_db | mysql -h boa.snake.net samp_db
À l'avenir, si vous souhaitez actualiser à nouveau la base de données sur boa.snake.net, ignorez la commande mysqladmin, mais ajoutez --add-drop-table à mysqldump pour éviter que la table existe déjà : %mysqldump --add- table de dépôt samp_db | mysql -h boa.snake.net samp_db
D'autres options mysqldump utiles incluent : la combinaison --flush-logs et --lock-tables sera utile pour vérifier votre base de données. --lock-tables verrouille toutes les tables que vous sauvegardez et --flush-logs ferme et rouvre le fichier journal de mise à jour. Le nouveau journal de mise à jour inclura uniquement les requêtes qui ont modifié la base de données à partir du point de sauvegarde. Cela définira l’heure de sauvegarde du point de contrôle de votre journal de mise à jour. (Cependant, si vous avez des clients qui doivent effectuer une mise à jour, verrouiller toutes les tables n'est pas une bonne idée pour l'accès des clients pendant la sauvegarde.)
Si vous utilisez --flush-logs pour effectuer un point de contrôle dans une sauvegarde, il est probablement préférable de vider l'intégralité de la base de données.
Si vous sauvegardez des fichiers séparés, il est plus difficile de synchroniser les points de contrôle du journal de mise à jour avec les fichiers de sauvegarde. Lors de la récupération, vous extrayez généralement le contenu du journal de mise à jour base de données par base de données. Il n'existe aucune option permettant d'extraire les mises à jour pour des tables individuelles, vous devez donc les extraire vous-même.
Par défaut, mysqldump lit tout le contenu d'une table en mémoire avant de l'écrire. Ceci est généralement vraiment inutile et constitue en fait presque un échec si vous avez une grande table. Vous pouvez utiliser l'option --quick pour dire à mysqldump d'écrire chaque ligne chaque fois qu'il en récupère une. Pour optimiser davantage le processus de coulée, utilisez --opt au lieu de --quick. L'option --opt active des options supplémentaires pour accélérer le transfert des données et leur relecture.
L'implémentation de sauvegardes avec --opt est probablement la méthode la plus courante en raison de l'avantage de rapidité des sauvegardes. Cependant, soyez averti, l'option --opt a un coût. --opt optimise votre processus de sauvegarde, pas l'accès des autres clients à la base de données. L'option --opt empêche quiconque de mettre à jour les tables que vous sauvegardez en verrouillant toutes les tables à la fois. Vous pouvez facilement voir l’effet sur l’accès général à la base de données. Lorsque votre base de données est généralement utilisée très fréquemment, ajustez simplement la sauvegarde une fois par jour.
Une option qui a l'effet inverse de --opt est --dedayed. Cette option amène mysqldump à écrire des instructions INSERT DELAYED au lieu d'instructions INSERT. --delayed est utile si vous chargez un fichier de données dans une autre base de données et que vous souhaitez que cette opération ait un impact minimal sur les requêtes pouvant apparaître dans cette base de données.
L'option --compress est utile lorsque vous copiez la base de données sur une autre machine car elle réduit le nombre d'octets transférés sur le réseau. Voici un exemple. Notez que --compress est donné pour les programmes qui communiquent avec un serveur sur un hôte distant, pas pour les programmes qui se connectent à l'hôte local :
%mysqldump --opt samp_db | mysql --compress -h boa.snake.net samp_db
mysqldump propose de nombreuses options, voir "Manuel de référence MySQL" pour plus de détails.
2 Méthode de sauvegarde et de copie utilisant la base de données de copie directe
Une autre façon de sauvegarder la base de données et les tables qui n'implique pas mysqldump consiste à copier directement les fichiers des tables de la base de données. Cela se fait généralement à l'aide d'utilitaires tels que cp, tar ou cpio. Les exemples de cet article utilisent cp.
Lorsque vous utilisez une méthode de sauvegarde directe, vous devez vous assurer que la table n'est plus utilisée. Si le serveur modifie une table pendant que vous la copiez, la copie n'a aucun sens.
La meilleure façon de garantir l'intégrité de votre copie est d'arrêter le serveur, de copier les fichiers, puis de redémarrer le serveur. Si vous ne souhaitez pas arrêter le serveur, verrouillez-le pendant la vérification de la table. Si le serveur est en cours d'exécution, les mêmes restrictions s'appliquent à la copie de fichiers et vous devez utiliser le même protocole de verrouillage pour « faire taire » le serveur.
En supposant que le serveur est en panne ou que vous avez verrouillé la table que vous souhaitez copier, ce qui suit montre comment sauvegarder l'intégralité de la base de données samp_db dans un répertoire de sauvegarde (DATADIR représente le répertoire de données du serveur) : %cd DATADIR%cp -r samp_db /usr /archive/mysql
Une seule table peut être sauvegardée comme suit :
%cd DATADIR/samp_db%cp member.* /usr/archive/mysql/samp_db%cp score.* /usr/archive/mysql/samp_db ....
Une fois la sauvegarde terminée, vous pouvez redémarrer le serveur (si vous l'avez arrêté) ou libérer les verrous placés sur la table (si vous avez laissé le serveur en marche).
Pour copier une base de données d'une machine à une autre à l'aide de fichiers de copie directe, copiez simplement les fichiers dans le répertoire de données approprié sur l'autre hôte du serveur. Assurez-vous que le fichier est au format MyIASM ou que les deux machines ont la même structure matérielle, sinon votre base de données aura un contenu étrange sur l'autre machine. Vous devez également vous assurer que le serveur d'une autre machine n'accède pas aux tables de la base de données pendant que vous les installez.
3 Réplication de la base de données
La réplication est similaire à la copie d'une base de données sur un autre serveur, mais sa signification exacte est de garantir que les deux bases de données sont entièrement synchronisées en temps réel. Cette fonctionnalité apparaîtra dans la version 3.23 et n’est pas encore très mature, cet article ne la présentera donc pas en détail.
4 Restaurer les données à partir d'une sauvegarde
La corruption des bases de données peut survenir pour de nombreuses raisons et à des degrés divers. Si vous avez de la chance, vous risquez de ne corrompre qu'une ou deux tables (comme une panne de courant), si vous n'avez pas de chance, vous devrez peut-être remplacer l'intégralité du répertoire de données (comme une corruption de disque). La récupération est également requise dans certaines situations, par exemple lorsqu'un utilisateur supprime une base de données ou une table par erreur. Quelle que soit la cause de ces événements malheureux, vous devrez mettre en œuvre une sorte de rétablissement.
Si les tables sont endommagées mais pas perdues, essayez de les réparer avec myisamchk ou isamchk. Si de tels dommages peuvent être réparés par un programme de réparation, vous n'aurez peut-être pas du tout besoin d'utiliser le fichier de sauvegarde. Pour le processus de réparation des tables, voir « Maintenance et réparation de la base de données ».
Le processus de récupération implique deux sources d'informations : vos fichiers de sauvegarde et vos journaux de modifications. Le fichier de sauvegarde restaure la table dans l'état dans lequel elle se trouvait au moment de la sauvegarde. Cependant, la table a généralement été modifiée entre la sauvegarde et le problème, et le journal de mise à jour contient les requêtes utilisées pour effectuer ces modifications. Vous pouvez utiliser des fichiers journaux comme entrée dans MySQL pour répéter les requêtes. C'est pourquoi vous devez activer le journal des modifications.
Le processus de récupération varie en fonction de la quantité d'informations que vous devez récupérer. En fait, il est plus facile de restaurer l'intégralité de la base de données qu'une seule table, car il est plus facile d'appliquer le journal de mise à jour à la base de données qu'à une seule table.
4.1 Restaurer l'intégralité de la base de données
Tout d'abord, si la base de données que vous souhaitez restaurer est une base de données MySQL contenant une table d'attribution, vous devez exécuter le serveur avec l'option --skip-grant-table. Sinon, il se plaindra que la table d'autorisation est introuvable. Après avoir restauré la table, exécutez mysqladmin flush-privileges pour indiquer au serveur de charger les jetons d'autorisation et de les utiliser.
Copiez le contenu du répertoire de la base de données vers un autre emplacement si vous en avez besoin ultérieurement.
Réinstallez la base de données avec le dernier fichier de sauvegarde. Si vous utilisez le fichier généré par mysqldump, utilisez-le comme entrée dans mysql. Si vous utilisez des fichiers copiés directement depuis la base de données, copiez-les directement dans le répertoire de la base de données. Cependant, dans ce cas, vous devrez fermer la base de données puis la redémarrer avant de copier les fichiers.
Utilisez le journal de mise à jour pour répéter les requêtes qui modifient les tables de base de données après la sauvegarde. Pour tous les journaux de modifications applicables, transmettez-les en entrée à MySQL. La spécification de l'option --one-database amène MySQL à exécuter des requêtes uniquement pour la base de données que vous souhaitez restaurer. Si vous savez que vous devez appliquer tous les fichiers journaux de mise à jour, vous pouvez utiliser cette commande dans le répertoire contenant les journaux :
% ls -t -r -1 update.[0-9]* | xargs cat | mysql --one-database nom_base de données
La commande ls génère une liste sur une seule colonne de fichiers journaux de mise à jour, triés selon l'ordre dans lequel le serveur les a générés (Idée : si vous modifiez l'un des fichiers, vous modifierez l'ordre de tri, ce qui entraînera l'affichage du journal de mise à jour). utilisé dans le mauvais ordre.)
Très probablement, vous utiliserez certains journaux de modifications. Par exemple, si les journaux de mise à jour générés depuis votre sauvegarde sont nommés update.392, update.393, etc., vous pouvez réexécuter comme ceci :
%mysql --one-database nom_base < update.392
%mysql --one-database nom_base < update.393
.....
Si vous effectuez une récupération et utilisez le journal de mise à jour pour récupérer les informations perdues en raison d'une instruction DROP DATABASE, DROP TABLE ou DELETE incorrectement recommandée, veillez à supprimer ces instructions du journal de mise à jour avant de l'utiliser.
4.2 Restaurer une seule table
La restauration d'une seule table est plus complexe. Si vous utilisez un fichier de sauvegarde généré par mysqldump et qu'il ne contient pas de données pour les tables qui vous intéressent, vous devrez les extraire des lignes pertinentes et les utiliser comme entrée dans mysql. C'est la partie la plus facile. La partie la plus difficile consiste à extraire le fragment du journal de mise à jour qui s'applique uniquement à cette table. L'utilitaire mysql_find_rows peut vous être utile pour cela, qui extrait les requêtes multi-lignes du journal des modifications.
Une autre possibilité consiste à utiliser un autre serveur pour restaurer l'intégralité de la base de données, puis à copier les fichiers de table souhaités dans la base de données d'origine. Cela peut être très simple ! Lorsque vous copiez les fichiers dans le répertoire de la base de données, assurez-vous que le serveur de base de données d'origine est arrêté.