O Guia Definitivo do mysqldump: Backup, Restauração e Automação de Banco de Dados MySQL

mysqldump é um utilitário de linha de comando incluído com MySQL e MariaDB que gera backups lógicos serializando objetos de banco de dados e dados como uma sequência de instruções SQL. O arquivo de dump resultante pode recriar um banco de dados idêntico em qualquer servidor compatível, tornando-o a ferramenta padrão da indústria para backups, migrações entre servidores, atualizações de versão e fluxos de trabalho de recuperação de desastres.

Ao contrário de ferramentas de backup físico como Percona XtraBackup ou MySQL Enterprise Backup, o mysqldump opera na camada SQL — ele lê dados ao vivo através do protocolo MySQL e escreve SQL portátil e legível por humanos. Essa portabilidade é seu maior ponto forte e, em escala, sua principal limitação.

O Que o mysqldump Realmente Faz Internamente

Quando você invoca o mysqldump, o cliente conecta-se ao servidor MySQL, consulta o esquema de informações e o dicionário de dados, e emite um fluxo de instruções `CREATE DATABASE`, `CREATE TABLE`, `INSERT` e DDL para a saída padrão. Você redireciona esse fluxo para um arquivo, um pipe ou um utilitário de compressão.

Para tabelas InnoDB com `–single-transaction`, o mysqldump abre uma transação de leitura repetível antes de ler qualquer dado. Isso fornece um snapshot consistente em um ponto no tempo sem adquirir bloqueios de leitura globais — o banco de dados permanece totalmente gravável durante o dump. Para tabelas MyISAM, esse mecanismo não existe; o mysqldump recorre a `FLUSH TABLES WITH READ LOCK`, que bloqueia brevemente as gravações.

Compreender essa distinção é fundamental antes de escolher o mysqldump para cargas de trabalho em produção. Se o seu esquema mistura tabelas InnoDB e MyISAM, `–single-transaction` sozinho é insuficiente — você precisará de `–lock-all-tables` ou de uma janela de manutenção.

Pré-requisitos e Privilégios Necessários

Antes de executar qualquer comando de dump, verifique o seguinte:

• MySQL ou MariaDB está instalado e acessível (socket local ou TCP/IP).
• O utilizador de backup possui os privilégios mínimos necessários:
• `SELECT` em todas as tabelas de destino
• `LOCK TABLES` (necessário a menos que `–single-transaction` seja usado exclusivamente com InnoDB)
• `SHOW VIEW` para incluir views
• `TRIGGER` para incluir triggers
• `PROCESS` ao usar `–single-transaction` no MySQL 8+
• `RELOAD` para `FLUSH TABLES WITH READ LOCK`
• `REPLICATION CLIENT` se precisar de coordenadas de log binário para configuração de replicação

Crie um utilizador de backup dedicado em vez de executar dumps como root:

“`sql

CREATE USER 'backup_user'@'localhost' IDENTIFIED BY 'StrongPassword!';

GRANT SELECT, LOCK TABLES, SHOW VIEW, TRIGGER, PROCESS, RELOAD, REPLICATION CLIENT

ON *.* TO 'backup_user'@'localhost';

FLUSH PRIVILEGES;

“`

Executar o mysqldump como root com uma senha incorporada no comando shell expõe credenciais nas listagens de processos e no histórico do shell — um risco de segurança significativo em qualquer sistema compartilhado ou multiutilizador.

Sintaxe Básica

“`

mysqldump [OPTIONS] database_name [table1 table2 …] > backup_file.sql

“`

Referência Completa de Opções

Opções de Conexão

Opções de Escopo

Opções de Consistência e Bloqueio

Opções de Saída e Conteúdo

mysqldump vs. Métodos Alternativos de Backup

Escolher a estratégia de backup correta depende do tamanho do banco de dados, dos requisitos de RTO/RPO e da infraestrutura. Veja como o mysqldump se compara às alternativas mais comuns:

Para bancos de dados abaixo de 10–20 GB num ambiente de VPS Hosting, o mysqldump continua a ser a solução mais prática e portátil. Além desse limite, as ferramentas de backup físico oferecem janelas de backup e restauração dramaticamente mais rápidas.

Exemplos de Uso Prático

Exemplo 1: Fazer Backup de um Único Banco de Dados

“`bash

mysqldump -u backup_user -p database_name > /backups/database_name_$(date +%F).sql

“`

A substituição `$(date +%F)` acrescenta automaticamente a data ISO (ex., `2025-07-15`) ao nome do arquivo, evitando sobrescritas.

Exemplo 2: Fazer Backup de Múltiplos Bancos de Dados Específicos

“`bash

mysqldump -u backup_user -p –databases app_db analytics_db > /backups/multi_db_backup.sql

“`

O flag `–databases` faz com que o mysqldump emita instruções `CREATE DATABASE` e `USE`, tornando o dump autossuficiente para restauração.

Exemplo 3: Fazer Backup de Todos os Bancos de Dados

“`bash

mysqldump -u backup_user -p –all-databases –events –routines –triggers

> /backups/full_server_$(date +%F).sql

“`

Inclua sempre `–events`, `–routines` e `–triggers` em dumps completos do servidor. Esses objetos são omitidos silenciosamente sem flags explícitos.

Exemplo 4: Backup InnoDB Consistente (Seguro para Produção)

“`bash

mysqldump -u backup_user -p

–single-transaction

–flush-logs

–source-data=2

–routines –triggers –events

database_name > /backups/database_name_$(date +%F).sql

“`

`–flush-logs` rotaciona o log binário no início do dump. `–source-data=2` escreve o nome do arquivo de log binário atual e a posição como um comentário SQL, permitindo a recuperação em ponto no tempo reproduzindo os binlogs subsequentes a partir dessa posição.

Exemplo 5: Backup Comprimido com gzip

“`bash

mysqldump -u backup_user -p database_name | gzip -9 > /backups/database_name_$(date +%F).sql.gz

“`

Para servidores com restrições de CPU, substitua por `pigz` (gzip paralelo) para utilizar múltiplos núcleos:

“`bash

mysqldump -u backup_user -p database_name | pigz -9 > /backups/database_name_$(date +%F).sql.gz

“`

Exemplo 6: Backup Apenas do Esquema (Estrutura Sem Dados)

“`bash

mysqldump -u backup_user -p –no-data database_name > /backups/schema_only.sql

“`

Útil para controlar versões do seu esquema no Git ou implementar num ambiente de staging sem copiar dados de produção.

Exemplo 7: Backup Apenas de Dados (Sem Esquema)

“`bash

mysqldump -u backup_user -p –no-create-info database_name > /backups/data_only.sql

“`

Use isto quando o esquema de destino já existe e você só precisa de popular ou atualizar dados.

Exemplo 8: Fazer Backup de uma Única Tabela

“`bash

mysqldump -u backup_user -p database_name orders > /backups/orders_table_$(date +%F).sql

“`

Exemplo 9: Exportar um Subconjunto Filtrado de Linhas

“`bash

mysqldump -u backup_user -p database_name orders

–where="created_at >= '2025-01-01' AND status='completed'"

> /backups/orders_2025_completed.sql

“`

A opção `–where` é subutilizada mas extremamente poderosa para exportações parciais, arquivamento de dados e depuração de conjuntos de registos específicos.

Exemplo 10: Excluir Tabelas Específicas

“`bash

mysqldump -u backup_user -p database_name

–ignore-table=database_name.cache

–ignore-table=database_name.sessions

> /backups/database_name_no_cache.sql

“`

Excluir tabelas grandes e efémeras (caches, armazenamentos de sessão, tabelas de log) pode reduzir o tamanho e a duração do dump em uma ordem de magnitude.

Exemplo 11: Incluindo Procedimentos Armazenados, Funções e Triggers

“`bash

mysqldump -u backup_user -p –routines –triggers –events database_name > /backups/full_backup.sql

“`

Exemplo 12: Backup de Banco de Dados Remoto

“`bash

mysqldump -u backup_user -p -h 192.168.1.100 -P 3306 database_name

“`

Ao fazer backup de um servidor remoto, o tráfego atravessa a rede sem encriptação por padrão. Adicione os flags `–ssl-ca`, `–ssl-cert` e `–ssl-key` ou faça um túnel através de SSH:

“`bash

ssh user@remote-server "mysqldump -u backup_user -p database_name | gzip"

> /backups/remote_db_$(date +%F).sql.gz

“`

Restaurar um Backup mysqldump

Restaurar um Único Banco de Dados

“`bash

mysql -u root -p database_name < /backups/database_name_2025-07-15.sql

“`

Se o banco de dados de destino ainda não existir, crie-o primeiro:

“`bash

mysql -u root -p -e "CREATE DATABASE database_name CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;"

mysql -u root -p database_name < /backups/database_name_2025-07-15.sql

“`

Restaurar Todos os Bancos de Dados a partir de um Dump Completo do Servidor

“`bash

mysql -u root -p < /backups/full_server_2025-07-15.sql

“`

Como `–all-databases` incorpora instruções `CREATE DATABASE` e `USE`, não é necessário nenhum argumento de banco de dados de destino.

Restaurar a partir de um Backup Comprimido

“`bash

gunzip < /backups/database_name_2025-07-15.sql.gz | mysql -u root -p database_name

“`

Ou usando substituição de processo:

“`bash

mysql -u root -p database_name < <(gunzip -c /backups/database_name_2025-07-15.sql.gz)

“`

Restaurar uma Única Tabela a partir de um Dump Completo do Banco de Dados

Este é um cenário operacional comum que o arquivo de dump original torna não trivial. Use `sed` ou `grep` para extrair a secção relevante:

“`bash

sed -n '/^– Table structure for table `orders`/,/^– Table structure for table `/p'

backup_file.sql | head -n -1 | mysql -u root -p database_name

“`

Alternativamente, use `mysql_extract_table.sh` ou importe para um banco de dados temporário e copie a tabela:

“`bash

mysql -u root -p temp_restore < backup_file.sql

mysql -u root -p -e "INSERT INTO database_name.orders SELECT * FROM temp_restore.orders;"

“`

Recuperação em Ponto no Tempo Usando Logs Binários

Se o seu dump foi feito com `–source-data=2` e o log binário está ativado, você pode recuperar para qualquer ponto após o dump:

1. Identifique a posição do log binário a partir do comentário de cabeçalho do arquivo de dump.
2. Restaure o dump base.
3. Aplique os eventos de log binário subsequentes até ao timestamp desejado:

“`bash

mysqlbinlog –start-position=154 –stop-datetime="2025-07-15 14:30:00"

/var/lib/mysql/binlog.000042 | mysql -u root -p database_name

“`

Automatizar Backups com Cron

Tarefa de Backup Diário Básico

Armazene as credenciais em `~/.my.cnf` em vez de as incorporar nos comandos cron:

“`ini

[mysqldump]

user=backup_user

password=StrongPassword!

“`

Defina permissões restritas:

“`bash

chmod 600 ~/.my.cnf

“`

Em seguida, crie a tarefa cron:

“`bash

crontab -e

“`

“`

Daily compressed backup at 02:00, retained for 30 days

0 2 * * * mysqldump –single-transaction –routines –triggers –events database_name

Delete backups older than 30 days

10 2 * * * find /backups/ -name "*.sql.gz" -mtime +30 -delete

“`

Script de Backup para Produção

Para Servidores Dedicados que alojam múltiplos bancos de dados, um script mais robusto trata do registo de erros, verificações de espaço em disco e descarregamento remoto:

“`bash

#!/bin/bash

BACKUP_DIR="/backups/mysql"

RETENTION_DAYS=30

LOG_FILE="/var/log/mysql_backup.log"

DATE=$(date +%F_%H-%M)

DATABASES=$(mysql –defaults-file=/etc/mysql/backup.cnf -e "SHOW DATABASES;"

mkdir -p "$BACKUP_DIR"

for DB in $DATABASES; do

OUTPUT="$BACKUP_DIR/${DB}_${DATE}.sql.gz"

mysqldump –defaults-file=/etc/mysql/backup.cnf

–single-transaction –routines –triggers –events

"$DB" | gzip -9 > "$OUTPUT"

if [ $? -eq 0 ]; then

echo "$(date): SUCCESS – $DB -> $OUTPUT" >> "$LOG_FILE"

else

echo "$(date): FAILURE – $DB" >> "$LOG_FILE"

fi

done

find "$BACKUP_DIR" -name "*.sql.gz" -mtime +"$RETENTION_DAYS" -delete

“`

Reforço de Segurança para Operações mysqldump

A gestão de credenciais é o aspeto mais frequentemente negligenciado da segurança de backups. Nunca passe `-pYourPassword` diretamente na linha de comando — é visível na saída de `ps aux` e no histórico do shell. Use uma destas abordagens:

• `~/.my.cnf` com `chmod 600` (por utilizador)
• `/etc/mysql/backup.cnf` com `chmod 640`, pertencente ao root, legível pelo grupo de backup
• Variável de ambiente `MYSQL_PWD` (visível em `/proc`, use apenas em contentores isolados)
• MySQL Vault ou HashiCorp Vault para ambientes empresariais

As permissões dos arquivos de backup devem ser restritivas:

“`bash

chmod 640 /backups/database_name_2025-07-15.sql.gz

chown root:backup_group /backups/database_name_2025-07-15.sql.gz

“`

Encriptação em repouso: Para dados sensíveis, encripte os arquivos de backup antes de os armazenar ou transferir:

“`bash

mysqldump –single-transaction database_name

> /backups/database_name_$(date +%F).sql.gz.enc

“`

Encriptação de transporte: Ao fazer dump de um servidor remoto, use sempre SSL/TLS ou um túnel SSH. Num ambiente de VPS com cPanel, a interface de backup do cPanel trata disso automaticamente, mas as operações manuais de mysqldump requerem flags SSL explícitos.

Armadilhas Comuns e Como Evitá-las

Incompatibilidades de conjunto de caracteres são a causa mais frequente de restaurações corrompidas. Especifique sempre o conjunto de caracteres explicitamente:

“`bash

mysqldump –default-character-set=utf8mb4 database_name > backup.sql

mysql –default-character-set=utf8mb4 database_name < backup.sql

“`

A ausência de `–column-statistics=0` causa falhas quando um cliente MySQL 8.0 faz dump de um servidor MySQL 5.7 ou MariaDB. O cliente MySQL 8 tenta fazer dump de estatísticas de colunas que servidores mais antigos não suportam:

“`bash

mysqldump –column-statistics=0 -u backup_user -p database_name > backup.sql

“`

Esquecer `–routines`, `–triggers` e `–events` omite silenciosamente objetos críticos do banco de dados. Esses flags não estão ativados por padrão (exceto `–triggers`) e são frequentemente esquecidos em dumps ad-hoc.

Dumps de tabelas grandes causando OOM: o mysqldump armazena em buffer conjuntos de resultados inteiros na memória por padrão. Para tabelas muito grandes, adicione `–quick` (ativado por padrão na maioria das versões, mas vale a pena verificar) para transmitir linhas uma de cada vez em vez de armazenar em buffer:

“`bash

mysqldump –quick –single-transaction database_name > backup.sql

“`

Restaurar para uma versão diferente do MySQL: Dumps do MySQL 8.0 podem conter sintaxe não suportada no MySQL 5.7 (ex., índices funcionais, colunas invisíveis). Teste sempre as restaurações num ambiente com versão correspondente antes de confiar em migrações entre versões.

Desvio do valor de auto-incremento: Se restaurar uma tabela num esquema existente que já tem linhas, as instruções `INSERT` falharão em conflitos de chave primária a menos que inclua `–add-drop-table` ou trunce manualmente a tabela de destino primeiro.

Usar mysqldump para Migrações de Banco de Dados

O mysqldump é a abordagem padrão para migrar bancos de dados entre servidores — por exemplo, ao mover um site WordPress de Hospedagem Web Partilhada para um VPS, ou ao realojar para um ambiente de Painéis de Controlo VPS com mais recursos.

O fluxo de trabalho de migração recomendado:

1. Fazer dump do banco de dados de origem com opções completas:

“`bash

mysqldump –single-transaction –routines –triggers –events

–default-character-set=utf8mb4 source_db | gzip > migration.sql.gz

“`

1. Transferir de forma segura usando rsync sobre SSH:

“`bash

rsync -avz -e ssh migration.sql.gz user@target-server:/tmp/

“`

1. Criar o banco de dados de destino com conjunto de caracteres correspondente:

“`bash

mysql -u root -p -e "CREATE DATABASE target_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;"

“`

1. Restaurar e verificar:

“`bash

gunzip < /tmp/migration.sql.gz | mysql -u root -p target_db

mysql -u root -p target_db -e "SHOW TABLES; SELECT COUNT(*) FROM critical_table;"

“`

1. Atualizar a configuração da aplicação para apontar para o novo host do banco de dados.

Para aplicações que também dependem de infraestrutura de email, certifique-se de que os registos DNS e as configurações de Hospedagem de Email são atualizados em paralelo com a migração do banco de dados para evitar interrupções de serviço.

Verificar a Integridade do Backup

Um backup que nunca foi testado não é um backup — é uma suposição não testada. Implemente uma rotina de verificação:

“`bash

#!/bin/bash

Restore backup to a test database and verify row counts

TEST_DB="backup_verify_$(date +%s)"

BACKUP_FILE="/backups/database_name_$(date +%F).sql.gz"

mysql -u root -p -e "CREATE DATABASE $TEST_DB;"

gunzip < "$BACKUP_FILE" | mysql -u root -p "$TEST_DB"

PROD_COUNT=$(mysql -u root -p -sN -e "SELECT COUNT(*) FROM database_name.orders;")

TEST_COUNT=$(mysql -u root -p -sN -e "SELECT COUNT(*) FROM $TEST_DB.orders;")

if [ "$PROD_COUNT" -eq "$TEST_COUNT" ]; then

echo "Backup verified: row counts match ($PROD_COUNT rows)"

else

echo "BACKUP VERIFICATION FAILED: prod=$PROD_COUNT, test=$TEST_COUNT"

fi

mysql -u root -p -e "DROP DATABASE $TEST_DB;"

“`

Execute este script de verificação semanalmente via cron e alerte em caso de falha.

Matriz de Decisão: Quando Usar mysqldump

Lista de Verificação de Pontos-Chave Técnicos

• Use sempre `–single-transaction` para bancos de dados apenas InnoDB para evitar bloqueios de escrita durante o backup.
• Inclua sempre `–routines –triggers –events` em qualquer dump destinado a ser um backup completo.
• Armazene as credenciais em `~/.my.cnf` ou `/etc/mysql/backup.cnf` com `chmod 600/640` — nunca inline em scripts ou comandos cron.
• Adicione `–column-statistics=0` ao usar um cliente MySQL 8.0 contra um servidor MySQL 5.7 ou MariaDB.
• Especifique sempre `–default-character-set=utf8mb4` tanto no dump como na restauração para evitar corrupção de codificação de caracteres.
• Comprima todos os backups com gzip ou pigz; encripte dumps sensíveis com AES-256 antes da transferência para fora do local.
• Inclua `–flush-logs –source-data=2` em dumps de produção para permitir a recuperação em ponto no tempo via logs binários.
• Automatize a limpeza de retenção com `find … -mtime +N -delete` para evitar o esgotamento do disco.
• Teste as restaurações regularmente — verifique as contagens de linhas e faça verificações pontuais da integridade dos dados em relação à produção.
• Para esquemas de motor misto, use `–lock-all-tables` em vez de `–single-transaction` para garantir consistência.

Perguntas Frequentes

O mysqldump bloqueia tabelas durante o backup?

Com `–single-transaction` num banco de dados InnoDB puro, nenhum bloqueio de tabela é adquirido além de um breve flush inicial. As tabelas MyISAM sempre requerem um bloqueio de leitura (`LOCK TABLES`) porque não têm suporte a transações. Esquemas de motor misto requerem `–lock-all-tables` para um snapshot consistente, o que bloqueia as escritas durante toda a duração do dump.

Como faço backup apenas do esquema do banco de dados sem nenhum dado?

Use o flag `–no-data`: `mysqldump -u backup_user -p –no-data database_name > schema.sql`. Isto exporta todas as instruções `CREATE TABLE`, `CREATE VIEW`, procedimentos armazenados e triggers sem nenhuma instrução `INSERT`.

Por que o meu mysqldump falha com erros de “column statistics”?

Isto ocorre quando um cliente MySQL 8.0 se conecta a um servidor MySQL 5.7 ou MariaDB. Adicione `–column-statistics=0` ao seu comando. Alternativamente, atualize o servidor para MySQL 8.0 ou use um binário cliente que corresponda à versão do servidor.

O mysqldump pode realizar backups incrementais?

Não. O mysqldump sempre produz um dump lógico completo do escopo especificado. A capacidade de backup incremental requer arquivamento de log binário (`mysqlbinlog`) combinado com um mysqldump base feito com `–flush-logs –source-data=2`. Backups físicos incrementais verdadeiros requerem Percona XtraBackup ou MySQL Enterprise Backup.

Qual é a forma mais segura de automatizar o mysqldump sem expor senhas?

Crie um utilizador de backup MySQL dedicado com os privilégios mínimos necessários, armazene as suas credenciais numa secção `[mysqldump]` de `~/.my.cnf` ou num arquivo de opções separado com `chmod 600`, e referencie-o com `–defaults-file=/path/to/backup.cnf`. Esta abordagem mantém as credenciais completamente fora das listagens de processos, do histórico do shell e das definições de tarefas cron.