Aula 20: Backup e restauração — mysqldump, mysqlpump e binlog

Nesta aula do curso MySQL — Do Zero ao Avançado, vamos dominar um dos pilares mais críticos da administração de bancos de dados: backup e restauração. Não importa se você gerencia um ambiente de produção com milhares de requisições por segundo ou um servidor interno com poucos gigabytes de dados; a ausência de uma política de backup confiável é uma falha inaceitável para qualquer profissional de TI. A perda de dados pode significar prejuízos financeiros, danos à reputação e até a inviabilização de um negócio. Por isso, nesta aula você aprenderá a utilizar as três ferramentas nativas mais importantes do ecossistema MySQL: mysqldump, mysqlpump e o binlog (binary log), cada uma com seu papel específico em uma estratégia robusta de backup e restauração.

Ao final desta aula, você será capaz de executar backups lógicos completos e consistentes de bancos de dados MySQL/MariaDB, restaurar dados de forma segura e controlada, configurar o binlog para recuperação point-in-time (PITR) e automatizar rotinas de backup com agendamento. Vamos abordar cenários reais, incluindo a restauração de um banco de dados após uma falha catastrófica, e mostraremos passo a passo cada comando, cada flag e cada saída esperada. Este conhecimento é fundamental para quem deseja atuar como DBA, administrador de sistemas, engenheiro DevOps ou consultor de infraestrutura.

Antes de começar, é importante destacar que uma estratégia de backup e restauração não se resume a executar um comando e salvar um arquivo. Ela envolve planejamento de janelas de backup, definição de objetivos de recuperação (RPO e RTO), teste periódico de restauração, armazenamento externo e documentação. Em nossos projetos na JRT Technology Solutions, nossos especialistas utilizam diariamente as ferramentas que você verá nesta aula para garantir a integridade e a disponibilidade dos dados de clientes de segmentos como fintechs, e-commerce e telecomunicações.

Para acompanhar esta aula, você precisará de um servidor MySQL 8.0 ou superior (ou MariaDB 10.6+) em funcionamento, acesso a um usuário com privilégios administrativos (SUPER ou BACKUP ADMIN) e espaço em disco suficiente para os arquivos de backup. Todos os procedimentos foram testados em ambientes Ubuntu/Debian e CentOS/RHEL/Rocky Linux, e as diferenças entre essas distribuições serão apontadas ao longo do texto. Recomendamos também que você tenha concluído as aulas anteriores do curso, pois partiremos do pressuposto de que você já entende os conceitos básicos de bancos de dados, tabelas, índices e usuários.

O que você vai aprender nesta aula

Esta aula foi desenhada para fornecer uma visão completa e prática sobre backup e restauração no MySQL. Ao final do estudo, você dominará os seguintes tópicos:

• Diferenciar os tipos de backup (lógico, físico, online, offline) e escolher o método adequado para cada cenário;
• Executar backups lógicos completos com mysqldump, incluindo as flags mais importantes para consistência e segurança;
• Utilizar o mysqlpump para backups paralelos, com compressão nativa e filtros avançados;
• Configurar o binlog (binary log) para registrar todas as modificações e habilitar recuperação point-in-time (PITR);
• Realizar restaurações completas e parciais a partir de arquivos de dump;
• Aplicar eventos de binlog para restaurar dados até um instante específico no tempo;
• Automatizar backups com cron (Linux) e planejar políticas de retenção;
• Identificar e resolver erros comuns durante processos de backup e restauração.

Pré-requisitos e Ambiente

Para tirar o máximo proveito desta aula, certifique-se de que o seu ambiente atende aos seguintes pré-requisitos:

• Servidor MySQL 8.0+ ou MariaDB 10.6+ instalado e em execução (local ou remoto);
• Acesso via terminal com um usuário que possua privilégios SELECT, LOCK TABLES, RELOAD, SUPER/BACKUP ADMIN e SHOW VIEW;
• Ferramentas de cliente MySQL instaladas (mysqldump, mysqlpump, mysqlbinlog);
• Espaço em disco livre (pelo menos o dobro do tamanho do banco de dados para dumps intermediários);
• Conhecimentos básicos de comandos Linux e de SQL (consultas, usuários, privilégios).

Se você estiver usando Ubuntu/Debian, pode instalar as ferramentas com o comando abaixo:

# Ubuntu/Debian: instalar cliente MySQL e ferramentas de backup
sudo apt update
sudo apt install -y mysql-client-core-8.0

