Innobackupex et mydumper, outils de sauvegarde MySQL

-------------------------------------------------------------- --- -

------Outil de sauvegarde physique Innobackupex------

------------------ --- -----------------------------

Manuel officiel : https://www.percona.com /doc/percona- xtrabackup/LATEST/index.html

est principalement utilisé pour la sauvegarde à chaud des données stockées dans des moteurs tels que InnoDB et MyISAM. Lors de la sauvegarde, les données à sauvegarder sont chargées dans la mémoire et. puis écrit dans le fichier de données de sauvegarde sur le disque. Les données modifiées lors de la sauvegarde sont ajoutées au fichier de sauvegarde de la même manière que pour la récupération du journal redo.

============================================ == ================================================= == ==

Processus de préparation complet d'innobackupex :

1. Activez xtrabackup_logfile. Utilisé pour enregistrer ces nouvelles modifications de données dans xtrabackup_logfile en temps réel pendant tout le processus de sauvegarde à chaud lorsque de nouvelles opérations DML sous le moteur de stockage InnoDB produisent des modifications de données. Le format d'enregistrement est le même que celui du redo log

2, en page. unités Copiez les fichiers de données stockés dans InnoDB : fichiers ibdataX et .ibd de l'espace table partagé. Étant donné que la page peut être écrite pendant la copie, les valeurs de somme de contrôle de tête et de queue de la page seront différentes. Par conséquent, lors de la génération ultérieure de fichiers de sauvegarde, vous devez appliquer le journal avant de les utiliser pour réparer certaines pages incomplètes.

3. Tables affleurantes avec verrou de lecture. Ajoutez un verrou en lecture à la table MyISAM pour copier les données stockées dans le moteur de non-transaction MyISAM

4. Copiez les fichiers .frm, .MYD et .MYI.

5. Obtenez la dernière position du binlog au moment où la sauvegarde est terminée : xtrabackup_binlog_info (les fichiers de données InnoDB peuvent être mis à jour).

6. Déverrouiller les tables ;

7. Une fois la sauvegarde terminée, enregistrez les paramètres minimaux requis pour démarrer la sauvegarde sur backup-my.cnf

( 2) Enregistrez le LSN dans xtrabackup_logfile.

(3) Enregistrez le type de sauvegarde (sauvegarde complète : complète, incrémentielle : incrémentielle ; les sauvegardes qui ont été appliquées dans le journal seront modifiées en préparation complète) et d'autres informations dans xtrabackup_checkpoints.

(4) Enregistrez d'autres informations de sauvegarde : xtrabackup_info

Ce qui suit est un résumé des données de la table de base de données, des fichiers d'espace table (ibdata) et du journal de rétablissement (ib_logfile) dans le répertoire de données sauf copie. De plus, tous les fichiers générés :

(1) backup-my.cnf

(2) xtrabackup_binlog_info : lors de l'utilisation de MyISAM. pour la sauvegarde des données, plus précis que xtrabackup_binlog_pos_innodb

(3) xtrabackup_binlog_pos_innodb : le fichier nouvellement généré après l'application du journal enregistre uniquement la position du journal binaire d'innodb et ne calcule pas le journal binaire généré par MyISAM

(4) xtrabackup_checkpoints

(5) xtrabackup_info

(6) xtrabackup_logfile (fichier principal)

(7) xtrabackup_slave_info (sauvegarder les fichiers importants de la bibliothèque) : vous devez ajouter l'option --slave-info lors de la sauvegarde, et "modifier master" sera enregistré dans ce fichier pour..." informations. Après avoir utilisé le fichier de sauvegarde pour restaurer la base de données esclave, ces informations seront utilisées pour pointer vers la base de données maître pour la synchronisation.

============================================ == ================================================= == ==

Processus de sauvegarde incrémentielle d'innobackupex

Lorsque innobackupex sauvegarde de manière incrémentielle les données de la table InnoDB, par rapport au processus de sauvegarde complète, lorsque la sauvegarde incrémentielle copie la page, elle comparera la sauvegarde. Le LSN de la page entre le fichier et les données actuelles, et le LSN de la page liée aux données modifiées augmenteront. Ainsi, innobackupex n'a besoin que de sauvegarder les pages avec des LSN modifiés.

