≡ Menu

Shell Script To Backup MySql Database Server

# 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):
# http://www.cyberciti.biz/nixcraft/vivek/blogger/2005/01/mysql-backup-script.html
# 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 : http://cyberciti.biz/fb/
# -------------------------------------------------------------------------
# This script is part of nixCraft shell script collection (NSSC)
# Visit http://bash.cyberciti.biz/ 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")"
# 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

Save the script and run it as a cron job:
@daily /path/to/yourmysql.sh

Comments on this entry are closed.

  • TurboPT

    Q1: …but i have not see this being used as mysqldump anywhere.
    A1: see the last if condition.

    Q2a: is INFORMATION_SCHEMA backed up in your script ?
    Q2b: Is this useful to backup?
    A2a: …if it appears in the show databases result, and it is not set to be skipped in the script, then yes.
    A2b: could be. The point is data recovery. Preview that database to see what you could potentially lose if not.

    Q3: …any advantage for backing up each database?
    A3: this is merely an example that you can tailor to your need(s). Having them separately could be advantageous when not “all databases” need to be restored.

  • Ashok Kumar

    I want to use this mysql backup script with incremental an weekly full backup.

    Can you i use this for this purpose and also facing error during the run this script.

    Error:- [: 70: -1: unexpected operator

    Please help

  • Ashok

    I am facing error during run the script

    [: 64: =-1: unexpected operator

  • Narendra Gollapilli

    You can the below lines before creating directory for today’s backup.

    RDIR=”$DEST/mysql/$(date -d “7 day ago” +”%d%m%Y”)”
    if [ -d $RDIR ];
    rmdir $RDIR;
    echo “Removing MySQL backup of date $(date -d “7 day ago” +”%d%m%Y”)”
    Narendra Gollapilli

  • Narendra Gollapilli

    The above lines i have mentioned for backup rotation for 7 days i.e removing data older than 7 days.

  • mccoy


    instead of # DO NOT BACKUP these databases,
    how can i do it as they other way around? (selecting specific database).

    i am very new to linux. please help. thanks alot!

  • you can do so by modifying


    DBS=”$($MYSQL -u $MyUSER -h $MyHOST -p$MyPASS -Bse ‘show databases’ |grep )”
    as it will return a list of your databases only which contain the given text.

    It works for my without any error as i saved this as .sh file

  • Here is a few tips for things that I ran into.

    First off, root doesnt have access to locked tables, so this is what I did to fix that.

    Edit this line:
    $MYSQLDUMP -u $MyUSER -h $MyHOST -p$MyPASS $db | $GZIP -9 > $FILE

    $MYSQLDUMP –skip-lock-tables -u $MyUSER -h $MyHOST -p$MyPASS $db | $GZIP -9 > $FILE

    By adding the –skip-lock-tables, the command will ignore the locktable feedback and continue on what it can actually do.

    Secondly, I also was recieving an error like “\r’: command not found”. This is caused from the way DOS(windows) writes a return key. This can be fixed by the following 2 commands:

    apt-get install dos2unix #if it is not installed already
    dos2unix /path/to/file.sh

    I hope this helps someone.

  • Nice little script, handy and quick to use.

    Ashok: Try to delete one of the equal signs on the actual rows you are having problems with.

  • Using Narendra Gollapilli’s prune lines in Ubuntu Server 11.04, had to change from “rmdir” to:
    rm -rf $RDIR;
    First day so I did not test the seven days cicle, other than that it is working. Also have to replace html left and right “” for “.
    About Niftyapple’s –skip-lock-tables note that html on this page replaced — for –.
    The correct is –skip-lock-tables

  • I mean TWO dashes, sorry it did with me again – – .
    Wordpress changes a lot of code stuff…
    two – to one —

  • Daniel Clarks


  • Can you help my problem ?

    in line 55 = DBS=”$($MYSQL -u $MyUSER -h $MyHOST -p$MyPASS -Bse ‘show databases’)”

    when i try to execute, this syntax has a problem, can anyone help to fix it ?

  • Luis Anaya

    This is a really nice script although I did some changes for it, nothing major:

    – Commented the CHOWN. For some reason is was doing a root wide CHOWN, being that I am not running the backups as root, I do not need to CHOWN the files anyway, they’ll be owned by the administrative account.
    – I had to add the list of databases that I wanted not to back up, I though that just adding the ones that I wanted would exclude the ones that I did not wanted to.

    But, it is a very handy script and I appreciate you posting it. Thanks.