Come impostare una replica mysql master slave per avere un database di backup
Scritto 30 agosto 2009 – 13:41 in: Tips, Tutti, Ubuntu
|
|
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
Dove Acquistare la R4 e la PS3 Break
Scopri Subito le migliori Offerte di Oggi per la R4i e la PS3 Break in uno degli eCommerce più affidabili cha ho Provato!
- la R4 Revolution con Pennino in REGALO!
- la R4i – R4 Nintendo con Pennino e Software in REGALO!
- la PS3 Break per Playstation 3!
Tutti i Prodotti sono Disponibili SUBITO!
Post Simili
Attualmente ho due server indipendenti che lavorano in modo distinto, l’un dall’altro.
Ogniuno ha a bordo dei servizi differenti che vanno ad interagire con un Mysql locale (versione 5).
Ogni installazione di Mysql ha tabelle differenti, utenti differenti, viste differenti.
Sono a tutti gli effetti due server Windows master.
Ho l’esigenza che l’uno diventi il “nodo di disastro” dell’altro… e quindi se uno dei due venisse a mancare, l’altro dovrebbe farsi carico di tutti i servizi.
Dal punto applicativo non ho grossi problemi: li lascio dormienti sulla macchina ed all’occorrenza li avvio.
Dal punto di vista del DB attualmente ho qualche perplessita’.
Quale sarebbe la strada migliore affinche’ si possa fare in modo che i dati del DB del server A vengano “trasferiti” nel DB del server B, in modo automatico e con una certa frequenza (tipo ogni 4 ore)?
Questo trasferimento di dati da A a B non deve pero’ compromettere i dati/utenti/viste/trigger gia’ presenti in B.
Un paio di domande..
-Server Windows o Linux?
-Quanto pesa in MB il database?
-I due server sono nella stessa LAN o sono in reti diverse?
Server Windows 2003 EE
Il DB server piu’ corposo (S1) ha una 20ina di database e pesa 180 gb circa.
L’altro DB server (S2) circa un quinto del primo.
Le due macchine hanno indirizzamento pubblico su lan distinte (dietro fw) ma hanno una seconda scheda di rete che le attesta sulla stessa 192.168.1.x
O_o 180GB di database mysql?
Dipende quindi dal tempo di disservizio che puoi avere.
per non avere disservizio io mi comprerei un terzo server (anche un server linux che con 300 euro te la cavi) su cui impostare la replica.
Altrimenti se vuoi impostare una esportazione con mysqldump ogni 4 ore
C:\Program Files\MySQL\MySQL Server 5.0\bin”\mysqldump -uBackup -pBackup %db% >Z:\backup\%db%.sql
poi dall’altro server la ricarichi quando vuoi con il comando mysql -uroot -ppassword < Z:\backup\%db%.sql all’occorrenza.
180GB di database sono veramente tanti e per fare un’esportazione ci potrebbe impiegare anche un 4 ore (dipende dai dischi)
Fammi sapere!