Detaillierte Analyse des binlog_format-Modus und der Konfiguration in MySQL

Es gibt drei Hauptmethoden der MySQL-Replikation: SQL-Anweisungsbasierte Replikation (SBR), Zeilenbasierte Replikation (RBR) und Gemischte Replikation (MBR). Entsprechend gibt es drei Binlog-Formate: STATEMENT, ROW und MIXED .

① STATEMENT-Modus (SBR)

Jede SQL-Anweisung, die Daten ändert, wird im Binärprotokoll aufgezeichnet. Der Vorteil besteht darin, dass nicht jede SQL-Anweisung und jede Datenzeilenänderung aufgezeichnet werden muss, wodurch die Menge der Binlog-Protokolle reduziert, IO gespart und die Leistung verbessert wird. Der Nachteil besteht darin, dass es in einigen Fällen zu inkonsistenten Daten im Master-Slave kommen kann (z. B. Probleme mit der Funktion sleep(), last_insert_id() und benutzerdefinierten Funktionen (udf)).

② ROW-Modus (RBR)

Zeichnen Sie nicht die Kontextinformationen jeder SQL-Anweisung auf, sondern nur, welche Daten geändert wurden und wie sie geändert wurden. Darüber hinaus besteht kein Problem darin, dass der Aufruf und die Auslösung von gespeicherten Prozeduren, Funktionen oder Triggern in bestimmten Situationen nicht korrekt reproduziert werden können. Der Nachteil besteht darin, dass insbesondere bei Änderungen an der Tabelle sehr viele Protokolle generiert werden, die das Protokoll zum Explodieren bringen.

③ MIXED-Modus (MBR)

Die beiden oben genannten Modi werden in Kombination verwendet. Für die allgemeine Replikation wird der STATEMENT-Modus zum Speichern von Binärprotokollen verwendet. Für Vorgänge, die im STATEMENT-Modus nicht repliziert werden können, wird der ROW-Modus zum Speichern von Binärprotokollen verwendet. MySQL wählt die Methode zum Speichern der Protokolle basierend auf der ausgeführten SQL-Anweisung aus.

Binlog-Replikationskonfiguration

In der MySQL-Konfigurationsdatei my.cnf können Sie binglog-bezogene Optionen konfigurieren

binlog_format = MIXED // Binlog-Protokollformat, MySQL verwendet standardmäßig eine Anweisung und „mixed“ wird empfohlen
log-bin = /data/mysql/mysql-bin.log //binlog-Protokolldatei expire_logs_days = 7 //Bereinigungszeit für Ablauf des Binlogs max_binlog_size = 100m //Größe der Binlog-Protokolldatei binlog_cache_size = 4m //Größe des Binlog-Cache max_binlog_cache_size = 512m //Maximale Größe des Binlog-Cache

Drei GEMISCHTE Beschreibung

Wenn die ausgeführte SQL-Anweisung eine Zeitfunktion wie now() enthält, wird im Protokoll eine entsprechende Zeitzeichenfolge von unix_timestamp()*1000 generiert. Wenn der Slave die Synchronisierung abschließt, verwendet er die Zeit, zu der das sqlEvent auftritt, um die Genauigkeit der Daten sicherzustellen. Darüber hinaus kann der Slave für einige Funktionsfunktionen die entsprechende Datensynchronisierung abschließen. Für einige der oben angegebenen UDF-Funktionen, die dazu führen, dass der Slave die Situation nicht erkennt, werden diese Binlogs im ROW-Format gespeichert, um sicherzustellen, dass das generierte Binlog vom Slave zum Abschließen der Datensynchronisierung verwendet werden kann.

Vergleichen wir nun die Vor- und Nachteile der folgenden Modi in SBR und RBR 2:

Vorteile von SBR:

Lange Geschichte und ausgereifte Technologie

Die Binlog-Datei ist klein

Das Binärprotokoll enthält alle Informationen zu Datenbankänderungen, die zur Prüfung der Sicherheit der Datenbank verwendet werden können.

Binlog kann für die Echtzeitwiederherstellung verwendet werden, nicht nur für die Replikation

Die Master- und Slave-Versionen können unterschiedlich sein und die Slave-Server-Version kann höher sein als die Master-Server-Version

Nachteile von SBR:

Nicht alle UPDATE-Anweisungen können repliziert werden, insbesondere diejenigen, die unbestimmte Operationen enthalten.

Die Replikation kann auch beim Aufrufen von UDFs mit nicht deterministischen Elementen fehlschlagen.

