BASH Quickie: Backing Up MySQL Databases

By · Published · linux, mysql, bash

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:

  1. Dump each database to a separate SQL file, with a timestamp.

  2. bzip the file.

  3. 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.

( Comments )

Did something I wrote help you out?

That's great! I don't earn any money from this site - I run no ads, sell no products and participate in no affiliate programs. I do this solely because it's fun; I enjoy writing and sharing what I learn.

All the same, if you found this article helpful and want to show your appreciation, here's my wishlist.

Related Posts

Backing Up and Rotating MySQL Databases the Easy Way

Recursive Queries with MySQL

comments powered by Disqus