Zusammenfassung der Binlog-Nutzung der MySQL-Datenbank (unbedingt lesen)

Ich werde nicht näher darauf eingehen, wie wichtig das Binärprotokoll Binlog für die MySQL-Datenbank ist. Basierend auf meiner täglichen Betriebserfahrung und in Kombination mit Online-Referenzmaterialien werde ich die Verwendung von Binlog-Protokollen klären:

1. Einführung in Binlog

1) Was ist Binlog?
Das Binlog-Protokoll wird verwendet, um alle Anweisungen aufzuzeichnen, die Daten aktualisieren oder potenziell aktualisiert haben (z. B. ein DELETE, das mit keinen Zeilen übereinstimmt). Dabei werden Aussagen in Form von „Events“ gespeichert, welche Veränderungen an Daten beschreiben.

2) Binlog-Funktion
Aufgrund des Binärprotokolls der Datenaktualisierungen kann es für Echtzeit-Backups verwendet und mit der Master/Slave-Replikation kombiniert werden.

3) Mit Binlog verbundene Parameter
log_bin

Das Setzen dieses Parameters bedeutet das Aktivieren der Binlog-Funktion und das Angeben des Pfadnamens

log_bin_index

Durch das Setzen dieses Parameters wird der Pfad und Name der binären Indexdatei festgelegt.

binlog_do_db

Dieser Parameter bewirkt, dass nur das Binärlog der angegebenen Datenbank aufgezeichnet wird

binlog_ignore_db
Dieser Parameter gibt an, dass das Binärprotokoll der angegebenen Datenbank nicht aufgezeichnet wird.

max_binlog_cache_size

Dieser Parameter gibt die maximale Größe des von binlog verwendeten Speichers an.

binlog_cache_size

Dieser Parameter gibt die von Binlog verwendete Speichergröße an, die mit Hilfe der Statusvariablen binlog_cache_use und binlog_cache_disk_use getestet werden kann.

binlog_cache_use: Die Anzahl der Transaktionen, die den Binärprotokoll-Cache verwenden

binlog_cache_disk_use: Die Anzahl der Transaktionen, die den Binärprotokoll-Cache verwendet haben, aber den Wert binlog_cache_size überschritten haben und eine temporäre Datei zum Speichern der Anweisungen in der Transaktion verwendet haben

max_binlog_size

Der Maximal- und Standardwert von Binlog beträgt 1 GB. Diese Einstellung kann die Größe von Binlog nicht streng steuern, insbesondere wenn Binlog nahe am Maximalwert liegt und auf eine relativ große Transaktion stößt. Um die Integrität der Transaktion sicherzustellen, ist es nicht möglich, das Protokoll zu wechseln. Alle SQL-Anweisungen der Transaktion können nur im aktuellen Protokoll aufgezeichnet werden, bis die Transaktion endet.

sync_binlog

Dieser Parameter wirkt sich direkt auf die Leistung und Integrität von MySQL aus

sync_binlog=0

Wenn die Transaktion festgeschrieben wird, schreibt Mysql nur die Daten in binlog_cache in die Binlog-Datei, führt aber keine Festplattensynchronisierungsanweisungen wie fsync aus, um das Dateisystem zu benachrichtigen, den Cache auf der Festplatte zu aktualisieren. Stattdessen überlässt es dem Dateisystem die Entscheidung, wann synchronisiert werden soll. Dies ist die beste Leistung.

sync_binlog=n , nachdem n Transaktionen festgeschrieben wurden, führt Mysql eine Anweisung zur Festplattensynchronisierung wie etwa fsync aus und das Comrade-Dateisystem aktualisiert den Binlog-Dateicache auf der Festplatte.

Die Standardeinstellung in Mysql ist sync_binlog=0, was bedeutet, dass keine obligatorischen Anweisungen zur Festplattenaktualisierung ausgeführt werden. Dies bietet die beste Leistung, birgt jedoch auch das größte Risiko. Wenn das System abstürzt, gehen alle Binlog-Informationen im Dateisystem-Cache verloren.

4) Binlog löschen

Binlog kann manuell oder automatisch gelöscht werden:

a) Binlog automatisch löschen

Verwenden Sie den Binlog-Parameter (expire_logs_days), um die automatische Löschung des Binlogs durch MySQL zu realisieren

mysql> Binärprotokolle anzeigen;
mysql> Variablen wie „expire_logs_days“ anzeigen; //Dieser Parameter gibt die Anzahl der Tage an, nach denen Binlog-Protokolle automatisch gelöscht/abgelaufen werden. Der Standardwert ist 0, was bedeutet, dass sie nicht automatisch gelöscht werden.
mysql> set global expire_logs_days=3; // bedeutet, dass die Protokolle 3 Tage lang aufbewahrt werden und nach 3 Tagen automatisch ablaufen.

b) Binlog manuell löschen

mysql> Reset Master; // Lösche das Binlog des Masters, d. h. lösche alle Binlog-Protokolle manuell
mysql> Reset Slave; //Lösche das Relay-Protokoll des Slaves
mysql> purge master logs before '2012-03-30 17:20:00'; //Lösche die Binlog-Logdateien im Log-Index vor dem angegebenen Datum
mysql> purge master logs to 'binlog.000002'; //Lösche die Binlog-Logdatei im Logindex der angegebenen Logdatei

