Configurare lo slave MySQL per la replica sicura

Scopo e termini rapidi

• Scopo: trasformare server2 in uno slave che replica exampledb da server1.
• Definizione breve: master è il server sorgente; slave è il server che riceve le modifiche.

Requisiti prima di cominciare

• Accesso root su server2 (e accesso amministrativo sul master per ottenere SHOW MASTER STATUS).
• Snapshot SQL esportato dal master (snapshot.sql).
• File di certificati SSL copiati su /etc/mysql/newcerts/ (se usi SSL).

1. Modifica del file di configurazione dello slave

Apri /etc/mysql/my.cnf sul server2 e assicurati che nella sezione [mysqld] siano presenti le seguenti impostazioni. L’esempio assume server-id=2 per il secondo server.

server2:

vi /etc/mysql/my.cnf

[...]
server-id=2
master-connect-retry=60
replicate-do-db=exampledb
[...]

Importante: il valore di server-id deve essere univoco e diverso da quello del master.

Riavvia MySQL dopo la modifica:

/etc/init.d/mysql restart

2. Creare il database vuoto sullo slave e importare lo snapshot

Crea un database vuoto exampledb su server2:

mysql -u root -p

CREATE DATABASE exampledb;  
quit;

Sopra stiamo solo creando la struttura vuota. Ora fermiamo il demone slave (se presente) e importiamo lo snapshot SQL:

/usr/bin/mysqladmin --user=root --password=yourrootsqlpassword stop-slave  
cd /tmp  
mysql -u root -pyourrootsqlpassword exampledb < snapshot.sql

Nota: sostituisci yourrootsqlpassword con la password reale del root MySQL.

3. Impostare il master e abilitare la replica

Riconnettiti a MySQL e configura i parametri del master. È cruciale usare i valori restituiti da SHOW MASTER STATUS sul master (MASTER_LOG_FILE e MASTER_LOG_POS).

mysql -u root -p

Esegui il comando CHANGE MASTER TO, sostituendo i valori con quelli corretti:

CHANGE MASTER TO MASTER_HOST='192.168.0.100', MASTER_USER='slave_user', MASTER_PASSWORD='slave_password', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=106, MASTER_SSL=1, MASTER_SSL_CA = '/etc/mysql/newcerts/ca-cert.pem', MASTER_SSL_CERT = '/etc/mysql/newcerts/client-cert.pem', MASTER_SSL_KEY = '/etc/mysql/newcerts/client-key.pem';

Spiegazione dei parametri principali:

• MASTER_HOST: IP o hostname del master (es. 192.168.0.100).
• MASTER_USER / MASTER_PASSWORD: utente con privilegi REPLICATION SLAVE sul master.
• MASTER_LOG_FILE / MASTER_LOG_POS: informazioni ottenute da SHOW MASTER STATUS sul master.
• MASTER_SSL: abilita connessione SSL (1 = sì).
• MASTER_SSL_CA / MASTER_SSL_CERT / MASTER_SSL_KEY: percorsi dei certificati sullo slave.

Poi avvia lo slave:

START SLAVE;

Verifica lo stato:

SHOW SLAVE STATUS \G

È fondamentale che i campi Slave_IO_Running e Slave_SQL_Running siano entrambi Yes. Se usi SSL, verifica inoltre Master_SSL_Allowed, Master_SSL_CA_File, Master_SSL_Cert e Master_SSL_Key.

Esempio di output atteso:

mysql> SHOW SLAVE STATUS \G  
************************* 1. row *************************  
               Slave_IO_State: Waiting for master to send event  
                 Master_Host: 192.168.0.100  
                 Master_User: slave_user  
                 Master_Port: 3306  
               Connect_Retry: 60  
             Master_Log_File: mysql-bin.000001  
         Read_Master_Log_Pos: 106  
              Relay_Log_File: mysqld-relay-bin.000002  
               Relay_Log_Pos: 251  
        Relay_Master_Log_File: mysql-bin.000001  
             Slave_IO_Running: Yes  
           Slave_SQL_Running: Yes  
               Replicate_Do_DB: exampledb  
           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: 106  
              Relay_Log_Space: 407  
              Until_Condition: None  
               Until_Log_File:  
                Until_Log_Pos: 0  
           Master_SSL_Allowed: Yes  
           Master_SSL_CA_File: /etc/mysql/newcerts/ca-cert.pem  
           Master_SSL_CA_Path:  
              Master_SSL_Cert: /etc/mysql/newcerts/client-cert.pem  
            Master_SSL_Cipher:  
               Master_SSL_Key: /etc/mysql/newcerts/client-key.pem  
        Seconds_Behind_Master: 0  
