MySQL-Replikation - Master-Slave-Setup einrichten

MySQL-Replikation ermöglicht die automatische Synchronisierung von Daten zwischen mehreren Datenbankservern. Sie ist essentiell für Hochverfügbarkeit, Backups und Lastverteilung.

Replikations-Typen

Übersicht

| Typ | Beschreibung | Verwendung | |-----|--------------|------------| | Asynchron | Standard, leicht verzögert | Allgemein | | Semi-synchron | Bestätigung vor Commit | Höhere Konsistenz | | Gruppen-Replikation | Multi-Master-fähig | Hochverfügbarkeit |

Architektur

                    ┌─────────────┐
                    │   Master    │
                    │ (Schreiben) │
                    └──────┬──────┘
                           │
              Binary Log   │
                           │
           ┌───────────────┼───────────────┐
           │               │               │
    ┌──────┴──────┐ ┌──────┴──────┐ ┌──────┴──────┐
    │   Slave 1   │ │   Slave 2   │ │   Slave 3   │
    │  (Lesen)    │ │  (Lesen)    │ │  (Backup)   │
    └─────────────┘ └─────────────┘ └─────────────┘

Voraussetzungen

Server-Daten

Master:  192.168.1.10
Slave:   192.168.1.11

Netzwerk-Konnektivität

# Vom Slave aus testen
telnet 192.168.1.10 3306

Firewall öffnen

# Auf Master
ufw allow from 192.168.1.11 to any port 3306

Master-Konfiguration

my.cnf anpassen

# /etc/mysql/mysql.conf.d/mysqld.cnf (Master)

[mysqld]
# Eindeutige Server-ID
server-id = 1

# Binary Log aktivieren
log_bin = /var/log/mysql/mysql-bin.log
binlog_format = ROW
binlog_expire_logs_seconds = 604800
max_binlog_size = 100M

# Synchrone Schreibvorgänge
sync_binlog = 1

# Welche Datenbanken replizieren
# binlog-do-db = mydb
# binlog-ignore-db = mysql

# InnoDB für Crash-Recovery
innodb_flush_log_at_trx_commit = 1

# Bind Address für Remote-Zugriff
bind-address = 0.0.0.0

MySQL neu starten

systemctl restart mysql

Replikations-Benutzer erstellen

-- Auf Master ausführen
CREATE USER 'replication'@'192.168.1.11' IDENTIFIED BY 'sicheres_passwort';
GRANT REPLICATION SLAVE ON *.* TO 'replication'@'192.168.1.11';
FLUSH PRIVILEGES;

Master-Status ermitteln

-- Binary Log Position notieren
SHOW MASTER STATUS;

+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |      154 |              |                  |
+------------------+----------+--------------+------------------+

Slave-Konfiguration

my.cnf anpassen

# /etc/mysql/mysql.conf.d/mysqld.cnf (Slave)

[mysqld]
# Eindeutige Server-ID (unterschiedlich vom Master)
server-id = 2

# Relay Log
relay-log = /var/log/mysql/mysql-relay-bin.log

# Read-Only (empfohlen)
read_only = 1
super_read_only = 1

# Binary Log auf Slave (für Ketten-Replikation)
# log_bin = /var/log/mysql/mysql-bin.log
# log_slave_updates = 1

# Crash-Safe
relay_log_recovery = 1
relay_log_info_repository = TABLE
master_info_repository = TABLE

MySQL neu starten

systemctl restart mysql

Daten synchronisieren

Mit mysqldump (kleine Datenbanken)

# Auf Master: Daten exportieren
mysqldump --all-databases --master-data=2 --single-transaction \
    --routines --triggers --events -u root -p > master_dump.sql

# Dump auf Slave übertragen
scp master_dump.sql root@192.168.1.11:/tmp/

# Auf Slave: Daten importieren
mysql -u root -p < /tmp/master_dump.sql

Mit Lock (große Datenbanken)

-- Auf Master
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;
-- Position notieren

-- In anderem Terminal: Dump erstellen
mysqldump --all-databases -u root -p > /tmp/master_dump.sql

-- Lock aufheben
UNLOCK TABLES;

Mit Percona XtraBackup (produktiv)

# Auf Master: Backup erstellen
xtrabackup --backup --target-dir=/backup/full -u root -p

# Backup vorbereiten
xtrabackup --prepare --target-dir=/backup/full

# Auf Slave kopieren und wiederherstellen
rsync -avz /backup/full/ slave:/var/lib/mysql/

Replikation starten

Slave konfigurieren