mysql> set sql_log_bin= 1/0 ; //Wenn der Benutzer Superrechte hat, kann er Binlog-Aufzeichnungen für die aktuelle Sitzung aktivieren oder deaktivieren.
mysql> show master logs; //Binlog-Protokollliste des Masters anzeigen
mysql> show binary logs; //Größe der Binlog-Protokolldatei des Masters anzeigen
mysql> show master status; //Wird verwendet, um Statusinformationen der Master-Binärprotokolldatei bereitzustellen
mysql> show slave hosts; //Liste der aktuell registrierten Slaves anzeigen. Slaves, die nicht mit der Option --report-host=slave_name beginnen, werden in dieser Liste nicht angezeigt.

mysql> flush logs; //Erstelle eine neue Binlog-Logdatei

Fallbeschreibung für die automatische Bereinigung und manuelle Löschung des MySQL-Binlog-Protokolls:

Wenn Sie den MySQL-Datenbank-Master-Slave aktivieren, wird eine große Anzahl von Dateien wie das Protokoll mysql-bin.00000* generiert, die viel Speicherplatz auf Ihrer Festplatte beanspruchen. 
mysql-bin.000001 
mysql-bin.000002 
mysql-bin.000003 
mysql-bin.000004 
mysql-bin.000005 
… 
 
Es gibt drei Lösungen zum Löschen dieser Binlog-Protokolle: 
1. Schließen Sie MySQL-Master und -Slave und schließen Sie Binlog. 
Der Beispielvorgang sieht wie folgt aus: 
[root@huqniupc ~]# vim /etc/my.cnf //Kommentieren Sie log-bin und binlog_format 
# Replikationsmasterserver (Standard) 
# Binäre Protokollierung ist für die Replikation erforderlich 
# log-bin=mysql-bin 
# Binäres Protokollierungsformat – gemischt empfohlen 
# binlog_format=gemischt 
Starten Sie dann die Datenbank neu. 2. Aktivieren Sie MySQL Master-Slave und legen Sie expire_logs_days fest. 
Der Beispielvorgang sieht wie folgt aus: 
[root@huqniupc ~]# vim /etc/my.cnf //Ändern Sie expire_logs_days, x ist die Anzahl der Tage für die automatische Löschung, normalerweise wird x auf einen kürzeren Wert eingestellt, z. B. 10 
expire_logs_days = x //Anzahl der Tage, nach denen Binärprotokolle automatisch gelöscht werden. Der Standardwert ist 0, was bedeutet „keine automatische Löschung“ 
Diese Methode erfordert einen Neustart von MySQL 
 
Natürlich können Sie MySQL Master-Slave auch starten, ohne MySQL neu zu starten, und expire_logs_days direkt in MySQL festlegen 
> Binärprotokolle anzeigen; 
> Variablen wie „%log%“ anzeigen; 
> global expire_logs_days = 10 festlegen; 
 
 
3. Löschen Sie die Binlog-Datei manuell (z. B. Mysql> PURGE MASTER LOGS TO 'MySQL-bin.010';) 
Der Beispielvorgang sieht wie folgt aus: 
[root@huqniupc ~]# /usr/local/mysql/bin/mysql -u root -p 
> PURGE MASTER LOGS BEFORE DATE_SUB(CURRENT_DATE, INTERVAL 10 DAY); // MySQL-Binlog-Protokolle von vor 10 Tagen löschen. Anhang 2 enthält Anweisungen und Beispiele zum manuellen Löschen von PURGE MASTER LOGS. > show master logs; 
  
Sie können auch den Master zurücksetzen und alle Binlog-Dateien löschen: 
# /usr/local/mysql/bin/mysql -u root -p 
> Reset Master; //Anhang 3: Beschreibung der Auswirkungen auf MySQL beim Löschen des Binärprotokolls--------------------------------------------------------------- 
PURGE MASTER LOGS Manuelles Löschen, Verwendung und Beispiele, MASTER und BINARY sind Synonyme> PURGE {MASTER | BINARY} LOGS TO 'log_name'
> {MASTER | BINARY}-PROTOKOLLE VOR „Datum“ LÖSCHEN
Löscht alle Binärprotokolle im Protokollindex vor dem angegebenen Protokoll oder Datum. Diese Protokolle werden auch aus der Liste der in der Protokollindexdatei aufgezeichneten MySQL BIN-LOG-Protokolle entfernt, sodass das angegebene Protokoll das erste wird. 
 
Beispiele: 
> PURGE MASTER LOGS TO 'MySQL-bin.010'; //MySQL-bin.010-Protokolle löschen> PURGE MASTER LOGS BEFORE '2008-06-22 13:00:00'; //Binlog-Protokolle vor 2008-06-22 13:00:00 löschen> PURGE MASTER LOGS BEFORE DATE_SUB( NOW( ), INTERVAL 3 DAY); //Binlog-Protokolle vor 3 Tagen löschen VORHER kann das Datumsargument der Variable das Format 'JJJJ-MM-TT hh:mm:ss' haben. 
-----------------------------------------------------

5) Auswirkungen auf MySQL beim Löschen des Binärprotokolls

Wenn ein aktiver Slave-Server gerade eines der Protokolle liest, die Sie löschen möchten, hat diese Anweisung keine Auswirkung und schlägt mit einem Fehler fehl. Wenn der Slave-Server jedoch heruntergefahren wird (oder die Master-Slave-Beziehung unterbrochen ist) und er zufällig eines der Protokolle bereinigt, das er lesen möchte, kann der Slave-Server nach dem Start nicht mehr replizieren. Diese Anweisung kann sicher ausgeführt werden, während Slave-Server replizieren, ohne sie anzuhalten.

