MySql database replication

La procedura che segue è stata ovviamente tratta dal sito ufficiale: http://dev.mysql.com/doc/refman/4.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:

  • disponibilità del dato – in caso di problemi sul sever che ospita il Master possiamo rapidamente switchare le connessioni sullo Slave
  • 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
  • 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

  3. M: bloccare le tabelle da scrittura per impedirne la modifica con la query

    FLUSH TABLES WITH READ LOCK;

    NB: 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 filesystem (es: /var/lib/mysql) o con mysqldump
  5. M: eseguire la query che segue ed annotare i risultati

    SHOW MASTER STATUS;

    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
  8. S: assicurarsi di avere nel my.cnf dello slave la riga

    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 query

SHOW PROCESSLIST;

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 uno

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 è 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 di 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() occupa due righe di log per cui in questo caso si dovrà impostare SQL_SLAVE_SKIP_COUNTER = 2

ESEMPIO: 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 (come in effetti mi è successo) di avere il Master molto più potente e veloce dello Slave e contemporaneamente di dover utilizzare il motore InnoDB.

Con le impostazioni standard lo Slave rimaneva sempre più 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 è 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

Per maggiori dettagli su questi parametri si veda la pagina http://dev.mysql.com/doc/refman/4.1/en/innodb-tuning.html, comunque in breve:

  • 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, è un rischio che si può correre)
  • il terzo modifica il comando utilizzato per la scrittura dei dati su disco (di default è fsync).

