Risoluzione rapida dei comuni guasti di replica MySQL

Risoluzione rapida dei comuni errori di replica di MySQL

La replica di MySQL è una funzionalità potente che consente di mantenere più copie del database, fondamentali per l'alta disponibilità, lo scaling in lettura e il disaster recovery. Tuttavia, la configurazione e la manutenzione della replica possono a volte portare a errori imprevisti. Questa guida fornisce un approccio pratico per diagnosticare e risolvere rapidamente i problemi comuni di replica di MySQL, concentrandosi sulla comprensione dei codici di errore e sull'ispezione dei log pertinenti.

Quando la replica si interrompe, può bloccare operazioni critiche, quindi è essenziale disporre di un processo di risoluzione dei problemi sistematico. Affronteremo i problemi più frequenti, fornendoti le conoscenze necessarie per identificare la causa principale e implementare soluzioni in modo efficiente. Comprendendo i sintomi e sapendo dove cercare indizi, puoi ridurre al minimo i tempi di inattività e garantire che la tua configurazione di replica rimanga sana.

Comprensione delle basi della replica di MySQL

Prima di addentrarsi nella risoluzione dei problemi, è utile un rapido ripasso su come funziona la replica di MySQL. In una tipica configurazione master-slave (o primary-replica):

Binary Log (Binlog) sul Primary: Il server primario registra tutti gli eventi che modificano i dati nei suoi file di log binari.
Thread di replica sulla Replica: Il server replica dispone di due thread:
- Thread I/O: Si connette al primario, legge gli eventi dal log binario del primario e li scrive nel proprio relay log.
- Thread SQL: Legge gli eventi dal relay log ed esegue sul database della replica.

Gli errori di replica si verificano solitamente quando il thread I/O non riesce a recuperare gli eventi o il thread SQL non riesce ad applicarli.

Codici di errore comuni di replica e il loro significato

MySQL fornisce codici di errore che offrono preziose informazioni sui problemi di replica. Il comando SHOW REPLICA STATUS (o SHOW SLAVE STATUS nelle versioni precedenti) è il tuo strumento principale per verificare lo stato della replica.

SHOW REPLICA STATUS\G

Cerca i seguenti campi chiave:

Replica_IO_Running: Dovrebbe essere Yes.
Replica_SQL_Running: Dovrebbe essere Yes.
Last_IO_Errno e Last_IO_Error: Errori relativi al thread I/O.
Last_SQL_Errno e Last_SQL_Error: Errori relativi al thread SQL.
Seconds_Behind_Source: Indica il ritardo della replica rispetto al primario.

Ecco alcuni numeri di errore comuni e le loro cause tipiche:

Errore 1062: Duplicate Entry

Last_SQL_Errno: 1062
Last_SQL_Error: Error 'Duplicate entry '...' for key '...' on query. Default database: '...'.

Causa: Il thread SQL sta tentando di applicare un evento dal primario che provoca una violazione di chiave duplicata sulla replica. Ciò accade spesso quando la replica è rimasta indietro e ha elaborato altre scritture che potrebbero aver creato gli stessi dati, o se un'incoerenza è stata introdotta manualmente sulla replica.

Risoluzione:
1. Identificare la query problematica: Il messaggio di errore include solitamente la query che è fallita.
2. Saltare la transazione (con cautela): Se sei sicuro che sia sicuro saltarla, puoi usare SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; seguito da START SLAVE SQL_THREAD; (o START REPLICA SQL_THREAD;). Attenzione: Saltare le transazioni può portare a divergenze nei dati. Comprendi le implicazioni prima di procedere.
3. Indagare sull'incoerenza dei dati: Se saltare non è un'opzione, potrebbe essere necessario riconciliare manualmente i dati o indagare sul motivo per cui si è verificato il duplicato. Ciò potrebbe comportare il ripristino della replica da un punto specifico se la replica è gravemente fuori sincronizzazione.

Errore 1236: Impossibile trovare il nome del primo file di log nell'indice del log binario

Last_IO_Errno: 1236
Last_IO_Error: Error 'Could not find first log file name in binary log index' when trying to read event from the http client side...

Causa: Il thread I/O non riesce a individuare il file di log binario specificato dal primario. Ciò significa solitamente che i file di log binari sono stati eliminati dal primario prima che la replica potesse leggerli, o che la replica sta tentando di connettersi utilizzando un file binlog che non esiste più.

Risoluzione:
1. Controllare la conservazione del binlog del primario: Assicurati che expire_logs_days (o binlog_expire_logs_seconds) sul primario sia impostato su un valore che conservi i log abbastanza a lungo affinché la replica possa recuperare.
2. Re-inizializzare la replica: La soluzione più comune è interrompere la replica, reimpostare i dati master della replica e re-inizializzarla da un backup o snapshot fresco del primario, assicurandosi che il nuovo file di log primario e la posizione siano impostati correttamente.

Errore 1577: La posizione del log binario del primario è richiesta

Last_IO_Errno: 1577
Last_IO_Error: Error: The primary's binary log position is required for this operation.

Causa: Questo errore si verifica in genere quando si tenta di avviare la replica senza specificare il nome corretto del file di log binario e la posizione sulla replica. Ciò può accadere dopo determinate modifiche alla configurazione o interventi manuali.

Risoluzione:
1. Verificare il comando CHANGE MASTER TO (o CHANGE REPLICATION SOURCE TO): Assicurati di aver specificato correttamente MASTER_LOG_FILE e MASTER_LOG_POS (o SOURCE_LOG_FILE e SOURCE_LOG_POS) quando configuri la replica.
2. Reimpostare e riconfigurare: Interrompere la replica, reimpostare lo stato della replica e riapplicare il comando CHANGE MASTER TO con i parametri corretti ottenuti dal primario.

Errore 1032: Impossibile trovare il record nella tabella '...'

Last_SQL_Errno: 1032
Last_SQL_Error: Error 'Can't find record in '...' table' on query. Default database: '...'.

Causa: Simile all'errore 1062, questo indica che il thread SQL sta tentando di eseguire un'operazione UPDATE o DELETE su un record che non esiste sulla replica. Ciò implica una divergenza dei dati, spesso a causa di una precedente transazione saltata o di una modifica manuale.

Risoluzione:
1. Identificare la query e la tabella: Il messaggio di errore fornisce dettagli.
2. Indagare sulla divergenza dei dati: Confronta lo stato della tabella interessata sul primario e sulla replica.
3. Saltare (con estrema cautela): Se il record mancante è insignificante o è stato gestito in altro modo, puoi saltare la transazione usando SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; e START REPLICA SQL_THREAD;.
4. Correzione manuale dei dati: Nei casi critici, potrebbe essere necessario inserire manualmente il record mancante o risincronizzare la tabella/il database.

Ispezione dei log di replica

Oltre a SHOW REPLICA STATUS, il log degli errori di MySQL e il log binario stesso sono risorse inestimabili.

Log degli errori di MySQL

Situato in genere in /var/log/mysql/error.log (o simile, a seconda del tuo sistema operativo e della configurazione), questo log contiene informazioni dettagliate sugli errori riscontrati dal server MySQL, inclusi quelli relativi ai thread di replica.

Cosa cercare:
* Stack trace dettagliati per gli errori.
* Problemi di connessione tra primario e replica.
* Timeout e problemi relativi alla rete.

Log binario del primario

Mentre i relay log della replica sono cruciali per il thread SQL, l'esame del log binario del primario può a volte aiutare a comprendere la sequenza di eventi che portano a un errore. È possibile utilizzare l'utility mysqlbinlog a questo scopo.

Esempio: per visualizzare gli eventi da un file di log binario specifico:

mysqlbinlog /path/to/mysql-bin.000001

Esempio: per visualizzare eventi attorno a un'ora o posizione specifica:

mysqlbinlog --start-datetime="2023-10-27 10:00:00" --stop-datetime="2023-10-27 11:00:00" /path/to/mysql-bin.000001

Casi d'uso:
* Comprendere la transazione esatta che ha causato un errore SQL della replica.
* Verificare la coerenza degli eventi che vengono scritti.

Passaggi generali per la risoluzione dei problemi

Quando la replica si interrompe, segui questi passaggi:

Controlla SHOW REPLICA STATUS: Inizia sempre da qui. È il modo più rapido per ottenere un riepilogo del problema.
Esamina Last_IO_Error e Last_SQL_Error: Comprendi il codice di errore specifico e il messaggio.
Consulta il log degli errori di MySQL: Cerca un contesto più dettagliato sul lato server.
Verifica la connettività di rete: Assicurati che la replica possa raggiungere il primario (firewall, DNS).
Controlla i privilegi dell'utente: L'utente di replica sul primario deve disporre delle autorizzazioni necessarie (REPLICATION SLAVE).
Assicurati che il primario sia configurato per la replica: Verifica che log_bin sia abilitato e server_id sia univoco.
Controlla l'impostazione read_only della replica: Se read_only è abilitato sulla replica, non applicherà le scritture dal primario a meno che non siano soddisfatte condizioni specifiche o venga temporaneamente disabilitato.

Migliori pratiche per prevenire errori

Monitora il ritardo della replica: Utilizza strumenti di monitoraggio per avvisarti quando Seconds_Behind_Source aumenta eccessivamente.
Backup regolari: Mantieni backup coerenti del tuo primario per poter re-inizializzare rapidamente una replica.
Conservazione sufficiente del binlog: Configura expire_logs_days in modo appropriato sul primario.
server_id univoco: Assicurati che ogni server nella tua topologia di replica abbia un server_id univoco.
Testa le procedure di failover: Esercitati regolarmente a scambiare i ruoli per assicurarti che la tua configurazione di replica sia robusta.

Conclusione

La risoluzione dei problemi di replica di MySQL richiede un approccio metodico. Comprendendo i codici di errore comuni, sapendo come interpretare l'output di SHOW REPLICA STATUS e sfruttando i log degli errori di MySQL e l'utility mysqlbinlog, puoi diagnosticare e risolvere in modo efficiente la maggior parte dei problemi di replica. Il monitoraggio proattivo e l'adesione alle migliori pratiche ridurranno ulteriormente il verificarsi di questi problemi, garantendo la stabilità e la disponibilità del tuo ambiente di database.