MySQL stellt Daten über Binlog wieder her

MySQL-Protokolldateien

Jede ausgereifte Software verfügt über ein ausgereiftes Protokollierungssystem. Wenn in der Software Probleme auftreten, sind diese Protokolle eine Fundgrube für die Suche nach der Problemquelle. MySQL stellt da keine Ausnahme dar. Es wird eine Reihe von Protokollen geben, die den Ausführungsstatus von MySQL aufzeichnen.

MySQL verfügt hauptsächlich über die folgenden Protokolle:

• Fehlerprotokoll: zeichnet Fehlerinformationen während des MySQL-Vorgangs auf
• Allgemeines Abfrageprotokoll: zeichnet die Anweisungen auf, die MySQL ausführt, einschließlich jeder SQL-Anweisung einer Abfrage, Änderung, Aktualisierung usw.
• Protokoll langsamer Abfragen: zeichnet die SQL-Anweisungen auf, deren Abfrage viel Zeit in Anspruch nimmt
• Binlog-Protokoll: zeichnet Datenänderungsprotokolle auf, einschließlich Tabellenerstellung, Datenaktualisierung usw.
  

Diese Protokolle müssen in der Datei my.cnf konfiguriert werden. Wenn Sie den Pfad der MySQL-Konfigurationsdatei nicht kennen, können Sie ihn mit dem Befehl mysql finden.

mysql --verbose --help|grep -A 1 „Standardoptionen“ #Dieser Befehl listet die Pfade der Reihe nach auf, die my.cnf durchsucht.

binlog

Binlog ist eine binäre Protokolldatei, die alle Datenbankaktualisierungsanweisungen aufzeichnet, einschließlich Tabellenaktualisierungen und Datensatzaktualisierungen, nämlich Datenmanipulationssprache (DML). Binlog wird hauptsächlich zur Datenwiederherstellung und Konfiguration der Master-Slave-Replikation verwendet.

Datenwiederherstellung: Wenn die Datenbank versehentlich gelöscht wird oder etwas Unaussprechliches passiert, können die Daten über Binlog zu einem bestimmten Zeitpunkt wiederhergestellt werden. Master-Slave-Replikation: Wenn eine Datenbank aktualisiert wird, zeichnet die Master-Datenbank dies über Binlog auf und benachrichtigt die Slave-Datenbank über die Aktualisierung. Auf diese Weise wird die Konsistenz der Master- und Slave-Datenbankdaten sichergestellt.

MySQL ist entsprechend seiner Funktionen in Serviceschichtmodule und Speicher-Engine-Schichtmodule unterteilt. Die Serviceschicht ist für Vorgänge wie Clientverbindung und Optimierung der SQL-Anweisungsverarbeitung verantwortlich, während die Speicher-Engine-Schicht für Datenspeicherung und -abfrage verantwortlich ist. Binlog gehört zum Protokoll des Serviceschichtmoduls, d. h. es ist Engine-unabhängig. Alle Datenänderungen der Daten-Engine werden im Binlog-Protokoll aufgezeichnet. Wenn die Datenbank abstürzt und die InnoDB-Engine verwendet wird, kann das Binlog-Protokoll auch den Commit-Status des InnoDB-Redo-Protokolls überprüfen.

Binlog-Protokoll ist eingeschaltet

So aktivieren Sie die Protokollierung:

1. Konfiguration hinzufügen

log_bin=EIN
log_bin_basename=/Pfad/bin-log
log_bin_index=/Pfad/bin-log.index

2. Legen Sie nur den Log-Bin-Parameter fest

 log-bin=/Pfad/bin-log

Wenn Binlog aktiviert ist, erstellt MySQL eine durch log_bin_index angegebene .index-Datei und mehrere binäre Protokolldateien. Der Index zeichnet alle von MySQL verwendeten Binlog-Dateien nacheinander auf. Das Binlog-Protokoll wird mit dem angegebenen Namen (oder Standardwert) plus einer fortlaufenden Nummer als Suffix neu erstellt, z. B. bin-log.000001. Das Binlog-Protokoll wird in den folgenden drei Situationen neu erstellt:

Wenn die Dateigröße den Wert des Parameters max_binlog_size erreicht, führen Sie den Befehl „Flush Logs“ aus, um den MySQL-Dienst neu zu starten.

Binlog-Protokollformat

Das Binlog-Format kann durch den Wert des Parameters binlog_format festgelegt werden. Die optionalen Werte sind Anweisung, Zeile und gemischt. * Anweisungsformat: zeichnet die ursprüngliche von der Datenbank ausgeführte SQL-Anweisung auf. * ​​Zeilenformat: zeichnet die Änderung bestimmter Zeilen auf. Dies ist der aktuelle Standardwert. * Gemischtes Format: Da die beiden oben genannten Formate ihre eigenen Vor- und Nachteile haben, wird das gemischte Format angezeigt.

Binlog-Protokollanzeigetool: mysqlbinlog

