Impostare una Master-Master Replication tra due MySQL Server

Premessa

Impostare una replication di tipo Master-Master, nota anche come mirror, è uno dei modi più semplici ed economici per incrementare le performance e la stabilità del sistema. Nel caso in cui non sappiate di cosa si tratta immaginate due istanze di MySQL Server che comunicano in tempo reale condividendo tutte le query di inserimento, modifica ed eliminazione - comprese quelle presenti all'interno di funzioni e stored procedures - in modo che i dati siano costantemente inseriti, aggiornati ed eliminati dall'una e dall'altra parte. Non solo: nell'esempio che faremo in questo articolo vedremo come la replication possa essere configurata anche per condividere la creazione, modifica ed eliminazione di utenti e database.

Inutile dire che, per ottenere questo, occorrerà acquistare un secondo server (ovvero un secondo servizio MySQL), possibilmente all'interno della stessa LAN, cosa che comporterà un aumento dei costi. Un investimento che non tarderà a ripagarsi, dotando il vostro sistema di due punti di forza di notevole importanza in termini di qualità del servizio:

• Un mirror di tipo 1:1 per tutti i database ospitati dal sistema - oppure, a scelta, per un sottoinsieme di database selezionabile a piacere - che renderà possibile ripristinare il sistema in conseguenza di un qualsivoglia crash hardware o software senza perdita di dati e senza bisogno di dover ripristinare alcun backup.
• Una base dati notevolmente più robusta ed efficiente,  che consentirà di bilanciare meglio gli accessi di eventuali client (o siti web) consentendo di suddividere le connessioni su due diversi servizi, ciascuno dotato di un IP e di una capacità computazionale propria, dotati della medesima base dati.

Uno degli utilizzi più comuni della Master-Master replication è all'interno di un cluster di 2 VPS o server dedicati, ciascuno dei quali dotato di un web server http che contiene uno o più siti alimentati da un database MySQL installato sulla stessa macchina. Mettendo in Master-Master replication i due servizi MySQL è possibile creare un mirror conforme di tutti i servizi web, creando così un vero e proprio sistema di disaster recovery (vedi figura sottostante) e/o un web cluster a 2 nodi da servire attraverso un load-balancer hardware installato a monte su un terzo IP (o altra tecnica di balancing a scelta).

 

Requisiti

Per mettere in piedi una Master-Master replication avrete bisogno di due server (VPS, Hosting, ambienti virtualizzati et. al.), ciascuno dei quali in grado di rispondere a un diverso indirizzo IP: non è necessario che siano identici, l'importante è che abbiano una potenza sufficiente a far girare decentemente la versione di MySQL che andrete ad installare (consigliamo MySQL 5.1 o superiore). Per comodità, nel corso dell'articolo li chiameremo rispettivamente Server A e Server B.

Step 1. Installare i MySQL Server

La prima cosa da fare è installare l'ultima versione di MySQL sul Server A e sul Server B. Nel caso in cui abbiate già una istanza installata e funzionante su uno dei due, effettuate (se possibile) un upgrade all'ultima versione e quindi installate la stessa identica versione sull'altro server.

Una volta installate entrambe le istanze di MySQL, modificate il file di configurazione di ciascuna di esse - /ProgramData/MySQL/MySQL Server 5.x/my.ini su Windows, /etc/mysql/my.cnf su Linux - e inserite le impostazioni indicate qui di seguito (o sostituitele a quelle già presenti):

Server A

Server B

Come potete vedere le configurazioni sono praticamente identiche, con la sola eccezione dei valori  server-id  e auto-increment-offset .

Queste impostazioni daranno modo a entrambi i server di replicare tutti i database presenti al loro interno, con la sola eccezione di test e information_schema. Questo significa che saranno replicati anche i comandi relativi alla creazione/modifica/eliminazione degli utenti e dei database, in quanto il database di sistema mysql sarà anch'esso replicato. Nel caso in cui vogliate mantenere una struttura utenti o database diversa è consigliabile escludere il database mysql inserendo gli appositi comandi binlog-ignore-db e replicate-ignore-db. Lo stesso dicasi per qualsiasi altro database che vogliate escludere dalla replicazione.

NOTA IMPORTANTE: replicare il database di sistema mysql non è una strada percorribile per i server basati su Debian, per i motivi spiegati all'interno di questo commento. In tutti i casi in cui non siete certi che i vostri server possano supportare la condivisione del database di sistema, il consiglio è di escluderlo secondo le modalità sopra descritte: perderete la possibilità di tenere gli utenti sincronizzati, ma nella maggior parte dei casi non è un danno grave in quanto non è un'operazione che si verifica con grande frequenza.

Se i database che volete escludere sono più di quelli che volete replicare potete anche scegliere di adottare una logica di tipo opt-in in luogo della logica opt-out mostrata nell'esempio: per far questo, eliminate tutti i comandi binlog-ignore-db e replicate-ignore-db e rimpiazzateli con una serie di comandi binlog-add-db e replicate-add-db relativi ai database che intendete replicare.

Una volta fatto questo, riavviate entrambi i servizi così da caricare il file di configurazione aggiornato e passate al punto successivo.

