Practical MySQL & MariaDB

Replica MySQL spiegata: configurazione, GTID, monitoraggio e guida alla risoluzione dei problemi

Avanzato / Altro
目次

1. Cos’è la replica MySQL? Panoramica e casi d’uso

La replica MySQL è una funzionalità che sincronizza copie di un database su altri server in tempo reale. Ciò migliora la ridondanza e le prestazioni del database. Di seguito, spieghiamo in dettaglio quando viene utilizzata la replica MySQL e come funziona.

Panoramica della replica MySQL

La replica MySQL condivide i contenuti del database su più server utilizzando un server master e uno o più server slave. In particolare, gli aggiornamenti dei dati registrati nel log binario del server master vengono letti e applicati dai server slave per mantenere i dati sincronizzati. Ciò consente la continuità del servizio passando a un server slave se il server master subisce un guasto.

Casi d’uso della replica MySQL

La replica MySQL è ampiamente utilizzata nei seguenti scenari:

  • Alta disponibilità : In caso di guasto, passare a un server slave riduce al minimo i tempi di inattività.
  • Bilanciamento del carico : Le query in sola lettura possono essere distribuite ai server slave, riducendo il carico sul server master.
  • Protezione dei dati e backup : Poiché la replica duplica i dati in tempo reale, può anche fungere da meccanismo di backup.

Tipi di replica

La replica MySQL include i seguenti tipi in base al metodo di sincronizzazione:

  • Replica asincrona : Il master procede senza attendere la conferma dallo slave. Questo consente tempi di risposta più rapidi ma può comportare che alcuni dati non raggiungano lo slave durante un guasto.
  • Replica semi-sincrona : Il master attende la conferma che i dati siano stati ricevuti dallo slave prima di procedere. Questo offre una maggiore affidabilità rispetto alla replica asincrona, sebbene con tempi di risposta leggermente più lenti.

La sezione successiva spiega i concetti fondamentali della replica MySQL, inclusi i log binari e il GTID.

2. Concetti di base della replica MySQL

Per comprendere la replica MySQL, è importante afferrare i ruoli del log binario e del GTID (Global Transaction ID), che svolgono ruoli critici nel processo di replica. Questi elementi costituiscono la base per una replica accurata dei dati.

Ruoli di Master e Slave

Nella replica MySQL, il server master e il server slave hanno ruoli distinti. Il master registra gli aggiornamenti dei dati nel log binario e li invia allo slave. Lo slave applica i log ricevuti per aggiornare i propri dati. Di conseguenza, lo slave mantiene gli stessi dati del master.

Log binario e log di relay

La replica MySQL si basa sui seguenti due log:

  1. Log binario
  • Il log binario registra gli aggiornamenti dei dati (come INSERT, UPDATE e DELETE) sul server master. Questo consente al server slave di mantenere lo stesso stato dei dati del master.
  1. Log di relay
  • Il log di relay memorizza gli eventi del log binario ricevuti dal master sul server slave. Il thread SQL dello slave esegue il log di relay in sequenza per applicare le modifiche ai dati.

Cos’è il GTID (Global Transaction ID)?

GTID è un meccanismo che assegna un ID univoco a ogni transazione, aiutando a mantenere la coerenza tra più slave. Utilizzando il GTID, non è più necessario specificare le posizioni del log binario. Solo le transazioni non ancora recuperate dal master vengono applicate automaticamente allo slave, semplificando notevolmente la gestione.

Vantaggi del GTID

  • Identificazione univoca : A ogni transazione viene assegnato un GTID unico, identificando chiaramente quali transazioni sono state applicate.
  • Recupero più semplice : Quando si utilizza il GTID, solo le transazioni non applicate vengono rielaborate dopo un riavvio del master o dello slave.
  • Gestione operativa efficiente : Anche in ambienti su larga scala con più slave, la coerenza delle transazioni può essere mantenuta con una gestione semplificata.

Per utilizzare il GTID, sono necessarie le impostazioni gtid_mode=ON e enforce_gtid_consistency=ON. Configurare queste impostazioni sia sul master che sullo slave abilita la replica basata su GTID.

La sezione successiva spiega i passaggi specifici per configurare la replica MySQL.

3. Procedura di Configurazione della Replica MySQL

Questa sezione spiega i passaggi necessari per configurare la replica MySQL. Seguendo questi passaggi, è possibile configurare una struttura master-slave di base e abilitare la sincronizzazione dei dati in tempo reale.

Configurazione del Server Master

