Simple Automated mySQL Backup Script

[Aldryic C'boas]

DISCLAIMER:  NEVER RUN UNTESTED SCRIPTS ON PRODUCTION DEPLOYMENTS.  ALWAYS TREAT UNTESTED SCRIPTS AS MALICIOUS, AND THOROUGHLY TEST BEFORE PUTTING TO USE.
 
Now, with that out of the way.  Some time back I wrote a script in perl to automate and organize SQL backups from our mySQL Slave server (you don't keep running slaves of important data?  Shame on you).  The goal was to keep things as simple as possible, and eliminate all but the absolutely necessary dependencies.  Today, I realized that with a little tweaking I could rewrite the script in pure bash, and essentially make it a 'drop in' script that would work on any machine already running SQL.  Had some time on my hands, so I did so.
 
First though, as this does require mysqldump access, you need to create a dedicated user for the script (what, you use a single all-powerful mysql user for everything?  Shame on you).  In our example, we'll use the username backups and the password steadytrot:

EDIT: Due to IPB sanitation, I couldn't include the actual SQL commands here.  What you need to do is create the backups@localhost user, and grant access to all DBs.  See http://pastebin.com/K3eTtUFf for the actual commands.

If you want to be even more paranoid about security, you can replace ALL PRIVILEGES with just what's needed for mysqldump.  We're specifying *.* since we want this script to be able to backup any database we specify without having to add new privileges each time.
 
This script is self-contained, and can simply be run as root - if you want, you can setup a custom user account to run from as well, just be sure to set the appropriate permissions on the directory we're about to create.  Speaking of:


mkdir /backups

For our example, we're going to have three databases we want to keep backups of - we'll call them whmcs, solus, and whiskey.  Now - case is important for this next part.  The directories we're about to create MUST have the same case as the database in SQL.  WHMCS != whmcs.  So now:


mkdir /backups/whmcs
mkdir /backups/solus
mkdir /backups/whiskey

Now, the script itself:


#!/bin/bash

    DATE=`date +%Y-%m-%d`
    LIST=`ls -d /backups/*/ | awk -F '/' '{print $3'}`
    
    for DB in $LIST; do
        ## ensure directory structure
        if [ ! -d "/backups/$DB/daily" ]; then
            mkdir /backups/$DB/daily
        fi
        
        if [ ! -d "/backups/$DB/weekly" ]; then
            mkdir /backups/$DB/weekly
        fi
        
        if [ ! -d "/backups/$DB/monthly" ]; then
            mkdir /backups/$DB/monthly
        fi
        
        ## create the actual backup
        mysqldump -u backups -p'steadytrot' $DB | gzip -c > /backups/$DB/latest.tgz
        
        ## daily backup rotation
        cp /backups/$DB/latest.tgz /backups/$DB/daily/$DB-$DATE.tgz
        for old in `ls /backups/$DB/daily -t | sed -s '1,8d'`; do
            rm /backups/$DB/daily/$old
        done;
        
        ## weekly backup rotation
        if [ $(date +%u) -eq 1 ]; then
            cp /backups/$DB/latest.tgz /backups/$DB/weekly/$DB-$DATE.tgz
            for old in `ls /backups/$DB/weekly -t | sed -s '1,5d'`; do
                rm /backups/$DB/weekly/$old
            done;
        fi
        
        ## monthly backup rotation
        if [ $(date +%d) -eq 1 ]; then
            cp /backups/$DB/latest.tgz /backups/$DB/monthly/$DB-$DATE.tgz
            for old in `ls /backups/$DB/monthly -t | sed -s '1,13d'`; do
                rm /backups/$DB/monthly/$old
            done;
        fi
    done;

Place that as /backups/backup.sh, and be sure to chmod +x the file.
 
Next step - automation.  Simply run crontab -e, and use the following:


#* * * * * command to be executed
#- - - - -
#| | | | |
#| | | | ----- Day of week (0-7), (Sunday=0/7)
#| | | ------- Month (1-12)
#| | --------- Day of month (1-31)
#| ----------- Hour (0-23)
#------------- Minute (0-59)

0 4 * * * cd /backups; /bin/sh backup.sh

And we're done - the script will run every morning at 4am system time.
 
Adding new DBs to be backed up is as simple as adding a new directory in /backups.  Removing a DB from the list is also just as simple - delete the directory, no more backups.  The first time the script runs (and the first time a new directory is added), the script will automatically create the daily/weekly/monthly subfolders.  7 daily backups are kept in rotation; 4 weeklies (with each new weekly being added on monday); and 12 monthlies (each run on the 1st of the month).
 
What makes this better than <script name here>?
That would be subjective to the user, of course.  But it's a very simple script, very small, easy to adjust, and has practically no dependencies outside of the mysql suite - which you should already have installed.  And it's not PHP >_>
 
Why are you using full paths everywhere when it appears to be redundant?
To make the script as cross-platform as possible.  Sometimes the smallest bugs (such as two distros not agreeing on pathing) can be the most annoying to deal with.
 
Why is there no error/completion reporting?
As the title states, this is a simple script.  I personally hand-check important things such as backups/etc every morning, so I did not need an extra notification in my inbox to delete.  Adding a completion notification is as simple as adding a mail -s call to the script if you really want it.
 
Can I make modifications to the script, or re-post it elsewhere?
Absolutely.  Unless I explicitly state otherwise, any code I post publicly is free-for-all - just be nice and quote the source if you repost it elsewhere :3
 
How do I know if the script is working correctly?
Just run `ls -R /backups` - you'll get a full list of each DB being backed up, and the current number/size of backups.

(Shame on you >_>)

 

[Jade]

Looks nice

 

[MartinD]

Could do this a lot easier with php.

 

[Aldryic C'boas]

I hate you

 

[Francisco]

*clicks link to this discussion in our staff channel*

"Hey cool Aldryic wrote a tutorial!"


mkdir /backups/whiskey
*closes window*
Francisco

 

[definedcode]

Pretty nice, could probably shorten the directory existence checking in bash. Or a lot in Ruby or PHP. Useful nonetheless, thanks!

 

[Francisco]

Pretty nice, could probably shorten the directory existence checking in bash. Or a lot in Ruby or PHP. Useful nonetheless, thanks!

I think his plan was to make it not require any other fat

Francisco

 

[PwnyExpress]

@Aldryic C'boas you forgot to lock the tables for a consistent backup

 

[Aldryic C'boas]

@Aldryic C'boas you forgot to lock the tables for a consistent backup

mysqldump locks tables when it runs - which is why I run a Slave SQL for our stuff, and pull backups from that (so not to have a locked master SQL when people are trying to do things).

 

[DomainBop]

Could do this a lot easier with php.

PHP might present a problem if PHP isn't installed on the server.

 

[Aldryic C'boas]

Eh, Martin was just trolling me - he knows how much I hate PHP   The folks that tried to seriously suggest that a different language would be easier?  Completely full of it.

 

[raindog308]

Very nice.  And +1 for pretty post.

You could not do this easier in PHP  But perhaps in perl.

My backup philosophy is a little different.  I don't know any scenario where I wouldn't want to backup a DB.  If I did, I'd rather have a "exclude" list than "assume exclude unless specified".  The latter in my experience is a recipe for discovering you don't have a backup you need.  I generally hold this true for all backups - backup unless specifically excluded.

I also do a full backup of the whole data store - useful if you need to restore everything though not vital if you have all the individual DBs.l

Perl makes it easy to take the name of the backup (something.yyyymmdd), convert it to epoch seconds, and determine if it's older than the current epoch - retention.  Or you can do a find/rm to remove files older than X days.

One of the headaches is error checking.  For example, if the mkdir fails, you don't want to proceed.  Also checking which command failed in pipelines is a headache.  I thought bash had a set flag that turned on "abort entire script if there is an error" but I couldn't google it up.  If there is, I'd turn it on the for the mkdir part, turn it off after, and then error check each mysqldump.

But anyway, very nice.  I'd hire you.  And there is a whiskey distillery tasting room only a short stumble from my employer...

 

[Aldryic C'boas]

And there is a whiskey distillery tasting room only a short stumble from my employer...

Fran, it's time to talk contract terms >_>

Yeah, I intentionally left out things such as sanity checking, etc.  Partially to keep the script simple, and partially to encourage others to learn more themselves - using this script as a base to expand upon.

 

[eva2000]

FYI, switch out gzip for multi-threaded gzip = pigz and watch backup times become 2x to 8x times faster (well compression part)