Lösung für das Problem des Datenverlusts bei Verwendung der Ersetzungsoperation in MySQL

Vorwort

Die Entwickler des Unternehmens verwendeten beim Aktualisieren von Daten die Anweisung „replace into“. Durch unsachgemäße Verwendung gingen große Datenmengen verloren. Dieser Artikel analysiert, wie es zu dem Datenverlust kam.

1. Problembeschreibung

Die Entwickler des Unternehmens verwendeten beim Aktualisieren von Daten die Anweisung „replace into“. Durch unsachgemäße Verwendung gingen große Datenmengen verloren. Nachfolgend finden Sie eine Analyse der Ursachen für den Datenverlust.

2. Problemanalyse

a. REPLACE-Prinzip

Die offizielle Erklärung des REPLACE INTO-Prinzips lautet:

REPLACE funktioniert genau wie INSERT, außer dass, wenn eine alte Zeile in der Tabelle denselben Wert wie eine neue Zeile für einen PRIMARY KEY oder einen UNIQUE-Index hat, die alte Zeile gelöscht wird, bevor die neue Zeile eingefügt wird.

Wenn der Primärschlüssel oder eindeutige Schlüssel der neu eingefügten Zeile bereits in der Tabelle vorhanden ist, wird der ursprüngliche Datensatz gelöscht und die neue Zeile eingefügt. Wenn er nicht in der Tabelle vorhanden ist, wird er direkt eingefügt.

Adresse: https://dev.mysql.com/doc/refman/5.6/en/replace.html

b. Problemsymptome

Die Tabellenstruktur der fehlenden Daten ist wie folgt:

Tabelle „active_items“ erstellen (

Die ausgeführten Ersetzungsanweisungen sind wie folgt (mehrere):

Ersetzen Sie in Active_Items (Anzeigen-ID, Punktzahl) Werte ('XXXXXXX', 1800);

Durch Abfragen des Binärprotokolls zum Suchen des Ausführungsdatensatzes sind einige davon wie folgt:

### AKTUALISIEREN Sie `Elemente`.`aktive_Elemente`
### @21=0 /* TINYINT meta=0 nullable=0 is_null=0 */

Die bearbeitete ad_id existiert bereits, daher wird sie zuerst gelöscht und dann eingefügt. Es ist ersichtlich, dass mit Ausnahme der angegebenen ad_id und Punktzahl andere Felder auf die Standardwerte geändert werden, was zum Verlust der Originaldaten führt (obwohl sie im Protokoll zur Aktualisierung konvertiert werden).

c. Vergleichstests

Als nächstes habe ich folgende Tests durchgeführt:

• Datenverlust während des MySQL-Ersetzungsvorgangs
• Auf der linken Seite wird die Anweisung REPLACE verwendet, auf der rechten Seite die Anweisung DELETE + INSERT. Das Endergebnis ist genau dasselbe.
• Die Zeile mit der ursprünglichen Primärschlüssel-ID 1 wird gelöscht, die Primärschlüssel-ID der neu eingefügten Zeile wird auf 4 aktualisiert und der Standardwert wird in das Feld c ohne angegebenen Inhalt eingefügt.
• Eine Datenzeile wurde mit REPLACE aktualisiert und MySQL meldete, dass die Anzahl der betroffenen Zeilen 2 Zeilen betrug.

Zusammengefasst bedeutet dies, dass eine Zeile gelöscht und eine Zeile eingefügt wird.

3. Datenwiederherstellung

Nach Datenverlust oder Datenfehlern gibt es mehrere Möglichkeiten zur Wiederherstellung:

1. Die Geschäftsseite schreibt ihr eigenes Drehbuch für die Erholung
2. Verwenden Sie MySQL-Binlog, um das fehlerhafte SQL herauszufinden und Reverse-SQL zur Datenwiederherstellung zu generieren (geeignet für kleine SQL-Datenmengen)
3. Wiederherstellen des Datenstatus zum Zeitpunkt vor der fehlerhaften Operation durch historische Sicherungsdateien + inkrementelles Binärprotokoll

4. Problemerweiterung

Aus der obigen Analyse können wir ersehen, dass REPLACE die alte Zeile löscht und die neue Zeile einfügt, das Binärprotokoll dies jedoch in Form einer Aktualisierung aufzeichnet, was ein weiteres Problem mit sich bringt:

Der Selbstwachstumswert der Slave-Datenbank ist kleiner als der der Master-Datenbank

1. Testen

a. Master-Slave-Konsistenz:

Hauptbibliothek:

mysql> zeigen erstellen Tabelle tG

Aus der Bibliothek:

mysql> zeigen erstellen Tabelle tG

b. Hauptbibliothek REPLACE:

Hauptbibliothek:

mysql> ersetzen in t (a,b)values(1,7);

Aus der Bibliothek:

mysql> zeigen erstellen Tabelle tG

Beachten Sie, dass die AUTO_INCREMENT-Werte der Master- und Slave-Tabellen jetzt unterschiedlich sind.

c. Simulieren Sie das Upgrade vom Slave zum Master und führen Sie INSERT in der Slave-Datenbank aus:

mysql> in t einfügen (a,b,c)Werte(4,4,4);

Beim Einfügen aus der Datenbank wird ein Fehler gemeldet und der Primärschlüssel wiederholt. Nach der Fehlermeldung ist AUTO_INCREMENT +1, sodass das Einfügen bei erneuter Ausführung erfolgreich sein kann.

2. Fazit

Unter normalen Umständen hat dieses Problem keine Auswirkungen, aber:

Wenn die Masterdatenbank regelmäßig eine große Anzahl von REPLACE-Anweisungen verwendet, wodurch der AUTO_INCREMENT-Wert der Slavedatenbank weit hinter dem der Masterdatenbank zurückbleibt, treten beim erneuten Einfügen von Daten in der neuen Masterdatenbank eine große Anzahl von Primärschlüsselduplizierungsfehlern auf, was dazu führt, dass das Einfügen von Daten fehlschlägt.

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. Vielen Dank für Ihre Unterstützung von 123WORDPRESS.COM.