Detaillierte Erklärung von MySQL-Transaktionen und MySQL-Protokollen

Transaktionale Merkmale

1. Atomarität: Nach dem Start einer Transaktion müssen alle Vorgänge abgeschlossen sein oder überhaupt nicht ausgeführt werden. Es ist unmöglich, dass die Transaktion mittendrin stecken bleibt.

2. Konsistenz: Die Integritätsbeschränkungen der Datenbank werden vor und nach dem Start und Ende der Transaktion nicht verletzt. Wenn A beispielsweise Geld an B überweist, ist es für A nicht möglich, das Geld abzuziehen, B erhält es jedoch nicht.

3. Isolation: Nur eine Transaktion darf gleichzeitig dieselben Daten anfordern und es gibt keine Interferenzen zwischen verschiedenen Transaktionen. Wenn A beispielsweise Geld von einer Bankkarte abhebt, kann B kein Geld auf diese Karte überweisen, bevor A den Abhebungsvorgang abgeschlossen hat.

4. Dauerhaftigkeit: Nachdem die Transaktion abgeschlossen ist, werden alle durch die Transaktion an der Datenbank vorgenommenen Aktualisierungen in der Datenbank gespeichert und können nicht zurückgesetzt werden.

Probleme mit der Transaktionsparallelität

1. Dirty Read: Transaktion A liest die von Transaktion B aktualisierten Daten und dann führt B den Vorgang zurück, sodass die von A gelesenen Daten schmutzige Daten sind

2. Nicht wiederholbares Lesen: Transaktion A liest dieselben Daten mehrmals. Während Transaktion A mehrere Daten liest, aktualisiert und speichert Transaktion B die Daten. Dies führt zu inkonsistenten Ergebnissen, wenn Transaktion A dieselben Daten mehrere Male liest.

3. Phantomlesen: Systemadministrator A ändert die Noten aller Studenten in der Datenbank von bestimmten Punktzahlen in ABCDE-Noten, aber Systemadministrator B fügt zu diesem Zeitpunkt einen Datensatz mit einer bestimmten Punktzahl ein. Wenn Systemadministrator A die Änderung abgeschlossen hat, stellt er fest, dass immer noch ein Datensatz vorhanden ist, der nicht geändert wurde, als ob eine Illusion aufgetreten wäre. Dies wird als Phantomlesen bezeichnet.

Zusammenfassung: Nicht wiederholbare Lesevorgänge und Phantom-Lesevorgänge können leicht verwechselt werden. Bei nicht wiederholbaren Lesevorgängen stehen Änderungen im Mittelpunkt, während bei Phantom-Lesevorgängen Hinzufügungen oder Löschungen im Mittelpunkt stehen. Um das Problem nicht wiederholbarer Lesevorgänge zu lösen, müssen Sie nur die Zeilen sperren, die die Bedingungen erfüllen. Um das Problem von Phantom-Lesevorgängen zu lösen, müssen Sie die Tabelle sperren.

Transaktionsisolierung

MySQL verwendet nach der Serialisierung standardmäßig „wiederholbares Lesen“

#Überprüfen Sie die globale TransaktionsisolationsstufeSELECT @@global.tx_isolation;
#Überprüfen Sie die aktuelle Transaktionsisolationsstufe der SitzungSELECT @@session.tx_isolation; 
#Überprüfen Sie die aktuelle TransaktionsisolationsstufeSELECT @@tx_isolation;
#Legen Sie die globale Isolationsstufe fest. Legen Sie die globale Transaktionsisolationsstufe fest. Lesen Sie fest.
#Stellen Sie die aktuelle Sitzungsisolationsstufe ein. Stellen Sie die Transaktionsisolationsstufe der Sitzung ein. Lesen Sie fest.

Serialisierbarkeit ist die höchste Isolationsebene und löst das Phantomleseproblem, indem sie eine Reihenfolge der Transaktionen erzwingt, in der sie nicht miteinander in Konflikt geraten können. Kurz gesagt: Es wird bei jedem Lesen einer Datenzeile eine gemeinsame Sperre hinzugefügt, was auf dieser Ebene zu zahlreichen Zeitüberschreitungen und Sperrkonflikten führen kann.

