Exemple de partage de code sur l'opération de récupération de données après une suppression accidentelle de la base de données MySQL

L'éditeur suivant vous proposera un article sur les instructions d'opération de récupération de données pour la base de données mysql après une suppression accidentelle. L'éditeur le trouve plutôt bon, je vais donc le partager avec vous maintenant et le donner comme référence pour tout le monde. Suivons l'éditeur et jetons un coup d'œil

Dans les travaux quotidiens d'exploitation et de maintenance, la sauvegarde de la base de données mysql est cruciale ! L'importance de la base de données pour le site Web nous empêche d'échouer dans la gestion des données MySQL !
Ensuite, les gens feront inévitablement des erreurs. Peut-être qu'un jour le cerveau fera un court-circuit et une erreur sera commise pour supprimer la base de données. Que dois-je faire ? ? ?

Ce qui suit est une explication du plan de récupération après la suppression accidentelle de la base de données MySQL.

1. Scénario de travail

(1) La base de données MySQL est automatiquement entièrement sauvegardée à 12 : 00 tous les soirs.
(2) Un matin au travail, à 9 heures, un collègue s'est évanoui et a fait tomber une base de données !
(3) Récupération d'urgence requise ! Les fichiers de données de sauvegarde et les fichiers binlog incrémentiels peuvent être utilisés pour la récupération de données.

2. Idées de récupération de données

(1) Utiliser les données enregistrées dans le fichier SQL complet Instruction CHANGE MASTER, fichier binlog et informations sur son point d'emplacement, recherchez la partie incrémentielle dans le fichier binlog.
(2) Utilisez la commande mysqlbinlog pour exporter le fichier binlog ci-dessus dans un fichier SQL et supprimez l'instruction drop .
(3) Les données complètes peuvent être restaurées en exportant des fichiers SQL de fichiers complets et des fichiers binlog incrémentiels.

3. Exemple

------------- - ----------------------------
Tout d'abord, assurez-vous que mysql a la fonction binlog activée Ajoutez le bloc [mysqld] dans le fichier /etc/my.cnf :
log-bin=mysql-bin
puis redémarrez le service mysql
------------ -- ----------------

(1) Créer une table clients sous les ops bibliothèque

mysql> use ops;
mysql> create table customers(
-> id int not null auto_increment,
-> name char(20) not null,
-> age int not null,
-> primary key(id)
-> )engine=InnoDB;
Query OK, 0 rows affected (0.09 sec)

mysql> show tables;
+---------------+
| Tables_in_ops |
+---------------+
| customers |
+---------------+
1 row in set (0.00 sec)

mysql> desc customers;
+-------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | char(20) | NO | | NULL | |
| age | int(11) | NO | | NULL | |
+-------+----------+------+-----+---------+----------------+
3 rows in set (0.02 sec)

mysql> insert into customers values(1,"wangbo","24");
Query OK, 1 row affected (0.06 sec)

mysql> insert into customers values(2,"guohui","22");
Query OK, 1 row affected (0.06 sec)

mysql> insert into customers values(3,"zhangheng","27");
Query OK, 1 row affected (0.09 sec)

mysql> select * from customers;
+----+-----------+-----+
| id | name | age |
+----+-----------+-----+
| 1 | wangbo | 24 |
| 2 | guohui | 22 |
| 3 | zhangheng | 27 |
+----+-----------+-----+
3 rows in set (0.00 sec)

(2) Effectuez maintenant une sauvegarde complète

[root@vm-002 ~]# mysqldump -uroot -p -B -F -R -x --master-data=2 ops|gzip >/opt/backup/ops_$(date +%F).sql.gz
Enter password: 
[root@vm-002 ~]# ls /opt/backup/ops_2016-09-25.sql.gz
-----------------

Description du paramètre :

-B : Spécifier la base de données

-F : Actualiser le journal
-R : Sauvegarde
procédure stockéeetc.-x : verrouiller la table
-- master-data : Ajoutez l'instruction CHANGE MASTER, le fichier binlog et les informations d'emplacement à l'instruction de sauvegarde
-----------------

(3) Insérez à nouveau les données

mysql> insert into customers values(4,"liupeng","21");
Query OK, 1 row affected (0.06 sec)

mysql> insert into customers values(5,"xiaoda","31");
Query OK, 1 row affected (0.07 sec)

mysql> insert into customers values(6,"fuaiai","26");
Query OK, 1 row affected (0.06 sec)

mysql> select * from customers;
+----+-----------+-----+
| id | name | age |
+----+-----------+-----+
| 1 | wangbo | 24 |
| 2 | guohui | 22 |
| 3 | zhangheng | 27 |
| 4 | liupeng | 21 |
| 5 | xiaoda | 31 |
| 6 | fuaiai | 26 |
+----+-----------+-----+
6 rows in set (0.00 sec)

(4) Par erreur, la base de données de test a été supprimée

mysql> drop database ops;
Query OK, 1 row affected (0.04 sec)

A ce moment, entre la préparation complète et le moment de la mauvaise opération, les données écrites par l'utilisateur sont dans le binlog et doivent être restaurées !

