Erstellen Sie einen stabilen und hochverfügbaren Cluster basierend auf MySQL + MyCat, Lastausgleich, Master-Slave-Replikation und Lese-/Schreibtrennung

Bei der Optimierung der Datenbankleistung wird im Allgemeinen Clustering eingesetzt. Die Investition in Oracle-Clustersoftware und -hardware ist teuer. Heute habe ich einen Tag damit verbracht, eine Clusterumgebung auf Basis von MySQL aufzubauen.

Hauptideen

Einfach ausgedrückt: Implementieren Sie die MySQL Master-Slave-Replikation --> verwenden Sie mycat, um einen Lastenausgleich zu erreichen.

Nach einem Vergleich der häufig verwendeten Methoden zur Lese- und Schreibtrennung wird mycat aufgrund seiner aktiven Community und stabilen Leistung empfohlen.

Testumgebung

MYSQL-Version: Serverversion: 5.5.53. Sie können das WINDWOS-Installationspaket von der offiziellen Website herunterladen.

Hinweis: Stellen Sie sicher, dass die MySQL-Version 5.5 oder höher ist. Die Konfigurationsmethode für die Master-Slave-Synchronisierung ist bei früheren Versionen anders.

Die Implementierungsidee von Linux ist ähnlich, ändern Sie einfach my.cnf.

• Ein Master-MySQL. 192.168.110.1:3306, Benutzer „root“, Passwort „root“. Betriebssystem: Win7 x64, Speicher: 4 GB
• Installationspfad: C:\Programme\MySQL\MySQL Server 5.5\bin
• B bereitet MySQL vor. 192.168.110.2:3306, Benutzer „root“, Passwort „root“. Betriebssystem: win2003 x64, Speicher: 1g
• Installationspfad: C:\Programme\MySQL\MySQL Server 5.5\bin
• Erstellen Sie eine sync_test-Datenbank in MySQL auf A und B
  

Implementierung der MySQL Master-Slave-Replikation

Die Grundidee ist: Das Haupt-MySQL von A beginnt mit der Protokollierung, das Standby-MySQL von B liest das Vorgangsprotokoll und führt es synchron aus.

Im Allgemeinen wird die Master-Slave-Synchronisierung verwendet, eine Master-Master-Synchronisierung wird nicht empfohlen.

Konfigurieren Sie einen Master-MySQL

1) Ändern Sie my.ini. Sie müssen ein Protokollverzeichnis und eine Datei mysql-bin.log am entsprechenden Speicherort log-bin="C:/Programme/MySQL/MySQL Server 5.5/log/mysql-bin.log" erstellen.

[mysqld]
server-id=1 #Hostkennung, Integer-Port=3306  
log-bin="C:/Programme/MySQL/MySQL Server 5.5/log/mysql-bin.log" #Stellen Sie sicher, dass diese Datei beschreibbar ist read-only=0 #Host, sowohl Lesen als auch Schreiben sind möglich binlog-do-db=sync_test #Datenbank muss gesichert werden, mehrere Zeilen schreiben binlog-ignore-db=mysql #Datenbanken, die nicht gesichert werden müssen, mehrere Zeilen schreiben

2) Erlauben Sie den MySQL-Fernzugriff

#Melden Sie sich bei der MySQL-Konsole an
Geben Sie %home%/bin ein und führen Sie mysql -uroot -proot aus
#Genehmigung. Erlauben Sie dem Root-Benutzer den Remotezugriff auf einen MySQL-Master aus dem IP-Bereich 192.168.110.*
mysql>GEWÄHREN SIE ALLE PRIVILEGIEN FÜR *.* AN 'root'@'192.168.110.*', IDENTIFIZIERT DURCH 'root' MIT GRANT-OPTION;
# Wirksam werden. Dieser Vorgang ist sehr wichtig!
mysql> FLUSH-PRIVILEGIEN;

3) Starten Sie die A-Master-MySQL-Datenbank neu

Geben Sie %home%/bin ein und führen Sie mysql -uroot -proot aus

mysql>net stop mysql;
mysql>net start mysql;

4) Überprüfen Sie den Hauptstatus des MySQL-Protokolls

mysql> Masterstatus anzeigen\G;
*************************** 1. Reihe ***************************
      Datei:mysql-bin.000003
    Position: 107
  Binlog_Do_DB: sync_test
Binlog_Ignore_DB: mysql
1 Zeile im Satz (0,00 Sek.)

FEHLER:
Keine Abfrage angegeben

Konfigurieren Sie B Standby MySQL

1) Ändern Sie my.ini. Sie müssen ein Protokollverzeichnis und eine Datei mysql-bin.log am entsprechenden Speicherort log-bin="C:/Programme/MySQL/MySQL Server 5.5/log/mysql-bin.log" erstellen.