6) Binglog anzeigen

Sie können den Inhalt von Binlog über den Befehl mysqlbinlog anzeigen

[root@localhost ~]# mysqlbinlog /home/mysql/binlog/binlog.000003 | mehr
/*!40019 SETZEN @@session.max_insert_delayed_threads=0*/;
/*!50003 SETZEN @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
TRENNUNGSZEICHEN /*!*/;
# bei 4
#120330 16:51:46 Server-ID 1 end_log_pos 98 Start: Binlog v 4, Server v 5.0.45-Log erstellt 120330 1
6:51:46
# Warnung: Dieses Binärprotokoll wurde nicht ordnungsgemäß geschlossen. Höchstwahrscheinlich ist mysqld beim Schreiben abgestürzt.
# bei 196
#120330 17:54:15 Server-ID 1 End-Log-Pos 294 Abfrage Thread-ID = 3 Exec-Zeit = 2 Fehlercode = 0
ZEITSTEMPEL FESTLEGEN=1333101255/*!*/;
in tt7 einfügen, select * from tt7/*!*/;
# bei 294
#120330 17:54:46 Server-ID 1 End-Log-Pos 388 Abfrage Thread-ID = 3 Exec-Zeit = 28 Fehlercode = 0
ZEITSTEMPEL FESTLEGEN=1333101286/*!*/;
Tabelle ändern tt7 engine=innodb/*!*/;

Binlog-Format analysieren:

Standort

Die Position in der Datei. „at 196“ gibt den Startpunkt des „Ereignisses“ an, das beim 196. Byte beginnt; „end_log_pos 294“ gibt das Ende beim 294. Byte an

Zeitstempel

Zeitstempel des Ereignisses: „120330 17:54:46“

Ausführungszeit des Ereignisses

Die für die Ausführung des Ereignisses benötigte Zeit: „exec_time=28“

Fehlercode

Der Fehlercode lautet: „error_code=0“

Server-ID

Server-ID: „Server-ID 1“

Beachten Sie folgende Punkte:

1. Es ist unmöglich, sich vorzustellen, dass das MySQL-Protokoll jederzeit in den Zustand zurückversetzt werden kann. Diese Wiederherstellung hat eine Voraussetzung!
Es muss mindestens eine Datenbanksicherung vom Beginn der Protokollaufzeichnung an vorhanden sein. Das Wiederherstellen der Datenbank über Protokolle ist eigentlich nur ein Vorgang zum Wiedergeben vorheriger Vorgänge. Denken Sie nicht zu viel darüber nach.

Da es sich um einen Wiedergabevorgang handelt, müssen Sie vorsichtig sein. Wenn Sie die Wiederherstellung zweimal ausführen, entspricht dies einer zweimaligen Wiedergabe, und die Folgen sind vorstellbar.

Also:

1) Denken Sie daran, vor der Wiederherstellung Ihre Daten zu sichern.

2) Da viele Binärdateien vorhanden sind und der wiederherzustellende Datenumfang groß ist, können Sie bei der Wiederherstellung das Zusammenführen der Protokolldateien in Erwägung ziehen.

2. Aktivieren Sie die Binlog-Protokollierung

Um die Datenbank über Protokolle wiederherzustellen, müssen Sie zunächst log-bin=mysql-bin in der Datei my.cnf definieren . Der generierte Binlog-Protokollname wird nach mysql-bin benannt.

3. Wann wird eine neue Binlog-Datei generiert?

1) Fügen Sie während der Sicherung --flush-logs hinzu

2) Beim Neustart des MySQL-Dienstes

Besonderer Hinweis: Bei jedem Start von MySQL wird eine Datei ähnlich mysql-bin.00000n neu generiert. Wenn Ihr MySQL einmal am Tag neu gestartet werden muss, müssen Sie darauf achten, nicht die falsche Protokolldatei auszuwählen.

2. Einführung in das Binlog-Protokollformat

(1) MySQL-Binlog hat drei Formate: Statement, MiXED und ROW

1) Anweisung: Jede SQL-Anweisung, die Daten ändert, wird im Binärprotokoll aufgezeichnet.

Vorteile: Es ist nicht erforderlich, Änderungen in jeder Zeile aufzuzeichnen. Dadurch wird die Anzahl der Binlog-Protokolle reduziert, E/A gespart und die Leistung verbessert. (Wie viel Leistung und Protokollvolumen im Vergleich zu Zeilen eingespart werden kann, hängt von der SQL-Situation der Anwendung ab. Normalerweise ist die Protokollmenge, die durch Ändern oder Einfügen desselben Datensatzes im Zeilenformat generiert wird, immer noch geringer als die Protokollmenge, die durch eine Anweisung generiert wird. Wenn jedoch bedingte Aktualisierungsvorgänge, das Löschen ganzer Tabellen, Tabellenänderungen und andere Vorgänge berücksichtigt werden, generiert das ROW-Format eine große Menge an Protokollen. Wenn Sie also darüber nachdenken, Protokolle im ROW-Format zu verwenden, sollten Sie die tatsächliche Situation der Anwendung berücksichtigen, wie stark die Menge der generierten Protokolle ansteigt und welche IO-Leistungsprobleme dies mit sich bringt.)