# Verificar versões instaladas
mysqldump --version
mysqlpump --version
mysqlbinlog --version

Em CentOS/RHEL/Rocky Linux, o pacote é semelhante, mas o gerenciador de pacotes é diferente:

# CentOS/RHEL/Rocky Linux: instalar cliente MySQL e ferramentas de backup
sudo dnf install -y mysql

# Verificar versões instaladas
mysqldump --version
mysqlpump --version
mysqlbinlog --version

A saída esperada para a verificação de versão deve ser algo como:

mysqldump  Ver 8.0.36 for Linux on x86_64 (MySQL Community Server - GPL)
mysqlpump Ver 8.0.36 for Linux on x86_64 (MySQL Community Server - GPL)
mysqlbinlog Ver 8.0.36 for Linux on x86_64 (MySQL Community Server - GPL)

Observação: Se o seu servidor for MariaDB, o mysqlpump pode não estar disponível — trata-se de uma ferramenta mais presente no MySQL. Nesse caso, concentre-se em mysqldump e mysqlbinlog. Confirme também se o daemon do MySQL está acessível:

# Verificar conexão e privilégios do usuário
mysql -u root -p -e "SELECT VERSION(); SHOW GRANTS FOR CURRENT_USER();"

+-----------+
| VERSION() |
+-----------+
| 8.0.36    |
+-----------+
Grants for root@localhost
GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION

Agora que o ambiente está pronto, vamos entender a teoria por trás dos backups e, em seguida, colocar a mão na massa.

Fundamentos de Backup e Restauração no MySQL

Antes de executar qualquer comando, é imprescindível compreender os conceitos que sustentam uma estratégia de backup e restauração. No universo MySQL, os backups podem ser classificados em duas grandes categorias: lógicos e físicos. O backup lógico gera um conjunto de instruções SQL (como CREATE TABLE e INSERT) que, quando executadas, recriam o banco de dados do zero. O mysqldump e o mysqlpump são as ferramentas nativas para esse tipo de backup. Já o backup físico copia os arquivos binários do banco (como .ibd e .frm), exigindo que o servidor esteja parado ou que se utilize uma ferramenta especializada, como o Percona XtraBackup.

Outra classificação importante diz respeito à consistência do backup. Um backup consistente é aquele que representa um estado coerente do banco de dados em um único instante, sem incluir transações pela metade. Para garantir consistência em backups online (com o servidor em execução), o MySQL utiliza mecanismos como --single-transaction, que inicia uma transação consistente no InnoDB, e --lock-all-tables, que bloqueia todas as tabelas durante o processo. O binlog (binary log) também desempenha um papel crucial, pois registra cada modificação aplicada ao banco, permitindo a restauração point-in-time: você pode restaurar um backup completo e, em seguida, aplicar os eventos do binlog até o momento imediatamente anterior à falha.

Para planejar uma política de backup e restauração, você deve definir duas métricas-chave: RPO (Recovery Point Objective) e RTO (Recovery Time Objective). O RPO define a quantidade máxima de dados que você está disposto a perder em caso de desastre — por exemplo, se você realiza backups diários, seu RPO é de até 24 horas. Para reduzir o RPO a minutos ou segundos, é necessário utilizar backups incrementais ou binlog. O RTO, por sua vez, define o tempo máximo aceitável para restaurar o serviço após a falha. Backups físicos geralmente oferecem RTO menor que backups lógicos, pois evitam a re-execução de instruções SQL.

No contexto desta aula, focaremos nas ferramentas mysqldump, mysqlpump e binlog, que juntas formam a base de quase toda estratégia de backup e restauração no MySQL. O mysqldump é a ferramenta clássica e universal, compatível com praticamente todas as versões do MySQL e MariaDB. O mysqlpump foi introduzido no MySQL 5.7 como evolução do primeiro, oferecendo paralelismo, compressão nativa e filtros mais refinados. O binlog, por sua vez, não é uma ferramenta de backup em si, mas um registro contínuo que possibilita a recuperação de dados entre dois backups.

Em nossos projetos na JRT Technology Solutions, recomendamos sempre a combinação de backups lógicos semanais (com mysqldump ou mysqlpump) com backups incrementais diários via binlog, além de um backup físico mensal para grandes volumes de dados. Essa abordagem em camadas garante RPO baixo e flexibilidade na restauração, atendendo inclusive a requisitos de auditoria e conformidade.

Backup e Restauração com mysqldump — Passo a Passo Completo

O mysqldump é o utilitário mais tradicional para backup e restauração lógica no MySQL. Ele se conecta ao servidor, lê os dados e gera um arquivo de texto com instruções SQL. A grande vantagem é a portabilidade: um dump gerado em uma versão pode, na maioria dos casos, ser importado em outra versão do MySQL ou até em MariaDB, desde que se respeitem as compatibilidades de sintaxe. Neste passo a passo, você aprenderá a criar um backup completo de todos os bancos de dados, com as flags essenciais para garantir consistência e segurança.

Para este exemplo, utilizaremos um servidor com os bancos employees, sales e inventory. O comando a seguir cria um dump completo no diretório /backups/mysql:

# 1. Criar o diretório de destino do backup
sudo mkdir -p /backups/mysql
sudo chown $USER:$USER /backups/mysql

# 2. Executar o backup completo com mysqldump
mysqldump \
  --user=root \
  --password \
  --all-databases \
  --single-transaction \
  --routines \
  --triggers \
  --events \
  --hex-blob \
  --source-data=2 \
  --set-gtid-purged=OFF \
  --result-file=/backups/mysql/full_backup_2026-08-23.sql

# 3. Verificar o tamanho e as primeiras linhas do arquivo gerado
ls -lh /backups/mysql/full_backup_2026-08-23.sql
head -n 30 /backups/mysql/full_backup_2026-08-23.sql

Explicação linha por linha:

• --user=root: define o usuário de conexão. Em produção, crie um usuário dedicado com privilégios de backup.
• --password: solicita a senha interativamente (não a registre no histórico do shell).
• --all-databases: inclui todos os bancos de dados, inclusive mysql, information_schema e performance_schema (este último geralmente não contém dados reais, mas a flag é aceita).
• --single-transaction: inicia uma transação com nível de isolamento REPEATABLE READ antes de ler os dados. Isso garante uma visão consistente do banco sem bloquear tabelas InnoDB, ideal para ambientes de produção.
• --routines: inclui stored procedures e funções.
• --triggers: inclui triggers. Por padrão, triggers são incluídas, mas é boa prática explicitá-las.
• --events: inclui eventos agendados (event scheduler).
• --hex-blob: codifica dados binários (BLOB) em hexadecimal para evitar corrupção durante importação.
• --source-data=2: (ou --master-data=2 em versões antigas) escreve no dump um comentário com o nome do binlog e a posição exata no momento do backup. Isso é fundamental para a restauração point-in-time que faremos mais adiante.
• --set-gtid-purged=OFF: desativa a inclusão de instruções de GTID no dump. Muitos administradores preferem OFF para evitar problemas com replicação; se você usar replicação GTID, pode optar por AUTO (padrão).
• --result-file=/backups/mysql/full_backup_2026-08-23.sql: define o arquivo de saída, evitando redirecionamento com > e suprimindo mensagens de progresso.

A saída esperada após a execução do comando deve ser silenciosa (por causa de --result-file), mas a verificação subsequente mostrará o tamanho e as primeiras linhas do dump:

-rw-r--r-- 1 user user 248M ago 23 15:42 /backups/mysql/full_backup_2026-08-23.sql

-- MySQL dump 10.13  Distrib 8.0.36, for Linux (x86_64)
--
-- Host: localhost    Database: employees
-- ------------------------------------------------------
-- Server version	8.0.36

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!50503 SET NAMES utf8mb4 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
--
-- Position to start replication or point-in-time recovery from
--

-- CHANGE MASTER TO MASTER_LOG_FILE='binlog.000015', MASTER_LOG_POS=1572345;

Observe a linha final: ela indica o arquivo binlog e a posição (binlog.000015 na posição 1572345) que correspondem ao instante em que o backup foi tirado. Esse dado é essencial para aplicar eventos posteriores e recuperar dados até um ponto específico.

Para restaurar esse backup em um servidor íntegro (ou em um banco de teste), utilize o cliente mysql com redirecionamento de entrada. É recomendável desligar temporariamente o FOREIGN_KEY_CHECKS e garantir que o arquivo seja executado em uma sessão com privilégios administrativos:

# Restaurar o backup completo em outro servidor (ou no mesmo, se for teste)
mysql --user=root --password < /backups/mysql/full_backup_2026-08-23.sql

