Database Replication: Hochverfügbarkeit

Datenbank-Replikation verteilt Daten auf mehrere Server für Verfügbarkeit und Skalierung. Lernen Sie Master-Slave, Master-Master und moderne Replikations-Strategien.

Replikations-Typen

1. MASTER-SLAVE (Primary-Replica)
   ┌──────────────┐
   │    MASTER    │◄───── Alle Writes
   │  (Primary)   │
   └──────┬───────┘
          │ Replication
    ┌─────┴─────┐
    ▼           ▼
┌───────┐  ┌───────┐
│SLAVE 1│  │SLAVE 2│◄───── Reads verteilt
│(Read) │  │(Read) │
└───────┘  └───────┘

2. MASTER-MASTER (Multi-Primary)
   ┌──────────────┐     ┌──────────────┐
   │   MASTER 1   │◄───►│   MASTER 2   │
   │ Reads/Writes │     │ Reads/Writes │
   └──────────────┘     └──────────────┘
          ↑                    ↑
          │    Bidirektional   │
          │    Replication     │
          └────────────────────┘

3. CIRCULAR REPLICATION
   ┌───────┐     ┌───────┐
   │Node 1 │────►│Node 2 │
   └───┬───┘     └───┬───┘
       ▲             │
       │             ▼
   ┌───┴───┐     ┌───┴───┐
   │Node 4 │◄────│Node 3 │
   └───────┘     └───────┘

Master-Slave Konfiguration (MySQL)

# === MASTER (Primary) ===
# /etc/mysql/mysql.conf.d/mysqld.cnf

[mysqld]
server-id = 1
log_bin = /var/log/mysql/mysql-bin.log
binlog_do_db = myapp_production    # Zu replizierende DB
bind-address = 0.0.0.0

# Für bessere Performance
sync_binlog = 1
innodb_flush_log_at_trx_commit = 1

-- Auf MASTER: Replication User erstellen
CREATE USER 'replication'@'%' IDENTIFIED BY 'secure_password';
GRANT REPLICATION SLAVE ON *.* TO 'replication'@'%';
FLUSH PRIVILEGES;

-- Position für Slave notieren
SHOW MASTER STATUS;
-- +------------------+----------+--------------+
-- | File             | Position | Binlog_Do_DB |
-- +------------------+----------+--------------+
-- | mysql-bin.000003 | 73548    | myapp        |
-- +------------------+----------+--------------+

# === SLAVE (Replica) ===
# /etc/mysql/mysql.conf.d/mysqld.cnf

[mysqld]
server-id = 2                      # Einzigartig pro Slave
relay_log = /var/log/mysql/relay-bin.log
log_bin = /var/log/mysql/mysql-bin.log
read_only = 1                      # Keine Writes auf Slave

-- Auf SLAVE: Replikation konfigurieren
CHANGE MASTER TO
    MASTER_HOST = 'master.db.local',
    MASTER_USER = 'replication',
    MASTER_PASSWORD = 'secure_password',
    MASTER_LOG_FILE = 'mysql-bin.000003',
    MASTER_LOG_POS = 73548;

-- Replikation starten
START SLAVE;

-- Status prüfen
SHOW SLAVE STATUS\G
-- Wichtig:
-- Slave_IO_Running: Yes
-- Slave_SQL_Running: Yes
-- Seconds_Behind_Master: 0

PostgreSQL Streaming Replication

# === PRIMARY ===
# postgresql.conf

wal_level = replica
max_wal_senders = 3
wal_keep_size = 1GB
hot_standby = on

# pg_hba.conf - Replikation erlauben
host replication replicator 192.168.1.0/24 md5

-- Replication User erstellen
CREATE ROLE replicator WITH REPLICATION LOGIN PASSWORD 'secure';

# === STANDBY ===
# Base Backup vom Primary erstellen
pg_basebackup -h primary.db.local -D /var/lib/postgresql/data \
    -U replicator -P --wal-method=stream

# standby.signal erstellen (PostgreSQL 12+)
touch /var/lib/postgresql/data/standby.signal

# postgresql.conf
primary_conninfo = 'host=primary.db.local user=replicator password=secure'
hot_standby = on

Read/Write Splitting

// Application-Level Splitting
class DatabaseManager {
    private PDO $master;
    private array $slaves;
    private int $currentSlave = 0;

    public function __construct(array $config) {
        $this->master = new PDO($config['master']);
        foreach ($config['slaves'] as $slave) {
            $this->slaves[] = new PDO($slave);
        }
    }

    public function getWriteConnection(): PDO {
        return $this->master;
    }