Nachteile: Da nur die ausgeführten Anweisungen aufgezeichnet werden, müssen, damit diese Anweisungen auf dem Slave korrekt ausgeführt werden können, auch einige relevante Informationen jeder Anweisung während der Ausführung aufgezeichnet werden, um sicherzustellen, dass alle Anweisungen auf dem Slave dieselben Ergebnisse erzielen können wie bei der Ausführung auf dem Master. Darüber hinaus kann es bei der MySQL-Replikation, beispielsweise bei einigen spezifischen Funktionen, dazu kommen, dass der Slave mit dem Master übereinstimmt, was zu vielen damit verbundenen Problemen führt (beispielsweise bei der sleep()-Funktion, last_insert_id() und benutzerdefinierten Funktionen (udf)).

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)

Gleichzeitig erzeugt INSERT ...SELECT mehr Zeilensperren als RBR

2) Zeile: zeichnet die kontextbezogenen Informationen der SQL-Anweisung nicht auf, speichert nur, welcher Datensatz geändert wird

Vorteile: Binlog muss die kontextbezogenen Informationen der ausgeführten SQL-Anweisung nicht aufzeichnen, sondern nur aufzeichnen, wie der Datensatz geändert wurde. Daher zeichnet der Protokollinhalt auf Zeilenebene die Details jeder Datenänderungszeile klar auf. Und es wird kein Problem geben, dass die gespeicherte Prozedur, Funktion oder der Triggeraufruf und der Trigger in bestimmten Situationen nicht korrekt kopiert werden können

Nachteile: Wenn alle ausgeführten Anweisungen im Protokoll aufgezeichnet werden, werden sie als Änderungen an jeder Datensatzzeile aufgezeichnet, was zu einer großen Menge an Protokollinhalten führen kann. Wenn beispielsweise eine Aktualisierungsanweisung mehrere Datensätze ändert, wird jede Änderung im Binärprotokoll aufgezeichnet, was zu einer großen Menge an Binärprotokollen führt. Insbesondere bei der Ausführung von Anweisungen wie „Alter Table“ wird jeder Datensatz aufgrund der Änderung der Tabellenstruktur geändert, sodass jeder Datensatz der Tabelle im Protokoll aufgezeichnet wird.

3) Gemischte Ebene: Dies ist eine Kombination der beiden oben genannten Ebenen . Allgemeine Anweisungsänderungen verwenden das Anweisungsformat, um das Binärprotokoll zu speichern. Beispielsweise können einige Funktionen und Anweisungen den Master-Slave-Replikationsvorgang nicht abschließen, sodass das Zeilenformat zum Speichern des Binärprotokolls verwendet wird. MySQL unterscheidet das aufzuzeichnende Protokollformat je nach ausgeführter spezifischer SQL-Anweisung, d. h. es wird zwischen Anweisung und Zeile gewählt. Der Zeilenebenenmodus der neuen Version von MySQL wurde ebenfalls optimiert. Nicht alle Änderungen werden auf Zeilenebene aufgezeichnet. Wenn sich beispielsweise die Tabellenstruktur ändert, wird dies im Anweisungsmodus aufgezeichnet. Bei Anweisungen, die Daten ändern, wie etwa Aktualisieren oder Löschen, werden Änderungen an allen Zeilen weiterhin aufgezeichnet.

Gemischte Protokollbeschreibung:

Während des Slave-Protokollsynchronisierungsprozesses generiert das MIXED-Protokollformat für Zeitfunktionen wie jetzt eine entsprechende Zeitzeichenfolge unix_timestamp()*1000 im Protokoll. Wenn der Slave die Synchronisierung abgeschlossen hat, verwendet er die Zeit, zu der das sqlEvent auftritt, um die Datengenauigkeit 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.

(2) Grundkonfiguration und Formateinstellung von Binlog

1) Grundlegende Vorbereitung

Das Binlog-Protokollformat kann durch das Attribut binlog_format der MySQL-Datei my.cnf angegeben werden. Zum Beispiel die folgenden:
binlog_format = MIXED //binlog-Protokollformat
log_bin = Verzeichnis/mysql-bin.log //binlog-Protokollname
expire_logs_days = 7 //Bereinigungszeit für das Ablaufen des Binlogs
max_binlog_size 100m //Größe jeder Binlog-Protokolldatei

binlog-do-db=Der Name der zu sichernden Datenbank. Wenn Sie mehrere Datenbanken sichern möchten, setzen Sie diese Option einfach wiederholt.
binlog-ignore-db=Datenbanken, die nicht gesichert werden müssen, werden gelöscht. Wenn Sie mehrere Datenbanken sichern, setzen Sie diese Option einfach wiederholt.

2) Auswahl des Binlog-Protokollformats

Mysql verwendet standardmäßig das Statement-Log-Format und MIXED wird empfohlen.
Aufgrund einiger spezieller Verwendungszwecke können Sie die Verwendung von ROWED in Betracht ziehen, z. B. das Synchronisieren von Datenänderungen über Binlog-Protokolle, wodurch viele damit verbundene Vorgänge eingespart werden. Die Verarbeitung von Binlog-Daten wird sehr einfach, und im Vergleich zu gemischten Daten ist auch das Parsen sehr einfach (vorausgesetzt natürlich, dass der durch das erhöhte Protokollvolumen verursachte IO-Overhead im tolerierbaren Bereich liegt).

