Shell Script To Backup MySql Database Server

#!/bin/bash
# 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.
# -------------------------------------------------------------------------
 
MyUSER="SET-MYSQL-USER-NAME"     # USERNAME
MyPASS="SET-PASSWORD"       # PASSWORD
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
DEST="/backup"
 
# Main directory where backup will be stored
MBD="$DEST/mysql"
 
# Get hostname
HOST="$(hostname)"
 
# Get data in dd-mm-yyyy format
NOW="$(date +"%d-%m-%Y")"
 
# File to store current backup file
FILE=""
# Store list of databases
DBS=""
 
# DO NOT BACKUP these databases
IGGY="test"
 
[ ! -d $MBD ] && mkdir -p $MBD || :
 
# Only root can access it!
$CHOWN 0.0 -R $DEST
$CHMOD 0600 $DEST
 
# Get all database list first
DBS="$($MYSQL -u $MyUSER -h $MyHOST -p$MyPASS -Bse 'show databases')"
 
for db in $DBS
do
    skipdb=-1
    if [ "$IGGY" != "" ];
    then
	for i in $IGGY
	do
	    [ "$db" == "$i" ] && skipdb=1 || :
	done
    fi
 
    if [ "$skipdb" == "-1" ] ; then
	FILE="$MBD/$db.$HOST.$NOW.gz"
	# 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
    fi
done

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

Featured Articles:

Want to read Linux tips and tricks, but don't have time to check our blog everyday? Subscribe to our email newsletter to make sure you don't miss a single tip/tricks.

{ 1 trackback }

mysql in fedora core 4 - The UNIX Forums
April 17, 2008 at 10:14 am

{ 13 comments… read them below or add one }

1 Thiago July 3, 2008 at 2:13 pm

It`s very good

thanks

Reply

2 Stephen Reese August 23, 2008 at 3:19 am

Great script! I really like the addition of the which command.

Reply

3 ajax December 7, 2008 at 6:09 pm

all, you are done great server works,

thx

Reply

4 DVD March 20, 2009 at 2:13 pm

Nice script.

Reply

5 Gilnei Moraes April 22, 2009 at 1:04 pm

Just my one cent:
run rsync daemon using the backup directory just to be sure you are playing safe. Is worthless to backup MySQL not having a copy in remote location in case of a server crash.

Reply

6 fRank May 15, 2009 at 10:13 am 
#!/bin/bash

NOW=`date +"%Y-%m"`;
BACKUPDIR="location/of/your/backup/dir/$NOW";

### Server Setup ###
#* MySQL login user name *#
MUSER="user";

#* MySQL login PASSWORD name *#
MPASS="pass";

#* MySQL login HOST name *#
MHOST="your-mysql-ip";
MPORT="your-mysql-port";

# DO NOT BACKUP these databases
IGNOREDB="
information_schema
mysql
test
"

#* MySQL binaries *#
MYSQL=`which mysql`;
MYSQLDUMP=`which mysqldump`;
GZIP=`which gzip`;

# assuming that /nas is mounted via /etc/fstab
if [ ! -d $BACKUPDIR ]; then
  mkdir -p $BACKUPDIR
else
 :
fi

# get all database listing
DBS="$(mysql -u $MUSER -p$MPASS -h $MHOST -P $MPORT -Bse 'show databases')"

# SET DATE AND TIME FOR THE FILE
NOW=`date +"d%dh%Hm%Ms%S"`; # day-hour-minute-sec format
# start to dump database one by one
for db in $DBS
do
        DUMP="yes";
        if [ "$IGNOREDB" != "" ]; then
                for i in $IGNOREDB # Store all value of $IGNOREDB ON i
                do
                        if [ "$db" == "$i" ]; then # If result of $DBS(db) is equal to $IGNOREDB(i) then
                                DUMP="NO";         # SET value of DUMP to "no"
                                #echo "$i database is being ignored!";
                        fi
                done
        fi

        if [ "$DUMP" == "yes" ]; then # If value of DUMP is "yes" then backup database
                FILE="$BACKUPDIR/$NOW-$db.gz";
                echo "BACKING UP $db";
                $MYSQLDUMP --add-drop-database --opt --lock-all-tables -u $MUSER -p$MPASS -h $MHOST -P $MPORT $db | gzip > $FILE
        fi
done

Reply

7 Brian Nettles July 16, 2009 at 9:50 pm

Incredible!!!!!!!

I have wasted hours trying to do this in perl. One small copy and paste from your site and all of the mysql databases are now being automatically backed up. Thank you so much. You are worth your weight in gold.

Reply

8 No Brains!! September 9, 2009 at 12:44 pm

Forgive the ignorance but would you have to stop the mySQL service before running this script?

Reply

9 Vivek Gite September 11, 2009 at 11:45 am

No

Reply

10 sukhbir September 29, 2009 at 5:34 am

this script is very good.

Can anybody having a script to extract database from MYSQLDUMP.

Regards,

Reply

11 Hunner October 21, 2009 at 4:05 am

`mysql -u $user -p < backup.dump` will take a dump file and put it back into the DB.

Reply

12 Deven J February 4, 2010 at 3:56 pm

Undoubtedly, very simple and useful.

Reply

13 Zeb Evans February 27, 2010 at 5:02 am

Just a note that will hopefully help others. I’m just starting to learn shell scripting all my other ones have been using phpcli so take this with a grain of salt. I was getting a bunch of these errors.

==: unexpected operator

I had to change these lines and use the single = comparison instead of ==

[ "$db" = "$i" ] && skipdb=1 || :
done
fi

if [ "$skipdb" = "-1" ] ; then

Reply

Previous post:

Next post: