Bash Posts

Here’s a little quickie for you. Say you have a small MySQL server floating around your house that you want to have regular backups of. You do want regular backups right? In my case, the biggest motivation was wanting a regular way to grab a recent MySQL dump of an internal tool I use at home to develop against. After poking around the Internet a bit, I was surprised that, other than mysqldump itself, there doesn’t seem to be a simple tool out there that you can slam into a cronjob and let it do it’s thing. So, like any good hacker, I decided to brew my own. After all, when you have 256,428 different solutions, why not make solution 256,429? :)

In some ways, after years of doing programming and scripting, I’m now sort of rediscovering the power of the shell. Tonight, I was working on my server and remembered that I needed to start backing up my MySQL databases (which you do also … right?). So instead of writing a script to do that, with a little research, I was able to come up with a way to: Dump each database to a separate SQL file, with a timestamp. bzip the file. Keep 5 days worth of backups for each database, rotating the oldest backup off. Here’s what I came up with: cd /backup/mysql; for i in $(mysql -BNe 'show databases' -u root -p<password>); do mysqldump -u root -p<password> $i | bzip2 > $i-`date +"%Y%m%d"`.sql.bz2; rm -rf $i-`date -d "-5 day" +"%Y%m%d"`.sql.bz2; done > /dev/null 2>&1 Shoved that in my crontab. Works great. Linux rocks.