Replikation verstehen: Primary, Replica, Lag und Failover

1. Wie MySQL-Replikation funktioniert

MySQL Replikation ist der Mechanismus, mit dem Änderungen von einem Primary-Server auf einen oder mehrere Replica-Server übertragen werden. Das Grundprinzip ist dabei konstant geblieben: Der Primary schreibt alle Änderungen in das Binary Log. Der Replica liest das Binary Log über einen I/O-Thread und schreibt es lokal als Relay Log. Ein SQL-Thread führt dann die Änderungen aus dem Relay Log auf der Replica aus.

Diese Architektur hat weitreichende Konsequenzen für den Betrieb. Replikation ist standardmäßig asynchron — der Primary wartet nicht darauf, dass die Replica die Änderung übernommen hat. Wenn der Primary ausfällt, bevor die Replica aufgeholt hat, gehen diese Änderungen verloren. Das ist kein Bug, sondern ein Designkompromiss zugunsten niedrigerer Schreiblatenzen.

Für Magento-Shops bedeutet das: Eine Read Replica entlastet den Primary von Leseabfragen aus Reports, Admin-Backend und Drittsystemen — aber sie ist kein Hochverfügbarkeits-Mechanismus ohne zusätzliche Failover-Automatisierung. Beides zusammen ergibt jedoch eine robuste Datenbankarchitektur für mittelgroße bis große Shops.

2. Asynchrone vs. Semi-synchrone Replikation

Die Standard-Replikation in MySQL ist asynchron: Der Primary schreibt die Transaktion ins Binary Log und antwortet dem Client sofort, ohne auf Bestätigung durch die Replica zu warten. Das maximiert die Schreibgeschwindigkeit, kann aber beim Ausfall des Primary zu Datenverlust führen, wenn der Replica-I/O-Thread hinter dem Binary Log zurückliegt.

Semi-synchrone Replikation ist ein Mittelweg: Der Primary wartet nach einem COMMIT darauf, dass mindestens eine Replica die Transaktion ins Relay Log geschrieben — aber noch nicht ausgeführt — hat. Das reduziert das Risiko von Datenverlust beim Failover erheblich, erhöht aber die Schreiblatenz. In MySQL 8.0 ist semi-synchrone Replikation über das rpl_semi_sync_source_*-Plugin verfügbar.

-- Check current replication mode on primary
SHOW VARIABLES LIKE 'rpl_semi_sync%';

-- Check async replication lag (run on replica)
SHOW REPLICA STATUS\G
-- Key fields:
-- Seconds_Behind_Source: estimated lag in seconds
-- Source_Log_File: binary log file on primary that IO thread is reading
-- Read_Source_Log_Pos: position in that file
-- Relay_Source_Log_File: binary log file that SQL thread is applying
-- Exec_Source_Log_Pos: position SQL thread has applied up to
-- Replica_IO_Running: Yes = IO thread is running
-- Replica_SQL_Running: Yes = SQL thread is running
-- Last_Error: last replication error (should be empty)

3. Binary Log Formate: STATEMENT, ROW und MIXED

Das Binary Log Format bestimmt, wie Änderungen auf der Replica angewendet werden. Es hat erheblichen Einfluss auf Log-Größe, Replikationssicherheit und Diagnosierbarkeit. MySQL 8 verwendet standardmäßig ROW-basiertes Format.

STATEMENT-Format: Das Binary Log enthält die ursprünglichen SQL-Statements. Das erzeugt sehr kleine Logs und ist gut lesbar. Es schlägt aber bei nicht-deterministischen Funktionen fehl — NOW(), RAND() oder UUID() liefern auf Primary und Replica unterschiedliche Werte. Das kann zu Datendivergenz führen, die schwer zu erkennen ist.

ROW-Format: Das Binary Log enthält die tatsächlich geänderten Zeilen — nicht das Statement, sondern die Vorher/Nachher-Werte jeder betroffenen Zeile. Das ist größer, aber exakt und sicher. Eine Row-basierte Replikation ist stabil gegenüber nicht-deterministischen Funktionen und funktioniert auch für komplexe Triggers korrekt.

MIXED-Format: MySQL wechselt automatisch zwischen STATEMENT und ROW, abhängig davon, ob ein Statement nicht-deterministisch ist. Das war in älteren MySQL-Versionen ein guter Kompromiss, in MySQL 8 ist ROW jedoch der Standard und in den meisten Setups vorzuziehen.

-- Check the binary log format
SHOW VARIABLES LIKE 'binlog_format';
-- Recommended: ROW for MySQL 8

-- Check binary log position and files (run on primary)
SHOW MASTER STATUS;
SHOW BINARY LOGS;

-- Read binary log events (useful for debugging replication issues)
SHOW BINLOG EVENTS IN 'binlog.000042' LIMIT 20;

-- For ROW format: use mysqlbinlog with --verbose to see row changes
-- mysqlbinlog --verbose --base64-output=DECODE-ROWS binlog.000042 | head -100

4. GTID-Replikation: Einfacheres Failover