Da es sich bei Binlog um eine Binärdatei handelt, kann sie nicht wie andere Dateien direkt geöffnet und angezeigt werden. MySQL stellt jedoch ein Binlog-Anzeigetool namens mysqlbinlog bereit, das Binärdateien analysieren kann. Natürlich sind die Analyseergebnisse von Protokollen in verschiedenen Formaten unterschiedlich: 1. Anweisungsformatprotokoll, führen Sie mysqlbinlog /path/bin-log.000001 aus, Sie können die ursprünglich ausgeführte SQL-Anweisung direkt sehen. 2. Zeilenformatprotokoll, die Lesbarkeit ist nicht so gut, aber Sie können das Dokument dennoch durch den Parameter mysqlbinlog -v /path/bin-log.000001 lesbarer machen

Zwei Paare sehr wichtiger Parameter von mysqlbinlog: 1. --start-datetime --stop-datetime analysiert Binlog innerhalb eines bestimmten Zeitraums; 2. --start-position --stop-position analysiert Binlog zwischen zwei Positionen;

Verwenden von Binlog zum Wiederherstellen von Daten

Die Verwendung von Binlog zum Wiederherstellen von Daten besteht im Wesentlichen darin, alle DML-Vorgänge über Binlog zu finden, die falschen SQL-Anweisungen zu entfernen und dann den langen Marsch erneut zu durchlaufen, um die Daten wiederherzustellen.

Offline-Übung

1. Erstellen Sie eine Datentabelle und fügen Sie Anfangswerte ein

    CREATE TABLE `Benutzer` (
              `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
              `name` varchar(255) DEFAULT NULL,
              `Alter` int(8) DEFAULT NULL,
              PRIMÄRSCHLÜSSEL (`id`)
     )ENGINE=InnoDB STANDARD-CHARSET=utf8mb4;
     INSERT INTO `Benutzer` (`ID`, `Name`, `Alter`)
        WERTE
            (null, „Name 1“, 5);

2. Suchen Sie die Position der Datenbank und des Binärprotokolls der letzten vollständigen Sicherung (P.S.: Natürlich kann es auch zeitlich wiederhergestellt werden). Dabei wird der aktuelle Stand als Ausgangswert der Sicherung verwendet.

    mysqldump -uroot -p T > /path/xxx.sql; # Sichern Sie die Datenbank. show master status; # Überprüfen Sie die aktuelle Position, sie ist 154.

3. Mehrere Datensätze einfügen

  INSERT INTO `Benutzer` (`ID`, `Name`, `Alter`)
  WERTE
     (null, 'Name 2', 13),
     (null, 'Name 3', 14),
     (null, 'Name 4', 15),
     (null, 'Name Fünf', 16),
     (null, „Name 6“, 17);

4. Führen Sie eine falsche Operation durch und fügen Sie nach der falschen Operation mehrere weitere Daten ein

Benutzer aktualisieren, Alter auf 5 setzen;
 INSERT INTO `Benutzer` (`ID`, `Name`, `Alter`)
 WERTE
    (null, 'Name 7', 16),
    (null, „Name 8“, 18);

5. Führen Sie nach dem Erkennen des falschen Vorgangs eine Datenwiederherstellung durch. Stoppen Sie zunächst den externen Dienst von MySQL und verwenden Sie die Sicherungsdaten, um die letzten Daten wiederherzustellen.

6. Verwenden Sie den Befehl mysqlbinlog, um die Binärdatei zu analysieren und herauszufinden,

    Der Fehler trat an Position 706 auf und der letzte normale Vorgang endete an Position 513.
    Es gibt eine normale SQL-Ausführung von 1152 bis zum Ende

7. Exportieren Sie die ausführbare SQL-Datei aus dem Binlog-Protokoll über den Befehl mysqlbinlog und importieren Sie die Daten in MySQL

 mysqlbinlog --start-position=154 --stop-position=513 bin-log.000001 > /Pfad/bak.sql;
 mysql -uroot -p < /Pfad/bak.sql;

8. Überspringen Sie die falsche Aktualisierungsanweisung und führen Sie die nachfolgenden normalen Anweisungen erneut aus, indem Sie die Logik von Schritt 7 befolgen, um die Datenwiederherstellungsarbeiten abzuschließen

Zusammenfassung

Egal, wie spät es ist, ein Datenbankabsturz ist frustrierend und ärgerlich. Man kann sagen, dass Binlog nach Datenbankabstürzen und Datenverlusten in verschiedenen Situationen eine Pille des Bedauerns ist. Dieser Artikel führt einfach ein Datenwiederherstellungsexperiment für die Datenbank in einer Offline-Umgebung durch. Wenn etwas nicht stimmt, geben Sie bitte Bescheid.

Oben finden Sie Einzelheiten zur MySQL-Datenwiederherstellung über Binlog. Weitere Informationen zur MySQL-Binlog-Datenwiederherstellung finden Sie in den anderen verwandten Artikeln auf 123WORDPRESS.COM!