So verwenden Sie Binlog zur Datenwiederherstellung in MySQL

Vorwort

Kürzlich wurden Daten online falsch bearbeitet. Da die Datenbank direkt geändert wurde, ist die einzige Möglichkeit, sie wiederherzustellen, das MySQL-Binlog. Binlog verwendet den ROW-Modus, was bedeutet, dass für jeden betroffenen Datensatz eine SQL-Anweisung generiert wird. Außerdem wird das Projekt binlog2sql verwendet.

MySQL Binary Log, auch als Binärlog bekannt, ist eine binäre Logdatei, die durch Änderungen bei der MySQL-Ausführung generiert wird. Es hat zwei Hauptfunktionen:

* Datenantwort

* Master-Slave-Datenbank. Wird auf der Slave-Seite verwendet, um Hinzufügungen, Löschungen und Änderungen vorzunehmen und so die Synchronisierung mit dem Master aufrechtzuerhalten.

Grundlegende Konfiguration und Format von Binlog

Grundlegende Konfiguration von Binlog

Binlog muss im mysqld-Knoten der mysql-Konfigurationsdatei konfiguriert werden:

# Serverid im Log
Server-ID = 1
# Protokollpfad log_bin = /var/log/mysql/mysql-bin.log
# Wie viele Tage sollen Protokolle gespeichert werden? expire_logs_days = 10
# Die Größe jedes Binlogs max_binlog_size = 1000M
#binlgo-Modus binlog_format=ROW
# Die Standardeinstellung sind alle Datensätze. Sie können konfigurieren, welche aufgezeichnet werden müssen und welche nicht. #binlog_do_db = include_database_name
#binlog_ignore_db = Include-Datenbankname

Binärprotokollstatus anzeigen

• BINÄRPROTOKOLLE ANZEIGEN; Binärprotokolldateien anzeigen
• SHOW VARIABLES LIKE '%log_bin%' um den Log-Status anzuzeigen
• SHOW MASTER STATUS Anzeige des Speicherorts der Protokolldatei

Drei Binlog-Formate

1.REIHE

Bei der Zeilenprotokollierung wird für jede Zeilenänderung ein Datensatz generiert.

Vorteile: Die Kontextinformationen sind relativ vollständig. Bei der Wiederherstellung nach einem fehlerhaften Vorgang sind die ursprünglichen Informationen direkt im Protokoll zu finden. Es bietet gute Unterstützung für die Master-Slave-Replikation.

Nachteile: Die Ausgabe ist sehr groß und wenn es sich um eine Alter-Anweisung handelt, wird eine große Anzahl von Datensätzen generiert

Das Format ist wie folgt:

Löschen Sie aus "zurück" Geburtstag "=" 2017-05-05 00:00:00 "und" avatar "=" Mädchen.gif "und" Version "= 25 und" Passwort "= 'ecfadcde9305f8891bcfe5a1e28c253e' und" Salt "= '8PGBY' und` id "= 1 1 # ##-10 ##-10 #1 # # # #1 # # # # # # # # #1 # # # # # #1 # # # # #1 # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # ##-

2.ERKLÄRUNG

Bei SQL-Anweisungen erzeugt jede Anweisung einen Datensatz

Vorteile: Die Menge der generierten Protokolle ist relativ gering und die Master- und Slave-Versionen können inkonsistent sein

Nachteile: Einige Anweisungen in der Master-Slave-Beziehung können nicht unterstützt werden, wie z. B. automatisch inkrementierte Primärschlüssel und UUID.

Das Format ist wie folgt:

aus „sys_role“ löschen;

3.MISCHEN

Es kombiniert die Vorteile beider. Im Allgemeinen wird der STATEMENT-Modus verwendet und der ROW-Modus für nicht unterstützte Anweisungen.

Konvertieren in SQL

mysqlbinlog

Da binlog binär ist, muss es zuerst in eine Textdatei konvertiert werden. Im Allgemeinen können Sie mysqlbinlog, das mit MySQL geliefert wird, verwenden, um es in Text zu konvertieren.

mysqlbinlog --no-defaults --base64-output='decode-rows' -d room -v mysql-bin.011012 > /root/binlog_2018-10-10

Parameter Beschreibung

• --no-defaults Um den Fehler zu vermeiden: mysqlbinlog: unknown variable 'default_character_set=utf8mb4'
• --base64-output='decode-rows' wird zusammen mit -v verwendet, um eine Base64-Dekodierung durchzuführen. Es gibt viele andere Parameter, die verwendet werden, um den Bereich einzuschränken, wie Datenbank, Startzeit, Startposition usw. Diese Parameter sind bei der Fehlerbehebung sehr hilfreich.

Die grundlegenden Blöcke von Binlog sind wie folgt:

# bei 417750
#181007 1:50:38 Server-ID 1630000 end_log_pos 417844 CRC32 0x9fc3e3cd Abfrage Thread-ID = 440109962 Exec_time = 0 Fehlercode = 0
ZEITSTEMPEL EINSTELLEN=1538877038/*!*/;
BEGINNEN

1. # bei 417750

Gibt den Offset der aktuellen Position relativ zum Anfang der Datei an. Dies kann als Parameter --start-position im Befehl mysqlbinlog verwendet werden.

2. #181007 1:50:38 Server-ID 1630000 End-Log-Pos 417844 CRC32 0x9fc3e3cd Abfrage Thread-ID = 440109962 Exec-Zeit = 0 Fehlercode = 0

181007 1:50:38 bedeutet, dass es 1:50:38 am 7. Oktober 2018 ist. Die Server-ID ist die von Ihnen in der Konfigurationsdatei konfigurierte Uhrzeit. End_log_pos 417844 bedeutet, dass dieser Block bei 417844 endet. thread_id: Thread-ID der Ausführung, exec_time: Ausführungszeit, error_code: Fehlercode

3. SETZEN SIE TIMESTAMP=1538877038/!/;

BEGINNEN

Spezifische Ausführungsanweisung

Das durch eine Datensatzzeile generierte Protokoll sieht wie folgt aus

# bei 417750
#181010 9:50:38 Server-ID 1630000 end_log_pos 417844 CRC32 0x9fc3e3cd Abfrage Thread-ID = 440109962 Exec_time = 0 Fehlercode = 0
ZEITSTEMPEL FESTLEGEN=1539136238/*!*/;
BEGINNEN
/*!*/;
# bei 417844
#181010 9:50:38 Server-ID 1630000 end_log_pos 417930 CRC32 0xce36551b Table_map: `goods`.`good_info` zugeordnet zur Nummer 129411
# bei 417930
#181010 9:50:38 Server-ID 1630000 end_log_pos 418030 CRC32 0x5827674a Update_rows: Tabellen-ID 129411 Flags: STMT_END_F
### UPDATE `Waren`.`good_info`
###WO
### @1='2018:10:07' /* DATE meta=0 nullable=0 is_null=0 */
### @2=9033404 /* INT meta=0 nullable=0 is_null=0 */
### @3=1 /* INT meta=0 nullable=0 is_null=0 */
### @4=8691108 /* INT meta=0 nullable=0 is_null=0 */
### @5=9033404 /* INT meta=0 nullable=0 is_null=0 */
### @6=20 /* LONGINT meta=0 nullable=0 is_null=0 */
### @7=1538877024 /* TIMESTAMP(0) meta=0 nullable=0 is_null=0 */
### SATZ
### @1='2018:10:07' /* DATUM meta=0 nullable=0 is_null=0 */
### @2=9033404 /* INT meta=0 nullable=0 is_null=0 */
### @3=1 /* INT meta=0 nullable=0 is_null=0 */
### @4=8691108 /* INT meta=0 nullable=0 is_null=0 */
### @5=9033404 /* INT meta=0 nullable=0 is_null=0 */
### @6=21 /* LONGINT meta=0 nullable=0 is_null=0 */
### @7=1538877024 /* TIMESTAMP(0) meta=0 nullable=0 is_null=0 */
# bei 418030
#181010 9:50:38 Server-ID 1630000 end_log_pos 418061 CRC32 0x468fb30e Xid = 212760460521
BEGEHEN /*!*/;
# bei 418061

Oben wird das von einer Datensatzzeile generierte Protokoll angezeigt. Beginnen Sie mit SET TIMESTAMP=1539136238/*!*/; und enden Sie mit COMMIT/*!*/; Wir können die Reichweite anhand der von den beiden angegebenen Standorte einschränken.

Beachten Sie die Nummer bei 417750 vor dem SET TIMESTAMP am Anfang eines Datensatzes und die Nummer bei 418061 nach dem COMMIT am Ende.

Verwenden von binlog2sql

Einführung in die offizielle Website von binlog2sql: Analysieren Sie das gewünschte SQL aus MySQL Binlog. Abhängig von den Optionen können Sie das ursprüngliche SQL, Rollback-SQL, INSERT-SQL ohne Primärschlüssel usw. erhalten.

Die grundlegende Verwendung ist wie folgt:

python binlog2sql.py -hlocalhost -P3306 -udev -p'\*' -d Raum -t Rauminfo --start-file='mysql-bin.011012' --start-position 129886892 --stop-position 130917280 > rollback.sql

Ich werde die spezifische Verwendung nicht erläutern, da sie auf GitHub sehr klar erklärt wird. Sie können sich hauptsächlich die vielen Bedingungen ansehen, die zum Filtern verwendet werden, z. B. Start- und Endzeit --start-datetime/--stop-datetime, Tabellennamenbeschränkung -t, Datenbankbeschränkung -d, Anweisungsbeschränkung --sql-type. Ich werde hauptsächlich über einige Probleme sprechen, auf die ich gestoßen bin.

MySQL-Binlog-Modus

Es muss hier auf ROW eingestellt werden, da der ROW-Modus die Originalinformationen enthält. Wenn Sie binlog2sql direkt verwenden können, um das Rollback-SQL umgekehrt zu generieren, müssen Sie, wenn STATEMENT nicht generiert werden kann, die geplante MySQL-Sicherungsdatei verwenden, um das Rollback durchzuführen.

Spezifische Vorgänge zum Wiederherstellen von Daten

Denn was damals online ausgeführt wurde, war eine Update-Anweisung und es gab keinen eindeutigen Schlüsselindex. Dadurch wurden über zweitausend Datensätze aktualisiert. Die Anweisung lautet wie folgt:

Aktualisiere Room_Info und setze Status=1, wobei Status=2;

• Suchen Sie zunächst die entsprechende Binlog-Datei entsprechend der Betriebszeit. Ich erinnere mich, dass die Betriebszeit etwa 9 Uhr morgens war. Suchen Sie daher die entsprechende Binlog-Datei, deren letzte Änderungszeit größer als 9 Uhr und die nächstgelegene Zeit war. Verwenden Sie den Linux-Befehl ll, um die Änderungszeit der Datei anzuzeigen.
• Bestimmte Datenbanken filtern Da sich alle Binärlogdateien einer MySQL-Instanz in einer Datei befinden, müssen wir zuerst andere Datenbanken entfernen, die wir nicht schließen möchten. Verwenden Sie den Parameter -d, um die Dateninstanz anzugeben. Verwenden Sie dann die Startzeit (--start-datetime) und Endzeit (--stop-datetime), um weiter zu filtern
  

mysqlbinlog --no-defaults -v --base64-output='decode-rows' -d Raum --start-datetime='2018-10-10 9:00:00' --stop-datetime='2018-10-10 10:00:00' mysql-bin.011012>temp.sql

• Analyse komprimierter Abrufdateien

zip temp.zip temp.sql && sz temp.zip

Rufen Sie die Datei ab und analysieren Sie sie lokal mit einem Texttool wie vscode, das über reguläre Ausdrücke verfügt. Basierend auf den von Ihnen geänderten Funktionen habe ich beispielsweise eine Zimmernummer 888888, die nicht geändert werden sollte. Sie können den Änderungsdatensatz dieser Zimmernummer überprüfen. Die Anweisung im ROW-Modus lautet „Where first and set second“. Mit dem regulären Ausdruck room_id=888888.*show_state=1.*AND show_state=2 ist eine schnelle Zuordnung möglich. Meine damalige Anweisung betraf über 2.000 Datensätze. Suchen Sie anhand der von Ihnen gefundenen Anweisung das at vor dem beginnenden SET TIMESTAMP=1539136238 und das at nach dem abschließenden COMMIT.

• Generieren Sie Rollback-Anweisungen mit binlog2sql

python binlog2sql.py -hlocalhost -P3306 -udev -p'*' -d Raum -t Rauminfo -B --start-file='mysql-bin.011012' --start-position 129886892 --stop-position 130917280 > rollback.sql

Zusätzlich

Denn hier habe ich eine Situation, in der ein Update mehrere Datensätze betrifft. Wenn es sich um einen eindeutigen Schlüssel handelt, ist nur ein Datensatz betroffen. Es besteht kein Grund, sich so viel Mühe zu machen. Verwenden Sie einfach binlog2sql mit den Parametern -d und -t, um die Datenbank und die Tabelle einzuschränken, und verwenden Sie dann grep zum Suchen, und Sie können das entsprechende SQL direkt abrufen. In mysqlbinlog fehlt die Funktion zur Begrenzung von Tabellen und zur Begrenzung von Anweisungen. Beispielsweise kann durch eine tabellengenaue Delete-Anweisung eine große Datenmenge reduziert und schnell gefunden werden.

Zusammenfassen

Das Obige ist der vollständige Inhalt dieses Artikels. Ich hoffe, dass der Inhalt dieses Artikels einen gewissen Lernwert für Ihr Studium oder Ihre Arbeit hat. Wenn Sie Fragen haben, können Sie eine Nachricht hinterlassen. Vielen Dank für Ihre Unterstützung von 123WORDPRESS.COM.