GTID (Global Transaction Identifier) ist ein eindeutiger Bezeichner für jede Transaktion im Replikationsverbund. Ein GTID besteht aus der UUID des Servers, auf dem die Transaktion ursprünglich ausgeführt wurde, und einer Sequenznummer: UUID:Sequenznummer, zum Beispiel 3E11FA47-71CA-11E1-9E33-C80AA9429562:23.

Der entscheidende Vorteil von GTID gegenüber der klassischen dateibasierten Replikation: Die Replica weiß genau, welche Transaktionen sie bereits ausgeführt hat und welche noch fehlen. Beim Failover muss kein neues Binary Log File und keine neue Position ermittelt werden — ein neuer Primary wird einfach auf Basis der GTID-Position synchronisiert. Das macht automatisierte Failover-Tools wie MHA und Orchestrator deutlich zuverlässiger.

-- Check if GTID mode is active
SHOW VARIABLES LIKE 'gtid_mode';
SHOW VARIABLES LIKE 'enforce_gtid_consistency';

-- Show which GTIDs have been executed on this server
SELECT @@global.gtid_executed;

-- Show which GTIDs the replica still needs to apply
-- (GTID set difference between primary and replica)
-- Run on replica:
SELECT gtid_subtract(
    '3E11FA47-71CA-11E1-9E33-C80AA9429562:1-100',  -- primary GTID set
    @@global.gtid_executed                           -- replica GTID set
) AS missing_gtids;

-- Set up replication with GTID (MySQL 8 syntax)
-- CHANGE REPLICATION SOURCE TO
--     SOURCE_HOST     = 'primary-host',
--     SOURCE_USER     = 'replication_user',
--     SOURCE_PASSWORD = 'replication_password',
--     SOURCE_AUTO_POSITION = 1;  -- GTID-based, no file/pos needed
-- START REPLICA;

5. Replikations-Lag diagnostizieren

Replikations-Lag ist die Zeitdifferenz, um die der Replica-Stand hinter dem Primary zurückliegt. Das Feld Seconds_Behind_Source in SHOW REPLICA STATUS gibt eine Schätzung — aber diese Schätzung hat eine wichtige Einschränkung: Sie basiert auf dem Timestamp des aktuell angewendeten Events. Wenn der Replica-SQL-Thread wartet, zeigt das Feld 0, obwohl der Replica in Wirklichkeit weit hinter dem Primary liegt.

Das Werkzeug pt-heartbeat aus dem Percona Toolkit löst dieses Problem: Es schreibt regelmäßig einen Timestamp in eine kleine Tabelle auf dem Primary und misst auf der Replica, wie alt dieser Timestamp dort ist. Das ergibt eine präzise, unabhängige Lag-Messung.

-- Basic replica status (run on replica server)
SHOW REPLICA STATUS\G

-- Key fields to monitor for lag:
-- Seconds_Behind_Source   — lag estimate (may be 0 even when lagging)
-- Replica_IO_Running       — should be "Yes"
-- Replica_SQL_Running      — should be "Yes"
-- Last_Error               — should be empty
-- Relay_Log_Space          — size of relay log (large = SQL thread behind)

-- Check relay log size vs. binary log position gap
SELECT
    (SELECT variable_value FROM performance_schema.global_status WHERE variable_name = 'Relay_Log_Space')  AS relay_log_space_bytes;

-- pt-heartbeat setup on primary (run once):
-- CREATE TABLE heartbeat (
--     ts             DATETIME(6) NOT NULL,
--     server_id      INT UNSIGNED NOT NULL,
--     file           VARCHAR(255),
--     position       BIGINT UNSIGNED,
--     relay_master_log_file VARCHAR(255),
--     exec_master_log_pos   BIGINT UNSIGNED,
--     PRIMARY KEY (server_id)
-- );
-- pt-heartbeat --update --database magento_db --daemonize

-- Monitor lag on replica:
-- pt-heartbeat --monitor --database magento_db

6. Lag-Ursachen und Lösungen: MTA und pt-heartbeat

Replikations-Lag entsteht aus mehreren möglichen Quellen. Die häufigste ist die Verarbeitungsgeschwindigkeit: Standardmäßig führt der Replica-SQL-Thread Änderungen seriell aus — ein Thread, eine Transaktion nach der anderen. Wenn der Primary unter Last viele parallele Schreiboperationen ausführt, kann der SQL-Thread der Replica einfach nicht mithalten.

Die Lösung ist Multi-Threaded Applier (MTA) — parallele Replikation. MySQL 8 unterstützt WRITESET-basierte parallele Replikation, die Transaktionen, die keine gemeinsamen Zeilen betreffen, parallel auf der Replica anwenden kann. Das ist deutlich effizienter als die ältere datenbankbasierte Parallelisierung.

-- Enable parallel replication on replica (MySQL 8)
-- Run on replica or set in my.cnf:
SET GLOBAL replica_parallel_workers       = 8;
SET GLOBAL replica_parallel_type         = 'WRITESET';
SET GLOBAL replica_preserve_commit_order = ON;

