Trennung leicht gemacht: MySQL Router 8.2.0 und der „Read / Write Split“

Mit der neuen Version 8.2.0 vom MySQL Router gibt es eine neue Funktion, die den Betrieb von MySQL InnoDB Clustern und / oder ReplicaSets weiter verbessert. In den meisten Setups unserer Kunden wird auch der MySQL Router eingesetzt. Dieser bietet bislang die Möglichkeit Lese- und Schreibaktivitäten über verschiedene zur Verfügung gestellte Ports (per Default 6446 für lesende und (!) schreibende Zugriffe und 6447 für nur (!) lesende Zugriffe) zu trennen. Damit kann ein Read-Scale-Out über die sekundären Systeme erreicht und das primäre System z. B. entlastet werden. Bislang musste die Applikation diesen gezielten (richtigen) Portzugriff umsetzen. Mit der Version 8.2.0 und dem „transparent read / write splitting“ wird dies nun einfacher.  

Die Versuchsanordnung

Für unser Beispiel-Setup nutze ich meine Umgebung, die ich für die kommende DOAG-Konferenz & Austeilung `23 zusammengestellt habe.

Werbeblock: https://meine.doag.org/events/anwenderkonferenz/2023/agenda/#eventDay.all#textSearch.Matthias%20Jung%20MySQL

Diese besteht aus einem MySQL Cluster mit den Knoten:

• doag10 (Source)
• doag20 (Replica)
• doag30 (Replica)

Auf einem weiteren Knoten „doag40“ habe ich die aktuelle Version (Innovation Release; „Nichts ist so beständig wie der Wandel“ (Heraklit): Oracle MySQL ändert sein Versionsmodell) des MySQL Routers heruntergeladen und entpackt.

Beim „Bootstrapping“ des Routers bleibt alles beim Alten. Wir geben einen beliebigen Cluster-Knoten und einen gültigen Account an und installieren unseren Router:

./mysqlrouter --bootstrap admin:admin@doag10:3306 -d router_8.2.0 --user=mysql
# Bootstrapping MySQL Router 8.2.0 (MySQL Community - GPL) instance at '/tmp/mysql-router-8.2.0-linux-glibc2.28-x86_64/bin/router_8.2.0'...

- Creating account(s) (only those that are needed, if any)
- Verifying account (using it to run SQL queries that would be run by Router)
- Storing account in keyring
- Adjusting permissions of generated files
- Creating configuration /tmp/mysql-router-8.2.0-linux-glibc2.28-x86_64/bin/router_8.2.0/mysqlrouter.conf

# MySQL Router configured for the InnoDB Cluster 'DOAG'

After this MySQL Router has been started with the generated configuration

    $ ./mysqlrouter -c /tmp/mysql-router-8.2.0-linux-glibc2.28-x86_64/bin/router_8.2.0/mysqlrouter.conf

InnoDB Cluster 'DOAG' can be reached by connecting to:

## MySQL Classic protocol

- Read/Write Connections: localhost:6446
- Read/Only Connections:  localhost:6447
- Read/Write Split Connections: localhost:6450

## MySQL X protocol

- Read/Write Connections: localhost:6448
- Read/Only Connections:  localhost:6449
 

Der „Kenner“ sieht bereits in den Ausgaben der Installation, dass nun drei und nicht mehr zwei Ports konfiguriert werden (Bereich „MySQL Classic protocol“). Die „alten“ Ports für „Read/Write“ und „Read/Only“ bleiben erhalten. Neu ist der Port für den „Read/Write Split“. Per Default ist dies 6450. 

Die Teststellung

Unser InnoDB Cluster ist betriebsbereit.  

MySQL  doag10:3306 ssl  JS > var cluster = dba.getCluster()
MySQL  doag10:3306 ssl  JS > cluster.status()
{
    "clusterName": "DOAG",
    "defaultReplicaSet": {
        "name": "default",
        "primary": "doag10:3306",
        "ssl": "REQUIRED",
        "status": "OK",
        "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.",
        "topology": {
            "doag10:3306": {
                "address": "doag10:3306",
                "memberRole": "PRIMARY",
                "mode": "R/W",
                "readReplicas": {},
                "replicationLag": "applier_queue_applied",
                "role": "HA",
                "status": "ONLINE",
                "version": "8.1.0"
            },
            "doag20:3306": {
                "address": "doag20:3306",
                "memberRole": "SECONDARY",
                "mode": "R/O",
                "readReplicas": {},
                "replicationLag": "applier_queue_applied",
                "role": "HA",
                "status": "ONLINE",
                "version": "8.1.0"
            },
            "doag30:3306": {
                "address": "doag30:3306",
                "memberRole": "SECONDARY",
                "mode": "R/O",
                "readReplicas": {},
                "replicationLag": "applier_queue_applied",
                "role": "HA",
                "status": "ONLINE",
                "version": "8.1.0"
            }
        },
        "topologyMode": "Single-Primary"
    },
    "groupInformationSourceMember": "doag10:3306"
 

Die primäre Instanz läuft auf dem Knoten „doag10“. Auf den Systemen „doag20“ und „doag30“ laufen die Replicas.

Wir bauen über den MySQL Router eine Verbindung zu unserem Cluster auf. Dazu nutzen wir den neuen Port 6450: 

root@doag30# mysql -uadmin -padmin -P6450 -hdoag30

mysql> select @@hostname;
+------------+
| @@hostname |
+------------+
| doag20     |
+------------+
1 row in set (0.01 sec)
 

Wir wurden auf eines der beiden Replica-Systeme durchgestellt („doag20“). Dieses ist im Modus „R/O“ (read only). Trotzdem werden wir im Folgenden auch einige schreibende Aktivitäten durchführen, u. a.:

Das Anlegen einer Datenbank („Split“) und Tabelle „"split_test“)

Das Schreiben in diese Tabelle 

mysql> create database split;
Query OK, 1 row affected (0.04 sec)

mysql> select @@hostname;
+------------+
| @@hostname |
+------------+
| doag20     |
+------------+
1 row in set (0.03 sec)

mysql> use split;
Database changed
mysql> create table split_test ( id int primary key auto_increment, value varchar(20));
Query OK, 0 rows affected (0.02 sec)

mysql> insert into split_test values (null, @@hostname);
Query OK, 1 row affected (0.03 sec)
 

Wie wir unschwer erkennen können, haben unsere Aktionen zu keinen Problemen geführt. Sowohl die Objekte als auch der zu erzeugende Datensatz konnten erzeugt werden. Beim INSERT haben wir über die SQL-Variable „@@hostname“ des Namens des MySQL-Knotens in die Tabelle geschrieben. Die Initiale Verbindung hatten wir zu diesem Zeitpunkt ja zu einem rein lesenden System („doag20“). Was ist hier also passiert?

Wenn wir uns den erzeugten Datensatz anschauen, sehen wir, dass der Router dieser Anfrage (INSERT) an das „Source“-System („doag10“) geroutet hat. Dies ist natürlich auch bereits vorher für das „CREATE DATABASE“ und „CREATE TABLE“-Kommando erfolgt.

Die nachfolgenden lesenden Zugriffe werden dann aber wieder gehen das Replikat („doag20“) „gefahren". 

mysql> select * from split_test;
+----+--------+
| id | value  |
+----+--------+
|  1 | doag10 |
+----+--------+
1 row in set (0.01 sec)

mysql> select @@hostname;
+------------+
| @@hostname |
+------------+
| doag20     |
+------------+
1 row in set (0.01 sec)
 

Das Testprotokoll / Fazit

Wieder ein nützliches „Feature" im Umfeld der MySQL InnoDB-Clusters. Nun muss nicht mehr die Applikation entscheiden, welche Kommandos über welchen Port laufen sollen. In vielen Standard-Produkten ist eine multiple Port-Konfiguration ohnehin gar nicht möglich.Über denen neuen „Read/Write Split"-Port kann nun auch ein „Read-Scale-Out" ohne großen Aufwand realisiert werden. Aus unserer Sicht ist die ein echter Mehrwert.

Weitere interessante Beiträge um den MySQL InnoDB-Cluster und den Router finden Sie hier: 

Lesen lernen: Der MySQL InnoDB Cluster & Read Replicas

Mach Dich unsichtbar: MySQL Generated Invisible Primary Keys

Kurz und gut – Episode #10 Sag mir, woher Du kommst!

Ein Geheimnis behalten: MySQL Router „Bootstrapping" ohne „root"-Account

Immer schön friedlich. Monitoring des MySQL-Router mit der RESTful API

Doppelt gemoppelt: Wie man Cluster "clustered". MySQL-InnoDB-ClusterSets