[mysqld]
# für Synchronisierungstest hinzufügen
server-id=2 #Slave-ID log-bin="C:/Programme/MySQL/MySQL Server 5.5/log/mysql-bin.log" #Stellen Sie sicher, dass diese Datei beschreibbar ist #master-host="192.168.110.1" #Host-IP
#master-user=root #Benutzername für den Datenbankzugriff#master-pass=root #Passwort für den Datenbankzugriff#master-port=3306 #Host-Port#master-connect-retry=60 #Wenn der Slave-Server feststellt, dass die Verbindung zum Master-Server getrennt ist, die Zeitdifferenz (in Sekunden) für die erneute Verbindung
replicate-do-db=sync_test #Repliziert nur eine bestimmte Datenbank replicate-ignore-db=mysql #Repliziert keine bestimmte Datenbank

2) Starten Sie die B-Standby-MySQL-Datenbank neu

Geben Sie %home%/bin ein und führen Sie mysql -uroot -proot aus

mysql>net stop mysql;
mysql>net start mysql;

3) Konfigurieren Sie die Datenquelle der Standby-Datenbank B und überprüfen Sie, ob der Status des hervorgehobenen Bereichs normal ist.

mysql>ändere Master in master_host='192.168.110.1',master_port='3306',master_user='root',master_password='root';
mysql>Slave-Start;
mysql>Slave-Status anzeigen\G;
*************************** 1. Reihe ***************************
        Slave_IO_State: Wartet auf das Senden eines Masters
         Master_Host: 192.168.110.1
         Master_Benutzer: root
         Master_Port: 3306
        Verbindungswiederholung: 60
       Master_Log_File: mysql-bin.000003
     Read_Master_Log_Pos: 107
        Relay_Log_File: wjt-1c698d8a032-relay-bin.00001
        Relay_Log_Pos: 253
    Relay_Master_Log_File: mysql-bin.000003
       Slave_IO_Running: Ja
      Slave_SQL_Running: Ja
       Replizieren_Do_DB:sync_test
     Replicate_Ignore_DB: mysql
      Tabelle_replizieren:
    Tabelle_Ignorieren_replizieren:
   Wild_Do_Tabelle replizieren:
 Tabelle_Wild_Ignore_replizieren:
          Last_Errno: 0
          Letzter_Fehler:
         Skip_Counter: 0
     Exec_Master_Log_Pos: 107
       Relay_Log_Space: 565
       Until_Condition: Keine
        Bis_Log_Datei:
        Bis_Log_Pos: 0
      Master_SSL_Allowed: Nein
      Master_SSL_CA_Datei:
      Master_SSL_CA_Pfad:
       Master_SSL_Zertifikat:
      Master_SSL_Chiffre:
        Master_SSL_Schlüssel:
    Sekunden_Hinter_Master: 0
Master_SSL_Verify_Server_Cert: Nein
        Last_IO_Errno: 0
        Letzter_E/A-Fehler:
        Last_SQL_Errno: 0
        Letzter_SQL_Fehler:
 Server-IDs replizieren_ignorieren:
       Master_Server_Id: 1
1 Zeile im Satz (0,00 Sek.)
FEHLER:
Keine Abfrage angegeben

Überprüfen der Ergebnisse der Synchronisierungskonfiguration

• Ein Haupt-MySQL: Verwenden Sie Navicat, um die Tabelle sync_table in der Bibliothek sync_test zu erstellen und einige Daten hinzuzufügen
• B MySQL-Backup: Verwenden Sie das Navicat-Tool, um die Sync_Test-Bibliothek zu überprüfen. Sie können sehen, dass die Sync_Table-Tabelle und die Daten synchronisiert wurden
  

Realisieren Sie die Lese-/Schreibtrennung

Die Hauptidee besteht darin, Mycat-Middleware zu verwenden, um SQL-Befehle an den MySQL-Backend-Knoten weiterzuleiten. mycat ist nicht für die Datenbanksynchronisierung verantwortlich.

Installieren Sie mycat

Was ist Mycat? Es kann als Middleware für den Datenbankzugriff betrachtet werden, ähnelt jedoch eher Produkten wie f5 und ngnix und verfügt über Funktionen wie Zugriffsrouting, Sharding-Operationen für mehrere Tabellen usw. Auf jeden Fall sehr kraftvoll.

• Download: http://www.mycat.io/, dieser Artikel verwendet: 1.6-RELEASE
  
• Entpacken Sie Mycat-server-1.6-RELEASE-20161012170031-win.tar in das Verzeichnis D:\dev-bin\mycat
  
