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.

Record Speedtest.net Results From the Command Line

For those who live in the command line as much as possible

Install speedtest-cli

speedtest-cli is a Python app that provides a command line interface for testing bandwidth using speedtest.net. Installation is simple. It should work on Linux and OS X.

The Bash Scripts

There’s two scripts, speedtest.sh and speedtest-simple.sh. Pretty self-explanatory. Results from speedtest.sh are stored in st_results in the current working directory. speedtest-simple.sh results are stored in st_results_simple, also in the current working directory.

speedtest.sh Results

Below are the results of two speedtests run with speedtest.sh, along with the sharing image URL.

Retrieving speedtest.net configuration…
Retrieving speedtest.net server list…
Testing from Mediacom Communications (173.22.40.33)…
Selecting best server based on ping…
Hosted by CHRJO (Council Bluffs, IA) [204.50 km]: 20.981 ms
Testing download speed………………………………….
Download: 32.44 Mbit/s
Testing upload speed…………………………………………..
Upload: 5.57 Mbit/s
Share results: http://www.speedtest.net/result/3335225265.png .
#############

Retrieving speedtest.net configuration…
Retrieving speedtest.net server list…
Testing from Mediacom Communications (173.22.40.33)…
Selecting best server based on ping…
Hosted by American Broadband (Blair, NE) [213.79 km]: 20.981 ms
Testing download speed………………………………….
Download: 33.03 Mbit/s
Testing upload speed…………………………………………..
Upload: 5.49 Mbit/s
Share results: http://www.speedtest.net/result/3335230578.png .
#############

You can get less verbose output by modifying the speedtest-cli flags. speedtest-cli --simple --share will produce very simple results that are a bit easier to read. Two tests with the speedtest-simple.sh script are below. Note the absolute crap speeds. Wonderful hotel wi-fi, hah!

speedtest-simple.sh Results

Ping: 13.351 ms
Download: 1.92 Mbit/s
Upload: 0.94 Mbit/s
Share results: http://www.speedtest.net/result/3342143070.png .
#############

Ping: 13.431 ms
Download: 1.80 Mbit/s
Upload: 0.93 Mbit/s
Share results: http://www.speedtest.net/result/3342149985.png .
#############

It’s a very, very simple way of logging the speedtest.net results, but it’ll do for most situations. When I get some free time this week, I’m going to combine speedtest.sh and speedtest-simple.sh and make it accept a --simple argument to generate the simple log.

After that’s done, I’ll be dropping the results into a SQLite database. I’ve gotten pretty familiar with SQLite lately, so it shouldn’t be too difficult.

Once this stuff is logging to a SQLite databse, I’ll put it up on GitHub, I can’t be the only one who would love to run SQL queries against this sort of personalized bandwidth data.

Update April 19, 2014: Updated the code in the GitHub Gist to include the date and time of the speedtest.