paolo@bimodesign.com | +34 608 61 64 10

DB Relational

        

MySQL e la replication

La replication identifica un meccanismo di trasferimento di dati tra due o piu' database, attraverso operazioni di lettura (dall'unico master) e di scrittura (su uno o piu' slave). E' un sistema asincrono perche' queste operazioni, master--slave, avvengono non in maniera simultanea, anche se i tempi sono brevissimi.
La replica sincrona e' identificabile invece con i cosidetti Cluster, di cui mi occupero' in un prossimo articolo.
L'obiettivo della replication e':
- disponibilita' del dato: in caso di problemi sul sever che ospita il Master possiamo rapidamente cambiare le connessioni sullo Slave
- backup anche frequenti sia con dump che da filesystem, senza caricare eccessivamente o fermare il servizio sul Master, perche' viene sfruttato lo Slave.
- bilanciamento del carico: avendo cura di effettuare le operazioni di scrittura esclusivamente sul Master, possiamo utilizzare anche lo Slave in lettura, dividendo cosi' il carico di lavoro sulle due macchine. (in realta', questo si realizza meglio usando i Cluster, dove, tra l'altro, e' possibile distribuire il carico, addirittura di singole select, su piu' instanze dello stesso db)

Ora entriamo nei dettagli tecnici su come configurare e attivare questa operazione.
Consiglio di fare comunque sempre riferimento alla documentazione ufficiale del sito di MySQL che nel mio caso e' relativa alla versione 5.5
http://dev.mysql.com/doc/refman/5.5/en/replication.html

Questi i passi che seguiremo

  • Configurazione del Master
  • Configurazione dello Slave
  • Creare l'utente sul Master
  • Ottenere le coordinate dei Binary Log (sul Master)
  • Creare un Data Snapshot usando mysqldump
  • Attivare lo Slave
  • Verifiche
  • Eventuali Problemi

Configurazione del Master
Per permettere la comunicazione tra master e slave, devono essere abilitato il binary loggin e definire un unique server ID, seguendo questi passaggi:
- shutdown MySQL server
- edit i file my.cnf o my.ini (nel caso di sistemi Windows) nel seguente modo:

[mysqld]
log-bin=mysql-bin
server-id=1
max_binlog_size = 104857600
binlog-do-db = mydatabasename
binlog-ignore-db = test

gli ultimi due parametri (non obbligatori), indicano l'esclusione o meno di uno o piu' database dalla replication.
- restar MySQL server
Note: occorre verificare che non sia abilitata l'opzione skip-networking sul master, altrimenti non sara' possibile metterlo in comunicazione con lo slave.

Configurazione dello Slave
Questi gli step - shutdown del server (slave)
- edit i file my.cnf o my.ini (nel caso di sistemi Windows) nel seguente modo:

[mysqld]
server-id=2

- restar MySQL server (Slave)
Note: Non e' necessario abilitare il binary logging sullo slave, per la replication, ma potrebbe comunque essere utile farlo, nel caso si vogliano utilizzare i binary log per un backup incrementale sullo slave, alleggerendo quindi il carico sul server. Inoltre, potrebbe essere utile nel caso in cui lo slave sia un master di un altro slave.

Creare l'utente sul Master
Per permettere allo slave di accedere al master, c'e' chiaramente bisogno di creare un utente e password su quest'ultimo. E' inoltre preferibile (ma non obbligatorio) creare un utente dedicato solo alla replication e poi dargli i relativi permessi (GRANT), nel seguente modo:

CREATE USER 'repl'@'%.mydomain.com' IDENTIFIED BY 'slavepass';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%.mydomain.com';

Note: Per aumentare la sicurezza modificare il campo host (%) indicando un IP o un hostname specifico.

Ottenere le coordinate dei Binary Log (sul Master)
Eseguire sul Master questa operazione per bloccare le tabelle da scrittura ed impedirne la modifica con la query. Questo passo e' molto importante e serve per allineare i database prima di iniziare la replica vera e propria

FLUSH TABLES WITH READ LOCK;

Note: non disconnettere il client da cui si esegue questo comando, altrimenti il blocco da scrittura viene disabilitato. Inoltre per verificare che l'operazione sia andata a buon fine, su un'altra sessione del master eseguire questo comando che dara' il risultato indicato appena sotto

SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000003 | 73       | test         | manual,mysql     |
+------------------+----------+--------------+------------------+


Creare un Data Snapshot usando mysqldump
fare una copia del database Master con mysqldump e poi copiarlo sul server. E' possibile eseguire questa operazione, sia con il classico comando

mysqldump --all-databases --lock-all-tables > dbdump.db

sia usando quest'altra opzione che non fa nient'altro che includere il "READ LOCK" indicato in precedenza.

mysqldump --all-databases --master-data > dbdump.db

Nel caso in cui lo spazio sul master non sia sufficiente, prevedere di eseguire uno zip del file dump durante l'export.
Quindi eseguire dallo stesso client su cui e' stato eseguito il "READ LOCK" il seguente comando

UNLOCK TABLES;

perche' oramai il master puo' continuare ad accettare operazioni in scrittura o lettura.

Attivare lo Slave
Questo passaggio dipende da come e' stata effettuata la copia dei dati del master.
a) Se abbiamo usato mysqldump, allora dobbiamo seguire questi step:
- startup dello slave con l'opzione

