MySQL Backup Bash Shell Script

Which MySQL backup scripts do I use ? The question has come up a few times already so decided to post what MySQL backup script I am using right now. I’ve used quite a few different ones all using some form of mysqldump Update: script has evolved into details here.

The current MySQL backup shell script I use was written by Vivek Gite.  I altered the script to also add hour, minute and seconds in the time stamp for the mysqldump sql backup file. Settings to customise the backup script to your needs are pretty self explanatory.

The mysqldump sql backup files will be located at /backup/mysql by default. Change the location to suite your own needs. Also change CHOWN and CHMOD values if you want the backup sql files to be accessible by a user other than root. You’ll also be able to set which MySQL database names you can ignore in the backup process.

Save below script as and CHMOD +x the file and use a cronjob to schedule when to run I have a scheduled cronjob to automatically backup my MySQL databases (lightly used ones) every 12 hours.

# Shell script to backup MySql database
# To backup Nysql databases file to /backup dir and later pick up by your
# script. You can skip few databases from backup too.
# For more info please see (Installation info):
# Last updated: Aug - 2005
# --------------------------------------------------------------------
# This is a free shell script under GNU GPL version 2.0 or above
# Copyright (C) 2004, 2005 nixCraft project
# Feedback/comment/suggestions :
# -------------------------------------------------------------------------
# This script is part of nixCraft shell script collection (NSSC)
# Visit for more information.
# -------------------------------------------------------------------------
MyHOST="localhost"          # Hostname
# 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)"
# Backup Dest directory, change this if you have someother location
# Main directory where backup will be stored
# Get hostname
# Get data in dd-mm-yyyy format
NOW="$(date +"%d-%m-%Y-%H%M%S")"
# File to store current backup file
# Store list of databases
# DO NOT BACKUP these databases
[ ! -d $MBD ] && mkdir -p $MBD || :
# Only root can access it!
# Get all database list first
DBS="$($MYSQL -u $MyUSER -h $MyHOST -p$MyPASS -Bse 'show databases')"
for db in $DBS
    if [ "$IGGY" != "" ];
	for i in $IGGY
	    [ "$db" == "$i" ] && skipdb=1 || :
    if [ "$skipdb" == "-1" ] ; then
	# do all inone job in pipe,
	# connect to mysql using mysqldump for select mysql database
	# and pipe it out to gz file in backup dir :)
        $MYSQLDUMP -u $MyUSER -h $MyHOST -p$MyPASS $db | $GZIP -9 > $FILE


MaxCDN Site Acceleration

Get Cost Effective Content Delivery
Unbeatable speed, stats, & price

No Comments


  1. vBulletin hosting – List of questions to ask a new web host | - [...] (+ MySQL plugin for MySQL backup) for peace of mind.MySQL automatic backups: I’d also invest in a automated MySQL…
  2. - multi feature MySQL database backup and restore script with Percona XtraBackup & Mydumper tool support | - [...] on June 20th, 2011. I compiled the script based on the modified MySQL mysqldump backup script by Vivek Gite…

Leave a Comment