-- Check current parallel replication settings
SHOW VARIABLES LIKE 'replica_parallel%';
SHOW VARIABLES LIKE 'replica_preserve_commit_order';

-- Identify large transactions causing lag (run on primary)
-- Large single transactions lock the replica SQL thread for their entire duration
SELECT
    trx_id,
    trx_started,
    TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS running_seconds,
    trx_rows_modified,
    trx_rows_locked
FROM information_schema.innodb_trx
WHERE TIMESTAMPDIFF(SECOND, trx_started, NOW()) > 10
ORDER BY running_seconds DESC;

-- Check for replication errors and clear if safe to do so
SHOW REPLICA STATUS\G
-- If Last_Error shows a known safe-to-skip error:
-- SET GLOBAL SQL_REPLICA_SKIP_COUNTER = 1;
-- START REPLICA;

Weitere häufige Lag-Ursachen: lange DDL-Operationen (ALTER TABLE) auf dem Primary sperren die Replica-SQL-Thread für die gesamte Dauer; schwache Replica-Hardware mit langsamerem Storage als der Primary; und zu kleine innodb_buffer_pool_size auf der Replica, die zu mehr Disk-I/O führt.

7. Magento mit Read Replica konfigurieren

Magento unterstützt nativ die Konfiguration einer Read Replica (Slave Connection) über die Datenbankverbindungskonfiguration. Leseabfragen werden dann automatisch auf die Replica geleitet, während Schreiboperationen weiterhin auf dem Primary ausgeführt werden. Wichtig: Da Replikation asynchron ist, kann die Replica kurz hinterherhinken. Für zeitkritische Lesevorgänge direkt nach einem Schreibvorgang muss die Anwendung das berücksichtigen.

-- Verify replica is in sync before enabling in Magento
-- Run on replica:
SHOW REPLICA STATUS\G
-- Seconds_Behind_Source should be 0 or near 0

-- Test read query on replica (to verify it has current data)
SELECT entity_id, increment_id, status, created_at
FROM sales_order
ORDER BY created_at DESC
LIMIT 5;

Die Konfiguration in Magento erfolgt in app/etc/env.php unter dem Schlüssel db. Der slave_connection-Block nimmt die Verbindungsdaten der Replica auf. Magento's Ressourcenmodell leitet dann automatisch Leseabfragen an die Replica-Verbindung weiter.

-- env.php slave_connection configuration (PHP array, shown as reference):
-- 'db' => [
--     'connection' => [
--         'default' => [
--             'host'     => 'primary-host',
--             'dbname'   => 'magento',
--             'username' => 'magento',
--             'password' => 'secret',
--         ],
--         'slave_connection' => [
--             'default' => [
--                 'host'     => 'replica-host',
--                 'dbname'   => 'magento',
--                 'username' => 'magento_readonly',
--                 'password' => 'secret',
--             ],
--         ],
--     ],
-- ],

-- Create a read-only user for the replica connection
-- Run on primary (will replicate to replica):
CREATE USER 'magento_readonly'@'%' IDENTIFIED BY 'strong_password_here';
GRANT SELECT ON magento.* TO 'magento_readonly'@'%';
FLUSH PRIVILEGES;

8. Failover: MHA und Orchestrator

Manueller Failover ist bei einem unerwarteten Primary-Ausfall zu langsam. Tools wie MHA (Master High Availability Manager) und Orchestrator automatisieren diesen Prozess: Sie erkennen den Primary-Ausfall, wählen die am wenigsten zurückliegende Replica aus, synchronisieren die anderen Replicas auf den neuen Stand und promoten die ausgewählte Replica zum neuen Primary.

-- Manual failover checklist on replica (when primary is down):

-- Step 1: Stop replication and check final position
STOP REPLICA;
SHOW REPLICA STATUS\G
-- Note: Exec_Source_Log_Pos — last applied position

-- Step 2: Verify no pending relay log events
SELECT COUNT(*) AS pending_events
FROM performance_schema.replication_applier_status_by_worker;

-- Step 3: Promote replica to primary
RESET REPLICA ALL;
-- Remove slave configuration, this server is now primary

-- Step 4: Verify GTID state on new primary
SELECT @@global.gtid_executed AS committed_transactions;

-- Step 5: Update other replicas to point to new primary
-- CHANGE REPLICATION SOURCE TO
--     SOURCE_HOST        = 'new-primary-host',
--     SOURCE_AUTO_POSITION = 1;  -- GTID-based auto-position
-- START REPLICA;

-- Step 6: Verify other replicas are catching up
SHOW REPLICA STATUS\G

Orchestrator bietet gegenüber MHA zusätzlich ein Web-Interface zur Visualisierung der Replikationstopologie und ermöglicht komplexere Topologien wie Kaskaden-Replikation. Für Magento-Shops mit einer einfachen Primary/Replica-Topologie ist MHA jedoch gut geeignet und einfacher zu betreiben.

10. Zusammenfassung

11. FAQ: MySQL Replikation