Shell Script To Backup MySql Database Server

in Categories Backup, MySQL last updated March 12, 2010
# 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")"
# 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/

Share this on:

55 comment

  1. 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 ?

  2. 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

  3. 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.

  4. 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/

    I hope this helps someone.

  5. 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

  6. hi,

    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!

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

  8. 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

  9. 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

  10. Vivek

    I am new to MYSQL. I saw your script. Please help me understand what you are doing
    MyHOST=”localhost” # Hostname
    eg: variable MyUSER will contain SET-MYSQL-USER-NAME, but i have not see this being used as mysqldump anywhere.

    is INFORMATION_SCHEMA backed up in your script ? Is this useful to backup?

    -all-databases will dump ALL databases, is there any advantage for backing up
    each database?

    Thank You


    1. 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.

  11. Hi,

    I try this script in Ubuntu 10.04 and works great. When I move to centos 5.6 I get the following error:

    No such file or Directory /usr/bin/mysql

    I try the line: DBS=”$($MYSQL -u $MyUSER -h $MyHOST -p$MyPASS -Bse ‘show databases’)” manually and its works great. Any ideas?


  12. You dont need to add any databases to the “file” or “dbs” variables. Those empty values just initialize the variable. If you want to skip databases, use a standard list, no commas etc.

    IGGY="test thisdatabase thatdatabase herdata mydata"
  13. # File to store current backup file
    # Store list of databases

    # DO NOT BACKUP these databases

    Hey I want some help on above.
    What shall i add in file?
    And can I add multiple dbs in DBS and IGGY ?If so? then how?gimme one example.

    1. You can enter databases separated with a space here that you don’t want to backup.

      IGGY=”test test2 test3”

  14. Hi Vivek, thanks for such a good script.
    in this script you are creating one file for one db. Can I create one file for each table of a db.
    File name would be like dbname-filename-date … etc….

    Thanks in advance..

    Have a question? Post it on our forum!