• Stellen Sie sicher, dass die Java-Umgebung jdk1.7 oder höher ist, sonst unterstützt mycat nicht

Installation abgeschlossen

Konfigurieren Sie mycat

1) server.xml. Konfigurieren Sie Zugriffsbenutzer und Berechtigungen. Ändern Sie die hervorgehobenen Informationen, wobei „Admin“ und „User“ Benutzer sind, die auf mycat zugreifen, und „TESTDB“ die virtuelle Datenbank von mycat ist, auf die Anwendungen der höheren Ebene zugreifen können.

<Benutzername="admin">
    <property name="password">admin</property>
    <Eigenschaftsname="schemas">TESTDB</Eigenschaft>
    <!-- DML-Berechtigungseinstellungen auf Tabellenebene-->
    <!--     
    <Berechtigungen check="false">
      <schema name="TESTDB" dml="0110" >
        <Tabellenname="tb01" dml="0000"></Tabelle>
        <Tabellenname="tb02" dml="1111"></Tabelle>
      </schema>
    </Berechtigungen>    
     -->
  </Benutzer>
  <Benutzername="Benutzer">
    <property name="password">Benutzer</property>
    <Eigenschaftsname="schemas">TESTDB</Eigenschaft>
    <Eigenschaftsname="readOnly">wahr</Eigenschaft>
  </Benutzer>

2) schema.xml. Dieser Teil ist nicht leicht zu verstehen, deshalb habe ich ihn vereinfacht und in drei Hauptkonfigurationen unterteilt: Schema, Datenknoten und Datenhost.

Der Knoten <scheme> definiert die virtuelle Datenbank von mycat als TESTDB, balance="1": Schreibvorgänge werden an Maschine A und Lesevorgänge an Maschine B weitergeleitet.

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
  <schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
    <!-- Hier gibt es keine Konfiguration, was bedeutet, dass alle Tabellen auf den Knoten dn1 verteilt sind-->
  </schema>
  <dataNode name="dn1" dataHost="localhost1" database="sync_test" />
  <dataHost name="localhost1" maxCon="1000" minCon="10" balance="1"
       writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
    <heartbeat>Benutzer auswählen()</heartbeat>
    <!-- kann mehrere Schreibhosts haben -->
    <writeHost host="hostM1" url="192.168.110.1:3306" Benutzer="root" Passwort="root">
      <!-- kann mehrere Lesehosts haben -->
      <readHost host="hostS2" url="192.168.110.2:3306" Benutzer="root" Passwort="root" />
    </writeHost>
  </dataHost>
</mycat:schema>

Starten Sie mycat

1) Starten Sie mycat

D:\dev-bin\mycat\bin>startup_nowrap.bat

Die Hintergrundinformationen sind wie folgt:

D:\dev-bin\mycat\bin>startup_nowrap.bat
D:\dev-bin\mycat\bin>REM-Prüfung JAVA_HOME & java
D:\dev-bin\mycat\bin>set "JAVA_CMD=C:\Programme (x86)\Java\jdk1.7.0_13/bin/java"
D:\dev-bin\mycat\bin>wenn "C:\Programme (x86)\Java\jdk1.7.0_13" == "" gehe zu noJavaHome
D:\dev-bin\mycat\bin>wenn vorhanden "C:\Programme (x86)\Java\jdk1.7.0_13\bin\java.exe" gehe zum Haupteintrag
D:\dev-bin\mycat\bin>REM setze HOME_DIR
D:\dev-bin\mycat\bin>set "CURR_DIR=D:\dev-bin\mycat\bin"
D:\dev-bin\mycat\bin>cd ..
D:\dev-bin\mycat>set "MYCAT_HOME=D:\dev-bin\mycat"
D:\dev-bin\mycat>cd D:\dev-bin\mycat\bin
#Wenn der Start fehlschlägt, ändern Sie bitte die folgenden Parameter in der Datei D:\dev-bin\mycat\bin\startup_nowrap.bat. Die Standardspeichernutzung beträgt 2G
D:\dev-bin\mycat\bin>"C:\Programme (x86)\Java\jdk1.7.0_13/bin/java" -server -Xms512m -Xmx512m -XX:MaxPermSize=64M -XX:+AggressiveOpts -XX:MaxDirectMemorySize=768m -DMYCAT_HOME=D:\
p "..\conf;..\lib\*" io.mycat.MycatStartup

MyCAT-Server wurde erfolgreich gestartet. Siehe Protokolle in logs/mycat.log. #Bei erfolgreichem Start werden die folgenden Informationen angezeigt.

Hinweis: Wenn das Protokoll Informationen wie „192.168.110.2 nicht verbunden“ enthält, erlauben Sie bitte den Remote-Zugriff auf MySQL-Server B.