-- Auf Slave ausführen
CHANGE MASTER TO
    MASTER_HOST='192.168.1.10',
    MASTER_USER='replication',
    MASTER_PASSWORD='sicheres_passwort',
    MASTER_LOG_FILE='mysql-bin.000001',
    MASTER_LOG_POS=154;

-- Replikation starten
START SLAVE;

-- Status prüfen
SHOW SLAVE STATUS\G

MySQL 8.0+ Syntax

-- Neue Syntax ab MySQL 8.0.22
CHANGE REPLICATION SOURCE TO
    SOURCE_HOST='192.168.1.10',
    SOURCE_USER='replication',
    SOURCE_PASSWORD='sicheres_passwort',
    SOURCE_LOG_FILE='mysql-bin.000001',
    SOURCE_LOG_POS=154;

START REPLICA;
SHOW REPLICA STATUS\G

Status prüfen

Wichtige Felder

SHOW SLAVE STATUS\G

*************************** 1. row ***************************
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
        Seconds_Behind_Master: 0
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 154
               Relay_Log_File: mysql-relay-bin.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000001
                   Last_Error:

Erwartete Werte

| Feld | Erwartet | |------|----------| | Slave_IO_Running | Yes | | Slave_SQL_Running | Yes | | Seconds_Behind_Master | 0 (oder niedrig) | | Last_Error | (leer) |

GTID-basierte Replikation

Was ist GTID?

Global Transaction Identifier
- Eindeutige ID für jede Transaktion
- Vereinfacht Failover
- Keine manuelle Binlog-Position nötig

Master-Konfiguration

# /etc/mysql/mysql.conf.d/mysqld.cnf

[mysqld]
server-id = 1
log_bin = /var/log/mysql/mysql-bin.log
binlog_format = ROW

# GTID aktivieren
gtid_mode = ON
enforce_gtid_consistency = ON

Slave-Konfiguration

[mysqld]
server-id = 2
relay-log = /var/log/mysql/mysql-relay-bin.log
read_only = 1

# GTID aktivieren
gtid_mode = ON
enforce_gtid_consistency = ON

GTID-Replikation starten

-- Auf Slave
CHANGE MASTER TO
    MASTER_HOST='192.168.1.10',
    MASTER_USER='replication',
    MASTER_PASSWORD='sicheres_passwort',
    MASTER_AUTO_POSITION=1;

START SLAVE;
SHOW SLAVE STATUS\G

Semi-synchrone Replikation

Plugin installieren

-- Auf Master
INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
SET GLOBAL rpl_semi_sync_master_enabled = 1;

-- Auf Slave
INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
SET GLOBAL rpl_semi_sync_slave_enabled = 1;

-- Slave neu verbinden
STOP SLAVE;
START SLAVE;

Permanente Konfiguration

# Master
[mysqld]
rpl_semi_sync_master_enabled = 1
rpl_semi_sync_master_timeout = 10000

# Slave
[mysqld]
rpl_semi_sync_slave_enabled = 1

Status prüfen

-- Auf Master
SHOW STATUS LIKE 'Rpl_semi_sync%';

+--------------------------------------------+-------+
| Variable_name                              | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_status                | ON    |
| Rpl_semi_sync_master_clients               | 1     |
+--------------------------------------------+-------+

Mehrere Slaves

Konfiguration

# Slave 2
[mysqld]
server-id = 3
relay-log = /var/log/mysql/mysql-relay-bin.log
read_only = 1

Chained Replication

Master → Slave 1 → Slave 2 → Slave 3

# Slave 1 als Relay
[mysqld]
server-id = 2
log_bin = /var/log/mysql/mysql-bin.log
log_slave_updates = 1
read_only = 1

Failover

Manueller Failover

-- Auf altem Master: Schreibvorgänge stoppen
SET GLOBAL read_only = 1;
SHOW MASTER STATUS;

-- Auf Slave: Replikation prüfen
SHOW SLAVE STATUS\G
-- Warten bis Seconds_Behind_Master = 0

-- Slave zum Master promoten
STOP SLAVE;
RESET SLAVE ALL;
SET GLOBAL read_only = 0;

-- Anwendung auf neuen Master umstellen

Mit Orchestrator

# Installation
wget https://github.com/openark/orchestrator/releases/download/v3.2.6/orchestrator_3.2.6_amd64.deb
dpkg -i orchestrator_3.2.6_amd64.deb

# Web-Interface: http://server:3000

Troubleshooting

Replikations-Fehler

