Come configurare un sistema di Replicazione Master Slave su Mysql

di Giulio di Bari :: 15 febbraio 2018

La replica MySQL è un processo che consente di copiare automaticamente, ed in tempo reale, un database presente su un server primario, che chiamiamo "master", su un server secondario che chiamiamo "slave". Disponendo di una copia del database, saremo protetti nel caso in cui il master si danneggiasse.

Una replica è utile non solo per conservare un backup dei dati del master. Ad esempio, può servire per analizzare o elaborare i dati copiati dal master usando lo slave, cioè lasciando che il master lavori solo per il sito web evitando così di appesantirlo da attività non necessarie e rendendolo quindi più veloce. Possiamo anche configurare il sito web in modo che legga i dati dallo slave anzichè sul master.

In questo tutorial, effettuato utilizzando una distribuzione CentOS, vediamo un esempio molto semplice di replica mysql: un master invierà i dati a un singolo slave (uno, perchè è possibile anche inviare i dati a più slave per aver così più copie del nostro database).

Per procedere occorre:

Installare MySql su entrambi i server.
Avere due indirizzi IP: uno del server principale, dove è presente il master, e uno del server secondario dove è presente lo slave. Ovviamente connessi tra loro.
Ad esempio:

192.168.1.1 - Server principale "master"
192.168.1.2 - Server secondario "slave"

Installiamo MySql su entrambi i server

Con Yum scarichiamo MySQL, sia il server che il client. Si rimanda ad altro articolo per vedere nel dettaglio il processo di installazione.

# yum install -y mysql-server mysql-client mysql-devel

Configuriamo il MySql MASTER

Siamo sul server 192.168.1.1, ed editiamo, utilizzando l'editor "vi", il file di configurazione "my.cnf"

# vi /etc/mysql/my.cnf

Al suo interno, subito dopo il blocco identificato con [mysqld], aggiungiamo le seguenti righe

[mysqld]
........
server-id = 1
log_bin = /var/log/mysql/mysql-bin.log
binlog_do_db = miodatabase

Con server-id=1 diciamo a MySql che quello è il server master (1 identifica il master)
Nel log_bin indichiamo a MySQL dove salvare il file di log. Possiamo anche eliminare questa riga, lasciando che MySQL utilizzi la sua path di default.
Nel binlog_do_db indichiamo il nome del database oggetto del nostro backup. Se volgiamo fare il backup di tutti i database, eliminiamo questa riga.
Ci sono altre variabili che possono essere aggiunte in questo file, ma per adesso ci interessa mettere in piedi un semplice e funzionante e sistema di replicazione master slave.

Salviamo le modifiche cliccando il tasto "Esc" della tastiera e digitando la sequenza: duepunti, w (write), q (quite)

:wq

RIavviamo MySql

# service mysqld restart

Apriamo il client Mysql (la sua shell) come utente root, usando il comando

# mysql -u root -p

Verrà richiesta la pwd MySQL dell'utente root assegnata in fase di installazione di MySQL. Siamo dentro Mysql!

Adesso dobbiamo assegnare allo slave i privilegi di lettura dei dati dal master: in pratica creiamo un utente con permessi di replica, es. "mioslave" , assegnandogli una password, es. "slavepassword". Prendiamone nota. Serviranno quando lavoreremo sullo slave.

mysql> GRANT REPLICATION SLAVE ON *.* TO 'mioslave'@'%' IDENTIFIED BY 'slavepassword';

Ricarichiamo i privilegi

mysql> FLUSH PRIVILEGES;

Adesso dobbiamo salvare il database che vogliamo mettere in replica, per poi trasferirlo sullo slave. Per farlo innanzitutto blocchiamo il master da possibili scritture per tutto il periodo necessario al backup del database. Da questo momento il master sarà disponibile SOLO in lettura e non in scrittura... per cui agiamo velocemente se il sito è in produzione!

mysql> FLUSH TABLE WITH READ LOCK;

Adesso verifichiamo lo stato del master

mysql> SHOW MASTER STATUS;

Verrà visualizzata una schermata simile a questa

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

In questa schermata viene indicato il nome del file dove è in corso il salvataggio dei log del Master: è il file che abbiamo indicato nel path log_bin del file di configurazione my.cf.
Il file in questione ha un limite di capienza, superato il quale viene storicizzato e ne viene creato uno successivo. Ecco il perchè nel nostro esempio abbiamo "000012" appeso al nome del file (significa che sono stati già salvati 12 file log precedenti)
Prendiamo nota del numero che appare nella colonna "position", nel nostro esempio "107": questo numero rappresenta la posizione, all'interno del file log mysql-bin.000012, dal quale lo slave inizierà a replicare il master quando lanceremo la replica. Tutti dati precedenti infatti li salviamo adesso, facendone un backup, e li importeremo sulla replica. Avendo bloccato da scrittura il Master, nessun altro dato potrà essere aggiunto. Quando toglieremo il blocco da scrittura, il Master riprenderà a scrivere da questo punto "107".

