Quando la Replication smette di funzionare: analisi, ripristino e resync della MySQL Replication

Abbiamo già avuto modo di parlare di Replication tra Database MySQL in un articolo precedente, occasione in cui l'abbiamo descritta come un ottimo modo di aumentare la sicurezza e l'affidabilità di un sistema di archiviazione dati senza spendere una fortuna. In questo articolo vedremo cosa fare quando la Replication smette di funzionare: come accorgersene, cosa fare per ripristinarla e, soprattutto, come assicurarsi che i dati siano nuovamente sincronizzati.

Identificare le cause

La prima cosa da fare è assicurarsi che la Replication sia effettivamente rotta. Anche se nella maggior parte dei casi siamo in grado di poterlo affermare in modo empirico osservando i dati, è necessario verificarlo in modo oggettivo. Per far questo collegatevi allo Slave, digitate il comando SHOW SLAVE STATUS - oppure  SHOW SLAVE STATUS\G se preferite ricevere i risultati in human-readable format - e andate a leggere il contenuto delle colonne  Slave_IO_Running e  Slave_SQL_Running : se uno dei due valori è NO significa che la Replication è effettivamente rotta, in caso contrario il problema è da ricondurre ad altre cause.

Una volta confermato che la Replication si è interrotta, la seconda cosa da fare è cercare di capire cosa ha provocato la rottura. A prescindere dalla nostra configurazione queste sono, in ordine di probabilità, le cause principali che provocano l'interruzione di una Replication:

• Presenza di una (o più) query che non ha dato errori sul Master - finendo quindi nel binary log -  ma la cui esecuzione è fallita sullo Slave per qualche motivo.
• Shutdown repentino del Master e/o dello Slave, con conseguente impossibilità di connettersi per diverso tempo fino allo scadere del Connect Retry Timeout previsto.
• Problemi di stabilità dello Slave o della macchina che lo ospita: tabelle danneggiate, indici rotti, Hard-Disk pieno o difettoso, altre problematiche di natura hardware, etc.

Vediamole in dettaglio e, per ciascuna di esse, identifichiamo le opportune strategie di prevenzione/contenimento del problema.

Presenza di query con errori

Effettuare questo controllo è molto semplice: è sufficiente connettersi allo Slave, digitare il comando SHOW SLAVE STATUS e leggere il contenuto della colonna Last_Error. Se la colonna è vuota il vostro problema non è questo, in caso contrario continuate a leggere.

La presenza di una query contenente errori è senza ombra di dubbio la causa più comune di rottura della Replication: possiamo dire senza timore di smentita che almeno due persone su tre, tra tutte quelle che leggeranno questo articolo, si accorgeranno di avere questo problema.

A loro beneficio, cerchiamo di capire cosa è successo e perché. Come ha fatto una query che provoca errori sullo Slave ad essere correttamente processata dal Master, al punto di finire nel binary log? Le possibilità sono diverse, sebbene non risultino sempre ovvie osservando la query in questione cerchiamo di elencare le principali cause e, visto che ci siamo, prevedere alcuni possibili workaround da applicare al nostro sistema prima di effettuare il ripristino della Replication stessa.

Cause

• Se la query è di tipo INSERT , è probabile che l'errore sia dovuto al fatto che la PRIMARY KEY  specificata è già presente nella tabella dello Slave. In moltissimi casi questo è dovuto al fatto che sia il Master che lo Slave ospitano un sito web, applicazione o servizio che prevede l'inserimento automatico di dati all'interno del DB sulla base di una pianificazione temporale - ogni notte, ogni settimana, etc. - o in conseguenza di operazioni eseguite dall'utente - durante il login, a seguito del logout, all'apertura di una determinata pagina/schermata, etc.: questo inserimento automatico avviene sia sul Master che sullo Slave poiché entrambi gli ambienti hanno una istanza di questa applicazione configurata per svolgere le medesime operazioni. Per comprendere meglio la cosa, proviamo a fare un esempio: immaginiamo una Web Application che, durante la notte, inserisce una riga nella tabella logs contenente alcune statistiche relative al giorno appena trascorso. La tabella prevede una chiave primaria basata sulla data del giorno in questione, il che significa che ogni record verrà creato con un identificativo univoco... ma tale identificativo sarà identico a quello generato da qualsiasi altra istanza della Web Application: di conseguenza, presupponendo due o più istanze collegate a DB diversi e posti in Replication tra di loro, queste query eseguite automaticamente entreranno in conflitto con quelle presenti nel binary log del Master, mettendo fuori gioco la nostra Replication oppure generando un'errore nell'esecuzione automatica a seconda di chi "arriva dopo".
• Se la query è di tipo UPDATE , è probabile che l'errore sia dovuto al fatto che lo Slave non contiene più - o non contiene ancora - un record che, invece, è stato effettivamente modificato sul Master. Anche in questo caso il motivo è quasi sempre da ricondurre alla presenza di qualche operazione automatica sul DB, ad esempio l'esecuzione periodica di uno script di manutenzione volto ad eliminare righe potenzialmente inutili o più vecchie di una determinata data. In questi casi è spesso sufficiente un piccolo disallineamento tra i due server per provocare una cancellazione da una parte, ma non dall'altra. Proviamo a immaginare un esempio anche in questo caso: pensiamo a un servizio di manutenzione che si occupa di cancellare periodicamente i record sul DB relativi all'upload di file multimediali nel caso in cui ad essi non corrisponde un file presente fisicamente sull'HDD del server. Lo Slave, magari poiché si trova su una macchina non attiva in produzione, non possiede quei file e quindi subisce - a differenza del Master - la cancellazione dei record relativi: il risultato è che, non appena uno di quei record viene modificato sul Master, la Replication viene messa KO.