Gemeinsames Schloss: Der Codename des gemeinsamen Schlosses lautet S

Zusammensetzung des MySQL-Protokolldateisystems

1. Zusammensetzung des MySQL-Protokolldateisystems

a. Fehlerprotokoll: zeichnet Probleme auf, die beim Starten, Ausführen oder Stoppen von mysqld auftreten.
b. Allgemeines Protokoll: zeichnet die hergestellten Clientverbindungen und ausgeführten Anweisungen auf.
c. Update-Log: zeichnet Anweisungen auf, die Daten ändern. Dieses Protokoll wird in MySQL 5.1 nicht mehr verwendet.
d. Binärprotokoll: zeichnet alle Anweisungen auf, die Daten ändern. Wird auch für die Replikation verwendet.
e. Protokoll langsamer Abfragen: zeichnet alle Abfragen auf, deren Ausführung länger als „long_query_time“ Sekunden dauert, oder Abfragen, die keine Indizes verwenden.
f. Innodb-Protokoll: Innodb-Redo-Protokoll

Binärprotokoll (binlog):

Enthält alle Daten, die aktualisiert wurden oder möglicherweise aktualisiert wurden (z. B. ein DELETE, das keine Zeilen ergab).

Enthält Informationen zur Ausführungszeit jeder Anweisung, die die Datenbank aktualisiert (DML).

Schließt keine Anweisungen ein, die keine Daten ändern. Wenn Sie diese Option aktivieren möchten, müssen Sie die allgemeine Protokollierungsfunktion aktivieren.

Der Hauptzweck besteht darin, die Datenbank so weit wie möglich bis zum Zeitpunkt des Datenbankausfalls wiederherzustellen, da das Binärprotokoll alle nach der Sicherung vorgenommenen Aktualisierungen enthält

Wird verwendet, um alle Anweisungen auf dem Master-Replikationsserver zu protokollieren, die an den Slave-Server gesendet werden.

Durch Aktivieren dieser Option wird die Datenbankleistung um 1 % reduziert, die Datenbankintegrität bleibt jedoch gewährleistet. Bei wichtigen Datenbanken lohnt es sich, die Leistung gegen die Integrität einzutauschen. Es ähnelt in gewisser Weise der Aktivierung des Archivmodus in Oracle.

Variablen wie „%version%“ anzeigen; 
Variablen wie '%log_bin%' anzeigen; //Ob Binlog aktiviert werden soll
Variablen wie „%binlog%“ anzeigen; // Binlog-bezogene Parameter Variablen wie „%datadir%“ anzeigen; // Datendateiverzeichnis, in dem Protokolle standardmäßig gespeichert werden #Bearbeiten Sie my.cnf, um den Speicherort des Binärprotokolls festzulegen (Hinweis: Nach der Konfiguration des Binärprotokollpfads und des Dateinamens wird die Systemvariable log_bin automatisch auf „on“ gesetzt.) 

log_bin=/var/lib/mysql/binarylog/binlog 

#Wenn Sie in my.cnf nur log_bin festlegen, aber keinen Dateinamen angeben, starten Sie die Datenbank neu. Sie werden feststellen, dass der Name der Binärprotokolldatei das Format ${hostname}-bin #Switch log show master status hat.
Protokolle spülen;
Masterstatus anzeigen;

Bei jedem Neustart des MySQL-Dienstes wird eine neue Binärprotokolldatei generiert, was einem Binärprotokollwechsel entspricht. Wenn Sie die binäre Protokollierung umstellen, werden Sie sehen, dass diese Zahlen weiter ansteigen. Zusätzlich zu diesen binären Protokolldateien wird auch eine Datei DB-Server-bin.index generiert. In dieser Datei wird eine Liste aller binären Protokolldateien gespeichert, die auch als Index der binären Dateien bezeichnet wird.

Binärprotokolle können manuell über Befehle gelöscht oder auf automatische Bereinigung eingestellt werden.

Binärprotokolle anzeigen;
mysql> Binärprotokolle nach „DB-Server-bin.000002“ löschen;