(5) Afficher le fichier binlog nouvellement ajouté après la sauvegarde complète

[root@vm-002 ~]# cd /opt/backup/
[root@vm-002 backup]# ls
ops_2016-09-25.sql.gz
[root@vm-002 backup]# gzip -d ops_2016-09-25.sql.gz 
[root@vm-002 backup]# ls
ops_2016-09-25.sql
[root@vm-002 backup]# grep CHANGE ops_2016-09-25.sql 
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=106;

Il s'agit de l'emplacement du fichier binlog au moment de la sauvegarde complète

C'est-à-dire la ligne 106 de MySQL -bin.000002, donc c'est avant le fichier Les données du fichier binlog sont déjà incluses dans ce fichier sql complet

(6) Déplacez le fichier binlog et exportez-le en tant que fichier sql, et supprimez l'instruction drop

Vérifiez le répertoire de stockage de données de mysql. On peut voir à partir de ce qui suit qu'il se trouve sous /var/lib/mysql

[root@vm-002 backup]# ps -ef|grep mysql
root 9272 1 0 01:43 pts/1 00:00:00 /bin/sh /usr/bin/mysqld_safe 
--datadir=/var/lib/mysql --socket=/var/lib/mysql/mysql.sock --pid-file=/var/run/mysqld/mysqld.pid --basedir=/usr --user=mysql
mysql 9377 9272 0 01:43 pts/1 00:00:00 /usr/libexec/mysqld --basedir=/usr 
--datadir=/var/lib/mysql --user=mysql --log-error=/var/log/mysqld.log --pid-file=/var/run/mysqld/mysqld.pid --socket=/var/lib/mysql/mysql.sock
[root@vm-002 backup]# cd /var/lib/mysql/
[root@vm-002 mysql]# ls
ibdata1 ib_logfile0 ib_logfile1 mysql mysql-bin.000001 mysql-bin.000002 mysql-bin.index mysql.sock test
[root@vm-002 mysql]# cp mysql-bin.000002 /opt/backup/

.

Exportez le fichier binlog vers le fichier sql et vim Modifiez-le pour supprimer l'instruction drop

[root@vm-002 backup]# mysqlbinlog -d ops mysql-bin.000002 >002bin.sql
[root@vm-002 backup]# ls
002bin.sql mysql-bin.000002 ops_2016-09-25.sql
[root@vm-002 backup]# vim 002bin.sql #删除里面的drop语句

Remarque :

en récupération Le fichier binlog doit être déplacé avant de préparer complètement les données, sinon les instructions continueront d'être écrites dans le binlog pendant le processus de récupération, provoquant éventuellement une récupération incrémentielle. la partie données devient déroutante

(7) Restaurer les données

[root@vm-002 backup]# mysql -uroot -p < ops_2016-09-25.sql 
Enter password: 
[root@vm-002 backup]#

Vérifiez la base de données pour voir si la bibliothèque d'opérations est là

mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| ops |
| test |
+--------------------+
4 rows in set (0.00 sec)

mysql> use ops;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select * from customers;
+----+-----------+-----+
| id | name | age |
+----+-----------+-----+
| 1 | wangbo | 0 |
| 2 | guohui | 0 |
| 3 | zhangheng | 0 |
+----+-----------+-----+
3 rows in set (0.00 sec)

À ce moment, les données au moment de la sauvegarde complète sont restaurées

Ensuite, utilisez le fichier 002bin.sql pour restaurer les nouvelles données entre le moment de la sauvegarde complète et la suppression de la base de données. Cette partie des données a également été restaurée ! !

[root@vm-002 backup]# mysql -uroot -p ops <002bin.sql
Enter password: 
[root@vm-002 backup]#

Ce qui précède est un exemple de processus de récupération incrémentielle des données de la base de données MySQL !

mysql> select * from customers;
+----+-----------+-----+
| id | name | age |
+----+-----------+-----+
| 1 | wangbo | 24 |
| 2 | guohui | 22 |
| 3 | zhangheng | 27 |
| 4 | liupeng | 21 |
| 5 | xiaoda | 31 |
| 6 | fuaiai | 26 |
+----+-----------+-----+
6 rows in set (0.00 sec)

************************************** **** ********

Enfin, quelques points pour résumer :

1) Ce cas s'applique à la réparation des erreurs d'opération causées par des instructions SQL artificielles ou des situations de veille chaude sans réplication maître-esclave ni temps d'arrêt

2) La condition de récupération est que MySQL doit activer la fonction de journal binlog, et toutes les données doivent être entièrement préparées et incrémentielles

3) Pendant la récupération, il est recommandé d'arrêter les mises à jour externes, cela Autrement dit, il est interdit de mettre à jour la base de données

4) Restaurez d'abord le volume complet, puis restaurez les journaux incrémentiels après le temps de sauvegarde complète dans les fichiers SQL dans l'ordre, puis supprimez les instructions SQL problématiques dans le fichier (vous pouvez également utiliser des points d'heure et de localisation), puis restaurer dans la base de données.

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!