Introduction détaillée au « flashback » rapide de MySQL basé sur le binlog hors ligne

Hier, un client a soudainement déclaré qu'il avait supprimé une grande quantité de données par erreur. Le CTO m'a directement attiré dans un groupe de discussion et m'a dit qu'il l'aiderait à récupérer ses données. Ils ont creusé le trou eux-mêmes et ont prévu de laisser le côté développement le restaurer sur la base des journaux d'activité. On leur a dit que seules les informations telles que la suppression de la clé primaire étaient enregistrées et que la suppression physique était impossible.

J'ai regardé les journaux enregistrés sur le serveur et j'ai découvert que plusieurs d'entre eux avaient accidentellement supprimé les enregistrements. Bien qu'Alibaba RDS puisse cloner une instance et la restaurer à l'époque précédant sa suppression, il est difficile de retrouver les dizaines de milliers d'identifiants dispersés, et les données associées à plusieurs tables doivent également être restaurées, ce qui est gênant.

Pensez à la solution de flashback de MySQL. J'ai déjà lu plusieurs articles connexes et j'ai même presque utilisé python pour analyser moi-même le binlog et l'inverser pour obtenir le SQL de restauration. Je n'ai vraiment pas le temps, je dois donc l'utiliser de toute urgence maintenant. . J'ai rapidement cherché des « solutions toutes faites » en ligne.

Le texte commence

---

Le flashback rapide de MySQL (y compris Alibaba RDS) peut être considéré comme l'antidote en cas de mauvais fonctionnement de la base de données. La fonction de flashback peut renvoyer la base de données à avant le mauvais fonctionnement. Mais même la base de données Oracle ne prend en charge le flashback que sur une courte période.

L'implémentation open source MySQL flashback existante sur Internet utilise le principe d'analyse du binlog et de génération de SQL inversé : (doit être en mode ligne)

1. Pour l'opération de suppression, générer une insertion (DELETE_ROWS_EVENT)

2. Pour l'opération de mise à jour, échangez l'ordre des valeurs dans binlog (UPDATE_ROWS_EVENT)

3. Pour l'opération d'insertion, inversement generate delete ( WRITE_ROWS_EVENT)

4. Pour plusieurs événements, générer sql à l'envers

Les deux méthodes d'implémentation ci-dessus se font via le package python-mysql-replication , simulez une bibliothèque esclave de la bibliothèque d'origine, puis utilisez show binary logs pour obtenir le binlog, lancez une demande de synchronisation du binlog, puis analysez EVENT. Cependant, après la synchronisation du binlog d'Alibaba Cloud RDS avec la base de données esclave, il a été rapidement purgé . Si vous souhaitez restaurer la partie des données d'hier, les deux options ne peuvent pas obtenir le journal binaire. En d’autres termes, le temps de flashback est limité.

Il existe également des implémentations relativement simples, qui consistent à analyser le fichier physique binlog et à implémenter la restauration, comme binlog-rollback.pl Je l'ai essayé, mais la vitesse est trop lente.

Afin de ne pas affecter la vitesse, mais aussi de vouloir utiliser une solution de flashback plus mature, nous pouvons faire ceci :

1. Utiliser une instance mysqld auto-construite pour purge Copiez le binlog dans le répertoire de l'instance

2. Dans l'instance auto-construite, créez la table (structure) qui doit être restaurée au préalable, car l'outil doit être connecté pour obtenir des métadonnées de information_schema.columns Lors de la copie des informations

3. , vous pouvez remplacer le nom du fichier binlog de l'instance mysql pour le garder continu

4. Vous devrez peut-être modifier mysql-bin.index pour vous assurer que le nom du fichier peut également être reconnu par mysqld

5. Redémarrez l'instance mysql, show binary logs et voyez s'il est dans la liste

6. Ensuite, vous pouvez l'utiliser n'importe lequel des outils ci-dessus, simuler la bibliothèque esclave, spécifier un fichier binlog, l'heure de début, l'heure de fin, obtenir le SQL de restauration

7. , puis filtrer le sql requis

   en fonction de la logique métier

En bref, il s'agit d'utiliser un autre mysql pour transmettre l'événement binlog. Rappel chaleureux :

1. Ne pas avoir un écart de version trop important entre les deux instances

2. Faites attention aux autorisations des fichiers

3. Si gtid est activé dans la bibliothèque d'origine, cette instance auto-construite doit également activer gtid

Exemple :

python mysqlbinlog_back.py --host="localhost" --username="ecuser" --password="ecuser" --port=3306 \
--schema=dbname --tables="t_xx1,t_xx2,t_xx3" -S "mysql-bin.000019" -E "2017-03-02 13:00:00" -N "2017-03-02 14:09:00" -I -U

===log will also  write to .//mysqlbinlog_flashback.log===
parameter={'start_binlog_file': 'mysql-bin.000019', 'stream': None, 'keep_data': True,
 'file': {'data_create': None, 'flashback': None, 'data': None}, 'add_schema_name': False, 'start_time': None, 
 'keep_current_data': False, 'start_to_timestamp': 1488430800,
 'mysql_setting': {'passwd': 'ecuser', 'host': 'localhost', 'charset': 'utf8', 'port': 3306, 'user': 'ecuser'},
 'table_name': 't_xx1,t_xx2,t_xx3', 'skip_delete': False, 'schema': 'dbname', 'stat': {'flash_sql': {}},
 'table_name_array': ['t_xx1', 't_xx2', 't_xx3'],
 'one_binlog_file': False, 'output_file_path': './log', 'start_position': 4, 'skip_update': True,
 'dump_event': False, 'end_to_timestamp': 1488434940, 'skip_insert': True, 'schema_array': ['dbname']
}
scan 10000 events ....from binlogfile=mysql-bin.000019,timestamp=2017-03-02T11:42:14
scan 20000 events ....from binlogfile=mysql-bin.000019,timestamp=2017-03-02T11:42:29
...

Conseils :
binlog est au format ROW, dml Chaque ligne affectée enregistrera deux événements : Table_map et Row_log. Le table_id dans table_map n'affecte pas l'instance à laquelle il est appliqué. Cet identifiant peut être considéré comme un mécanisme logique pour enregistrer la version de la structure de la table - lorsqu'il ne trouve pas la définition de la table dans table_definition_cache, l'identifiant est incrémenté de 1 et attribué à la table. Enregistrez dans la table binlog.

Expérience d'utilisation de mysqlbinlog_back.py  :

• Assurez-vous de spécifier le nom de la bibliothèque, l'indication, le nom du fichier binlog de démarrage, l'heure de début et la fin temps. Peut accélérer l'analyse.

• Selon les besoins de récupération, sélectionnez -I, -U, -D pour spécifier les types d'opérations à annuler.

• Si seules des données partielles de la table sont restaurées (flashback non complet), la table associée ne peut pas être restaurée correctement. Par exemple, il est nécessaire de restaurer les données supprimées, mais il est impossible de restaurer les données de l'entreprise qui ont provoqué la mise à jour d'autres tables en raison de la suppression, à moins qu'elles ne soient complètement flashées.

• Ne prend pas en charge les champs de table de type enum, tels que le champ f_do_type de t_xx3. Vous pouvez modifier la définition d'énumération sur l'instance auto-construite en int.

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!