MySQL-replicatie: gids voor master-replica-opzet

Naarmate een gameserver of een druk bezochte webapplicatie groeit, heeft één enkele database moeite om al het lees- en schrijfverkeer in zijn eentje te dragen. Met MySQL-replicatie verdeel je die belasting door je data van één hoofdserver (master) naar een of meer kopieën (replica/slave) bijna in realtime te kopiëren: schrijfacties gaan naar de master, terwijl zware leesqueries over de replica's worden verdeeld. In deze gids bouw je een master-replica-opzet vanaf nul, gebruik je de moderne GTID-gebaseerde aanpak en leer je hoe je de gezondheid van je replica bewaakt.

Hoe werkt replicatie?

De kern van MySQL-replicatie is de binary log (binlog). Elke datawijzigende bewerking op de master (INSERT, UPDATE, DELETE, DDL) wordt naar de binlog geschreven. De replica maakt verbinding met de master, kopieert die log naar zijn eigen relay log en speelt de events vervolgens op zichzelf opnieuw af. Het proces draait met twee threads:

• I/O-thread: leest de binlog van de master en schrijft die naar de relay log van de replica.
• SQL-thread (applier): past de events uit de relay log toe op de data van de replica.

Standaardreplicatie is asynchroon: de master commit zijn transactie zonder te wachten tot de replica het event heeft ontvangen. Daardoor kan de replica enkele seconden achterlopen op de master, wat replication lag wordt genoemd. Je hiervan bewust zijn is cruciaal bij het ontwerpen van leesdistributie.

De masterserver configureren

De eerste stap is het inschakelen van de binary log op de master en het geven van een unieke identiteit aan de server. Elke replicatieknoop moet een netwerkbreed unieke server_id hebben. Voeg dit toe aan de sectie [mysqld] van je my.cnf-bestand:

[mysqld]
server_id = 1
log_bin = /var/log/mysql/mysql-bin.log
binlog_format = ROW
gtid_mode = ON
enforce_gtid_consistency = ON
bind_address = 0.0.0.0

binlog_format = ROW registreert wijzigingen op rijniveau en is veel betrouwbaarder dan de modus STATEMENT. gtid_mode = ON schakelt moderne GTID-gebaseerde replicatie in, die het positiebeheer automatiseert. Herstart de service na de wijziging:

sudo systemctl restart mysql

Maak vervolgens een speciale gebruiker aan waarmee de replica verbinding maakt. Geef die alleen het recht REPLICATION SLAVE; deel geen brede permissies uit:

CREATE USER 'repl'@'%' IDENTIFIED BY 'SterkWachtwoord!';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
FLUSH PRIVILEGES;

Bestaande data naar de replica verplaatsen

Voordat de replica kan bijbenen, moet hij vanaf hetzelfde punt als de bestaande data starten. De schoonste manier om een consistente momentopname te maken is mysqldump. Met GTID ingeschakeld wordt de begin-GTID-set direct in de dump ingebed:

mysqldump --all-databases --single-transaction \
  --source-data=2 --triggers --routines --events \
  -u root -p > volledige-backup.sql

--single-transaction maakt een consistente momentopname van InnoDB-tabellen zonder ze te vergrendelen, en --source-data=2 voegt de binlog-positie als commentaar toe. Kopieer het bestand naar de replica en importeer het:

scp volledige-backup.sql gebruiker@replica-ip:/tmp/
# op de replica:
mysql -u root -p < /tmp/volledige-backup.sql

De replicaserver verbinden

Geef de replica in zijn my.cnf een andere server_id en overweeg hem alleen-lezen te maken, zodat een onbedoelde schrijfactie de replicatie niet kan breken:

[mysqld]
server_id = 2
gtid_mode = ON
enforce_gtid_consistency = ON
read_only = ON
super_read_only = ON
relay_log = /var/log/mysql/relay-bin.log

Na het herstarten van de service richt je de replica op de master. In MySQL 8 gebruiken de moderne commando's de SOURCE-terminologie:

CHANGE REPLICATION SOURCE TO
  SOURCE_HOST = '10.0.0.1',
  SOURCE_USER = 'repl',
  SOURCE_PASSWORD = 'SterkWachtwoord!',
  SOURCE_AUTO_POSITION = 1;

START REPLICA;

Dankzij GTID laat SOURCE_AUTO_POSITION = 1 de replica vanaf het juiste punt doorgaan zonder dat je handmatig een binlog-bestandsnaam en positie hoeft in te voeren. In oudere versies is het equivalent CHANGE MASTER TO ... MASTER_AUTO_POSITION = 1 gevolgd door START SLAVE.

Replicatie bewaken en problemen oplossen

Om te controleren of de replica daadwerkelijk draait, vraag je zijn status op:

SHOW REPLICA STATUS\G

Let in de uitvoer vooral op deze drie velden:

• Replica_IO_Running: Yes en Replica_SQL_Running: Yes — beide threads moeten draaien.
• Seconds_Behind_Source — hoeveel seconden de replica achterloopt op de master. Als dit blijft oplopen, kan de replica de belasting niet bijbenen.
• Last_Error — als dit niet leeg is, is de SQL-thread vastgelopen bij het toepassen van een event.

De meest voorkomende oorzaken van lag zijn trage schijven, ontbrekende indexen op de replica en single-threaded toepassing. In MySQL 8 kun je events parallel toepassen door replica_parallel_workers te verhogen:

SET GLOBAL replica_parallel_workers = 4;

Wil je sterkere consistentiegaranties, dan kun je semi-synchrone replicatie inschakelen; in die modus bevestigt de master een commit pas wanneer minstens één replica het event in zijn relay log heeft geschreven. Je moet de plugin installeren en inschakelen:

INSTALL PLUGIN rpl_semi_sync_source SONAME 'semisync_source.so';
SET GLOBAL rpl_semi_sync_source_enabled = 1;

De leesbelasting verdelen

Het hele doel van replicatie is het opsplitsen van de belasting. Houd in je applicatie twee connection pools aan: schrijfacties en consistentiegevoelige queries zoals SELECT ... FOR UPDATE gaan naar de master, terwijl zware leesacties zoals rapportages en lijsten naar de replica's gaan. Frameworks als Laravel ondersteunen dit op configuratieniveau:

'mysql' => [
  'read'  => ['host' => ['10.0.0.2']],
  'write' => ['host' => ['10.0.0.1']],
  'sticky' => true,
],

sticky => true is een cruciaal detail: als je binnen hetzelfde verzoek een schrijfactie hebt uitgevoerd, stuurt het ook de volgende leesacties naar de master, zodat je niet door lag data leest die op de replica nog niet zichtbaar is.

Veelgestelde vragen

Is replicatie hetzelfde als een back-up?

Nee. Omdat de replica elke bewerking op de master herhaalt, verwijdert hij ook meteen een tabel die je per ongeluk hebt gewist. Replicatie is voor hoge beschikbaarheid en lastverdeling; voor dataherstel heb je nog steeds regelmatige mysqldump- of fysieke back-ups nodig.

Kan ik meer dan één replica toevoegen?

Ja. Een master kan meerdere replica's voeden; geef elk een andere server_id en herhaal dezelfde opzetstappen. Dit is de meest gangbare manier om lezen horizontaal te schalen.

Wat moet ik doen als de replica achterop raakt bij de master?

Bewaak eerst Seconds_Behind_Source. Bij aanhoudende lag versnel je de schijf van de replica (SSD/NVMe), verhoog je het aantal parallelle applier-workers en voeg je de ontbrekende indexen op de replica toe. Het opsplitsen van zware bulkschrijfacties in kleinere batches vermindert de lag ook merkbaar.

Laat de databaselaag je niet afremmen terwijl je je server opschaalt. Wil je een hoogbeschikbare MySQL-replicatie-architectuur ontwerpen voor je gameserver of webproject, neem dan contact met me op en we zetten het samen op.