Prima, modifica il file di configurazione del server master (solitamente my.cnf o my.ini) per abilitare il log binario e impostare l’ID del server.

  1. Modifica del File di Configurazione
  • Aggiungi le seguenti impostazioni nella sezione [mysqld] e imposta un ID server univoco (ad esempio, 1).
    [mysqld]
server-id=1
log-bin=mysql-bin
  • L’server-id deve essere un numero univoco per ciascun server, e log-bin abilita il log binario.
  1. Creazione di un Utente per la Replica
  • Crea un utente dedicato per la replica sul server master e concedi i privilegi necessari.
    CREATE USER 'repl'@'%' IDENTIFIED BY 'password';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
FLUSH PRIVILEGES;
  • Questo utente è necessario affinché il server slave possa accedere ai dati dal master.
  1. Controllo dello Stato del Master
  • Controlla il file di log binario corrente e la posizione (posizione del log). Queste informazioni sono necessarie durante la configurazione del server slave.
    SHOW MASTER STATUS;
  • Il File (nome del file di log) e la Position visualizzati da questo comando verranno utilizzati nella configurazione dello slave.

Configurazione del Server Slave

Successivamente, modifica il file di configurazione del server slave e configura l’ID del server e le informazioni sul master.

  1. Modifica del File di Configurazione
  • Imposta un server-id univoco anche sul server slave (ad esempio, 2). Questo valore deve differire dall’ID del server master.
    [mysqld]
server-id=2
  • È anche comune impostare read_only=ON per prevenire le scritture di dati sul server slave.
  1. Configurazione delle Informazioni sul Master nello Slave
  • Esegui il seguente comando sul server slave, specificando il nome host del master, l’utente, il nome del file di log binario e la posizione.
    CHANGE MASTER TO
    MASTER_HOST='master_host',
    MASTER_USER='repl',
    MASTER_PASSWORD='password',
    MASTER_LOG_FILE='mysql-bin.000001',
    MASTER_LOG_POS=123;
  • Inserisci i valori confermati in precedenza sul master per MASTER_LOG_FILE e MASTER_LOG_POS .
  1. Avvio della Replica
  • Esegui il seguente comando sul server slave per avviare la replica.
    START SLAVE;

Verifica dello Stato della Replica

Verifica che la replica tra master e slave sia configurata correttamente.

  • Controllo dello Stato del Master
    SHOW MASTER STATUS;
  • Controllo dello Stato dello Slave
    SHOW SLAVE STATUS\G;
  • Se Slave_IO_Running e Slave_SQL_Running mostrano entrambi Yes , la replica sta funzionando normalmente.

La sezione successiva spiega le configurazioni avanzate della replica, incluse le differenze tra replica asincrona e semi-sincrona e la configurazione utilizzando GTID.

4. Tipi di Replica e Utilizzo Avanzato

La replica MySQL include due tipi principali basati sul metodo di sincronizzazione dei dati: replica asincrona e replica semi-sincrona. Comprenderne le caratteristiche e scegliendole in base al caso d’uso, è possibile migliorare sia le prestazioni del sistema che l’affidabilità. Questa sezione spiega anche i benefici dell’utilizzo di GTID (Global Transaction Identifier) per la configurazione della replica.

Differenze tra Replica Asincrona e Semi-Sincrona

1. Replica Asincrona

Nella replica asincrona, il server master restituisce una risposta al client immediatamente dopo aver completato una transazione. In altre parole, il master può continuare a elaborare nuove richieste anche mentre la sincronizzazione verso il server slave è ritardata. Questo fornisce eccellenti prestazioni di risposta ed è adatto a sistemi focalizzati sulla distribuzione del carico. Tuttavia, durante un guasto, esiste il rischio che i dati non ancora applicati al server slave possano essere persi.

2. Replica Semi-Sincrona

In semi-sincrona replica, il server master restituisce una risposta al client solo dopo aver confermato che il trasferimento dei dati al server slave è completato. Questo migliora la coerenza dei dati, ma poiché attende lo slave, i tempi di risposta delle transazioni possono aumentare. La replica semi‑sincrona è particolarmente adatta a sistemi che richiedono alta coerenza dei dati o ambienti in cui l’affidabilità dei dati è la massima priorità.

Replicazione con GTID

GTID (Global Transaction Identifier) assegna un ID univoco a ogni transazione e mantiene la coerenza delle transazioni tra master e slave. Abilitare GTID semplifica la gestione della replica rispetto alla tradizionale replica basata sulla posizione del binary log.

Vantaggi di GTID

  • Coerenza dei Dati Migliorata : Con GTID, lo slave può identificare automaticamente le transazioni non ancora applicate, facilitando il mantenimento della coerenza.
  • Gestione della Replica Semplificata : GTID migliora l’efficienza per failover, commutazione master/slave e operazioni di recupero. Poiché non è più necessario specificare le posizioni del binary log, le operazioni diventano più semplici.

