Configurazione della Replica Asincrona MySQL: Guida Passo-Passo

La replica asincrona MySQL è ancora uno dei blocchi fondamentali più utili in una configurazione di database. Puoi usarla per repliche di lettura, backup più sicuri, copie per report, staging di migrazione e disaster recovery. La parola importante è "asincrona": il sorgente conferma una transazione senza attendere che la replica la applichi. Questo impedisce al sorgente di bloccarsi su ogni scrittura della replica, ma significa anche che la replica può rimanere indietro.

La vecchia terminologia in molti comandi MySQL dice "master" e "slave". Le versioni più recenti di MySQL usano "source" e "replica" in comandi come SHOW REPLICA STATUS e CHANGE REPLICATION SOURCE TO. Potresti ancora vedere i vecchi comandi in sistemi, esempi e script più datati. Questa guida usa source e replica nella spiegazione e mostra prima la forma più recente del comando con note dove la sintassi più vecchia differisce.

L'esempio utilizza due server:

• Sorgente: 192.168.1.100
• Replica: 192.168.1.101
• Utente di replica: repl_user
• Ambito: tutti i database, a meno che non si filtri intenzionalmente

Fai questo prima in un ambiente di test se non hai mai eseguito la procedura prima. La configurazione della replica è semplice quando tutto è pulito. Diventa stressante quando il sorgente è occupato, il dump è incoerente o la replica contiene già dati vecchi.

Prima di Toccare la Configurazione

Conferma le basi:

• Entrambi i server eseguono versioni MySQL compatibili.
• La replica può raggiungere il sorgente sulla porta MySQL, di solito 3306.
• Hai accesso amministrativo a entrambe le istanze MySQL.
• Puoi modificare il file di configurazione MySQL e riavviare MySQL se necessario.
• La replica è vuota o sai esattamente quali dati esistenti devono essere sostituiti.
• Il sorgente ha spazio su disco sufficiente per i log binari.

Sull'host replica, testa l'accesso di rete di base:

nc -vz 192.168.1.100 3306

Se nc non è disponibile, usa telnet o gli strumenti di connettività del tuo provider cloud. Risolvi firewall, gruppi di sicurezza, indirizzi bind e routing prima di configurare la replica. Un utente di replica non servirà a nulla se il percorso TCP è bloccato.

Configurare il Server Sorgente

Il sorgente deve scrivere le modifiche nei log binari. La replica legge quegli eventi e li memorizza nei log relay prima di applicarli.

Modifica il file di configurazione MySQL sul sorgente. Le posizioni comuni sono /etc/mysql/mysql.conf.d/mysqld.cnf, /etc/my.cnf o un file incluso da quei percorsi. Aggiungi o verifica queste impostazioni sotto [mysqld]:

[mysqld]
server-id=1
log-bin=mysql-bin
binlog_format=ROW

server-id deve essere unico su ogni server nella topologia di replica. log-bin abilita la registrazione binaria. binlog_format=ROW è un'impostazione predefinita pratica per la maggior parte delle configurazioni di replica moderne perché registra le modifiche alle righe anziché basarsi sul comportamento di riesecuzione delle istruzioni.

Fai attenzione con binlog-do-db e binlog-ignore-db. Il filtraggio sembra comodo, ma può sorprenderti perché il comportamento basato sulle istruzioni dipende dal database predefinito selezionato dalla sessione. Se hai bisogno di replica filtrata, progettala e testala deliberatamente. Per una prima configurazione affidabile, replica tutto.

Riavvia MySQL sul sorgente:

sudo systemctl restart mysql
# o, su alcuni sistemi
sudo systemctl restart mysqld

Verifica le impostazioni:

SHOW VARIABLES LIKE 'server_id';
SHOW VARIABLES LIKE 'log_bin';
SHOW VARIABLES LIKE 'binlog_format';

log_bin dovrebbe essere ON. server_id dovrebbe essere diverso da zero e unico.

Creare un Utente di Replica

Crea un account dedicato sul sorgente che la replica utilizzerà. Limita l'host all'indirizzo della replica se la tua progettazione di rete lo consente:

CREATE USER 'repl_user'@'192.168.1.101' IDENTIFIED BY 'usa_un_segreto_reale_qui';
GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'192.168.1.101';

Il nome del privilegio è ancora REPLICATION SLAVE nelle concessioni MySQL, anche se la documentazione più recente spesso dice "replica" altrove.

Testa il login dall'host replica:

mysql -h 192.168.1.100 -u repl_user -p

Se fallisce, risolvi autenticazione e rete ora. Le cause comuni sono bind-address sul sorgente, regole firewall, mancata corrispondenza dell'host utente, DNS che risolve a un indirizzo diverso e incompatibilità del plugin di autenticazione con un client più vecchio.

Configurare il Server Replica

Sulla replica, configura un server-id diverso. I log relay sono solitamente abilitati automaticamente per la replica, ma nominarli esplicitamente può rendere le operazioni più chiare:

[mysqld]
server-id=2
relay_log=mysql-relay-bin
read_only=ON

Per una protezione più forte, considera super_read_only=ON dopo la configurazione. read_only non impedisce a ogni account privilegiato di scrivere. super_read_only è più sicuro per le repliche che non dovrebbero mai accettare scritture dell'applicazione, ma potresti doverlo disattivare temporaneamente per determinate attività amministrative.

Riavvia MySQL sulla replica e verifica:

sudo systemctl restart mysql

SHOW VARIABLES LIKE 'server_id';
SHOW VARIABLES LIKE 'read_only';

Effettuare uno Snapshot Iniziale Coerente

La replica deve partire da una copia dei dati che corrisponda a una posizione specifica del log binario. Se lo snapshot e la posizione non corrispondono, la replica potrebbe avviarsi ma essere comunque errata.

Per database pesanti su InnoDB, mysqldump --single-transaction è solitamente il metodo coerente più semplice. Evita un blocco di lettura globale lungo per le tabelle transazionali. Includi --source-data=2 nelle versioni più recenti di MySQL in modo che il dump registri il file e la posizione del log binario del sorgente come riga commentata. Le versioni più vecchie usano --master-data=2.

Esegui questo su un host affidabile che possa connettersi al sorgente:

mysqldump -h 192.168.1.100 -u root -p \
  --all-databases \
  --single-transaction \
  --routines \
  --triggers \
  --events \
  --source-data=2 \
  > source_dump.sql

Se la tua versione MySQL non supporta --source-data, usa:

mysqldump -h 192.168.1.100 -u root -p \
  --all-databases \
  --single-transaction \
  --routines \
  --triggers \
  --events \
  --master-data=2 \
  > source_dump.sql

--single-transaction è sicuro per la coerenza di InnoDB, ma non rende coerenti allo stesso modo le tabelle MyISAM non transazionali. Se hai ancora tabelle MyISAM, pianifica una finestra di manutenzione o usa un altro metodo di backup che ti dia uno snapshot coerente.

Controlla nel dump le coordinate di replica registrate:

grep -m 1 -E "CHANGE (MASTER|REPLICATION SOURCE)" source_dump.sql

Dovresti vedere una riga commentata contenente un file di log binario e una posizione. Conservala. La userai quando punterai la replica al sorgente, a meno che tu non scelga la replica basata su GTID.

Importare lo Snapshot sulla Replica

Trasferisci il dump alla replica usando il tuo normale metodo sicuro:

scp source_dump.sql db-replica:/tmp/source_dump.sql

Sulla replica, assicurati di non scrivere accidentalmente su un dataset esistente simile a quello di produzione. Se questa replica deve essere una copia pulita, elimina e ricrea solo ciò che il tuo piano di migrazione dice di sostituire. Poi importa:

mysql -u root -p < /tmp/source_dump.sql

Per dump grandi, esegui l'import in screen o tmux e controlla lo spazio su disco. Un import fallito perché /var/lib/mysql o /tmp si è riempito fa perdere tempo e può lasciarti con una replica caricata a metà.

Puntare la Replica al Sorgente

