MYSQL REPLICATION

How-to set-up a Master-Slave configuration for 2 MySQL servers

Introduzione

Il MySQL database di bookeeping di BaBar e' soggetto a carico spesso pesante e prolungato.
Da una parte traccia tutte le operazioni di import da SLAC (e distribuzione sui sistemi di storage locali) dei dati;
dall'altra serve l'utenza che necessita di usare gli user tools di bookeeping.

E' necessaria una copia del database da cui ripartire qualora il server principale dovesse avere qualche problema, p.es. a livello hardware.
Si osservi che la replicazione non si presta utilmente ad una politica di backup poiche' un comando di DELETE viene replicato rapidamente sullo slave; invece la replicazione rappresenta la protezione contro fallimenti hardware.

Avere una replica del database senza aggiungere un processo in esecuzione sul server principale (che chiamiamo 'master') che sarebbe pesante
e lungo e' reso possibile dalla replicazione su altro server ('slave') la quale e' una funzionalita' nativa di MySQL.

La replicazione utilizza il paradigma dello "Shared Nothing" che prevede su uno (o piu') server una copia locale del DB presente sul master.
Avere piu' slave server e' possibile a partire da mysql 4.1; noi usiamo una 3.x particolarmente stabile e comunque ci basta avere un solo slave.
La replica e' asincrona (non e' istantanea e non vi e' la certezza che avvenga in un tempo prestabilito). Una stima rozza del tempo di sincronia indicherebbe intervalli da 0.1sec al sec a seconda dell'ambiente operativo.

Nel seguito partiamo dalla presenza di un database MySql attivo e funzionante sul master e dalla disponibilita' di uno slave gia' configurato per la replica che supponiamo per problemi esterni (p.es. hardware) si sia disallineato.

Discuteremo sia la configurazione dei server come master e slave che le modalita' con cui fare ripartire la replicazione sullo slave. Alla fine risultera' ovvio come ricaricare il database dallo slave su un master problematico.

Configurazione dei server master e slave

La directory principale e' /sql, al cui interno troviamo la sotto-directory databases piu' un po' di snapshot files .tar
(che invece tipicamente ci si aspetta di trovare in /var/lib/mysql):
drwx------ 2 root root 16384 Oct 26 2005 lost+found
-rw-r--r-- 1 mysql mysql 49582080 Nov 9 2005 batch-snapshot.tar
-rw-r--r-- 1 mysql mysql 1499678720 Nov 9 2005 bbkr14-snapshot.tar
-rw-r--r-- 1 mysql mysql 622766080 Nov 9 2005 bbkr18-snapshot.tar
-rw-r--r-- 1 mysql mysql 10240 Nov 9 2005 test-snapshot.tar
-rw-r--r-- 1 mysql mysql 51200 Nov 9 2005 import-snapshot.tar
-rw-r--r-- 1 mysql mysql 30720 Nov 9 2005 condupd-snapshot.tar
-rw-r--r-- 1 mysql mysql 3752314880 Nov 9 2005 bbrora-snapshot.tar
-rw-r--r-- 1 mysql mysql 81920 Nov 9 2005 mysql-snapshot.tar
drwxr-xr-x 10 mysql mysql 4096 Nov 9 2005 databases
Dentro la directory /sql/databases si trovano i vari databases:
drwx------ 2 mysql mysql 4096 Nov 9 2005 batch
drwx------ 2 mysql mysql 4096 Nov 9 2005 bbkr14
drwx------ 2 mysql mysql 4096 Nov 9 2005 bbrora
drwx------ 2 mysql mysql 4096 Nov 9 2005 condupd
drwx------ 2 mysql mysql 4096 Nov 9 2005 mysql
drwx------ 2 mysql mysql 4096 Nov 10 2005 test
drwx------ 2 mysql mysql 4096 Jan 27 2006 import
drwx------ 2 mysql mysql 4096 Mar 6 12:06 bbkr18
-rw-rw---- 1 mysql mysql 85 May 4 12:39 master.info
I DB ufficiali del bookeeping di BaBar sono bbkr14 e bbkr18;
poi c'e' il DB bbrora con la tabella colldist e
il DB import con le tabelle destination, job, morejob, skim e moreskim.
Il DB mysql e' l'archivio di sistema.
Si noti anche la presenza del file master.info (siamo sullo slave) di cui parleremo dopo.
Se si va sul master troveremo nella stessa directory, oltre ai databases, anche i file di log
(necessari alla replicazione) in cui confluiscono tutte le operazioni che hanno alterato la base dei dati:
[...]
-rw-rw---- 1 mysql mysql 21047029 Jul 16 04:02 master-binlog.082
-rw-rw---- 1 mysql mysql 116 Jul 23 04:02 master-binlog.085
-rw-rw---- 1 mysql mysql 34160056 Jul 23 04:02 master-binlog.084
-rw-rw---- 1 mysql mysql 1740 Jul 30 04:02 master-binlog.index
-rw-rw---- 1 mysql mysql 116 Jul 30 04:02 master-binlog.087
-rw-rw---- 1 mysql mysql 27876896 Jul 30 04:02 master-binlog.086
-rw-rw---- 1 mysql mysql 1931392 Aug 3 06:22 master-binlog.088
Sul master e' presente il file master-binlog.index che semplicemente lista i log file.
Il file di configurazione del server mysql, my.cnf, si trova posizionato sotto /etc
(mentre tipicamente lo si trova in /etc/mysql).
La versione di /etc/my.cnf sul master e':
# Example mysql config file for very large systems.
#
# This is for large system with memory of 1G-2G where the system runs mainly MySQL.
# Here follows entries for some specific programs
# The MySQL server
[mysqld]
port = 3306
socket = /var/lib/mysql/mysql.sock
datadir = /sql/databases
skip-locking
set-variable = key_buffer=384M
set-variable = max_allowed_packet=24M
set-variable = table_cache=512
set-variable = sort_buffer=2M
set-variable = record_buffer=2M
set-variable = thread_cache=8
set-variable = tmp_table_size=500M
set-variable = thread_concurrency=8
set-variable = myisam_sort_buffer_size=64M
log-bin = master-binlog
server-id = 1
[mysql.server]
user=mysql
basedir=/var/lib
[safe_mysqld]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
[mysqldump]
quick
set-variable = max_allowed_packet=24M
[mysql]
no-auto-rehash
[isamchk]
set-variable = key_buffer=256M
set-variable = sort_buffer=256M
set-variable = read_buffer=2M
set-variable = write_buffer=2M
[myisamchk]
set-variable = key_buffer=256M
set-variable = sort_buffer=256M
set-variable = read_buffer=2M
set-variable = write_buffer=2M
[mysqlhotcopy]
interactive-timeout
Si noti come venga attivata l'istanza di creazione dei log file indispensabili affinche' le istruzioni SQL vengano propagate allo slave.
Si noti anche che la porta su cui il master e' in ascolto e' quella standard (3306).
La versione di /etc/my.cnf sullo slave e':
[mysqld]
master-host=bbr-sqlmaster.cr.cnaf.infn.it
master-user=[replication username]
master-password=[replication password]
server-id=2
datadir=/sql/databases
socket=/var/lib/mysql/mysql.sock
master-port=3306
[mysql.server]
user=mysql
basedir=/var/lib
[safe_mysqld]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
Come anche per il master e' fondamentale assegnare un valore che sia unico ed univoco alla direttiva server-id.
Vengono poi attivate le direttive per la replicazione; vengono infatti forniti
1) l'indirizzo del master
2) la porta su cui il master e' in ascolto
3) username e password dell'apposito utente sul master per il task di replicazione;
lo slave si presenta al master autenticandosi come tale utente (affinche' vengano accettate le connessioni di replica).
Tale utente deve disporre dei privilegi di "replication slave".
mysql> GRANT REPLICATION SLAVE ON . TO 'bbrrepl'@'localhost' IDENTIFIED BY '[...]';
mysql> FLUSH PRIVILEGES; [necessario??]
ed evidentemente, in precedenza, sul master e' stato creato l'utente con un comando di scrittura nella tabella 'user' del DB mysql:
mysql> use mysql;
mysql> insert into user values('bbr-sqlmaster.cr.cnaf.infn.it','bbrrepl',PASSWORD('[password]'),'N','N','N','N','N','N','N','N','N','Y','N','N','N','N');
Guardando la tabella con
mysql> select * from user;
si evince che "root" e' utente sul localhost, mentre "bbrrepl" e' utente sia sul master che sullo slave (si avra' avuto cura di eseguire questi ultimi comandi sia sul master che sullo slave anche se in realta' non e' strettamente necessario che l'utenza di replicazione sia creata anche sullo slave).
Poiche' username e password sono indicate in chiaro e' cruciale controllare che il file my.cnf sia leggibile solo da root e dall'utente mysql:
[root@bbr-sqlslave etc]# chmod 640 my.cnf
[root@bbr-sqlslave etc]# chown mysql my.cnf
Bisogna aggiungere che affinche' la replicazione funzioni bisogna anche garantire che l'ownership di master-binlog.index sia corretta:
[root@bbr-sqlmaster etc]# chown mysql:mysql /sql/databases/master-binlog.index
Si noti anche che nel my.cnf sullo slave bisognrebbe aggiungere anche la riga:
master-connect-retry=60

Utenti e processi

A parte l'utente 'bbrrepl', sul master (ed in genere su ogni mysql server) si trovano i seguenti 2 processi, uno di root ed uno dell'utente mysql (utente proprietario del processo MYSQL):

[root@bbr-sqlmaster root]# ps -aux
[...]
root 3522 0.0 0.0 4200 1096 ? S May04 0:00 /bin/sh /usr/bin/safe_mysqld --defaults-file=/etc/my.cnf --pid-file=/var/run/mysqld/mysqld.pid
mysql 3554 2.7 19.4 408184 399684 ? S May04 3938:13 /usr/libexec/mysqld --defaults-file=/etc/my.cnf --basedir=/usr --datadir=/sql/databases --user=mysql --pid-file=/var/run/mysqld/mysqld.pid --skip-locking
[...]

Si noti che il comando safe_mysqld eseguito come root e' quello che inizia il demone mysqld sul server Unix/Linux.

Si noti anche come l'utente mysql sia proprietario del processo 3554, che e' il demone di mysql come si evince esplicitamente da:
[root@bbr-sqlmaster root]# service mysqld status
mysqld (pid 3554) is running...

Comunque e' root quello che fa partire il demone; p.es. se fosse spento si farebbe:
[root@bbr-sqlmaster root]# service mysqld (re)start
oppure [root@bbr-sqlmaster root]# /etc/init.d/mysql (re)start
mentre se volessi stoppare il processo:
[root@bbr-sqlmaster root]# mysqladmin -u root -p shutdown
comando di shutdown dato attraverso l'utility mysqladmin usando root come utente di login (per il quale ci viene chiesta la password).

A questo proposito si noti che per ottenere il prompt di mysql e dare comandi in linea tipici di amministrazione del DB si fa:
[root@bbr-sqlmaster root]# mysql -u root -p
dove root e' ancora l'utente per loggarsi nel DB.
Ovviamente qui la password non e' quella di root sul sistema ma quella di root per loggarsi nel DB.
Una volta data la password entriamo sulla shell di MySQL:
[root@bbr-sqlmaster root]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 352645 to server version: 3.23.58-log
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql>

Si noti che viene ricordato come i comandi in linea vanno terminati con un ';' o un '\g'.

Vediamo ora qualche utile comando inerente la replicazione:

ma anche (si noti la differenza fra 'g' e 'G'):
mysql> show processlist \G
*************************** 1. row ***************************
Id: 352655
User: root
Host: localhost
db: NULL
Command: Query
Time: 0
State: NULL
Info: show processlist
1 row in set (0.00 sec)

mysql> exit [oppure 'quit']
Bye

Introduzione ai vari metodi di replicazione

Vi sono almeno 3 metodi di replicazione:

1) DATABASE DUMP

