Backup Automatizado MySQL com ShellScript

—Script-Backup-Database-MySQL –Th14go–

Script para backup de várias databases MySQL do mesmo servidor,o script contem informações em todo seu escopo, o backup é executado, utilizando o mysqldumb do próprio MySQL, após correr o backup de todas as databases, é iniciado o processo de compactação.É gerado LOG de backup e compactação, para auditoria de execução do script posteriormente, fique a vontade de dar dicas e melhorar o script !

Vamos preparar todo o ambiente antes de executar o script

1. Configuração para enviar log por e-mail
2. Rotina de Backup
3. Validar váriaveis do script
4. Enviando database para cloud Mega.nz
5. Backup Somente Dados sem Estrutura Database
6. Ignorar Table em Backup

1.Configuração para enviar log por e-mail
sudo apt-get install mutt
Editar Arquivo de Configuração
nano ~/.muttrc

Colar parâmetros abaixo.

Nome do Remetente#
set realname=”Backup por e-mail”

Email do Remetente#
set from=”[email protected];”

Usuario da conta de email#
set my_user=[email protected]

Senha da conta de email#
set my_pass=’senhaemail’

Autenticacao no servidor smtp de email, nesse caso do gmail.com#
set smtp_url=smtps://$my_user:$[email protected]

set ssl_force_tls = yes

2.Rotina de Backup – Crontab

root@th14golop35:~$ crontab -e # Inicia tarefa com o agendador de tarefas crontab, em “root”

00 19 * * * /bck-mysql.sh #Backup será executado todos os dias as 19:00hr

Para garantir renicie o serviço do crontab

service crontab restart

3.Editar Váriaveis do arquivo bck-mysql.sh
Variáveis para SQL dos BCK
Variáveis do MySQL#
Databases#

Configurar Mega.nz para envio de database para cloud
Instalar MegaCMD conforme distro. clique aqui
Comandos Mega
mega-login [email protected] suasenha
mega-put arquivo.txt /diretorioMega — Enviar Arquivos
mega-rm arquivo.txt /diretorioMega –Excluir Arquivos

Se você optar para que o backup seja feito somente com dados acrescente o comando “–no-create-info” no final da linha do dump ficando assim:
Ex: mysqldump –routines –triggers -h $HOST -u $USER -p$PASSWORD $DATABASEI –no-create-info > $BASEI

Comando para ignorar table em Backup faça uma variavel para armazenar o valor da database.table

ex: ignore=database.table1

ex: mysqldump –routines –triggers -h $HOST -u $USER -p$PASSWORD $DATABASEI –no-create-info –ignore-table=$ignore > $BASEI

Execute ./bck-mysql.sh ou sh bck-mysql.sh

—Script-Backup-Database-MySQL –Th14go–

Script to backup multiple MySQL databases,the script contains information throughout its scope, the backup is performed using mysqldumb from the MySQL itself, after running the backup of all databases, the compression process starts.Backup and compression LOG is generated for auditing the script afterwards, feel free to give tips and improve the script!

Let’s prepare the whole environment before running the script

1. Configuration to send log by email
2. Backup routine
3. Validate script variants
4. Config Mega.nz send file to Cloud

1.Configuration to send log by email
sudo apt-get install mutt
Edit Configuration File
nano ~ / .muttrc

Paste parameters below.

Sender Name
set realname = “E-mail Backup”

Sender Email
set from = “[email protected];”

Email account user
set my_user=[email protected]

Email account password
set my_pass = ‘passwordemail’

Authentication on smtp mail server, in this case gmail.com
set smtp_url = smtps: // $ my_user: $[email protected]

Security layer required by gmail.com
set ssl_force_tls = yes

2.Backup routine – Crontab

root @ th14golop35: ~ $ crontab -e # Start task with crontab task scheduler, under “root”

00 19 * * * /bck-mysql.sh #Backup will run every day at 7:00 pm

To ensure restart the crontab service

service crontab restart

3.Editing Bck-mysql.sh File Variables
BCK SQL Variables#
MySQL Variables#
Databases#

Config Mega.nz to send file Cloud
Install MegaCMDClick Here
Command Mega
mega-login [email protected] password
mega-put file.txt /path/toMega — Send File
mega-rm arquivo.txt /path/ToMega –Delete File

How to use.

—-SCRIPT——

DATA=date +%Y-%m-%d-%H.%M

SYNC_LOG=/var/log/bckmysql/bckmysql$DATA.log

BASEI=”/mnt/backup/MySQL/BASEI-$DATA.sql”
BASEII=”/mnt/backup/MySQL/BASEII-$DATA.sql”
BASEIII=”/mnt/backup/MySQL/BASEIII-$DATA.sql”
BASEIV=”/mnt/backup/MySQL/BASEIV-$DATA.sql”
BASEV=”/mnt/backup/MySQL/BASEV-$DATA.sql”

HOST=”localhost”
USER=”root”
PASSWORD=”suasenha”

DATABASEI=”BASEI”
DATABASEII=”BASEII”
DATABASEIII=”BASEIII”
DATABASEIV=”BASEIV”
DATABASEV=”BASEV”

echo “***************************” >> $SYNC_LOG
echo “Inicio do Backup Database BASEI” >> $SYNC_LOG
date >> $SYNC_LOG
mysqldump –routines –triggers -h $HOST -u $USER -p$PASSWORD $DATABASEI > $BASEI
echo “Fim do Backup da Database BASEI” >> $SYNC_LOG
date >> $SYNC_LOG
echo “***************************” >> $SYNC_LOG
echo “Inicio do Backup Database BASEII” >> $SYNC_LOG
date >> $SYNC_LOG

mysqldump –routines –triggers -h $HOST -u $USER -p$PASSWORD $DATABASEII > $BASEII
echo “Fim do Backup da Database BASEII” >> $SYNC_LOG
date >> $SYNC_LOG
echo “******************************” >> $SYNC_LOG
echo “Inicio do Backup Database BASEIII” >> $SYNC_LOG
date >> $SYNC_LOG

mysqldump –routines –triggers -h $HOST -u $USER -p$PASSWORD $DATABASEII > $BASEIII
echo “Fim do Backup da Database BASSEIII” >> $SYNC_LOG
date >> $SYNC_LOG
echo “******************************” >> $SYNC_LOG
echo “Inicio do Backup Database BASEIV” >> $SYNC_LOG
date >> $SYNC_LOG

mysqldump –routines –triggers -h $HOST -u $USER -p$PASSWORD $DATABASEIV > $BASEIV
echo “Fim do Backup da Database BASEIV” >> $SYNC_LOG
date >> $SYNC_LOG
echo “******************************” >> $SYNC_LOG
echo “Inicio do Backup Database BASEV” >> $SYNC_LOG
date >> $SYNC_LOG

mysqldump –routines –triggers -h $HOST -u $USER -p$PASSWORD $DATABASEV > $BASEV
echo “Fim do Backup da Database BASEV” >> $SYNC_LOG
date >> $SYNC_LOG
echo “******************************” >> $SYNC_LOG

echo “******************************” >> $SYNC_LOG
echo “Inicio da compactacao do Backup” >> $SYNC_LOG
date >> $SYNC_LOG

cd /mnt/backup/MySQL/
tar -cvzf DATABASES-$DATA.tar.gz sql
echo “**************************” >> $SYNC_LOG
echo “Fim da compactacao” >> $SYNC_LOG
date >> $SYNC_LOG
echo “***************************” >> $SYNC_LOG

rm -rvf /mnt/backup/MySQL/*.sql

rsync -Cravp /var/log/bckmysql /mnt/backup/LOG/

echo “********INICIO REMOÇÃO REMOTA*********“>>$SYNC_LOG
date >> $SYNC_LOG
find /mnt/backup/MySQL -mtime +10 -fprint /mnt/backup/LOG/logdeletedate +%F.txt -exec rm -rf {} \; >>$SYNC_LOG
date >> $SYNC_LOG
echo “****FIM REMOÇÃO REMOTA****************”>>$SYNC_LOG

echo “*****Upload Arquivos para Mega“>> $LOG
mega-login [email protected] suasenha
mega-put /mysqlbackup/ /bck-database >> $LOG

echo ‘Anexo log de Backup’ | mutt -s ‘Backup Databases MySQL’ -a /var/log/bckmysql/bckmysql-$DATA.log — [email protected]