Back up MySQL Databases with a Simple Bash Script

Dmitri Popov

Productivity Sauce

Jan 12, 2011 GMT
Dmitri Popov

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.

« previous post next post »

Comments

  • simple & compact version

    IGNOREDB="#information_schema#mysql#test#";
    NOW=`date +%Y-%m-%d`;
    BKPDIR=/path/to/backup/$NOW;
    mkdir -p $BKPDIR;
    for DB in $(echo 'SHOW DATABASES;' | mysql -uuser -ppassword | grep -v '^Database$'); do
    if [[ "${IGNOREDB}" =~ "#${DB}#" ]]; then continue; fi
    mysqldump -uuser -ppassword --opt --add-drop-database --lock-all-tables --max_allowed_packet=500M $DB | bzip2 > $BKPDIR/$NOW-$DB.sql.bz2;
    done;

  • Oh the horror

    The script has no validation on the dump, so you can continue to think that you have a good database and dump will fail as long as your information big-smile , also if you have mysql 5 or higher --routing --triggers should be added.

  • A shorter script using regular expression support

    I didn't tried next code but using regular expression support in Bash the script can be made sorter with something like:

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

    for db in $DBS
    do
    if [ "${IGNOREDB}" =~ "#${db}#" ]; then continue; fi
    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
    done

    Avoiding the second inner loop and the $DUMP variable.

    My two cents!

  • Backing up large databases

    This script will fail for large databases because it'll exceed typical max_packet_size settings.

    You'll want to add something like --max_allowed_packet=500M to the mysqldump command line to work around that.
comments powered by Disqus