Choque esporádico do processo de replicação MySQL master-slave

Question

Eu queria saber se alguém já passou por isso e talvez possa fornecer algumas dicas sobre esse problema.

Nós temos uma replicação mestre-escravo do MySQL planejada-baunilha. As tabelas são MyISAM e o mestre pode obter bastante leitura / gravação ativa. Usamos a instância do escravo para executar backups diários completos, a fim de evitar que o servidor mestre seja desativado. O processo de backup faz o seguinte:

STOP SLAVE SQL_THREAD 
mysqlhotcopy all tables 
START SLAVE SQL_THREAD

De vez em quando (uma vez por mês) a replicação é interrompida com mensagens de erro variadas indicando uma consulta ou arquivo de log danificado. Aqui está um que aconteceu ontem à noite:

mysql> show slave status \G
*************************** 1. row ***************************
             Slave_IO_State: Waiting for master to send event
                Master_Host: server8
                Master_User: nexus8
                Master_Port: 3306
              Connect_Retry: 60
            Master_Log_File: bin.000045
        Read_Master_Log_Pos: 581644327
             Relay_Log_File: relay.000086
              Relay_Log_Pos: 94131
      Relay_Master_Log_File: bin.000045
           Slave_IO_Running: Yes
          Slave_SQL_Running: No
            Replicate_Do_DB:
        Replicate_Ignore_DB:
         Replicate_Do_Table:
     Replicate_Ignore_Table:
    Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
                 Last_Errno: 1064
                 Last_Error: Error 'You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '138070603'£' at line 1' on query. Default database: 'wtsdb'. Query: 'UPDATE fill SET clearing_fee='0.0E id='138070603'£'
               Skip_Counter: 0
        Exec_Master_Log_Pos: 4164743
            Relay_Log_Space: 577574251
            Until_Condition: None
             Until_Log_File:
              Until_Log_Pos: 0
         Master_SSL_Allowed: No
         Master_SSL_CA_File:
         Master_SSL_CA_Path:
            Master_SSL_Cert:
          Master_SSL_Cipher:
             Master_SSL_Key:
      Seconds_Behind_Master: NULL

Eu sigo o seguinte procedimento para recuperar o erro acima e retomar a replicação:

stop slave;
change master to MASTER_LOG_POS = 4164743, MASTER_LOG_FILE = 'bin.000045';
start slave;

Temos vários servidores configurados dessa forma e todos eles esporadicamente param de se replicar com um erro semelhante. Qualquer conselho sobre como resolver isso seria muito apreciado.

Atualização:

Lendo o excelente artigo sugerido por pQd (especificamente na seção 3.5), parece bem possível que eu esteja sofrendo corrupção de logs de retransmissão, que pode "tornar as declarações incomparáveis". Aparentemente, isso é algo que pode ocorrer, mas indica um erro no código de replicação do MySQL. Eu realmente não consigo reproduzir esse problema, mas isso acontece pelo menos uma vez por mês para cada escravo em nosso ambiente de produção.

Aqui estão mais dois erros para referência:

*************************** 1. row *************************** 
             Slave_IO_State: Waiting for master to send event 
                Master_Host: server10
                Master_User: nexus10 
                Master_Port: 3306 
              Connect_Retry: 60 
            Master_Log_File: bin.000008 
        Read_Master_Log_Pos: 1034654844 
             Relay_Log_File: relay.001392 
              Relay_Log_Pos: 109929 
      Relay_Master_Log_File: bin.000008 
           Slave_IO_Running: Yes 
          Slave_SQL_Running: No 
            Replicate_Do_DB: 
        Replicate_Ignore_DB: 
         Replicate_Do_Table: 
     Replicate_Ignore_Table: 
    Replicate_Wild_Do_Table: 
