Einführung in Fehlerprotokolle, Binlog-Protokolle, Abfrageprotokolle und langsame Abfrageprotokolle in MySQL

Vorwort

Datenbankprotokolle sind eine leistungsstarke Grundlage, um Datenbankadministratoren dabei zu helfen, verschiedene Ereignisse zu verfolgen und zu analysieren, die in der Datenbank aufgetreten sind. MySQL stellt Fehlerprotokolle und Binlog-Protokolle bereit (binär). Protokoll), Untersuchungsprotokoll, langsames Abfrageprotokoll. Hier möchte ich die folgenden Fragen beantworten: Was ist der Zweck jedes Protokolls? Wie kontrolliere ich diese Protokolle? Wie nutzt man die in diesen Protokollen bereitgestellten Informationen?

Fehlerprotokoll

1. Fehlerprotokollfunktion

Fehler Das Protokoll zeichnet Informationen darüber auf, wann MySQL gestartet und gestoppt wird, sowie alle schwerwiegenden Fehler, die während des Serverbetriebs auftreten. Wenn die Datenbank aufgrund eines Fehlers nicht gestartet werden kann, z. B. MySQL startet abnormal, können wir zuerst dieses Protokoll überprüfen. In MySQL kann das Fehlerprotokoll-Protokoll (und andere Protokolle) nicht nur in Dateien, sondern natürlich auch in Datentabellen gespeichert werden. Was die Implementierung betrifft, studiert der Autor auch ... ·

2. Fehlerprotokollsteuerung und -verwendung

1.Konfiguration

Übergeben Sie log-error=[Dateiname] an die Konfiguration (in der MySQL-Konfigurationsdatei). Wenn Dateiname nicht angegeben ist, verwendet mysqld den Fehlerprotokollnamen host_name.err (Hostname ist der Hostname) und Der Standardwert ist: Die Protokolldatei wird in das durch den Parameter datadir angegebene Verzeichnis geschrieben (das Verzeichnis, in dem die Daten gespeichert werden).

Zum Beispiel verwende ich die integrierte WampServer-Umgebung lokal

wo log-error = D:/wamp/logs/mysql.log

Wie unten gezeigt

Wenn ich einen Protokollfehler auskommentiere (#log-error=D:/wamp/logs/mysql.log ) , starten Sie den Server neu. Sie können die Fehlerprotokolldatei in dem durch datadir

2. Sehen Sie sich das Fehlerprotokoll an

Das Format des Fehlerprotokolls: Uhrzeit [Fehlerstufe] Fehlermeldung

Wenn Sie Schwierigkeiten haben, das Fehlerprotokoll über die MySQL-Konfigurationsdatei zu finden, können Sie den Speicherort des Fehlerprotokolls über Befehle auf dem Client anzeigen

Imperative Ausdrücke verwenden: Variablen wie „log_error“ anzeigen;

Das Folgende ist das MySQL-Startprotokoll

Binärprotokoll

1. Funktion

Binärprotokoll (auch Binlog-Protokoll genannt) zeichnet alle DDL-Anweisungen (Datendefinitionssprache) und DML-Anweisungen (Datenmanipulationssprache) auf, enthält jedoch keine Datenabfrageanweisungen. Anweisungen werden in Form von „Ereignissen“ gespeichert. , das den Prozess von Datenänderungen beschreibt. Die beiden Hauptfunktionen dieses Protokolls sind: Datenwiederherstellung und Datenreplikation.

Datenwiederherstellung: MySQL selbst verfügt über Datensicherungs- und Wiederherstellungsfunktionen. Beispielsweise führen wir täglich um 12:00 Uhr eine Datensicherung durch. Wenn eines Tages um 13:00 Uhr die Datenbank ausfällt und der Datenbankinhalt verloren geht. Wir können dieses Problem durch Binärprotokolle lösen. Die Lösung besteht darin, zuerst die Datensicherungsdatei um 12:00 Uhr des Vortages in der Datenbank wiederherzustellen und dann mithilfe des Binärprotokolls den der Datenbank von 12:00 Uhr des Vortages auf 13:00 Uhr heute.

Datenreplikation: MySQL unterstützt die Datenreplikationsfunktion zwischen Master- und Slave-Servern und verwendet diese Funktion, um den Redundanzmechanismus der Datenbank zu implementieren, um die Verfügbarkeit sicherzustellen die Datenbank und verbessern die Leistung der Datenbankvirtue. MySQL implementiert die Datenübertragung über Binärprotokolle. Der binäre Protokollinhalt auf dem Master-Server wird an jeden Slave-Server gesendet und auf jedem Slave-Server ausgeführt, wodurch die Datenkonsistenz zwischen Master- und Slave-Server sichergestellt wird.

2. Binärprotokollsteuerung und -verwendung

1. Aktivieren

MySQL zeichnet standardmäßig keine Binärprotokolle auf. Wie kann ich die binäre Protokollierungsfunktion von MySQL aktivieren?

Wir können MySQL steuern, um die binäre Protokollierungsfunktion über die MySQL-Konfigurationsdatei zu starten. Starten Sie das MySQL-Binärprotokoll, indem Sie den Parameter log-bin=[base_name] ändern. mySQL zeichnet die geänderten Datenbankinhaltsanweisungen bei jedem Start in einer Protokolldatei mit dem Namen „base_name-bin.0000x“ auf, wobei „bin“ für „Binary“ und das Suffix „00000x“ für die Reihenfolge der binären Protokolldatei steht MySQL erhöht die Reihenfolge der Protokolldateien automatisch um 1. Wenn base_name nicht definiert ist, verwendet MySQL den durch den Parameter pid-file festgelegten Wert als Basisnamen der binären Protokolldatei.

Wenn ich die Log-Bin-Datei beispielsweise als „mybinlog“ bezeichne, befindet sie sich in D:/wamp/bin/mysql /mysql5 Generieren Sie im Verzeichnis .6.17/data die binäre Protokolldatei mybinlog.00000x.

Die binäre Protokolldatei ist wie unten dargestellt

Überprüfen Sie, ob das Bin-Log-Protokoll aktiviert ist, indem Sie Show-Variablen wie „log_bin“ verwenden.

2. Ansicht

MySQL-Binärprotokolle sind hauptsächlich für den internen MySQL-Gebrauch bestimmt und nicht für Datenbankadministratoren zum Lesen und Verwenden. Daher besteht ein wichtiger Unterschied zwischen Binärprotokollen und anderen Protokollen darin, dass sie Binärprotokolle sind Das Format der Datei ist kein Textformat und ihr Inhalt kann nicht direkt über Notepad angezeigt werden. Um die Administratorverwaltung zu erleichtern, stellt MySQL das Tool mysqlbinlog zum Anzeigen des Binärprotokollinhalts bereit.

Zum Beispiel: mysqlbinlog D:wampbinmysqlmysql5.6.17datamybinlog.000003

Die Ausführung Das Ergebnis ist wie folgt:

Jetzt machen wir einen Test, um zu sehen, ob das Bin-Protokoll meinen Vorgang zur Aktualisierung der Datenbank aufzeichnet

Zum Beispiel ändere ich die ID1 der Zeile mit ID2=2 in der Datentabelle t2 in 5. Dann frage ich die binäre Protokolldatei ab, um zu sehen, ob meine Vorgänge aufgezeichnet werden.

Die Ergebnisse sind offensichtlich. Die Binärdatei zeichnet auf, dass ich die Daten in der Datenbank geändert habe. Was meine Abfrageanweisung betrifft, wird sie nicht aufgezeichnet.

3. Löschen von Binärprotokollen

Für ein relativ ausgelastetes System, da heutzutage täglich eine große Anzahl von Protokollen generiert wird sind lang. Wenn die Zeit unklar ist (oder übertragen wird), wird viel Speicherplatz verschwendet. Daher ist das regelmäßige Löschen von Protokollen ein wichtiger Teil der DBA-Wartung der MYSQL-Datenbank.

1. Führen Sie den Reset-Master-Befehl über den Reset-Master-Befehl

aus, wodurch alle Binlog-Protokolle gelöscht werden. Neue Protokolldateinummern beginnen bei 000001.

2. Löschen Sie „xxxxxx“, indem Sie „Master-Protokolle löschen“ für „Basisname.xxxxxx“ über den Befehl „Master-Protokolle löschen“ ausführen

'Alle Protokolle vor der Nummer. Im Folgenden werde ich alle Protokolle vor mybinlog.000003 löschen.

Wie unten gezeigt:

3. Verwenden Sie den Befehl „Master-Protokolle vor „Zeit“ löschen“

, um Master-Protokolle vor „Zeit“ löschen“ bedeutet, alle Protokolle vor „Zeit“ zu löschen. Zeit' . Um beispielsweise alle Protokolle vor dem 01.04.2016 um 00:00:00 Uhr zu löschen, lautet der Befehl wie folgt:

Master-Protokolle vorher löschen '2016 -04-01 00:00:00';

4

Geben Sie die Anzahl der Tage für den Protokollablauf an, indem Sie den Parameter „expire_logs_days=#“ festlegen. Nach der angegebenen Anzahl von Tagen wird das Protokoll automatisch gelöscht Verfahren. Wenn Sie beispielsweise „expire_logs_day=3“ festlegen, bedeutet dies, dass es nach 3 Tagen automatisch gelöscht wird.

4. Beschreibung wichtiger Binärprotokollparameter

max_binlog_size: Gibt den Maximalwert einer einzelnen Binärprotokolldatei an , wenn dieser Wert überschritten wird, wird eine neue binäre Protokolldatei mit dem Suffix +1 generiert und in der .index-Datei aufgezeichnet.

binlog_cache_size: Cache-Bereichsgröße

sync_binlog: Zeigt an, dass der Cache mit der Festplatte synchronisiert wird, ohne dass viele Male in den Cache geschrieben wird. Wenn N auf 1 gesetzt ist, bedeutet dies, dass die Binärdatei synchron auf die Festplatte geschrieben wird. Die Standardsystemeinstellung in MySQL ist sync_binlog=0, was bedeutet, dass keine obligatorischen Festplattenaktualisierungsanweisungen ausgeführt werden. Die Leistung ist zu diesem Zeitpunkt am besten, aber das Risiko ist auch am größten. Denn sobald das System abstürzt, gehen alle Binlog-Informationen im binlog_cache verloren. Bei der Einstellung „1“ ist dies die sicherste Einstellung, weist jedoch den größten Leistungsverlust auf. Denn bei der Einstellung 1 geht selbst bei einem Systemabsturz höchstens eine nicht abgeschlossene Transaktion im binlog_cache verloren, ohne dass dies wesentliche Auswirkungen auf die tatsächlichen Daten hat.

binlog-do-db: Welche Datenbanktage aufgezeichnet werden müssen, der Standardwert ist leer, Dies zeigt an, dass alle Bibliotheksprotokolle mit dem Binärprotokoll synchronisiert sind.

binlog-ignore-db: Tage, an denen Datenbanken ignoriert werden müssen

log-slave-update: Muss wann konfiguriert werden Aufbau einer Master-Slave-Datenbank

binglog_format: Optionale Werte umfassen Anweisung (Aufzeichnen logischer SQL-Anweisungen), Zeile (Aufzeichnen von Tabellenzeilenänderungen), gemischt

5. Verwenden Sie Binärprotokolle für die Datenwiederherstellung

Wie bereits erwähnt, reichen Binärdaten allein oft nicht aus, wenn die Daten abnormal sind und Sie sie zu einem bestimmten Zeitpunkt wiederherstellen möchten Benötigt werden außerdem die Daten, die vor diesem Zeitpunkt gesichert wurden.

Um die Beobachtung des Effekts zu erleichtern, habe ich jetzt meine Datenbank gesichert. Zu diesem Zeitpunkt lauten die Daten in der Datentabelle t1 wie folgt:

Von nun an muss ich einige Vorgänge an den Daten ausführen, z. B. Aktualisierungs- oder Einfügevorgänge t1-Daten sind wie unten gezeigt

Zu diesem Zeitpunkt, wenn Es passiert etwas sehr Unglückliches und ein Hacker bricht ein. Alle Daten in meiner t1-Tabelle wurden gelöscht. Wie erhalte ich also die Daten, bevor der Hacker sie gelöscht hat?

Schritt 1: Ich muss meine Daten wiederherstellen Daten, die ich gesichert habe, das Ergebnis nach der Wiederherstellung ist wie folgt:

Schritt 2: Ich muss meine binäre Protokolldatei verwenden, um alle Datenvorgänge vom Moment der Datensicherung bis zum Moment vor dem Hacken wiederherzustellen

Ausführen: mysqlbinlog D:wampbinmysqlmysql5.6.17data
mybinlog.000004

Bei der Analyse des Binärprotokolls haben wir festgestellt, dass unsere Daten in Zeile „bei 637“ von Hackern angegriffen wurden, sodass wir nur noch … Alle vorherigen Vorgänge in Zeile 637 wiederherstellen können ignoriert werden.

So können wir den folgenden Befehl ausführen, um unsere Daten wiederherzustellen:

mysqlbinlog D:wampbinmysqlmysql5.6.17datamybinlog.000004 --stop-pos=637|mysql -uroot -p**dequan

Dann schauen wir uns die Daten unserer t1-Tabelle an

Endlich ist es geschafft, aber dies erinnert uns auch daran, dass wir zur Erleichterung der Datenwiederherstellung nicht nur das Binärprotokoll aktivieren, sondern auch speichern müssen die Daten regelmäßig.

Zusätzliche Informationen zum Binärprotokoll:

1 Wir können die aktuelle Situation auch mithilfe der einsehen Befehl zum Anzeigen binärer Protokolle Welche anderen binären Protokolle gibt es?

2. Wir können Ereignisse protokollieren, die durch Show Binlog Events

Show Binlog Events aufgezeichnet wurden Zeigt alle protokollierten Ereignisse an. Wenn Sie ein bestimmtes binäres Protokollaufzeichnungsereignis abfragen möchten, können Sie am Ende + „Protokollname“ hinzufügen, wie unten gezeigt:

Abfrageprotokoll

1. Funktionsbeschreibung

Das Abfrageprotokoll zeichnet alle Anweisungen vom Client auf. Sie können den Speicherort über log=[file_name] angeben. Wenn der Wert file_name nicht angegeben ist, wird das Protokoll in das Verzeichnis geschrieben, in dem sich datadir befindet. Der Standarddateiname lautet host_name.log Größere Auswirkungen auf die Systemleistung werden im Allgemeinen nicht aktiviert, daher werde ich hier nicht auf Details eingehen.

Langsames Abfrageprotokoll

1. Funktionsbeschreibung

Langsames Abfrageprotokoll ist Zeichnen Sie alle SQL-Anweisungsprotokolle auf, deren Ausführungszeit den Parameter long_query_time (Einheit: Sekunden) überschreitet. Die Wartezeit auf den Erhalt der Tabellensperre wird nicht als Ausführungszeit gezählt. Wir können die Protokollfunktion für langsame Abfragen über die Option log-slow-queries=[file_name] aktivieren. Wenn file_name nicht angegeben ist, befindet sich das Protokollverzeichnis wie beim vorherigen Protokoll im Verzeichnis „Datedir“ und der Standardname lautet host_name-slow.log.

2. Lesen des langsamen Abfrageprotokolls

Fragen Sie den Öffnungsstatus der langsamen Abfrage ab

Fügen Sie den folgenden Code in die Konfigurationsdatei ein , Langsame Abfrage aktivieren

#Langsame Abfrage aktivieren
slow_query_log=ON
slow_query_log_file=D:/wamp/logs/myslowquery.log
long_query_time=1.5

(Einige Stellen sagen, dass das langsame Abfrageprotokoll durch log-slow-queries=[file_name] angegeben wird, aber ich habe es versucht und beim Starten von MySQL wird hier ein Fehler gemeldet Es kann sein, dass ich mich in Win Operate unter dem System befinde, oder vielleicht ist mein lokales MySQL mit der integrierten WampServer-Umgebung installiert, oder vielleicht ist die verwendete Version eine andere)

Überprüfen Sie die Einstellung für die langsame Abfragezeit

Wenn Sie die langsame Abfragezeit ändern, können Sie set long_query_time=1.5;

verwenden Um den Effekt besser erkennen zu können, ändern wir das Zeitlimit für langsame Abfragen auf 0,15, schreiben dann eine SQL mit einer Zeit von mehr als 0,15 und überprüfen schließlich das Protokoll, um festzustellen, ob die SQL-Anweisung aufgezeichnet wurde.

Stellen Sie die langsame Abfragezeit ein und führen Sie die entsprechende SQL-Anweisung aus

Ich habe oben insgesamt 3 SQL-Anweisungen ausgeführt. Schauen wir uns nun das langsame Abfrageprotokoll an:

Es werden nur die SQL-Anweisungen aufgezeichnet, deren Abfrage lange dauert.

Das Obige ist die Einführung des Fehlerprotokolls, des Binlog-Protokolls, des Abfrageprotokolls und des langsamen Abfrageprotokolls in MySQL . Weitere verwandte Inhalte finden Sie auf der chinesischen PHP-Website (www.php.cn)!