MySql repliche – Master & Slave

Sebbene questo articolo esula molto dal corso che mi ero proposto di redigere di volta in volta, in quanto tratta di argomenti di configurazione molto avanzata, reputando tali nozioni utilissime dal punto di vista sistemistico, ho deciso di inserire tale post comunque, costruendo una categoria ad hoc, relativa così ad argomenti più avanzati….

Insomma una cosa dedicata ai più “smanettoni” 🙂 …

La procedura che segue è stata ovviamente tratta dal sito ufficiale:
http://dev.mysql.com/doc/refman/5.1/en/replication.html

Innanzitutto, per chi non lo sapesse già, il meccanismo di replica qui descritto è detto anche asincrono, poichè le operazioni di scrittura vengono eseguite da un database primario detto Master e solo successivamente (se pur dopo un tempo brevissimo in alcuni casi) da un server secondario detto Slave. Per una replica sincrona è necessario utilizzare invece il MySQL Cluster, riguardo al quale spero prima o poi di scrivere un articolo…

Gli scopi di questo meccanismo sono:

  1. disponibilità del dato – in caso di problemi sul sever che ospita il Master possiamo rapidamente modificare impostazioni di slave in modo tale da modificare l’indirizzo IP della macchina e configurarlo come fosse il nuovo Master, in tal modo si eroga un servizio più continuo per le postazioni clients.
  2. bilanciamento del carico – avendo cura di effettuare le operazioni di scrittura  esclusivamente sul Master, possiamo utilizzare anche lo Slave in lettura, dividendo così il carico di lavoro sulle due macchine (particolarmente utile in presenza di quantitativi di dati piuttosto rilevanti).
  3. backup anche frequenti sia con dump che da filesystem – senza caricare eccessivamente o fermare il servizio sul Master, perchè viene sfruttato lo Slave.

Convenzionalmente verranno indicate le operazioni da eseguire sul master con M e sullo slave con S.
Per preparare i server sono necessarie le seguenti operazioni:

1.    M: aggiungere un utente con privilegi di replica, lanciando la query
GRANT REPLICATION SLAVE ON *.* TO ‘repl_user’@’%’ IDENTIFIED BY ‘repl_password’;
Per aumentare la sicurezza modificare il campo host (%) indicando un IP o un hostname specifico.

2.    M: attivare i log binari in my.cnf (ed eventualmente escludere o includere i db desiderati) e assicurarsi di indicare che il server sarà il master; le righe interessate sono le seguenti:
[mysqld]
server-id = 1
log-bin = /var/log/mysql/mysql-bin.log
max_binlog_size = 104857600
binlog-do-db = mydatabasename
binlog-ignore-db = test

Nel caso in cui non vengano specificati i database da replicare e quelli da ignorare; l’impostazione predefinita replicherà tutti i database.

3.    M: bloccare le tabelle da scrittura per impedirne la modifica con la query:
FLUSH TABLES WITH READ LOCK;

NOTA:
Non disconnettere il client da cui si esegue questo comando, altrimenti il blocco da scrittura viene disabilitato. Questo passo è molto importante e serve per allineare i database prima di iniziare la replica vera e propria.

4.    M: fare una copia del database sorgente via file-system (es: /var/lib/mysql) o con mysqldump io ho optato per mysqldump eventualmente anche tramite interfaccia GUI, in quanto risulta essere più flessibile nel caso in cui le versioni dei due server M ed S siano diverse tra di loro.

5.    M: eseguire la query che segue ed annotare i risultati
 SHOW MASTER STATUS;
mysql> SHOW MASTER STATUS\G
*************************** 1. row ***************************
File: mysql-bin.000009
Position: 346
Binlog_Do_DB:
Binlog_Ignore_DB:
1 row in set (0.02 sec)

Questo ci dice esattamente in che punto del log binario del master, con la scrittura ancora bloccata in precedenza, abbiamo eseguito una copia dei database: filename e position; saranno il punto di partenza che imposteremo sullo slave nei prossimi passi.

6.    M: a questo punto si può sbloccare il master con
UNLOCK TABLES;
che può continuare tranquillamente ad accettare query di scrittura.

7.    S: importare il database (o meglio i database) salvati al punto 4 con una sostituzione di cartelle da filesystem o con un esecuzione del file di dump ottenuto con mysqldump a seconda del metodo utilizzato (nel mio specifico caso optato per il secondo metodo poichè a mio avviso più flessibile).

8.    S: assicurarsi di avere nel my.cnf dello slave la riga seguente:
server-id=2
Qui l’attivazione dei log binari non serve, potrebbe essere utile invece nel caso di server replicati a catena o di replica bidirezionale.

