Wie stellt MySQL die Datenintegrität sicher?

Die Bedeutung von Datenkonsistenz und -integrität für das Online-Geschäft liegt auf der Hand. Wie können wir sicherstellen, dass keine Daten verloren gehen? Heute besprechen wir, welche Verbesserungen MySQL hinsichtlich Datenintegrität und starker Konsistenz vorgenommen hat.

1. MySQL Zwei-Phasen-Commit

In relationalen Datenbanken wie Oracle und MySQL ist die Write-Ahead-Protokollierungsstrategie wichtig. Solange die Protokolle auf der Festplatte gespeichert werden, können Sie sicher sein, dass nach einem abnormalen Neustart von MySQL keine Daten verloren gehen. Wenn wir in MySQL von Protokollen sprechen, müssen wir Redo-Log und Binlog erwähnen.

1. Protokoll wiederholen

Das Redo-Protokoll, auch als Redo-Log-Datei bezeichnet, zeichnet die Änderungen an den Datenzeilen auf jeder Datenseite detailliert auf und zeichnet die Werte nach der Änderung der Daten auf. Das Redo-Log wird für die Wiederherstellung nach einem Datenbankabsturz verwendet und ist eine der wichtigsten Funktionen zur Gewährleistung der Datensicherheit.

Das Redo-Log wird sequenziell und zyklisch geschrieben. Die Dateigröße und Anzahl der Redo-Logs werden durch die beiden Parameter innodb_log_file_size und innodb_log_files_in_group gesteuert. Bevor das Redo-Log auf die Festplatte geschrieben wird, wird es zunächst in den Redo-Log-Puffer geschrieben, dessen Größe durch innodb_log_buffer_size gesteuert wird. Wie werden Protokolle auf der Festplatte gespeichert, nachdem sie in den Redo-Log-Puffer geschrieben wurden? Um die Schreibstrategie des Redo-Protokolls zu steuern, verwendet Innodb je nach den unterschiedlichen Werten des Parameters innodb_flush_log_at_trx_commit unterschiedliche Strategien. Es gibt drei verschiedene Werte:

• 1. Wenn auf 0 gesetzt: Wenn eine Transaktion festgeschrieben wird, aktualisiert der MySQL-Hintergrund-Master-Thread die Cache-Datei alle 1 Sekunde in die Protokolldatei.
• 2. Wenn der Wert auf 1 gesetzt ist, bedeutet dies, dass das Redo-Protokoll bei jedem Festschreiben einer Transaktion direkt auf der Festplatte gespeichert wird. Dadurch wird sichergestellt, dass das Transaktionsprotokoll nicht verloren geht. Die Datenbankleistung wird jedoch geringfügig beeinträchtigt.
• 3. Wenn der Wert auf 2 gesetzt ist, bedeutet dies, dass bei jedem Festschreiben einer Transaktion nur das Redo-Protokoll in die Protokolldatei geschrieben wird, die Festplatte jedoch nicht geleert wird. Das Dateisystem leert die Festplatte automatisch.

Unter den drei Modi bietet 0 die beste Leistung, ist aber nicht sicher. Wenn der MySQL-Prozess abstürzt, gehen Daten einer Sekunde verloren. Option 1 bietet die höchste Sicherheit, hat aber die größten Auswirkungen auf die Leistung. Option 2 steuert hauptsächlich die Datenträgerleerungszeit durch das Betriebssystem. Wenn nur MySQL abstürzt, hat dies keine Auswirkungen auf die Daten. Wenn der Host abnormal abstürzt, gehen auch Daten verloren.

2. Binlog

Binlog, auch als Binärprotokoll bekannt, zeichnet alle Vorgänge auf, die die MySQL-Datenbank ändern, mit Ausnahme von Auswahl- und Anzeigevorgängen. Es dient hauptsächlich den Funktionen Wiederherstellung, Replikation und Überwachung. Zu den Binlog-Formaten gehören hauptsächlich Anweisungs-, Zeilen- und gemischte Formate.

Anweisung: Auf Operationen basierende SQL-Anweisungen werden im Binärprotokoll aufgezeichnet. Dies wird nicht empfohlen.