# Verificar se a restauração foi bem-sucedida consultando os bancos
mysql -u root -p -e "SHOW DATABASES;"

+--------------------+
| Database           |
+--------------------+
| employees          |
| information_schema |
| inventory          |
| mysql              |
| performance_schema |
| sales              |
| sys                |
+--------------------+

Se você precisar restaurar apenas um banco a partir de um dump completo (que contém vários bancos), use a opção --one-database no cliente mysql. Por exemplo, para restaurar somente o banco sales:

# Restaurar somente o banco 'sales' a partir do dump completo
mysql --user=root --password --one-database sales < /backups/mysql/full_backup_2026-08-23.sql

Esse comando é extremamente útil em cenários de recuperação parcial, quando apenas um banco foi corrompido ou sofreu uma exclusão acidental de dados. Lembre-se de que a restauração sobrescreve as tabelas existentes, portanto, use com cautela em ambientes de produção.

Backup e Restauração com mysqlpump — Paralelismo e Filtros Avançados

O mysqlpump é a evolução do mysqldump, introduzido no MySQL 5.7. Suas principais vantagens são o paralelismo na leitura das tabelas, a compressão nativa (LZ4 ou ZLIB), o processamento mais eficiente de grandes bancos de dados e a possibilidade de incluir ou excluir objetos com sintaxe avançada. Em nossos projetos na JRT Technology Solutions, utilizamos o mysqlpump quando precisamos reduzir o tempo total de backup e restauração de bancos com dezenas ou centenas de gigabytes.

O comando básico para um backup completo com mysqlpump é semelhante ao do mysqldump, mas adicionamos as flags --default-parallelism e --compress-output. Vamos gerar um backup com 4 threads paralelas e compressão LZ4:

# Backup completo com mysqlpump usando paralelismo e compressão
mysqlpump \
  --user=root \
  --password \
  --all-databases \
  --single-transaction \
  --default-parallelism=4 \
  --compress-output=LZ4 \
  --routines \
  --triggers \
  --events \
  --hex-blob \
  --result-file=/backups/mysql/full_backup_2026-08-23_pump.lz4

# Verificar o tamanho do arquivo comprimido
ls -lh /backups/mysql/full_backup_2026-08-23_pump.lz4

Explicação das novas opções:

• --default-parallelism=4: define que até 4 tabelas serão processadas simultaneamente. O valor ideal depende do número de CPUs e do subsistema de I/O do servidor; em máquinas com 8 núcleos, 4 threads é um bom ponto de partida.
• --compress-output=LZ4: comprime o dump com o algoritmo LZ4, que é muito rápido. Você pode substituir por ZLIB para maior compressão (mais lento).

Após a execução, a saída esperada é semelhante a:

Dump progress: 1/10 tables, 25000/1000000 rows
Dump progress: 5/10 tables, 500000/1000000 rows
Dump progress: 10/10 tables, 1000000/1000000 rows
Dump completed in 14123 ms

-rw-r--r-- 1 user user 73M ago 23 15:49 /backups/mysql/full_backup_2026-08-23_pump.lz4

Note que o arquivo comprimido ocupa significativamente menos espaço que o dump textual do exemplo anterior (73 MB versus 248 MB). Para restaurar um arquivo gerado com compressão, você precisa descomprimir o dump antes de passá-lo ao cliente mysql. O mysqlpump não restaura arquivos comprimidos diretamente; o processo é:

# 1. Descomprimir o dump LZ4 (requer o utilitário lz4 instalado)
# Ubuntu/Debian: sudo apt install -y liblz4-tool
# CentOS/RHEL/Rocky: sudo dnf install -y lz4
lz4 -d /backups/mysql/full_backup_2026-08-23_pump.lz4 /backups/mysql/full_backup_2026-08-23_pump.sql

# 2. Restaurar o dump descomprimido
mysql --user=root --password < /backups/mysql/full_backup_2026-08-23_pump.sql

# 3. Verificar se a restauração foi concluída com sucesso
mysql -u root -p -e "SELECT COUNT(*) AS total_tables FROM information_schema.tables WHERE table_schema NOT IN ('information_schema', 'performance_schema', 'sys');"

+--------------+
| total_tables |
+--------------+
|          235 |
+--------------+

O mysqlpump também oferece filtros poderosos com as opções --include-databases, --exclude-tables e expressões LIKE. Por exemplo, para fazer backup de apenas dois bancos (sales e inventory) e excluir tabelas de log:

# Backup seletivo com mysqlpump
mysqlpump \
  --user=root \
  --password \
  --include-databases=sales,inventory \
  --exclude-tables=sales.audit_log,inventory.raw_data \
  --single-transaction \
  --result-file=/backups/mysql/selective_backup_2026-08-23.sql

Esse recurso é valioso para reduzir o tamanho do backup em bancos que armazenam tabelas temporárias ou de auditoria que não precisam ser restauradas com a mesma prioridade. Lembre-se de documentar quais objetos foram excluídos, para que a equipe de recuperação não seja surpreendida durante um incidente real.

Backup e Restauração Point-in-Time com binlog

O binlog (binary log) é o coração da restauração point-in-time no MySQL. Ele registra todos os eventos que modificam dados — INSERT, UPDATE, DELETE, DDL — na ordem exata em que foram executados. Com um backup completo e a sequência de binlogs, você pode recuperar o banco para qualquer instante entre o backup e o momento do acidente, minimizando a perda de dados.

Antes de usar o binlog para backup e restauração, você precisa ativá-lo. Por padrão, o MySQL 8.0 já vem com o binlog habilitado em muitas distribuições, mas é fundamental verificar e, se necessário, configurar. Vamos verificar o estado atual:

# Verificar se o binlog está ativado e em qual formato
mysql -u root -p -e "SHOW VARIABLES LIKE 'log_bin'; SHOW VARIABLES LIKE 'binlog_format'; SHOW BINARY LOGS;"

+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | ON    |
+---------------+-------+
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW   |
+---------------+-------+
+---------------+-----------+-----------+
| Log_name      | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000014 | 1073741824 | No        |
| binlog.000015 | 456789012 | No        |
+---------------+-----------+-----------+

Se a variável log_bin estiver OFF, você precisará configurá-la no arquivo my.cnf e reiniciar o servidor. Faremos essa configuração detalhada na próxima seção. Agora, vamos simular um cenário de backup e restauração point-in-time completo:

1. Realizar um backup completo consistente (já feito anteriormente com mysqldump). Anote a posição do binlog contida no dump (binlog.000015, posição 1572345).
2. Após o backup, continuar operando o banco normalmente — inserir novos dados, criar tabelas, etc.
3. Simular um acidente — por exemplo, uma instrução DROP DATABASE sales; executada por engano às 16:30 do dia corrente.
4. Restaurar o backup completo do passo 1.
5. Identificar os binlogs gerados após o backup e aplicar os eventos até o instante imediatamente anterior ao acidente.

Vamos detalhar os passos 4 e 5. Primeiro, identifique os arquivos binlog disponíveis no servidor:

# Listar os binlogs atuais
mysql -u root -p -e "SHOW BINARY LOGS;"

+---------------+------------+-----------+
| Log_name      | File_size  | Encrypted |
+---------------+------------+-----------+
| binlog.000014 | 1073741824 | No        |
| binlog.000015 | 987654321  | No        |
| binlog.000016 | 345678901  | No        |
| binlog.000017 | 123456789  | No        |
+---------------+------------+-----------+

Agora, precisamos saber a posição exata em que o acidente ocorreu. Para isso, utilize o mysqlbinlog para examinar os eventos e localizar a instrução DROP DATABASE. Suponha que ela esteja no arquivo binlog.000016:

# Examinar o conteúdo do binlog.000016 para localizar o evento de DROP
mysqlbinlog --no-defaults /var/lib/mysql/binlog.000016 | grep -n -A 5 "DROP DATABASE"

12345:# at 456789
12346:#240823 16:30:00 server id 1  end_log_pos 567890 CRC32 0x9f3a2b1c
12347:# 	Query	thread_id=42	exec_time=0	error_code=0
12348:USE sales/*!*/;
12349:DROP DATABASE `sales`;

O comando mysqlbinlog converte o binlog binário em texto legível. O evento DROP DATABASE aparece na linha 12349. Para restaurar os dados até antes desse evento, você pode aplicar os eventos do binlog até a posição anterior ao evento (end_log_pos do evento anterior) ou usar filtros de tempo com a opção --stop-datetime. A abordagem mais precisa é usar a posição:

# Restaurar o backup completo primeiro (se ainda não foi feito)
mysql --user=root --password < /backups/mysql/full_backup_2026-08-23.sql

