Shell script to backup MySql database (Performed for Drupal databases). It ignores drupal cache and temporary information common tables content, although it fits any mysql database dump. To backup a Mysql database file to a chosen directory you must provide the database name, and mysql access information through parameters in the script executio…

easymysqldump.sh

#!/bin/bash
# Shell script to backup MySql database (Performed for Drupal databases) 
# It ignores drupal cache and temporary information common tables content,
# although it fits any mysql database dump.
# To backup a Mysql database file to a chosen directory you must provide the 
# database name, and mysql access information through parameters in the script 
# execution.
# Based on the original script on
# http://www.cyberciti.biz/nixcraft/vivek/blogger/2005/01/mysql-backup-script.html 
# Last updated: May - 2013
# --------------------------------------------------------------------
# This is a free shell script under GNU GPL version 2.0 or above
# Copyright (C) 2004, 2005 nixCraft project
# Feedback/comment/suggestions : http://cyberciti.biz/fb/
# -------------------------------------------------------------------------

# ATTENTION
# Change your destination directories

# Temporary destination directory, change this if you have someother location (With trailing slash)
TMP="/tmp/"
 
# Main directory where backup will be stored (With trailing slash)
MBD="/home/myuser/mysqldump/"

# DON'T TOUCH BELOW HERE

#Parameters
#ex: sh mysqlbackup.sh root root localhost mydatabase

# @param1
#Database username 
MyUSER=$1
# @param2
#Database password
MyPASS=$2
# @param3
#Database Hostname
MyHOST=$3
# @param4
#Database Name
DBS=$4
 
# Linux bin paths, change this if it can not be autodetected via which command
MYSQL="$(which mysql)"
MYSQLDUMP="$(which mysqldump)"
CHOWN="$(which chown)"
CHMOD="$(which chmod)"
GZIP="$(which gzip)"
 
# Get hostname
HOST="$(hostname)"
 
# Get data in dd-mm-yyyy format
NOW="$(date +"%Y%m%d_%H%M%S")"
 
# File to store current backup file
FILE="$DBS.$NOW.sql"

#Temporary dump file
TMP_FILE="$TMP$FILE"
  
#Create backup path if dont exist
[ ! -d $MBD ] && mkdir -p $MBD || :
 
#Load all tables 
TBS="$($MYSQL -u $MyUSER -h $MyHOST -p$MyPASS -D $DBS -Bse 'show tables;')"

#Create backup file
touch $TMP_FILE

echo "Backup file created "

#Run every table in database
for tb in $TBS
do
  #For cache tables backup only structure, otherwise backup content too
	case "$tb" in 
	    watchdog|queue|sessions|cache*) 
				echo "Saving structure from table $tb"
	            $MYSQLDUMP -d --single-transaction --compact --add-drop-table -u $MyUSER -h $MyHOST -p$MyPASS $DBS $tb | sed 's/ AUTO_INCREMENT=[0-9]*\b//' >> $TMP_FILE ;;
	        *)
				echo "Saving structure and content from table $tb"
				$MYSQLDUMP --single-transaction --compact --quick --add-drop-table --extended-insert=FALSE -u $MyUSER -h $MyHOST -p$MyPASS $DBS $tb >> $TMP_FILE ;;
	esac

done;

#Lets make database dump file smaller
echo "Generating a database dump file smaller $MDB$FILE.gz"
TAR_DUMP="$(gzip -c $TMP_FILE > $MDB$FILE.gz && rm $TMP_FILE)"

exit 0