Ripristino completo di una replica MySQL bloccata.

di Giulio di Bari :: 07 marzo 2018

Vi sarà certamente capitato, in un sistema di replicazione MySQL MASTER-SLAVE di vedere fermarsi la replica (lo slave) e quindi di avere l'esigenza di ripristinarla.

Ci siamo già occupati sia della creazione di un sistema master slave, sia di esportazione di un database utilizzando mysqldump.

Le cause del blocco di una replicazione possono così riassumersi:

presenza di una o più query che nel Master sono state correttamente eseguite (e quindi sono finite nel binary log da replicare), ma sullo Slave la loro esecuzione per qualche ragione è fallita.
spegnimento ("shutdown") improvviso del server Master e/o del server Slave, con conseguente impossibilità di connettersi tra loro per diverso tempo fino allo scadere del tempo di timeout previsto per la riconnessione ("Connect Retry Timeout").
problemi di stabilità sul server Slave: hard disk danneggiato o saturo, problemi vari di natura hardware, tabelle danneggiate, indici rotti, ...

Vediamo adesso un esempio pratico di come operare il ripristino in caso di SLAVE bloccato.

Questa è la nostra architettura:

server MySQL master
indirizzo ip: 192.168.1.1
user: root
password: antonio
database da replicare: miodb,fatture

Sul server master abbiamo inoltre creato un utente a cui abbiamo assegnato i permessi per effettuare la replica, con queste credenziali:
user: mioslave
password: slavepassword
In pratica, lo slave si connetterà al master usando queste credenziali
server MySQL slave
indirizzo ip: 192.168.1.2
user: root
password: cleopatra

In caso di SLAVE bloccato, cerchiamo subito di itentificarne la causa.

Entriamo sul server SLAVE, ed accediamo a MySQL da terminale, come utenti root

# mysql -u root -p

Entrati nella shell Mysql, verifichiamo lo stato di MySQL usando il comando "SHOW SLAVE STATUS" appare un listato con lo stato della replica

mysql> SHOW SLAVE STATUS

E possibile ottenere un output in un formato più leggibile usando

mysql> SHOW SLAVE STATUS\G

Capiamo che la replica è ferma se le voci Slave_IO_Running e/o Slave_SQL_Running hanno come valore NO, anzichè SI.

Buttate un occhio anche al valore di Seconds_Behind_Master, che indica il ritardo in secondi della replica, quanti secondi sono passati sono passato dalla scrittura del log file del MASTER e la presa in carico dello SLAVE. In caso di replica bloccata questo numero tenderà a lievitare. Se master e slave sono perfettamente allineati avrà valore vicino o uguale a 0, in presenza di un traffico elevato potreste vedere il numero innalzarsi.

Tra le righe viene inoltre evidenziato l'errore, ad esempio:

Last_Errno: 1146
Last_Error: Error 'Duplicate entry 'xyz' for key 1' on query. Default database: 'miodb'.

Un primo tentativo di ripristino può essere quello di dire a MySQL di saltare l'istruzione che determina l'errore ed operiamo come segue.

Sempre da shell MySQL, stoppiamo lo slave

mysql> STOP SLAVE;

Dopodichè, diciamo a MySQL di saltare la query che determina l'errore

mysql> SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;

Mettendo "1" diciamo a MySQL di saltare l'ultima query, quella che ha determinato l'errore.
Allo stesso modo potevamo dirgli di saltare le ultime 2 query, mettendo "2".

Riavviamo quindi lo slave

mysql> START SLAVE;

Ricontrolliamo lo stato dello slave

mysql> SHOW SLAVE STATUS

Se non sono presenti errori, e la replica è ripartita, vedrete il Seconds_Behind_Master calare ed andare verso lo 0. Ma non cancate vittoria. Monitorate lo slave per i prossimi minuti/ore, potrebbe ricapitare di bloccarsi. Se questo succede occorre risolvere il problema alla radice: reimportiamo tutti i dati dal master cioè procediamo con un riallineamento completo dello Slave.

Operazioni da eseguire sul MASTER

Per prima cosa operiamo sul master seguendo questa procedura

1: Stoppiamo la scrittura dei records sul master

Dobbiamo esportare il o l database da replicare, per poi trasferirli sullo slave. Per farlo dobbiamo bloccare il master da possibili scritture per tutto il periodo necessario alla esportazione.

Nella shell di mysql utilizziamo questo comando

mysql> FLUSH TABLE WITH READ LOCK;

Da questo momento il master sarà disponibile SOLO in lettura e non in scrittura... per cui agiamo velocemente se il sito è in produzione !

2: Indentifichiamo il file log del master

Da shell mysql verifichiamo lo stato del master

mysql> SHOW MASTER STATUS;

Verrà presentata una schermata simile a questa:

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

Prendiamo nota del nome del file (mysql-bin.000012) e della posizione (107) raggiunta dal master nella sua scrittura. Al riavvio della scrittura, il master ripartirà da qui.

2: Esportiamo i database da replicare sullo slave utilizzando mysqldump

Apriamo un altro terminale e utilizziamo il comando mysqldump, spiegato dettagliatamente in un altro articolo, per esportare i due database che stiamo replicando

# mysqldump -u root -pantonio --opt --databases miodb fatture > miobkp.sql

Abbiamo così ottenuto un file .sql che dovremmo poi uplodare sul server slave.

Se il file ha una dimensione notevole, potreste alleggerirne il peso potrestre utilizzando un compressore come ad esempio gzip

# gzip -9 miobkp.sql

ottenendo il file "miobkp.sql.gz" che, trasferito sul server slave, dovrete decompiremere e riportare al suo stato naturale così

4: Riavviamo la scrittura del master

Ultimati gli step precedenti non ci resta che riavviare la scrittura del master

mysql> UNLOCK TABLES;

Il lavoro sulla shell mysql è ultimato

mysql> QUIT;

Possiamo abbandonare il master e passare al server slave

Operazioni da eseguire sullo SLAVE

Adesso ci spostiamo sul server SLAVE ed eseguiamo questi step

1: Uplodiamo il file sql ottenuto

Dobbiamo trasferire su questo server il file ottenuto. Trovate voi il modo migliore, in FTP, da una chiavetta..

Se avevamo compresso il file con gzip, decomprimiamolo così

# gzip -d backup.sql.gz

2: Stoppiamo la replicazione

Nonostane la replica dovrebbe essere interrotta a causa dell'errore che ci ha portato a questa reinstallazione, lanciare lo STOP SLAVE non fa male!

Apriamo la shell mysql

# mysql -u root -p

e stoppiamo la replica

mysql> STOP SLAVE;

3: Importiamo i database esportati dal master

Sempre da shell mysql utiliziamo questa scrittura

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

Possiamo notare che abbiamo indicato

CHANGE MASTER TO MASTER_HOST: è l'ip del master
MASTER_USER: è il nome utente che sul server master che ha i permessi di replica
MASTER_PASSWORD: è la password di questo utente
MASTER_LOG_FILE: è il file di log usato dal master al momento della esportazione
MASTER_LOG_POS: è la posizione su tale file raggiunta dal master

4: Facciamo ripartire lo slave

Siamo giunti alla fine... che lo slave riparta!

mysql> START SLAVE;

Se tutto è andato in modo corretto, con lo SHOW SLAVE STATUS non verranno più evidenziati errori

Usiamo dalla shell

mysql> QUIT;

Chi lavora spesso su sistemi di replica master slave, sopratutto in presenza di elevato traffico, avrà modo di affrontare ahimè questa problematica, ma col tempo e con l'esperienza acquisita, configurando correttamente i parametri di MySQL, sarete in grado di creare repliche stabili e durevoli nel tempo.

That's all !

Ripristino completo di una replica MySQL bloccata.

Operazioni da eseguire sul MASTER

Operazioni da eseguire sullo SLAVE

Potrebbe interessarti