9.    S: eseguire le seguenti query come utente root del mysql prendendo i dati dai punti 1 e 5:
CHANGE MASTER TO
-> MASTER_HOST=’hostname_or_IP_ADDRESS’,
-> MASTER_USER=’repl_user’,
-> MASTER_PASSWORD=’repl_password’,
-> MASTER_LOG_FILE=’log_file_name’,
-> MASTER_LOG_POS=log_position;

START SLAVE;

Ovviamente sostituendo ai parametri di esempio quelli del proprio sistema; con il primo comando indichiamo allo slave quale è il master e da quale posizione partire oltre che l’utenza da utilizzare, mentre con il secondo attiviamo la replica.

VERIFICHE

Per verificare il corretto funzionamento è sufficiente lanciare la seguente query sullo slave:
mysql> SHOW PROCESSLIST\G
*************************** 1. row ***************************
Id: 1
User: system user
Host:
db: NULL
Command: Connect
Time: 48157
State: Waiting for master to send event
Info: NULL
*************************** 2. row ***************************
Id: 2
User: system user
Host:
db: NULL
Command: Connect
Time: 4578
State: Has read all relay log; waiting for the slave I/O thread to update it
Info: NULL
*************************** 3. row ***************************
Id: 4
User: root
Host: tower64.BOZUNET:1213
db: NULL
Command: Query
Time: 0
State: NULL
Info: SHOW PROCESSLIST
3 rows in set (0.00 sec)

Sullo slave e tra le varie righe si dovrebbe trovare uno status “Waiting for master to send event“, se ci fossero problemi di connessione il messaggio sarebbe invece “Connecting to master“.
Inoltre lanciando il seguente comando:
mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: tower64
Master_User: rep
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000009
Read_Master_Log_Pos: 346
Relay_Log_File: tower-relay-bin.000010
Relay_Log_Pos: 243
Relay_Master_Log_File: mysql-bin.000009
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
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: 346
Relay_Log_Space: 783
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
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 2003
Last_IO_Error: error connecting to master ‘rep@tower64:3306’ – retry-time: 60  retries: 86400
Last_SQL_Errno: 0
Last_SQL_Error:
1 row in set (0.00 sec)

Si ottiene una serie di dati tra cui “Slave_IO_State” che deve essere impostato a “Waiting for master to send event” e “Seconds_Behind_Master” che deve essere diverso da NULL“. Questo valore rappresenta la differenza tra il timestamp di esecuzione di una query sul master e quello di esecuzione della stessa sullo slave per cui normalmente è 0 quando cioè i database sono perfettamente allineati oppure un intero superiore (per esempio potrei trovare un valore >0 subito dopo lo start dello slave).

TROUBLESHOOTING

Il meccanismo si può inceppare solo nel caso in cui si perda la connessione con il Master (per problemi di utenze modificate o banali problemi di rete) oppure nel caso in cui i database non siano più uguali; mi spiego meglio: nel caso in cui una operazione eseguita sul Master provochi un effetto diverso sullo slave, dove con effetto diverso intendo anche il caso particolare in cui sul Master la query generi un messaggio di errore mentre sullo Slave vada a buon fine.
In questo caso lo slave si interrompe automaticamente e con uno
SHOW SLAVE STATUS\G

Si trovano tra le varie informazioni anche l’ultimo errore e la query che l’ha generato.
A questo punto se non si vuole ripartire da capo con l’impostazione della replica (snapshot ecc. ecc.) è possibile alternativamente:

  • rendere lo slave identico al master “a mano” (per quanto concerne la query che ha generato errore) e tentare uno
    START SLAVE;
  • ignorare la query e imporre allo slave di passare al comando successivo nel log binario con
    SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
    START SLAVE;

Si noti che una query contenente AUTO_INCREMENT o LAST_INSERT_ID() occupano due righe di log per cui in questo caso si dovrà impostare SQL_SLAVE_SKIP_COUNTER = 2

DORMIRE SOGNI TRANQUILLI

Il gioco è fatto !! 🙂
Facile non è vero ??
Ora siete i felici possessori di un sistema a prova quasi di bomba anche in considerazione del fatto che i due server M ed S possono anche risiedere in luoghi fisici molto distanti tra di loro…
Quindi se disgraziatamente una delle due sale server prendesse fuoco, l’altra conterrà sempre i vostri dati …
Quindi (vista l’ora a cui ho scritto l’articolo) auguro la buona notte e sogni tranquilli anche a Voi 🙂

Prossimo post

Posted in Documentazione Tecnica, MySQL Avanzato and tagged , .