Mysql master replica how-to

Chi segue già da un pò i miei how-to sa come la penso, sono abbastanza fissato con la sicurezza dei sistemi e del dato, sicurezza che viene garantita con un certo livello di ridondanza dell’architettura.

Oggi vedremo come creare un semplice cluster di databases MySQL, ridondato tramite la funzione di master-replica dello stesso software.

Una premessa è obbligatoria: per fare un vero cluster di db mysql ridondato ci vogliono minimo 4 macchine, o quantominimo 3, anche se non è la via più canonica di fare un cluster con un numero dispari di macchine, questo perchè cosi si può garantire in ogni momento che tutte le macchine abbiano la macchina master di riferimento (capirete tra poco cosa sto dicendo), anche in caso di guasto di uno dei nodi del cluster, questa guida si rifà ad un cluster finto di sole due macchine per pura semplicità, resta sottinteso che per qualsiasi dubbio ci sono i commenti o il forum.

Le macchine:

Come già detto utilizzeremo due macchine per il cluster, è preferibile avere macchine con due schede di rete o con minimo una dual-port, in quanto vi consiglio di collegare le due macchine con un cavo cross via [[back-to-back]], per svariati motivi, velocità, sicurezza, stabilità; perchè in quella interfaccia verranno allineati i dati del database.

Riporto i dati delle macchine cosi come li ho usati su due macchine virtuali in ufficio, voi ovviamente utilizzate i dati che avete dalle vostre macchine.

Server1:

IP: 192.168.0.193 
utente_replica: slave1 
password_replica: slave1
Server2:
 
IP:192.168.0.44 
utente_replica: slave2 
password_replica:slave2
Preparazione macchine e software:
Do per scontato che abbiate già installato mysql-server e mysql-client sulle vostre macchine, procediamo quindi con una prima configurazione di di mysql, eliminando il listening solo su localhost, e mettendo mysql in ascolto su tutte le interfaccie:
Notare bene: tutte le operazioni verranno descritte una volta, ma sono da replicarsi anche sulla seconda macchina con i dovuti accorgimenti.
 
[...]
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
#bind-address = 127.0.0.1
[...]
commentata questa opzione riavviamo mysql, e dopo logghiamoci via mysql-client e diamo i permessi di replica all'utente della macchina slave:
Server1:
 
mysql -u root -p 
 grant replication slave on *.* to 'slave2'@'%' identified by 'slave2'; 
flush privileges; 
quit
Ora come ora abbiamo il db solo su Server1, per garantire la replica dobbiamo creare un database vuoto sul Server2, in questo esempio effettueremo la replica di un database chiamato portalinux.
Server2:
 
mysql -u root -p 
create database portalinux; 
quit
Set-up della replica:
Adesso la base è pronta, dobbiamo solo configurare i due mysql per attivare i processi di master/slave su entrambe le macchine, la prima parte comporta la modifica del file di configurazione /etc/mysql/my.cnf inserendo alcune opzioni, tra quelle che andremo ad inserire dobbiamo sapere che due fra tutte sono le più importanti:
auto_increment_increment: controlla il fattore di incremento, tra due incrementi successivi (è un bel gioco di parole)
auto_increment_offset: determina lo starting point del valore della colonna auto_increment, cioè il valore iniziale.
ponendo di avere un cluster costituito da N nodi, la prima delle due opzioni avra un valore = N, nel nostro esempio quindi sarà uguale a 2.
La seconda opzione invece avrà un valore diverso da N, e diverso in ogni macchina, per comodità io ho utilizzato un valore in base all'ordine logico che ho voluto dare alle macchine; quindi sul Server1 avrà valore = 1, e naturalmente sul server2 avrà valore = 2.
Questi parametri vanno inseriti insieme ad altri che vediamo in seguito, dobbiamo fare attenzione che quello che andiamo ad inserire non sia già inserito commentato od addirittura già attivo, quindi occhio...
Server1:
 
##############################
##mysql replica
###############################
server-id = 1
replicate-same-server-id = 0
auto-increment-increment = 2
auto-increment-offset = 1
master-host = 192.168.0.44
master-user = slave1
master-password = slave1
master-connect-retry = 60
replicate-do-db = portalinux
log_bin = /var/log/mysql/mysql-bin.log
binlog-do-db = portalinux
relay-log = /var/lib/mysql/slave-relay.log
relay-log-index = /var/lib/mysql/slave-relay-log.index
expire_logs_days = 10
max_binlog_size = 500M
##############################
Server2:
 
