MySQL verwendet mysqldump + binlog, um die Prinzipanalyse der gelöschten Datenbank vollständig wiederherzustellen

1. Übersicht

Beim täglichen Betrieb und bei der Wartung von MySQL-Datenbanken können Benutzer versehentlich Daten löschen. Zu den häufigsten Vorgängen, bei denen Daten versehentlich gelöscht werden, gehören:

• Der Benutzer führt den Löschvorgang aus und löscht Daten, die aufgrund falscher Bedingungen nicht gelöscht werden sollten (DML-Operation).
• Der Benutzer führt die Aktualisierung aus, die Aktualisierungsdaten schlagen jedoch aufgrund falscher Bedingungen fehl (DML-Vorgang).
• Der Benutzer löscht versehentlich die Drop-Table-Tabelle (DDL-Vorgang).
• Der Benutzer leert versehentlich die Tabelle (Truncate) (DDL-Operation).
• Benutzer löscht Datenbank, lässt Datenbank fallen und läuft weg (DDL-Vorgang)
• …Warten

Obwohl diese Situationen nicht oft vorkommen, müssen wir in der Lage sein, sie zu beheben, wenn sie doch einmal auftreten. Im Folgenden wird beschrieben, wie das geht.

(II) Wiederherstellungsprinzip

Wenn Sie die Datenbank auf den Zustand vor dem Fehler wiederherstellen möchten, benötigen Sie eine vollständige Sicherung der Datenbank und aller nach der vollständigen Sicherung generierten Binärprotokolle.

Vollständige Sicherungsfunktion: Verwenden Sie die vollständige Sicherung, um die Datenbank am Speicherort der letzten vollständigen Sicherung wiederherzustellen.

Funktion des Binärprotokolls: Nachdem Sie die Datenbank mithilfe eines vollständigen Sicherungssatzes am Speicherort der letzten vollständigen Sicherung wiederhergestellt haben, müssen Sie alle Aktionen wiederholen, die nach der letzten vollständigen Sicherung in der Datenbank aufgetreten sind. Der Wiederholungsprozess besteht darin, die Binärprotokolldatei in SQL-Anweisungen zu analysieren und diese dann zur erneuten Ausführung in die Datenbank einzufügen.

Beispiel: Xiao Ming hat am 1. April um 20:00 Uhr mit mysqldump eine Datenbanksicherung erstellt. Am 2. April um 0:00 Uhr hat Xiao Hua die Datenbank versehentlich gelöscht. Wenn Sie dann eine Datenbankwiederherstellung durchführen, müssen Sie die vollständige Sicherung verwenden, die am Abend des 1. April erstellt wurde, um die Datenbank auf „1. April um 20:00 Uhr“ wiederherzustellen. Wie können Sie die Daten nach 20:00 Uhr am 1. April und vor 0:00 Uhr am 2. April wiederherstellen? Sie müssen das Binärprotokoll analysieren, um das während dieses Zeitraums ausgeführte SQL zu wiederholen.

(III) Test zur Wiederherstellung gelöschter Datenbanken

(3.1) Versuchszweck

In diesem Experiment habe ich das Löschen der Datenbank direkt getestet und „drop database lijiamandb“ ausgeführt, um zu bestätigen, ob sie wiederhergestellt werden kann.

(3.2) Testverfahren

Erstellen Sie die Testtabellen test01 und test02 in der Testdatenbank lijiamandb, führen Sie dann mysqldump aus, um die Datenbank vollständig vorzubereiten, und führen Sie anschließend drop database aus, um zu bestätigen, ob die Datenbank wiederhergestellt werden kann.

SCHRITT 1: Testdaten erstellen. Um die geschäftige tägliche Produktionsumgebung zu simulieren, erzeugen häufige Datenbankoperationen eine große Menge an Binärprotokollen. Ich verwende speziell gespeicherte Prozeduren und EVENTS, um eine große Menge an Daten zu erzeugen.

Erstellen Sie eine Testtabelle:

benutze lijiamandb;erstelle Tabelle test01
 (
 id1 int nicht null auto_increment,
 Name varchar(30),
 Primärschlüssel (id1)
 );

Tabelle erstellen test02
 (
 id2 int nicht null auto_increment,
 Name varchar(30),
 Primärschlüssel (id2)
 );