3) Auswahl des mysqlbinlog-Formats

MySQL-Auswahlprinzip für das Protokollformat: Wenn Sie INSERT, UPDATE, DELETE und andere direkte Tabellenoperationen verwenden, wird das Protokollformat gemäß der Einstellung von binlog_format aufgezeichnet. Wenn Sie Verwaltungsanweisungen wie GRANT, REVOKE, SET PASSWORD verwenden, wird ohnehin der SBR-Modus zum Aufzeichnen verwendet.

(3) MySQL Binlog-Protokollanalyse

Zeigen Sie das spezifische MySQL-Protokoll über den Befehl MysqlBinlog wie folgt an:

///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

ZEITSTEMPEL FESTLEGEN=1350355892/*!*/;

BEGINNEN

/*!*/;

# bei 1643330

#121016 10:51:32 Server-ID 1 End-Log-Pos 1643885 Abfrage Thread-ID = 272571 Exec-Zeit = 0 Fehlercode = 0

ZEITSTEMPEL FESTLEGEN=1350355892/*!*/;

In T_test einfügen….)

/*!*/;

# bei 1643885

#121016 10:51:32 Server-ID 1 end_log_pos 1643912 Xid = 0

BEGEHEN /*!*/;

///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

1. Zeit, Dinge anzufangen:

ZEITSTEMPEL FESTLEGEN=1350355892/*!*/;

BEGINNEN

2. SQLevent-Startpunkt

#bei 1643330: Der Startpunkt des Ereignisses, beginnend bei 1643330 Bytes.

3. Der Zeitpunkt, zu dem sqlevent auftritt

#121016 10:51:32: ist die Zeit, zu der das Ereignis aufgetreten ist.

4.Server-ID

Server-ID 1: die Server-ID des Masters

5. SQLevent-Endpunkt und aufgewendete Zeit, Fehlercode

end_log_pos 1643885: ist der Endpunkt des Ereignisses und endet mit 1643885 Bytes.

execTime 0: aufgewendete Zeit

error_code=0: Fehlercode

Xid: Ereignis zeigt die festgeschriebene XA-Transaktion an

3. Optimierungsbeschreibung des MySQL-Protokolls (Fokus auf Binlog-Protokoll)

Das MySQL-System ist hochgradig skalierbar und kann in einer Umgebung mit ausreichenden Hardwareressourcen effizient ausgeführt werden. Es läuft aber auch in einer Umgebung mit sehr wenigen Ressourcen gut.
Aber egal was passiert, ausreichende Hardwareressourcen sind immer hilfreich, um die MySQL-Leistung zu verbessern.

Im Folgenden konzentrieren wir uns auf die Analyse der Auswirkungen von MySQL-Protokollen (hauptsächlich Binlog) auf die Systemleistung und leiten entsprechende Optimierungsideen basierend auf den relevanten Merkmalen der Protokolle ab.

1) Auswirkungen von Protokollen auf die Leistung
Der direkte Leistungsverlust durch die Protokollaufzeichnung stellt die teuerste IO-Ressource im Datenbanksystem dar .

MySQL-Protokolle umfassen hauptsächlich Fehlerprotokolle (ErrorLog), Aktualisierungsprotokolle (UpdateLog) , Binärprotokolle (Binlog), Abfrageprotokolle (QueryLog), Protokolle für langsame Abfragen (SlowQueryLog) usw.
Besonderer Hinweis: Das Update-Log ist nur in älteren MySQL-Versionen verfügbar und wurde durch das Binär-Log ersetzt .

Standardmäßig öffnet das System nur das Fehlerprotokoll und schließt alle anderen Protokolle, um E/A-Verluste zu minimieren und die Systemleistung zu verbessern.
In etwas wichtigeren praktischen Anwendungsszenarien müssen Sie jedoch im Allgemeinen zumindest das Binärprotokoll öffnen, da dies die Grundlage für die inkrementelle Sicherung vieler MySQL-Speicher-Engines und die Grundvoraussetzung für die Replikation von MySQL ist.
Um die MySQL-Leistung weiter zu optimieren und langsam ausgeführte SQL-Anweisungen zu lokalisieren, öffnen viele Systeme manchmal auch Protokolle für langsame Abfragen, um SQL-Anweisungen aufzuzeichnen, deren Ausführungszeit einen bestimmten (von uns festgelegten) Wert überschreitet.

Im Allgemeinen ist die Abfrageprotokollierung in Produktionssystemen selten aktiviert. Denn nachdem das Abfrageprotokoll aktiviert wurde, wird jede in MySQL ausgeführte Abfrage im Protokoll aufgezeichnet, was zwar eine relativ große E/A-Belastung für das System mit sich bringt, der tatsächliche Nutzen jedoch nicht sehr groß ist. Im Allgemeinen wird das Protokoll nur für einen kurzen Zeitraum zur Analyse in einer Entwicklungs- und Testumgebung geöffnet, um herauszufinden, welche SQL-Anweisungen für bestimmte Funktionen verwendet werden.
Daher ist im MySQL-System das MySQL-Protokoll, das die Leistung beeinflusst (mit Ausnahme der Protokolle der einzelnen Speicher-Engines selbst), hauptsächlich Binlog.

2) Binlog-bezogene Parameter und Optimierungsstrategien