Step 2. Creare l'utente Replicator

L'operazione successiva che dovete effettuare è creare l'utente replicator, che verrà utilizzato per occuparsi della replicazione e al quale andranno forniti i permessi necessari. Inutile dire che, dovendo impostare una Master-Master replication, l'utente andrà creato sia sul Server A che sul Server B. Potete crearlo come qualsiasi altro utente, ad esempio utilizzando i seguenti comandi direttamente dalla shell di MySQL:

Sostituite [root_password] con la password di root e [replicator_password] con una password a vostra scelta: tenetela a mente, la utilizzerete nuovamente tra poco (cfr. Step 4.5).

Effettuate questo passaggio su entrambi i server: nel caso in cui vogliate utilizzare una password diversa, ricordate di usare quella giusta durante gli Step 4 e 5.

Step 3. Sincronizzate i Database

Questa è una operazione che dovrete effettuare manualmente: prima di impostare la replication, dovrete assicurarvi che le due istanze di MySQL installate su Server A e su Server B contengano gli stessi dati. Se utilizzate soltanto tabelle MyISAM, potete effettuare la sincronizzazione copiando i folder corrispondenti direttamente dalla directory /data/ della vostra installazione di MySQL: in caso contrario potete effettuare un export / import delle tabelle da sincronizzare utilizzando la shell di MySQL, come descritto nella documentazione ufficiale, oppure ricorrere a un tool di terze parti che faccia questo lavoro per voi: a tale scopo potete utilizzare il Database Migration Tool incluso nella suite MySQL Workbench come si può vedere nella figura seguente (istruzioni per l'uso):

Oppure utilizzare l'utilissima funzione Copy Database to different Host/Database dell'ottimo programma SQLyog (se non ne avete mai sentito parlare, leggete qui), una delle migliori Management GUI di MySQL presenti sul mercato: nell'infelice caso in cui non la conosciate già vi consiglio fortemente di provare la Community Edition (multi-piattaforma e open-source) prelevandola dalla pagina ufficiale su GitHub: non ve ne pentirete di certo.

Step 4. Impostare la Replication dal Server A al Server B

La prossima cosa da fare è impostare l'istanza di MySQL installata sul Server B affinché faccia propri tutti i comandi di modifica relativi ai database configurati per la replication inviati al Server A: in altre parole, occorre configurare il Server B come slave del Server A. Per far questo, collegatevi al Server A tramite MySQL Shell, SQLyog o qualsiasi altro strumento e date il seguente comando:

Il Server A risponderà con un response di questo tipo:

Prendete nota del valore delle colonne File e Position: vi serviranno a breve.

Adesso collegatevi al Server B (sempre tramite shell, SQLyog o altra GUI) e date i seguenti comandi:

Sostituite Server A IP Address con l'indirizzo IP numerico del Server A, [replicator_password] con la password che avete impostato per l'utente replicator durante lo Step 2, mysql-bin.000001 and 107 con i valori delle colonne File e Position restituiti dal Server A all'inizio di questo step.

Se avrete fatto tutto correttamente dovreste ricevere la seguente risposta dal Server B:

 

Step 5. Impostare la Replication dal Server B al Server A

Adesso occorre fare la stessa identica cosa dall'altra parte, ovvero configurare il Server A come slave del Server B. Connettetevi al Server B e date il seguente comando:

Riceverete anche questa volta un response di questo tipo:

Connettetevi ora al Server A e date i seguenti comandi:

Sostituite anche in questo caso Server B IP Address con l'IP numerico del Server B, replicator_password con la password che avete attribuito all'utente replicator durante lo step 2, mysql-bin.000001 e 107 con i valori delle colonne File e Position restituiti dal Server B.

Se avrete fatto tutto correttamente dovreste ricevere la seguente risposta dal Server A:

Da questo momento in avanti la Master-Master replication sarà attiva sui vostri due MySQL.

Test della Replication

L'ultima cosa da fare è sincerarsi che la replication sia realmente attiva. Suggeriamo di fare le seguenti prove:

1. creare un nuovo database (tramite MySQL shell, SQLyog o altra GUI) sul Server A, quindi connettersi al Server B per assicurarsi che il database sia stato creato anche lì.
2. creare un nuovo utente mysql sul Server B, quindi connettersi al Server A per assicurarsi che l'utente sia stato creato anche lì.
3. effettuare una o più query di tipo insert, delete o update sul Server A e controllare che i dati vengano inseriti, cancellati o modificati anche sul Server B.

Inutile dire che i primi due test daranno esito positivo solo nel caso in cui abbiate scelto di non escludere il database mysql dalla replication (cfr. Step 1). Nel caso in cui abbiate deciso di escluderlo, oppure di adottare la logica opt-in senza includerlo esplicitamente, dovrete effettuare manualmente le operazioni sugli utenti e sui database su entrambi i server.

EDIT: Nel caso in cui la Replication si arresti, perda la sincronizzazione o smetta di funzionare vi consiglio di dare un'occhiata a questo articolo che illustra le principali operazioni da effettuare in questi casi.