Lors de la sauvegarde de MyISAM, une opération de sauvegarde complète est toujours effectuée.

============================================ == ================================================= == ==

Exemple d'instruction de sauvegarde

Autorisations requises pour le compte de sauvegarde : RELOAD, LOCK TABLES, REPLICATION CLIENT

(1) Entièrement préparé :

étape 1 :

innobackupex --defaults-file=/usr/local/mysql/my.cnf --user=username --password='user_passwd' --host=[HOST] -- port=【PORT】 --no-timestamp /tmp/innobackup_all

step2：

innobackupex --apply-log --defaults-file=/tmp/innobackup_all/backup-my .cnf --user=username --password='user_passwd' --host=[HOST]--port=[PORT] --/tmp/innobackup_all

(2) Sauvegardes partielles : formulaire de sauvegarde Par exemple : mydatabase.mytable

étape 1 :

Utilisez --include avec l'expression régulière

innobackupex --include='^mydatabase[.]mytable' /path/to /backup --no-timestamp

Utilisez --tables-file avec un fichier texte enregistrant le nom complet de la table (un nom de table par ligne)

echo "mydatabase.mytable " > /tmp/tables.txt

innobackupex --tables-file=/tmp/tables.txt /path/to/backup --no-timestamp

Utilisez --databases pour spécifier les bibliothèques et les tables (par exemple, table de sauvegarde : mydatabase.mytable et bibliothèque : mysql)

innobackupex --databases="mydatabase.mytable mysql" /path/to/backup --no -timestamp --user=backup --password=backup

étape 2 :

préparer une sauvegarde partielle : innobackupex --apply-log --export /path/to/ backup/

(--databases, les tables de base de données non spécifiées vous demanderont « est-ce que la note existe » pendant la phase de préparation, vous pouvez ignorer ce message)

(3) Sauvegarde incrémentielle (en supposant qu'elle est entièrement préparé, Chemin : $FULLBACKUP)

étape 1 :

Première sauvegarde incrémentielle (basée sur une sauvegarde complète) : innobackupex --incremental $INCREMENTALBACKUP_1 --incremental-basedir=$FULLBACKUP --user=USER --password=PASSWORD

Deuxième sauvegarde incrémentielle (basée sur la première sauvegarde incrémentielle) : innobackupex --incremental $INCREMENTALBACKUP_2 --incremental-basedir=NCREMENTALBACKUP_1 --user=USER -- password=PASSWORD

(...)

Nième fois

étape 2 : préparer

innobackupex -- apply-log --redo-only $FULLBACKUP --use-memory=1G --user=USER --password=PASSWORD

innobackupex --apply-log --redo-only $FULLBACKUP--incremental- dir=$INCREMENTALBACKUP_1 --use- memory=1G --user=DVADER --password=D4RKS1D3

innobackupex --apply-log --redo-only $FULLBACKUP --incremental-dir=$ INCREMENTALBACKUP_2 --use-memory=1G --user=DVADER --password=D4RKS1D3

(...)

innobackupex --apply-log--redo -only $FULLBACKUP --incremental-dir=$ INCREMENTALBACKUP_N --use-memory=1G --user=DVADER --password=D4RKS1D3

innobackupex --apply-log $FULLBACKUP --use- memory=1G --user=$USERNAME -- password=$PASSWORD

--use-memory : Spécifiez la mémoire que la préparation peut utiliser en conjonction avec --apply-log pour accélérer la préparation

Dans la phase de préparation, --redo-only doit être ajouté lors du premier processus d'intégration de sauvegarde complète et de sauvegarde incrémentielle. Enfin, une fois toutes les sauvegardes incrémentielles intégrées, les fichiers de sauvegarde complète intégrés aux sauvegardes incrémentielles doivent être à nouveau préparés.

============================================ == ================================================= == ==

Quelques autres paramètres courants :

Utiliser ensemble : --stream=xbstream --compress --compress-threads=8 --parallel=4 > Option xbstream Les fichiers ibd de la table seront compressés et diffusés un par un, le paramètre innodb-file-per-table doit donc être activé)

--parallèle : numéro de concurrence de sauvegarde (faisant référence à la copie le fichier ibd, différent de compress-threads (c'est le nombre de threads effectuant la compression)

--stream: tar, xbstream. Souvent utilisé avec : innobackupex [...] --stream=tar /backupdir/ | gzip - > backupfile.tar.gz

--tmpdir : le répertoire temporaire avant la diffusion sur la machine distante Emplacement

--cryptage : cryptage de sauvegarde. Dans les situations réelles, la méthode la plus couramment utilisée est

(1) openssl, qui ajoute des options de chiffrement à la méthode tar+gz ci-dessus : innobackupex [...] --stream=tar /backupdir/ | | openssl aes -256-cbc -k "abc" > backupfile.tar.gz.aes-256-cbc

(2) des3, innobackupex [...] --stream=tar /backupdir/ | gzip - | openssl des3 -salt -k "abc" > backupfile.tar.gz.des3

=================== === ================================================ === ========================

processus de récupération d'innobackupex

1. innobackupex -- apply-log, le but est d'obtenir le journal redo de xtrabackup_log, de mettre à jour certaines pages incomplètes, de définir les valeurs de somme de contrôle de tête et de queue et de mettre à jour le LSN avec le dernier numéro LSN dans le processus de sauvegarde (en fait, cela devrait l'être ; divisé en processus de sauvegarde)

2. Copiez les données de sauvegarde dans le répertoire de données de la base de données

3. Modifiez les autorisations du répertoire de données et démarrez-le.

============================================ == ================================================= == ==

Exemple d'instruction de récupération :

1. Fermez l'instance avant la récupération

2. Sauvegardez le répertoire de données d'origine (le journal de rétablissement et le journal d'annulation également). doivent être sauvegardés s'ils sont séparés)

3. innobackupex --copy-back --user=username --password='user_passwd' --socket=/usr/local/mysql/run/mysqld .sock --defaults-file=/usr /local/mysql/my.cnf /tmp/innobackup_all (ou copiez directement le fichier de sauvegarde préparé)

4. Modifiez les autorisations du répertoire et démarrez mysql

====== ======================================== ========== ========================================= =

De Quanbei Avec Percona XtraBackup, vous pouvez exporter des tables individuelles de n'importe quelle base de données InnoDB et les importer dans Percona Server avec XtraDB ou MySQL 5.6 (la source n'a pas besoin d'être XtraDB ou MySQL 5.6, mais la destination le fait). Cela ne fonctionne que sur des fichiers .ibd individuels et ne peut pas exporter une table qui n'est pas contenue dans son propre fichier .ibd.

est requis Dans la phase de préparation, une seule table est exportée via l'option --export :

Une fois une sauvegarde complète créée, préparez-la avec l'option --export :

$ innobackupex --apply-log --export /path/to/backup

Cela créera pour chaque InnoDB avec son propre tablespace un fichier avec l'extension .exp.

Un fichier se terminant par .exp sera créé pour l'espace table de chaque table innodb

Le fichier de sortie est sous la forme :

/data/backups/mysql/test/export_test .exp
/data/backups/mysql/test/export_test.ibd
/data/backups/mysql/test/export_test.cfg

Lors de l'importation de tables à partir d'autres serveurs, vous avez besoin pour créer d'abord une table (car il n'y a pas d'informations sur la structure de la table dans le fichier de table indépendant) :

