Introduction à la méthode de récupération de données MySQL via binlog (exemple de code)

Cet article vous apporte une introduction à la méthode de récupération de données MySQL via binlog (exemple de code). J'espère qu'il sera utile aux amis dans le besoin. vous aide.

En comprenant la configuration pertinente des journaux binlog, vous pouvez simplement maîtriser l'opération de récupération des données de la base de données via binlog

fichier journal mysql

;

Tout logiciel mature disposera d'un système de journalisation mature. Lorsqu'un problème survient avec le logiciel, ces journaux constituent un trésor pour interroger la source du problème. De même, MySQL ne fait pas exception. Il y aura également une série de journaux enregistrant l'état d'exécution de MySQL.

mysql possède principalement les types de journaux suivants :

• Journal des erreurs : enregistre les informations d'erreur lors du fonctionnement de mysql

• Journal général des requêtes : enregistrez les instructions exécutées par MySQL, y compris chaque requête SQL, modification, mise à jour, etc.

• Journal des requêtes lentes : enregistrez les instructions SQL qui prennent du temps à interroger

• journal binlog : enregistre les enregistrements de modifications de données, y compris la création de tables, les mises à jour de données, etc.

Ces journaux doivent être configurés dans mon Fichier .cnf, si vous ne connaissez pas mysql Vous pouvez utiliser la commande mysql pour trouver le chemin du fichier de configuration

mysql --verbose --help|grep -A 1 'Default options’ #该命令会罗列出my.cnf 顺序查找的路径。

journal binlog

binlog est le. journal binaire, un fichier journal binaire qui enregistre toutes les instructions de mise à jour de la base de données. Y compris les mises à jour de tables et les mises à jour d'enregistrements, c'est-à-dire le langage de manipulation de données (DML), binlog est principalement utilisé pour la récupération de données et la configuration de la réplication maître-esclave

• Récupération de données : lorsque la base de données est accidentellement supprimée ou lorsque quelque chose d'indescriptible se produit, les données peuvent être restaurées à un certain moment via binlog.

• Réplication maître-esclave : lorsqu'il y a une mise à jour de la base de données, la base de données maître enregistre via binlog et notifie à la base de données esclave de se mettre à jour, garantissant ainsi que les données de la base de données maître-esclave sont cohérentes ;

Mysql est divisé en module de couche de service et en module de couche de moteur de stockage en fonction de ses fonctions. La couche de service est responsable de la connexion client, de l'optimisation du traitement des instructions SQL et d'autres opérations. La couche moteur de stockage est responsable du stockage et des requêtes des données ; binlog appartient au module de couche de service. Le journal est indépendant du moteur et toutes les modifications de données du moteur de données seront enregistrées dans le journal binlog. Lorsque la base de données plante, si le moteur InnoDB est utilisé, le journal binlog peut également vérifier l'état de validation du journal redo InnoDB.

Journal binlog activé

Méthode activée par le journal :

Ajouter une configuration

log_bin=ON
log_bin_basename=/path/bin-log
log_bin_index=/path/bin-log.index

2. Définissez simplement le paramètre log-bin

 log-bin=/path/bin-log

Lorsque binlog est activé, mysql créera un fichier .index spécifié par log_bin_index et plusieurs fichiers journaux binaires. L'index enregistre toutes les données utilisées par mysql dans. commande. fichier binlog. Le journal binlog utilisera le nom spécifié (ou la valeur par défaut) plus un nombre incrémenté comme suffixe, ex : bin-log.000001 Lorsque les trois situations suivantes se produisent, le journal binlog sera reconstruit :

Le. la taille du fichier atteint la valeur du paramètre max_binlog_size

Exécuter la commande flush logs
Redémarrer le service mysql

format de journal binlog

Par la valeur du paramètre paramètre binlog_format, vous pouvez définir le format binlog. Les valeurs facultatives incluent instruction, ligne, mixte

  * statement 格式：记录数据库执行的原始 SQL 语句
  * row 格式：记录具体的行的修改，这个为目前默认值
  * mixed 格式：因为上边两种格式各有优缺点，所以就出现了 mixed 格式

