Replicazione MySQL

Perché sto scrivendo questo articolo?

Perché oggi è mi venuta voglia di fare esperimenti con la replicazione di MySQL (anche se di solito non smanio per lavorare con questo database ). L’idea è di capire come funziona la configurazione di base della replicazione con lo scopo di creare un servizio ad alta affidabilità (High Availability).

MySQL come la maggior parte dei RDMS consente di definire un pool di server nei quali esiste un server master e uno o più server slave sui quali vengono replicate in automatico tutte le operazioni effettuate sul master. Lo scopo è quello di disporre di uno o più backup, aggiornati in tempo reale, della base dati principale. L’utilità è la più varia: mero backup, possibilità di sveltire le operazioni di lettura istruendo il sistema ad effettuare le scritture sul master e le letture sul/sugli slave (anche a livello di singola tabella) e così via.

L’argomento è vasto e la modalità descritta è una delle tante possibili. La replicazione può essere asincrona o semi sincrona, ed inoltre i server slave possono a loro volta fungere da master per altri slave e così via. Per i miei esperimenti mi limiterò a mettere in piedi la soluzione più semplice ovvero un server master e un server slave.

Questo non è un tutorial vero e proprio, si tratta solamente dei miei appunti personali pertanto mi concentrerò più sul come che sul perché. Se volete approfondire trovate migliaia di tutorial scritti in maniera migliore e che si concentrano di più sul perché vengono fatte determinate operazioni.

Infine, per effettuare i miei esperimenti, avrò bisogno di configurare server LAMP e macchine virtuali quindi le informazioni che leggerete possono tornare utili per mettere in piedi questi servizi in maniera semplice e veloce .

N.B. Parliamo di esperimenti quindi non pensiate di usare questa configurazione in produzione così come ve la illustrerò. Buona parte delle normali norme di sicurezza sono state ignorate per velocizzare il processo quindi bisognerebbe rivedere tutta la parte relativa alla sicurezza (magari in futuro se avrò voglia lo farò!).

Piano di battaglia

L’idea è quella di usare la mia linux box (Linux Mint 19.2 configurata come server LAMP con Apache 2.4, MySQL 5.7 e PHP 7.2) come sistema Master (da ora Host) e una macchina virtuale (da adesso Guest) come sistema slave sul quale replicare il database che gira su host.

Per la macchina virtuale Guest, avendo io necessità base, ho scelto di usare:

• Virtual Box 6.0 (scaricato direttamente dal sito del prodotto )

• Ubuntu Server 18.04.02 LTS (dal sito di Canonical)

Per testare il tutto vorrei qualcosa di più di una banale interazione da CLI di MySQL pertanto ho deciso di installare una banale webapp di esempio per vedere in maniera più utile i risultati della replicazione.

Essendo io un fan di F3 ( Fat Free Framework ) ho cercato e trovato una piccola webapp che faccia uso di tale framework scrivendo i dati su MySQL: la potete scaricare qua!

N.B. : il progetto è vecchio pertanto se lo scaricate ed installate così com’è otterrete un bel errore:

Fatal error: Cannot use $this as parameter in lib/base.php on line 1296

dipeso dal fatto che la versione di F3 usata è obsoleta e ha problemi con PHP 7 : basta scaricare la versione aggiornata di F3 e tutto funzionerà egregiamente (vi basta sovrascrivere la directory lib del progetto con quella della nuova versione di F3).

Installazione e configurazione componenti

Procediamo quindi all’installazione dei singoli componenti necessari all’esperimento.

Virtual Machine

Installare VirtualBox 6.0 e creare una macchina virtuale usando la iso di ubuntu server 18.04. Per la configurazione della macchina virtuale usare il default; l’unica opzione che ho variato riguarda la rete : ho selezionato la modalità scheda con bridge selezionando poi l’interfaccia del wifi.

Seguire l’installazione standard avendo cura di attivare il demone ssh quando l’installer lo richiede.

Terminata l’installazione autentichiamoci con l’utente creato e verifichiamo che tutto funzioni e procediamo agli aggiornamenti di rito:

sudo apt-get update && sudo apt-get update

Infine è necessario installare i Guest additions Tools di virtual box : non disponendo di GUI (siamo su Ubuntu server!) usare l’opzione del menu di virtual box tornerà un bel errore (anche se la iso sul cdrom virtuale sarà regolarmente compilata, vedi impostazioni della macchina vituale). E’ necessario procedere manualmente creando un mount point per il cdrom:

sudo mkdir /media/cdrom

sudo mount /dev/cdrom /media/cdrom

servono poi dei pacchetti specifici:

apt-get install -y dkms build-essential linux-headers-generic linux-headers-$(uname -r)

A questo punto riavviate la macchina virtuale, spostatevi in /media/cdrom e installate i Guest Additions Tools:

sudo ./VBoxLinuxAdditions.run

Scambiare le informazioni tra Host e Guest può essere un problema perché l’uso della clipboard richiede che guest disponga di una GUI (che non abbiamo) quindi l’unica soluzione è usare una cartella condivisa tra Host e Guest. Creiamo il mount point:

sudo mkdir /media/host

e configuriamo il tutto dall’interfaccia di Virtual box : basta selezionare il nome della cartella che useremo su Host e farla puntare al mount point appena creato. Riavviare la macchina virtuale e verificare che l’accesso alla cartella condivisa funzioni.

MySQL

Queste istruzioni saranno eseguite solo su Guest (Ubuntu 18.04 LTS) giacché Host (Linux Mint 19.2) è già configurato ma se non è così per voi potete tranquillamente usarle anche su Host : vanno bene su tutte le distro Debian e Ubuntu derivate.

sudo apt-get install mysql-server

poi configuriamo le opzioni per la sicurezza:

sudo mysql_secure_installation

(basta seguire le istruzioni proposte, è abbastanza semplice!)

Infine è necessario configurare le opzioni per l’autenticazione: MySQL ci consente di accedere al sistema senza password ma non è l’opzione che noi desideriamo usare pertanto:

sudo mysql

e procediamo ad impostare l’accesso per l’utente root tramite password:

mysql > ALTER USER ‘root’@’localhost’ IDENTIFIED WITH mysql_native_password BY ‘<password_di_root>’;

mysql > FLUSH PRIVILEGES;

a questo punto procediamo all’accesso “regolare”:

sudo mysql -u root -p

e verifichiamo che tutto funzioni.

Apache e PHP

Anche in questo caso la procedura riguarderà solo Guest ma si può fare anche su Host:

sudo apt-get install apache2 php libapache2-mod-php php-mysql

infine abilitare le rewrite rules di apache (servono a F3):

sudo a2enmod rewrite

sudo systemctl reload apache2

Webapp

Infine configuriamo la webapp di prova: copiamo i sorgenti scaricati in /var/www/html avendo cura di sostituire la sotto cartella lib del progetto con quella dell’ultima versione di F3 scaricata.

Creiamo la configurazione per il virtual host di apache:

<VirtualHost *:80>
        # The ServerName directive sets the request scheme, hostname and port that
        # the server uses to identify itself. This is used when creating
        # redirection URLs. In the context of virtual hosts, the ServerName
        # specifies what hostname must appear in the request's Host: header to
        # match this virtual host. For the default virtual host (this file) this
        # value is not decisive as it is used as a last resort host regardless.
        # However, you must set it for any further virtual host explicitly.
        ServerName f3simpleajaxcrud.it
        ServerAlias www.f3simpleajaxcrud.it

        ServerAdmin webmaster@localhost
        DocumentRoot /var/www/html/F3SimpleAjaxCRUD

        <Directory "/var/www/html/F3SimpleAjaxCRUD">
            Options Indexes FollowSymLinks MultiViews
            # changed from None to FileInfo
            AllowOverride FileInfo
            Order allow,deny
            allow from all
        </Directory>

        # Available loglevels: trace8, ..., trace1, debug, info, notice, warn,
        # error, crit, alert, emerg.
        # It is also possible to configure the loglevel for particular
        # modules, e.g.
        #LogLevel info ssl:warn

        ErrorLog ${APACHE_LOG_DIR}/error.log
        CustomLog ${APACHE_LOG_DIR}/access.log combined

        # For most configuration files from conf-available/, which are
        # enabled or disabled at a global level, it is possible to
        # include a line for only one particular virtual host. For example the
        # following line enables the CGI configuration for this host only
        # after it has been globally disabled with "a2disconf".
        #Include conf-available/serve-cgi-bin.conf
</VirtualHost>

da salvare in /etc/apache2/sites-available/www.f3simpleajaxcrud.it.conf. Infine attiviamo il virtual host con:

sudo a2ensite www.f3simpleajaxcrud.it.conf

sudo systemctl reload apache2

Questa configurazione va bene sia per Host che per Guest ma richiede una significativa variazione. Mentre su host avremmo le righe:

ServerName f3simpleajaxcrud.it
ServerAlias www.f3simpleajaxcrud.it

Su guest avremmo invece:

ServerName f3simpleajaxcrud2.it
ServerAlias www.f3simpleajaxcrud2.it

anche se siamo su una rete privata non possono esistere due domini uguali!

Infine la webapp richiede di essere configurata quindi procediamo a creare il database e il relativo utente (dalla cli di MySQL ovviamente):

mysql > CREATE DATABASE f3ajax;

mysql > CREATE USER f3ajax@’localhost’ IDENTIFIED BY ‘F3@jax!!’;

mysql > GRANT ALL PRIVILEGES ON *.* TO f3ajax@’localhost’ WITH GRANT OPTION;

