Risoluzione Rapida dei Problemi Comuni di Replica MySQL

I guasti della replica MySQL sono più facili da risolvere quando separi due domande: la replica può recuperare eventi dalla sorgente e può applicare gli eventi che ha già recuperato? Sono guasti diversi. Un problema di rete, un log binario mancante, una password errata o una concessione host sbagliata di solito fermano il thread I/O. Una chiave duplicata, una riga mancante, una discrepanza DDL o una deriva dei dati di solito fermano il thread SQL.

Inizia con l'output dello stato. Su MySQL moderno:

SHOW REPLICA STATUS\G

Su sistemi più vecchi:

SHOW SLAVE STATUS\G

Usa il comando supportato dal tuo server. L'output più recente utilizza nomi come Replica_IO_Running, Replica_SQL_Running e Seconds_Behind_Source. L'output più vecchio utilizza Slave_IO_Running, Slave_SQL_Running e Seconds_Behind_Master.

La prima lettura utile è:

• Replica_IO_Running: se la replica è connessa e sta leggendo i log binari della sorgente.
• Replica_SQL_Running: se la replica sta applicando gli eventi del log relay.
• Last_IO_Errno e Last_IO_Error: perché il recupero è fallito.
• Last_SQL_Errno e Last_SQL_Error: perché l'applicazione è fallita.
• Relay_Master_Log_File, Exec_Master_Log_Pos o campi più recenti della posizione sorgente: dove si trova la replica nel flusso.

Non saltare direttamente a una soluzione. Copia prima l'output completo dello stato nelle note dell'incidente. Una volta eseguito RESET REPLICA, saltata una transazione o reindirizzata la replica, alcune delle migliori prove scompaiono.

Se il Thread I/O è Fermo

Quando Replica_IO_Running è No, la replica non sta leggendo con successo dalla sorgente. Il thread SQL potrebbe ancora applicare eventi del log relay più vecchi per un po', ma alla fine si esaurirà.

Le cause comuni sono:

• L'host o la porta della sorgente sono errati.
• Un firewall, un gruppo di sicurezza o una regola di routing blocca la connessione.
• La password dell'utente di replica è errata.
• L'utente di replica è autorizzato da un host diverso da quello effettivamente utilizzato dalla replica.
• La registrazione binaria è disabilitata sulla sorgente.
• La sorgente ha eliminato il file di log binario richiesto dalla replica.
• Le impostazioni TLS sono cambiate e la replica non può più autenticarsi.

Test dall'host della replica:

mysql -h source-db.example.com -u repl_user -p

Se un login diretto fallisce, anche la replica fallirà. Controlla l'account sulla sorgente:

SHOW GRANTS FOR 'repl_user'@'replica_host_or_ip';

L'account necessita del privilegio REPLICATION SLAVE. Il nome del privilegio usa ancora "SLAVE" nelle concessioni MySQL.

Controlla anche se la registrazione binaria è abilitata:

SHOW VARIABLES LIKE 'log_bin';
SHOW MASTER STATUS;

Nelle versioni più recenti, potrebbe essere disponibile SHOW BINARY LOG STATUS. Il punto è lo stesso: la sorgente deve avere log binari e il file richiesto deve ancora esistere.

Errore 1236: Log Binario Mancante o Illeggibile

Last_IO_Errno: 1236 è uno degli errori che di solito significa che la replica sta chiedendo un file di log binario o una posizione che la sorgente non può fornire. Il messaggio esatto varia. Potrebbe dire che il primo file di log non può essere trovato, un evento di log non può essere letto o la sorgente ha chiuso la connessione durante la lettura.

Il caso operativo più comune è semplice: la replica è stata offline troppo a lungo e la sorgente ha eliminato i log binari di cui aveva bisogno.

Controlla quali log rimangono sulla sorgente:

SHOW BINARY LOGS;

Quindi confronta quell'elenco con il file nominato nello stato della replica. Se la replica necessita di mysql-bin.000120 e la sorgente ora inizia con mysql-bin.000140, la replica non può recuperare dai log binari.

Hai tre scelte realistiche:

• Ripristinare o ricostruire la replica da un backup fresco preso dalla sorgente.
• Usare un'altra replica che ha ancora i dati necessari come sorgente clone, se il tuo processo lo supporta.
• Se si utilizza GTID e le transazioni mancanti esistono altrove, riconfigurare da una sorgente valida che può fornirle.

Non indovinare una posizione di log più recente solo per far ripartire la replica. Questo crea una replica con transazioni mancanti. Potrebbe sembrare sana mentre restituisce silenziosamente dati errati.

Dopo il recupero, aumenta la conservazione dei log binari se la capacità del disco lo consente:

[mysqld]
binlog_expire_logs_seconds=604800

Questo esempio è circa 7 giorni. Scegli un valore in base a quanto tempo le repliche possono rimanere offline durante la manutenzione o gli incidenti.