Configurazione della Replica GTID

Per utilizzare GTID, è necessario aggiungere e abilitare le seguenti opzioni nei file di configurazione sia del master che dello slave.

Configurazione del Server Master

[mysqld]
server-id=1
log-bin=mysql-bin
gtid_mode=ON
enforce_gtid_consistency=ON

Configurazione del Server Slave

[mysqld]
server-id=2
gtid_mode=ON
enforce_gtid_consistency=ON
read_only=ON

In un ambiente abilitato a GTID, la replica viene eseguita automaticamente tramite GTID semplicemente impostando le informazioni del master sullo slave usando il comando CHANGE MASTER TO.

La sezione successiva spiega i metodi di manutenzione per la replica MySQL e i punti chiave di monitoraggio per la gestione operativa.

5. Manutenzione e Monitoraggio della Replica

Per gestire correttamente la replica MySQL, è essenziale una manutenzione e un monitoraggio regolari. Questa sezione spiega i comandi per verificare se la replica funziona correttamente e come gestire gli errori comuni.

Come Verificare lo Stato della Replica

Utilizza i seguenti comandi per verificare lo stato di sincronizzazione tra master e slave.

Verifica dello Stato del Master

Puoi verificare lo stato della replica del server master usando il comando SHOW MASTER STATUS. Questo comando mostra il nome del file binary log corrente e la posizione, consentendoti di confermare gli ultimi aggiornamenti che dovrebbero essere consegnati allo slave.

SHOW MASTER STATUS;

L’output tipicamente include i seguenti campi:

  • File : Il nome del file binary log corrente generato dal master
  • Position : La posizione corrente all’interno del binary log
  • Binlog_Do_DB e Binlog_Ignore_DB : Database inclusi/esclusi dalla replica

Verifica dello Stato dello Slave

Puoi verificare lo stato della replica del server slave usando il comando SHOW SLAVE STATUS. I risultati includono le informazioni necessarie per determinare se lo slave sta funzionando correttamente.

SHOW SLAVE STATUS\G;

I campi chiave includono:

  • Slave_IO_Running e Slave_SQL_Running : Se entrambi sono Yes, lo slave è in esecuzione normale.
  • Seconds_Behind_Master : Indica quanto lo slave è indietro rispetto al master (in secondi). Idealmente, questo valore dovrebbe essere 0.

Risoluzione dei Problemi della Replica

Problemi comuni durante l’operazione di replica includono errori di connessione e incoerenze dei dati. Di seguito sono riportati casi di errore tipici e come affrontarli.

1. Errori di Connessione

Se Slave_IO_Running è No, significa che lo slave non può connettersi al master. Prova quanto segue:

  • Verifica il nome host o l’indirizzo IP del master : Assicurati che l’indirizzo del master sia corretto.
  • Controlla le impostazioni del firewall : Verifica che la porta necessaria (di solito 3306) sia aperta.

2. Incoerenza dei Dati

Se Last_Error contiene un messaggio di errore, potrebbe essersi verificata un’inconsistenza dei dati tra il master e lo slave. In tali casi, arresta lo slave e applica le correzioni prima di riavviare la replica.

STOP SLAVE;
# Restart after applying fixes
START SLAVE;

3. Ridurre il ritardo di replica

Il ritardo di replica può essere causato da limitazioni hardware dello slave o da problemi di rete. Se necessario, l’aggiornamento della configurazione del server slave può migliorare le prestazioni.

La sezione successiva approfondisce i problemi di replica e fornisce ulteriori soluzioni.

6. Problemi comuni e le loro soluzioni

Durante l’operazione di replica di MySQL, possono verificarsi vari problemi. Questa sezione spiega i problemi più comuni e come risolverli. Rilevare i problemi in anticipo e applicare le correzioni corrette aiuta a mantenere un funzionamento stabile del sistema.

1. Quando Slave_IO_Running è fermo

Sintomo: Se Slave_IO_Running mostra No nell’output di SHOW SLAVE STATUS, lo slave non è in grado di connettersi al master.

Cause e soluzioni:

  • Problemi di rete : Se c’è un problema di connettività di rete, lo slave non può accedere al master. Controlla le impostazioni del firewall e conferma che il master sia raggiungibile.
  • Hostname o indirizzo IP del master errato : Verifica che il nome host o l’indirizzo IP specificato in CHANGE MASTER TO sia corretto.
  • Problemi di privilegi dell’utente : Se l’utente di replica sul master non ha privilegi sufficienti, la connessione fallirà. Conferma che i privilegi corretti siano stati concessi usando GRANT REPLICATION SLAVE .