Dopo aver messo in stato di "locked" le tabelle dei DB
mysql> FLUSH TABLES WITH READ LOCK;
e preso nota del 'position number' relativo al binlog corrente mediante
mysql> SHOW MASTER STATUS;

si usa il comando mysqldump sul master:
mysqldump -u root -p --databases nomeDB [--lock-tables=false] > nomeDB.sql
il quale crea un SQL dump di nomeDB nel file nomeDB.sql che va trasferito sullo slave server.
L'opzione in [...] dovrebbe evitare che mysqldump possa tentare di applicare un ulteriore lock facendo durare il processo all'infinito.

Infine si rilascia il DB sul master:
mysql> UNLOCK TABLES;

Sullo slave col demone "spento" (altrimenti si fa mysql> SLAVE STOP;) (e suppongo gia' presente il nomeDB) si va a "caricare" il dump:
mysql -u root -p nomeDB < /path/to/nomeDB.sql

Infine caricare gli opportuni valori del master sullo slave (che in parte dovrebbe pero' gia' prendersi dal my.cnf):
mysql> CHANGE MASTER TO MASTER_HOST='[...]', MASTER_USER='[...]', MASTER_PASSWORD='[...]', MASTER_LOG_FILE ='[...]', MASTER_LOG_POS='[...]';
dove le ultime 2 informazioni si sono prese in precedenza facendo 'SHOW MASTER STATUS' sul master.

Infine si fa partire lo slave:
mysql> START SLAVE;

2) LOADING DATA FROM MASTER