Zeile: Basierend auf der Aufzeichnung der Zeilenänderungen wird der Inhalt vor und nach der Zeilenänderung aufgezeichnet. Der Zeilenmodus ist auch eine wichtige Garantie für die Datenbank, um Datenverlust zu verhindern, und es wird empfohlen, ihn zu verwenden.

Gemischt: Eine Mischung der ersten beiden Modi. Dies wird nicht empfohlen.

Die Schreiblogik von Binlog ist ebenfalls relativ einfach: Während der Transaktionsausführung wird es zuerst in den Binlog-Cache geschrieben und dann, wenn die Transaktion festgeschrieben wird, in die Binlog-Datei geschrieben. Der Binlog-Cache wird durch die Parameter binlog_cache_size und max_binlog_size gesteuert. Jedem Thread wird ein Binlog-Cache zugewiesen, aber sie teilen sich die Binlog-Datei.

Der Mechanismus von Binlog zum Schreiben von Protokolldateien wird von sync_binlog gesteuert:

• 1. Wenn sync_binlog = 0 ist, bedeutet dies, dass jede Transaktion nur ohne fsync geschrieben wird.
• 2. Wenn sync_binlog = 1 ist, bedeutet dies, dass fsync bei jeder Übermittlung einer Transaktion ausgeführt wird und die Daten auf die Festplatte geschrieben werden.
• 3. Wenn sync_binlog = N (N > 1), bedeutet dies, dass MySQL nach dem Festschreiben von n Transaktionen eine fsync-Aktion ausführt, um die Daten im Binlog-Cache auf die Festplatte zu übertragen.

Das Setzen von sowohl innodb_flush_log_at_trx_commit als auch sync_binlog auf 1 ist der klassische Doppel-Eins-Modus in MySQL-Daten, der sicherstellt, dass die Datenbank keine Daten verliert.

MySQL-Daten verwenden den WAL-Mechanismus, um die Leistungseinbußen zu verringern, die durch jedes Leeren schmutziger Daten verursacht werden. Wenn die „Double One“-Strategie festgelegt ist, wird dies die Leistung der Datenbank beeinträchtigen? Tatsächlich liegt dies hauptsächlich daran, dass sowohl das Redo-Log als auch das Binlog sequentiell geschrieben werden. Die sequentielle Schreibgeschwindigkeit der Festplatte ist viel schneller als die zufällige Schreibgeschwindigkeit. Darüber hinaus hat der Gruppen-Commit-Mechanismus in MySQL den IOPS-Verbrauch der Festplatte erheblich reduziert.

3. Zwei-Phasen-Commit

MySQL führt ein Zwei-Phasen-Commit (2pc) ein. MySQL behandelt normale Transaktionen als XA-Transaktionen (interne verteilte Transaktionen) und weist jeder Transaktion automatisch eine eindeutige ID (XID) zu. COMMIT wird passiv in zwei Phasen unterteilt: Vorbereiten und Commit.

Phase 1: Transaktionsvorbereitungsphase

An diesem Punkt wurde SQL erfolgreich ausgeführt und XID-Informationen sowie Redo- und Undo-Speicherprotokolle wurden generiert. Rufen Sie dann die Vorbereitungsmethode auf, um die erste Phase abzuschließen, den Transaktionsstatus auf TRX_PREPARED zu setzen und das Redo-Protokoll auf die Festplatte zu schreiben.

Phase 2: Commit-Phase

Wenn die erste Phase der Transaktion in die Vorbereitungsphase eintritt, wird das generierte Binärprotokoll in die Datei geschrieben und auf die Festplatte übertragen. Zu diesem Zeitpunkt muss die Transaktion festgeschrieben werden.

Spezifische Analyse abnormaler Szenarien:

1. Wenn eine Transaktion in der Vorbereitungsphase abstürzt und die Datenbank nicht in das Binärprotokoll geschrieben wird und die Speicher-Engine während der Wiederherstellung nicht festgeschrieben wird, wird die Transaktion zurückgesetzt.

