Detaillierte Erläuterung der MySQL-Replikationsprinzipien und praktischen Anwendungen

Dieser Artikel veranschaulicht anhand von Beispielen die Prinzipien und praktischen Anwendungen der MySQL-Replikation. Teilen Sie uns die Einzelheiten zu Ihrer Information mit:

Die Replikationsfunktion besteht darin, die Daten einer MySQL-Datenbank in eine oder mehrere MySQL-Slave-Datenbanken zu kopieren.

So funktioniert die Replikation: Alle auf dem Master-Server ausgeführten DDL- und DML-Anweisungen werden in Binärprotokollen aufgezeichnet, die von angeschlossenen Slave-Servern abgerufen, auf die Slaves kopiert und als Relay-Protokolle gespeichert werden.

Dieser Vorgang wird von einem Thread namens „IO-Thread“ gehandhabt, und ein anderer Thread namens „SQL-Thread“ führt die Anweisungen im Relay-Protokoll nacheinander aus.

Es gibt mehrere Replikationstopologien:

1. Traditionelle Replikation, ein Master und mehrere Slaves, ein Master-Server und mehrere Slave-Server.

2. Kettenreplikation: Ein Server kopiert von der Masterdatenbank und ein anderer Server kopiert von diesem Server. Der Zwischenserver wird auch als Relay-Masterdatenbank bezeichnet.

3. Master-Master-Replikation: Die beiden Masterbibliotheken akzeptieren Schreibvorgänge und Replikationen voneinander.

4. Multi-Source-Replikation: Eine Slave-Datenbank repliziert von mehreren Master-Datenbanken.

1. So kopieren Sie

1. Aktivieren Sie die binäre Protokollierung auf dem Master.

2. Erstellen Sie einen Replikationsbenutzer auf der Masterdatenbank.

3. Legen Sie auf dem Slave eine eindeutige Server-ID fest.

4. Sichern Sie die Daten aus der Hauptdatenbank.

5. Stellen Sie die in der Masterdatenbank gesicherten Daten auf der Slavedatenbank wieder her.

6. Führen Sie den Befehl CHANGE MASTER TO aus.

7. Beginnen Sie mit dem Kopieren.

2. Die einzelnen Schritte sind wie folgt:

1. Aktivieren Sie in der Masterdatenbank die binäre Protokollierung und legen Sie die Server-ID fest.

#Server-ID festlegen
Server-ID = 1
#Binlog öffnen log-bin = mysql-bin

2. Erstellen Sie einen Replikationsbenutzer auf der Masterdatenbank

Erstellen Sie den Benutzer „Benutzername“@„%“, identifiziert durch „Passwort“;
gewähre 'Benutzername'@'%' Replikations-Slave auf *.*;

3. Setzen Sie die Server-ID auf dem Slave

#Server-ID festlegen
server_id = 10

4. Sichern Sie die Daten in der Hauptdatenbank

mysqldump -u root -p --all-databases --routines --events --triggers --single-transaction --master-data > Exportpfad

5. Wiederherstellen der aus der Master-Datenbank exportierten Daten auf der Slave-Datenbank

mysql -u root -p -f <Hauptdatenbank-Sicherungsdatei.sql

6. Führen Sie den Befehl CHANGE MASTER TO auf dem Slave aus

ÄNDERN SIE MASTER IN 
MASTER_HOST='Hauptbibliotheks-IP',
MASTER_USER = 'Master-Datenbankreplikationsbenutzer',
MASTER_PASSWORD='Passwort',
MASTER_LOG_FILE='Name des Binärprotokolls',
MASTER_LOG_POS=Position des Binärprotokolls;

Der Name und die Position des Binärlogs sind bereits in der Sicherungsdatei der Masterbibliothek enthalten, ähnlich wie folgt:

ÄNDERN SIE MASTER IN MASTER_LOG_FILE='mysql-bin.000016', MASTER_LOG_POS=47845;

7. Führen Sie „start slave“ in der Slave-Bibliothek aus und zeigen Sie dann „show slave status\G;“ an, um den Replikationsstatus anzuzeigen.