Erstellen Sie eine gespeicherte Prozedur, um Daten in die Testtabelle einzufügen. Bei jeder Ausführung der gespeicherten Prozedur werden 10.000 Datensätze in test01 bzw. test02 eingefügt:

CREATE DEFINER=`root`@`%` PROZEDURE `p_insert`()
BEGINNEN
#Routinekörper kommt hier hin...
DECLARE str1 varchar(30);
DECLARE str2 varchar(30);
DECLARE ich int;
setze i = 0;

während ich < 10000
 setze str1 = Teilzeichenfolge (md5 (rand ()), 1,25);
 in test01(Name) Werte(str1) einfügen;
 setze str2 = Teilzeichenfolge (md5 (rand ()), 1,25);
 in test02(Name) Werte(str1) einfügen;
 setze i = i + 1;
 Ende während;
 ENDE

Erstellen Sie ein Ereignis, um die oben gespeicherte Prozedur alle 10 Sekunden auszuführen:

verwenden Sie lijiamandb;
 Ereignis erstellen, falls nicht vorhanden e_insert
 planmäßig alle 10 Sekunden
 nach Fertigstellung bewahren
 rufen Sie p_insert() auf;

EVENT starten und automatisch alle 10 Sekunden 10.000 Datensätze in test01 und test02 einfügen

mysql> Variablen wie „%event_scheduler%“ anzeigen;
+----------------------------------------------------------+----------+
| Variablenname | Wert |
+----------------------------------------------------------+----------+
| event_scheduler | AUS |
+----------------------------------------------------------+----------+

mysql> setze globalen event_scheduler = ein;
 Abfrage OK, 0 Zeilen betroffen (0,08 Sek.)

--Nach 3 Minuten. . .
SCHRITT 2: Nachdem Sie im ersten Schritt eine große Menge an Testdaten generiert haben, führen Sie mit mysqldump eine vollständige Sicherung der lijiamandb-Datenbank durch
mysqldump -h192.168.10.11 -uroot -p123456 -P3306 --single-transaction --master-data=2 --events --routines --databases lijiamandb > /mysql/backup/lijiamandb.sql

Hinweis: Sie müssen --master-data=2 hinzufügen, um den Endpunkt des mysqldump-Backups im Backup-Set festzulegen.

--Nach 3 Minuten. . .

SCHRITT 3: Um die Konsistenzprüfung der Daten vor und nach dem Löschen der Datenbank zu erleichtern, beenden Sie zunächst das Einfügen von Daten in die Tabelle. Zu diesem Zeitpunkt enthalten sowohl test01 als auch test02 930.000 Datenzeilen. Wir müssen auch sicherstellen, dass bei der nachfolgenden Wiederherstellung 930.000 Datenzeilen vorhanden sind.

mysql> setze globalen event_scheduler = aus;
Abfrage OK, 0 Zeilen betroffen (0,00 Sek.)

mysql> wähle count(*) aus test01;
 +----------+
 | Anzahl(*) |
 +----------+
 | 930000 |
 +----------+
Zeile im Satz (0,14 Sek.)

mysql> wähle count(*) aus test02;
 +----------+
 | Anzahl(*) |
 +----------+
 | 930000 |
 +----------+
Zeile im Satz (0,13 Sek.)

SCHRITT 4: Löschen Sie die Datenbank

mysql> Datenbank lijiamandb löschen;
Abfrage OK, 2 Zeilen betroffen (0,07 Sek.)

SCHRITT 5: Vollständiger Import mit mysqldump

mysql> Datenbank lijiamandb erstellen;
Abfrage OK, 1 Zeile betroffen (0,01 Sek.)

mysql> beenden
 Tschüss
 [root@masterdb binlog]# mysql -uroot -p123456 lijiamandb < /mysql/backup/lijiamandb.sql 
 mysql: [Warnung] Die Verwendung eines Passworts in der Befehlszeilenschnittstelle kann unsicher sein.

Nach der Durchführung einer vollständigen Sicherung und Wiederherstellung stellte sich heraus, dass nur 753.238 Datensätze vorhanden waren:

[root@masterdb binlog]# mysql -uroot -p123456 lijiamandb 

mysql> wähle count(*) aus test01;
 +----------+
 | Anzahl(*) |
 +----------+
 |753238|
 +----------+
