Come configurare una replica di database MySQL su Ubuntu 18.04

Introduzione

La replica di MySQL è un processo che consente di copiare automaticamente i dati da un server di database a uno o più server.

MySQL supporta una serie di topologie di replica con la topologia Master/Slave che è una delle topologie più note in cui un server di database funge da master, mentre uno o più server fungono da slave. Per impostazione predefinita, la replica è asincrona in cui il master invia eventi che descrivono le modifiche al database nel suo registro binario e gli slave richiedono gli eventi quando sono pronti.

Questo tutorial copre un esempio di base della replica Master/Slave di MySQL con un server master e uno slave su Ubuntu 18.04. Lo stesso vale per MariaDB.

Questo tipo di topologia di replica è più adatto per la distribuzione di repliche di lettura per il ridimensionamento della lettura, il backup di database live per il ripristino di emergenza e per i lavori di analisi.

Prerequisiti

Questo esempio presuppone che tu abbia due server che eseguono Ubuntu 18.04, che possono comunicare tra loro su una rete privata. Se il provider di hosting non offre indirizzi IP privati, è possibile utilizzare gli indirizzi IP pubblici e configurare il firewall per consentire il traffico sulla porta 3306 solo da fonti attendibili.

I server in questo esempio hanno i seguenti IP:

Master IP: 192.168.333.333

Slave IP:  192.168.444.444

Installare MySQL

L'impostazione predefinita I repository Ubuntu 18.04 includono MySQL versione 5.7. Per evitare problemi, è meglio installare la stessa versione di MySQL su entrambi i server.

Se vuoi impostare una password per il login al server MySQL, quindi cambiare il metodo di autenticazione, leggi questa guida: Come installare MySQL su Ubuntu 18.04, altrimenti continua a leggere per installare MySQL con le configurazioni di default.

Installa MySQL sul server Master (il server in cui è situato il primo database):

sudo apt-get update

sudo apt-get install mysql-server

Installa MySQL sul server Slave (il server in cui è situato il secondo database) usando gli stessi comandi:

sudo apt-get update

sudo apt-get install mysql-server

Configurare il server Master

Il primo passo è configurare il server MySQL principale. Apporteremo le seguenti modifiche:

• Impostare il server MySQL per l'ascolto sull'IP privato.
• Impostare un ID server univoco.
• Abilitare la registrazione binaria

Per fare ciò aprire il file di configurazione e decommentare MySQL o impostare quanto segue:

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

Cerca la riga bind-address = 127.0.0.1 e modificala inserendo l'indirizzo IP del server Master. Aggiungi anche le righe server-id e log_bin. Nel nostro esempio:

bind-address           = 192.168.333.333
server-id              = 1
log_bin                = /var/log/mysql/mysql-bin.log

Salva e chiudi il file.

Al termine, riavviare il servizio MySQL per rendere effettive le modifiche:

sudo systemctl restart mysql

Il passaggio successivo è creare un nuovo utente di replica. Accedi al server MySQL come utente root digitando:

sudo mysql

Oppure, se hai impostato una password per l'utente root, utilizza il seguente comando per accedere al server MySQL:

sudo mysql -u root -p

Dall'interno del prompt di MySQL, eseguire le seguenti query SQL che creeranno l'utente replica e garantiranno il privilegio REPLICATION SLAVE all'utente:

CREATE USER 'replica'@'192.168.444.444' IDENTIFIED BY 'replica_password';

GRANT REPLICATION SLAVE ON *.* TO 'replica'@'192.168.444.444';

Assicurati di cambiare l'IP con il tuo indirizzo IP slave. Inoltre puoi nominare l'utente come desideri.

Mentre sei ancora all'interno del prompt di MySQL, esegui il seguente comando che stamperà il nome file e la posizione binari.

SHOW MASTER STATUS\G

*************************** 1. row ***************************
             File: mysql-bin.000002
         Position: 625
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 
1 row in set (0.00 sec)

Prendi nota del nome del file, "mysql-bin.000002" e della posizione "625" . Questi valori saranno necessari durante la configurazione del server slave. Questi valori saranno probabilmente diversi sul tuo server.

Configurare il server Slave

Come per il server master sopra, apporteremo le seguenti modifiche al server slave:

• Impostare il server MySQL per l'ascolto sull'IP privato
• Impostare un ID server univoco
• Abilitare la registrazione binaria

Apri il file di configurazione di MySQL e modifica le seguenti righe:

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

Cerca la riga bind-address = 127.0.0.1 e modificala inserendo l'indirizzo IP del server Slave. Aggiungi anche le righe server-id e log_bin. Nel nostro esempio:

bind-address           = 192.168.444.444
server-id              = 2
log_bin                = /var/log/mysql/mysql-bin.log

Salva e chiudi il file.

Riavvia il servizio MySQL:

sudo systemctl restart mysql

Il prossimo passo è configurare i parametri che il server slave utilizzerà per connettersi al server master. Accedi alla shell MySQL:

sudo mysql

Oppure, se hai impostato una password per l'utente root, utilizza il seguente comando per accedere al server MySQL:

sudo mysql -u root -p

Innanzitutto, ferma i thread degli slave:

STOP SLAVE;

Eseguire la query seguente che imposterà lo slave per replicare il master:

CHANGE MASTER TO
MASTER_HOST='192.168.333.333',
MASTER_USER='replica',
MASTER_PASSWORD='replica_password',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=629;

Assicurati di utilizzare l'indirizzo IP, il nome utente e la password corretti. Il nome e la posizione del file di registro devono essere uguali ai valori ottenuti dal server Master.

Al termine, avviare i thread slave.

START SLAVE;

Provare la configurazione

A questo punto, dovresti avere una configurazione di replica Master/Slave funzionante.

Per verificare che tutto funzioni come previsto, creeremo un nuovo database sul server principale:

sudo mysql

CREATE DATABASE replicatest;

Accedi alla shell MySQL slave:

sudo mysql

Eseguire il comando seguente per elencare tutti i database:

SHOW DATABASES;

Noterai che il database che hai creato sul server principale è replicato sullo slave:

+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| replicatest        |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

Conclusione

In questo tutorial, ti abbiamo mostrato come creare una replica Master/Slave MySQL.