3. Einrichten der Master-Master-Replikation

Angenommen, die Masterbibliotheken sind Master1 und Master2.

1. Setzen Sie master2 auf schreibgeschützt

setze @@global.read_only = ein;

2. Erstellen Sie einen Replikationsbenutzer auf Master2. Falls vorhanden, müssen Sie ihn nicht erstellen.

Erstellen Sie den Benutzer „Benutzername“@„%“, identifiziert durch „Passwort“;
gewähre 'Benutzername'@'%' Replikations-Slave auf *.*;

3. Stellen Sie sicher, dass das Binärprotokoll auf Master2 eingeschaltet ist, und überprüfen Sie die Koordinaten des Binärprotokolls auf Master2

Masterstatus anzeigen;

4. Führen Sie gemäß den Informationen in Schritt 2 den Befehl CHANGE MASTER TO auf Master1 aus

ÄNDERN SIE MASTER IN 
MASTER_HOST='MASTER2 Host-IP',
MASTER_USER = 'MASTER2 Replikationsbenutzer',
MASTER_PASSWORD='Passwort',
MASTER_LOG_FILE='Name des Binärprotokolls',
MASTER_LOG_POS=Position des Binärprotokolls;

5. Aktivieren Sie den Slave-Modus auf Master1

Slave starten;

6. Stellen Sie Master2 auf Lesen und Schreiben ein

setze @@global.read_only = aus;

4. Einrichten der Multi-Source-Replikation

Legen Sie Server3 als Slave-Bibliothek von Server1 und Server2 fest.

1. Legen Sie das Binärprotokoll und die Server-ID von Server1 und Server2 fest. Informationen zu bestimmten Vorgängen finden Sie oben.

2. Erstellen Sie Replikationsbenutzer auf Server1 und Server2. Informationen zu spezifischen Vorgängen finden Sie oben.

3. Legen Sie die Server-ID auf Server3 fest.

4. Sichern Sie die Daten von Server1 und Server2.

5. Stellen Sie die auf Server1 und Server2 gesicherten Daten auf Server3 wieder her.

6. Ändern Sie auf Server3 das Replikationsrepository von DATEI in TABELLE.

Sklave stoppen;
Setzen Sie das globale Master-Info-Repository auf „Tabelle“.
Setzen Sie das globale Relay_Log_Info_Repository auf „Tabelle“.

Sie müssen auch die Konfigurationsdatei ändern:

[mysqld]
Master-Info-Repository = TABELLE
Relay-Log-Info-Repository = TABELLE

7. Führen Sie auf Server3 den Befehl CHANGE MASTER TO aus und benennen Sie den Kanal

ÄNDERN SIE MASTER IN 
MASTER_HOST='Server1 Host-IP',
MASTER_USER = 'Server1-Replikationsbenutzer',
MASTER_PASSWORD='Passwort',
MASTER_LOG_FILE='Name des Binärprotokolls von Server1',
MASTER_LOG_POS=Server1-Binärprotokollposition für Kanal „Server1“;

ÄNDERN SIE MASTER IN 
MASTER_HOST='Server2 Host-IP',
MASTER_USER = 'Server2-Replikationsbenutzer',
MASTER_PASSWORD='Passwort',
MASTER_LOG_FILE='Name des Binärprotokolls von Server2',
MASTER_LOG_POS=Server2-Binärprotokollposition für Kanal „Server2“;

8. Führen Sie auf Server3 die Anweisung START SLAVE FOR CHANNEL für jeden Kanal aus.

Starte Slave für Kanal „Server1“;
Starte Slave für Kanal „Server2“;

9. Überprüfen Sie den Synchronisierungsstatus und zeigen Sie den Slave-Status\G an.

Um den Slave-Status des angegebenen Kanals abzurufen, zeigen Sie den Slave-Status für den Kanal „Kanalname“\G an;

5. Replikationsfilter einrichten

Sie können auswählen, welche Tabellen oder Datenbanken repliziert werden sollen. Auf dem Master können Sie die Optionen --binlog-do-db und --binlog-ignore-db verwenden, um die Datenbanken auszuwählen, in denen Änderungen aufgezeichnet werden sollen, um das Binärprotokoll zu steuern. Ein besserer Ansatz ist die Kontrolle der Slave-Bibliothek.

1. Kopieren Sie die angegebene Datenbank

REPLIKATIONSFILTER ÄNDERN REPLICATE_DO_DB = (db1, db2);

2. Kopieren Sie die angegebene Tabelle

REPLIKATIONSFILTER ÄNDERN REPLICATE_DO_TABLE = ('db1.table1');

3. Wenn Sie Platzhalter zur Auswahl von Tabellen verwenden möchten

REPLIKATIONSFILTER ÄNDERN REPLICATE_WILD_DO_TABLE = ('db1.tb_%');

4. Ignorieren Sie die Datenbank

REPLIKATIONSFILTER ÄNDERN REPLICATE_IGNORE_DB = (db1, db2);

5. Ignorieren Sie die angegebene Tabelle

REPLIKATIONSFILTER ÄNDERN REPLICATE_IGNORE_TABLE = ('db1.table1');

6. Wechseln Sie die Slave-Bibliothek von der Master-Slave-Replikation zur Kettenreplikation

Beispielsweise ist jetzt Server A die Masterdatenbank, Server B und Server C sind Slavedatenbanken und sie werden auf Server A repliziert. Jetzt möchte ich Server C als Slave-Bibliothek von Server B verwenden.

1. Stoppen Sie die Slave-Bibliothek auf Server C

Sklave stoppen;
Slave-Status anzeigen\G;

Notieren Sie die Werte von Relay_Master_Log_File und Exec_Master_Log_Pos

2. Stoppen Sie die Slave-Datenbank auf Server B

Sklave stoppen;
Slave-Status anzeigen\G;

Notieren Sie die Werte von Relay_Master_Log_File und Exec_Master_Log_Pos

3. Vergleichen Sie die Protokollposition von Server B mit der von Server C, um herauszufinden, welche die letzte Synchronisierung von Server A ist. Normalerweise wird Server C zuerst gestoppt, wenn er die Bibliothek verlässt, und das Protokoll von Server B wird früher angezeigt.

4. Verwenden Sie auf Server C die Anweisung START SLAVE UNTIL, um es mit der Protokollposition von Server B zu synchronisieren:

START SLAVE UNTIL MASTER_LOG_FILE='Der Name des Server-B-Protokolls im vorherigen Schritt', MASTER_LOG_POS=die Position des Server-B-Protokolls im vorherigen Schritt;

5. Überprüfen Sie auf Server C, ob der Slave-Status angezeigt wird. Exec_Master_Log_Pos und Until_Log_Pos sollten identisch sein.

6. Überprüfen Sie auf Server B den Status der Masterdatenbank und starten Sie die Slavedatenbank.

Masterstatus anzeigen;
Slave starten;
Slave-Status anzeigen\G;

7. Stoppen Sie auf Server C die Slave-Datenbank und führen Sie den Befehl CHANGE MASTER TO aus.

Sklave stoppen;
ÄNDERN SIE MASTER IN
MASTER_HOST='IP von Server B',
MASTER_USER = 'Server B Replikationsbenutzer',
MASTER_PASSWORD='Passwort',
MASTER_LOG_FILE='Erhalten Sie den Protokollnamen durch die Anzeige des Masterstatus im vorherigen Schritt',
MASTER_LOG_POS=Die Protokollposition wurde im vorherigen Schritt durch die Anzeige des Masterstatus ermittelt.

8. Starten Sie auf Server C die Replikation und überprüfen Sie den Status

Slave starten;
Slave-Status anzeigen\G;

7. Kettenreplikation auf Master-Slave-Replikation umstellen

Server A->Server B->Server C. Was müssen Sie tun, wenn Sie möchten, dass Server C direkt der Slave von Server A ist?

1. Stoppen Sie auf Server B die Slave-Datenbank und zeichnen Sie den Status der Master-Datenbank auf.

Sklave stoppen;
Masterstatus anzeigen\G;

