MySQL Backup Strategy
As I mentioned in a past blog entry, I’ve been working my butt off on a new vertical market product, which is currently in beta. As I finished off a section of code last week, I started thinking about the database – it was growing larger by the day, both in table count and data population. Data backup and recovery was a growing concern. While hosted services carry their own backup solutions (usually automatically part of your hosting plan — whether they say so or not [think: liability!]), the same can’t be said for most developers who tend to develop locally, and rely heavily upon manual backups when they feel they reach a critical point in development.
Anyway, After reading a number of interesting articles on homebrew backup solutions (one interesting method involved using GIT to store differential backups, which is nice!), I ultimately decided I needed something more fine-grained – since most of them relied on mysqldump of entire databases into a single file. I like being able to run data stress tests and just restore a single table afterwards rather than having to do a whole import.
The option I selected was to write a bash script and have it run off of cron (since I run Linux). It’s fast, reliable, and uses very little space. Thought I’d share it, if theres anyone else out there who needs something similiar. it creates incremential backups separated into directories named after the date, then further separated by database name. Each individual table is dumped to its own .sql file and bzip’d. Here’s the bash code
#!/bin/bash
# cd to the script dir to make sure files are put here
NOW="$(date +"%Y%m%d")"
BACKUP_PATH="/mnt/backups/db/$NOW"
if [ ! -e "$BACKUP_PATH" ]; then
mkdir -p $BACKUP_PATH
fi
cd $BACKUP_PATH
DATABASES="$(mysql -Bse 'SHOW DATABASES')"
# loop through the databases
for DB in ${DATABASES[@]}
do
mkdir -p "$DB"
echo "Backing up $DB:"
TABLES="$(mysql $DB -Bse 'SHOW TABLES')"
# loop through the tables in the database
for TABLE in ${TABLES[@]}
do
echo "===$TABLE"
mysqldump "$DB" "$TABLE" | bzip2 > "${DB}/${TABLE}.bz2"
done
done
echo "done."
I put in the echos so I’d get a nice somewhat formatted email from cron whenever it runs. You can of course remove them. Any feedback or updates would be appreciated. Thanks!
Popularity: 61% [?]
If you enjoyed this post, please consider to leave a comment or subscribe to the feed and get future articles delivered to your feed reader.
