Backup de bases MySQL [Shell Script]

Backup de bases MySQL

Publicado por Jonas Oliani (última atualização em 23/02/2017)

[ Hits: 3.591 ]

Download backup_mysql_vivaolinux.sh

3 0

Denuncie Favoritos Indicar

Script criado para efetuar dump de todas as bases MySQL. Utiliza a aplicação sendEmail ( http://caspian.dotconf.net/menu/Software/SendEmail/ ) para envio do log de execução.

Para instalação no Debian, executar:

# apt-get update && apt-get install sendemail

Necessário criar um usuário para efetuar o dump das bases com as permissões corretas, descritas no próprio script.

Cria automaticamente a estrutura de backup dentro de /opt, sendo:

- /opt/backup/mysql o diretório de backup das bases
- /opt/backup/log o log de backup

Testado somente no Debian.

Esconder código-fonte

#!/bin/bash
#################
#Adicionar usuário ao mysql para executar o backup
# GRANT SELECT, LOCK TABLES, SHOW VIEW, EVENT, TRIGGER ON *.* TO usuario@host identified by 'senha';
#Necessário instalação do SendEmail para envio de emails de logs (não é necessário um MX no servidor de banco)
#Variáveis
###################################################
DBUSER='backupdb'
DBPASS='backupdb'
DBHOST='localhost'
DIRBKPDB=/opt/backup
DIRLOG=/opt/backup/log
DIRBKP=/opt/backup/mysql
ASSUNTOEMAL='ASSUNTOEMAL'
EMAILFROM='EMAILFROM@EMAIL.DOMINIO'
EMAILTO='EMAILTO@EMAIL.DOMINIO'
SMTP='SMTP.DOMINIO'
SMTPPASS='SMTPPASS'
###################################################

#VERIFICA DIRETORIO DE BACKUP
if /usr/bin/test ! -d $DIRBKPDB
        then
                echo -e "\033[33;1m Diretorio $DIRBKPDB nao encontrado. Criando... \033[m"
                /bin/mkdir $DIRBKPDB 2> /dev/null
        fi

#VERIFICA DIRETORIO DE LOG
if /usr/bin/test ! -d $DIRLOG
        then
                echo -e "\033[33;1m Diretorio $DIRLOG nao encontrado. Criando... \033[m"
                /bin/mkdir $DIRLOG 2> /dev/null
        fi

#VERIFICA SENDEMAIL ESTÁ INSTALADO
SENDEMAIL=$(/usr/bin/which sendemail)
        if /usr/bin/test -z $SENDEMAIL
        then
                        echo -e "\033[33;1m SendEmail nao encontrado. Efetuar a instalacao. Finalizando... \033[m"
                        exit 0
        fi

echo -e "<html>\n<body>" > $DIRLOG/backup.log

echo "Executando Backup Mysql $ASSUNTOEMAL - $(date) <br><br>" >> $DIRLOG/backup.log

#FUNCAO PARA ENVIO DE EMAILS
ENVIAEMAIL () {

        MENSAGEM=$(cat $DIRLOG/backup.log)

        $SENDEMAIL -f "$EMAILFROM" -t "$EMAILTO" -u "$ASSUNTOEMAL" -m "$MENSAGEM" -s "$SMTP":587 -xu "$EMAILFROM" -xp "$SMTPPASS" -o tls=no >> $DIRLOG/backup.log

        }

#REMOVE BACKUPS ANTIGOS
if /usr/bin/test -d $DIRBKP
        then
                /bin/rm -r $DIRBKP &> /dev/null
                /bin/mkdir $DIRBKP &> /dev/null
        else
                /bin/mkdir $DIRBKP &> /dev/null
        fi

#Verifica conexao com banco
CONDB=1
while [ $CONDB -le 4 ]; do
        TESTADB=$(mysqlshow -u $DBUSER -p"$DBPASS" -h $DBHOST 2>> $DIRLOG/backup.log | grep Databases | sed 's/ //g')
        if test -z $TESTADB
                then
                        if [ $CONDB -eq 4 ]; then
                                echo "<br><font color=red> Numero de tentativas de conexao ao BANCO excedida ($CONDB). Finalizando... </font><br>" >> $DIRLOG/backup.log
                                ENVIAEMAIL
                                exit 0
                        fi
                        echo "<br><font color=red> Nao foi possivel conectar ao BANCO. Aguardando 5min. Tentativa $CONDB </font><br>" >> $DIRLOG/backup.log
                        sleep 300
                else
                        break
                fi
        (( CONDB++ ))
done

#Executa o dump das bases e compacta
LISTADB=$(/usr/bin/mysqlshow -u $DBUSER -p"$DBPASS" -h $DBHOST | grep -v 'Databases\|----\|_schema' | sed 's/|//g')
        if test -z $LISTADB 2> /dev/null
                then
                        echo "<font color=red> Usuario $DBUSER sem permissao para DUMP de bases </font><br>" >> $DIRLOG/backup.log
                        echo "<font color=red>Executar  GRANT SELECT, LOCK TABLES, SHOW VIEW, EVENT, TRIGGER ON *.* TO usuario@host identified by 'senha'; </font>" >> $DIRLOG/backup.log
                else
        for DUMP in $LISTADB; do
                /usr/bin/mysqldump --routines --events -u $DBUSER -p"$DBPASS" $DUMP -h $DBHOST > $DIRBKP/$DUMP.sql 2>> $DIRLOG/backup.log
                        if [ $? -eq 0 ]
                                then
                                        /bin/gzip $DIRBKP/$DUMP.sql 2>> $DIRLOG/backup.log
                                        if [ $? -eq 0 ]
                                                then
                                                        echo "BACKUP <b> $DUMP </b> EXECUTADO COM <font color=blue> SUCESSO </font><br>" >> $DIRLOG/backup.log
                                                else
                                                        echo "<font color=red> COMPACTACAO DO DUMP $DUMP EXECUTADA COM FALHA </font><br>" >> $DIRLOG/backup.log
                                        fi
                                else
                                        echo "<font color=red>DUMP $DUMP EXECUTADO COM FALHA </font><br>" >> $DIRLOG/backup.log
                                fi
         done
        fi

echo "</html>\n</body>" >> $DIRLOG/backup.log
ENVIAEMAIL