outil d'affichage du journal binlog : mysqlbinlog

Parce que binlog est un fichier binaire et ne peut pas être utilisé comme les autres. Identique au fichier, ouvrez-le directement et visualisez-le. Cependant, MySQL fournit l'outil de visualisation de binlog mysqlbinlog, qui peut analyser les fichiers binaires. Bien sûr, les résultats de l'analyse des journaux dans différents formats sont différents ;

1. statement 格式日志，执行 mysqlbinlog  /path/bin-log.000001，可以直接看到原始执行的 SQL 语句
2. row格式日志，则可读性没有那么好，但仍可通过参数使文档更加可读 mysqlbinlog -v  /path/bin-log.000001

Mysqlbinlog a deux paires de paramètres très importants

  1. --start-datetime  --stop-datetime 解析某一个时间段内的binlog；
  2. --start-position  --stop-position   解析在两个position之间的binlog；

Utilisez binlog pour récupérer des données :

Utiliser binlog pour récupérer des données consiste essentiellement à rechercher toutes les opérations DML via binlog, à supprimer les instructions SQL erronées, puis à parcourir à nouveau la Longue Marche pour récupérer les données

Pratique hors ligne Fuck :

Créer une table de données et insérer la valeur initiale

Trouver la position de la base de données et binlog de la dernière sauvegarde complète (ps : bien sûr il peut également être restauré au fil du temps). Ici, l'état actuel est utilisé comme valeur initiale de la sauvegarde

    CREATE TABLE `users` (
              `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
              `name` varchar(255) DEFAULT NULL,
              `age` int(8) DEFAULT NULL,
              PRIMARY KEY (`id`)
     ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
     INSERT INTO `users` (`id`, `name`, `age`)
        VALUES
            (null, '姓名一', 5);

Insérer plusieurs enregistrements

    mysqldump -uroot -p T > /path/xxx.sql;   # 备份数据库
    show master status;   # 查看当前的position位置，此时值为154

Faites une erreur et insérez plusieurs données après l'erreur

  INSERT INTO `users` (`id`, `name`, `age`)
  VALUES
     (null, '姓名二', 13),
     (null, '姓名三', 14),
     (null, '姓名四', 15),
     (null, '姓名五', 16),
     (null, '姓名六', 17);

Après avoir découvert le mauvais fonctionnement, effectuez la récupération des données. Tout d'abord, arrêtez le service externe de MySQL et utilisez les données de sauvegarde pour restaurer les dernières données

update users set age = 5；
 INSERT INTO `users` (`id`, `name`, `age`)
 VALUES
    (null, '姓名七', 16),
    (null, '姓名八', 18);

Utilisez la commande mysqlbinlog pour analyser le fichier binaire ; et l'analyse trouvée

Exportez le fichier SQL exécutable du journal binlog via la commande mysqlbinlog et importez les données dans mysql

    误操作发生在position为706位置，且上次正常操作的结束位置在513
    在1152到结尾位置有正常执行的SQL执行

Ignorez la mauvaise instruction de mise à jour, puis utilisez la logique de l'étape 7 pour exécuter à nouveau l'instruction normale pour terminer le travail de récupération des données

 mysqlbinlog --start-position=154  --stop-position=513  bin-log.000001 > /path/bak.sql;
 mysql -uroot -p < /path/bak.sql;

Résumé

Peu importe quand, un crash de base de données fera froncer les sourcils et s'énervera . Binlog peut être considéré comme un médicament regrettable après un crash de base de données et une perte de données dans diverses circonstances. Cet article mène simplement une expérience de récupération de données sur la base de données via un environnement hors ligne. S'il y a quelque chose qui ne va pas, veuillez me donner quelques conseils

<.>Cet article se termine ici. Pour plus de connaissances sur MySQL, vous pouvez faire attention à la colonne

Tutoriel MySQL

sur le site php chinois ! ! !

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!