-- Fehler überspringen (vorsichtig!)
STOP SLAVE;
SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
START SLAVE;

-- Bei GTID
STOP SLAVE;
SET GTID_NEXT='uuid:transaction_id';
BEGIN; COMMIT;
SET GTID_NEXT='AUTOMATIC';
START SLAVE;

Replikation neu aufbauen

-- Auf Slave
STOP SLAVE;
RESET SLAVE ALL;

-- Daten neu synchronisieren
-- (mysqldump oder xtrabackup)

-- Replikation neu starten
CHANGE MASTER TO ...;
START SLAVE;

Lag analysieren

-- Aktuelle Position
SHOW SLAVE STATUS\G

-- Binary Log Events
SHOW BINLOG EVENTS IN 'mysql-bin.000001' LIMIT 10;

-- Langsame Queries auf Slave
SHOW PROCESSLIST;

IO Thread Probleme

-- Verbindung prüfen
-- Slave_IO_Running: No

-- Mögliche Ursachen:
-- 1. Netzwerk-Problem
-- 2. Falsches Passwort
-- 3. Firewall blockiert
-- 4. Master nicht erreichbar

-- Test vom Slave
mysql -h 192.168.1.10 -u replication -p

SQL Thread Probleme

-- Slave_SQL_Running: No
-- Last_SQL_Error: ...

-- Häufige Ursachen:
-- 1. Duplicate Key
-- 2. Table nicht vorhanden
-- 3. Unterschiedliche Schema

-- Last Error anzeigen
SHOW SLAVE STATUS\G

Monitoring

Replikations-Lag

#!/bin/bash
# /usr/local/bin/check-replication.sh

LAG=$(mysql -N -e "SHOW SLAVE STATUS\G" | grep "Seconds_Behind_Master" | awk '{print $2}')

if [ "$LAG" == "NULL" ]; then
    echo "CRITICAL: Replication not running"
    exit 2
elif [ "$LAG" -gt 300 ]; then
    echo "WARNING: Replication lag is ${LAG}s"
    exit 1
else
    echo "OK: Replication lag is ${LAG}s"
    exit 0
fi

Prometheus + mysqld_exporter

# prometheus.yml
scrape_configs:
  - job_name: 'mysql'
    static_configs:
      - targets: ['localhost:9104']

Best Practices

Empfohlene Einstellungen

# Master
[mysqld]
server-id = 1
log_bin = /var/log/mysql/mysql-bin.log
binlog_format = ROW
gtid_mode = ON
enforce_gtid_consistency = ON
sync_binlog = 1
innodb_flush_log_at_trx_commit = 1

# Slave
[mysqld]
server-id = 2
relay-log = /var/log/mysql/mysql-relay-bin.log
read_only = 1
super_read_only = 1
gtid_mode = ON
enforce_gtid_consistency = ON
relay_log_recovery = 1

Sicherheit

-- Minimale Rechte für Replikations-User
CREATE USER 'replication'@'slave_ip' IDENTIFIED BY 'passwort';
GRANT REPLICATION SLAVE ON *.* TO 'replication'@'slave_ip';

-- SSL für Replikation
CHANGE MASTER TO
    MASTER_SSL = 1,
    MASTER_SSL_CA = '/etc/mysql/ca.pem',
    MASTER_SSL_CERT = '/etc/mysql/client-cert.pem',
    MASTER_SSL_KEY = '/etc/mysql/client-key.pem';

Zusammenfassung

| Komponente | Master | Slave | |------------|--------|-------| | server-id | Eindeutig | Eindeutig | | log_bin | An | Optional | | read_only | Nein | Ja | | relay-log | - | An |

| Befehl | Funktion | |--------|----------| | SHOW MASTER STATUS | Position anzeigen | | SHOW SLAVE STATUS | Replikations-Status | | START SLAVE | Replikation starten | | STOP SLAVE | Replikation stoppen |

| Problem | Lösung | |---------|--------| | IO Thread down | Netzwerk/Auth prüfen | | SQL Thread down | Fehler analysieren | | Hoher Lag | Slave-Performance |

Fazit

MySQL-Replikation ist fundamental für Hochverfügbarkeit und Skalierung. GTID-basierte Replikation vereinfacht Failover erheblich. Semi-synchrone Replikation bietet höhere Datensicherheit auf Kosten der Performance. Monitoring ist essentiell um Replikationsprobleme frühzeitig zu erkennen. Für produktive Umgebungen sollte immer ein automatisiertes Failover-System wie Orchestrator eingesetzt werden.