2. Stellen Sie auf Server C sicher, dass die Verzögerung des Slaves aufgeholt wurde. Relay_Master_Log_File und Exec_Master_Log_Pos sollten dem Master-Status auf Server B entsprechen.

Sobald die Verzögerung aufgeholt ist, wird die Slave-Datenbank gestoppt.

Sklave stoppen;

3. Rufen Sie auf Server B die Protokollkoordinatenwerte (Relay_Master_Log_File und Exec_Master_Log_Pos) von Server A über den Show-Slave-Status ab und starten Sie den Slave.

Slave-Status anzeigen\G;
Slave starten;

4. Stoppen Sie auf Server C die Slave-Datenbank und führen Sie den Befehl CHANGE MASTER TO aus, um auf Server A zu verweisen.

Sklave stoppen;
ÄNDERN SIE MASTER IN 
MASTER_HOST='IP von Server A',
MASTER_USER = 'Replikationsbenutzer von Server A',
MASTER_PASSWORD='Passwort',
MASTER_LOG_FILE = 'Im vorigen Schritt erhaltenes Protokoll',
MASTER_LOG_POS=im vorherigen Schritt erhaltene Protokollposition;

5. Starten Sie auf Server C die Slave-Datenbank und überprüfen Sie ihren Status.

Slave starten;
Slave-Status anzeigen\G;

8. Verzögerte Replikation einrichten

Warum benötigen Sie eine verzögerte Replikation? Es ist möglich, dass eine katastrophale Anweisung auf der primären Datenbank ausgeführt wird und Sie eine Wiederherstellung ab dem Zeitpunkt in der Sicherung durchführen müssen. Wenn die Datenbank zu groß ist, führt dies zu langen Ausfallzeiten.

Um diese Situation zu vermeiden, können Sie einen verzögerten Slave verwenden. Wenn ein Desaster eintritt und der verzögerte Slave die Katastrophenanweisung noch nicht ausgeführt hat, können Sie zunächst die Replikation stoppen, den Slave die Katastrophenanweisung überspringen lassen und den Slave schließlich zum Master befördern.

1. Hören Sie auf, aus der Bibliothek zu rennen

Sklave stoppen;

2. Stellen Sie die Verzögerungszeit in Sekunden ein

ÄNDERN SIE MASTER IN MASTER_DELAY = 3600;
Slave starten;

3. Überprüfen Sie den Slave-Status

Slave-Status anzeigen\G;

SQL_Delay: Die Anzahl der Sekunden, um die die Slave-Datenbank gegenüber der Master-Datenbank verzögert ist.

SQL_Remaining_Delay: Die Anzahl der verbleibenden Sekunden der Verzögerung. Wenn die Verzögerung beibehalten wird, ist dieser Wert NULL.

Slave_SQL_Running_State: Der Status des SQL-Threads

9. Einrichten der GTID-Replikation

Die globale Transaktionskennung GTID ist eine eindeutige Kennung, die im Programm erstellt und jeder auf dem Master übermittelten Transaktion zugeordnet wird. Diese Kennung ist nicht nur in der Masterdatenbank, sondern auch in anderen Slavedatenbanken eindeutig.

Für alle oben beschriebenen Replikationen müssen der Speicherort der Binärdatei und der Replikationsstartpunkt angegeben werden. Wenn Sie den Master einer Slave-Bibliothek auf eine andere umstellen, müssen Sie den Speicherort der Binärdatei erneut ermitteln, was problematisch sein kann.

Um dies zu vermeiden, können Sie eine GTID-basierte Replikation verwenden. MySQL verwendet GTID, um die Position des Binärprotokolls automatisch zu erkennen.

1. Aktivieren Sie GTID in my.cnf in allen Datenbanken

[mysqld]
gtid_mode = EIN
erzwingen-gtid-Konsistenz = 1
überspringen_Slave_Start

2. Legen Sie für die Masterdatenbank den Schreibschutz fest, um sicherzustellen, dass die Daten in der Masterdatenbank mit denen in der Slavedatenbank übereinstimmen.

setze @@global.read_only = ein;

3. Starten Sie alle Slave-Bibliotheken neu, damit GTID wirksam wird.

4. Starten Sie die Hauptbibliothek neu.

5. Führen Sie den Befehl CHANGE MASTER TO auf dem Slave aus, um die GTID-Replikation einzurichten

ÄNDERN SIE MASTER IN
MASTER_HOST='Hauptbibliotheks-IP',
MASTER_PORT=3306,
MASTER_USER = 'Benutzer kopieren',
MASTER_PASSWORD='Passwort',
MASTER_AUTO_POSITION=1;

6. Führen Sie „Start Slave“ auf allen Slave-Bibliotheken aus und überprüfen Sie den Status.

10. Einrichten der halbsynchronen Replikation

Standardmäßig erfolgt die Replikation asynchron, und der Master weiß nicht, ob der Schreibvorgang den Slave erreicht hat. Wenn zwischen Master und Slave eine Verzögerung auftritt, stürzt der Master ab und die Daten, die den Slave noch nicht erreicht haben, gehen verloren.

Um dieses Problem zu lösen, wartet die Masterbibliothek bei der halbsynchronen Replikation, bis mindestens eine Slavebibliothek die geschriebenen Daten empfängt.

1. Installieren Sie das Plug-In rpl_semi_sync_master auf der Masterdatenbank

Plugin rpl_semi_sync_master installieren SONAME 'semisync_master.so';

Für Windows verwenden Sie bitte Folgendes:

Installieren Sie das Plugin rpl_semi_sync_master SONAME „semisync_master.dll“.

2. Bestätigen Sie, dass das Plugin aktiviert ist

Wählen Sie Plugin-Name und Plugin-Status aus Information_schema.plugins, wobei Plugin-Name wie „%semi%“ aussieht.

3. Halbsynchrone Replikation aktivieren und Timeout anpassen

setze @@global.rpl_semi_sync_master_enabled=1;
setze @@global.rpl_semi_sync_master_timeout=100;

4. Installieren Sie das Plug-In rpl_semi_sync_slave in der Slave-Bibliothek

Installieren Sie das Plugin rpl_semi_sync_slave SONAME „semisync_slave.so“.

Für Windows verwenden Sie bitte Folgendes:

Installieren Sie das Plugin rpl_semi_sync_slave SONAME „semisync_slave.dll“.

5. Bestätigen Sie, dass das Plugin aktiviert ist

Wählen Sie Plugin-Name und Plugin-Status aus Information_schema.plugins, wobei Plugin-Name wie „%semi%“ aussieht.

6. Aktivieren Sie in der Slave-Bibliothek die halbsynchrone Replikation und starten Sie den IO-Thread der Slave-Bibliothek neu

setze global rpl_semi_sync_slave_enabld = 1;
STOPPEN SIE SLAVE IO_THREAD;
STARTE SLAVE IO_THREAD;

7. Überprüfen Sie den Halbsynchronisationsstatus auf folgende Weise

Status wie „rpl_semi_sync_master_clients“ anzeigen;

Zeigen Sie die Anzahl der Clients an, die im halbsynchronen Modus mit der Masterdatenbank verbunden sind.

Status wie „rpl_semi_sync_master_status“ anzeigen;

Die Masterdatenbank wechselt zwischen asynchroner und halbsynchroner Replikation. Ein bedeutet halbsynchrone Replikation, Aus bedeutet asynchrone Replikation.

Leser, die an weiteren MySQL-bezogenen Inhalten interessiert sind, können sich die folgenden Themen auf dieser Site ansehen: „MySQL-Abfragekenntnisse“, „Zusammenfassung der allgemeinen MySQL-Funktionen“, „MySQL-Protokolloperationskenntnisse“, „Zusammenfassung der MySQL-Transaktionsoperationskenntnisse“, „MySQL-gespeicherte Prozedurkenntnisse“ und „Zusammenfassung der MySQL-Datenbanksperrenkenntnisse“.

Ich hoffe, dass dieser Artikel für jedermann beim Entwurf einer MySQL-Datenbank hilfreich ist.