Zeile im Satz (0,12 Sek.)

mysql> wähle count(*) aus test02;
 +----------+
 | Anzahl(*) |
 +----------+
 |753238|
 +----------+
Zeile im Satz (0,11 Sek.)

Nach dem vollständigen Import sind die Daten offensichtlich unvollständig. Verwenden Sie als Nächstes mysqlbinlog, um eine inkrementelle Wiederherstellung des Binärprotokolls durchzuführen.

Das Wichtigste bei der Verwendung von mysqlbinlog für die inkrementelle Protokollwiederherstellung besteht darin, die Startposition (Startposition) und die Endposition (Stoppposition) zu bestimmen, die wiederhergestellt werden sollen. Die Startposition (Startposition) ist die Position, nachdem wir alles ausgeführt haben, und die Endposition ist die Position, bevor der Fehler auftritt.
SCHRITT 6: Bestätigen Sie den endgültigen Speicherort des mysqldump-Backups

[root@masterdb-Backup]# cat lijiamandb.sql |grep "CHANGE MASTER"
-- ÄNDERN SIE MASTER IN MASTER_LOG_FILE='master-bin.000044', MASTER_LOG_POS=8526828

Wenn die Sicherung an Position 8526828 des Protokolls Nr. 44 erfolgen soll, kann der Startpunkt der Wiederherstellung auf 8526828 des Protokolls Nr. 44 festgelegt werden.

--Bestätigen Sie als Nächstes den wiederherzustellenden Endpunkt, d. h. die Position vor der Ausführung von „DROP DATABASE LIJIAMAN“. Sie müssen dies im Binlog bestätigen.

[root@masterdb binlog]# ls
 master-bin.000001 master-bin.000010 master-bin.000019 master-bin.000028 master-bin.000037 master-bin.000046 master-bin.000055
 master-bin.000002 master-bin.000011 master-bin.000020 master-bin.000029 master-bin.000038 master-bin.000047 master-bin.000056
 master-bin.000003 master-bin.000012 master-bin.000021 master-bin.000030 master-bin.000039 master-bin.000048 master-bin.000057
 master-bin.000004 master-bin.000013 master-bin.000022 master-bin.000031 master-bin.000040 master-bin.000049 master-bin.000058
 master-bin.000005 master-bin.000014 master-bin.000023 master-bin.000032 master-bin.000041 master-bin.000050 master-bin.000059
 master-bin.000006 master-bin.000015 master-bin.000024 master-bin.000033 master-bin.000042 master-bin.000051 master-bin.index
 master-bin.000007 master-bin.000016 master-bin.000025 master-bin.000034 master-bin.000043 master-bin.000052
 master-bin.000008 master-bin.000017 master-bin.000026 master-bin.000035 master-bin.000044 master-bin.000053
 master-bin.000009 master-bin.000018 master-bin.000027 master-bin.000036 master-bin.000045 master-bin.000054

# Nach vielen Suchen wird festgestellt, dass sich die Drop-Datenbank in der Protokolldatei Nr. 54 befindet [root@masterdb binlog]# mysqlbinlog -v master-bin.000056 | grep -i "drop database lijiamandb"
 [root@masterdb binlog]# mysqlbinlog -v master-bin.000055 | grep -i "Datenbank lijiamandb löschen"
 [root@masterdb binlog]# mysqlbinlog -v master-bin.000055 | grep -i "Datenbank lijiamandb löschen"
 [root@masterdb binlog]# mysqlbinlog -v master-bin.000054 | grep -i "Datenbank lijiamandb löschen"
Datenbank lijiamandb löschen

# Zur einfachen Suche als Text speichern [root@masterdb binlog]# mysqlbinlog -v master-bin.000054 > master-bin.txt


