Formato de registro binário do MySQL - preenchimento de disco

Question

Eu tenho uma instância do servidor MySQL configurada com log binário ativado. Eu não faço nenhuma replicação, mas os logs binários fazem parte do nosso plano de recuperação - para poder reproduzir todas as transações desde o último backup completo.

Não muito tempo atrás, foi notado em um sistema que estava rodando há algumas semanas e que o log de erro do MySQL cresceu para o tamanho indeterminado de mais de 5 GB. Olhando para o log, quase todas as linhas escritas para ele eram um aviso sobre uma "declaração insegura gravada no log binário".

Agora, não controlo a aplicação que está a utilizar a base de dados, pelo que não posso tentar tornar estas declarações "seguras". Então, como uma "correção", configurei o binlog_format para MIXED , em vez de STATEMENT . Isso diz ao MySQL para usar o registro STATEMENT onde for possível, mas voltar para o registro ROW com instruções inseguras. Fazer isso manteve com sucesso o tamanho do log de erros em um tamanho gerenciável.

NO ENTANTO, agora os logs binários estão crescendo muito mais rápido do que costumavam (vi 3 GB de arquivos de log em apenas algumas horas hoje), presumivelmente porque agora o sistema está gravando no log para cada linha que é afetada (para instruções "inseguras"), e para instruções que afetam um grande número de linhas, bem, você obtém a imagem.

Então, eu me encontro entre uma rocha e um lugar difícil. Se eu usar o formato STATEMENT , os logs binários serão gerenciáveis, mas recebo um número insano de avisos no log de erros. Se eu usar o formato MIXED , o log de erros é bom, mas os logs binários crescem rápido o suficiente para possivelmente preencher a partição em até um único dia.

O que me leva à minha pergunta: qual é exatamente a repercussão dessas declarações "inseguras"? Como eu disse, não tenho nenhuma replicação acontecendo, então não preciso me preocupar com um servidor sendo exatamente igual a outro. Eu simplesmente preciso garantir que, no caso em que precisamos restaurar a partir de um backup, todos os dados estão lá. O registro de instruções "inseguras" causará perda de dados ou haverá apenas uma situação em que determinadas linhas estão em ordens diferentes (e possivelmente com ids de chave primária diferentes)? Se não for grande coisa, posso desabilitar avisos no log de erros (embora isso pareça pesado).

Caso contrário, posso ser forçado a eliminar completamente o registro binário e confiar apenas em backups completos potencialmente desatualizados para o plano de recuperação.

Algum conselho nesta situação?

    

Answer 1

O formato de replicação baseado em linha na verdade usa mais espaço em disco que o baseado em instruções. Isso é simples porque no log binário você teria todos os dados que foram inseridos / atualizados não apenas na instrução. Portanto, se uma instrução disser para inserir 100 linhas, se binlog_format = STATEMENT inserir apenas uma instrução, mas se ROW realmente conter todas as entradas.

Portanto, para economizar espaço em disco, é necessário reverter para STATEMENT. Em um modo mix, o mysql tentará escrever como um STATEMENT no log binário, mas no caso de declarações inseguras, ele será revertido para ROW. No seu caso, parece que você tem muitas declarações inseguras, então você acaba com binlogs baseados em ROW.

Você pode fazer algumas coisas

• deixe-o como ROW e implemente um trabalho de limpeza que limpará os logs após um período de tempo. Para isso, é necessário calcular o que é apropriado para o seu sistema. Antes de excluir os registros, você deve copiá-los em outro lugar para não perdê-los.

• implemente a replicação através de um segundo sistema e tenha novamente um trabalho de limpeza no mestre (verifique se o escravo está em sincronia ou se você pode perder dados)

• dê uma boa olhada nas declarações potencialmente inseguras, isso pode exigir colaboração com os desenvolvedores do seu aplicativo.