Backup automático do MySQL (triggers, stored procedures, agendamento cron, versionamento por data e limpeza de backups antigos)

Colaboração: Christian Guerreiro

Data de Publicação: 03 de maio de 2019

Recentemente fui solicitado a ajudar uma pessoa que estava precisando de uma rotina pra fazer o backup automático de um banco de dados MySQL no Linux. Após algumas pesquisas reuni as informações e propus uma solução que me pareceu bem completa, e incluía:

• Backup do banco de dados
• Backup de triggers e stored procedures
• Agendamento da rotina de backup
• Armazenamento versionado dos backups
• Rotina de eliminação de backups antigos

Fiz ainda uma abordagem gradativa, pois a pessoa que solicitou não tem muita experiência com Linux, então comecei do mais simples pro mais complexo.

1. Comando simples de backup

Caso a pessoa desejasse apenas um comando que pudesse executar manualmente, sob demanda, para realização do backup, a linha abaixo realiza o backup completo do banco de dados MySQL (incluindo triggers e stored procedures). Basta substituir dbusername, dbpassword e /caminho pelas respectivas informações de usuário, senha e caminho da pasta onde será armazenado o backup.

 /usr/bin/mysqldump routines [ triggers] -u dbusername -p dbpassword dbname > /caminho/backup.sql

Importante! A senha deve ficar colada na opção -p (a pessoa me informou isso depois, mas não consegui confirmar se o script que fiz estava errado ou a pessoa errou na hora de digitar não importa).

Observe também que a opção triggers está entre colchetes. Isto porque esta opção é padrão, ou seja, mesmo que não seja informada, o backup das triggers será feito. O mesmo não vale para as stored procedures, que precisam ser selecionadas com a opção routines. Você pode estar pensando: então pra quê esta opção triggers? Aha! É pra quando você precisar fazer backup somente das triggers. Genial hein? 🙂

2. Criar arquivos por data (pra não sobrescrever)

Você deve ter percebido que o backup, no comando anterior, é armazenado no arquivo backup.sql. Sendo assim, a cada nova execução do comando, o backup seria sobrescrito. Caso não deseje que isso ocorra, basta alterar o nome do arquivo a cada execução.

Agora imagine que você resolve que vai deixar agendado para executar o backup todo dia. Fica inviável renomear o arquivo manualmente a cada execução, certo? Neste caso, melhor criar um nome dinâmico para o arquivo do backup. Que tal usar data e hora?

É exatamente isso que a opção $(date +%F) faz! E mais, esta opção resulta na data no formato AAAA-MM-DD, ou seja, 4 dígitos do ano, 2 do mês e mais 2 do dia. Resultado: os arquivos ficam fáceis de ser ordenados por data, sem precisar usar a data de modificação, mas apenas ordenando por nome mesmo.

O comando abaixo, depois de ajustado como já indicamos no item 1, pode ser agendado para executar todo dia, sem risco de sobrescrever os arquivos de backups anteriores.

Optei por sugerir a criação de uma pasta para o backup de cada banco de dados para melhor organização, mas o comando pode ser facilmente adaptado para armazenar todos os backups de todos os bancos de dados na mesma pasta, bastando suprimir o trecho_DBNAME da pasta.

/usr/bin/mysqldump routines [ triggers] -u dbusername -p dbpassword dbname >/caminho/backup_DBNAME/$(date +%F)_full_DBNAME.sql

3. [opcional] Apagar backups anteriores

A depender da frequência de backup do banco de dados (há empresas que precisam de vários backups por dia!), haverá uma quantidade grande de backups antigos, e neste caso pode fazer sentido apagar os mais antigos (talvez fazendo um backup em fita antes).

Para isso, o comando abaixo permite localizar e remover automaticamente, os arquivos anteriores a 7 dias, considerando a data de modificação do arquivo de backup. Caso deseje remover backups mais antigos (depois de 30 dias, por exemplo), basta alterar o valor 7 para o valor apropriado.

$ find /caminho/backup_DBNAME/ -type f -mtime +7 -exec rm {} +

4. Juntando tudo!

Agora que já pensamos nas várias questões relativas à rotina de backup, que tal juntar tudo num script único backup_mysql.sh?

#!/bin/bash
/usr/bin/mysqldump routines [ triggers] -u dbusername -p dbpassword dbname >/caminho/backup_DBNAME/$(date +%F)_full_DBNAME.sql
find /caminho/backup_DBNAME/ -type f -mtime +7 -exec rm {} +

O script acima vai fazer o backup do banco, salvar o arquivo por data, e depois apagar os backups anteriores a uma semana.

Legal, né?

Importante! Não esquecer de dar permissão de execução no script com o comando chmod +x backup_mysql.sh .

5. Agendamento no cron

Opa! Quase tudo pronto. Só falta agendar o script para execução automática.

Execute o comando crontab -e para incluir a execução do backup no usuário desejado (root ou outro).

Você será levado para um editor com um conteúdo semelhante ao abaixo:

# Minute Hour Day of Month Month Day of Week Command
# (0-59) (0-23) (1-31) (1-12 or Jan-Dec) (0-6 or Sun-Sat)

Este conteúdo indica o formato de agendamento do cron, onde você deve informar minuto, hora, dia do mês, mês, dia da semana que o comando deve ser executado, além do próprio comando, claro!

30 4 * * 1-6 /caminho/backup_mysql.sh

Você consegue identificar em que dias e horários o backup será executado com base nas informações acima?

Difícil?

Ok, vamos lá.

O backup será executado às 4h30min da manhã, todos os dias do mês, todos os meses, porém apenas de segunda a sábado (1-6).

6. Conclusão

Viu como é fácil?

Brincadeiras à parte, há alguns dias li um texto que fazia recomendações a estudantes de cursos de sistemas de informação e afins.

Uma das recomendações me chamou a atenção, pois tinha um título mais ou menos assim: Quem tem medo do Linux? .

Saiba mais... vEcoShell, o canivete suiço para #virtualização Pois é. Saber transitar no mundo dos pinguins é cada vez mais importante, num mundo onde as tendências tecnológicas mais atuais e mais transformadoras têm relação direta com software livre (cloud, devops, big data, etc), conhecer o software livre mais importante de todos deixou de ser uma opção e virou uma obrigação, não acha?

Então? Diz aí o que achou da solução. Identificou algum erro? Tem alguma sugestão? Vai ser útil pra você? Fala que eu te escuto (oops, leio! 🙂

Christian Guerreiro é auditor de TI, professor e empreendedor, fundador do Tecnologia que Interessa! e Escola Bitcoins.