# Bestätigen Sie, dass der Speicherort vor dem Löschen der Datenbank lautet: 9019487 der Datei 54
 # bei 9019422
 #200423 16:07:46 Server-ID 11 end_log_pos 9019487 CRC32 0x86f13148 Anonymous_GTID last_committed=30266 sequence_number=30267 rbr_only=no
 SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
 # bei 9019487
 #200423 16:07:46 Server-ID 11 end_log_pos 9019597 CRC32 0xbd6ea5dd Abfrage Thread-ID = 100 Exec_Time = 0 Fehlercode = 0
 ZEITSTEMPEL EINSTELLEN=1587629266/*!*/;
 SET @@session.sql_auto_is_null=0/*!*/;
 /*!\C utf8 *//*!*/;
 SETZEN @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
 Datenbank lijiamandb löschen
 /*!*/;
 # bei 9019597
 #200423 16:09:25 Server-ID 11 end_log_pos 9019662 CRC32 0x8f7b11dc Anonymous_GTID last_committed=30267 sequence_number=30268 rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
 # bei 9019662
 #200423 16:09:25 Server-ID 11 end_log_pos 9019774 CRC32 0x9b42423d Abfrage Thread_ID=100 Exec_Time=0 Fehlercode=0
 ZEITSTEMPEL FESTLEGEN=1587629365/*!*/;
 Datenbank lijiamandb erstellen

SCHRITT 7: Bestimmen Sie die Start- und Endpunkte und starten Sie die inkrementelle Wiederherstellung: 8526828 von Protokoll Nr. 44
Ende: Datei 54 9019487

Dies wird zur Ausführung in drei Befehle aufgeteilt. Die Startprotokolldatei beinhaltet den Startpositionsparameter und wird separat ausgeführt. Die Stoppprotokolldatei beinhaltet den Stopppositionsparameter und wird separat ausgeführt. Die Zwischenprotokolldateien beinhalten keine speziellen Parameter und werden alle zusammen ausgeführt.

# Logdatei starten

# Protokolldatei wird gestartet mysqlbinlog --start-position=8526828 /mysql/binlog/master-bin.000044 | mysql -uroot -p123456

 
# Zwischenprotokolldatei mysqlbinlog /mysql/binlog/master-bin.000045 /mysql/binlog/master-bin.000046 /mysql/binlog/master-bin.000047 /mysql/binlog/master-bin.000048 /mysql/binlog/master-bin.000049 /mysql/binlog/master-bin.000050 /mysql/binlog/master-bin.000051 /mysql/binlog/master-bin.000052 /mysql/binlog/master-bin.000053 | mysql -uroot -p123456

 
# Beenden Sie die Protokolldatei mysqlbinlog --stop-position=9019487 /mysql/binlog/master-bin.000054 | mysql -uroot -p123456

SCHRITT 8: Die Wiederherstellung ist abgeschlossen. Bestätigen Sie, dass alle Daten wiederhergestellt wurden.

[root@masterdb binlog]# mysql -uroot -p123456 lijiamandb
mysql> wähle count(*) aus test01;
+----------+
| Anzahl(*) |
+----------+
| 930000 |
+----------+
Zeile im Satz (0,15 Sek.)

mysql> wähle count(*) aus test02;
+----------+
 | Anzahl(*) |
+----------+
 | 930000 |
+----------+
Zeile im Satz (0,13 Sek.)

4. Fazit

1. Bei DML-Operationen zeichnet Binlog alle DML-Datenänderungen auf:
--Beim Einfügen zeichnet Binlog die Zeilendaten des Einfügens auf
--Beim Update zeichnet Binlog die Zeilendaten vor und nach der Änderung auf.
--Beim Löschen zeichnet Binlog die Daten vor dem Löschen auf. Wenn der Benutzer versehentlich einen DML-Vorgang ausführt, kann mysqlbinlog verwendet werden, um die Datenbank auf den Punkt vor dem Fehler wiederherzustellen.

2. Bei DDL-Operationen zeichnet Binlog nur das Benutzerverhalten auf, keine Zeilenänderungen. Dies hat jedoch keinen Einfluss auf unsere Fähigkeit, die Datenbank auf den Punkt vor dem Fehler wiederherzustellen.

Kurz gesagt: Mit der vollständigen Sicherung von mysqldump und dem Binlog-Protokoll können Sie Daten zu jedem beliebigen Zeitpunkt vor dem Fehler wiederherstellen.

Dies ist das Ende dieses Artikels über die Verwendung von mysqldump+binlog zur vollständigen Wiederherstellung einer gelöschten Datenbank in MySQL. Weitere Informationen zur Wiederherstellung einer gelöschten Datenbank in MySQL finden Sie in früheren Artikeln auf 123WORDPRESS.COM oder in den folgenden verwandten Artikeln. Ich hoffe, Sie werden 123WORDPRESS.COM auch in Zukunft unterstützen!