-
Sauvegarde et récupération MySQL samedi 30/09/2006 - 14:21 — an
Sauvegarde et récupération MySQL
Auteur/Traducteur : Ye Jinrong (E-mail : ), Source : http://imysql.cn . Veuillez indiquer l'auteur/traducteur et la source lors de la réimpression. Les contrevenants ne seront pas poursuivis.
Date : 01/10/2006
Cet article explique le mécanisme de sauvegarde et de récupération de MySQL et comment gérer les tables de données, y compris les deux principaux types de tables : MyISAM et Innodb. La version de MySQL conçue dans cet article est la 5.0.22.
Les outils de sauvegarde gratuits actuellement pris en charge par MySQL incluent : mysqldump, mysqlhotcopy. Vous pouvez également utiliser la syntaxe SQL pour la sauvegarde : BACKUP TABLE ou SELECT INTO OUTFILE, ou sauvegarder les journaux binaires (binlog), ou copier directement les fichiers de données et les fichiers de configuration associés. Les tables MyISAM sont enregistrées sous forme de fichiers, elles sont donc relativement faciles à sauvegarder. Plusieurs des méthodes mentionnées ci-dessus peuvent être utilisées. Toutes les tables d'Innodb sont stockées dans le même fichier de données ibdata1 (il peut également s'agir de plusieurs fichiers ou de fichiers d'espace table indépendants), ce qui est relativement difficile à sauvegarder. Les solutions gratuites peuvent consister à copier des fichiers de données et à sauvegarder le journal binaire ou à utiliser mysqldump. .
1.mysqldump
1.1 Sauvegarde
mysqldump utilise un mécanisme de sauvegarde au niveau SQL. Il exporte les tables de données dans des fichiers de script SQL. Il est relativement adapté à la mise à niveau entre différentes versions de MySQL. C'est également la méthode de sauvegarde la plus couramment utilisée.
Parlons maintenant de certains des principaux paramètres de mysqldump :
--compatible=nom
Il indique à mysqldump avec quelle base de données ou ancienne version du serveur MySQL les données exportées seront compatibles. Les valeurs peuvent être ansi, mysql323, mysql40, postgresql, oracle, mssql, db2, maxdb, no_key_options, no_tables_options, no_field_options, etc. Pour utiliser plusieurs valeurs, séparez-les par des virgules. Bien entendu, il ne garantit pas une compatibilité totale, mais il essaie d’être compatible.
--complete-insert, -c
Les données exportées utilisent la méthode INSERT complète incluant les noms de champs, c'est-à-dire que toutes les valeurs sont écrites sur une seule ligne. Cela peut améliorer l'efficacité de l'insertion, mais cela peut être affecté par le paramètre max_allowed_packet et provoquer un échec d'insertion. Par conséquent, ce paramètre doit être utilisé avec prudence, du moins je ne le recommande pas.
--default-character-set=jeu de caractères
Spécifiez le jeu de caractères à utiliser lors de l'exportation des données. Si la table de données n'utilise pas le jeu de caractères latin1 par défaut, cette option doit être spécifiée lors de l'exportation, sinon des caractères tronqués apparaîtront après la nouvelle importation des données.
--disable-clés
Dites à mysqldump d'ajouter les instructions /*!40000 ALTER TABLE DISABLE KEYS */; et /*!40000 ALTER TABLE table ENABLE KEYS */; au début et à la fin de l'instruction INSERT. car c'est le cas. L'index est reconstruit une fois que toutes les données ont été insérées. Cette option ne convient qu'aux tables MyISAM.
--extended-insert = vrai|faux
Par défaut, mysqldump active le mode --complete-insert, donc si vous ne souhaitez pas l'utiliser, utilisez simplement cette option et définissez sa valeur sur false.
--hex-blob
Exportez les champs de chaîne binaire au format hexadécimal. Cette option doit être utilisée s'il y a des données binaires. Les types de champs concernés sont BINARY, VARBINARY et BLOB.
--lock-all-tables,-x
Avant de démarrer l'exportation, soumettez une demande pour verrouiller toutes les tables de toutes les bases de données afin de garantir la cohérence des données. Il s'agit d'un verrou de lecture global qui est automatiquement désactivé avec les options --single-transaction et --lock-tables.
--lock-tables
Il est similaire à --lock-all-tables, mais il verrouille la table de données actuellement exportée au lieu de verrouiller toutes les tables de la base de données en même temps. Cette option s'applique uniquement aux tables MyISAM. S'il s'agit d'une table Innodb, vous pouvez utiliser l'option --single-transaction.
--no-create-info, -t
Exportez uniquement les données sans ajouter d'instruction CREATE TABLE.
--pas de données,-d
Aucune donnée n'est exportée, seule la structure des tables de la base de données est exportée.
--opter
Ceci est juste une option rapide, équivalente à l'ajout également de --add-drop-tables --add-locking --create-option --disable-keys --extended-insert --lock-tables --quick --set- options de jeu de caractères. Cette option permet à mysqldump d'exporter des données rapidement, et les données exportées peuvent être réimportées rapidement. Cette option est activée par défaut, mais peut être désactivée avec --skip-opt. Notez que si vous exécutez mysqldump sans spécifier l'option --quick ou --opt, l'intégralité du jeu de résultats sera placée en mémoire. Des problèmes peuvent survenir si vous exportez une base de données volumineuse.
--rapide,-q
Cette option est utile lors de l'exportation de grandes tables. Elle force mysqldump à afficher directement les enregistrements obtenus à partir de la requête du serveur au lieu de récupérer tous les enregistrements et de les mettre en cache en mémoire.
--routines, -R
Exportez des procédures stockées et des fonctions personnalisées.
--transaction unique
Cette option soumet une instruction SQL BEGIN avant d'exporter les données. BEGIN ne bloque aucune application et garantit un état cohérent de la base de données lors de l'exportation. Cela ne fonctionne qu'avec des tables transactionnelles telles que InnoDB et BDB.
Cette option et l'option --lock-tables s'excluent mutuellement car LOCK TABLES entraîne la validation implicite de toutes les transactions en attente.
Pour exporter de grandes tables, l'option --quick doit être utilisée en combinaison.
--déclencheurs
Exportez également les déclencheurs. Cette option est activée par défaut, utilisez --skip-triggers pour la désactiver.
Veuillez vous référer au manuel pour plus de détails sur les autres paramètres. J'utilise généralement le SQL suivant pour sauvegarder les tables MyISAM :
/usr/local/mysql/bin/mysqldump -uyejr -pyejr
--default-character-set=utf8 --opt --extended-insert=false
--triggers -R --hex-blob -x nom_base de données > nom_base.sql
Utilisez le code SQL suivant pour sauvegarder les tables Innodb :
/usr/local/mysql/bin/mysqldump -uyejr -pyejr
--default-character-set=utf8 --opt --extended-insert=false
--triggers -R --hex-blob --single-transaction nom_base de données > nom_base.sql
De plus, si vous souhaitez implémenter une sauvegarde en ligne, vous pouvez également utiliser le paramètre --master-data, comme suit :
/usr/local/mysql/bin/mysqldump -uyejr -pyejr
--default-character-set=utf8 --opt --master-data=1
--single-transaction --flush-logs nom_base de données > nom_base.sql
Il demande uniquement la table de verrouillage au début, puis actualise le journal binaire, puis ajoute l'instruction CHANGE MASTER au fichier exporté pour spécifier l'emplacement du journal binaire de la sauvegarde actuelle. Si vous souhaitez restaurer ce fichier sur l'esclave, vous pouvez utiliser. cette façon de le faire.
1.2 Restauration Le fichier sauvegardé avec mysqldump est un script SQL qui peut être directement importé. Il existe deux manières d'importer les données.
Utilisez directement le client MySQL, par exemple :
/usr/local/mysql/bin/mysql -uyejr -pyejr nom_base < nom_base.sql
L'utilisation de la syntaxe SOURCE n'est en fait pas une syntaxe SQL standard, mais une fonction fournie par le client mysql, par exemple :
SOURCE /tmp/nom_base.sql;
Ici, vous devez spécifier le chemin absolu du fichier, et il doit s'agir d'un fichier que l'utilisateur en cours d'exécution de mysqld (par exemple, personne) n'a l'autorisation de lire.
2. mysqlhotcopie
2.1 Sauvegarde
mysqlhotcopy est un programme PERL écrit à l'origine par Tim Bunce. Il utilise LOCK TABLES, FLUSH TABLES et cp ou scp pour sauvegarder rapidement la base de données. Il s'agit du moyen le plus rapide de sauvegarder une base de données ou une seule table, mais il ne peut s'exécuter que sur la machine sur laquelle se trouvent les fichiers de base de données (y compris les fichiers de définition de table de données, les fichiers de données et les fichiers d'index). mysqlhotcopy ne peut être utilisé que pour sauvegarder MyISAM et ne fonctionnera que sur les systèmes de type Unix et NetWare.
mysqlhotcopy prend en charge la copie de plusieurs bases de données en même temps et prend également en charge les expressions régulières. Voici quelques exemples :
root#/usr/local/mysql/bin/mysqlhotcopy -h=localhost -u=yejr -p=yejr
db_name /tmp (changer le répertoire de la base de données db_name
Copier dans /tmp
Vers le bas)
root#/usr/local/mysql/bin/mysqlhotcopy -h=localhost -u=yejr -p=yejr
nom_base_1 ... nom_base_n /tmp
root#/usr/local/mysql/bin/mysqlhotcopy -h=localhost -u=yejr -p=yejr
nom_bd./regex/ /tmp
Veuillez vous référer au manuel pour une utilisation plus détaillée, ou appelez la commande suivante pour afficher l'aide de mysqlhotcopy :
perldoc /usr/local/mysql/bin/mysqlhotcopy
Notez que si vous souhaitez utiliser mysqlhotcopy, vous devez disposer des autorisations SELECT et RELOAD (pour exécuter FLUSH TABLES), et vous devez également avoir l'autorisation de lire le répertoire datadir/db_name.
2.2 Restaurer
Mysqlhotcopy sauvegarde l'intégralité du répertoire de la base de données lorsqu'il est utilisé, il peut être copié directement dans le répertoire de données spécifié par mysqld (ici /usr/local/mysql/data/). En même temps, il convient de prêter attention aux problèmes d'autorisation, comme dans l'exemple suivant :
root#cp -rf nom_base de données /usr/local/mysql/data/
root#chown -Rbody:nobody /usr/local/mysql/data/ (changez le propriétaire du répertoire db_name en mysqld
utilisateur en cours d'exécution)
3. Sauvegarde de la syntaxe SQL
3.1 Sauvegarde
La syntaxe BACKUP TABLE est en fait similaire au principe de fonctionnement de mysqlhotcopy. Ils verrouillent tous les deux la table puis copient le fichier de données. Il peut réaliser une sauvegarde en ligne, mais l'effet n'est pas idéal, ce n'est donc pas recommandé. Il copie uniquement les fichiers de structure de table et les fichiers de données, mais ne copie pas les fichiers d'index en même temps, la récupération est donc plus lente.
exemple:
RETOUR TABLE nom_table TO '/tmp/nom_bd/';
Notez que vous devez disposer de l'autorisation FILE pour exécuter ce SQL et que le répertoire /tmp/db_name/ doit être accessible en écriture par l'utilisateur mysqld. Le fichier exporté ne peut pas écraser le fichier existant pour éviter les problèmes de sécurité.
SELECT INTO OUTFILE exporte les données dans un fichier texte ordinaire. Vous pouvez personnaliser l'intervalle des champs pour faciliter le traitement de ces données.
exemple:
SELECT * INTO OUTFILE '/tmp/db_name/tbl_name.txt' FROM tbl_name ;
Notez que vous devez disposer de l'autorisation FILE pour exécuter ce SQL et que le fichier /tmp/db_name/tbl_name.txt doit être accessible en écriture par l'utilisateur mysqld. Le fichier exporté ne peut pas écraser le fichier existant pour éviter les problèmes de sécurité.
3.2 Pour restaurer des fichiers sauvegardés à l'aide de la méthode BACKUP TABLE, vous pouvez exécuter l'instruction RESTORE TABLE pour restaurer la table de données.
exemple:
RESTAURER LA TABLE À PARTIR DE '/tmp/db_name/';
Les exigences d'autorisation sont similaires à celles décrites ci-dessus.
Pour les fichiers sauvegardés à l'aide de la méthode SELECT INTO OUTFILE, vous pouvez exécuter l'instruction LOAD DATA INFILE pour restaurer la table de données.
exemple:
CHARGER LE FICHIER DE DONNÉES '/tmp/db_name/tbl_name.txt' DANS LA TABLE tbl_name ;
Les exigences d'autorisation sont similaires à celles décrites ci-dessus. Avant d'importer des données, la table de données doit déjà exister. Si vous craignez la duplication de données, vous pouvez ajouter le mot-clé REPLACE pour remplacer les enregistrements existants ou utiliser le mot-clé IGNORE pour les ignorer.
4. Activer le journal binaire (binlog)
La méthode d'utilisation de binlog est relativement plus flexible, évite les soucis et les efforts et peut également prendre en charge la sauvegarde incrémentielle.
Mysqld doit être redémarré lorsque binlog est activé. Tout d’abord, fermez mysqld, ouvrez my.cnf et ajoutez les lignes suivantes :
identifiant du serveur = 1
log-bin = binlog
log-bin-index = binlog.index
Ensuite démarrez mysqld. Binlog.000001 et binlog.index seront générés pendant l'opération. Le premier fichier est mysqld enregistrant toutes les opérations de mise à jour sur les données, et le dernier fichier est l'index de tous les binlogs, qui ne peuvent pas être facilement supprimés. Veuillez consulter le manuel pour plus d'informations sur binlog.
Lorsque vous avez besoin d'effectuer une sauvegarde, vous pouvez d'abord exécuter l'instruction SQL pour permettre à mysqld de terminer l'écriture dans le journal binaire actuel, puis sauvegarder directement le fichier. De cette manière, l'objectif de la sauvegarde incrémentielle peut être atteint :
FLUSH LOGS ; Si vous sauvegardez le serveur esclave dans le système de réplication, vous devez également sauvegarder les fichiers master.info et relay-log.info.
Le fichier binlog sauvegardé peut être visualisé à l'aide de l'outil mysqlbinlog fourni par MySQL, tel que :
/usr/local/mysql/bin/mysqlbinlog /tmp/binlog.000001
Cet outil vous permet d'afficher toutes les instructions SQL dans une base de données spécifiée et peut également limiter la plage de temps, ce qui est très pratique. Veuillez vous référer au manuel pour plus de détails.
Lors de la restauration, vous pouvez utiliser des instructions similaires à celles-ci :
/usr/local/mysql/bin/mysqlbinlog /tmp/binlog.000001 mysql -uyejr -pyejr nom_base de données
Utilisez les instructions SQL sorties par mysqlbinlog directement comme entrée pour l'exécuter.
Si vous avez une machine inactive, vous pouvez aussi bien utiliser cette méthode pour la sauvegarder. Étant donné que les exigences de performances des machines esclaves sont relativement faibles, le coût est faible. Une sauvegarde incrémentielle peut être réalisée à faible coût et une partie de la pression des requêtes de données peut être partagée. Pourquoi pas ?
5. Sauvegarde directe des fichiers de données Par rapport aux méthodes précédentes, la sauvegarde des fichiers de données est la plus directe, la plus rapide et la plus pratique. L'inconvénient est que la sauvegarde incrémentielle est fondamentalement impossible. Afin de garantir la cohérence des données, l'instruction SQL suivante doit être exécutée avant de sauvegarder le fichier :
FLUSH TABLES AVEC READ LOCK ; c'est-à-dire vider toutes les données de la mémoire sur le disque et verrouiller la table de données pour garantir qu'aucune nouvelle donnée n'est écrite pendant le processus de copie. Les données sauvegardées par cette méthode sont également très simples à restaurer, il suffit de les recopier dans le répertoire de la base de données d'origine.
Notez que pour les tables de type Innodb, vous devez également sauvegarder ses fichiers journaux, c'est-à-dire les fichiers ib_logfile*. Car lorsque la table Innodb est endommagée, vous pouvez compter sur ces fichiers journaux pour la récupérer.
6. Stratégie de sauvegarde Pour les systèmes avec un volume d'activité moyen, la stratégie de sauvegarde peut être déterminée comme suit : sauvegarde complète pour la première fois, sauvegarde incrémentielle une fois par jour, sauvegarde complète une fois par semaine, etc. Pour les systèmes importants et occupés, vous aurez peut-être besoin d'une sauvegarde complète une fois par jour, d'une sauvegarde incrémentielle une fois par heure, voire plus fréquemment. Afin de réaliser une sauvegarde en ligne et une sauvegarde incrémentielle sans affecter les activités en ligne, le meilleur moyen consiste à utiliser le mécanisme de réplication maître-esclave (réplication) pour effectuer des sauvegardes sur la machine esclave.
7. Maintenance des données et reprise après sinistre. En tant qu'administrateur de base de données (je ne le suis pas encore, haha), l'une des tâches les plus importantes est de garantir que les tables de données peuvent être utilisées en toute sécurité, de manière stable et à grande vitesse. Par conséquent, vos tableaux de données doivent être entretenus régulièrement. L'instruction SQL suivante est utile :
CHECK TABLE ou REPAIR TABLE, vérifier ou maintenir les tables MyISAM
OPTIMISER LA TABLE, optimiser la table MyISAM
ANALYZE TABLE, analysez la table MyISAM Bien entendu, les commandes ci-dessus peuvent toutes être complétées via l'outil myisamchk, et ne seront pas décrites en détail ici.
Les tables Innodb peuvent être défragmentées et améliorées en termes de vitesse d'indexation en exécutant les instructions suivantes :
ALTER TABLE nom_table MOTEUR = Innodb;
Il s'agit en fait d'une opération NULL. En apparence, elle ne fait rien, mais elle réorganise les fragments.
Les tables MyISAM couramment utilisées peuvent être restaurées à l'aide des méthodes mentionnées ci-dessus. Si l'index est cassé, vous pouvez utiliser l'outil myisamchk pour reconstruire l'index. Pour les tables Innodb, ce n'est pas si simple, car il stocke toutes les tables dans un seul espace table. Cependant, Innodb dispose d'un mécanisme de vérification appelé point de contrôle flou. Tant que le fichier journal est enregistré, les erreurs peuvent être réparées en fonction du fichier journal. Vous pouvez ajouter les paramètres suivants dans le fichier my.cnf pour permettre à mysqld de vérifier automatiquement le fichier journal au démarrage :
innodb_force_recovery = 4
Voir le manuel pour plus d'informations sur ce paramètre.
8. Résumez la sauvegarde des données et déterminez la stratégie de sauvegarde appropriée. C'est une petite partie de ce que fait un administrateur de base de données. Tout est difficile au début.