Se il Thread SQL è Fermo

Quando Replica_SQL_Running è No, la replica ha recuperato eventi ma non è riuscita ad applicarne uno. Questo è spesso un problema di coerenza dei dati, non un problema di connessione.

Leggi l'intero Last_SQL_Error. Di solito ti dice la tabella, la chiave, l'operazione fallita e talvolta la posizione del log sorgente. Quindi ispeziona la riga pertinente sia sulla sorgente che sulla replica prima di cambiare qualsiasi cosa.

Per un evento fallito attorno a una posizione nota del log binario, mysqlbinlog può mostrare l'evento:

mysqlbinlog --start-position=123456 --stop-position=124500 /var/lib/mysql/mysql-bin.000321

Se i log binari della sorgente non sono sull'host locale, usa opzioni remote o ispeziona un file di log copiato. Fai attenzione con gli eventi basati su riga: potrebbero aver bisogno di opzioni di decodifica e metadati della tabella per essere leggibili.

Errore 1062: Voce Duplicata

Last_SQL_Errno: 1062 significa che la replica ha tentato di inserire o aggiornare una riga e ha incontrato una chiave univoca che esiste già.

Le cause tipiche includono:

• Qualcuno ha scritto direttamente sulla replica.
• La replica è stata inizializzata dallo snapshot sbagliato.
• Un precedente errore di replica è stato saltato.
• Le impostazioni di auto-increment sono errate in un design multi-sorgente o attivo-attivo.
• Le scritture dell'applicazione sono andate a due server scrivibili per errore.

La soluzione allettante è:

STOP REPLICA;
SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
START REPLICA;

La sintassi più vecchia usa STOP SLAVE e START SLAVE. Questo potrebbe essere accettabile per una replica di reportistica usa e getta dopo aver confermato che la riga non ha importanza. È pericoloso per una replica che potrebbe essere promossa in seguito. Saltare significa che la replica non ha più la stessa cronologia delle transazioni della sorgente.

Un processo più sicuro è:

1. Identificare la tabella e la chiave in conflitto.
2. Confrontare la riga sulla sorgente e sulla replica.
3. Decidere se la riga della replica deve essere eliminata, aggiornata o se la replica deve essere ricostruita.
4. Registrare la decisione, perché ora questo è un evento di coerenza dei dati.

Se la replica è destinata al failover, la ricostruzione è spesso più pulita che correggere a mano diverse differenze sconosciute.

Errore 1032: Impossibile Trovare il Record

Last_SQL_Errno: 1032 di solito significa che la replica ha tentato di aggiornare o eliminare una riga che non esiste localmente. Questa è l'immagine speculare di molti problemi di chiave duplicata. La sorgente aveva una riga; la replica no.

Le cause comuni sono:

• Una riga è stata eliminata manualmente sulla replica.
• Una transazione precedente è stata saltata.
• Il dump iniziale ha perso dati.
• I filtri di replica hanno escluso scritture precedenti.

Non dare per scontato che la riga mancante sia innocua. Se un UPDATE non riesce a trovare una riga, la replica è già diversa dalla sorgente. Confronta i conteggi e i dati di esempio attorno alla chiave interessata. Se la tabella è piccola, un ricaricamento della tabella potrebbe essere ragionevole. Se è grande o critica, usa uno strumento di coerenza o ricostruisci la replica.

Problemi di Autenticazione e Concessione Host

Un guasto molto comune dopo la rotazione delle password o le modifiche alla rete è un errore I/O che sembra accesso negato:

Access denied for user 'repl_user'@'10.0.2.15'

L'host nell'errore è quello che MySQL vede. Potrebbe non corrispondere al nome host che ti aspettavi, specialmente con NAT, proxy o networking di container.

Sulla sorgente, ispeziona gli utenti:

SELECT user, host, plugin FROM mysql.user WHERE user = 'repl_user';

Se la replica si connette da 10.0.2.15, una concessione per 'repl_user'@'replica.internal' potrebbe non corrispondere a meno che la risoluzione dei nomi e le concessioni siano allineate. Preferisci modelli host espliciti che corrispondano al tuo design di rete.

Se il plugin differisce, i client più vecchi potrebbero fallire contro account che utilizzano plugin di autenticazione più recenti. Aggiornare il client è di solito meglio che indebolire l'autenticazione, ma in ambienti con versioni miste potresti aver bisogno di una modifica di compatibilità pianificata.

Problemi del Log Relay

A volte la connessione sorgente è ok, ma la replica ha corruzione del log relay o problemi locali del disco. L'errore potrebbe menzionare un errore di lettura del log relay, un evento troncato o una posizione del log relay.

Prima controlla la salute del disco e lo spazio libero. Un disco pieno può creare diversi sintomi di replica strani:

df -h
iostat -xz 1

Se il log relay è corrotto ma la sorgente ha ancora i log binari necessari, spesso puoi resettare i log relay e lasciare che la replica recuperi di nuovo. Il comando esatto dipende dalla versione e dalla topologia. Non eseguire comandi di reset con noncuranza; conferma di conoscere il file di log sorgente e la posizione che è già stata eseguita.

In molti casi, questo tipo di problema è un segno che l'host della replica aveva un problema di archiviazione sottostante. Risolvilo prima di fidarti di nuovo della replica.

Il Ritardo di Replica Non è Sempre un Guasto

Seconds_Behind_Source può essere alto mentre entrambi i thread sono in esecuzione. Ciò significa che la replica è viva ma in ritardo. Tratta il ritardo in modo diverso da un thread fermo.

Controlla:

• Il disco della replica è saturo?
• La sorgente sta generando un picco di scritture?
• Le letture lunghe sulla replica competono con il thread SQL?
• La replica è più piccola o più lenta della sorgente?
• Un lavoro di backup o uno snapshot è iniziato allo stesso tempo?

Se il ritardo sta diminuendo, la replica sta recuperando. Se il ritardo sta crescendo, rimuovi il carico o aggiungi capacità. Riavviare una replica in ritardo raramente risolve un collo di bottiglia di risorse sostenuto.

Filtri e Replica Multi-Sorgente

I filtri di replica possono rendere i guasti più difficili da leggere. Una replica può ignorare intenzionalmente alcuni database o tabelle, ma l'applicazione potrebbe ancora aspettarsi che i dati correlati esistano. Se usi filtri, ispezionali prima di presumere che la replica sia corrotta:

SHOW REPLICA STATUS\G

Cerca campi che menzionano Replicate_Do_DB, Replicate_Ignore_DB, Replicate_Do_Table o regole di riscrittura. L'output più vecchio usa gli stessi nomi generali sotto SHOW SLAVE STATUS.

Il filtraggio è particolarmente rischioso con scritture cross-database. Se una transazione aggiorna app.orders e audit.order_events, ma la replica filtra audit, la copia risultante potrebbe essere tecnicamente coerente con il filtro e ancora inutile per un flusso di lavoro che si aspetta righe di audit. La registrazione basata su istruzioni può rendere i filtri di database ancora più sorprendenti perché il database predefinito selezionato può influenzare se un evento viene replicato.

La replica multi-sorgente aggiunge un altro livello. Un canale può essere sano mentre un altro è fermo. In tal caso, controlla lo stato per tutti i canali invece di leggere solo il primo blocco di output:

SHOW REPLICA STATUS\G

Nelle configurazioni basate su canali, l'output dello stato include un nome di canale. Risolvi il canale fallito senza resettare i canali sani. Se due sorgenti possono scrivere chiavi sovrapposte nella stessa tabella, gli errori di chiave duplicata sono spesso un problema di progettazione piuttosto che un guasto di replica una tantum.

Evita la Deriva Nascosta dei Dati

Il peggior guasto di replica è quello che dice Yes e contiene ancora dati errati. La deriva può verificarsi dopo transazioni saltate, scritture dirette sulle repliche, importazioni fallite, filtri errati o riparazioni manuali.

Per le repliche importanti, pianifica controlli di coerenza. Il pt-table-checksum di Percona Toolkit è comunemente usato per questo, e pt-table-sync può aiutare a riparare le differenze in situazioni controllate. Questi strumenti possono creare carico, quindi testali prima ed eseguili con limiti che corrispondano al tuo ambiente di produzione.

Proteggi anche le repliche da scritture accidentali:

[mysqld]
read_only=ON
super_read_only=ON

Usa credenziali separate per le letture dell'applicazione. Non permettere agli utenti dell'applicazione di avere ampi privilegi di scrittura sulle repliche "per ogni evenienza".

Una Checklist Rapida per Incidenti

Usa questo ordine quando la replica si rompe:

1. Salva l'output di SHOW REPLICA STATUS\G.
2. Controlla se il thread I/O o il thread SQL si è fermato.
3. Leggi Last_IO_Error o Last_SQL_Error; non fare affidamento solo sul numero di errore.
4. Controlla il log degli errori MySQL per timestamp corrispondenti.
5. Per guasti I/O, testa rete, credenziali, concessioni, TLS e disponibilità del log binario.
6. Per guasti SQL, ispeziona la riga o la tabella interessata sia sulla sorgente che sulla replica.
7. Decidi se riparare, saltare con rischio documentato, ricaricare una tabella o ricostruire la replica.
8. Dopo il recupero, esegui un test di scrittura reale e monitora il ritardo.

La maggior parte dei guasti di replica MySQL non vengono risolti da un singolo comando magico. Vengono risolti preservando le prove, identificando quale thread è fallito e scegliendo una soluzione che non ti lasci con una replica che è in esecuzione ma inaffidabile.