--skip-slave-start 

in maniera che non si attivi la replication. A questo link trovate le opzioni per la replication sullo slave
- Importare il file dump

shell> mysql < fulldb.dump


b) Se abbiamo creato uno snapshot, allora
- Estrarre i dati nella directory dei dati dello Slave, verificando i permessi dei file in modo tale che lo slave possa accedervi e modificarli

tar xvf dbdump.tar

- startup dello slave con l'opzione

--skip-slave-start 

in maniera che non si attivi la replication.
Sullo slave, eseguire quindi le seguenti query come utente root di mysql, utilizzando i dati dei punti precendenti, dove indichiamo allo slave quale è il master e da quale posizione partire oltre che l'utenza da utilizzare

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;


Attivare finalmente la replication:

START SLAVE


Verifiche
Per verificare il corretto funzionamento basta lanciare sullo Slave questa istruzione

SHOW PROCESSLIST;

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 questo comando

SHOW SLAVE STATUS\G;

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 e' 0 quando cioe' i database sono perfettamente allineati oppure un intero superiore (per esempio potrei trovare un valore >0 subito dopo lo start dello slave).

Eventuali Problemi
Il meccanismo si puo' 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 piu' uguali.
Questo puo' ad esempio succedere 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 questo comando

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.) e' possibile procedere in uno dei seguenti modi
a) rendere lo slave identico al master manualmente, ovvero eseguire la select del master che ha generato l'errore, e tentare uno

START SLAVE;

b) 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 se una query contiene AUTO_INCREMENT o LAST_INSERT_ID() occupa due righe di log per cui in questo caso si dovra' impostare

SQL_SLAVE_SKIP_COUNTER = 2


Tips and Tricks
In questa sezione riporto alcuni accorgimenti, che avro' cura di aggiornare, ogni volta che se ne presentino di nuovi. Aumentare le prestazioni dello Slave Se si vuole utilizzare lo Slave con lo scopo principale di avere un backup in tempo reale dei dati, potrebbe capitare di avere il Master molto piu' potente e veloce dello Slave e contemporaneamente di dover utilizzare il motore InnoDB.
Con le impostazioni standard lo Slave rimaneva sempre piu' indietro rispetto al Master (il ritardo aumentava di un secondo al secondo tanto per essere precisi).
Quello che ha permesso un recupero rapido e un funzionamento efficace e' stato il così detto fine tuning dei seguenti parametri del my.cnf nello Slave (una macchina con 2Gb di memoria):

innodb_buffer_pool_size = 1500M
innodb_flush_log_at_trx_commit = 0
innodb_flush_method=O_DSYNC

- il primo parametro ottimizza l'uso della memoria
- il secondo modifica la frequenza delle scritture su disco (aumentando il rischio di perdita di dati non ancora scritti, in caso di improvviso spegnimento della macchina o in generale di improvviso stop del servizio, ma visto che si tratta dello Slave, e' un rischio che si può correre)
- il terzo modifica il comando utilizzato per la scrittura dei dati su disco (di default e' fsync).