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:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

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