Workaround

Premesso che la soluzione ottimale a questo tipo di problemi dipende dalle caratteristiche specifiche del DB e delle relative applicazioni che ne fanno uso, è consigliabile tenere conto dei seguenti accorgimenti generali:

Inserire uno switch

Prevedete, magari tramite uno switch nel file di configurazione del'applicazione ( web.config , app.config , et. al.), la possibilità di disattivare le esecuzioni automatiche di cui sopra. In tal modo potrete decidere in qualsiasi momento quali istanza si occuperanno di tali esecuzioni e quali no: avrete quindi la possibilità di intervenire tempestivamente in caso di problemi di questo tipo per impedire che la replication venga interrotta in continuazione, magari nell'attesa di implementare una soluzione definitiva.

Utilizzare la direttiva IGNORE

Se le query che vi danno problemi non contengono informazioni specifiche dell'istanza che le esegue e possono quindi essere "saltate" senza creare problemi potrebbe essere sufficiente dotarle della direttiva IGNORE , così da non bloccare la Replication in caso di inserimenti duplicati o di modifiche a record inesistenti. Ovviamente - specialmente nel caso delle query di tipo UPDATE  - si tratta di un workaround che rischia di creare un disallineamento tra Master e Slave e va quindi utilizzato con grande attenzione.

Escludere le query dal Binary Log

Se, contrariamente ai due punti di cui sopra, le query che vi danno problemi necessitano di essere eseguite localmente su ciascuna istanza in quanto contengono informazioni specifiche e caratteristiche potenzialmente variabili o dipendenti da ciascun singolo contesto, la soluzione più adeguata potrebbe essere escludere le query potenzialmente "bloccanti" dal binary log. E' possibile ottenere questo risultato facendo uso della variabile di sessione sql_log_bin , che può essere impostata a zero inserendo la direttiva  SET sql_log_bin = 0;  subito prima di una (o più) query. Nel caso in cui la vostra applicazione preveda il riciclo ovvero il riutilizzo delle sessioni, è consigliabile ripristinare il valore originario inserendo la direttiva  SET sql_log_bin = 1;  al termine della (o delle) query da escludere. Il nostro consiglio è di effettuare l'intera sequenza di applicazioni all'interno di una singola istruzione MySQL come nell'esempio seguente:

Ripristino

Dopo aver studiato le cause - ed eventualmente applicato i workaround del caso - vediamo come ripristinare la Replication. Nel caso in cui il problema sia dovuto alla presenza di un numero limitato di query appartenenti alla medesima tipologia è spesso possibile risolvere la cosa velocemente facendo in modo che lo Slave proceda nell'esecuzione del binary-log "saltando" le query bloccanti; nel caso in cui il problema investa un numero elevato di query appartenenti a diverse tipologie è consigliabile effettuare il riallineamento integrale dello Slave: si tratta di un procedimento lungo e potenzialmente faticoso, benché quasi interamente automatico, ma è l'unico modo che avete per ripristinare integralmente la coerenza tra i due DB.

Skip di una singola query

Questa è l'istruzione da eseguire sullo Slave per forzare il "salto" di una singola query presente nel binary log:

Questa istruzione consente allo Slave di oltrepassare la query che ha provocato il problema, riprendendo l'esecuzione del binary log da quella immediatamente successiva. Subito dopo averla eseguita è opportuno analizzare nuovamente lo stato della Replication con  SHOW SLAVE STATUS  per vedere se il problema è stato risolto oppure no. I valori da guardare in questo caso sono tre:

• Slave_IO_Running  e Slave_SQL_Running che, se entrambi a Yes, confermano che la Replication è effettivamente ripartita.
• Seconds_Behind_Master , che indica il numero di secondi di distanza tra la data di ultimo aggiornamento del binary log e quella della query processata in quel momento: in altre parole, il numero di secondi che separano lo Slave dal Master.

Nel caso in cui il problema sia stato risolto i primi due valori si manterranno su Yes e il terzo scenderà a velocità sostenuta: in caso contrario, non dovrete far altro che analizzare la query bloccante successiva, leggendo nuovamente il contenuto della colonna Last_Error.

IMPORTANTE: Esistono alcuni scenari, ad esempio se fate un utilizzo intenso di transactions, in cui l'utilizzo di SQL_SLAVE_SKIP_COUNTER potrebbe non essere indicato: per approfondire questo aspetto, consigliamo di leggere l'ottimo articolo dei ragazzi di Percona sull'argomento. Se non avete mai sentito parlare di Percona e lavorate spesso con MySQL vi consiglio di colmare al più presto questa grave mancanza leggendo la voce su Wikipedia e quindi la presentazione del Percona Toolkit sul sito ufficiale, che vi farà rimpiangere di non aver installato i vostri MySQL su Linux.

Riallineamento integrale dello Slave

Nel caso in cui lo skip di singole query dovesse rivelarsi troppo oneroso o non fosse sufficiente per risolvere il vostro problema dovete ricorrere a un riallineamento integrale: la procedura non è complessa ma bloccherà il vostro DB per alcuni secondi, quindi assicuratevi di svolgerla in un momento opportuno. Connettetevi al Master mediante MySQL command-line o con un programma di gestione come SQLyog (se non ne avete mai sentito parlare, leggete qui) ed eseguite i seguenti comandi:

Quindi, mantenendo la finestra di connessione attiva, procedete a effettuare un dump completo del database avendo cura di specificare i parametri necessari che serviranno allo Slave per ripristinare la replication:

Subito dopo aver avviato il comando  mysqldump  potete rilasciare il LOCK alle tabelle, così da consentire ai vostri servizi di poter tornare rapidamente online. Tornate sulla finestra di connessione precedente e inserite il seguente comando:

Completata l'operazione, copiate il file dump sul PC che ospita il DB slave e importatelo con il seguente comando:

Quindi riavviate lo Slave:

La replication partirà automaticamente, sincronizzando anche eventuali modifiche effettuate sul DB Master durante il processo di importazione.

Shutdown repentino del Master e/o dello Slave

Se la rottura della replication è dovuta a una disconnessione prolungata di una delle due macchine, la prima cosa da fare è tentare di riavviarla manualmente con un semplice:

E controllare subito dopo lo status della Replication con il consueto  SHOW SLAVE STATUS . Controlliamo nuovamente il contenuto delle colonne  Slave_IO_Running e  Slave_SQL_Running  e assicuriamoci che entrambe riportino uno Yes. Teniamole sotto controllo periodicamente, assicurandoci che restino su Yes man mano che i Seconds_Behind_Master  scendono a zero. In caso affermativo avremo risolto, altrimenti dovremo procedere con un Riallineamento integrale dello Slave secondo le modalità descritte sopra.

Problemi di stabilità dello Slave o della macchina che lo ospita

Se avete a che fare con tabelle danneggiate, indici malfunzionanti, o peggio ancora problemi legati al cedimento dell'hardware, è molto probabile che le condizioni dello Slave siano compromesse: in questi casi procedere con un semplice riavvio della replication potrebbe non essere la soluzione più sicura per preservare l'integrità dei nostri dati, in quanto esiste la concreta possibilità che alcune query non siano state eseguite correttamente nonostante l'indice di lettura del binary log sia stato correttamente aggiornato sulla macchina Slave: questo significa che potremmo avere un archivio non sincronizzato anche nel caso in cui la Replication riparta senza colpo ferire. Il nostro consiglio è quindi di non rischiare e procedere con un Riallineamento integrale dello Slave secondo le modalità descritte sopra a meno che non si abbia la possibilità di determinare in modo pressoché certo la presenza degli stassi dati su entrambi i DB, cosa fattibile soltanto per archivi di dimensioni piuttosto contenute.

Per il momento è tutto: felice sviluppo!

Print Friendly & PDF Download