Recentemente ho dovuto affrontare il problema di creare un backup di un server MySQL di produzione, contenente tutti i database di un piccolo hosting provider italiano. Esistono varie soluzioni per ottenere il medesimo scopo, sia gratuite che a pagamento, e dopo aver analizzato i pro e i contro, è stata scelta la funzione di replication integrata in MySQL stesso.
Annoto qui a futura memoria una guida pratica con tutti i passaggi per l’attivazione della replica speculare di un host MySQL, con la particolare attenzione di fermare il meno possibile i database di produzione.
1) Installare MySQL sul server di backup (slave)
Scaricare ed installare il motore di database MySQL. Non è obbligatorio, ma consiglio che sia la stessa versione del server di produzione, per evitare problemi con le query contenente codice SQL extra standard. Può essere installato senza problemi su un sistema operativo differente dal master. Consigliata una connessione LAN dedicata.
2) Assegnare un ID univoco al server di produzione (master) ed al backup (slave)
Modificare il file my.ini dei 2 server aggiungendo le seguenti righe
[mysqld] server-id=N log-bin=mysql-bin
Dove N è un numero, es. 1 per il master e 2 per lo slave. log-bin si può tralasciare sullo slave.
Riavviare entrambi i MySQL.
3) Creare sul master un utente con i permessi di replica
Non obbligatorio, ma fortemente consigliato. Non utilizzate l’utente root perché in caso di cambio password la replica si interrompe ed andrebbe riconfigurata.
GRANT REPLICATION SLAVE ON *.* TO 'replicator'@'%' IDENTIFIED BY 'password';
Sostituire 'replicator' e 'password' con valori a piacere
4) Bloccare il master in sola lettura
Per ottenere un backup consistente, è necessario bloccare l’host MySQL master in sola lettura. D’ora in poi inizia il fermo macchina del database di produzione (tutte le query di insert ed update restituiranno un errore).
FLUSH TABLES WITH READ LOCK;
Importante: la query deve essere eseguita e la relativa sessione con l’host dev’essere lasciata aperta, altrimenti all’uscita le tabelle verrebbero sbloccate automaticamente. Non va bene, ad esempio, bloccare le tabelle da phpMyAdmin o MySQL Query.
5) Backup del master e restore dello slave
Eseguite il backup del master ed immediatamente dopo il restore sullo slave, compreso il database MySQL contenente gli utenti ed i permessi sui database. Potete utilizzare lo strumento che riterrete più opportuno: abitualmente utilizzo MySQL Administrator.
6) Annotare la posizione del log e sbloccare il master
Subito dopo aver eseguito il backup è possibile annotare il nome del file del log binario e la relativa posizione attuale, informazioni che ci serviranno nel passaggio successivo.
SHOW MASTER STATUS;
Quindi ora si può sbloccare il database di produzione. Qui si conclude il fermo macchina del database di produzione.
UNLOCK TABLES;
7) Attivare lo slave
Ora si deve configurare lo slave in modo che riesca a collegarsi al master, leggere il log binario ed eseguire tutte le nuove query per rendere sincronizzati i 2 database.
CHANGE MASTER TO MASTER_HOST = 'master_host', MASTER_USER = 'replicator', MASTER_POST = 3306, MASTER_PASSWORD = 'password', MASTER_LOG_FILE = 'mysql-bin.000001', MASTER_LOG_POS = N;
In questa query vanno inserite tutte le informazioni ottenute nei passaggi precedenti. Una volta fatto è sufficiente far partire il processo di replica nello slave
START SLAVE;
Quindi verificare che tutto sia andato a buon fine.
SHOW SLAVE STATUS;
È importante verificare che Slave_IO_Running e soprattutto Slave_SQL_Running siano entrambi con valore Yes, altrimenti significa che qualcosa è andato storto. Normalmente l’ultimo errore SQL riportato in Last_Error è significativo del problema. Ad esempio un errore SQL per chiavi primarie duplicate è sintomo di posizione log errata dovuto allo sblocco anticipato del master.