2. Inconsistenza dei dati sullo slave

Sintomo: Se i dati non corrispondono tra il master e lo slave, lo slave può entrare in uno stato incoerente.

Cause e soluzioni:

  • Correzione manuale dei dati : Arresta lo slave, correggi manualmente la transazione problematica, quindi riavvia la replica. STOP SLAVE; # Fix data if necessary START SLAVE;
  • Risincronizzazione : Se l’incoerenza è su larga scala, esegui un backup completo dal master e risincronizza lo slave.

3. Ritardo di replica

Sintomo: Se Seconds_Behind_Master non è 0 nell’output di SHOW SLAVE STATUS, lo slave è in ritardo rispetto al master. Idealmente, questo valore dovrebbe avvicinarsi il più possibile a 0.

Cause e soluzioni:

  • Limitazioni hardware dello slave : Se il server slave ha risorse insufficienti, potrebbe non riuscire a tenere il passo. L’aggiornamento dell’hardware può essere efficace.
  • Ottimizzazione delle query : Se le query ricevute dal master richiedono troppo tempo per essere eseguite sullo slave, può verificarsi un ritardo di replica. Aggiungere indici e ottimizzare le query può ridurre i tempi di elaborazione.

4. Errori di permessi dell’utente di replica

Sintomo: Se Last_Error mostra un messaggio relativo ai permessi, lo slave potrebbe non avere privilegi sufficienti per connettersi al master.

Cause e soluzioni:

  • Riconfigurare i privilegi dell’utente : Assicurati che sul master esista un utente con i privilegi appropriati. Riconfigura se necessario. GRANT REPLICATION SLAVE ON *.* TO 'repl'@'slave_ip_address'; FLUSH PRIVILEGES;

5. Crescita del binary log

Sintomo: I binary log del master possono crescere eccessivamente, consumando spazio su disco.

Cause e soluzioni:

  • Rotazione dei binary log : Elimina o archivia regolarmente i binary log per evitare una crescita eccessiva. Configurando expire_logs_days, è possibile rimuovere automaticamente i log più vecchi di un periodo specificato. SET GLOBAL expire_logs_days = 7; # Delete logs older than 7 days

Comprendendo questi problemi comuni di replica MySQL e le relative soluzioni, è possibile garantire una gestione operativa più fluida. La sezione successiva riassume i punti chiave per la gestione della replica.

7. Conclusione

La replica MySQL è una funzionalità critica per migliorare la coerenza dei dati e l’affidabilità del sistema. In questo articolo, abbiamo coperto tutto, dai concetti di base della replica MySQL alle procedure di configurazione, alle pratiche di monitoraggio e ai metodi di risoluzione dei problemi. Di seguito è riportato un riepilogo dei punti chiave per una gestione efficace della replica.

Punti Chiave

  1. Scegliere il Tipo di Replica Adeguato
  • La replica asincrona offre tempi di risposta più rapidi ed è ideale per la distribuzione del carico. Tuttavia, se l’affidabilità è la priorità, la replica semi‑sincrona è più adatta. Scegli in base ai requisiti del tuo sistema.
  1. Uso Efficace di GTID
  • GTID elimina la necessità di specificare le posizioni del log binario e consente una gestione fluida delle transazioni. È particolarmente utile in ambienti con più slave o dove il failover è critico.
  1. Monitoraggio Regolare dello Stato
  • Utilizza SHOW MASTER STATUS e SHOW SLAVE STATUS per monitorare regolarmente lo stato operativo sia dei server master che slave. Un’azione tempestiva al rilevamento di anomalie riduce i rischi come l’incoerenza dei dati o il lag.
  1. Padroneggiare la Risoluzione di Base dei Problemi
  • I problemi comuni di replica includono errori di connessione dello slave, incoerenze dei dati e lag. Comprendere le soluzioni di base per ciascun problema garantisce operazioni più fluide.
  1. Gestione del Log Binario
  • Poiché i log binari possono consumare spazio su disco significativo, è consigliato configurare expire_logs_days per la pulizia automatica ed eseguire manutenzioni regolari.

La replica MySQL non è un compito di configurazione una tantum. Il monitoraggio continuo e una corretta manutenzione sono essenziali. Revisionando regolarmente lo stato del sistema e regolando le configurazioni quando necessario, è possibile costruire e mantenere un sistema di database altamente affidabile.

Speriamo che questa guida ti aiuti a comprendere meglio e a implementare la replica MySQL. Ti auguriamo operazioni di replica fluide e stabili.