Back up MySQL Databases with a Simple Bash Script

Productivity Sauce

If you host your own blog or any Web-based application running on the Apache/MySQL/PHP stack, you should have a backup system in place for keeping data stored in MySQL databases safe. There are several solutions that can help you with that, but nothing beats a simple Bash script I stumbled upon in a blog post comment. Here is the script in all its beauty:

#!/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

The best part is that you only need to specify a handful of parameters to make the script work. This includes BACKUPDIR (the destination for storing backups), MUSER (MySQL user), MPASS (MySQL user password), MHOST (the IP address of the MySQL server, e.g. localhost), and MPORT (the port the MySQL database is running on, default is 3306).

You can run the script manually, or you can set up a cron job which will perform backups on a regular basis. To do this, run the crontab -e command and add the following line (replace the sample path with the actual path and backup script name):

@daily /path/to/mysqlbackupscript.sh

Don't forget to make the script executable using the chmod a+x mysqlbackupscript.sh command.

Comments