20 thoughts on “MySql database replication

  1. Ciao Diego, grazie per la guida 🙂
    Ho una domanda da farti: io ho seguito le tue istruzioni e lo slave funziona ma se vado ad inserire un nuovo database nel master anche aspettando svariati minuti questo database non lo vedo nello slave e il comando SHOW SLAVE STATUS\G;
    da il seguente risultato:
    Slave_IO_State: Waiting for master to send event
    Master_Host: 10.0.0.7
    Master_User: repl
    Master_Port: 3306
    Connect_Retry: 10
    Master_Log_File: mysql-bin.000002
    Read_Master_Log_Pos: 98
    Relay_Log_File: mysqld-relay-bin.000004
    Relay_Log_Pos: 235
    Relay_Master_Log_File: mysql-bin.000002
    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: 98
    Relay_Log_Space: 235
    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

    quindi mi pare tutto ok.
    Cosa può essere?

    1. Ciao devi verificare sul master le variabili replicate_do_db e replicate_ignore_db, per verificare che non ci siano esclusioni
      (SHOW MASTER STATUS).
      Se è valorizzata la replicate_do_db, automaticamente esclude tutti i DB non citati.

      Stessa verifica va fatta sui log binari: se il nuovo database non è incluso nei log binari del master, non ci sarà nessun passaggio di dati.

      Inoltre dovresti verificare sulla documentazione MySql, perchè se non sbaglio i comandi di creazione database non vengono considerati, per cui il nuovo DB va creato a mano sullo slave.

      1. Ciao,

        E’ possibile settare in MySQL i tempi per la replica?, mi spiego meglio, le attività di replica avvengono solo ad evento per ogni cambiamento sul master? o possono essere effettuati dei settaggi per cui la replica avviene ad esempio ogni ora?

        Grazie Mille

        1. Vediamo di semplificare il più possibile: ad ogni modifica sul master, lo slave esegue la stessa query su se stesso. Se non ci sono scritture, lo slave “sta in ascolto” non deve fare granchè, per cui non ci sono motivi per attivare la sincronizzazione ogni ora.

          In ogni caso è possibile attivare e disattivare la replica ad intervalli regolari, la difficoltà eventuale può essere sapere o calcolare il tempo necessario per l’allineamento dei dati.

          Lo puoi fare lanciando periodicamente uno script sullo slave che:

          1) esegua uno “START SLAVE”
          2) esegua uno “SHOW SLAVE STATUS” ogni minuto per verificare il valore di “SECONDS BEHIND MASTER”
          3) se tale valore è 0, esegua uno “STOP SLAVE”

          PS: se intendi lanciare tale script ogni ora, è fatica sprecata a mio avviso, se proprio vuoi fare così lancialo solo una o due volte al giorno…

          1. Salve,
            ho letto il suo post molto chiaro in merito alla replica dei db MySql.
            Non ho capito se può essere utilizzata anche per centralizzare n database remoti in un unico locale. L’idea sarebbe che i database remoti (chiaramente con tabelle diverse) diventino “master” e l’unico server locale diventi “slave” di ognuno di essi. Di fatto avrei un “backup” locale di tutti i db in giro.
            E’ secondo lei possibile? Suggerisce soluzioni più furbe?
            Grazie

          2. Salve! Per quanto ne so io non credo sia possibile far replicare più master su un solo slave e se anche lo fosse non sarebbe una buona idea:banalmente per gestire la manutenzione del processo di replica, non si avrebbe la possibilita’ di effettuare riallineamenti parziali di un solo master.

            Quello che si fa di solito per ottenere l’effetto di avere tutte le repliche centralizzate su un solo server e’ di installare più istanze mysql (su porte diverse!), ognuna delle quali fa da slave per un master.

          3. Ciao Diego, complimenti per la guida, molto chiara e precisa.
            Volevo chiederti se io faccio una replica di un db, ho problemi se sul database replicato (cioè quello di destinazione) eseguo dei trigger per aggiornare delle tabelle che nel db di origine non esistono??
            Grazie

          4. Ciao Nando,

            come prima cosa mi viene da chiedermi perchè non mettere i trigger anche sul master, in modo da avere *TUTTO* replicato allo stesso modo e risolvere il problema alla radice.

            Cmq, la replica funziona fintanto che le operazioni di scrittura sul master, eseguite poi in replica sullo slave, ottengono lo stesso identico effetto.
            Ciò significa che se il trigger ad esempio è stato costruito in modo da far fallire appositamente una insert, la replica si potrebbe interrompere.

            Per i casi di trigger che invece operano *esclusivamente* su altre tabelle non replicate, la cosa potrebbe anche funzionare… visto la semplicità del problema, perchè non provi in un ambiente di test e ci fai sapere? 😉

          5. Ciao a tutti, ho provato e funziona. Spiego la mia situazione.
            Faccio una replica su un db slave e su questo ho inserito dei trigger che mi vanno a scrivere su alcune tabelle che non sono contenute nel master e funziona.
            Il db master è identico allo slave, però in quest’ultimo ho inserito queste tabelle per tenere traccia delle operazioni effettuate.
            Non ho inserito i trigger sul master perchè non mi interessa tener traccia su quel db delle modifiche.
            A presto

  2. Ciao

    sapete dirmi come mai quando cerco di ripristinare una replica che è caduta ottengo:

    Error ‘Table ‘xxxxx.yyyy’ doesn’t exist’ on query. Default database: ‘zzzzzz’. Query: ‘INSERT IGNORE `WWWWWWWW`……

    Eppure finche non riparto con l’ UNLOCK sul master mi dice che lo slave è allineato, appena riparto mi dà questo errore.

    Usando SET GLOBAL SQL_SLAVE_SKIP_COUNTER=2 non mi cambia nulla

    Grazie

  3. Ho letto con molto interesse questa guida.
    In passato avevo fatto una sincronizzazione mediante un semplice rsync che mi faceva un backup della base dati /mysql/lamiabase/ da un server all’altro.

    Cosa non funziona in questo approccio ?
    Io non ho avuto mai problemi e ho potuto anche ripristinare i dati una volta che mi successe che il server ando’ in panne…
    Grazie
    Emanuele

    1. Ciao Emanuele, la risposta è semplice:

      1. non garantisci l’integrità del dato, cioè potrebbe essere inconsistente il tuo backup
      2. non è per backup che si fa la replica, ma per ridondanza: in qulunque momento passi da master a slave senza buchi temporali nei dati
      3. non funziona se usi innodb, nel tuo caso usi tabelle myisam probabilmente

      Il tuo aproccio va bene per basi dati non innodb e poco movimentate.

      Spero possa esserti d’aiuto!

      ciao

  4. Ciao Diego, complimenti per la guida intanto. Hai qualche metodo da consigliarmi per far partire un avviso quando la replica slave non è più sincronizzata con il db master?
    Grazie

    1. L’unico modo che conosco è attraverso uno script (bash, php….) che a intervalli regolari interroghi lo SLAVE STATUS e mandi una mail di avviso o altro tipo di alert.
      Il comando è SHOW SLAVE STATUS e il valore da intercettare e’ SECONDS BEHIND MASTER.
      SE a 0 è tutto ok, se è vuoto o comunque non è un numero intero c’è un problema. Se è un intero alto, dove alto dipende dallo scopo della tua replica, c’è comunque un problema. Se è un istanza di backup e dimensionata al risparmio, puoi anche permetterti scostamenti di qualche minuto. Se è una replica per alta affidabilità, qualche secondo potrebbe essere già troppo. Dipende da cosa ci gira sopra insomma.

      Qui trovi le informazioni precise e dettagliate: http://dev.mysql.com/doc/refman/5.7/en/replication-administration-status.html

  5. Ciao, complimenti per la guida, avrei un questito da porti.
    Ho effettuato la configurazione master/slave tra due pc e fin qui tutto bene…i database del master me li ritrovo nello slave ed ogni modifica viene aggiornata.
    Il problema mi sorge quando ho provato a settare un altro slave su un altro pc. Infatti, eseguendo da slave2 “show slave status\G” ottengo in uscita i messaggi:

    …..
    Slave_IO_Running: Connecting
    Slave_SQL_Running: Yes
    …..
    Last_IO_Errno: 1130
    Last_IO_Error: error connecting to master ‘[email protected]:3306’ – retry-time: 60 retries 86400

    nel file my.cnf dello slave2 ho solo incrementato l’id senza apportare altra modifica….per il resto ho seguito gli stessi passaggi fatti con il primo slave (funzionante), come mai questo secondo slave non riesce a connettersi al master?
    grazie in anticipo.
    Alfredo

    1. Ciao Alfredo, vedendolo cosi sembrerebbe un problema squisitamente di rete, lo slave2 e il master si vedono in rete?

      Se da slave2 fai un telnet 192.168.0.100 3306 va in connessione o in timeout?

        1. nel mysql del master facendo “show full processlist” vedo che lo slave1 è connesso…mentre dello slave2 non v’è traccia 🙁

  6. Ciao Diego, articolo interessante anche se adesso siamo un po’ troppo lontani dalla sua pubblicazione. Avrei un quesito da porti, master online e slave in locale, se fra il master e lo slave si interrompe la connessione e per continuare a lavorare tutte le query vengono eseguite solo sullo slave (è possibile?), al ripristino della connessione si avrà un allineamento dei dati o andrà tutto in blocco? e in quanto tempo le query eseguite sul master saranno presenti anche sullo slave? Grazie mille.

    1. Forse intendevi dire “tutte le query vengono eseguite solo sul MASTER” ?
      Cioè se si ferma la connessione M-S, il master continua a lavorare e lo slave smette di replicare.
      In questo caso al ripristino della connessione credo (è un po’ che non ci lavoro) va fatta partire la replica con uno

      START SLAVE;

      E progressivamente vedrai diminuire il valore di Seconds_Behind_Master.

      In quanto tempo? Dipende dalla quantità di scritture fatte sul master dal momento del blocco al momento del ripristino e dalle scritture attualmente in corso. Quindi se il master ha poche scritture potenzialmente anche in pochi secondi.
      Oppure anche mai, se la capacità di scrittura dello slave è inferiore al quantitativo di scritture sul master 🙂

      Inoltre le scritture sono conservate nei log binari, quindi va opportunamente configurata la retention del binary log del master.
      Se hai i binary log degli ultimi 3 giorni e la connessione si interrompe per 4, hai spaccato la replica, va rifatto tutto da capo.

Leave a Reply to diego Cancel reply

Your email address will not be published.