Su MySQL 8.0.23 e successivi, usa CHANGE REPLICATION SOURCE TO:

CHANGE REPLICATION SOURCE TO
  SOURCE_HOST='192.168.1.100',
  SOURCE_USER='repl_user',
  SOURCE_PASSWORD='usa_un_segreto_reale_qui',
  SOURCE_LOG_FILE='mysql-bin.000001',
  SOURCE_LOG_POS=1234;

Sostituisci il file e la posizione con i valori del dump. Se stai usando la sintassi più vecchia, l'equivalente è:

CHANGE MASTER TO
  MASTER_HOST='192.168.1.100',
  MASTER_USER='repl_user',
  MASTER_PASSWORD='usa_un_segreto_reale_qui',
  MASTER_LOG_FILE='mysql-bin.000001',
  MASTER_LOG_POS=1234;

Se il tuo ambiente utilizza GTID, la configurazione è diversa. Normalmente configuri la modalità GTID su entrambi i server, ripristini un dump che preserva lo stato GTID e usi SOURCE_AUTO_POSITION=1 invece di un file e una posizione. Non mescolare istruzioni GTID e file-posizione casualmente; scegli un approccio e testalo.

Una Breve Nota sulla Replica GTID

La replica GTID è spesso più facile da gestire dopo la configurazione perché MySQL tiene traccia delle transazioni tramite ID di transazione globali invece di farti gestire manualmente un file di log binario e una posizione. È particolarmente utile durante il failover, i cambiamenti del sorgente e la ricostruzione delle repliche.

Ciò non significa che dovresti attivarla casualmente nel bel mezzo di una migrazione. Entrambi i server necessitano di impostazioni GTID compatibili e il tuo processo di backup o dump deve preservare il corretto stato GTID. Un modello comune è configurare:

[mysqld]
gtid_mode=ON
enforce_gtid_consistency=ON

Poi, dopo aver ripristinato un dump compatibile con GTID, configura la replica con:

CHANGE REPLICATION SOURCE TO
  SOURCE_HOST='192.168.1.100',
  SOURCE_USER='repl_user',
  SOURCE_PASSWORD='usa_un_segreto_reale_qui',
  SOURCE_AUTO_POSITION=1;

Usa questo solo se la cronologia GTID del sorgente e della replica è pulita e compresa. Se non sei sicuro, la replica file-e-posizione è più facile da comprendere per una prima configurazione. La scelta peggiore è mescolare esempi di entrambi gli approcci finché la replica non si avvia, ma la cronologia delle transazioni non è quella che pensi.

Avvia la replica:

START REPLICA;

Sintassi più vecchia:

START SLAVE;

Controlla lo stato:

SHOW REPLICA STATUS\G

Sintassi più vecchia:

SHOW SLAVE STATUS\G

I campi chiave sono:

Campo	Valore sano	Cosa ti dice
Replica_IO_Running	Yes	La replica può connettersi e recuperare eventi del log binario.
Replica_SQL_Running	Yes	La replica può applicare eventi del log relay.
Last_IO_Error	vuoto	Problemi di rete, credenziali o log del sorgente appaiono qui.
Last_SQL_Error	vuoto	Conflitti di dati ed errori di applicazione appaiono qui.
Seconds_Behind_Source	basso o in calo	Un indicatore approssimativo di ritardo.

L'output più vecchio usa Slave_IO_Running, Slave_SQL_Running e Seconds_Behind_Master.

Testare con una Piccola Scrittura

Non dichiarare vittoria dopo che i thread dicono Yes. Crea una piccola tabella di test o inserisci una riga innocua in uno schema di test esistente sul sorgente, poi verifica che appaia sulla replica.

Esempio sul sorgente:

CREATE DATABASE IF NOT EXISTS repl_check;
CREATE TABLE IF NOT EXISTS repl_check.heartbeat (
  id INT PRIMARY KEY,
  checked_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);
REPLACE INTO repl_check.heartbeat (id) VALUES (1);

Sulla replica:

SELECT * FROM repl_check.heartbeat;

