Backup de MySQL que você consegue restaurar de verdade

O banco caiu numa terça à noite. Você abre a pasta de backups, acha o arquivo de ontem, roda o restore, e ele para na metade com erro numa view. Ou sobe inteiro, mas sem as procedures que o fechamento do mês chama. A resposta curta: backup é o arquivo gerado pelo mysqldump fora da requisição web, copiado para longe do servidor do banco e restaurado de verdade num banco vazio de tempos em tempos. Sem esse último passo, você tem um arquivo otimista, não um backup.

Quase todo sistema PHP ganha, cedo ou tarde, um botão "gerar backup". Ele monta o SQL dentro do request, a pessoa baixa e todo mundo dorme tranquilo. Até a tabela crescer, o PHP estourar o tempo limite no meio e o arquivo sair cortado sem que ninguém perceba, porque ninguém nunca abriu.

Por que o backup que existe não restaura

O restore falha por motivos previsíveis. Estes são os que mais aparecem em produção:

• Faltou estrutura. Rotina caseira que exporta só INSERT, sem CREATE TABLE, não sobe num banco vazio.
• Faltaram objetos. O mysqldump inclui triggers por padrão, mas procedures, functions e eventos só entram se você pedir.
• O arquivo está cortado. O processo morreu por tempo limite, memória ou disco cheio, e o script não conferiu o código de saída.
• Os dados não batem entre si. O dump leu a tabela de pedidos num instante e a de itens noutro, com vendas entrando no meio. Sobe, mas com item apontando para pedido que não existe.
• O destino é diferente da origem. Views e procedures carregam um DEFINER, o usuário que as criou. Se ele não existe no servidor onde você restaura, o objeto falha ao ser criado ou ao ser usado.

Nenhum desses problemas aparece quando você olha o tamanho do arquivo. Todos aparecem no restore. Por isso o teste de restauração não é um extra: é a única prova de que o resto funcionou.

Como gerar o backup com mysqldump

O PHP que atende o usuário não é lugar para montar backup. O caminho adulto é o mysqldump agendado no próprio servidor, ou o backup automático do serviço gerenciado, se você usa um. O PHP, no máximo, mostra quando foi o último backup e se ele deu certo.

Um usuário de banco só para isso

O backup não precisa escrever nada. Crie um usuário que só lê o schema da aplicação:

CREATE USER 'backup'@'localhost' IDENTIFIED BY 'troque-por-uma-senha-longa';
GRANT SELECT, SHOW VIEW, TRIGGER, EVENT, LOCK TABLES ON loja.* TO 'backup'@'localhost';

Para exportar procedures e functions, algumas versões do MySQL exigem um privilégio extra de leitura de rotinas. Se o dump reclamar de permissão nelas, procure na documentação da sua versão qual é o privilégio certo, em vez de resolver com ALL PRIVILEGES.

Senha fora da linha de comando

Senha passada com -p na linha de comando aparece na lista de processos e no histórico do shell. Use um arquivo de opções que só o root lê, ou o cofre de segredos do seu ambiente:

# /etc/mysql/backup.cnf  (dono root, permissão 600)
[client]
user=backup
password=troque-por-uma-senha-longa

O script de backup

1. Gere o dump com transação única, rotinas e eventos.
2. Comprima no caminho, sem passar por arquivo intermediário gigante.
3. Grave num nome temporário e só renomeie se tudo deu certo.
4. Confira se o arquivo está íntegro e terminou de verdade.
5. Apague os backups mais velhos que a sua retenção.

#!/usr/bin/env bash
set -euo pipefail

BANCO="loja"
DESTINO="/var/backups/mysql"
ARQUIVO="$DESTINO/$BANCO-$(date +%F-%H%M).sql.gz"
PARCIAL="$DESTINO/em-andamento-$BANCO.sql.gz"

mysqldump --defaults-extra-file=/etc/mysql/backup.cnf \
  --single-transaction \
  --routines --events --triggers \
  --no-tablespaces \
  "$BANCO" | gzip > "$PARCIAL"

gzip -t "$PARCIAL"
zcat "$PARCIAL" | tail -n 1 | grep -q "Dump completed"
mv "$PARCIAL" "$ARQUIVO"

find "$DESTINO" -name "$BANCO-*.sql.gz" -mtime +14 -delete

O que cada parte faz:

• --single-transaction lê todas as tabelas InnoDB a partir do mesmo instante, sem travar a escrita da aplicação. Não vale para MyISAM. Durante o dump, evite ALTER TABLE, DROP TABLE e afins em outras conexões, porque eles quebram essa consistência.
• --routines e --events trazem procedures, functions e eventos. --triggers já é padrão, mas deixar explícito evita surpresa.
• --no-tablespaces evita o erro de privilégio PROCESS que o MySQL 8 passou a cobrar para exportar informação de tablespace, que um backup lógico comum não usa.
• O banco vai como nome solto, sem --databases. Assim o arquivo não traz CREATE DATABASE nem USE loja, e você consegue restaurar num banco com outro nome sem sobrescrever o original por engano.
• set -o pipefail faz o script falhar se o mysqldump falhar, mesmo com o gzip terminando bem. Sem isso, um dump cortado vira um arquivo "válido".
• O mysqldump escreve uma linha final com "Dump completed". Se ela não está lá, o arquivo terminou antes da hora.

