Skip to content

Instantly share code, notes, and snippets.

@lnunesbr
Created May 17, 2013 22:55
Show Gist options
  • Save lnunesbr/5602481 to your computer and use it in GitHub Desktop.
Save lnunesbr/5602481 to your computer and use it in GitHub Desktop.
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…
#!/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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment