Come impostare una replica mysql master slave per avere un database di backup

Questa è una guida supersemplificata di come creare un sistema di replica mysql master – slave.

Gli scopi di questo meccanismo sono:

• Maggiore affidabilità del database – nel caso in cui il Server Master abbia dei problemi e diventi irraggiungibile possiamo rapidamente modificare impostazioni del Server Slave in modo tale da modificare l’indirizzo IP della macchina e configurarlo come fosse il nuovo Master.
• Utilizzare il secondo database come un backup. Inoltre si può backuppare il filesystem senza appesantire il database master

Per facilità indicherò le operazioni da eseguire sul master con M e sullo slave con S.

Ecco i passaggi per attivare questo meccanismo:

M: Accedere al database mysql e creare un utente con i privilegi di replica:

GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'%' IDENTIFIED BY 'repl_password'

Se siete maniaci della sicurezza potete sostituire il % con l’ip dello slave.

Adesso modificare il file my.cnf posto nella cartella di installazione di mysql. (Su ubuntu normalmente è in /etc/mysql/my.cnf

[mysqld]
server-id = 1
log-bin = /var/log/mysql/mysql-bin.log
max_binlog_size = 104857600
#Facoltativi
binlog-do-db = databasecritico
binlog-ignore-db = databaseinutile

Attenzione!! [mysqld] è già presente in quel file quindi non aggiungete queste righe all’inizio ma nella sezione mysqld.

Nel caso in cui non vengano specificati binlog-do-db e binlog-ignore-db, l’impostazione predefinita replicherà tutti i database.

Una volta salvato il file riavviate il servizio mysql con il comando

/etc/init.d/mysql restart

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

FLUSH TABLES WITH READ LOCK;

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

Io ho usato mysql query administrator in quanto risulta essere più flessibile nel caso in cui le versioni dei due server M ed S siano diverse tra di loro.

5. M: eseguire la query che segue ed annotare i risultati

mysql> SHOW MASTER STATUS\G
*************************** 1. row ***************************
File: mysql-bin.000001
Position: 531
Binlog_Do_DB:
Binlog_Ignore_DB:
1 row in set (0.02 sec)

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: Arrestare il servizio myslq

/etc/init.d/mysql stop

e fare il backup del database con mysqldump e/o salvataggio del filesystem.

8. Importare il database (o meglio i database) salvati al punto 4 con un esecuzione del file di dump ottenuto con mysqldump a seconda del metodo utilizzato (nel mio specifico caso optato per il secondo metodo poichè a mio avviso più flessibile).

Attenzione!! Spesso e volentieri importando il database “information schema” e “mysql” (i due di default che troviamo ad installazione nuova e pulita) ho riscontrato diversi problemi. Consiglio quindi di evitare di effettuare un ripristino di questi database e magari copiare quello che interessa row per row.

9. S: assicurarsi di avere nel my.cnf dello slave (sempre nella sottosezione [mysqld] le righe seguenti:

server-id = 2
log-bin = /var/log/mysql/mysql-bin.log
max_binlog_size = 104857600

10. 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 seguente query sullo slave:

mysql> SHOW PROCESSLIST\G
*************************** 1. row ***************************
     Id: 1
   User: system user
   Host:
     db: NULL
Command: Connect
   Time: 181475
  State: Waiting for master to send event
   Info: NULL
*************************** 2. row ***************************
     Id: 2
   User: system user
   Host:
     db: NULL
Command: Connect
   Time: 188662
  State: Has read all relay log; waiting for the slave I/O thread to update it
   Info: NULL
*************************** 3. row ***************************
     Id: 12
   User: root
   Host: localhost
     db: NULL
Command: Query
   Time: 0
  State: NULL
   Info: SHOW PROCESSLIST
3 rows in set (0.00 sec)

Tra le varie righe deve esserci “Waiting for master to send event“, se ci fossero problemi di connessione e/o regole firewall il messaggio sarebbe invece “Connecting to master“. Inoltre lanciando il seguente comando:

SHOW SLAVE STATUS\G
*************************** 1. row ***************************
             Slave_IO_State: Waiting for master to send event
                Master_Host: 172.17.2.130
                Master_User: repl_user
                Master_Port: 3306
              Connect_Retry: 60
            Master_Log_File: mysql-bin.000004
        Read_Master_Log_Pos: 98
             Relay_Log_File: mysqld-relay-bin.000061
              Relay_Log_Pos: 235
      Relay_Master_Log_File: mysql-bin.000004
           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
1 row in set (0.00 sec)

Si ottiene una serie di dati tra cui “Slave_IO_State” che deve essere “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 o se ho appena ripristinato la connessione tra i due database).

TROUBLESHOOTING

Il meccanismo si può inceppare:

• Nel caso in cui si perda la connessione con il Master (per problemi di utenze modificate o banali problemi di rete) che si sistema normalmente al ripristino della connessione.
• Nel caso in cui i database non siano più uguali: nel 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.

In questo ultimo caso

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() occupano due righe di log per cui in questo caso si dovrà impostare SQL_SLAVE_SKIP_COUNTER = 2 DORMIRE SOGNI TRANQUILLI.

Questo articolo è stato preso ed approfondito da questo ottimo articolo

/etc/init.d/mysql restart