Schauen wir uns zunächst die relevanten Parameter von Binlog an. Sie können die relevanten Parameter von Binlog abrufen, indem Sie den folgenden Befehl ausführen.
Natürlich wird auch der Parameter „innodb_locks_unsafe_for_binlog“ angezeigt, der nur für die Innodb-Speicher-Engine gilt und sich auf Binlog bezieht:

mysql> Variablen wie „%binlog%“ anzeigen; 
+-----------------------------------------+----------------------+ 
| Variablenname | Wert | 
+-----------------------------------------+----------------------+ 
| binlog_cache_size | 16777216 | 
| binlog_checksum | CRC32 | 
| binlog_direct_non_transactional_updates | AUS | 
| binlog_error_action | IGNORE_ERROR | 
| binlog_format | GEMISCHT | 
| binlog_gtid_simple_recovery | AUS | 
| binlog_max_flush_queue_time | 0 | 
| binlog_order_commits | EIN | 
| binlog_row_image | VOLL | 
| binlog_rows_query_log_events | AUS | 
| binlog_stmt_cache_size | 32768 | 
| binlogging_impossible_mode | IGNORE_ERROR | 
| innodb_api_enable_binlog | AUS | 
| innodb_locks_unsafe_for_binlog | AUS | 
| max_binlog_cache_size | 18446744073709547520 | 
| max_binlog_size | 1073741824 | 
| max_binlog_stmt_cache_size | 18446744073709547520 | 
| vereinfachtes_binlog_gtid_recovery | AUS | 
| sync_binlog | 1 | 
+-----------------------------------------+----------------------+ 
19 Zeilen im Satz (0,00 Sek.)

„binlog_cache_size“ : Die Größe des Caches, der während einer Transaktion binäre Protokoll-SQL-Anweisungen enthält. Der Binärprotokollcache ist der Speicher, der jedem Client zugewiesen wird, wenn der Server die Transaktionsspeicher-Engine unterstützt und das Binärprotokoll aktiviert (Option --log-bin). Beachten Sie, dass jeder Client einen Binärprotokollcache-Speicherplatz einer festgelegten Größe zuweisen kann. Wenn in Ihrem System häufig Transaktionen mit mehreren Anweisungen vorkommen, können Sie versuchen, den Wert zu erhöhen, um eine bessere Leistung zu erzielen. Natürlich können wir den aktuellen Status von binlog_cache_size anhand der folgenden beiden Statusvariablen von MySQL bestimmen: Binlog_cache_use und Binlog_cache_disk_use.

„max_binlog_cache_size“ : Entspricht „binlog_cache_size“, stellt jedoch die maximale Cache-Speichergröße dar, die Binlog verwenden kann. Wenn wir eine Transaktion mit mehreren Anweisungen ausführen und max_binlog_cache_size nicht groß genug ist, meldet das System möglicherweise den Fehler „Die Transaktion mit mehreren Anweisungen erfordert mehr als ‚max_binlog_cache_size‘ Bytes an Speicher.“

„max_binlog_size“ : Der Maximalwert des Binlog-Protokolls, im Allgemeinen auf 512 M oder 1 G eingestellt, darf jedoch 1 G nicht überschreiten. Diese Größe kann die Größe des Binlogs nicht streng steuern, insbesondere wenn das Binlog kurz vor dem Ende steht und auf eine große Transaktion stößt. Um die Integrität der Transaktion sicherzustellen, kann das System das Protokoll nicht wechseln und kann nur alle SQL-Anweisungen der Transaktion im aktuellen Protokoll aufzeichnen, bis die Transaktion endet. Dies unterscheidet sich geringfügig vom Redo-Protokoll von Oracle, da das Redo-Protokoll von Oracle die Änderungen am physischen Speicherort der Datendatei aufzeichnet und auch Informationen zu Redo und Undo aufzeichnet. Daher ist es für Oracle nicht kritisch, ob sich dieselbe Transaktion im selben Protokoll befindet. Was MySQL in Binlog aufzeichnet, sind Informationen zu Änderungen in der Datenbanklogik, die MySQL als „Ereignis“ bezeichnet. Dabei handelt es sich eigentlich um die Abfrageanweisung wie DML, die Datenbankänderungen bewirkt.

"sync_binlog" : Dieser Parameter ist für das MySQL-System von entscheidender Bedeutung. Er wirkt sich nicht nur auf den Leistungsverlust von Binlog zu MySQL aus, sondern beeinträchtigt auch die Integrität der Daten in MySQL. Nachfolgend finden Sie Beschreibungen der verschiedenen Einstellungen für den Parameter „sync_binlog“:

sync_binlog=0 : Nachdem die Transaktion festgeschrieben wurde, führt MySQL keine Anweisungen zur Festplattensynchronisierung wie etwa fsync aus, um die Informationen in binlog_cache auf der Festplatte zu aktualisieren, sondern überlässt dem Dateisystem die Entscheidung, wann die Synchronisierung erfolgen soll, oder synchronisiert nur dann auf die Festplatte, wenn der Cache voll ist.

sync_binlog=n , nachdem jeweils n Transaktionen festgeschrieben wurden, führt MySQL eine Festplattensynchronisierungsanweisung wie beispielsweise fsync aus, um das Schreiben der Daten im binlog_cache auf die Festplatte zu erzwingen.