mysqlfrm --diagnostic /data/2017-03-22_16-13-00/yayun/t1.frm (en utilisant le L'outil mysql-utilities mysqlfrm lit la structure de la table à partir du fichier de sauvegarde)

mysql> CREATE TABLE mytable (...) ENGINE=InnoDB; (Allez créer une table basée sur la structure de table lue précédemment)

Supprimer les fichiers d'espace table :

mysql> ALTER TABLE mydatabase.mytable DISCARD TABLESPACE;

Copiez les fichiers .ibd et .exp exportés dans le répertoire de données :

Après cela, copiez les fichiers mytable.ibd et mytable.exp (ou mytable.cfg si vous importez vers MySQL 5.6) dans le répertoire d'accueil de la base de données

, puis importez l'espace table :

mysql> mabase de données.mytable IMPORT TABLESPACE;

-------------------------------------- --- ---

------Outil de sauvegarde logique mydumper------

---------------- --- ------------------------

Certains documents en anglais sont extraits du README sur GitHub : https://github. com/maxbube/ mydumper

Dans la version 5.5/5.6 de la base de données MySQL, par rapport à l'utilisation du mysqldump officiellement fourni pour la sauvegarde monothread, l'outil de sauvegarde multithread mydumper présente des avantages uniques. (Pour les versions postérieures à MySQL 5.7.11, le responsable a finalement résolu le problème de sauvegarde cohérente de l'outil de sauvegarde logique parallèle mysqlpump. Pour mysqlpump, veuillez vous référer à l'introduction de Daniel Jiang Chengyao : http://www.tuicool.com/articles/ E77bYz7)

Inconvénients : Il est difficile de sauvegarder sur un centre de sauvegarde distant via un streaming simultané, et le plus souvent cela se fait directement sur le disque local.

== Comment fonctionne un instantané cohérent ? ==
Tout cela est effectué selon les meilleures pratiques et traditions MySQL :

* Par précaution, les requêtes lentes sur le serveur annulent le dump, ou se faire tuer
* Le verrouillage global en écriture est acquis ("FLUSH TABLES WITH READ LOCK")
* Diverses métadonnées sont lues ("SHOW SLAVE STATUS", "SHOW MASTER STATUS")
* Autres threads connectez-vous et établissez des instantanés ("START TRANSACTION AVEC UN INSTANTANÉ CONSISTANT")
** Dans la version antérieure à la version 4.1.8, il crée une table InnoDB factice et la lit.
* Une fois que tous les threads de travail annoncent l'établissement de l'instantané, le maître exécute "DÉVERROUILLEZ LES TABLES" et démarre la mise en file d'attente des tâches.

Mécanisme de mise en œuvre de la cohérence de Mydumper :

* Lorsque vous rencontrez une requête lente, soit le dump arrête de s'exécuter, soit mydumper tue la requête lente. (Le paramètre --long-query-guard est utilisé pour convenir de la durée d'une requête lente. La valeur par défaut est de 60 secondes. Si --kill-long-queries est ajouté à ce paramètre, la requête lente sera activement supprimée. S'il n'est pas ajouté, mydumper tuera automatiquement la requête lente lorsqu'il rencontrera la requête lente. Il cessera de s'exécuter)
* Utilisez "FLUSH TABLES WITH READ LOCK" pour appliquer un verrou de lecture global, ce qui empêchera les instructions DML
* Afficher les métadonnées : "SHOW SLAVE STATUS", "SHOW MASTER STATUS"

* "START TRANSACTION AVEC un instantané cohérent" : Lorsque le démarrage de la transaction ouvre la transaction, il établit immédiatement un instantané de la lecture cohérente de la transaction en cours. Sans l'option with, la transaction ne démarrera réellement que lorsque la première instruction de la transaction sera exécutée, et un instantané de lecture cohérent sera établi

* À partir de la version 4.1.8, mydumper crée une table virtuelle de type InnoDB . Lisez les données à partir de celui-ci

* Une fois que tous les threads signalent que l'instantané de cohérence est établi, exécutez "UNLOCK TABLES" et démarrez la tâche de file d'attente.

Exemple d'instruction de sauvegarde :

mydumper --user=username --password=user_passwd --socket=/... --regex '^( ?!(mysql))' --output=/backupdir --compress --verbose=3 --logfile=/backupdir/mydumper_backup.log

Explication des paramètres communs :

-- base de données Spécifiez les bibliothèques qui doivent être sauvegardées
--tables-list Spécifiez les tables qui doivent être sauvegardées, séparées par (en cas de conflit avec l'option regex, l'expression régulière prévaudra)

- -regex '^(?!(mysql |test))' : options de filtrage de la base de données

--output=/backupdir : chemin de sortie du fichier de sauvegarde

--compress : sortie compressée fichier (suffixe .gz)

--verbose=3 : informations de niveau de journal de sortie pour faciliter l'observation de l'état de la sauvegarde (0 = silencieux, 1 = erreurs, 2 = avertissements, 3 = informations, par défaut 2)

--logfile=/ backupdir/mydumper_backup.log : Spécifiez l'emplacement du fichier journal d'exécution de mydumper

--threads Spécifiez le nombre de threads utilisés lors de la sauvegarde, la valeur par défaut est 4

--statement-size : Limiter la longueur maximale des instructions SQL (mydumper fusionnera SQL lors de la sauvegarde)
--rows : Divisez la table par le nombre de lignes. Améliorez les performances de concurrence lorsque myloader
--chunk-filesize : divisez les données de la table en fonction de la taille du fichier de sortie. Améliorez les performances de concurrence de myloader
--no-locks : ne verrouillez pas la table (les données peuvent être incohérentes)
--binlogs : sauvegardez le binlog. Lorsque la sauvegarde échoue, vous pouvez vérifier le journal de sauvegarde et trouver la cause de l'erreur à proximité du point de sauvegarde

Répertoire du fichier de sauvegarde de sortie :

* Structure de la bibliothèque : dbname-schema- créer.sql.gz

* Structure de la table : dbname.tblname1-schema.sql.gz

* Données de la table : dbname.tblname1.sql.gz

(Chaque bibliothèque et table possède son propre fichier de sauvegarde indépendant. Lorsqu'une seule table doit être restaurée, restaurez-la via mydumper Table données complètes + incrément de récupération du binlog)

* métadonnées : lors de l'inclusion de la sauvegarde, la position actuelle du binlog est

---------------- -- ------------------------------------------------ --

Dump démarré à : 2017-07-04 09:45:57
AFFICHER LE STATUT DU MAÎTRE :
Journal : mysql-bin.000048
Pos : 107
GTID : (null)
Dump terminé à : 2017-07-04 09:45:57

--------------------- ----- ---------------------------------------

* mydumper_backup.log : Enregistrez l'état d'exécution du programme de sauvegarde

Explication des paramètres pertinents de la commande de récupération myloader
--emplacement du fichier de sauvegarde du répertoire
--requêtes-par-transaction Le nombre de SQL exécutés pour chaque transaction, la valeur par défaut est 1000
--overwrite-tables Supprimez d'abord les tables existantes, puis restaurez-les (il est nécessaire de sauvegarder la structure des tables lors de la sauvegarde des fichiers)
--database spécifie la base de données qui doit être restaurée
--enable-binlog est utilisée pour restaurer les données Operation record binlog
--threads spécifie le nombre de threads utilisés lors de la restauration, la valeur par défaut est 4

- -enable-binlog : restaurer le binlog sauvegardé

Remarque : myloader ne peut être que dans la bibliothèque Récupérer au niveau niveau. La récupération d'une seule table peut appeler directement le fichier correspondant contenant les instructions SQL dans le fichier de sauvegarde

De plus, innobackupex sauvegarde les données avant ce moment où la sauvegarde est terminée, tandis que mydumper (y compris mysqldump , mysqlpump, etc.) Le moment des données sauvegardées est l'heure à laquelle la sauvegarde démarre.

Permettez-moi de mentionner l'idée principale de la récupération : qu'il s'agisse d'une sauvegarde physique ou d'une sauvegarde logique, le principe de récupération le plus fiable est que la base de données doit interdire temporairement l'écriture des données. Ensuite, restaurez d'abord la sauvegarde complète, appliquez une sauvegarde incrémentielle au point de défaillance le plus proche, puis appliquez le journal binlog et ignorez le point de défaillance.

Considérez toujours qu'arrêter l'opération d'écriture sur le serveur en ligne pour quelques instructions de mauvais fonctionnement sur une seule table et utiliser la méthode de récupération complète + incrémentielle est un peu laborieux et le gain dépasse la perte. S'il n'existe pas de base de données de secours avec une stratégie de délai de réplication, utiliser les fichiers sauvegardés par mydumper pour restaurer une seule table, ou prendre du recul et utiliser le flashback est une solution rapide et efficace.

Conseils :

Après avoir utilisé Innobackupex ou mydumper pour restaurer la plupart des données, utilisez mysqlbing pour remplir les parties de données qui ne peuvent pas être couvertes par le programme de sauvegarde ci-dessus.

Explication du paramètre Mysqlbinlog :

–start-position=N (inclus lors de la lecture)

Démarrez la lecture à partir de l'événement lorsque la première position dans le journal binaire est égale au paramètre N.
–stop-position=N (non inclus lors de la lecture)
Arrêtez la lecture de l'événement lorsque la première position dans le journal binaire est égale ou supérieure au paramètre N.

Lorsque vous utilisez mysqlbinlog pour appliquer des journaux binlog, si vous devez s'étendre sur plusieurs fichiers, lisez plusieurs fichiers en même temps. La position de départ est le point de départ du premier fichier binlog et la position d'arrêt est. le point final du dernier fichier.

Exemple : mysql-bin.000048 (pos856), mysql-bin.000051 (pos1042)

/usr/local/mysql/bin/mysqlbinlog mysql-bin.000048 mysql-bin. 000049 mysql-bin.000050 mysql-bin.000051 --start-position=856 --stop-position=1042 > /tmp/backup1/backup_new.sql

Conseils :

Ayez toujours une surveillance des sauvegardes ;

Les objets de sauvegarde des deux outils de sauvegarde ci-dessus sont principalement inclus dans le répertoire de données. Il est à noter que le binlog contient également certaines données, ainsi que les données. binlog est également requis. Faites des sauvegardes.

Une brève mention sur la stratégie de sauvegarde. La stratégie de sauvegarde que nous développons est déterminée en fonction du type d'entreprise.

Pour les activités de croissance des données, une stratégie complète + incrémentielle est adoptée, tandis que pour le type de mise à jour des données, une sauvegarde complète est adoptée.

La sauvegarde logique est souvent utilisée pour des opérations telles que la mise à niveau de version MySQL ou la récupération d'une seule table.

Pour résumer les considérations ci-dessus, les bases de données en ligne adoptent généralement la sauvegarde physique comme méthode principale, la sauvegarde logique en complément et la sauvegarde des journaux binaires.

Document de référence :

Description du fichier de génération de sauvegarde innobackupex :

http://fordba.com/xtrabackup-produce-file-intruduction.html

Recettes pour innobackupex :

https://www.percona.com/doc/percona-xtrabackup/LATEST/how-tos.html#recipes-ibk

Comment générer à partir de innobackupex Restauration d'une seule table en sauvegarde complète :

https://www.percona.com/doc/percona-xtrabackup/2.2/innobackupex/restoring_individual_tables_ibk.html

https:/ / www.percona.com/blog/2012/01/25/how-to-recover-a-single-innodb-table-from-a-full-backup/

https:// www .percona.com/blog/2017/03/15/restore-single-innodb-table-full-backup-accidentally-dropping/

Cas réel de récupération d'une seule table :

http://www.cnblogs.com/gomysql/p/6600616.html

Créer et restaurer des sauvegardes partielles :

https://www.percona. com /doc/percona-xtrabackup/2.2/innobackupex/partial_backups_innobackupex.html

Mysqldump combiné avec un cas de récupération de binlog :

http://blog.chinaunix.net/uid-25135004-id- 1761635.html

http://www.cnblogs.com/hanyifeng/p/5756462.html

Utiliser le fichier complet mysqldump pour la récupération d'une seule base de données (à tester)

https://stackoverflow.com/questions/1013852/can-i-restore-a-single-table-from-a-full-mysql-mysqldump-file