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