#Melden Sie sich bei der MySQL-Konsole an
Geben Sie %home%/bin ein und führen Sie mysql -uroot -proot aus
#Genehmigung. Erlauben Sie dem Root-Benutzer den Remotezugriff auf Bmysql aus dem IP-Bereich 192.168.110.*
mysql>GEWÄHREN SIE ALLE PRIVILEGIEN FÜR *.* AN 'root'@'192.168.110.*', IDENTIFIZIERT DURCH 'root' MIT GRANT-OPTION;
# Wirksam werden, dieser Vorgang ist sehr wichtig!
mysql> FLUSH-PRIVILEGIEN;

Testen der Lese-/Schreibtrennung

Überprüfen der Synchronisierung

• Verwenden Sie Navicat, um eine Verbindung zu Mycat herzustellen. Die Vorgehensweise ist dieselbe wie bei der Verbindung mit der physischen MySQL-Datenbank. Benutzer admin, Passwort admin, Port 8066
• Erstellen Sie in der virtuellen Bibliothek TESTDB eine neue Tabelle test2 und fügen Sie einige Daten hinzu
• Überprüfen Sie, ob die Daten von Knoten A und Knoten B synchronisiert wurden.

Schließen Sie die Synchronisierung von B-Standby-MySQL und überprüfen Sie die Lese-/Schreibtrennung

mysql> Slave-Stopp;
Abfrage OK, 0 Zeilen betroffen (0,00 Sek.)
mysql> Slave-Status anzeigen\G;
*************************** 1. Reihe ***************************
        Slave_IO_State:
         Master_Host: 192.168.110.1
         Master_Benutzer: root
         Master_Port: 3306
        Verbindungswiederholung: 60
       Master_Log_File: mysql-bin.000003
     Read_Master_Log_Pos: 478
        Relay_Log_File: wjt-1c698d8a032-relay-bin.00001
        Relay_Log_Pos: 624
    Relay_Master_Log_File: mysql-bin.000003
       Slave_IO_Running: Nein
      Slave_SQL_Running: Nein
       Replizieren_Do_DB:sync_test
     Replicate_Ignore_DB: mysql
      Tabelle_replizieren:
    Tabelle_Ignorieren_replizieren:
   Wild_Do_Tabelle replizieren:
 Tabelle_Wild_Ignore_replizieren:
          Last_Errno: 0
          Letzter_Fehler:
         Skip_Counter: 0
     Exec_Master_Log_Pos: 478
       Relay_Log_Space: 936
       Until_Condition: Keine
        Bis_Log_Datei:
        Bis_Log_Pos: 0
      Master_SSL_Allowed: Nein
      Master_SSL_CA_Datei:
      Master_SSL_CA_Pfad:
       Master_SSL_Zertifikat:
      Master_SSL_Chiffre:
        Master_SSL_Schlüssel:
    Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: Nein
        Last_IO_Errno: 0
        Letzter_E/A-Fehler:
        Last_SQL_Errno: 0
        Letzter_SQL_Fehler:
 Server-IDs replizieren_ignorieren:
       Master_Server_Id: 1
1 Zeile im Satz (0,00 Sek.)
FEHLER:
Keine Abfrage angegeben

3) Verwenden Sie Navicat, um eine Verbindung zu Mycat herzustellen. Die Vorgehensweise ist dieselbe wie bei der Verbindung zur physischen MySQL-Datenbank. Benutzer admin, Passwort admin, Port 8066

• Nach erfolgreicher Verbindung werden die TESTDB-Datenbank und die Testdatentabelle angezeigt
• Fügen Sie der Testtabelle einige Daten hinzu und speichern Sie sie.
• Führen Sie „select * from test“ aus, um den Testvorgang anzuzeigen. Sie werden sehen, dass die Daten nicht aktualisiert werden.

Ursache: mycat leitet die Abfrage sq an B weiter, daher ist der gelesene Ergebnissatz inkonsistent.

Informationen zur Verwendung von mycat finden Sie in der maßgeblichen Anleitung auf der offiziellen Website. Die Dual-Active-Dual-Standby-Architektur wird später aktualisiert.

Zusammenfassen

Oben habe ich Ihnen den Aufbau eines stabilen und hochverfügbaren Cluster-Lastausgleichs, der primären und sekundären Replikation und der Lese-/Schreibtrennung auf Basis von MySQL+MyCat vorgestellt. Ich hoffe, es wird Ihnen helfen. Wenn Sie Fragen haben, hinterlassen Sie mir bitte eine Nachricht und ich werde Ihnen rechtzeitig antworten. Ich möchte auch allen für ihre Unterstützung der Website 123WORDPRESS.COM danken!