Master_SSL_Verify_Server_Cert: No  
                Last_IO_Errno: 0  
                Last_IO_Error:  
               Last_SQL_Errno: 0  
               Last_SQL_Error:  
1 row in set (0.00 sec)  
  
mysql>

Esci dalla shell MySQL quando hai finito:

quit;

Ora ogni modifica a exampledb sul master verrà replicata sullo slave. Esegui un test per confermare la sincronizzazione.

Controlli rapidi e criteri di accettazione

• Slave_IO_Running = Yes
• Slave_SQL_Running = Yes
• Read_Master_Log_Pos e Exec_Master_Log_Pos avanzano dopo operazioni sul master
• MasterSSL* popolati se MASTER_SSL=1
• Seconds_Behind_Master vicino a 0 in condizioni normali

Checklist operativa (playbook rapido)

• Verificare la versione MySQL compatibile tra master e slave.
• Creare snapshot coerente sul master (lock tables o mysqldump con –master-data).
• Trasferire snapshot e certificati al server2 in modo sicuro.
• Aggiornare /etc/mysql/my.cnf con server-id unico.
• Riavviare MySQL e creare il DB vuoto.
• Importare snapshot e applicare CHANGE MASTER TO con i valori corretti.
• START SLAVE; e validare con SHOW SLAVE STATUS \G.

Troubleshooting: errori comuni e soluzioni

• Slave_IO_Running = No: controlla network, MASTER_HOST, porta 3306, e credenziali MASTER_USER.
• Slave_SQL_Running = No: verifica errori SQL nel campo Last_SQL_Error e correggi righe/indici incompatibili.
• Differenze binlog position: assicurati che lo snapshot importato corrisponda alla posizione MASTER_LOG_FILE/MASTER_LOG_POS usata.
• Errore SSL: verifica i permessi dei file /etc/mysql/newcerts/* e che i certificati corrispondano al master.
• Logged errors: controlla /var/log/syslog e il log error di MySQL per messaggi più dettagliati.

Esempi di comandi utili per debug:

SHOW MASTER STATUS;
SHOW SLAVE STATUS \G
tail -n 200 /var/log/syslog
mysqladmin processlist -u root -p

Quando la replica non è adatta (controesempi)

• Dati altamente sensibili: non usare replica non criptata (sempre SSL o tunnel privato).
• Replica sincrona richiesta: la replica standard è asincrona e può perdere dati in caso di crash del master.
• Repliche con struttura di tabelle diversa: evitare replicate-do-db se schemi divergono.

Alternative e strategie avanzate

• Replica master-master (bidirezionale) per alta disponibilità — usare con attenzione per conflitti.
• Replicazione basata su GTID per gestione più semplice dei failover nelle versioni MySQL moderne.
• Utilizzare strumenti di orchestrazione (MHA, Orchestrator) per automatizzare failover.

Sicurezza e best practice

• Limitare i privilegi dell’utente di replica: GRANT REPLICATION SLAVE only.
• Proteggere i file dei certificati (permessi 600) e usare permessi OS restrittivi.
• Monitorare regolarmente Seconds_Behind_Master e i log di errore.

Test cases / Acceptance tests suggeriti

• Creare una tabella di prova sul master, inserire 100 righe e verificare che compaiano sullo slave.
• Eliminare una riga sul master e verificare la rimozione sullo slave.
• Riavviare il master e confermare che la replica si riconnetta entro il periodo di master-connect-retry.

1-line glossary

• Binlog: log binario delle modifiche sul master usato per la replica.
• Relay log: file sullo slave che memorizza eventi letti dal master.
• MASTER_LOG_POS: posizione nel binlog del master.

Note finali

Importante: conserva sempre una copia sicura dello snapshot e delle chiavi. Testa la replica in ambiente di staging prima di passarla in produzione.

Link utili

• MySQL: http://www.mysql.com/
• Debian: http://www.debian.org/