MySQL database replicatie

Thuis wilde ik een goede en stabiele DNS-infrastructuur. Ik had het standaard DNS op de router kunnen gebruiken, maar ik zocht iets anders.

Ik had een paar wensen:

• Snel en betrouwbaar
• Iets anders dan BIND
• Bewerken via webinterface én commandoregel moet kunnen

De oplossing werd uiteindelijk een PowerDNS-server op een virtuele machine en dezelfde setup op een fysieke machine. De gegevens staan in een MySQL-database op beide machines en worden onderling gerepliceerd.

Zo hebben beide machines dezelfde informatie en is het niet erg als er één uitvalt.

MySQL-database-replicatie configureren  

Ik ga ervan uit dat je MySQL al op je servers hebt geïnstalleerd. Zo niet, dan zijn er genoeg handleidingen, bijvoorbeeld how to install MySQL .

In het voorbeeld gebruik ik deze namen en IP-adressen:

Stap 1 – MySQL-masterserver voorbereiden  

Zorg eerst dat je server bereikbaar is en klaar om voor replicatie te configureren. Pas daarvoor het MySQL-configuratiebestand aan. (Als je vi niet prettig vindt, kun je nano gebruiken.) Ik ga ervan uit dat je dit niet als root doet, daarom staat overal sudo voor.

sudo vi /etc/mysql/my.cnf

Serveridentiteit  

Pas het bind-adres aan van:

bind-address = 127.0.0.1

naar

bind-address = 192.168.1.10

Wijzig ook de server-ID:

server-id = 1

Als deze instellingen er nog niet staan, kun je ze in een apart bestand zetten, bijvoorbeeld:

/etc/mysql/conf.d/address.conf

Zorg dat de instellingen onder [mysqld] staan.

Logbestanden  

Voor replicatie heb je een binair logbestand nodig met alle wijzigingen in de database.

log_bin = /var/log/mysql/mysql-bin.log

En we geven aan welke database wordt gerepliceerd:

binlog_do_db = pdns

MySQL herstarten  

Sla het bestand op (:wq in vi) en herstart MySQL, bijvoorbeeld met sudo service mysql restart op Debian/Ubuntu.

Stap 2 – Configureren voor replicatie  

Voor de verdere stappen gebruik ik:

De replicatie zelf stel je in via de MySQL-commandoregel:

mysql -uroot -p

Rechten toekennen  

Geef eerst rechten aan de slave-MySQL-server.

GRANT REPLICATION SLAVE ON *.* TO 'remote_user'@'%' IDENTIFIED BY 'password';
FLUSH PRIVILEGES;

Let op: deze replicatiegebruiker heeft toegang tot alle databases. Wil je dat niet, geef dan alleen rechten op pdns.*.

Replicatie instellen  

Gebruik de database die je wilt repliceren en voorkom wijzigingen:

USE PDNS;
FLUSH TABLES WITH READ LOCK;

Controleer of alles goed staat:

SHOW MASTER STATUS;

Je zou een tabel moeten zien zoals:

Onthoud deze waarden, die heb je straks nodig.

Dump de database naar een bestand:

! mysqldump -u root -p --opt pdns > pdns.sql

Met ! voer je een shellcommando uit. Ondersteunt jouw MySQL dat niet, open dan een tweede terminal en voer mysqldump daar uit—als je de mysql-sessie sluit, worden de databases weer ontgrendeld.

Na de export:

UNLOCK TABLES;
QUIT;

De master-database is nu klaar voor replicatie.

Stap 3 – De slave-databaseserver configureren  

Kopieer het bestand pdns.sql naar de slave, bijvoorbeeld met scp:

scp pdns.sql user@db02:

waarbij user een gebruiker is met toegang tot server db02.

Alle volgende stappen doe je op de slave.

Database aanmaken  

Er moet een database bestaan om naar te repliceren. Maak die in de mysql-shell:

create database pdns

en importeer de data:

mysql -uroot -p pdns < pdns.sql

MySQL configureren  

Net als op de master pas je my.cnf op de slave aan:

server-id = 2
log_bin = /var/log/mysql/mysql-bin.log
relay-log = /var/log/mysql/mysql-relay-bin.log
binlog_do_db = pdns

Sla op en herstart MySQL.

Stap 4 – Replicatie inschakelen  

Als master en slave klaarstaan, schakel je replicatie in.
We wijzen deze server aan als slave van master 192.168.1.10, met gebruiker remote_user en het punt waar de replicatie moet beginnen.

CHANGE MASTER TO MASTER_HOST='192.168.1.10',MASTER_USER='remote_user',
MASTER_PASSWORD='password', MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=  107; 

Omdat ik dit voor DNS had ingericht, gebruikte ik bewust geen hostnamen.

Start de replicatie met START SLAVE;

Controleer of alles draait:

SHOW SLAVE STATUS\G

Je krijgt dan iets als:

************************** 1. row ***************************
   	            Slave_IO_State: Waiting for master to send event
   	               Master_Host: 192.168.1.10
   	               Master_User: remote_user
              Master_Port: 3306
   	             Connect_Retry: 60
   	           Master_Log_File: mysql-bin.000001
   	       Read_Master_Log_Pos: 107
   	            Relay_Log_File: mysql-relay-bin.000001
   	             Relay_Log_Pos: 266
   	     Relay_Master_Log_File: mysql-bin.000001
   	          Slave_IO_Running: Yes
   	         Slave_SQL_Running: Yes
...

(alleen het begin van de output)

Troubleshooting  

Met SHOW SLAVE STATUS\G zie je veel details over het replicatieproces.

Wachtwoorden  

Sommige wachtwoorden werken niet bij replicatie. Eén van mijn wachtwoorden bevatte een hekje (‘#’). Pas toen ik dat verwijderde, kwam replicatie goed op gang.

Firewall  

Staat er in SHOW SLAVE STATUS\G ‘connecting’, dan blokkeert vaak een firewall de verbinding.

Zet poort 3306 (MySQL, tcp/udp) open in je firewall.
Voor ufw kun je een applicatieprofiel in /etc/ufw/applications.d zetten:

[MySQL]
title=MySQL
description=MySQL - database server
ports=3306

Laad het profiel:

ufw app update MySQL

en schakel het in:

ufw allow MySQL

Controleer met ufw status. Je zou iets moeten zien als:

To                         Action      From
--                         ------      ----
Nginx Full                 ALLOW       Anywhere
OpenSSH                    ALLOW       Anywhere
PowerDNS                   ALLOW       Anywhere
MySQL                      ALLOW       Anywhere
Nginx Full (v6)            ALLOW       Anywhere (v6)
OpenSSH (v6)               ALLOW       Anywhere (v6)
PowerDNS (v6)              ALLOW       Anywhere (v6)
MySQL (v6)                 ALLOW       Anywhere (v6)

Voor andere firewalls: zie de handleiding van je product.

Replicatie faalt  

Met deze statement sla je het volgende event van de master over:

SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; 
SLAVE START; 

Afsluiting  

Zoals je ziet is replicatie instellen niet extreem moeilijk, maar je moet de stappen netjes volgen. Daarna kun je je database naar andere servers laten repliceren zonder meteen complexe clusters nodig te hebben.