Comment récupérer rapidement des données après un mauvais fonctionnement de MySQL

Système de gestion de bases de données relationnelles MySQL

MySQL est un petit système de gestion de bases de données relationnelles open source développé par la société suédoise MySQL AB. MySQL est largement utilisé sur les sites Web de petite et moyenne taille sur Internet. En raison de sa petite taille, de sa vitesse rapide, de son faible coût total de possession et surtout des caractéristiques de l'open source, de nombreux sites Web de petite et moyenne taille choisissent MySQL comme base de données de site Web afin de réduire le coût total de possession d'un site Web.

Cet article présente principalement la méthode de récupération rapide des données après un mauvais fonctionnement de MySQL. Les amis dans le besoin peuvent s'y référer

Résumé : Utilisez binlog Flash. retour des données de mauvaise opération.
En gros, tous les programmeurs qui travaillent avec des bases de données (il peut s'agir bien sûr de vos collègues) rencontreront un problème : comment restaurer rapidement MySQL après une mauvaise opération ? Par exemple, si vous supprimez une table et oubliez d’ajouter des restrictions, la table entière sera perdue. S'il s'agit toujours des données commerciales essentielles de l'environnement en ligne, cette question sera alors un gros problème. Il est très important de pouvoir restaurer rapidement les données après une mauvaise opération.

Solution traditionnelle

Reconstruisez l'instance avec une sauvegarde complète, puis utilisez une sauvegarde incrémentielle du journal binaire pour restaurer l'état avant l'erreur de fonctionnement. Ensuite, ignorez le mauvais fonctionnement de SQL et continuez à utiliser binlog. Cette méthode prend du temps et demande beaucoup de travail et ne vaut pas la peine d'être recommandée.

Utilisez binlog2sql pour flashback rapidement

Tout d'abord, vérifiez que le binlog est activé sur votre serveur MySQL et définissez les paramètres suivants :

[mysqld]
server-id = 1
log_bin = /var/log/mysql/mysql-bin.log
max_binlog_size = 100M
binlog-format = row

Si le binlog n'est pas activé, il y a il n'y a pas de préréglage. Si le SQL de restauration est généré, il ne peut vraiment pas être annulé rapidement. Pour MySQL qui stocke des données commerciales importantes, il est fortement recommandé d'activer binlog.

Ensuite, installez l'outil open source binlog2sql. binlog2sql est un outil d'analyse de binlog simple et facile à utiliser. L'une de ses fonctions est de générer du SQL de restauration.

shell> git clone https://github.com/danfengcao/binlog2sql.git
shell> pip install -r requirements.txt

Ensuite, nous pouvons générer du SQL de rollback.

Contexte : Xiao Ming a accidentellement supprimé les données de l'intégralité de la table tbl dans la bibliothèque de test vers 20 heures et a nécessité une restauration urgente.

test库tbl表原有数据
mysql> select * from tbl;
+----+--------+---------------------+
| id | name  | addtime       |
+----+--------+---------------------+
| 1 | 小赵  | 2016-12-10 00:04:33 |
| 2 | 小钱  | 2016-12-10 00:04:48 |
| 3 | 小孙  | 2016-12-13 20:25:00 |
| 4 | 小李  | 2016-12-12 00:00:00 |
+----+--------+---------------------+
4 rows in set (0.00 sec)

mysql> delete from tbl;
Query OK, 4 rows affected (0.00 sec)

20:28时，tbl表误操作被清空
mysql> select * from tbl;
Empty set (0.00 sec)

Étapes pour restaurer les données :

1. Connectez-vous à MySQL et affichez le fichier binlog actuel

mysql> show master status;
+------------------+-----------+
| Log_name     | File_size |
+------------------+-----------+
| mysql-bin.000051 |    967 |
| mysql-bin.000052 |    965 |
+------------------+-----------+

2. Le dernier fichier binlog est mysql-. bin.000052 , nous localisons ensuite l'emplacement du binlog du mauvais fonctionnement SQL. La personne qui a commis une erreur ne peut connaître que l’heure approximative de l’opération erronée, et nous filtrons les données en fonction de l’heure approximative.

shell> python binlog2sql/binlog2sql.py -h127.0.0.1 -P3306 -uadmin -p'admin' -dtest -ttbl --start-file='mysql-bin.000052' --start-datetime='2016-12-13 20:25:00'
 --stop-datetime='2016-12-13 20:30:00'
输出：
INSERT INTO `test`.`tbl`(`addtime`, `id`, `name`) VALUES ('2016-12-13 20:25:00', 3, '小孙'); #start 4 end 290 time 2016-12-13 20:25:46
INSERT INTO `test`.`tbl`(`addtime`, `id`, `name`) VALUES ('2016-12-13 20:26:00', 4, '小李'); #start 317 end 487 time 2016-12-13 20:26:26
UPDATE `test`.`tbl` SET `addtime`='2016-12-12 00:00:00', `id`=4, `name`='小李' WHERE `addtime`='2016-12-13 20:26:00' AND `id`=4 AND `name`='小李' LIMIT 1; 
#start 514 end 701 time 2016-12-13 20:27:07
DELETE FROM `test`.`tbl` WHERE `addtime`='2016-12-10 00:04:33' AND `id`=1 AND `name`='小赵' LIMIT 1; #start 728 end 938 time 2016-12-13 20:28:05
DELETE FROM `test`.`tbl` WHERE `addtime`='2016-12-10 00:04:48' AND `id`=2 AND `name`='小钱' LIMIT 1; #start 728 end 938 time 2016-12-13 20:28:05
DELETE FROM `test`.`tbl` WHERE `addtime`='2016-12-13 20:25:00' AND `id`=3 AND `name`='小孙' LIMIT 1; #start 728 end 938 time 2016-12-13 20:28:05
DELETE FROM `test`.`tbl` WHERE `addtime`='2016-12-12 00:00:00' AND `id`=4 AND `name`='小李' LIMIT 1; #start 728 end 938 time 2016-12-13 20:28:05

3. Nous avons obtenu l'emplacement exact de l'erreur de fonctionnement SQL entre 728 et 938, et avons filtré davantage en fonction de l'emplacement, utilisé le mode flashback pour générer du SQL de restauration et vérifié si le SQL de restauration était correct

shell> python binlog2sql/binlog2sql.py -h127.0.0.1 -P3306 -uadmin -p'admin' -dtest -ttbl --start-file='mysql-bin.000052' --start-pos=3346 --end-pos=3556 -B
输出：
INSERT INTO `test`.`tbl`(`addtime`, `id`, `name`) VALUES ('2016-12-12 00:00:00', 4, '小李'); #start 728 end 938 time 2016-12-13 20:28:05
INSERT INTO `test`.`tbl`(`addtime`, `id`, `name`) VALUES ('2016-12-13 20:25:00', 3, '小孙'); #start 728 end 938 time 2016-12-13 20:28:05
INSERT INTO `test`.`tbl`(`addtime`, `id`, `name`) VALUES ('2016-12-10 00:04:48', 2, '小钱'); #start 728 end 938 time 2016-12-13 20:28:05
INSERT INTO `test`.`tbl`(`addtime`, `id`, `name`) VALUES ('2016-12-10 00:04:33', 1, '小赵'); #start 728 end 938 time 2016-12-13 20:28:05

Confirmez que le SQL de restauration est correct et exécutez l'instruction de restauration. Connectez-vous à MySQL pour confirmer que la restauration des données a réussi.

shell> python binlog2sql.py -h127.0.0.1 -P3306 -uadmin -p'admin' -dtest -ttbl --start-file='mysql-bin.000052'
 --start-pos=3346 --end-pos=3556 -B | mysql -h127.0.0.1 -P3306 -uadmin -p'admin'

mysql> select * from tbl;
+----+--------+---------------------+
| id | name  | addtime       |
+----+--------+---------------------+
| 1 | 小赵  | 2016-12-10 00:04:33 |
| 2 | 小钱  | 2016-12-10 00:04:48 |
| 3 | 小孙  | 2016-12-13 20:25:00 |
| 4 | 小李  | 2016-12-12 00:00:00 |
+----+--------+---------------------+

À ce stade, vous n'avez plus à craindre d'être viré.

Questions fréquemment posées

Certaines personnes peuvent se demander : comment puis-je annuler rapidement mon opération DDL par erreur ? Par exemple, une grande table est supprimée.

C’est difficile à faire. Parce que même en mode ligne, les opérations DDL n'enregistrent pas les modifications apportées à chaque ligne de données dans le journal binaire, le DDL ne peut donc pas être annulé via le journal binaire. Pour implémenter la restauration DDL, les anciennes données doivent être sauvegardées avant d'exécuter DDL. Quelqu'un a en effet obtenu une restauration rapide du DDL en modifiant le code source du serveur MySQL. J'ai trouvé le lin xiaobin d'Alibaba et soumis un correctif. Mais pour autant que je sache, très peu de sociétés Internet nationales ont appliqué cette fonctionnalité. Quant à la raison, je pense que la raison principale est que je suis trop paresseux pour m'en soucier. Il n'est pas nécessaire d'utiliser cette fonction basse fréquence. La raison secondaire est qu'elle ajoutera du stockage supplémentaire.

Par conséquent, un mauvais fonctionnement du DDL ne peut généralement être restauré que via une sauvegarde. Si l’entreprise ne peut plus utiliser de sauvegarde, alors il est vraiment recommandé d’acheter un billet d’avion. Ce qu'il faut faire? Exécuter

mysql En plus de binlog2sql, existe-t-il d'autres outils de restauration ?

Bien sûr. Ali Peng Lixun a ajouté la fonctionnalité de flashback à mysqlbinlog, qui devrait être la première fonction de flashback de mysql. Peng a résolu le problème de la restauration DML et a expliqué l'idée de conception consistant à utiliser binlog pour le flashback DML. La fonctionnalité de restauration DDL a également été proposée et mise en œuvre par l'équipe Alibaba. Ces deux fonctions sont innovantes et les outils de flashback apparus depuis sont essentiellement des imitations des deux ci-dessus. De plus, Inception open source de Qunar est un ensemble d'outils d'exploitation et de maintenance automatisés MySQL. Celui-ci est relativement lourd. Il prend en charge la restauration DML, non pas à partir du binlog, mais à partir de la sauvegarde. Il prend également en charge la structure de la table de restauration DDL. ~ Il existe une autre méthode appelée sauvegarde différée de l'esclave, qui consiste à configurer un esclave qui n'ajoute pas de trafic professionnel et à le retarder délibérément pendant un certain temps. Cela élimine en fait l'étape de récupération d'instance basée sur la méthode traditionnelle. Cette méthode consomme une machine supplémentaire et n'est pas recommandée.

S'il manque d'excellents articles sur d'excellents outils liés à la restauration de MySQL, veuillez nous le faire savoir.

Ce qui précède explique comment récupérer rapidement des données après un mauvais fonctionnement de MySQL. Pour plus de contenu connexe, veuillez faire attention au site Web PHP chinois (www.php.cn) !