mentre dalla cli di linux carichiamo il database:

sudo mysql -u root -p f3ajax < /var/www/html/F3SimpleAjaxCRUD/db.sql

modifichiamo ora il file /etc/hosts del solo server Host (sul Guest non server) aggiungendo le seguenti righe:

127.0.0.1       www.f3simpleajaxcrud.it
192.168.1.4     www.f3simpleajaxcrud2.it

dove 192.168.1.4 è l’ip assegnato a Guest.

Visitando http://www.f3simpleajaxcrud.it/ dovrebbe rispondere la webapp installata su host mentre visitando http://www.f3simpleajaxcrud2.it/ dovrebbe rispondere la webapp su guest.

Replicazione MySQL

Infine passiamo alla configurazione della replicazione di MySQL quindi andiamo a editare il file di configurazione di MySQL che per ubuntu e derivate si trova in /etc/mysql/mysql.conf.d/mysqld.cnf.

Come prima cosa è necessario configurare l’ip reale della macchina (per default è impostato su 127.0.0.1 quindi gli accessi funzionano solo sulla macchina locale)

bind-address = 192.168.1.3

Questo dovrà essere fatto sia su Host (192.168.1.3) che su Guest (192.168.1.4)

Su Host impostiamo i parametri per il master:

log-bin=mysql-bin
binlog-do-db=f3ajax
server-id=1
innodb_flush_log_at_trx_commit=1

da notare server-id = 1 per identificare il nodo e binlog-do-db = f3ajax per identificare il database da replicare (è possibile replicare più database contemporaneamente separandone i nomi tramite virgola oppure non indicare la direttiva replicandoli tutti).

A questo punto è necessario creare un utente specifico da usare per la replicazione e dare a questo utente il permesso di accedere ai database che vogliamo replicare:

mysql > GRANT REPLICATION SLAVE ON *.* TO ‘<nome_utente>’@’%’ IDENTIFIED BY ‘<password>’;

mysql > GRANT ALL PRIVILEGES ON f3ajax.* TO ‘<nome_utente>’@’%’;

mysql > FLUSH PRIVILEGES;

Ora bisogna predisporre una copia del database per lo slave. Nel nostro caso non serve: il database è già attivo e non è stato modificato ma supponendo dobbiate effettuare l’operazione su un server in uso riporto le istruzioni per impostare il server in sola lettura, effettuare il backup dei dati e ri-attivare la modalità lettura-scrittura del database. Iniziando impostando il database in sola lettura:

mysql > FLUSH TABLE WITH READ LOCK;

poi da riga di comando si procede alla creazione del backup:

mysqldump -u root -p –opt database_da_copiare > nome_dump.sql

e da interfaccia MySQL eseguiamo il comando:

mysql > SHOW MASTER STATUS;

il cui output dovrebbe essere del tipo:

+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000012 | 611      |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

infine riportiamo il database in modalità lettura-scrittura

mysql > UNLOCK TABLES;

Passiamo ora allo slave e procediamo a modificare i parametri di configurazione di MySQL:

log-bin=mysql-bin
binlog-do-db=f3ajax
server-id=2

da interfaccia MySQL eseguiamo il seguente comando:

mysql > CHANGE MASTER TO MASTER_HOST=’192.168.1.3′,MASTER_USER='<utente_replicazione>’, MASTER_PASSWORD='<password_utente-replicazione>’, MASTER_LOG_FILE=’mysql-bin.000001′, MASTER_LOG_POS=  611;

Da notare che è stato riportato il numero di posizione raggiunto dal master (611) in modo da istruire lo slave in merito alla posizione di partenza. Facciamo quindi partire la replicazione sullo slave:

mysql > START SLAVE;

ed è fatta! Verifichiamo (sempre da interfaccia MySQL) come procede il tutto:

mysql > SHOW SLAVE STATUS\G;

Se tutto va bene la voce Slave_IO_State dovrebbe essere valorizzata con Waiting for master to send event.

Se qualcosa non va bisognerà fare un po’ di debugging ma seguendo i passi descritti non dovrebbero esserci problemi.

Può capitare che qualche comando eseguito sul master non possa essere replicato sullo slave e quindi che la replicazione si blocchi ! ( Il comando SHOW SLAVE STATUS ci mostra sempre lo stato della replicazione sullo slave). In questi casi basta agire sullo slave bloccando la replicazione, saltando la riga (si presume l’ultima eseguita) e ri-attivando la replicazione.

mysql > STOP SLAVE; SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; START SLAVE;

Può capitare ci siano errori ricorrenti legati alla tipologia di errore (es. record duplicato) : in questi casi si possono indicare nel file di configurazione l’elenco degli errori da non considerare separandoli con una virgola. Per questo è altri dettagli meglio diate un’occhiata alla documentazione ufficiale 😉