Replica Master-Slave di MYSQL

Mysql supporta agevolmente la replicazione one-way. Questo tipo di replicazione è mono direzionale (asincrona), ovvero consente di replicare le operazioni effettuate sui database di un server master automaticamente su uno più server slave. E’ possibile definire un server mysql di appoggio come slave e tenerlo sincronizzato in questo modo con il master.

Questo aumenta le prestazioni, in quanto i due sistemi possono lavorare in sincrono e aumenta la sicurezza, poichè in caso di blocco del master lo si può sostituire con lo slave, con una procedura che resta comunque manuale. Ovviamente la scrittura dovrà sempre essere effettuata sul master mentre la lettura potrà essere dirottata anche sullo slave aumentando di molto la velocità di interrogazione e sgravando il carico sul master server.

Il procedimento della replicazione funziona così :
1) Il master registra tutte le query di scrittura e di modifica in un file di log binario.
2) I server slaves leggono il log binario dal master ed eseguono localmente le query aggiornando i database localmente
3) Per un singolo master possiamo avere più slaves.
4) Mentre per un singolo slave possiamo avere solo un Master
5) Il master e gli slaves possono filtrare le query per:
Database
Tabella

Ma vediamo in pratica come effettuare questa operazione.
Consideriamo di avere due server Mysql in rete locale che chiameremo:
mastersql (192.168.0.1)
slavesql (192.168.0.2)

E’ necessario avere i due server mysql installati e verificare la possibilità di accedere da uno (slavesql) all’altro (mastersql) attraverso la rete (ping o telnet sulla 3306) o all’incirca in questo modo, dallo slavesql: mysqlshow –h mastersql –ppassword
Adesso possiamo configurare i sistemi

Configurazione del master

Per fare questo bastano pochi comandi SQL e alcune modifiche al file di configurazione my.ini. E’ bene copiarsi il file My.ini originale (in caso di annullamento di tutto).

Bisogna modificare alcuni parametri di configurazione importanti del server mysql. Questi parametri vanno messi nella sezione [mysqld] e non nella sezione [mysql]

# binary logging
log-bin
è obbligatorio per abilitare la replicazione, perché è proprio da questo file che lo slave
leggerà le modifiche per aggiornare le sue basi dati, eseguendo in sequenza gli stessi comandi.

server-id = 1
Questo valore va impostato ad 1 per il master

In pratica il file My.Ini deve diventare una cosa del genere…

[client]
port=3306
[mysql]
default-character-set=latin1
[mysqld]
# The TCP/IP Port the MySQL Server will listen on
port=3306
server-id=1
log-bin

Ora bisogna operare con SQL dalla consolle di MYSQL
E’ necessario impostare i privilegi di accesso per lo slave server:

mysql -u root -p
mysql> GRANT FILE ON *.* TO replica@slavesql IDENTIFIED BY ‘secret’;
consente l’accesso ai file per il server slavesql con user replica e password secret Attenzione : la parte “@slavesql” deve essere sostituita con il nome del Pc che subirà la replica. Se il pc si chiama “ginopallo”…allora sarà :

mysql> GRANT FILE ON *.* TO replica@ginopallo IDENTIFIED BY ‘secret’;

Una regola più permissiva può consentire l’accesso in replica a più sistemi e sarà del tipo:

GRANT FILE ON *.* TO replica@”%” IDENTIFIED BY ‘secret’;

mysql> FLUSH TABLES WITH READ LOCK;
esegue il flush delle tabelle e le blocca in scrittura per impedire modifiche.

mysql> GRANT REPLICATION SLAVE ON *.* TO replica@ ginopallo IDENTIFIED BY ‘secret’;

questa è la parte che fornisce privilege di replica. Se si omette questa riga, nel log dello SLAVE si vedranno errori di questo tipo:
“you need the REPLICATION SLAVE privilege for this operation”
“[ERROR] While trying to obtain the list of slaves from the master”

Configurazione dello slave

Anche in questo caso è necessario agire sul file my.ini che deve diventare una cosa del genere:

[client]
port=3306
[mysql]
default-character-set=latin1
[mysqld]
server-id=2
master-host=192.168.1.103
master-user=replica
master-password=secret

i dati più importanti sono tutti nella sezione Mysqld
Facciamo ripartire il mysql e teniamo d’occhio il file di log host.err:

040116 11:35:31 Slave I/O thread: connected to master ‘replica@192.168.0.1:3306’,
replication started in log ‘FIRST’ at position 4

questa riga vuol dire che la replicazione è iniziata.

Una nota importante :
Ogni volta che si modifica my.ini occorre eliminare il file master.info (nella cartella data), che comunque contiene i parametri relativi alla connessione (host,user e password).

Bisogna ricordarselo in quanto questo file ha la precedenza sulle eventuali modifiche che possono essere state fatte al file my.ini. Quindi in caso di modifica di user o password per esempio è bene ricordarsi di cancellarlo e di far ripartire il server mysql.

Invece nel file host-relay-bin.001 vengono registrate tutte le modifiche effettuate dal master e riportate sullo slave.

A questo punto riabilitiamo sul master le query in scrittura:
mysql mysql –u root -p
mysql > UNLOCK TABLES;

Test del funzionamento
Basta fare un modifica sul DB master, ad esempio aggiungere un record e ricercarlo nella copia del DB presente sullo slave per trovarlo, oppure basta creare un database sul server master e verificarne la sua presenza anche sullo slave. I tempi di latenza sono ovviamente legati al carico di lavoro e al peso dell’operazione effettuata, in alcuni casi la sincronizzazione può essere istantanea in altri richiedere un certo tempo.