Questo rileva errori semplici come puntare al sorgente sbagliato, filtrare il database o usare coordinate obsolete.

Proteggere il Canale di Replica

Se il traffico di replica attraversa una rete non affidabile, richiedi TLS. Anche all'interno di una rete privata, molti team ora preferiscono il traffico di database crittografato perché i confini di rete cambiano nel tempo.

Come minimo, crea l'utente e il canale di replica in modo che le credenziali non siano condivise con gli account dell'applicazione. Per canali basati su TLS, configura i certificati secondo la tua versione MySQL e poi includi le opzioni SSL nella configurazione del sorgente di replica. Le opzioni esatte variano in base alla versione e alla politica dei certificati, ma l'intento è lo stesso: la replica deve verificare di connettersi al sorgente previsto e proteggere le credenziali e le modifiche alle righe in transito.

Mantieni anche il privilegio dell'utente di replica ristretto. Non ha bisogno di ampio accesso DDL o DML. Se qualcuno ottiene quella password, il raggio di esplosione dovrebbe essere limitato alla lettura dei log di replica, non alla scrittura dei dati dell'applicazione.

Problemi Comuni di Configurazione

Se Replica_IO_Running è No, la replica non può recuperare eventi. Controlla:

• SOURCE_HOST è corretto.
• Il sorgente è in ascolto sull'indirizzo e sulla porta previsti.
• Firewall e gruppi di sicurezza consentono il traffico.
• L'host dell'utente di replica corrisponde all'IP sorgente della replica.
• La password e il plugin di autenticazione funzionano con la versione client/server della replica.
• Il file di log binario richiesto esiste ancora sul sorgente.

Se Replica_SQL_Running è No, la replica ha recuperato eventi ma non è riuscita ad applicarli. Controlla Last_SQL_Error. Le chiavi duplicate spesso significano che la replica non è stata inizializzata dallo snapshot esattamente corrispondente o qualcuno ha scritto direttamente sulla replica. Le righe mancanti spesso significano deriva dei dati. Saltare una transazione con SQL_SLAVE_SKIP_COUNTER può far muovere il thread, ma può anche rendere la replica errata. Usalo solo quando capisci la transazione fallita e accetti il rischio di divergenza.

Se il ritardo è alto subito dopo la configurazione, lascia che la replica recuperi e osserva se Seconds_Behind_Source diminuisce. Un grande import di dump seguito dall'avvio della replica può lasciare un arretrato. Se il ritardo cresce invece di ridursi, ispeziona I/O del disco sulla replica e volume di scrittura sul sorgente.

Mantenere la Replica in Salute Dopo la Configurazione

Configura la conservazione dei log binari sul sorgente in modo che le repliche possano sopravvivere a manutenzione e interruzioni. MySQL moderno usa binlog_expire_logs_seconds:

[mysqld]
binlog_expire_logs_seconds=604800

Questo esempio mantiene i log per circa 7 giorni. Scegli un valore basato sulle tue esigenze di recupero e sulla capacità del disco. I sistemi più vecchi possono usare expire_logs_days.

Monitora lo stato della replica e il ritardo. Come minimo, avvisa quando uno dei thread di replica si ferma, quando il ritardo supera la tua tolleranza e quando l'utilizzo del disco del sorgente cresce perché i log binari non vengono eliminati. Per i controlli di coerenza dei dati, molti team usano strumenti di Percona Toolkit come pt-table-checksum e pt-table-sync, ma testali attentamente prima di eseguirli su dati di dimensioni di produzione.

Infine, tieni lontano il traffico dell'applicazione dalla replica finché non sei sicuro che sia in sola lettura, aggiornata e monitorata. Una replica che accetta scritture accidentali è peggio di nessuna replica, perché il danno può rimanere nascosto fino al failover o al recupero.

La replica asincrona funziona bene quando lo snapshot iniziale, le coordinate del log binario, i privilegi e il monitoraggio sono tutti allineati. La maggior parte delle configurazioni fallite deriva da uno di questi elementi che viene assunto invece di essere verificato.