Binärprotokolle auf xxx bereinigen; bedeutet, alle Binärprotokolldateien vor einem bestimmten Protokoll zu löschen. Dieser Befehl ändert die relevanten Daten im Index: Bereinigen Sie Binärprotokolle vor „10.03.2017, 10:10:00 Uhr“. Löschen Sie die Binärprotokolldateien vor einem bestimmten Zeitpunkt.
Master-Protokolle vor date_sub( now() ), Intervall 7 Tage bereinigen; Binärprotokolldateien vor 7 Tagen löschen; Master zurücksetzen; Alle Binärprotokolldateien löschen (derzeit besteht keine Master-Slave-Replikationsbeziehung)

Variablen wie „expire_logs_days“ anzeigen; Wir können auch den Parameter „expire_logs_days“ festlegen, um eine automatische Bereinigung einzurichten. Der Standardwert ist 0, was bedeutet, dass die automatische Löschfunktion nicht aktiviert ist. Wenn die automatische Bereinigungsfunktion aktiviert ist, werden die Binärprotokolldateien, die diese Anzahl von Tagen überschreiten, automatisch gelöscht. Die automatische Löschung erfolgt normalerweise, wenn MySQL gestartet wird oder wenn das Protokoll geleert wird.
setze expire_logs_days=7;

Mit dem Binärprotokoll verbundene Parameter

1. Die Systemvariable log_bin_trust_function_creators ist standardmäßig deaktiviert. Durch Aktivieren dieses Parameters wird die Erstellung gespeicherter Prozeduren, Funktionen und Trigger eingeschränkt.

2: Die Systemvariable sql_log_bin wird verwendet, um zu steuern, ob die Binärprotokollfunktion auf Sitzungsebene ein- oder ausgeschaltet ist. Der Standardwert ist ON, was bedeutet, dass die Binärprotokollfunktion aktiviert ist.

3. Die Systemvariable binlog_cache_size gibt an, dass jedem Client ein Cache der Größe binlog_cache_size zugewiesen wird. Der Standardwert ist 32768. Voraussetzung für die Verwendung des Binärprotokollcaches ist, dass der Server eine Engine verwendet, die Transaktionen unterstützt und die Binärprotokollfunktion aktiviert hat. Es handelt sich um einen von MySQL konzipierten Speicherbereich, der Binärprotokolldaten vorübergehend für einen kurzen Zeitraum zwischenspeichert, um die Effizienz von Binärprotokoll zu verbessern. Generell gilt: Wenn unsere Datenbank keine großen Transaktionen enthält und die Schreibvorgänge nicht besonders häufig sind, sind 2 bis 4 MB eine geeignete Wahl. Wenn unsere Datenbank jedoch viele große Transaktionen oder Anweisungen mit mehreren Transaktionen aufweist und das Schreibvolumen relativ groß ist, können wir binlog_cache_size entsprechend erhöhen. Gleichzeitig können wir mit binlog_cache_use und binlog_cache_disk_use analysieren, ob die eingestellte binlog_cache_size ausreichend ist und ob aufgrund unzureichender Speichergröße eine große Menge an binlog_cache temporäre Dateien (binlog_cache_disk_use) zum Zwischenspeichern verwendet.

Sie können Binlog_cache_disk_use und Binlog_cache_use überprüfen, um festzustellen, ob binlog_cache_size angepasst werden muss.

4. Systemvariable max_binlog_cache_size Die maximale Cache-Speichergröße, die von Binärprotokollen verwendet werden kann. Wenn beim Ausführen einer Transaktion mit mehreren Anweisungen max_binlog_cache_size nicht groß genug ist, meldet das System möglicherweise den Fehler „Die Transaktion mit mehreren Anweisungen erforderte mehr als ‚max_binlog_cache_size‘ Bytes an Speicher.“

5. Systemvariable max_binlog_stmt_cache_size

max_binlog_cache_size ist für Transaktionsanweisungen und max_binlog_stmt_cache_size ist für Nicht-Transaktionsanweisungen. Wenn wir feststellen, dass Binlog_cache_disk_use oder Binlog_stmt_cache_disk_use relativ groß ist, müssen wir eine Erhöhung der Cachegröße in Betracht ziehen.

6. Die Systemvariable max_binlog_size gibt die maximale Größe des Binärprotokolls an, die im Allgemeinen auf 512 MB oder 1 GB eingestellt ist, jedoch 1 GB nicht überschreiten darf. Diese Einstellung kann die Größe des Binärprotokolls nicht streng steuern, insbesondere wenn das Binärprotokoll in der Nähe einer großen Transaktion liegt. 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.

7. Die Systemvariable binlog_checksum wird zur Master-Slave-Verifizierung der Replikation verwendet. NONE bedeutet, dass keine Prüfsumme generiert wird und CRC-32 bedeutet, dass dieser Algorithmus zur Prüfung verwendet wird.

8. Systemvariable sync_binlog. Dieser Parameter ist für das MySQL-System von entscheidender Bedeutung. Er wirkt sich nicht nur auf den Leistungsverlust der binären Protokolldatei zu MySQL aus, sondern beeinflusst auch die Integrität der Daten in MySQL.

sync_binlog = 0, wenn die Transaktion festgeschrieben ist, schreibt Mysql nur die Daten in binlog_cache in die Binlog-Datei, führt jedoch keine Anweisungen zur Festplattensynchronisierung wie fsync aus, um das Dateisystem anzuweisen, den Cache auf der Festplatte zu aktualisieren, sondern überlässt dem Dateisystem die Entscheidung, wann die Synchronisierung erfolgen soll. Die Standardeinstellung in MySQL ist sync_binlog=0, was bedeutet, dass keine obligatorischen Anweisungen zur Festplattenaktualisierung erfolgen. Diese Einstellung bietet die beste Leistung, birgt aber auch das größte Risiko. Wenn das System abstürzt, gehen alle Binärprotokollinformationen im Dateisystemcache verloren. Dies führt zum Problem unvollständiger Daten.

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

Sie können sync_binlog entsprechend anpassen, um eine höhere Parallelität und Leistung auf Kosten eines gewissen Maßes an Konsistenz zu erzielen.

9. Die Systemvariable binlog_format gibt den Typ des Binärprotokolls an. Es gibt drei Werte: STATEMENT, ROW und MIXED. Der Standardmodus vor MySQL 5.7.6 ist STATEMENT. Der Standardmodus für MySQL 5.7.7 und höher ist der ROW-Modus. Dieser Parameter betrifft hauptsächlich die Master-Slave-Replikation.

SQL-Anweisungsbasierte Replikation (SBR),

zeilenbasierte Replikation (RBR),

Gemischtbasierte Replikation (MBR).

Binärprotokollinhalte anzeigen

Methode 1: Verwenden Sie die Methode „Binlog-Ereignisse anzeigen“, um die Protokolle der aktuellen und angegebenen Binlogs abzurufen. Sie ist nicht zum Extrahieren einer großen Anzahl von Protokollen geeignet.

BINLOG-EREIGNISSE ANZEIGEN [IN 'log_name'] [AB pos] [LIMIT [offset,] row_count]

BINLOG-EREIGNISSE IN 'mysql-bin.000005' ANZEIGEN \G

Methode 2: Verwenden Sie die Befehlszeile mysqlbinlog, um Protokollinhalte anzuzeigen (geeignet für die Stapelprotokollextraktion).

System mysqlbinlog /var/lib/mysql/DB-Server-bin.000013;
mysqlbinlog /var/lib/mysql/DB-Server-bin.000013 > test.sql;

Arten von Binärprotokollen

Segmentbasiertes Protokollformat

binlog_format=ANWEISUNG

Die SQL-Anweisung der Operation wird aufgezeichnet.

Vorteil:

Die Menge der Protokolldatensätze ist relativ gering, wodurch Datenträger- und Netzwerk-E/A gespart werden. Die durch Ändern oder Einfügen nur eines Datensatzes im ROW-Format generierte Protokollmenge ist geringer als die vom Segment generierte Protokollmenge.

Mangel:

Die Kontextinformationen müssen aufgezeichnet werden, um sicherzustellen, dass die Ausführungsergebnisse der Anweisung auf dem Slave-Server mit denen auf dem Master-Server übereinstimmen.

Bestimmte nicht-deterministische Funktionen wie UUID und USER() können nicht repliziert werden.

Dies kann zu Dateninkonsistenzen zwischen dem primären und sekundären Server der MySQL-Replikation führen und so die Replikationsverbindung unterbrechen.

