MySQL Replication

Da Inc0Wiki.

Preambolo

Questa guida prevede che il/i server MySQL siano già configurati e funzionanti (anche da tempo): non ci occuperemo della messa in funzione del server.

Scopo

Questa guida ha lo scopo di insegnare come implementare un sistema di replicazione di server MySQL: la replicazione prevede la presenza di un master ed N slave; gli slave sono una copia del Master: su di essi è possibile eseguire le letture ma le scritture devono essere eseguite solo sul Master.
In una futura guida verrà spiegato come aggirare questa limitazione.

Pacchetti Necessari

mysqld - questa guida è stata scritta e testata con MySQL versione 5.0

Un po' di Teoria...

...che non guasta mai: la replicazione prevede l'uso di almeno due server, uno che agisce come Master, l'altro che agisce come Slave: è sul master che devono essere eseguite le scritture (INSERT, UPDATE, DELETE, ecc.) affinchè vengano replicate sullo slave ma nulla vieta di eseguire le letture (SELECT) sugli slave.
A grandi linee, la replicazione funziona in questo modo:
- il Master registra i cambiamenti nel suo bynary log
- lo Slave copia gli eventi del binary log del Master nel suo relay log
- lo Slave replica gli eventi del suo relay log applicando i cambiamenti ai propri dati

Configurazione

Il file di configurazione di MySQL è my.cnf e solitamente si trova in /etc/mysql: è necessario apportare alcune piccole modifiche al fine di definire quale server ha il ruolo di master, quale quello di slave, dove salvare il bin_log e dove il relay_log. Oltre al file di configurazione, inoltre, è necessario creare un'utenza in grado di eseguire la replicazione.

Master: /etc/mysql/my.cnf

La configurazione del Master prevede l'aggiunta/modifica delle seguenti righe:

# Nome del file bin-log dove saranno registrare le operazioni di modifica dei dati
log-bin                                 = mysql-bin

# Numero identificativo UNIVOCO del server MySQL
server-id                               = 1

# Quanto devono essere vecchi i dati contenuti nel bin-log perchè possano essere eliminati
expire_logs_days                        = 28

# Di default il file di bin-log non è sincronizzato con le scritture sul DB. Forziamo questo valore ad 1
# al fine di ottenere tale sincronia ed evitare la perdita di informazioni nel bin-log in caso di crash.
sync_binlog                             = 1

Slave: /etc/mysql/my.cnf

La configurazione dello Slave prevede l'aggiunta/modifica delle seguenti righe:

# Numero identificativo UNIVOCO del server MySQL
server-id               = 10

# Nome del file bin-log dove saranno registrare le operazioni di modifica dei dati
log_bin                 = mysql-bin

# Nome del file relay-log  dove saranno importate le informazioni del Master
relay-log               = mysql-relay-bin

# Per abilitare il salvataggio delle modifiche apportate ai dati presenti sullo Slave nel proprio bin-log
log_slave_updates       = 1

Account per la Replicazione

Ovviamente, per eseguire la replicazione è necessario che un utente possa accedere ai dati, in modo specifico al bin-log: a tale scopo è necessario creare sia sul Master che sullo Slave un utente con privilegi di REPLICATION SLAVE e REPLICATION CLIENT.
Esempio:

mysql> GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO utente_replicatore@'192.168.1.%' IDENTIFIED BY 'p4ssw0rd';

dove

  • utente_replicatore: l'utente che verrà usato per eseguire la replicazione
  • *.*: database/tabelle alle quali l'utente deve poter accedere
  • 192.168.1.%: l'indirizzo ip (in questo caso la sottorete 192.168.1.0/24) dalla quale l'utente potrà collegarsi
  • p4ssw0rd: la password usata per la connessione

Preparazione della Replica

Master

Probabilmente il Master del quale volete eseguire la replica è ha già dei dati al suo interno, è quindi necessario procedere ad una copia iniziale di tali dati sullo/gli Slave per poi avviare la replicazione.
Inziamo bloccando le modifiche delle tabelle sul Master loggando nella shell MySQL e digitiando

mysql> FLUSH TABLES WITH READ LOCK;

Non usciamo dalla shell o il blocco verrà rimosso. Eseguiamo una copia dei database: a voi la scelta del come procedere: mysqldump, copia dei files o altro e poi visualizziamo lo stato del Master con

mysql> SHOW MASTER STATUS;
+---------------+----------+--------------+------------------+
| File          | Position | Binlog_do_db | Binlog_ignore_db |
+---------------+----------+--------------+------------------+
| mysql-bin.006 | 183      |              |                  |
+---------------+----------+--------------+------------------+

Annotiamo il nome del file ed il valore associato a Position in quanto queste informazioni saranno necesssarie sullo slave al fine di avviare la replicazione.
A questo punto possiamo sbloccare il Master così che le scritture siano nuovamente possibili quindi, nella shell dalla quale prima lo abbiamo bloccato digitiamo:

mysql> UNLOCK TABLES;

Slave

La predisposizione dello Slave prevede l'importazione dei DB al momento dell'esecuzione di FLUSH TABLES WITH READ LOCK sul Master e l'esecuzione della query

mysql> CHANGE MASTER TO
MASTER_HOST='hostname_or_IP_ADDRESS',
MASTER_USER='utente_replicatore',
MASTER_PASSWORD='p4ssw0rd',
MASTER_LOG_FILE='mysql-bin.006',
MASTER_LOG_POS=183;

dove

  • hostname_or_IP_ADDRESS: sono l'hostname del master o l'ip addresso dello stesso
  • utente_replicatore: è l'utente abilitato alla replicazione
  • p4ssw0rd: è la password associata a tale utente
  • mysql-bin.006: è il nome del bin-log ottenuto tramite l'esecuzione di SHOW MASTER STATUS sul master
  • 183: è la posizione ottenuta tramite l'esecuzione di SHOW MASTER STATUS sul master

Quello che è abbiamo appena fatto è stato dire allo Slave che il master è hostname_or_IP_ADDRESS, l'utente da usare per la replicazione è utente_replicatore la cui password è p4ssw0rd e che la replicazione dovrà partire recuperando le informazioni dal file mysql-bin.006 alla posizione 183.
Una volta certi che tutte le informazioni sono corrette, sullo slave, basta eseguire

mysql> START SLAVE;

per avviare la replicazione.

Verifica

Per vefiricare che tutto funziona regolarmente, sul Master potete eseguire:

mysql>  SHOW PROCESSLIST;

o, per una lettura più facile:

mysql> SHOW PROCESSLIST\G;

e verificare la presenza di una riga simile a

*************************** 1. row ***************************
     Id: 70616
   User: replicator
   Host: 10.0.0.51:53447
     db: NULL
Command: Binlog Dump
   Time: 33362
  State: Has sent all binlog to slave; waiting for binlog to be updated
   Info: NULL

Dallo Slave, potete eseguire

mysql> show slave status\G;
*************************** 1. row ***************************
             Slave_IO_State: Waiting for master to send event
                Master_Host: 192.168.1.2
                Master_User: replicator
                Master_Port: 3306
              Connect_Retry: 60
            Master_Log_File: mysql-bin.000100
        Read_Master_Log_Pos: 16136910
             Relay_Log_File: mysql-relay-bin.000256
              Relay_Log_Pos: 9375748
      Relay_Master_Log_File: mysql-bin.000100
           Slave_IO_Running: Yes
          Slave_SQL_Running: Yes
-----------------------------------------CUT-----------------------------------------
1 row in set (0.07 sec)

ERROR:
No query specified

mysql>

per verificare nuovamente che non ci siano problemi durante la replicazione.

Suggerimenti

Probabilmente questa non è la prima guida sulla replicazione che avete letto, probabilmente da qualche parte avete trovato scritto che per eseguire la copia dei DB dal master allo slave è possibile usare LOAD DATA FROM MASTER e/o LOAD TALBE FROM MASTER ma io vi sconsiglio caldamente di usare tali istruzioni in quanto con la versione 5.0 e successive sono deprecate, e funzionano solo se l'engine della tabelle è MyISAM: esatto, InnoDB & co non sono supportati.

Se riscontrate problemi nell'avvio della replica oltre a leggere l'errore mostrato dopo l'esecuzione di START SLAVE, verificate cosa appare all'esecuzione di SHOW PROCESSLIST e SHOW SLAVE STATUS inoltre, come sempre, guardate nel file di log di MySQL.

In una situazione con un Master ed uno Slave, al fine di eseguire il backup evitando di bloccare il master, si può procedere fermando lo slave tramite STOP SLAVE, eseguire il dump del/dei database interessati e riavviarlo tramite START SLAVE.

Se master e slave per qualche motivo vanno fuori sync, è ovviamente necessario valutare il motivo che ha causato il problema ed a seconda della gravità è necessario procedere di conseguenza. Potrebbe capitarvi un errore su una tabella temporanea che non è stata creata sullo slave e che quando viene richiesta per l'eliminazione, ferma la replica generando un errore; se ritenete che basti rimuovere la tabella potete procederein questo modo:
- fermate lo slave

mysql> stop slave;

- eliminate la tabella
- fate saltare l'esecuzione della successiva query (è possibile specificare quante query saltare indicando, anzichè 1, un valore superiore):

mysql> SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;

-fate ripartire lo slave e verificate che tutto funzioni

mysql> start slave; show slave status\G;

Se aveste la necessità di far 'skippare' lo stesso tipo di errore, potete aggiungere quanto segue al file di configurazione dell'istanza slave:

slave-skip-errors = error_id, error_id #all for all errors

A vostro rischio e pericolo di quello che potrebbe succedere alla bontà dei dati ;)

Credits

Autore: Incubus

E-Mail: theincubus (at) gmail (dot) com