Anweisungen mit den folgenden Funktionen können ebenfalls nicht kopiert werden:

* DATEI LADEN()

* UUID()

* BENUTZER()

* GEFUNDENE_ZEILEN()

* SYSDATE() (sofern die Option --sysdate-is-now nicht beim Start aktiviert wurde)

INSERT ... SELECT erzeugt mehr Zeilensperren als RBR

Beim Replizieren eines UPDATE, das einen vollständigen Tabellenscan erfordert (in der WHERE-Klausel wird kein Index verwendet), werden mehr Zeilensperren angefordert als mit RBR.

Bei InnoDB-Tabellen mit AUTO_INCREMENT-Feldern blockieren INSERT-Anweisungen andere INSERT-Anweisungen.

Bei einigen komplexen Anweisungen ist der Ressourcenverbrauch auf dem Slave-Server schwerwiegender. Im RBR-Modus ist nur der geänderte Datensatz betroffen. Die gespeicherte Funktion (nicht die gespeicherte Prozedur) führt die Funktion NOW() auch einmal aus, wenn sie aufgerufen wird. Dies kann gut oder schlecht sein.

Die ermittelte UDF muss auch auf dem Slave-Server ausgeführt werden

Die Datentabelle muss nahezu konsistent mit dem primären Server sein, da es sonst zu Replikationsfehlern kommen kann.

Wenn bei der Ausführung komplexer Anweisungen Fehler auftreten, werden mehr Ressourcen verbraucht

Vorteile von RBR:

Jede Situation kann kopiert werden, was die sicherste und zuverlässigste Möglichkeit zum Kopieren ist

Wie die meisten anderen Datenbanksysteme, Replikationstechnologie

In den meisten Fällen ist die Replikation wesentlich schneller, wenn die Tabelle auf dem Slave über einen Primärschlüssel verfügt.

Beim Replizieren der folgenden Anweisungen werden weniger Zeilensperren verwendet:

* EINFÜGEN ... AUSWÄHLEN

* INSERT mit AUTO_INCREMENT-Feldern

* UPDATE- oder DELETE-Anweisungen, die keine Bedingungen haben oder nicht viele Datensätze ändern

Weniger Sperren beim Ausführen von INSERT-, UPDATE- und DELETE-Anweisungen

Es ist möglich, mehrere Threads zu verwenden, um die Replikation vom Server aus durchzuführen

Nachteile von RBR:

Binlog ist viel größer

Das Binlog enthält bei komplexen Rollbacks viele Daten

Wenn eine UPDATE-Anweisung auf dem Masterserver ausgeführt wird, werden alle geänderten Datensätze in das Binärprotokoll geschrieben, während SBR nur einmal schreibt, was häufig zu gleichzeitigen Schreibproblemen im Binärprotokoll führt.

Große, von UDFs generierte BLOB-Werte können die Replikation verlangsamen

Aus dem Binärprotokoll ist nicht ersichtlich, welche Anweisungen kopiert wurden.

Wenn Sie eine gestapelte SQL-Anweisung auf einer nicht transaktionalen Tabelle ausführen, verwenden Sie am besten den SBR-Modus, da es sonst leicht zu Dateninkonsistenzen zwischen dem Master- und dem Slave-Server kommen kann. Darüber hinaus lauten die Verarbeitungsregeln für Änderungen in den Tabellen in der Systembibliothek mysql wie folgt:

Wenn Sie INSERT, UPDATE oder DELETE verwenden, um die Tabelle direkt zu bedienen, wird das Protokollformat entsprechend der Einstellung von binlog_format aufgezeichnet.

Wenn Sie Verwaltungsanweisungen wie GRANT, REVOKE, SET PASSWORD usw. verwenden, wird trotzdem die Protokollierung im SBR-Modus verwendet.

Hinweis: Nach der Übernahme des RBR-Modus können viele zuvor aufgetretene Probleme mit doppelten Primärschlüsseln gelöst werden.

Zusammenfassen

Dies ist der gesamte Inhalt dieses Artikels zur detaillierten Analyse des binlog_format-Modus und der Konfiguration in MySQL. Ich hoffe, er wird für alle hilfreich sein. Interessierte Freunde können sich auf Folgendes beziehen: Mehrere wichtige MySQL-Variablen, detaillierte Erklärung der MySQL-Vorbereitungsprinzipien, MySQL-Variablendeklaration und gespeicherte Prozeduranalyse usw. Wenn Sie Fragen haben, hinterlassen Sie bitte eine Nachricht, jeder ist herzlich eingeladen, zu kommunizieren und zu diskutieren.