    public function getReadConnection(): PDO {
        // Round-Robin über Slaves
        $slave = $this->slaves[$this->currentSlave];
        $this->currentSlave = ($this->currentSlave + 1) % count($this->slaves);
        return $slave;
    }
}

// Repository nutzt korrekten Server
class UserRepository {
    public function find(int $id): ?User {
        // READ → Slave
        $conn = $this->db->getReadConnection();
        return $this->query($conn, 'SELECT * FROM users WHERE id = ?', [$id]);
    }

    public function save(User $user): void {
        // WRITE → Master
        $conn = $this->db->getWriteConnection();
        $this->execute($conn, 'INSERT INTO users ...', [...]);
    }
}

ProxySQL für automatisches Routing

# ProxySQL Configuration

-- Hostgroups definieren
INSERT INTO mysql_servers(hostgroup_id, hostname, port)
VALUES
    (10, 'master.db.local', 3306),   -- Writer
    (20, 'slave1.db.local', 3306),   -- Reader
    (20, 'slave2.db.local', 3306);   -- Reader

-- Query Rules
INSERT INTO mysql_query_rules(rule_id, active, match_pattern, destination_hostgroup)
VALUES
    (1, 1, '^SELECT', 20),           -- SELECTs zu Slaves
    (2, 1, '.*', 10);                -- Alles andere zu Master

-- User konfigurieren
INSERT INTO mysql_users(username, password, default_hostgroup)
VALUES ('app_user', 'password', 10);

LOAD MYSQL SERVERS TO RUNTIME;
LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;

Replication Lag handhaben

// Problem: Nach Write sofort Read → Daten noch nicht repliziert

// ❌ Falsch
public function createAndReturn(User $user): User {
    $this->repository->save($user);       // → Master
    return $this->repository->find($user->id);  // → Slave (evtl. noch ohne Daten!)
}

// ✅ Lösung 1: Read-Your-Writes
public function createAndReturn(User $user): User {
    $this->repository->save($user);
    return $this->repository->findFromMaster($user->id);  // Master für diesen Read
}

// ✅ Lösung 2: Session-Sticky Reads
// Nach einem Write für X Sekunden nur Master lesen

// ✅ Lösung 3: Synchrone Replikation
// PostgreSQL: synchronous_commit = on
// MySQL: Semi-synchronous replication

// ✅ Lösung 4: Zurückgegebenes Objekt nutzen
public function createAndReturn(User $user): User {
    $this->repository->save($user);
    return $user;  // Bereits das vollständige Objekt
}

Failover-Strategien

AUTOMATISCHES FAILOVER

1. MySQL Group Replication
   - Automatische Primary-Wahl
   - Eingebaute Failure Detection

2. Orchestrator (MySQL)
   - Topology Discovery
   - Automatisches Failover
   - Web UI

3. Patroni (PostgreSQL)
   - HA Solution mit etcd/ZooKeeper
   - Automatisches Failover
   - REST API

4. Cloud-Managed
   - AWS RDS Multi-AZ
   - Google Cloud SQL HA
   - Azure SQL Failover Groups


MANUELLES FAILOVER (MySQL)

-- Auf altem Master (falls noch erreichbar)
SET GLOBAL read_only = ON;
STOP SLAVE;

-- Slave zum neuen Master promoten
STOP SLAVE;
RESET MASTER;
SET GLOBAL read_only = OFF;

-- Andere Slaves auf neuen Master umstellen
CHANGE MASTER TO
    MASTER_HOST = 'new-master.db.local',
    MASTER_LOG_FILE = '...',
    MASTER_LOG_POS = ...;
START SLAVE;

Monitoring

# MySQL Replikations-Status
SHOW SLAVE STATUS\G

# Wichtige Metriken:
# - Seconds_Behind_Master (Lag)
# - Slave_IO_Running (Verbindung zum Master)
# - Slave_SQL_Running (SQL-Threads)
# - Last_Error (Fehler)

# PostgreSQL Replikations-Status
SELECT
    client_addr,
    state,
    sent_lsn,
    write_lsn,
    replay_lsn,
    sync_state
FROM pg_stat_replication;

# Lag prüfen
SELECT
    EXTRACT(EPOCH FROM (now() - pg_last_xact_replay_timestamp()))
    AS replication_lag_seconds;

// Prometheus Metriken exportieren (Beispiel)
$lag = $this->getReplicationLag();

$metrics = [
    'mysql_slave_lag_seconds' => $lag,
    'mysql_slave_running' => $this->isSlaveRunning() ? 1 : 0,
];

// Alert wenn Lag > 30 Sekunden
if ($lag > 30) {
    $this->alerting->trigger('High replication lag', $lag);
}

Weitere Informationen

• ⚖️ Load Balancing
• 💾 Database Sharding
• 📊 Monitoring Grundlagen