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).

Sincronizzare mediante rsync

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

rsync

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

Ciao sapete dirmi come mai

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

Ciao Diego, grazie per la

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?

Verifiche

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.

Ciao, E' possibile settare

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

Tempi per la replica

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...

MySql Replica di più server remoti in un unico server locale

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

sistema mysql multi master

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.

Ciao Diego, complimenti per

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