MySQL Advanced Replication - Multimaster

Da Inc0Wiki.

Preambolo

Questa guida prevede che il/i server MySQL sia/no già configurato/i e funzionante/i (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 in cui è possibile eseguire scritture su tutti i nodi (sistema Multimaster): la replicazione prevede quindi la presenza di almeno 2 server.

Pacchetti Necessari

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

Un po' di Teoria...

...che non guasta mai: la replicazione in un sistema Multimaster prevede l'uso di almeno due server che verranno configurati come Master/Slave su cui verranno inoltre modificati i valori di default delle variabili auto_increment_increment che gestisce l'incremento tra valori successivi di campi definiti AUTO_INCREMENT e auto_increment_offset che definisce il valore di partenza dei campi definiti AUTO_INCREMENT.
Esempio:

+-------------------------------+-------------------------------+-------------------------------+
|             Srv1              |             Srv2              |             Srv3              |
+-------------------------------+-------------------------------+-------------------------------+
| auto_increment_increment = 10 | auto_increment_increment = 10 | auto_increment_increment = 10 |
| auto_increment_offset    = 0  | auto_increment_offset    = 1  | auto_increment_offset    = 2  |
+-------------------------------+-------------------------------+-------------------------------+
| Definita una ipotetica colonna chiamata ID con attributo AUTO INCREMENT, la scrittura di una  |
| riga sui server sopra indicati, indipendentemente dal momento in cui viene eseguita, assumerà |
| i seguenti valori:                                                                            |
+-----------------------------------------------------------------------------------------------+
|               0               |               1               |                2              |
|              10               |              11               |               12              |
|              20               |              21               |               22              |
|              30               |              31               |               32              |
|              40               |              41               |               42              |
|              50               |              51               |               52              |
+-------------------------------+-------------------------------+-------------------------------+

E' evidente quanto sia importante scegliere il valore di auto_increment_increment in base al numero di server che si intende utilizzare nella replicazione (valutando anche espansioni future).

Configurazione

Il file di configurazione di MySQL è my.cnf e solitamente si trova in /etc/mysql: in quanto ogni server agisce sia da Master che da Slave, su ognuno dovrà essere definito sia il file bin_log che il relay_log, dovrà essere impostato un server_id univoco, bisognerà impostare il medesimo valore di auto_increment_increment e cambiare quello di auto_increment_offset a seconda del server. Oltre al file di configurazione, inoltre, è necessario creare un'utenza in grado di eseguire la replicazione.

/etc/mysql/my.cnf

# 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

# Ipotizzando di voler replicare 2 server, e prevedendo l'ampliamento a 4, il valore di
# auto_increment_increment sarà 4 mentre quello di auto_increment_offset varierà a seconda del server:
# da 0 a 3 o da 1 a 4.
auto_increment_increment      = 4
auto_increment_offset         = 0

Account per la Replicazione

Per eseguire la replicazione è necessario che un utente possa accedere ai dati, in modo specifico al bin-log: a tale scopo è necessario creare sui nodi 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

La prima cosa da fare è configurare il/i nuovo/i server (che da ora chiameremo B) come Slave così da arrivare ad una replicazione standard. Fatto ciò si dovra configurare il server pre-esistente (che da ora chiameremo A) affinchè legga le modifiche effettuate sui dati di B e le apporti ai propri: per fare questo andiamo su B, entriamo nella shell mysql e digitiamo

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) visualizziamo lo stato del DB con

mysql> SHOW MASTER STATUS;
+---------------+----------+--------------+------------------+
| File          | Position | Binlog_do_db | Binlog_ignore_db |
+---------------+----------+--------------+------------------+
| mysql-bin.002 | 1054     |              |                  |
+---------------+----------+--------------+------------------+

ed annotiamo il nome del file ed il valore associato a Position in quanto queste informazioni saranno necesssarie al fine di avviare la replicazione.
A questo punto possiamo sbloccare le scritture con:

mysql> UNLOCK TABLES;

Andiamo su A, entriamo nella shell mysql e digitiamo

mysql> CHANGE MASTER TO
MASTER_HOST='hostname_or_IP_ADDRESS_of_B',
MASTER_USER='utente_replicatore',
MASTER_PASSWORD='p4ssw0rd',
MASTER_LOG_FILE='mysql-bin.002',
MASTER_LOG_POS=1054;

dove

  • hostname_or_IP_ADDRESS_of_b: sono l'hostname del server B
  • utente_replicatore: è l'utente abilitato alla replicazione
  • p4ssw0rd: è la password associata a tale utente
  • mysql-bin.002: è il nome del bin-log ottenuto tramite l'esecuzione di SHOW MASTER STATUS
  • 1054: è la posizione ottenuta tramite l'esecuzione di SHOW MASTER STATUS

Quello che è abbiamo appena fatto è stato dire a A (il server esistente da tempo) che il suo Master è hostname_or_IP_ADDRESS_of_B, 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.002 alla posizione 1054.
Una volta certi che tutte le informazioni sono corrette, digitate

mysql> START SLAVE;

per avviare la replicazione.

Verifica

Per vefiricare che tutto funziona regolarmente, potete eseguire:

mysql>  SHOW PROCESSLIST;

o, per una lettura più facile:

mysql> SHOW PROCESSLIST\G;

e verificare se ottenete un output simile al seguente:

mysql> show processlist\G;
*************************** 1. row ***************************
     Id: 1
   User: system user
   Host:
     db: NULL
Command: Connect
   Time: 90
  State: Waiting for master to send event
   Info: NULL
*************************** 2. row ***************************
     Id: 2
   User: system user
   Host:
     db: NULL
Command: Connect
   Time: 30
  State: Has read all relay log; waiting for the slave I/O thread to update it
   Info: NULL
*************************** 3. row ***************************
     Id: 4
   User: root
   Host: localhost
     db: NULL
Command: Query
   Time: 0
  State: NULL
   Info: show processlist
*************************** 4. row ***************************
     Id: 5
   User: repl
   Host: mx02.valhalla.lan:40066
     db: NULL
Command: Binlog Dump
   Time: 79
  State: Has sent all binlog to slave; waiting for binlog to be updated
   Info: NULL
4 rows in set (0.00 sec)

ERROR:
No query specified

mysql>

che indica che la replicazione funziona regolarmente.

Suggerimenti

Come sempre, 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, guardate nel file di log di MySQL.

Note

In una situazione di questo tipo dove è possibile eseguire scritture su tutti i nodi, bisogna prestare attenzione anche a replicare le utenze ed i privilegi associati agli utenti ovvero bisogna replicare ANCHE il database mysql che contiene queste informazioni e quando aggiungete/modificate un utente dovete assicurarvi che i privilegi che andate ad associare gli permettano/neghino di eseguire le operazioni a cui intendete abilitarlo/inibirlo su tutti i nodi.

Credits

Autore: Incubus

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