In MySQL ist die Systemvorgabeeinstellung sync_binlog=0, was bedeutet, dass keine obligatorischen Anweisungen zur Datenträgeraktualisierung ausgeführt werden. Die Leistung ist zu diesem Zeitpunkt am besten, aber das Risiko ist auch am größten. Denn wenn das System abstürzt, gehen alle Binlog-Informationen im Binlog-Cache verloren. Bei der Einstellung „1“ handelt es sich um die sicherste Einstellung, die jedoch auch den größten Leistungsverlust mit sich bringt. Denn wenn der Wert auf 1 gesetzt ist, geht selbst bei einem Systemabsturz höchstens eine nicht abgeschlossene Transaktion im binlog_cache verloren, was keine wesentlichen Auswirkungen auf die tatsächlichen Daten hat. Basierend auf Erfahrungen aus der Vergangenheit und entsprechenden Tests kann bei Systemen mit vielen gleichzeitigen Transaktionen der Unterschied in der Schreibleistung zwischen „sync_binlog“ auf 0 und „sync_binlog“ auf 1 gesetzt bis zu fünfmal oder mehr betragen.

Andere:

Die MySQL-Replikation wird tatsächlich dadurch erreicht, dass das Binärprotokoll auf der Masterseite mithilfe des IO-Threads über das Netzwerk auf die Slaveseite kopiert wird und die Protokolle im Binärprotokoll dann über den SQL-Thread analysiert und auf die Datenbank angewendet werden. Daher hat die Größe des Binärprotokolls direkte Auswirkungen auf den IO-Thread und das Netzwerk zwischen dem Master- und dem Slave-Ende.

Die Menge des in MySQL generierten Binlogs kann nicht geändert werden. Solange unsere Abfrage die Daten in der Datenbank ändert, muss das der Abfrage entsprechende Ereignis im Binlog aufgezeichnet werden. Gibt es für uns also keine Möglichkeit, die Replikation zu optimieren? Natürlich nicht. In der MySQL-Replikationsumgebung gibt es tatsächlich 8 Parameter, mit denen wir die Datenbank oder Tabelle steuern können, die repliziert oder ignoriert werden soll. Diese sind:

Binlog_Do_DB : Legen Sie fest, welche Datenbanken (Schemas) Binlog aufzeichnen müssen;

Binlog_Ignore_DB : Legen Sie fest, welche Datenbanken (Schemas) Binlog nicht aufzeichnen sollen;

Replicate_Do_DB : Legen Sie die zu replizierende Datenbank (Schema) fest. Mehrere DBs werden durch Kommas (",") getrennt.

Replicate_Ignore_DB : Legen Sie die Datenbank (Schema) fest, die ignoriert werden kann.

Replicate_Do_Table : Legen Sie die zu replizierende Tabelle fest.

Replicate_Ignore_Table : Legen Sie die Tabelle fest, die ignoriert werden kann.

Replicate_Wild_Do_Table : Dieselbe Funktion wie Replicate_Do_Table, kann aber mit Platzhaltern festgelegt werden;

Replicate_Wild_Ignore_Table : Dieselbe Funktion wie Replicate_Ignore_Table, kann aber mit Platzhaltern festgelegt werden;

Durch die oben genannten acht Parameter können wir die Menge an Binlog vom Master zum Slave entsprechend dem tatsächlichen Bedarf leicht so gering wie möglich halten und so den Netzwerkverkehr vom Master zum Slave reduzieren, das IO-Volumen des IO-Threads verringern und die Anzahl der SQL-Threads verringern, die SQL analysieren und anwenden, wodurch letztendlich das Datenverzögerungsproblem auf dem Slave verbessert wird.

Tatsächlich werden die ersten beiden der oben genannten acht Parameter auf der Master-Seite eingestellt, während die letzten sechs Parameter auf der Slave-Seite eingestellt werden. Obwohl die ersten beiden Parameter und die letzten sechs Parameter keinen direkten Funktionszusammenhang haben, können sie ähnliche Funktionen zur Optimierung der MySQL-Replikation aktivieren. Natürlich gibt es einige Unterschiede, die Hauptunterschiede sind wie folgt:

Wenn die ersten beiden Parameter auf der Master-Seite festgelegt werden, wird nicht nur das durch die Binlog-Datensätze auf der Master-Seite verursachte IO-Volumen reduziert, sondern der IO-Thread auf der Master-Seite kann auch die Anzahl der Binlog-Lesevorgänge reduzieren, und die an den IO-Thread auf der Slave-Seite übergebene Binlog-Menge ist natürlich geringer. Der Vorteil hierbei besteht darin, dass die Netzwerk-E/A reduziert werden kann, das E/A-Volumen des Slave-E/A-Threads verringert wird und die Arbeitslast des Slave-SQL-Threads reduziert wird, wodurch die Replikationsleistung maximal optimiert wird. Natürlich bringt die Einstellung auf der Masterseite einige Nachteile mit sich, da MySQL anhand der durch die Abfrage, die das Ereignis generiert hat, geänderten Daten bestimmt, ob ein Ereignis repliziert werden soll.