Replicate_Wild_Ignore_Table: 
                 Last_Errno: 1064 
                 Last_Error: Error 'You have an error in your SQL syntax; check the manual that corresponds to your MySQL server versio n for the right syntax to use near '' at line 1' on query. Default database: 'avidb'. Query: 'DELETE FROM reconciliat' 
               Skip_Counter: 0 
        Exec_Master_Log_Pos: 1026067551 
            Relay_Log_Space: 8697758 
            Until_Condition: None 
             Until_Log_File: 
              Until_Log_Pos: 0 
         Master_SSL_Allowed: No 
         Master_SSL_CA_File: 
         Master_SSL_CA_Path: 
            Master_SSL_Cert: 
          Master_SSL_Cipher: 
             Master_SSL_Key: 
      Seconds_Behind_Master: NULL 



*************************** 1. row *************************** 
             Slave_IO_State: Waiting for master to send event 
                Master_Host: server8
                Master_User: nexus8 
                Master_Port: 3306 
              Connect_Retry: 60 
            Master_Log_File: bin.000019 
        Read_Master_Log_Pos: 476189428 
             Relay_Log_File: relay.000163 
              Relay_Log_Pos: 98576 
      Relay_Master_Log_File: bin.000019 
           Slave_IO_Running: Yes 
          Slave_SQL_Running: No 
            Replicate_Do_DB: 
        Replicate_Ignore_DB: 
         Replicate_Do_Table: 
     Replicate_Ignore_Table: 
    Replicate_Wild_Do_Table: 
Replicate_Wild_Ignore_Table: 
                 Last_Errno: 1064 
                 Last_Error: Error 'You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1' on query. Default database: 'wtsdb'. Query: 'UPDATE requestlog SET id='2173589',start_time='2009-08-18 04:59:10',end_time='2009-08-18 04:59:10',ip='X.X.X.X',session='hkacn7d54',lock_wait_time='0',unmarshal_time='0.00057435035705566',unmarshaled_objects='30',data_sync_time='0',page_process_time='0',memory_used='1572864',memory_used_peak='1572864',method='GET',request='/report.php',request_parameters='
groupId = -4 
startDate = 2009-08-17 
reportType = trades 
accountId = 2409 
endDate = 2009-08-17',progress_percent='0',progress_text='' WHERE id='2173588'' 
               Skip_Counter: 0 
        Exec_Master_Log_Pos: 186172755 
            Relay_Log_Space: 290116589 
            Until_Condition: None 
             Until_Log_File: 
              Until_Log_Pos: 0 
         Master_SSL_Allowed: No 
         Master_SSL_CA_File: 
         Master_SSL_CA_Path: 
            Master_SSL_Cert: 
          Master_SSL_Cipher: 
             Master_SSL_Key: 
      Seconds_Behind_Master: NULL 

    

Answer 1

Verifique novamente se seus conjuntos de caracteres nos bancos de dados e conexões são o que você espera que eles sejam - sua replicação sufocando em uma instrução com um caractere não-ASCII é imediatamente suspeita.

Observe o log binário do mestre para ver como a instrução registrada é diferente da declaração que a réplica está tentando executar - que oferecerá dicas para o problema.

Infelizmente, você também deve se certificar de que as versões do servidor para o mestre e o escravo sejam as mesmas. Recentemente, encontrei uma instância estranha em que tivemos um SQL incorreto tentando atualizar uma coluna numérica não-NULL para NULL. Na versão em que o master estava rodando, o MySQL converteu-o silenciosamente em 0, mas na versão em que a réplica estava rodando, foi um erro.

Além disso, em vez de fazer CHANGE MASTER para corrigir a réplica, você deve estar fazendo

STOP SLAVE;
[correct and execute whatever statement is the problem]
SET GLOBAL sql_slave_skip_counter = 1;
START SLAVE;

link

    

Answer 2

é possível que algumas de suas consultas de atualização não sejam seguras para replicação? procure por exemplo aqui - ponto 3.1

é um bom hábito comparar seus dados de tempos em tempos entre mestre e escravo [azar você usa myisam não innodb] - por exemplo com mk-table-checksum de maatkit .

ps: mais dois links para analisar: 1 e 2 .