Tutto simile al metodo precedente se non per il fatto che non si produce un dump sul master ma si esegue sullo slave il comando:
mysql> LOAD DATA FROM MASTER;

Lo svantaggio di questo metodo sta nel fatto che il DB e' "locked" durante tale operazione
il che puo' pesare se i DB in gioco sono molto grandi (il dump e' fatto in locale, il loading attraverso la rete).

3) DATABASE SNAPSHOT

Avendo locked i vari DB o avendo spento il demone sul master, si fa uno snapshot (cioe' un tarball) di tutta la directory
di dati mysql o solo di alcuni DB di interesse. Il tarball viene copiato e spacchettato sullo slave.

Noi preferiamo fare uno snapshot del master e copiarlo sullo slave; di questo metodo forniamo tutti i dettagli.

Metodo di replicazione preferito in dettaglio

Tipicamente si inizia con:
mysql> FLUSH TABLES WITH READ LOCK;
e si prende nota del 'position number' relativo al binlog corrente mediante
mysql> SHOW MASTER STATUS;
poiche' si tratta delle coordinate di replica a partire dalle quali lo slave dovra' iniziare a processare gli update presi dal master.
Si noti che se il master non fosse abilitato a creare i bin-log le coordinate di default da dare allo slave sarebbero ('',4).

Si noti che per mantenere il lock attivo NON bisogna chiudere il mysql client cioe' la shell in cui abbiamo istanziato il lock!

In alternativa o, addirittura meglio, in aggiunta (!), si puo' spegnere proprio il servizio sul master (noi possiamo anche fare un breve outage) comandandolo preferibilmente mediante:
[root@bbr-sqlmaster root]# mysqladmin -u root -p shutdown
Se lo si fa in aggiunta (preferibile) siamo sicuri che il server va giu' con gli snapshot corrispondenti al file di log e alla posizione correnti.
Il fatto che l'outage sia preferibile deriva dal fatto che cosi' facendo la replicazione funzionera' per tutti i tipi di tabelle, sia MyISAM che InnoDB.

A questo punto, per ogni DB in /sql/databases/ si eseguono sul master comandi del tipo
[root@bbr-sqlmaster databases]# tar -cvf 7sql/bbkr14-snapshot.tar ./bbkr14
e cosi' via (dopo aver pulito la directory /sql dei vecchi tarball precedenti).

Poi si copiano i file tarball sullo slave:
[root@bbr-sqlmaster sql]# scp *.tar bbr-sqlslave:/sql/databases/
(o direttamente in /tmp se non e' troppo piccola)
da spacchettare sullo slave:
[root@bbr-sqlslave databases]# tar -xvf bbkr14-snapshot.tar
e cosi' via per gli altri tarball
(oppure in /tmp da dove poi copiarli in /sql/databases).

Bisogna accertarsi che i DB risultanti siano di mysql:
[root@bbr-sqlslave databases]# chown mysql:mysql *.tar

Possiamo replicare anche il DB mysql avendo sullo slave lo stesso set di utenti;
non dobbiamo invece includere nella replica file come master.info e relay-log.info.

Adesso e' possibile riabilitare l'attivita' di scrittura sui DB:
mysql> UNLOCK TABLES;
oppure riportare su il servizio mysql(se precedentemente spento):
[root@bbr-sqlmaster root]# service mysqld start

Infine bisogna pulire i log-bin prima di iniziare la replicazione:
mysql> RESET MASTER;

tale istruzione va eseguita necessariamente pena il verificarsi di una serie di errori nella replicazione.
Non ho voluto verificare cosa accada di rovinoso; una volta pero' dato il comando bisogna "lockare" di nuovo tutto e rileggere file e posizione.

A questo punto il servizio deve essere fatto partire anche sullo slave:
[root@bbr-sqlslave root]# service mysqld start

Inoltre al fine di poter caricare gli opportuni valori del master sullo slave (che in parte dovrebbe pero' gia' prendersi dal my.cnf):
mysql> CHANGE MASTER TO MASTER_HOST='[...]', MASTER_USER='[...]', MASTER_PASSWORD='[...]', MASTER_LOG_FILE ='[master-binlog.xxx]', MASTER_LOG_POS=[#];
dove le ultime 2 informazioni si sono prese in precedenza facendo 'SHOW MASTER STATUS' sul master. Dovrebbe bastare cambiare gli ultimi 2 parametri.

Dopodiche' si fa partire la replicazione sullo slave:
mysql> SLAVE START;
In realta' risulta superfluo avendo fatto lo start del servizio.

CONTROLLO DELLA REPLICAZIONE

E' il caso di verificare subito che master e slave siano allineati facendo 'SHOW MASTER[SLAVE] STATUS' sul master[slave]
e controllando che la 'position' sia la stessa (oltre al medesimo binlog file).
Lo stato della variabile Slave_Running che deve essere 'YES'.
Nella 5.1 le variabili interessate sono Slave_IO_Running e Slave_SQL_Running (il primo parametro indica che lo slave e' attivo, il secondo che esegue la replica); se pero' vi sono dei problemi bisogna anche verificare Slave_IO_State per conoscere lo stato del server slave.

Eventuali errori, se la replicazione non funziona per qualche ragione, possono essere debuggati in /var/log/mysqld.log sullo slave.

Una volta che sullo slave e' attivata la replicazione, lo slave usa due file, master.info e relay-log.info,
al fine di tener traccia quanto del binary log corrente (del master) e' stato processato.
Essi non vanno rimossi e i parametri vanno modificati mediante il comando CHANGE MASTER.
Si noti che il contenuto di master.info ignora ("overrides") alcune delle linee di my.cnf. Anche in questo file la password dell'utente di replicazione e' in chiaro quindi bisogna verificare la correttezza dei diritti per sicurezza.

NOTA BENE: Nel caso bisogna far riallineare lo slave su cui la replicazione e' fallita, p.es. nello stato "Waiting to reconnect after a failed read", errore in seguito al quale lo slave va fuori sincronia (il master.info segna master-binlog.052, mentre il master ormai e' allo 090), bisogna ricordarsi di fare:
mysql> SLAVE STOP;
shutdown del server
eventuale [non mi sembra necessaria] cancellazione del file master.info

COME FUNZIONA LA REPLICAZIONE: 3 THREAD

In breve si puo' dire che vi sono 3 thread:

1) sullo slave; fa I/O col master, per capire cosa c'e' da fare per la replicazione;
2) sul master; fa il "dump" dei file bin-log;
3) sullo slave; legge e copia i dati; a tal fine usa i relay-log.info file che vengono cancellati automaticamente
(in caso di necessaria cancellazione manuale si usa FLUSH LOGS)

Con il comando SHOW PROCESSLIST si vede lo stato dei thread:
(1) e (3) sullo slave;
(2) sul master.

Bibliografia:
http://dev.mysql.com/doc/refman/4.1/en/replication-howto.html
"La replicazione nativa di MySQL: sicurezza e performance", Linux&C, Anno 8, Numero 53.
http://www.howtoforge.com/mysql_database_replication/

Autore: Alexis Pompili (agosto-2006). Crediti sono dovuti ad A.Crescente ed E.Antonioli per i loro suggerimenti.