Die Datenbank, in der die Abfrage ausgeführt wird, basiert nicht auf dem Standardschema, sondern auf dem Standardschema, in dem die Abfrage ausgeführt wird, d. h. der Datenbank, die bei unserer Anmeldung angegeben wird, oder der Datenbank, die beim Ausführen von „USE DATABASE“ angegeben wird. Nur wenn der aktuelle Standard-DB und der in der Konfiguration festgelegte DB vollständig übereinstimmen, liest der IO-Thread das Ereignis in den IO-Thread des Slaves. Wenn sich daher die Standard-Datenbank und die zu replizierende Datenbank im System unterscheiden und die Daten einer bestimmten Tabelle in der zu replizierenden Datenbank geändert werden, wird das Ereignis nicht auf den Slave repliziert, was dazu führt, dass die Daten auf der Slave-Seite nicht mit den Daten auf dem Master übereinstimmen. Wenn die Daten in einem Schema, das nicht repliziert werden muss, unter dem Standardschema geändert werden, werden sie auf die Slave-Seite repliziert. Wenn die Slave-Seite dieses Schema nicht hat, schlägt die Replikation fehl und wird beendet.

Wenn die folgenden sechs Parameter auf der Slave-Seite festgelegt sind, ist die Leistungsoptimierung möglicherweise etwas schlechter als auf der Master-Seite, da alle Ereignisse, die repliziert werden müssen oder nicht, vom IO-Thread auf die Slave-Seite gelesen werden, wodurch nicht nur das Netzwerk-IO-Volumen erhöht wird, sondern auch das RelayLog-Schreibvolumen für den IO-Thread der Slave-Seite. Der Umfang der Protokollanwendungen durch den Slave-SQL-Thread auf der Slave-Seite kann jedoch immer noch reduziert werden. Auch wenn die Leistung etwas schlechter ist, kann durch die Einrichtung eines Replikationsfiltermechanismus auf der Slave-Seite sichergestellt werden, dass es zu keinen Inkonsistenzen in den Slave- und Master-Daten oder Replikationsfehlern kommt, die durch Probleme mit dem Standardschema verursacht werden.

3) Langsames Abfrageprotokoll Abfrageprotokollbezogene Parameter und Verwendungsvorschläge
Werfen wir einen Blick auf die relevante Parameterkonfiguration von SlowQueryLog. Manchmal müssen wir das langsame Abfrageprotokoll (SlowQueryLog) öffnen, um die weniger effizienten Abfrageanweisungen im System zu lokalisieren. Wir können die relevanten Einstellungen des System-Langsamabfrageprotokolls wie folgt anzeigen:

mysql> Variablen wie „log_slow%“ anzeigen; 
+------------------+--------+ 
| Variablenname | Wert | 
+------------------+--------+ 
| log_slow_queries | EIN | 
+------------------+--------+ 
1 Zeile im Satz (0,00 Sek.) 
 
mysql> Variablen wie „long_query%“ anzeigen; 
+-----------------+--------+ 
| Variablenname | Wert | 
+-----------------+--------+ 
| lange Abfragezeit | 1 | 
+-----------------+--------+ 
1 Zeile im Satz (0,01 Sek.) 

Der Parameter „log_slow_queries“ zeigt an, ob das System die Funktion SlowQueryLog aktiviert hat, und der Parameter „long_query_time“ gibt an, um wie viel Uhr die Ausführungszeit der Abfrage den SlowQuery-Datensatz der aktuellen Systemeinstellung überschreitet. In der von MySQL AB veröffentlichten MySQL-Version beträgt die kürzeste langsame Abfragezeit, die SlowQueryLog festlegen kann, 1 Sekunde, was in einigen Fällen möglicherweise nicht vollständig unseren Anforderungen entspricht. Wenn Sie das Zeitlimit für langsame Abfragen weiter verkürzen möchten, können Sie den von Percona bereitgestellten Microslow-Patch (mslPatch genannt) verwenden, um diese Einschränkung zu umgehen. mslpatch kann nicht nur die langsame Abfragezeit auf Millisekunden reduzieren, sondern auch das aufgezeichnete SQL anhand bestimmter Regeln filtern, z. B. indem nur die SlowQuery aufgezeichnet wird, die eine bestimmte Tabelle und andere Zusatzfunktionen betrifft.

Die Gesamtauswirkungen des Einschaltens der Funktion SlowQueryLog auf die Systemleistung sind nicht so groß wie die von Binlog. Schließlich ist die Datenmenge in SlowQueryLog relativ klein, sodass auch der damit verbundene IO-Verlust relativ klein ist. Das System muss jedoch die Ausführungszeit jeder Abfrage berechnen, sodass immer ein gewisser Verbrauch entsteht, hauptsächlich CPU-Verbrauch. Wenn Ihr System über ausreichende CPU-Ressourcen verfügt, müssen Sie sich über diesen kleinen Verlust keine Sorgen machen. Schließlich kann es uns eine größere Leistungsoptimierung bringen. Wenn unsere CPU-Ressourcen jedoch relativ knapp sind, können wir diese Funktion die meiste Zeit vollständig ausschalten und müssen nur gelegentlich die Funktion SlowQueryLog aktivieren, um möglicherweise langsame Abfragen zu lokalisieren.

Andere MySQL-Protokolle werden selten verwendet (QueryLog) oder haben wenig Einfluss auf die Leistung, daher werden wir sie hier nicht im Detail analysieren.

Die obige Zusammenfassung der Verwendung des Binlog-Protokolls in der MySQL-Datenbank (unbedingt lesen) ist der gesamte Inhalt, den der Herausgeber mit Ihnen teilt. Ich hoffe, es kann Ihnen als Referenz dienen. Ich hoffe auch, dass Sie 123WORDPRESS.COM unterstützen werden.