##############################
##mysql replica
###############################
server-id = 2
replicate-same-server-id = 0
auto-increment-increment = 2
auto-increment-offset = 2
master-host = 192.168.0.193
master-user = slave2
master-password = slave2
master-connect-retry = 60
replicate-do-db = portalinux
log_bin = /var/log/mysql/mysql-bin.log
binlog-do-db = portalinux
relay-log = /var/lib/mysql/slave-relay.log
relay-log-index = /var/lib/mysql/slave-relay-log.index
expire_logs_days = 10
max_binlog_size = 500M
##############################
Import del db:
Adesso è il turno della seconda parte della configurazione, dobbiamo loggarci via CLI ai due mysql e fare alcune operazioni, questa volta scriverò le operazioni da fare su tutti e due i server, in quanto in questo punto del tutorial le cose da fare sono diverse:
Server1:
Dobbiamo innanzitutto bloccare la lettura alle tabelle del database, e poi esportarlo per poi effettuare a manina il primo allineamento tra le due macchine:
mysql -u root -p 
use portalinux; 
flush tables with read lock; 
show master status;
Con l'ultimo comando vediamo lo stato del processo master sulla macchina 1, l'output sarà di questo genere:
 
mysql> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000065 | 98 | portalinux | | 
+------------------+----------+--------------+------------------+
1 row in set (0.01 sec)
ok...segnamoci da parte questi dati, e non chiudiamo la shell di mysql, altrimenti il lock sulle tabelle verrà rimosso e dovremo ripetere l'operazione; con la seconda shell effettuiamo l'export del db per poi copiarlo via scp sul Server2:
mysqldump -u root -p portalinux > portalinux.sql 
scp portalinux.sql root@192.168.0.44:~/
finito l'scp possiamo chiudere la seconda shell sulla macchina e rimuovere il lock sulle tabelle:
mysql> unlock tables;
Server2
Sulla seconda macchina adesso dobbiamo importare il dump del db appena copiato, prima però dobbiamo stoppare il processo slave di mysql:
 
mysqladmin -u root -p stop-slave
mysql -u root -p portalinux < portalinux.sql
mysql -u root -p
use portalinux;
flush tables with read lock;
show master status;
 
mysql> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000060 | 28858 | portalinux | | 
+------------------+----------+--------------+------------------+
1 row in set (0.01 sec)
anche qui, dobbiamo segnare e tenere da parte per dopo queste informazioni, infine possiamo sbloccare le tabelle:
mysql> unlock tables;
Settaggio processi slave sulle macchine:
Bene, adesso siamo sicuri che tutti e due i server sanno di essere il nodo master del cluster, per ultimare la procedura dobbiamo anche fargli sapere che, contemporaneamente, devono anche essere slave rispetto all'altra macchina:
Server2:
 
mysql> CHANGE MASTER TO MASTER_HOST='192.168.0.44',MASTER_USER='slave2', /
 MASTER_PASSWORD='slave2',MASTER_LOG_FILE='mysql-bin.000065',MASTER_LOG_POS=98;
Query OK, 0 rows affected (0.07 sec)
ecco a cosa ci servivano quei parametri che ci siamo segnati e tenuti da parte, in questo comando dobbiamo inserire ciò che Server1 ci ha restituito, e viceversa faremo appunto su Server1.
E' il momento della verità, dobbiamo vedere se tutto è a posto, possiamo innanzitutto far partire il processo slave e poi controllare se tutto va come deve andare:
 
start slave;
show slave status;
 
mysql> show slave status \G
*************************** 1. row ***************************
 Slave_IO_State: Waiting for master to send event
 Master_Host: 192.168.0.193
 Master_User: slave2
 Master_Port: 3306
 Connect_Retry: 60
 Master_Log_File: mysql-bin.000065
 Read_Master_Log_Pos: 209
 Relay_Log_File: slave-relay.000003
 Relay_Log_Pos: 235
 Relay_Master_Log_File: mysql-bin.000065
 Slave_IO_Running: Yes
 Slave_SQL_Running: Yes
 Replicate_Do_DB: portalinux
 Replicate_Ignore_DB: 
 Replicate_Do_Table: 
 Replicate_Ignore_Table: 
 Replicate_Wild_Do_Table: 
Replicate_Wild_Ignore_Table: 
 Last_Errno: 0
 Last_Error: 
 Skip_Counter: 0
 Exec_Master_Log_Pos: 209
 Relay_Log_Space: 235
 Until_Condition: None
 Until_Log_File: 
 Until_Log_Pos: 0
 Master_SSL_Allowed: No
 Master_SSL_CA_File: 
 Master_SSL_CA_Path: 
 Master_SSL_Cert: 
 Master_SSL_Cipher: 
 Master_SSL_Key: 
 Seconds_Behind_Master: 0
1 row in set (0.00 sec)
quello che ci interessa l'ho evidenziato in grassetto, Slave I/O Running e Slave SQL Running devono avere entrambi come valore yes, altrimenti qualcosa è andato storto. In tal caso diamo un occhiata al syslog, che ci è molto amico e ci dirà chiaramente cosa sta succedendo.
Una volta effettuata questa stessa operazione su Server1 abbiamo finito, e possiamo testare il tutto semplicemente eliminando/creando/rinominando/aggiornando una tabella del db, noteremo come all'istante la stessa operazione verrà effettuata anche sull'altra macchina.
Da notare che cosi come l'abbiamo impostato noi il processo e bivalente, cioè possiamo decidere di aggiornare indiscriminatamente su Server1 o su Server2, in quanto essendo entrambe sia master che slave rispetto l'altra, i cambiamenti verranno registrati in entrambi i sensi.
Buona replica