Adesso facciamo il backup del nostro database "miodatabase" creando il file "miobkp.sql". Per farlo dobbiamo aprire un nuovo terminale, perchè in questo momento stiamo lavorando all'interno di MySQL.
Quindi aprite un nuovo terminale e digitate:

# mysqldump -u root -p --opt miodatabase > miobkp.sql

Se invece state facendo un backup globale di tutti i database (quindi nel file my.cf avete rimosso la riga "binlog_do_db") il comando per salvare tutti i database sarà il seguente

# mysqldump -u root -p --all-databases > miobkp.sql

Finito il salvataggio del database, togliamo il blocco da scrittura del master

mysql> UNLOCK TABLES;

Da questo momento il master riprende a scrivere, partendo dalla posizione "107". Da questa posizione lo slave inizierà la replica.
Infine usciamo da MySQL

mysql> QUIT;

Il lavoro sul Master è finito.

Configuriamo il Mysql SLAVE

Ci spostiamo sul server 192.168.1.2, ed editiamo anche qui il file di configurazione "my.cnf", aggiungendo la riga
server-id = 2, dove 2 identifica il primo slave... il primo perchè nulla vieta di aggiungere altri slave, su altri server, che quindi avranno numeri incrementali (3,4...).

[mysqld]
........
server-id = 2

Salviamo il file. E riavviamo MySQL

Accediamo quindi al cliente MySQL e creiamo il database "miodatabase" che volgiamo replicare su questa macchina.

mysql> CREATE DATABASE newdatabase;

Usciamo dal client MySQL

mysql> QUIT;

Importiamo il file salvato dal master

# /usr/bin/mysql --user=root --password=lamiapassword --host=localhost miodatabase < miobkp.sql

Se abbiamo esportato tutti i database, ripetiamo la procedura per ogni database presente nel file miobkp.sql

Riapriamo il client MySQL e, nel caso lo slave fosse già attivo, blocchiamolo (nell'incertezza bloccare sempre)

mysql> STOP SLAVE;

Adesso inchiamo allo slave dov'è il master e le credenziali per accedervi

mysql> CHANGE MASTER TO MASTER_HOST='192.168.1.1',MASTER_USER='mioslave', MASTER_PASSWORD='slavepassword', MASTER_LOG_FILE='mysql-bin.000012', MASTER_LOG_POS=107

Il master è sul server "192.168.1.1", le credenziali le abbiamo create prima sul master, il quale file partire per la replica (mysql-bin.000012) e la posizione raggiunta sul file (107 le avevamo ottenuto sempre dal master con lo "SHOW MASTER STATUS"

Facciamo partire la replica

mysql> START SLAVE;

Verifichiamo lo stato della replica, cioè che tutto stia già funzionando

mysql> SHOW SLAVE STATUS;

Appariranno varie righe un po confuse... ci interessa la voce "Slave_IO_State" il cui valore deve essere "Waiting for master to send event". Se è presente tutto dovrebbe essere andato a buon fine (ma normalmente i primi tentavi falliscono miseramente ... ).

Il database del master è in replicazione in tempo reale sullo slave. Usciamo dal client Mysql.
That's all!

Tips and Trick

Il file my.cf ha molte opzioni da settare, legate alle esigenze del nostro database. Si rimanda per questo approfondimento ad altri articoli.
Però un consiglio posso già darvelo. In siti, sopratutto con elevato traffico, esiste il rischio di un eccessivo disallinemanento tra master e slave: in pratica la replica non riesce a star dietro alla velocità di scrittura del master.
Per rendere più veloce un processo di replica, nel file my.cf del master, nel blocco [mysqld], dopo "server-id=1", aggiungete questa righe

[mysqld]
........
server-id = 1
skip-name-resolve

Nel processo di replica, MySQL master, per sua impostazione, esegue una ricerca DNS inversa (Reverse DNS lookup): partendo dall'indirizzo ip viene cercato il nome host dello slave. Questo può generare lentezza nella connessione. Per evitarla bisogna aggiungere, al file /etc/hosts del server dove mysql è in esecuzione, il nome host dello slave, oppure fare in modo che mysql non effettui più la risoluzione usando l'istruzione "skip-name-resolve".

Approfondimenti

Elenco opzioni MySql