backup mysql file per table

backup_mysql_per_table.sh

#!/bin/bash

# comands
ECHO=/bin/echo
DATE=/bin/date
DF=/bin/df
EXPORT=/usr/bin/mysqldump


#validate number params
if [ $# -ne 3 ]; then

  $ECHO ""
  $ECHO ""
  $ECHO "El número de parámetros debe ser 3"
  $ECHO "Los parámetros deben ser [aplicacion] [bbdd] [servidor]"
  $ECHO ""
  $ECHO ""
  exit;

fi

# Recogida de parametros
APLI=$1
DB=$2
SERVER=$3

# connect to
USER="-u USER_BACKUP_ROLE"
PASSWORD="-pPWD_USER_BACKUP"
HOST="-h $SERVER"

# files and others
FICHERO_LOG="/tmp/backup_$APLI.log"
BACKUP_DIR="/backups_mysql/$APLI"
RESTORESCRIPT="$BACKUP_DIR/__restoreData.sql"
OPCIONS="--opt"
OPTIONS_TABLE="--add-drop-table"
DATE=`/bin/date '+%y%m%d_%H%M%S'`
LIS_DEST_MAIL="[email protected]"



# functions
function remove_old_files() {
	$ECHO removing old temporary files if they exists...
	rm -f ${BACKUP_DIR}/*.sql > /dev/null 2>&1
	rm -f ${BACKUP_DIR}/*.tar > /dev/null 2>&1
	cd ${BACKUP_DIR}
}

function backup_database_file_per_table() {

$ECHO "=========================================="
$ECHO ${DB}
$ECHO "=========================================="
$ECHO 'SET FOREIGN_KEY_CHECKS=0;' > $RESTORESCRIPT

for TABLE in `mysql $HOST $USER $PASSWORD $DB -e 'show tables' | egrep -v 'Tables_in_' `; do
        local TABLENAME=$($ECHO $TABLE|awk '{ printf "%s", $0 }')
        local FILENAME="${TABLENAME}.sql"
        $ECHO Dumping $TABLENAME
        $ECHO 'source' $FILENAME';' >> $RESTORESCRIPT
        mysqldump $OPTIONS_TABLE $HOST $USER $PASSWORD $DB $TABLENAME > ${BACKUP_DIR}/${FILENAME}
done

$ECHO 'SET FOREIGN_KEY_CHECKS=1;' >> $RESTORESCRIPT

}

function compressing_backup() {
$ECHO making tar...
tar -cf ${BACKUP_DIR}/${DB}_${DATE}.tar *.sql  > /dev/null 2>&1

$ECHO compressing...
gzip -9 ${BACKUP_DIR}/${DB}_${DATE}.tar > /dev/null 2>&1

}

function remove_temporal_files() {
$ECHO removing temporary files...
rm -f ${BACKUP_DIR}/ *.sql > /dev/null 2>&1
rm -f ${BACKUP_DIR}/*.tar > /dev/null 2>&1
}

function init_process_report() {
>$FICHERO_LOG
local HORA_REAL=`date +%T" - "%A" "%d" de "%B" de "%Y`
$ECHO "-------------------------------------------------------------------------------------------" >> $FICHERO_LOG
$ECHO $HORA_REAL "- Inicio del proceso de backup de la BB.DD. MySQL $APLI " >> $FICHERO_LOG
$ECHO "-------------------------------------------------------------------------------------------" >> $FICHERO_LOG

}

function purge_old_files() {
local HORA_REAL=`date +%T`
$ECHO "-------------------------------------------------------------------------------------------" >> $FICHERO_LOG
$ECHO $HORA_REAL "- Eliminamos ficheros $DIR_BACKUP con mas de siete dias" >> $FICHERO_LOG
$ECHO "-------------------------------------------------------------------------------------------" >> $FICHERO_LOG
find $DIR_BACKUP -type f -mtime +7 -exec ls -la {} \; >> $FICHERO_LOG
find $DIR_BACKUP -type f -mtime +7 -exec rm -f {} \; >> $FICHERO_LOG

}

function finish_process_report() {
local HORA_REAL=`date +%T`
$ECHO "-------------------------------------------------------------------------------------------" >> $FICHERO_LOG
$ECHO $HORA_REAL "- Estado del filesystem del dir. $DIR_BACKUP despues de la limpieza" >> $FICHERO_LOG
$ECHO "-------------------------------------------------------------------------------------------" >> $FICHERO_LOG
$DF -h $DIR_BACKUP >> $FICHERO_LOG
$ECHO "===========================================================================================" >> $FICHERO_LOG
}

# 
# 3,2,1,zerooooooo!!
#
$ECHO "start... "
init_process_report
remove_old_files
backup_database_file_per_table
compressing_backup
remove_temporal_files
purge_old_files
finish_process_report
mailx -s "Copia de seguridad MySQL $BBDD y limpieza de ficheros del dir. $BACKUP_DIR del host $SERVER" $LIS_DEST_MAIL < $FICHERO_LOG
$ECHO "...finish "