Agende no cron, num horário de pouco movimento, e guarde a saída num log que alguém olha:

30 3 * * * /usr/local/bin/backup-mysql.sh >> /var/log/backup-mysql.log 2>&1

Onde guardar o arquivo

Backup no mesmo disco do banco protege contra DELETE errado e mais nada. Disco que morre, servidor apagado e conta invadida levam os dois juntos. Copie o arquivo para outro lugar logo depois de gerar: um bucket de armazenamento de objetos em outra conta ou região, ou outro servidor. A regra de bolso continua boa: três cópias, em dois tipos de mídia, uma fora do local.

A credencial que envia o arquivo deve poder gravar e não apagar, ou o destino deve ter versionamento com retenção. Assim um script errado ou um invasor não limpa a produção e o backup na mesma noite. E lembre que o dump tem nome, e-mail e endereço de cliente: criptografe antes de sair do servidor e controle quem baixa. Isso faz parte da adequação à LGPD, não é capricho.

Como provar que o backup restaura

Uma vez por mês, no mínimo, faça o ensaio completo. Ele não precisa de servidor novo, só de um MySQL descartável na mesma versão principal da produção.

1. Suba um MySQL de teste, por exemplo num contêiner.
2. Crie um banco vazio e restaure o arquivo mais recente, cronometrando.
3. Compare a contagem das tabelas principais com a produção usando COUNT(*). O número de linhas do information_schema é estimado no InnoDB e não serve para essa conferência.
4. Aponte uma cópia da aplicação para esse banco. Faça login e abra um relatório que usa procedure ou view.
5. Anote a data, o arquivo usado e o tempo de restore.

docker run --name restore-teste -e MYSQL_ROOT_PASSWORD=senha-descartavel -d mysql:8.4
# espere o servidor terminar de subir antes de seguir

docker exec restore-teste mysql -uroot -psenha-descartavel -e "CREATE DATABASE loja_restore"

time (gunzip < /var/backups/mysql/loja-2026-10-17-0330.sql.gz \
  | docker exec -i restore-teste mysql -uroot -psenha-descartavel loja_restore)

docker exec restore-teste mysql -uroot -psenha-descartavel \
  -e "SELECT COUNT(*) FROM loja_restore.pedidos"

A senha na linha de comando aqui é de um contêiner que vai ser apagado em seguida. Em servidor de verdade, vale a regra do arquivo de opções. Troque a tag mysql:8.4 pela versão que a produção usa.

Se o restore reclamar de DEFINER, você achou um problema que ia aparecer no pior dia. Crie o usuário definidor no destino ou recrie os objetos com um definidor que existe nos dois lados.

O tempo do restore é o número que importa para o negócio. Se o banco leva duas horas para subir, o sistema fica duas horas fora no dia do desastre, e ninguém deveria descobrir isso naquele dia. Lembre também que um dump diário significa perder até um dia de dados. Se isso é inaceitável, o próximo passo é recuperação até um ponto no tempo, com binlog ou com o recurso equivalente do serviço gerenciado.

Erros comuns

• Gerar o backup dentro do request PHP. Funciona com tabela pequena e corta o arquivo em silêncio quando ela cresce.
• Não checar o código de saída. Sem pipefail, um mysqldump que morreu no meio produz um .sql.gz íntegro e incompleto.
• Esquecer --routines e --events. O banco sobe, o login funciona e o fechamento do mês quebra porque a procedure não existe.
• Usar --databases e restaurar "num banco de teste". O USE dentro do arquivo manda os dados para o banco original.
• Guardar o backup no mesmo servidor. O disco que morre leva a produção e a cópia juntos.
• Nunca restaurar. Você só descobre que o arquivo não serve no dia em que ele é a única coisa que sobrou.

Checklist

• O backup roda fora da aplicação web, agendado, com usuário de banco só de leitura.
• A senha do usuário de backup não aparece na linha de comando nem no repositório.
• O dump usa --single-transaction, --routines e --events.
• O script falha visivelmente se o dump falhar e só renomeia o arquivo completo.
• Existe cópia fora do servidor do banco, criptografada, que a credencial de envio não consegue apagar.
• Há um restore de teste registrado no último mês, com data e duração.
• Alguém recebe aviso quando o backup da noite não aconteceu.

Escolha a tabela que você teria mais medo de perder e responda olhando o seu ambiente, não a memória: qual foi a data do último restore bem-sucedido dela, e quantos minutos ele levou?