# Aplicar os eventos do binlog desde a posição do backup (1572345) até antes do DROP (posição 567890)
mysqlbinlog --no-defaults \
  --start-position=1572345 \
  --stop-position=567890 \
  /var/lib/mysql/binlog.000015 /var/lib/mysql/binlog.000016 | mysql --user=root --password

Explicação:

• --start-position=1572345: posição registrada no dump (início das mudanças após o backup).
• --stop-position=567890: posição do evento DROP DATABASE. Os eventos são aplicados até a posição anterior, ou seja, tudo antes do comando destrutivo.
• A combinação de múltiplos arquivos binlog no mesmo comando garante que toda a sequência seja reproduzida na ordem correta.

Após a execução, verifique se o banco sales foi restaurado e se contém os dados inseridos antes do acidente:

# Verificar se o banco sales existe e possui os registros esperados
mysql -u root -p -e "USE sales; SHOW TABLES; SELECT COUNT(*) FROM orders;"

+-------------------+
| Tables_in_sales   |
+-------------------+
| customers         |
| orders            |
| order_items       |
+-------------------+
+----------+
| COUNT(*) |
+----------+
|   48291  |
+----------+

Esse fluxo demonstra o poder do binlog na restauração point-in-time. Sem ele, você perderia todos os dados gerados entre o backup e o instante do acidente. Em ambientes de produção, é comum manter pelo menos 7 dias de binlogs para permitir recuperações flexíveis.

Configuração Detalhada — binlog e Segurança no my.cnf

Agora que você entendeu a importância do binlog, vamos configurá-lo corretamente no arquivo de configuração do MySQL. Em sistemas Ubuntu/Debian, o arquivo principal é /etc/mysql/mysql.conf.d/mysqld.cnf; em CentOS/RHEL/Rocky Linux, utilize /etc/my.cnf.d/mysql-server.cnf. O conteúdo completo com todos os parâmetros relevantes para uma estratégia robusta de backup e restauração é mostrado a seguir:

# Arquivo: /etc/mysql/mysql.conf.d/mysqld.cnf (Ubuntu/Debian)
# ou /etc/my.cnf.d/mysql-server.cnf (CentOS/RHEL/Rocky)

[mysqld]
# ==========================================
# Configuração do Binary Log (binlog)
# ==========================================

# Ativa o binlog e define o nome base dos arquivos.
# Os arquivos serão armazenados no datadir.
log_bin = /var/lib/mysql/binlog

# Define o formato do binlog: ROW, STATEMENT ou MIXED.
# ROW registra as linhas modificadas, garantindo máxima precisão
# para replicação e point-in-time recovery.
binlog_format = ROW

# Sincroniza o binlog a cada transação. Valor 1 é o mais seguro,
# mas pode impactar desempenho em cargas altas de escrita.
# Para ambientes de produção, mantenha 1.
sync_binlog = 1

# Define por quantos dias os binlogs serão mantidos automaticamente.
# Ajuste conforme sua política de retenção (ex.: 7 dias).
expire_logs_days = 7

# Limita o tamanho máximo de cada arquivo binlog.
# Ao atingir esse limite, o MySQL cria um novo arquivo.
max_binlog_size = 100M

# Garante consistência entre binlog e tabelas InnoDB em caso de crash.
# Também registra as posições de binlog para recuperação segura.
innodb_flush_log_at_trx_commit = 1

# Ativa a gravação de checksums nos eventos do binlog.
binlog_checksum = CRC32

# Permite que o binlog armazene grupos de transações em paralelo.
# Melhora a performance em replicação com multi-threaded slave.
binlog_transaction_dependency_tracking = COMMIT_ORDER

# Evita que o binlog registre eventos sem conexão com o armazenamento real.
# (padrão em MySQL 8.0)
binlog_row_event_max_size = 8192

# ==========================================
# Configurações adicionais para backup
# ==========================================

# Garante que o InnoDB libere os logs para o disco a cada commit,
# reduzindo o risco de perda de dados durante um crash.
innodb_log_file_size = 256M
innodb_log_buffer_size = 64M

# Define o diretório onde o MySQL cria arquivos temporários.
# Em backups com mydumper/mysqldump, certifique-se de que haja espaço.
tmpdir = /tmp

# Permite que o servidor aceite conexões de ferramentas de backup
# de qualquer interface (ajuste conforme sua política de segurança).
bind-address = 0.0.0.0

Explicação dos parâmetros fundamentais:

• log_bin: ativa o binlog e define o caminho/prefixo. Sem essa diretiva, o binlog fica desativado, e você perde a capacidade de PITR.
• binlog_format=ROW: registra as linhas alteradas em vez da instrução SQL. Isso evita inconsistências em funções não determinísticas e é obrigatório para replicação GTID e PITR precisa.
• sync_binlog=1: força a sincronização do binlog a cada transação. Em caso de falha de energia, você não perde eventos de binlog que ainda estavam em memória. O custo é um leve overhead de I/O.
• expire_logs_days=7: remove automaticamente binlogs com mais de 7 dias. Ajuste conforme o espaço em disco e a janela de recuperação desejada.
• max_binlog_size=100M: controla a rotação dos arquivos. O valor mínimo é 4096 bytes; o padrão é 1 GB.
• innodb_flush_log_at_trx_commit=1: garante durabilidade total das transações InnoDB, essencial para que o binlog e as tabelas estejam consistentes.

Após editar o arquivo, reinicie o serviço para aplicar as alterações. Os comandos variam conforme a distribuição:

# Ubuntu/Debian
sudo systemctl restart mysql
sudo systemctl status mysql

# CentOS/RHEL/Rocky Linux
sudo systemctl restart mysqld
sudo systemctl status mysqld

● mysql.service - MySQL Community Server
     Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)
     Active: active (running) since Sun 2026-08-23 16:45:12 -03; 18s ago
   Main PID: 12584 (mysqld)
     Status: "Server is operational"
      Tasks: 38 (limit: 2304)
     Memory: 352.6M
        CPU: 1.482s
     CGroup: /system.slice/mysql.service
             └─12584 /usr/sbin/mysqld

Lembre-se de que alterações no arquivo de configuração exigem privilégios de superusuário (sudo). Nunca edite o arquivo sem antes criar um backup dele, pois erros de sintaxe podem impedir o MySQL de iniciar.

Verificando a Instalação / Testando a Configuração

Após configurar o binlog e as ferramentas de backup, é obrigatório testar todo o fluxo de backup e restauração para garantir que tudo funciona conforme o esperado. Esta seção apresenta os comandos de verificação e suas saídas corretas. Realize esses testes em um ambiente de validação antes de depender deles em produção.

Primeiro, confirme que o binlog está ativo, com o formato ROW, e liste os arquivos existentes:

# Verificar status do binlog
mysql -u root -p -e "SHOW VARIABLES LIKE 'log_bin'; SHOW VARIABLES LIKE 'binlog_format'; SHOW BINARY LOGS; SHOW MASTER STATUS;"

+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | ON    |
+---------------+-------+
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW   |
+---------------+-------+
+---------------+-----------+-----------+
| Log_name      | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000018 | 104857600 | No        |
| binlog.000019 | 52428800  | No        |
+---------------+-----------+-----------+
+---------------+-----------+-----------+
| File          | Position  | Binlog_Do_DB | Binlog_Ignore_DB |
+---------------+-----------+---------------+------------------+
| binlog.000019 | 1024      |               |                  |
+---------------+-----------+-----------+------------------+

Agora, execute um backup completo de teste e verifique se o arquivo foi criado com sucesso e contém a instrução de posição do binlog:

# Criar um backup de teste
mysqldump \
  --user=root \
  --password \
  --all-databases \
  --single-transaction \
  --source-data=2 \
  --set-gtid-purged=OFF \
  --result-file=/backups/mysql/test_backup_verification.sql

# Verificar se o arquivo existe e contém a linha com MASTER_LOG_FILE
ls -lh /backups/mysql/test_backup_verification.sql
grep "MASTER_LOG_FILE" /backups/mysql/test_backup_verification.sql

-rw-r--r-- 1 user user 12M ago 23 17:05 /backups/mysql/test_backup_verification.sql
-- CHANGE MASTER TO MASTER_LOG_FILE='binlog.000019', MASTER_LOG_POS=512;

Para testar a restauração, crie um banco de dados temporário e importe o backup nele. Esse procedimento valida se o dump é íntegro e se a sintaxe SQL está correta:

# Criar um banco de teste para validação

mysql -u root -p -e "CREATE DATABASE IF NOT EXISTS backup_test;"

# Restaurar o dump no banco de teste (somente para validação)

mysql --user

Quer aprender na prática com especialistas?
A JRT Technology Solutions oferece treinamentos e implementação de MySQL para equipes corporativas.
  

    

    Falar no WhatsApp