Binlog-Format anzeigen

Variablen wie „binlog_format“ anzeigen;

setze Sitzung binlog_format=Anweisung;

Zeilenbasiertes Protokollformat

Ändern Sie das Binärformat my.ini in binlog_format=ROW

Vorteile der Zeile: Das Zeilenformat kann das Problem der Master-Slave-Inkonsistenz bei der MySQL-Replikation vermeiden. Dieses Format wird offiziell empfohlen. Dieselbe SQL-Anweisung ändert 10.000 Daten. Die segmentbasierte Protokollierung zeichnet nur diese SQL-Anweisung auf. Ein zeilenbasiertes Protokoll verfügt über 10.000 Datensätze und zeichnet die Änderungen jeder Datenzeile auf.

1. Die MySQL Master-Slave-Replikation ist sicherer.

2. Das Ändern jeder Datenzeile ist effizienter als die segmentbasierte Replikation. Wenn die Daten in der Datenbank durch eine fehlerhafte Operation geändert werden und kein Backup zum Wiederherstellen vorhanden ist, können wir die Daten wiederherstellen, indem wir das Binärprotokoll analysieren und die im Protokoll aufgezeichneten Datenänderungsoperationen rückgängig machen.

Nachteile der Zeile: große Menge an Protokolldatensätzen

binlog_row_image=[voll, minimal, Noblob]

vollständig: zeichnet alle Änderungen an Spalten auf; minimal: zeichnet nur geänderte Spalten auf. noblob: Wenn das Feld vom Typ Text oder CLOB ist, werden diese Protokolle nicht aufgezeichnet.

Verwenden Sie mysqlbinlog -vv ../data/mysql-bin.000005, um das detaillierte Protokoll anzuzeigen.

setze Sitzung binlog_row_image=minimal

Hybrides Protokollformat:

binlog_format=GEMISCHT

Funktionen: Das System trifft die Auswahl zwischen segment- und zeilenbasierten Protokollformaten basierend auf der SQL-Anweisung. Die Datenmenge wird durch das ausgeführte SQL bestimmt.

So wählen Sie ein Binärformat aus

Es wird empfohlen, binlog_format = mixed oder binlog_format = row; binlog_row_image = minimal zu verwenden.

Kopiermethode:

1. SQL-Anweisungsbasierte Replikation (SBR)

Vorteile: Es werden weniger Protokolle generiert, wodurch IDs für die Netzwerkübertragung gespart werden. Die Tabellendefinitionen der Master- und Slave-Datenbanken müssen nicht genau gleich sein.

Es ist flexibler als die zeilenbasierte Replikation.

Nachteile: Bei nicht-deterministischen Ereignissen kann die Konsistenz der Master-Slave-Replikationsdaten nicht garantiert werden. Für gespeicherte Prozeduren, Trigger

2. Zeilenbasierte Replikation (RBR)

Vorteile: Kann auf jede SQL-Replikation angewendet werden, einschließlich nicht-deterministischer Funktionen, gespeicherter Prozeduren usw. Kann die Verwendung von Datenbanksperren reduzieren.

Nachteile: Die Tabellenstruktur der Master- und Slave-Datenbanken muss identisch sein, da sonst die Replikation unterbrochen wird.

3. Die Arbeitsweise kopieren

1. Der Masterserver schreibt die Änderungen in das Binärprotokoll.

2. Der Slave liest die Änderungen im Binärprotokoll des Masters und schreibt sie in relay_log.

Protokollpunktbasierte Replikation, GTID-basierte Replikation.

3. Spielen Sie die Protokolle im Relay_log auf dem Slave erneut ab.

Durch die SQL-segmentbasierte Protokollierung wird aufgezeichnetes SQL in der Slave-Datenbank erneut ausgeführt.

Die zeilenbasierte Protokollierung wendet Änderungen an Datenzeilen direkt auf dem Slave an.

Oben finden Sie eine ausführliche Erläuterung zu MySQL-Transaktionen und MySQL-Protokollen. Weitere Informationen zu MySQL-Transaktionen und MySQL-Protokollen finden Sie in den anderen verwandten Artikeln auf 123WORDPRESS.COM!