In dit deel zetten wij de synchronisatie op van de databases die je op je Linux VPS'en host. Het doel is om een SQL-cluster te maken, waarbij twee of meer servers een identieke database bevatten. Bij downtime van één of meerdere servers nemen de andere(n) automatisch of handmatig (afhankelijk van je eigen keuze) de volledige functionaliteit van het cluster over.

In deze tutorials gebruiken wij dit SQL-cluster om een database in combinatie met een of meerdere websites te gebruiken, bijvoorbeeld een WordPress- of dynamische-website. Hoe je een SQL-cluster hieraan koppelt lichten wij later in deze tutorial series toe.

Voor het opzetten van een SQL-cluster gebruiken wij een MariaDB master-slave-configuratie. Bij een master-slave-setup is één van de VPS'en in je SQL-cluster de master en voert write-queries (write, drop, create, etc) uit naar zichzelf, en naar alle aan je cluster gekoppelde slave VPS'en. De slave VPS('en) voeren zelf hooguit read-queries uit (select from).

Kort gezegd is een write-query een actie waarbij data naar je database wordt weggeschreven en een read-query een actie waarbij data vanuit je database wordt opgehaald, zonder die te wijzigen.

De master-slave-replicatie opzetten

Voor het opzetten van je master-slave-setup zijn er enkele aanpassingen nodig in de MariaDB-configuratie, bijvoorbeeld om ervoor te zorgen dat je VPS'en elkaar correct kunnen identificeren. Daarnaast maak je een SQL-gebruiker aan die specifiek gebruikt wordt voor de slave-vps om met de master te kunnen communiceren.

Stap 1

Verbind met je VPS'en via SSH of de VPS console in je controlepaneel. Kies vervolgens één van je VPS'en die vanaf dit punt dienst gaat doen als master van je database cluster en noteer welke dit is. Open nu op deze VPS je MySQL-configuratiebestand:

CentOS / AlmaLinux / Rocky Linux :

nano /etc/my.cnf.d/server.cnf

of voor de nieuwste versies van MariaDB:

nano /etc/my.cnf.d/mariadb-server.cnf

Ubuntu / Debian:

nano /etc/mysql/mariadb.conf.d/50-server.cnf

Stap 2

In je configuratiebestand staat onder [mysqld] de regel #bind-address=0.0.0.0 of #bind-address=127.0.0.1 (al dan niet met # ervoor). MariaDB luistert standaard enkel naar TCP/IP verbindingen van de localhost. Dit betekent dat de slave(s) in je cluster standaard geen rechten hebben om met je master te verbinden.

Verwijder de # en pas de 0.0.0.0 aan naar ::

bind-address=::

Let op: als je in dit bestand de optie skip-networking ziet staan, zorg dan dat deze optie niet op 1 staat. Anders kunnen slave(s) niet verbinden met je master.

Stap 3

Voeg direct onder [mariadb] de inhoud hieronder toe:

log-bin
log_basename=dbmaster
server_id=1
wsrep_gtid_mode=ON
gtid_strict_mode=1

• log-bin: activeert binary logging. In binary logs worden alle veranderingen in de databases bijgehouden. Hiervoor wordt gebruik gemaakt van een index file en binary log files.
• log_basename: is de naam die wordt toegevoegd aan de naam van alle logbestanden die je cluster aanmaakt (niet enkel de binary logs). Gebruik je deze optie niet, dan wordt je hostname gebruikt, wat problemen geeft wanneer die om wat voor reden dan ook verandert.
• server_id: is het nummer waarmee je VPS geïdentificeerd wordt in het cluster. Dit moet per VPS uniek zijn.
• wsrep_gtid_mode: schakelt het gebruik van global transaction ID's (GTID) in. Deze zijn vanaf MariaDB 10.0  geïntroduceerd in MariaDB. Hierbij worden events (i.e. nummers) gekoppeld aan iedere event group in de binary logs (binlogs). Bij replicatie van event groups wordt de global transaction ID intact gehouden. Dit maakt het eenvoudiger om dezelfde binlogs events te identificeren. Voor een gedetailleerdere toelichting, zie de documentatie hierover van MariaDB.
• gtid_strict_mode: door deze optie op 1 te zetten, sluiten de GTID's van de master en slave op elkaar aan. Stel dat de originele master GTID 0-1-500 heeft en vervolgens onbereikbaar wordt, dan schrijft de slave de nieuwe wijzigingen weg vanaf 0-2-501 (de 1 en 2 identificeren welke server acties uitvoert).

Stap 4

Sla je wijzigingen op en sluit het bestand (ctrl + x > y > enter). Herhaal bovenstaande stappen op je andere VPS die je als slave gaat gebruiken. Geef daarbij een unieke server_id in stap 3.

De reden dat je dit ook op je slave-VPS instelt, is dat dit nodig is voor de failover functionaliteit die je in het volgende deel opzet. Zo verzeker je je setup ervan dat als je master uitvalt en een slave de nieuwe master wordt, de oude master (en eventuele andere slave(s)) daarmee kunnen verbinden wanneer die weer beschikbaar is.

Stap 5

Herstart MariaDB op alle VPS'en:

systemctl restart mariadb

Stap 6

Log in op een SQL-shell op de master- en Slave-VPS (verander eventueel root naar de daadwerkelijke gebruikersnaam). Er zal om een wachtwoord worden gevraagd van je root-user:

mysql -u root -p

Stap 7

Maak met onderstaande stappen een MariaDB-gebruiker aan op je master- en slave-VPS. Deze gebruiker zal door de slave(s) gebruikt worden om met de master te verbinden. 'Slavename' en 'password' vervang je door de gebruikersnaam en het wachtwoord die je wil gebruiken voor je slave(s) om met de master te verbinden.

CREATE USER 'slavename'@'LOCALHOST' IDENTIFIED BY 'password';
GRANT REPLICATION SLAVE ON *.* TO 'slavename'@'localhost' IDENTIFIED BY 'password' WITH GRANT OPTION;
FLUSH PRIVILEGES;

Stap 8

Voer het volgende commando uit en herhaal het op iedere VPS in je database-cluster voor iedere VPS. Stel dat je drie VPS'en gebruikt, dan herhaal je per VPS deze instructie twee keer (een keer per VPS).

Pas het private network IP-adres aan naar die van de andere VPS('en) dan de VPS waarop je het commando uitvoert. Stel dat je twee VPS'en hebt met IP's 192.168.1.1 en 192.168.1.2 en je voert het commando uit vanaf de VPS met het IP 192.168.1.1, dan verander je het IP in dit commando naar 192.168.1.2.

Vervang 'Slavename' en 'password' je de gebruikersnaam en het wachtwoord die je wil gebruiken voor je slave(s) om met de master te verbinden.

GRANT REPLICATION SLAVE ON *.* TO 'slavename'@'192.168.1.1' IDENTIFIED BY 'password' WITH GRANT OPTION;

Stap 9

Controleer met het volgende commando de huidige status van je Master:

SELECT @@global.gtid_binlog_pos;

De output zal er ongeveer als volgt uitzien:

+--------------------------+
| @@global.gtid_binlog_pos |
+--------------------------+
| 0-1-111                  |
+--------------------------+

De GTID (hier 0-1-111) heb je in stap 18 nodig om de slave(s) aan te geven vanaf welk punt zij de database moeten repliceren.

Achtergrondinformatie: Voor MariaDB 10.0 werd de waarde van file & position uit dit overzicht gebruikt om de slave(s) aan te geven waar ze moeten beginnen met de replicatie van de databases. Met de komst van global transaction ID's (zie stap 3) is dit echter niet meer nodig en wordt de global transcation ID (GTID) gebruikt.

Stap 10

Zijn er sinds je aan deze tutorial bent begonnen geen wijzigingen geweest aan je MariaDB-database, ga dan door naar stap 15. Zijn er wel wijzigingen geweest, kopieer dan eerst je database naar je slave(s) door de volgende stappen te gebruiken.

Plaats een tijdelijke lock op je database op beide VPS'en, zodat deze read-only wordt. Je voorkomt hiermee dat er veranderingen plaatsvinden in je database(s) terwijl je je database(s) overzet.

FLUSH TABLES WITH READ LOCK; 

Stap 11

Plaats vanuit command-line de back-up over. Hieronder lichten wij de command-line stappen toe (of gebruik phpMyAdmin om de database te exporteren op de master en te importeren op de slave).

Bekijk welke databases je allemaal gebruikt zodat je zeker weet dat je de juiste naam gebruikt voor stap 12:

SHOW DATABASES;
exit

Stap 12

Back-up vervolgens je database(s). Er zal wederom om je root wachtwoord worden gevraagd:

mysqldump -u root -p wordpress > /var/lib/mysql/wordpress-dump.sql

Voer dit commando uit voor iedere database waarvan je een back-up wil overzetten naar je slave VPS. Vervang wordpress door de daadwerkelijke naam van de databases en wordpress-dump.sql door de gewenste back-up bestandsnaam.

Stap 13

Verbind via SSH met je slave-VPS en kopieer de back-up met een Rsync-commando (zie het vorige deel als je Rsync nog niet hebt ingesteld):

rsync -e "ssh -p 22 -o StrictHostKeyChecking=no" transip@192.168.1.1:/var/lib/mysql/wordpress-dump.sql /var/lib/mysql/ --no-perms --no-owner --no-group --no-times

Vervang 22 door het nummer van je SSH-poort, de gebruikersnaam en het IP naar je daadwerkelijke gebruikersnaam en IP, en de back-up bestandsnaam en locatie naar de naam en locatie die je bj de stappen hierboven gebruikte.

Stap 14

Importeer de back-up door het volgende commando uit te voeren op je slave-VPS (wijzig de database-naam en locatie naar de daadwerkelijke naam en locatie):

mysql -u root -p wordpress < /var/lib/mysql/wordpress-dump.sql

Vergeet niet op je master de lock te herstellen:

mysql -u root -p
FLUSH TABLES WITH READ LOCK; 

Stap 15

Mocht je dit nog niet gedaan hebben, open dan nu SQL-poort 3306 voor SQL-servers en de VPS'en die van de database gebruik gaan maken. 

Vervang hier 192.168.1.0/24 door de range waar de adressen van je private network in vallen. Alternatief kun je ook ieder private IP-adres van je SQL-servers één voor één whitelisten met deze commando's:

Firewalld (CentOS, AlmaLinux, Rocky Linux)

firewall-cmd --permanent --zone=public --add-rich-rule='rule family=ipv4 source address=192.168.1.0/24 port port=3306 protocol=tcp accept'
firewall-cmd --reload

UFW (Ubuntu/Debian):

ufw allow from 192.168.1.0/24 to any port 3306

Stap 16

We gaan nu de slave(s) configureren om voor write-queries de master te gebruiken die we in de eerdere stappen hebben geconfigureerd. Start eerst een SQL shell op je slave als je daar niet nog steeds op zit:

mysql -u root -p

Stap 17

Stop de slave met het commando:

STOP SLAVE;

Stap 18

Wij vertellen de slave nu om de master te gebruiken voor write-acties, en waar hij de master kan vinden:

CHANGE MASTER TO 
MASTER_HOST='192.168.1.1',
MASTER_USER='slavename',
MASTER_PASSWORD='password',
MASTER_PORT=3306,
MASTER_CONNECT_RETRY=10,
MASTER_USE_GTID=current_pos;

• MASTER_HOST: Gebruik hier het (bij voorkeur) interne IP van je master.
• MASTER_USER: De gebruikersnaam die je in stap 6 aan hebt gemaakt.
• MASTER_PASSWORD: Het wachtwoord dat bij de 'Slave' gebruiker hoort.
• MASTER_PORT: De poort die MariaDB gebruikt. Voeg deze toe aan je firewall als je dat nog niet gedaan hebt.
• MASTER_CONNECT_RETRY: Het aantal pogingen dat gebruikt wordt om met de master te verbinden indien de eerste poging niet slaagt.
• MASTER_USE_GTID: Wanneer je master uitvalt, wordt automatisch een van je gekoppelde slave(s) de nieuwe master (hier komen we nog op terug). De oude master zal wanneer die weer bereikbaar is dan een slave zijn. Door de optie current_pos te gebruiken zal wanneer de oude master (die dus nu een slave is) weten vanaf welke global transaction ID de replicatie gestart moet worden. 

Stap 19

Sluit op alle VPS'en de MySQL-shell af en upgrade de system tables door op de VPS'en de volgende commando's uit te voeren:

exit
mysql_upgrade -u root -p

Stap 20

Je slave heeft waarschijnlijk nog een andere gtid dan je master (zie stap 9). Corrigeer je slave-VPS met de onderstaande commando's om vanaf de gtid van de master te synchronizeren. Vervang hier de waarde x-x-x door de waarde die je in stap 9 hebt genoteerd:

mysql -u root -p 
RESET MASTER;
STOP SLAVE;
SET GLOBAL gtid_slave_pos='x-x-x';
START SLAVE UNTIL master_gtid_pos='x-x-x';

Je krijgt een melding 'Query OK' te zien. Deze geeft echter niet aan of je slave daadwerkelijk goed verbonden is. Hiervoor gebruik je het commando:

SHOW SLAVE STATUS\G;

Problemen oplossen

Alle vormen van database clusters zijn gecompliceerd en het is dan ook onmogelijk uit te sluiten dat door omstandigheden je een probleem ervaart, bijvoorbeeld als een slave een tijd offline is geweest en een foutieve GTID probeert te gebruiken. In dit artikel worden enkele vaker voorkomende foutmeldingen behandelt.

Je databasecluster is nu geconfigureerd om automatisch je databases te synchronizeren. Daarmee zijn wij aan het eind gekomen van dit deel. Klik hier om verder te gaan met deel 4: het instellen van MaxScale en MariaDB monitor waarin je o.a. de automatische failover functionaliteit van je databasecluster configureert.