MySQL Binary Logs (BINLOGS), como apagar, replicar ou recuperar dados

Introdução

Os binary logs do MySQL são uma parte crucial do sistema de gerenciamento de banco de dados, desempenhando um papel fundamental na replicação e recuperação de dados. Estes arquivos registram todas as alterações feitas no banco de dados, permitindo a reconstrução precisa do estado do banco em um determinado momento. Neste artigo, exploraremos em detalhes os binary logs do MySQL, incluindo sua localização, gerenciamento e otimização.

O que são Binary Logs?

Os binary logs são arquivos que contêm um registro de todas as modificações feitas no banco de dados MySQL. Eles armazenam eventos que descrevem alterações como inserções, atualizações e exclusões de dados, bem como mudanças na estrutura do banco de dados. Esses logs são essenciais para:

1. Replicação: Permitem que servidores replica reproduzam as alterações feitas no servidor principal.
2. Recuperação de dados: Possibilitam a restauração do banco de dados para um ponto específico no tempo.

Localização dos Binary Logs

No Ubuntu, por padrão, os binary logs do MySQL são geralmente armazenados no diretório /var/lib/mysql. No entanto, a localização exata pode variar dependendo da configuração do seu sistema. Para confirmar o local dos binary logs, você pode usar o seguinte comando SQL:

SHOW VARIABLES LIKE 'log_bin_basename';

Este comando retornará o caminho base para os arquivos de binary log.

Habilitando Binary Logs

Para habilitar os binary logs no MySQL, você precisa editar o arquivo de configuração principal. No Ubuntu, este arquivo geralmente é /etc/mysql/mysql.conf.d/mysqld.cnf. Adicione ou modifique as seguintes linhas:

log_bin = /var/log/mysql/mysql-bin.log
expire_logs_days = 10
max_binlog_size = 100M

Após fazer essas alterações, reinicie o serviço MySQL:

sudo systemctl restart mysql

Gerenciando Binary Logs

Visualizando Binary Logs

Para visualizar o conteúdo dos binary logs, você pode usar a ferramenta mysqlbinlog. Por exemplo:

mysqlbinlog /var/lib/mysql/mysql-bin.000001

Este comando exibirá o conteúdo do arquivo de log binário especificado.

Purgando Binary Logs

Para evitar que os binary logs consumam muito espaço em disco, é importante purgá-los regularmente. Existem várias maneiras de fazer isso:

Usando o comando SQL PURGE BINARY LOGS:

PURGE BINARY LOGS TO 'mysql-bin.000010';

Este comando apagará todos os logs binários até o arquivo especificado.

Usando a data:

PURGE BINARY LOGS BEFORE '2025-03-04 11:51:00';

Este comando apagará todos os logs binários anteriores à data e hora especificadas.

Automatizando a Purga de Logs

Para automatizar o processo de purga, você pode configurar o parâmetro expire_logs_days ou binlog_expire_logs_seconds no arquivo de configuração do MySQL. Por exemplo:

binlog_expire_logs_seconds = 604800  # 7 dias

Isso fará com que o MySQL automaticamente remova logs binários mais antigos que 7 dias.

Otimizando o Uso de Espaço dos Binary Logs

Limitando o Espaço Total

O MySQL 8.4 introduziu a variável binlog_space_limit, que permite definir um limite superior para o tamanho total de todos os binary logs:

SET GLOBAL binlog_space_limit = 50G;

Este comando limita o espaço total dos binary logs a 50 GB. Quando o limite é atingido, os logs mais antigos são purgados automaticamente.

Ajustando o Tamanho Máximo do Arquivo

Você pode controlar o tamanho máximo de cada arquivo de binary log usando a variável max_binlog_size:

max_binlog_size = 100M

Isso limitará cada arquivo de log binário a um tamanho máximo de 100 MB.

Formato dos Binary Logs

O MySQL suporta três formatos de binary log: STATEMENT, ROW, e MIXED. A partir do MySQL 8.4, o formato padrão é ROW. Você pode alterar o formato usando:

SET GLOBAL binlog_format = 'MIXED';

Cada formato tem suas vantagens e desvantagens:

• STATEMENT: Registra as declarações SQL executadas.
• ROW: Registra as mudanças reais nas linhas do banco de dados.
• MIXED: Usa STATEMENT por padrão, mas muda para ROW quando necessário para garantir a replicação segura.

Monitoramento e Análise

Para monitorar efetivamente seus binary logs, você pode usar comandos como:

SHOW BINARY LOGS;
SHOW MASTER STATUS;

Estes comandos fornecem informações sobre os arquivos de log binário existentes e o status atual do logging.

Para análise mais detalhada, você pode criar scripts personalizados. Por exemplo, este script em bash resume as informações dos binary logs:

#!/bin/bash
BINLOG_FILE="mysqld-bin.000035"
START_TIME="2025-03-04 11:30:00"
STOP_TIME="2025-03-04 12:00:00"

mysqlbinlog --base64-output=decode-rows -vv \
  --start-datetime="${START_TIME}" \
  --stop-datetime="${STOP_TIME}" \
  ${BINLOG_FILE} | awk '
  BEGIN {
    s_type=""; s_count=0; count=0;
    insert_count=0; update_count=0; delete_count=0; flag=0;
  }
  {
    if(match($0, /#15.*Table_map:.*mapped to number/)) {
      printf "Timestamp : " $1 " " $2 " Table : " $(NF-4);
      flag=1
    } else if (match($0, /(### INSERT INTO .*..*)/)) {
      count++; insert_count++; s_type="INSERT"; s_count++;
    } else if (match($0, /(### UPDATE .*..*)/)) {
      count++; update_count++; s_type="UPDATE"; s_count++;
    } else if (match($0, /(### DELETE FROM .*..*)/)) {
      count++; delete_count++; s_type="DELETE"; s_count++;
    } else if (match($0, /^(# at) /) && flag==1 && s_count>0) {
      print " Query Type : "s_type " " s_count " row(s) affected";
      s_type=""; s_count=0;
    } else if (match($0, /^(COMMIT)/)) {
      print "[Transaction total : " count " Insert(s) : " insert_count " Update(s) : " update_count " Delete(s) : " delete_count "] \n+----------------------+----------------------+----------------------+----------------------+";
      count=0; insert_count=0; update_count=0; delete_count=0; s_type=""; s_count=0; flag=0
    }
  }'

Este script fornece um resumo detalhado das operações registradas nos binary logs, incluindo contagens de inserções, atualizações e exclusões.

Conclusão

Os binary logs do MySQL são uma ferramenta poderosa para garantir a integridade e a recuperabilidade dos dados. Compreender como gerenciá-los e otimizá-los é crucial para manter um ambiente de banco de dados eficiente e confiável. Ao implementar as práticas discutidas neste artigo, como purga regular, limitação de espaço e monitoramento adequado, você pode garantir que seus binary logs funcionem de maneira eficaz sem comprometer o desempenho ou o espaço em disco do seu sistema MySQL.