Anweisungen zum Wiederherstellen von Daten nach versehentlichem Löschen einer MySQL-Datenbank

Bei täglichen Betriebs- und Wartungsarbeiten ist die Sicherung der MySQL-Datenbank von entscheidender Bedeutung! Die Bedeutung der Datenbank für die Website erfordert von uns eine fehlerfreie Verwaltung der MySQL-Daten!
Dann ist es unvermeidlich, dass Menschen Fehler machen. Was passiert, wenn eines Tages ein Kurzschluss im Gehirn auftritt und die Datenbank versehentlich gelöscht wird? Was sollen wir tun? ? ?

Als Nächstes erläutern wir den Wiederherstellungsplan, nachdem die MySQL-Datenbank versehentlich gelöscht wurde.

1. Arbeitsszenario

(1) Die MySQL-Datenbank wird jede Nacht um 12:00 Uhr automatisch vollständig gesichert.
(2) Eines Morgens bei der Arbeit wurde ein Kollege um 9 Uhr ohnmächtig und ließ eine Datenbank fallen!
(3) Dringende Genesung erforderlich! Die gesicherten Datendateien und inkrementellen Binärprotokolldateien können zur Datenwiederherstellung verwendet werden.

2. Ideen zur Datenwiederherstellung

(1) Verwenden Sie die in der vollständigen SQL-Datei, der Binärprotokolldatei und ihren Positionsinformationen aufgezeichnete Anweisung CHANGE MASTER, um den inkrementellen Teil in der Binärprotokolldatei zu finden.
(2) Verwenden Sie den Befehl mysqlbinlog, um die obige Binärlog-Datei in eine SQL-Datei zu exportieren und die Drop-Anweisung zu entfernen .
(3) Durch den Export der SQL-Datei der vollständigen Sicherungsdatei und der inkrementellen Binärlog-Datei können die vollständigen Daten wiederhergestellt werden.

3. Beispiel

----------------------------------------
Stellen Sie zunächst sicher, dass MySQL Binlog aktiviert hat.
Fügen Sie im Abschnitt [mysqld] der Datei /etc/my.cnf Folgendes hinzu:
log-bin=mysql-bin
Starten Sie dann den MySQL-Dienst neu
----------------------------------------

(1) Erstellen Sie eine Tabelle Kunden unter der Ops-Datenbank

mysql> Ops verwenden;
mysql> Tabelle Kunden erstellen(
-> id int ungleich null auto_increment,
-> Name char(20) nicht null,
-> Alter int nicht null,
-> Primärschlüssel (ID)
->)engine=InnoDB;
Abfrage OK, 0 Zeilen betroffen (0,09 Sek.)

mysql> Tabellen anzeigen;
+-----------------+
| Tabellen_in_Ops |
+-----------------+
| Kunden |
+-----------------+
1 Zeile im Satz (0,00 Sek.)

mysql> desc Kunden;
+-------+----------+------+-----+---------+----------------+
| Feld | Typ | Null | Schlüssel | Standard | Extra |
+-------+----------+------+-----+---------+----------------+
| id | int(11) | NEIN | PRI | NULL | auto_increment |
| Name | char(20) | NEIN | | NULL | |
| Alter | int(11) | NEIN | | NULL | |
+-------+----------+------+-----+---------+----------------+
3 Reihen im Satz (0,02 Sek.)

mysql> in Kundenwerte einfügen (1, „wangbo“, „24“);
Abfrage OK, 1 Zeile betroffen (0,06 Sek.)

mysql> in Kundenwerte einfügen (2, „guohui“, „22“);
Abfrage OK, 1 Zeile betroffen (0,06 Sek.)

mysql> in Kundenwerte einfügen (3, „zhangheng“, „27“);
Abfrage OK, 1 Zeile betroffen (0,09 Sek.)

mysql> wähle * aus Kunden;
+----+-----------+-----+
| ID | Name | Alter |
+----+-----------+-----+
| 1 | wangbo | 24 |
| 2 | guohui | 22 |
| 3 | zhangheng | 27 |
+----+-----------+-----+
3 Zeilen im Satz (0,00 Sek.)

(2) Jetzt Voll-Backup durchführen

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

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

Parameterbeschreibung:

-B: Datenbank angeben
-F: Protokoll aktualisieren
-R: Sicherungsspeichervorgang usw.
-x: Tabelle sperren
--master-data: Fügen Sie der Sicherungsanweisung die Anweisung CHANGE MASTER sowie die Binärprotokolldatei und Standortinformationen hinzu
-----------------

(3) Daten erneut eingeben

mysql> in Kundenwerte einfügen (4, „liupeng“, „21“);
Abfrage OK, 1 Zeile betroffen (0,06 Sek.)

mysql> in Kundenwerte einfügen (5, „xiaoda“, „31“);
Abfrage OK, 1 Zeile betroffen (0,07 Sek.)

mysql> in Kundenwerte einfügen (6, „fuaiai“, „26“);
Abfrage OK, 1 Zeile betroffen (0,06 Sek.)

mysql> wähle * aus Kunden;
+----+-----------+-----+
| ID | Name | Alter |
+----+-----------+-----+
| 1 | wangbo | 24 |
| 2 | guohui | 22 |
| 3 | zhangheng | 27 |
| 4 | liupeng | 21 |
| 5 | xiaoda | 31 |
| 6 | fuaiai | 26 |
+----+-----------+-----+
6 Zeilen im Satz (0,00 Sek.)

(4) Die Testdatenbank wurde versehentlich gelöscht.

mysql> Datenbankoperationen löschen;
Abfrage OK, 1 Zeile betroffen (0,04 Sek.)

Zu diesem Zeitpunkt, zwischen dem Zeitpunkt der vollständigen Sicherung und dem Zeitpunkt des Fehlervorgangs, befinden sich die vom Benutzer geschriebenen Daten im Binärprotokoll und müssen wiederhergestellt werden!

(5) Zeigen Sie nach der vollständigen Sicherung die neu hinzugefügten Binlog-Dateien an

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

Dies ist der Speicherort der Binlog-Datei zum Zeitpunkt der vollständigen Vorbereitung, d. h. Zeile 106 von mysql-bin.000002. Daher sind die Daten in den Binlog-Dateien vor dieser Datei bereits in dieser vollständigen SQL-Datei enthalten.

(6) Verschieben Sie die Binlog-Datei und exportieren Sie sie als SQL-Datei. Entfernen Sie dabei die Drop-Anweisung.

Überprüfen Sie das MySQL-Datenspeicherverzeichnis. Sie können sehen, dass es sich in /var/lib/mysql befindet.

[root@vm-002 Sicherung]# 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 Punkte/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 Sicherung]# 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/

Exportieren Sie die Binlog-Datei in eine SQL-Datei und bearbeiten Sie sie mit Vim, um die Drop-Anweisung zu löschen.

[root@vm-002 Sicherung]# mysqlbinlog -d ops mysql-bin.000002 >002bin.sql
[root@vm-002 Sicherung]# ls
002bin.sql mysql-bin.000002 ops_2016-09-25.sql
[root@vm-002 Backup]# vim 002bin.sql #Löschen Sie die Drop-Anweisung darin

Beachten:

Vor der Wiederherstellung der vollständigen Backup-Daten muss die Binlog-Datei gelöscht werden. Andernfalls werden während des Wiederherstellungsvorgangs weiterhin Anweisungen in das Binlog geschrieben, was letztendlich dazu führt, dass die inkrementellen Wiederherstellungsdaten unübersichtlich werden.

(7) Daten wiederherstellen

[root@vm-002 Sicherung]# mysql -uroot -p < ops_2016-09-25.sql
Passwort eingeben:
[root@vm-002 Sicherung]#

Überprüfen Sie die Datenbank, um festzustellen, ob die Ops-Bibliothek vorhanden ist

mysql> Datenbanken anzeigen;
+--------------------+
| Datenbank |
+--------------------+
| Informationsschema |
|mysql |
| Betrieb |
| Prüfung |
+--------------------+
4 Zeilen im Satz (0,00 Sek.)

mysql> Ops verwenden;
Einlesen von Tabelleninformationen zur Vervollständigung von Tabellen- und Spaltennamen
Sie können diese Funktion deaktivieren, um einen schnelleren Start mit -A zu erhalten

Datenbank geändert
mysql> wähle * aus Kunden;
+----+-----------+-----+
| ID | Name | Alter |
+----+-----------+-----+
| 1 | wangbo | 0 |
| 2 | guohui | 0 |
| 3 | zhangheng | 0 |
+----+-----------+-----+
3 Zeilen im Satz (0,00 Sek.)

Zu diesem Zeitpunkt wurden die Daten zum Zeitpunkt der vollständigen Wiederherstellung wiederhergestellt

Verwenden Sie als Nächstes die Datei 002bin.sql, um die neu hinzugefügten Daten zwischen dem Zeitpunkt der vollständigen Vorbereitung und dem Zeitpunkt des Löschens der Datenbank wiederherzustellen.

[root@vm-002 Sicherung]# mysql -uroot -p ops <002bin.sql
Passwort eingeben:
[root@vm-002 Sicherung]#

Bei einer erneuten Überprüfung der Datenbank stellte ich fest, dass die Daten zwischen der vollständigen Sicherung und der Löschung der Datenbank ebenfalls wiederhergestellt wurden! !

mysql> wähle * aus Kunden;
+----+-----------+-----+
| ID | Name | Alter |
+----+-----------+-----+
| 1 | wangbo | 24 |
| 2 | guohui | 22 |
| 3 | zhangheng | 27 |
| 4 | liupeng | 21 |
| 5 | xiaoda | 31 |
| 6 | fuaiai | 26 |
+----+-----------+-----+
6 Zeilen im Satz (0,00 Sek.)

Oben sehen Sie einen Beispielprozess für die inkrementelle Datenwiederherstellung einer MySQL-Datenbank!

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

Lassen Sie uns abschließend einige Punkte zusammenfassen:

1) Dieser Fall ist auf die Behebung von Ausfallzeiten anwendbar, die durch fehlerhafte Vorgänge aufgrund menschlicher SQL-Anweisungen verursacht wurden, oder wenn kein Hot-Standby wie beispielsweise eine Master-Slave-Replikation vorhanden ist.

2) Die Wiederherstellungsbedingung besteht darin, dass MySQL die Binlog-Funktion aktivieren muss und alle Daten in vollständiger und inkrementeller Form gesichert werden müssen.

3) Beim Wiederherstellen wird empfohlen, externe Updates zu stoppen, d. h. die Aktualisierung der Datenbank zu verbieten

4) Stellen Sie zuerst den gesamten Betrag wieder her, stellen Sie dann die inkrementellen Protokolle nach dem Zeitpunkt der vollständigen Sicherung nacheinander in SQL-Dateien wieder her, löschen Sie anschließend die problematischen SQL-Anweisungen in den Dateien (Sie können sie auch nach Zeit und Ort wiederherstellen) und stellen Sie sie anschließend in der Datenbank wieder her.

Die obigen Anweisungen zur Datenwiederherstellung nach versehentlichem Löschen einer MySQL-Datenbank sind alle Inhalte, die der Herausgeber mit Ihnen teilt. Ich hoffe, sie können Ihnen als Referenz dienen. Ich hoffe auch, dass Sie 123WORDPRESS.COM unterstützen werden.