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.

Pretty YouTube Embeds with PrettyEmbed.js

Prettier YouTube Embeds

PrettyEmbed.js is a jQuery plugin for making your YouTube embeds look much better. It’s on GitHub, and a demo can be seen here on CodePen.

PrettyEmbed.js works with FitVid.js, but it’s not required, and comes with options like high-res preview images and advanced customization of embed options.

You can see some of the advanced customization options in the CodePen demo below. Just click the “JS” tab to see the JavaScript.

See the Pen PrettyEmbed.js Demo by Mike Zarandona (@mike-zarandona) on CodePen.

I’ve used this for a number of client websites, people like to host their videos on YouTube, yet have an elegant looking player that’s not screaming YOUTUBE when it’s embedded. This is where PrettyEmbed.js comes in. This is for those of you who want nice looking YouTube embeds.

Documentation is quite good and provides a list of all options and how to use it with HTML5 or with Javascript. It does require jQuery.

Aggregate System and Application Logs with Papertrail

Frustration-free log management

I’ve been using Papertrail for a few months now, and absolutely love it. Being able to search logs across all my servers at once is crazy nice.

I can even get alerts when someone logs in via SSH, which, by itself, has made Papertrail well worth it.

A non-production server was compromised, due to a since-rectified configuration issue. Papertrail notified me almost immediately, allowing for immediate action to be taken.

There’s a variety of pricing plans, and there’s even a free for life plan, which includes plenty of features for most folks. I’m currently on the free plan, but plan on upgrading soon. Adding more servers and will need the extra space at Papertrail.

Do you use a log management service?

View Results

Loading ... Loading ...

In addition to collecting logs from your servers, you can also send logs from your applications. Got a PHP application that’s erroring out for some reason? You can send that error to Papertrail for later investigation.

Same deal with Apache logs, MySQL logs, and pretty much every other piece of software that generates logs.

Not many limits on what you can configure Papertrail to do for you. It’s very powerful.

I suggest you give it a try. Installation is super easy, especially if you’re using rsyslog. Below is a screenshot of their installation instructions. Doesn’t get much easier than that.
papertrail-install

Change your WordPress Theme Favicon

Really easy to do, but rarely done

Not sure how to create your favicon? I typically use GIMP, along with this tutorial. It has some good info on creating “proper” favicons, too, so it’s a worthwhile read even if you aren’t creating a favicon at the moment.

I like X-Icon Editor, too (it’s in the screenshot attached to this post), and there’s tons of converters for creating .ico favicon files.

Just add this modify the following and add it to your theme’s functions.php file, or add it to your WordPress functionality plugin.

function my_favicon() { ?>
<link rel="shortcut icon" href="/path/to/favicon.ico" >
<?php }
add_action('wp_head', 'my_favicon');

Change /path/to/favicon.ico to point to your favicon.ico file and you’re done!

How-To: Add Minimal-UI Viewport Meta Tag to WordPress

Introduced with iOS 7.1

I don’t have an iPhone, but my daughter does have an iPad Mini, which is running the latest iOS, 7.1. However, this only works for those of you on iPhone’s, so I see no difference.

Martin Wolf was kind enough to let me use the image from his post about this subject so that I could more easily illustrate the difference. So, even if you don’t have an iPhone, you can still see the changes this makes in the featured image above.

With the release of iOS 7.1 (and possibly late 7.0.x builds), Safari introduced support for a new value in the viewport meta tag. To me, it sounds like it adds effects similar to how Chrome hides its top bar when a page is loaded, but more. For example, the navigation buttons at the bottom are hidden.

I rarely use Safari, like never. Chrome is available on iOS, so that’s what I’ve always used. That’s not to say that you shouldn’t support Safari to it’s fullest, because I’d wager that a majority of iOS users stick with the default, which is Safari.

Chances are, your theme already has a viewport meta tag defined in it’s header.php file. If it does, add minimal-ui to it, so it should look something like this:

<meta name="viewport" content="width=device-width, initial-scale=1, maximum-scale=1, minimal-ui" />

If your theme doesn’t already have a viewport meta tag set, you can add one with your functionality plugin or theme’s functions.php file like so:

<?php
function set_viewport() {
?>
<meta name="viewport" content="width=device-width, minimal-ui">
<?php
}
add_action('wp_head', 'set_viewport');
?>

Adding the code above will add a brand new viewport meta tag for you, so only use that if your theme isn’t already using a viewport meta tag in it’s header.php file.

That’s it!