Como fazer, automatizar e restaurar backups de bancos de dados MySQL

Introdução

Você está procurando uma maneira eficiente de fazer backup e restaurar seus bancos de dados MySQL? Neste guia, vamos explorar diferentes métodos para realizar backups e restaurações, incluindo opções que não exigem digitar usuário e senha toda vez. Vamos cobrir scripts, comandos e automação com cron para garantir que seus dados estejam sempre protegidos.

Como fazer Backup dos bancos de dados MySQL

É possível fazer um DUMP (exportar os dados) pela linha de comando com a seguinte instrução:

mysql-dump -u usuario -p nome_do_banco > nome_do_arquivo.sql

O arquivo gerado por este comando conterá todas informações e poderá ser utilização na restauração do banco de dados.

Como fazer Restore (Restauração) dos bancos de dados MySQL

Assim como o procedimento de backup, para restaurar é necessário pela linha de comando entrar com a seguinte instrução:

mysql -u usuario -p nome_do_banco < nome_do_arquivo.sql

Nesta situação, o arquivo exportado pelo mysql-dump é restaurado no banco de dados MySQL.

Automatizando o Backup

Método 1: Script de automação de Backup

Uma das formas mais flexíveis de fazer backup de seus bancos de dados MySQL é usando um script bash. Este método permite que você faça backup de múltiplos bancos de dados e adicione lógica personalizada conforme necessário.

Crie as pastas

mkdir -p ~/backup/mysql

Coloque a senha no arquivo ~/.mysqlpasswd (no exemplo abaixo estamos usando a senha do root)

nano ~/.mysqlpasswd

Script para automação:

#!/bin/bash

# Pasta de destino para armazenar os backups
DEST=~/backup/mysql/
CURRDATE=$(date +"%F")

# Hostname do servidor MySQL
HOSTNAME="localhost"
# Nome de usuário para fazer o backup
USER="root"
# Arquivo que contém a senha do usuário MySQL
PASS="$(cat ~/.mysqlpasswd)"

# Obtém a lista de bancos de dados
DATABASES=$(mysql -h $HOSTNAME -u $USER -p$PASS -e "SHOW DATABASES;" | tr -d "| " | grep -v Database)

# Cria a pasta de destino se não existir
[ ! -d $DEST ] && mkdir -p $DEST

for db in $DATABASES; do
  FILE="${DEST}/$db.sql.gz"
  FILEDATE=

  # Garante que seja feito apenas um backup por dia
  [ -f $FILE ] && FILEDATE=$(date -r $FILE +"%F")
  [ "$FILEDATE" == "$CURRDATE" ] && continue

  # Faz o backup e comprime
  [ -f $FILE ] && mv "$FILE" "${FILE}.old"
  mysqldump --single-transaction --routines --quick -h $HOSTNAME -u $USER -p$PASS -B $db | gzip > "$FILE"
  chown bacula:disk "$FILE"
  rm -f "${FILE}.old"
done

Este script faz o seguinte:

1. Define variáveis para o destino do backup, hostname do MySQL, usuário e senha.
2. Obtém uma lista de todos os bancos de dados no servidor.
3. Itera sobre cada banco de dados, criando um arquivo de backup comprimido.
4. Verifica se já existe um backup do dia atual antes de criar um novo.
5. Usa mysqldump com opções para garantir consistência e incluir rotinas armazenadas.

Para usar este script, você precisa:

1. Salvar o script em um arquivo (por exemplo, mysql_backup.sh).
2. Torná-lo executável com o comando chmod +x mysql_backup.sh.
3. Executá-lo manualmente ou configurá-lo para rodar automaticamente (veremos isso mais adiante).

Edite a CRON (como achar melhor, igual abaixo ou crontab -e )

nano /etc/crontab

Insira a seguinte linha ao final (presumindo que você tenha salvado o arquivo como ~/mysql_backup.sh)

* 2 * * * root ~/mysql_backup.sh

Salve e saia, e depois reinicie o serviço do cron

systemctl restart cron

Isso executará o backup todos os dias às 2:00 AM.

Método 2: Usando um Arquivo de Configuração

Se você prefere não incluir senhas diretamente em seus scripts ou comandos, pode usar um arquivo de configuração. Este método é mais seguro e conveniente.

Primeiro, crie um arquivo chamado .mysqlpwd (repare que este é diferente do exemplo acima) em seu diretório home com o seguinte conteúdo:

[mysqldump]
user=seu_usuario
password=sua_senha

[mysql]
user=seu_usuario
password=sua_senha

Agora, você pode usar o seguinte comando para fazer backup manualmente:

mysqldump --defaults-extra-file=~/.mysqlpwd nome_do_banco > ~/backup/mysql/"$(date '+%F').sql"

Ou, edite a CRON (como achar melhor, igual abaixo ou crontab -e )

nano /etc/crontab

Insira a seguinte linha ao final (presumindo que você tenha salvado o arquivo como ~/mysql_backup.sh)

* 2 * * * root mysqldump --defaults-extra-file=~/.mysqlpwd nome_do_banco > ~/backup/mysql/"$(date '+%F').sql"

Salve e saia, e depois reinicie o serviço do cron

systemctl restart cron

Isso executará o backup todos os dias às 2:00 AM.

Este comando faz o seguinte:

1. Usa --defaults-extra-file para especificar o arquivo de configuração.
2. Faz o backup do banco de dados especificado.
3. Salva o arquivo de backup com a data atual no nome.

Dicas Adicionais para Backup e Restauração

1. Compressão: Para economizar espaço, considere comprimir seus backups. Você pode modificar o comando mysqldump para incluir compressão:

   mysqldump --defaults-extra-file=~/.mysqlpwd nome_do_banco | gzip > ~/backup/mysql/"$(date '+%F').sql.gz"
   

2. Backups Incrementais: Para bancos de dados muito grandes, considere usar ferramentas como o Percona XtraBackup para backups incrementais.

3. Verificação de Integridade: Sempre verifique a integridade de seus backups periodicamente tentando restaurá-los em um ambiente de teste.

4. Armazenamento Remoto: Considere armazenar seus backups em um local remoto ou na nuvem para proteção adicional contra falhas de hardware local.

5. Rotação de Backups: Implemente uma política de retenção de backups para gerenciar o espaço de armazenamento, mantendo backups diários por uma semana, semanais por um mês e mensais por um ano, por exemplo.

Restauração de Backups

Para restaurar um backup, você pode usar o comando mysql. Se estiver usando o arquivo de configuração:

mysql --defaults-extra-file=~/.mysqlpwd nome_do_banco < nome_do_arquivo.sql

Se o backup estiver comprimido:

gunzip < arquivo_de_backup.sql.gz | mysql --defaults-extra-file=~/.mysqlpwd nome_do_banco

Lembre-se de que a restauração sobrescreverá os dados existentes no banco de dados. Sempre faça isso com cuidado e, se possível, em um ambiente de teste primeiro.

Conclusão

Fazer backup regular de seus bancos de dados MySQL é crucial para a segurança e integridade dos seus dados. Com os métodos descritos neste artigo, você pode automatizar o processo de backup, tornando-o mais seguro e conveniente. Seja usando um script personalizado, um arquivo de configuração ou uma combinação de ambos, você agora tem as ferramentas necessárias para implementar uma estratégia de backup robusta.

Lembre-se de testar regularmente seus backups e procedimentos de restauração para garantir que eles funcionem conforme o esperado quando você mais precisar deles. Com uma boa estratégia de backup em vigor, você pode ter tranquilidade sabendo que seus dados estão protegidos contra perda acidental ou falhas de sistema.