2. Wenn eine Transaktion in der Binärprotokollphase abstürzt, wurde das Protokoll nicht erfolgreich auf die Festplatte geschrieben. Die Transaktion wird beim Start zurückgesetzt. 3. Wenn eine Transaktion abstürzt, nachdem das Binärprotokoll per fsync() auf die Festplatte übertragen wurde, InnoDB jedoch keine Zeit zum Commit hatte, liest die MySQL-Datenbank während der Wiederherstellung das Xid_log_event des Binärprotokolls und weist InnoDB an, die Transaktionen mit diesen XIDs zu committen. Nachdem InnoDB diese Transaktionen committet hat, werden andere Transaktionen zurückgesetzt, um die Konsistenz der Speicher-Engine und des Binärprotokolls zu wahren.

Das zweiphasige Commit von MySQL stellt sicher, dass nach dem Neustart der Datenbank aufgrund eines abnormalen Herunterfahrens keine Daten verloren gehen.

2. Doppeltes Schreiben

Wie bereits erwähnt, stellen Redo-Log, Binlog und Zwei-Phasen-Commit sicher, dass Daten nach einem abnormalen Neustart von MySQL durch Rollforward und Rollback wiederhergestellt werden können. MySQL verwendet ein Redo-Protokoll zur Wiederherstellung. Das Redo-Protokoll zeichnet die physischen Vorgänge auf der Seite auf, es gibt jedoch ein Problem. Wenn die Seite selbst fehlerhaft ist, z. B. ein teilweises Schreibproblem (die Seitengröße beträgt 16 KB. Angenommen, beim Schreiben einer schmutzigen Seite im Speicher in die Datenbank werden 4 KB geschrieben und die Stromversorgung wird plötzlich unterbrochen. Das heißt, die ersten beiden 4 KB sind neu und die letzten 12 KB sind alt, sodass diese Datenseite unvollständig und eine fehlerhafte Datenseite ist.) Die Redo-Wiederherstellung überprüft die Integrität der Datenseite. Zu diesem Zeitpunkt ist die Datenseite bereits beschädigt, sodass das Redo-Protokoll nicht zur Wiederherstellung verwendet werden kann und die Daten verloren gehen.

Double Write-Prinzip:

1. Beim Aktualisieren der schmutzigen Seiten des Pufferpools werden diese nicht direkt in die Datendatei geschrieben, sondern zuerst in den Doppelschreibpuffer kopiert.

2. Schreiben Sie dann den Doppelschreibpuffer zweimal, jedes Mal 1 MB, in den gemeinsam genutzten Tabellenbereich auf der Festplatte.

3. Schreiben Sie abschließend die Datendatei aus dem Doppelschreibpuffer. Obwohl Daten immer zweimal geschrieben werden, werden Doppelschreibvorgänge sequenziell durchgeführt, wodurch tatsächlich etwa 10 % der Systemleistung verloren gehen.

Dies kann das oben erwähnte Problem des teilweisen Schreibfehlers lösen, da sich im gemeinsam genutzten Tabellenbereich auf der Festplatte eine Kopie der Datenseite befindet. Wenn die Datenbank beim Schreiben der Seite in die Datendatei abstürzt, kann beim Wiederherstellen der Instanz die Seitenkopie im gemeinsam genutzten Tabellenbereich gefunden und kopiert werden, um die ursprüngliche Datenseite zu überschreiben. Anschließend kann das Redo-Protokoll angewendet werden.

3. Zusammenfassung

Heute haben wir über die Zwei-Phasen-Commit- und Double-Write-Mechanismen von MySQL gesprochen, die jeweils erklärt haben, wie MySQL Datenverluste in den Szenarien verhindern kann, in denen MySQL abstürzt und neu gestartet wird und beim teilweisen Schreiben von Seiten. Was also sollen wir tun, wenn unser Betriebssystem abstürzt und nicht gestartet werden kann? Welche Optimierungen hat MySQL in der Cluster-Architektur vorgenommen, um sicherzustellen, dass keine Daten verloren gehen? Im nächsten Kapitel teilen wir mit Ihnen die Optimierungen und Verbesserungen von MySQL in der Clusterarchitektur.