Backup and Compress a MySQL Database With One Command

I’ll show you how to restore the backup, too!

I like to use simple bash scripts to do various tasks. Backing up MySQL is one of them.

I recently decided to start compressing my MySQL backups, as I started including all databases in one fell swoop. I use bzip2 to compress the .sql files produced by mysqldump. bzip2 is standard on pretty much every *nix operating system, so you likely won’t need to install it.

I’m also using Tarsnap for backups now, which is a great service, btw. So cutting the size down on the backups sent to Tarsnap will save me a bit of money. I’ll be doing an article later on that focuses entirely on Tarsnap.. I’m pretty in love with it. You can find an article about installing and using Tarsnap, right here at longren.io.

Online backups for the truly paranoid
A secure online backup service.

Anyway, here’s the command I use to backup and compress all databases on my MySQL server:

mysqldump -uroot -p --opt --all-databases | bzip2 -cq9 > /home/tyler/mysql-backups/backupname.sql.bz2

That will create a backup of all databases. The -cq9 piece in the bzip2 command uses stdin for input and tells bzip2 to be quiet. The number, 9, specifies the compression level that bzip2 should use.

The script embedded in the Gist below is what I use to all my databases to /home/tyler/mysql-backups/, and then that folder gets backed up to Tarsnap.
https://gist.github.com/tlongren/85e0d7d04cd507b1ec53

To restore the database, you’ll want to bunzip2 the .sql.bz2 file first:

bunzip2 backupname.sql.bz2

That will leave you with a backupname.sql file. Then bring the resulting .sql file into MySQL like so:

mysql -uroot -pyourpasswordfornoprompt < backupname.sql

The databases will need to either already exist, or there will need to be CREATE DATABASE statements in the .sql file. It’s up to you. I like to create my databases before hand, but it’s just personal preference.

That’s all there is to it. How do you take care of your backups? I’d love to hear how others are doing it. Comments are open.

There’s a thread going on at Hacker News, too.

Update: Made a slight modification to the code and gist suggested by sluggo.

Update 2: HackerNews user Nanzikambe suggested the method above will destroy disk I/O on your server. He suggests using ZFS snapshots instead. The example he posted is in the Gist below, and includes the ability to send the backup to a remote server. A good tutorial on backing up MySQL using ZFS can be found here.

Quick and Easy Remote Backups

I backup my database, website, and my home directory on a daily basis to a remote server via SSH and rsync. Rsync is a tool that synchronizes directories. I also use it for grabbing current copies of slackware-10.1 and slackware-current.

It’s really easy to backup files to a remote server with these tools. I create tar archives of my needed directories and store the current ones in /opt/backups. I’ve made various bash scripts to do this on a daily basis for me automatically. After that’s complete, the code shown below is run. It transfers my /opt/backups directory to the remote server. It’s path on the remote server would be /home/tyler/backups. All you need to do is set the SERVER variable to the hostname of the server you’re backing up to.

The hardest part is finding a remote server that you’re able to backup to. If you’re lucky, your web host will allow you SSH access. There’s no need for the server end to have rsync installed. All it needs is SSH. I know BlueHost gives account holders SSH access. They’ve been hosting our sites at work for a while now and promptly gave me an SSH account. Their sites said they’d need an ID, but they didn’t require one from me for one reason or another. I backup to a freinds Linux box that’s located in a datacenter somewhere.