Introduzione
Con il seguente tutorial vediamo come replicare un database Mariadb da un server Ubuntu 16.04 ad un altro server che nel mio caso è situato in un altro edificio, a questo scopo avevo creato precedentemente un tunnel con openVPN tra i due server per non esporre i servizi verso l'esterno, a questo proposito vi rimando al precedente tutorial che si trova qui
Se invece nel vostro caso i server si dovessero trovare nella stessa rete non sarà necessario.
I motivi per replicare un database possono essere molteplici, dalla necessità di avere una copia di backup alla possibilità di bilanciare il carico tra due server, in modalità master/slave solo ciò che verrà inserito nel master si replicherà sullo slave.
Prerequisiti
Due server ubuntu 16.04 LTS con MariaDB già installati e configurati
Accesso root ai due server
Server Master: 10.8.0.6
Server Slave: 10.8.0.1
Configurazione Master
In entrambe le macchine dovremo modificare il file di configurazione di MariaDB affinche si metta in ascolto su tutte le interfacce, di default resta in ascolto solo su localhost.
Modifichiamo il file con:
nano /etc/mysql/mariadb.conf.d/mysqld.cnf
cerchiamo e modifichiamo il parametro bind-address = 127.0.0.1 come segue:
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address = 0.0.0.0
Più in basso cerchiamo e decommentiamo i seguenti parametri come mostrato di seguito:
#
# The following can be used as easy to replay backup logs or for replication.
# note: if you are setting up a replication slave, see README.Debian about
# other settings you may need to change.
server-id = 1
log_bin = /var/log/mysql/mysql-bin.log
....
binlog_do_db = nomedeldatabasechevoletereplicare
Dopo aver effettuato queste modifiche salvate e riavviate il servizio mysql con il seguente comando:
service mysql restart
Il resto della configurazione va effettuata nella shel di mysql percui procediamo come segue:
mysql -u root -p
Innanzitutto dobbiamo creare un utente abilitato a inviare le informazioni tra un server e l'altro e concedergli i permessi di replica. Sostituite il nome utente 'replicator' e la password 'password' in base alle vostre esigenze. Non sarà possibile concedere i permessi a solo un database specifico ma la limitazione è comunque impostata nel file di configurazione precedentemente modificato e dichiarato col parametro binlog_do_db=nomedeldatabasechevoletereplicare
create user 'replicatore'@'%' identified by 'password';
GRANT REPLICATION SLAVE ON *.* TO 'replicator'@'%' IDENTIFIED BY 'password';
FLUSH PRIVILEGES;
A questo punto ci servono alcune informazioni che andranno impostate sul server slave, digitiamo quanto segue sempre nella shell di mysql:
SHOW MASTER STATUS;
dovrebbe comparirvi una tabella simile a questa, i parametri che ci servono sono File e Position, annotateveli da qualche parte
MariaDB [(none)]> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 | 495655 | example | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
Usciamo dalla shell di mysql e procediamo con un backup del database che vogliamo replicare
QUIT;
mysqldump -u root -p --opt nomedeldatabasedareplicare > backup.sql
Fate attenzione a non invertire in questa fase la procedura, Prima si guarda la posizione del master e dopo si fa il dump e non viceversa
Sarà necessario copiare il file di backup appena creato nel server di destinazione per lo scopo si può utilizzare ad esempio scp o via ftp o con una chiavetta o come preferite
esempio:
scp backup.sql nomeutente@10.8.0.1:/home/nomeutente
Configurazione Slave
Il procedimento da seguire per il server slave è molto simile al precedente ma prima di tutto creiamo e importiamo il database di cui abbiamo fatto il backup in precedenza
mysql -u root -p
CREATE DATABASE nomedatabasedareplicare;
EXIT;
mysql -u root -p nomedatabasedareplicare < /path/to/backup.sql
Ora procediamo a modificare il file di configurazione di mariadb
nano /etc/mysql/mariadb.conf.d/50-server.cnf
Cerchiamo decommentiamo e modifichiamo come segue, server-id dovrà avere un numero diverso dal precedente, va aggiunta la riga relay-log :
server-id = 2
relay-log = /var/log/mysql/mysql-relay-bin.log
log_bin = /var/log/mysql/mysql-bin.log
binlog_do_db = nomedatabasedareplicare
Riavviamo mysql anche nel server slave:
sudo service mysql restart
Il prossimo passo sarà quello di abilitare la replica concedendo l'autorizzazione all'utente che abbiamo creato sul server master, il tutto da shell mysql. Fate attenzione qui a modificare MASTER_LOG_FILE='mysql-bin.000001', e MASTER_LOG_POS= 107 con i dati estrapolati in precedenza dalla tabella e che vi avevo detto di prendere nota.Se non lo avete fatto ricordo che prima si guarda con SHOW MASTER STATUS; e poi eventualmente rifate il dump del database dal master e ricaricatelo sullo slave. Dopodichè siamo pronti ad aviare lo scambio dati
mysql -u root -p
CHANGE MASTER TO MASTER_HOST='10.8.0.6',MASTER_USER='replicatore', MASTER_PASSWORD='password', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS= 107;
START SLAVE;
SHOW SLAVE STATUS;
QUIT;
Se tutto e' andato a buon fine lo stato della replica dovrebbe risultare come segue:
Slave_IO_State: Waiting for master to send event
Master_Host: 10.8.0.6
.......
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
In caso di problemi si rimanda sempre a /var/log/syslog controllate che non ci siano messaggi di errore loggati che potrebbero aiutarvi a risolvere il problema,
Se per qualche motivo avete sbagliato il comando "CHANGE MASTER TO MASTER..." potete resettare le impostazioni con RESET SLAVE;
Controllo generale
Come controllare il Master
SHOW MASTER STATUS;
Se il master e' attivo viene restituita una tabella con una riga contenente gli estremi della posizione attuale sul binlog
Come controllare lo slave:
Con il comando SHOW SLAVE STATUS\G
controllare i due processi di slave:
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Come resettare le impostazioni sullo slave:
RESET SLAVE;
Alcuni comandi utili
backup database:
mysqldump -u user -p db_da_copiare > backup.sql
Backup di database multipli:
mysqldump -u user -p --databases dbuno dbdue dbtre > backup_di_tre_db.sql
Backup di tutti i database:
mysqldump -u user -p --all-databases > backup_tutti_i_db.sql
Backup di una tabella
mysqldump -u user -p db_da_copiare nome_tabella > backup.sql
Backup di tabelle multiple
mysqldump -u user -p db_da_copiare nome_tabella1 nome_tabella2 nome_tabella3 > backup.sql
Backup database con compressione gzip
mysqldump -u user -p db_da_copiare | gzip -9 > backup.sql.gz
Ripristino di uno o più database :
mysql -u user -p < backup.sql
Ripristino di un database estratto da un backup di più database:
mysql -u user -p --one-database nome_del_db < backup_tutti_i_db.sql
Ripristino di un backup di uno o più database compresso con gzip
gunzip < backup.sql.gz | mysql -u user -p
Informazioni Aggiuntive
Se notate nel log messaggi di avvisto tipo [Warning] Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT.
provate a procedere come segue:
Nella console mysql dello slave: STOP SLAVE;
Spostatevi nella console mysql del Master: FLUSH TABLES WITHREAD LOCK; FLUSH LOGS;SET GLOBAL binlog_format ='MIXED'; FLUSH LOGS; UNLOCK TABLES;
Ritornate nella console mysql dello slave: START SLAVE;
andate nel file di configurazione di mariadb /etc/mysql/mariadb.conf.d/50-server editatelo e aggiungete o modificate la seguente impostazione: binlog_format=MIXED