Versioned MySQL Backups with Bazaar

So maybe you have a backup solution in place and running periodically to make sure your databases are safe. At any point in time if the system fails, you're prepared. But what if, right before a backup, an unruly visitor finds an expolit on your application and wipes out a few tables? Your "backup" is then no longer a backup you can rely on.

How do we fix this? Versioning. While popular source control management systems, such as Subversion, Git, and Bazaar, are normally used for collaborating on code together, they can also be extremely handy with backups.

In this example, we'll be using Bazaar, but everything can easily be applied to Git or Subversion. Or CVS, but... let's not go there.

To start, find a directory to store your backup repository. I personally use /root/mysql. Inside that directory, start up your repository:

bzr init

Setup complete! But we haven't actually done anything yet. Next, make a backup script and fill it up:


#!/bin/bash

# cd to the script dir to make sure files are put here
cd "$(dirname $0)"

DATABASES="$(mysql -Bse 'SHOW DATABASES')"

# loop through the databases
for DB in ${DATABASES[@]}
do
mkdir -p "$DB"
TABLES="$(mysql $DB -Bse 'SHOW TABLES')"
# loop through the tables in the database
for TABLE in ${TABLES[@]}
do
mysqldump "$DB" "$TABLE" > "${DB}/${TABLE}.sql"
done
done

# version control
bzr add
bzr commit -m "cron backup"

Save that backup script inside the repository directory you just created. Mark it as executable, and add it to a system cron job. You may also want to tweak the mysql and mysqldump lines to add a password, or more simply, use a .my.cnf file in your home directory:

[client]

user = root
password = PASSWORD

Every time the script is run, it will look for all databases on the server (line 6) and make a directory for each of them. It will then look through each database individually (12) and write a separate .sql file for each table (16).

Finally, any new tables dumped will be added to the version control system (21) and the revision will finally be committed to disk (22).

Every change detected between backups can be reverted. Say the 'posts' table in the 'wordpress' database was fine at revision 17, but something went wrong on version 18. Let's revert the table back:

bzr revert -r 17 wordpress/posts.sql

That will reset the SQL backup back to revision 17, but not the database. You will still need to put the data back into the database:

mysql wordpress < wordpress/posts.sql

Again, the mysql command may need to be tweaked before using. Also, the 'posts' table will most likely need to be cleared before importing the data again.

That's it! I recommend running the cron job at least every six hours to minimize any potential data loss. If the repository is made on site, be sure to back up offsite as well. As with any backup script, your mileage may vary, and we cannot be held responsible in the event of a failure. You are encouraged to modify the script for your own needs.

Avatar for jacob Jacob Peddicord - http://jacob.peddicord.net/

Jacob is a web developer, student, and programmer from Ohio. He is a staff member at the Ubuntu Forums and is most likely a fanboy of the distribution. He loves to write in code and words, play video games, and rant about topics most would have abandoned long ago. Jacob uses GNOME and is never seen running stable software, much to the demise of his laptop.

Home » Articles »

Discussion: Versioned MySQL Backups with Bazaar

  1. Michael Greenly (guest)

    # Posted on 25 August 2008 at 02:55 AM

    Most databases provide some mechanism for doing point in time recoveries. In a production system I'd definitely recommend going that route.

    Combining regular revision controlled dumps and binary log resets may be worth if if you're trying to maintain an indefinite history. Especially if the data has lots of churn. If the data is fairly static simply compressing the binary log files is more likely more efficient.

    Still I'd recommend looking up 'point in time recoveries'



  2. gregf (guest)

    # Posted on 25 August 2008 at 03:23 PM

    I liked the idea a lot. I been backing up my databases with mysqldump for a while now, but just as tarballs. This is a much better solution. One thing I would consider doing though is adding the --compress option to your mysqldump line. Makes transfers faster. Does eat up a little cpu although nothing major.



  3. # Posted on 26 August 2008 at 09:13 PM

    [...] Version your MySQL&nbsp;backups Filed under: Linux, Security &#8212; 0ddn1x @ 2008-08-26 21:12:45 +0000 http://fosswire.com/2008/08/24/versioned-mysql-backups/ [...]



  4. # Posted on 26 August 2008 at 09:16 PM

    [...] Version MySQL&nbsp;backups Filed under: Linux, Security &#8212; 0ddn1x @ 2008-08-26 21:12:45 +0000 http://fosswire.com/2008/08/24/versioned-mysql-backups/ [...]



  5. Jeff (guest)

    # Posted on 03 September 2008 at 02:12 AM

    One problem with the mysqldump is it adds a timestamp at the end, causing every commit to be seen as different. To get around that, add the following before the bzr commands in your script. Use at your own risk!

    sed to remove timestamp

    put your own parent directory of the sql dumps in here

    FILES="$(find /path/to/dumps -type f -name '*sql')"

    loop through files

    for SQL in $FILES
    do
    

    sed command to delete any line starting with the timestamp

    the -i retains the same file

    sed '/-- Dump completed/d' "$SQL" -i -n
    

    done



  6. Bill Horsman (guest)

    # Posted on 23 September 2008 at 06:08 AM

    @Jeff: you can also tell mysqldump to skip all the comments completely. That might be easier:

    mysqldump --skip-comments "$DB" "$TABLE" &gt; "${DB}/${TABLE}.sql"



Home » Articles » Versioned MySQL Backups with Bazaar