MySQL: Como Criar Backup Automático com mysqldump

Por que o backup automático é crítico em ambientes VPS

A administração de banco de dados em infraestruturas modernas, especialmente em servidores VPS (Virtual Private Server), exige uma disciplina rigorosa quanto à preservação de dados. Dados são o ativo mais valioso de qualquer aplicação web ou serviço empresarial. A perda de informações devido a falhas de hardware, corrupção de software, ataques de ransomware ou erros humanos pode ser catastrófica para a continuidade do negócio.

Neste contexto, o backup automático deixa de ser uma recomendação e se torna uma necessidade absoluta. Embora soluções de snapshot de disco sejam úteis, elas não garantem a integridade lógica dos dados ou a portabilidade entre diferentes versões do banco de dados. Para garantir a recuperação de desastres eficaz e um ciclo de vida robusto de dados, é essencial utilizar ferramentas específicas para extração lógica.

O mysqldump é a ferramenta padrão da indústria para bancos MySQL e MariaDB. Ele gera um conjunto de comandos SQL que podem ser reexecutados para reconstruir o banco de dados original. Ao combinar essa ferramenta com scripts bash agendados via cron, criamos uma estratégia de backup confiável, versionada e otimizada.

Pré-requisitos e Preparação do Ambiente

Antes de implementar a automação, é fundamental garantir que o ambiente esteja preparado. Este tutorial assume que você já possui acesso SSH root ou com privilégios sudo em seu servidor VPS Linux (Ubuntu, Debian, CentOS ou similar) e que o MySQL ou MariaDB está instalado e em execução.

Passo 1: Verificar a instalação do MySQL/MariaDB

systemctl status mysql
# ou
systemctl status mysqld

Se o serviço não estiver ativo, inicie-o com sudo systemctl start mysql.

Passo 2: Criar um usuário dedicado para backups (Segurança)

Boas práticas de segurança recomendam que o processo de backup utilize uma conta com permissões mínimas necessárias, em vez da conta root do banco de dados. Isso limita o impacto caso as credenciais sejam comprometidas.

mysql -u root -p

Dentro do shell do MySQL, execute os seguintes comandos para criar um usuário backup_user e conceder permissões de leitura:

CREATE USER 'backup_user'@'localhost' IDENTIFIED BY 'SenhaForteAqui';
GRANT SELECT, LOCK TABLES, SHOW VIEW, EVENT, TRIGGER ON *.* TO 'backup_user'@'localhost';
FLUSH PRIVILEGES;
EXIT;

Passo 3: Criar o diretório de armazenamento

Crie um diretório dedicado para armazenar os dumps. Este local deve ter espaço suficiente e, idealmente, estar em uma partição separada do sistema operacional ou com políticas de retenção claras.

sudo mkdir -p /var/backups/mysql
sudo chown www-data:www-data /var/backups/mysql
# Ajuste as permissões conforme necessário para o usuário que rodará o script

Criando o Script Bash de Backup

Agora, vamos desenvolver o script bash que executará a cópia de segurança. Este script será responsável por gerar o arquivo de dump, compactá-lo para economizar espaço e disco, e realizar a limpeza dos backups antigos.

Crie um arquivo chamado backup_mysql.sh:

sudo nano /usr/local/bin/backup_mysql.sh

Insira o seguinte conteúdo no arquivo. Este script utiliza variáveis para facilitar a manutenção e suporta múltiplos bancos de dados.

Estrutura do Script

1. Definição de Variáveis: Define credenciais, datas e caminhos.
2. Geração do Nome do Arquivo: Usa a data atual para garantir unicidade e facilitar a organização temporal.
3. Execução do mysqldump: Realiza o dump com opções de otimização.
4. Compressão: Compacta o arquivo SQL bruto.
5. Limpeza (Rotation): Remove backups antigos para evitar consumo excessivo de disco.

#!/bin/bash

# Configurações do Banco de Dados
DB_USER="backup_user"
DB_PASS="SenhaForteAqui"
HOST="localhost"

# Configurações do Backup
BACKUP_DIR="/var/backups/mysql"
DATE=$(date +"%Y%m%d_%H%M%S")
RETENTION_DAYS=7
COMPRESS=true

# Lista de bancos para backup (se vazio, faz todos)
DBS_TO_BACKUP=""

# Se DBS_TO_BACKUP estiver vazio, pega todos os bancos
if [ -z "$DBS_TO_BACKUP" ]; then
    # Obtém a lista de bancos ignorando bancos de sistema
    DBS=$(mysql -u $DB_USER -p$DB_PASS -h $HOST -e "SHOW DATABASES;" | grep -Ev "(Information_schema|Performance_schema|Sys)")
else
    IFS=',' read -ra DB_ARRAY <<< "$DBS_TO_BACKUP"
    for db in "${DB_ARRAY[@]}"; do
        echo "Backupando o banco: $db"
        FILENAME="${BACKUP_DIR}/${db}_${DATE}.sql"
        
        # Comando mysqldump principal
        # --single-transaction: Garante consistência sem travar tabelas InnoDB
        # --routines: Inclui procedures e functions
        # --events: Inclui eventos agendados
        # --triggers: Inclui triggers
        # --hex-blob: Codifica dados binários para evitar problemas de encoding
        
        mysqldump -u "$DB_USER" -p"$DB_PASS" -h "$HOST" \
            --single-transaction \
            --routines \
            --events \
            --triggers \
            --hex-blob \
            --databases "$db" > "$FILENAME"

        # Compressão se habilitada
        if [ "$COMPRESS" = true ]; then
            gzip "$FILENAME"
            echo "Backup de $db concluído: ${FILENAME}.gz"
        else
            echo "Backup de $db concluído: $FILENAME"
        fi
    done
fi

# Limpeza de backups antigos (mantém apenas os últimos N dias)
echo "Limpando backups antigos com mais de $RETENTION_DAYS dias..."
find "$BACKUP_DIR" -type f -name "*.sql.gz" -mtime +$RETENTION_DAYS -delete
find "$BACKUP_DIR" -type f -name "*.sql" -mtime +$RETENTION_DAYS -delete

echo "Processo de backup finalizado em $(date)"

Explicação das Flags de Otimização:

• --single-transaction: Este é crucial para bancos InnoDB. Ele inicia uma transação SELECT consistente antes de começar o dump, permitindo que o banco continue recebendo escritas durante o processo, minimizando o impacto na performance da aplicação.
• --routines e --events: Muitos esquecem que procedimentos armazenados e agendamentos são parte do esquema lógico e devem ser preservados.
• --hex-blob: Previne corrupção de dados em colunas BLOB que contenham caracteres não imprimíveis ou com encoding específico.

Após salvar o arquivo, torne-o executável:

sudo chmod +x /usr/local/bin/backup_mysql.sh

Automatizando com Cron

O backup automático só funciona se for agendado corretamente. O utilitário cron do Linux é a ferramenta padrão para isso. Vamos configurar uma tarefa que rode diariamente, preferencialmente em horários de baixo tráfego (ex: 3:00 AM).

Edite o crontab do usuário root:

sudo crontab -e

Adicione a seguinte linha ao final do arquivo. Esta configuração executa o script diariamente às 3 da manhã.

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

Análise da sintaxe Cron:

• 0 3 * * *: Minuto 0, Hora 3, Todo o dia, Todo o mês, Todo o dia da semana.
• > /var/log/mysql-backup.log: Redireciona a saída padrão (stdout) para um arquivo de log. Isso é vital para auditoria e troubleshooting.
• 2>&1: Redireciona também os erros (stderr) para o mesmo arquivo de log, garantindo que falhas não passem despercebidas.

Salve o arquivo. O cron começará a executar o script automaticamente conforme a programação estabelecida.

Testando e Validando o Backup

Nunca confie em um sistema de backup que nunca foi testado. A primeira execução manual é essencial para verificar se as permissões estão corretas e se o espaço em disco é suficiente.

Passo 1: Execução Manual

sudo /usr/local/bin/backup_mysql.sh

Verifique se o arquivo foi criado no diretório especificado:

ls -lh /var/backups/mysql/

Você deve ver um arquivo nomeado como nomedobanco_20231027_030000.sql.gz.

Passo 2: Verificação de Integridade

Não basta ter o arquivo; é preciso garantir que ele pode ser restaurado. Realize um teste de restore em um ambiente isolado ou usando um banco temporário no mesmo servidor.

# Criar um banco de teste
mysql -u root -p -e "CREATE DATABASE test_restore;"

# Restaurar o backup mais recente
zcat /var/backups/mysql/suabanco_20231027_030000.sql.gz | mysql -u root -p test_restore

Verifique se as tabelas e dados estão presentes:

mysql -u root -p -e "USE test_restore; SHOW TABLES;"

Se os dados estiverem lá, sua estratégia de recuperação de desastres está funcional.

Otimizações Avançadas e Boas Práticas

Para ambientes de produção com grandes volumes de dados ou alta concorrência, considere as seguintes otimizações:

1. Exclusão de Bancos Específicos: No script, você pode excluir bancos de teste ou logs que não precisam ser salvos adicionando filtros na variável $DBS.
2. Backup Remoto (Off-site): Um backup local em um VPS não protege contra falhas físicas do datacenter. Utilize ferramentas como rsync, rclone ou scripts simples para enviar os arquivos .gz para um bucket S3, Google Cloud Storage ou outro servidor.
3. Aviso de Falha: Adicione lógica no script para enviar um e-mail ou notificação Slack/Telegram caso o comando mysqldump retorne erro. Isso permite ação imediata.
4. Monitoramento de Espaço: Configure alertas para quando o diretório /var/backups/mysql atingir 80% de capacidade.

Considerações Finais sobre Administração de Banco de Dados

A implementação de um script de backup com mysqldump e cron é o primeiro passo para uma administração de banco de dados profissional. No entanto, a automação deve ser parte de uma estratégia maior que inclui monitoramento de integridade, testes regulares de restore e proteção contra ameaças externas.