script-bkp-mysql.sh

gistfile1.txt

#!/bin/bash
 
####################### SCRIPT PARA BACKUP MYSQL #######################
# Jeferson R. Costa <[email protected]>                            #
# Created Feb, 2013                                                    #
# Update Feb, 2013                                                     #
# http://www.vivaolinux.com.br/script/Script-de-backup-MySQL-melhorado #
########################################################################
 
# Definindo parametros do MySQL
echo "  -- Definindo parametros do MySQL ..."
DB_NAME='dbname'
DB_USER='username'
DB_PASS='password'
DB_PARAM='--add-drop-table --add-locks --extended-insert --single-transaction -quick'
 
# Definindo parametros do sistema
echo "  -- Definindo parametros do sistema ..."
DATE=`date +%Y-%m-%d`
MYSQLDUMP=/usr/bin/mysqldump
BACKUP_DIR=/opt/bkp
BACKUP_NAME=mysql-$DATE.sql
BACKUP_TAR=mysql-$DATE.tar
 
#Gerando arquivo sql
echo "  -- Gerando Backup da base de dados $DB_NAME em $BACKUP_DIR/$BACKUP_NAME ..."
$MYSQLDUMP $DB_NAME $DB_PARAM -u $DB_USER -p$DB_PASS > $BACKUP_DIR/$BACKUP_NAME
 
# Compactando arquivo em tar
echo "  -- Compactando arquivo em tar ..."
tar -cf $BACKUP_DIR/$BACKUP_TAR -C $BACKUP_DIR $BACKUP_NAME
 
# Compactando arquivo em bzip2
echo "  -- Compactando arquivo em bzip2 ..."
bzip2 $BACKUP_DIR/$BACKUP_TAR
 
# Excluindo arquivos desnecessarios
echo "  -- Excluindo arquivos desnecessarios ..."
rm -rf $BACKUP_DIR/$BACKUP_NAME

Instalação via ssh

$ scp script-bkp-mysql.sh root@host:/root
$ ssh root@host
$ chmod +x script-bkp-mysql.sh
#      MINUTO HORA  DIA MES DIASEMANA USER SCRIPT  
$ echo "0     0     *   *   *         root sh /root/script-bkp-mysql.sh" >> /etc/crontab