Hourly Production Server Database And File Backups

How to setup hourly backups for your production server



Overview

It's incredibly dangerous to run a production server without setting up automatic backups. I often go with hourly database and file backups on my production servers. If anything goes wrong, we've got a recent backup to restore from.

This is pretty simple to setup with the Backup gem and has all the features we could need including email alerts when things go wrong.

Install the Backup gem

Login into your server with the same user account that your app runs as. For me, this is the deploy user.

Assuming you've already setup your server, you should already have Ruby installed. You can simply install the latest version of the backup gem like so:

gem install backup

Setup your Backup script

First we want to generate a new backup script:

backup generate:model --trigger production_backup

This will generate a folder in your user's home directory called Backup It will have a couple files inside:

  • config.rb - This is your main Backup configuration file. You can read through this if you like, but you probably won't need to change it
  • log - This is the where the backup logs are stored
  • models - This folder contains your scripts, specifically production_backup.rb that we just generated

Now we can edit ~/Backup/models/production_backup.rb to backup our database, files, and notify us.

Be sure to change the parts that are in all CAPS to match your server setup.

Open this file with Vim or Nano. I'll be using Vim:

vim ~/Backup/models/production_backup.rb

Replace the contents with the following config:

# encoding: utf-8

##
# Backup Generated: production_backup
# Once configured, you can run the backup with the following command:
#
# $ backup perform -t production_backup [-c <path_to_configuration_file>]
#
Model.new(:production_backup, 'Production Backup') do
  split_into_chunks_of 250
  compress_with Gzip

  ##
  # MySQL [Database]
  #
  database MySQL do |db|
    # To dump all databases, set `db.name = :all` (or leave blank)
    db.name               = "DATABASE_NAME"
    db.username           = "DATABASE_USERNAME"
    db.password           = "DATABASE_PASSWORD"
    db.host               = "localhost"
    db.port               = 3306
    db.additional_options = ["--quick", "--single-transaction"]
  end

  ## 
  # Archive our app
  #
  archive :app_archive do |archive|
    archive.use_sudo
    archive.add '/home/deploy/MYAPP/'
  end

  ##
  # Store on Amazon S3
  #
  store_with S3 do |s3|
    s3.access_key_id = "ACCESS_KEY_ID"
    s3.secret_access_key = "SECRET_ACCESS_KEY"
    s3.region = "us-west-2"
    s3.bucket = "BUCKET_NAME"
    s3.path = "/production/database"
  end

  ##
  # Mail [Notifier]
  #
  notify_by Mail do |mail|
    mail.on_success           = false
    #mail.on_warning           = true
    mail.on_failure           = true

    mail.from                 = "[email protected]"
    mail.to                   = "YOUR_EMAIL_ADDRESS"
    mail.address              = "smtp.mandrillapp.com"
    mail.port                 = 587
    mail.domain               = "YOUR_DOMAIN"
    mail.user_name            = "YOUR_SMTP_USERNAME"
    mail.password             = "YOUR_SMTP_PASSWORD"
    mail.authentication       = "login"
    mail.encryption           = :starttls
  end

end

What this will do is setup a backup for MySQL to be dumped and archive your Rails app (including any file uploads that are stored locally). All of this is saved to remotely on Amazon S3 and then it will email us if anything goes wrong.

You can also set on_success to true if you want to get emails on successful backups.

There's a lot of Documentation on the Backup gem if you want to make any modifications. They also support different databases like PostgreSQL, Mongo, Redis, many other storage locations and notification methods.

Test your Backup script

This is easy. Just run the following in your terminal. It will automatically detect the script in the models folder and run it for you.

backup perform -t production_backup

You should get some output telling you what the Backup is doing and, if you configured everything correctly, it will succeed and you will have a new file stored in your S3 bucket.

Schedule Your Backups

Now we need to make this script run every hour. We're going to use Cron to do this.

So first, let's grab the executable path:

which backup
# /usr/local/bin/backup

Take note of the output of this command because you're going to use it next.

And now let's type crontab -eto setup our Cron job. Add a line at the bottom that looks like the following:

0 * * * * /bin/bash -l -c '/usr/local/bin/backup perform -t production_backup'

Be sure to replace /usr/local/bin/backup with the output of the which backup command. This will make sure the Cron job can find the executable for Backup so that it can run your script.

This line basically tells Cron to perform the backup at the top of every hour. The cron format can be kind of hard to read, so if you'd like to learn more about it, take a look here.

If editing the crontab by hand doesn't strike your fancy, you can also use the Whenever gem to manage your backup Cron job instead. This does let you save your cron jobs into your Git repo so you can manage them easily. Definitely worth checking out.

Conclusion

With the Backup gem, it's super simple to backup your application and production data. Since your code is stored in a Git repository (I hope!) you don't need to worry about it too much. But when it comes to production data, you want to make sure it is backed up regularly, on a schedule, and saved to a remote machine in case anything happens to the server. The Backup gem helps you through a whole lot of this process with relative ease and a great amount of documentation.

Always be sure to test your backups and make sure you can safely restore from them!



Discussion


Gravatar

Thank you for this! I should get this up and running on a site I run for a complex fantasy football league...I've been meaning to with Cron jobs and whatnot, but it's just such a hassle and, so far, there hasn't been much site traffic, but still...a catastrophe could be hiding around an upcoming corner!

Gravatar

Glad I could help! :)

Gravatar

I've been horribly slow about getting this implemented (had to do a bunch of work on the site first) and I went about doing this stuff last night. The backup script is working brilliantly and I've restored from one of the backups to confirm it, so thank you for the writeup!

However, the Cron portion is proving to be quite the headache. I'm at Digital Ocean with an Ubuntu 12.10 server. Running crontab -e, I was getting errors about nano permissions but found out how to get rid of that nonsense (I can't find the link now, but it was removing a file and commenting out a line about logging stuff). Anyway, the cron job is in my file:

0 * * * * /bin/bash -l -c '/home/kkerley/.rvm/gems/ruby-1.9.3-p392/bin/backup perform -t sqwid_backup'

(got this by running crontab -l just now) but it's never firing. I thought maybe it was due to not having a newline at the end, but I've put one in three times. I also ran pgrep cron to confirm that it's running and it is (returned 598). I just don't understand what I'm doing wrong/why this isn't firing off hourly.

Are there any gotchas that I'm just not aware of? This is the first time I've messed with cron.

Thanks!

Gravatar

One thing (and I updated the line) is that it should be perform -t production_backup to match the name of the backup that you created earlier.

That should be it I imagine.

Gravatar

Ahhh! I didn't even notice. I saw that last night and thought it was weird when I was typing it in but figured it was just some weird cron command. :)

I've updated my cronjob and will hopefully know in 51 minutes if it worked or not.

Thanks for the quick reply and again for the tutorial!

Gravatar

It's working like a charm now. Thank you again for this guide!

Gravatar

Awesome!! :)


Gravatar

s3.path = "/production/database"
Is the the end point or path with the bucket? I get The bucket you are attempting to access must be addressed using the specified endpoint. Please send all future requests to this endpoint.

Does one need to set any permission on the bucket?

Gravatar

Solved, just needed to fix my bucket region.


Gravatar

For anyone getting this warning.

[fog][WARNING] fog: the specified s3 bucket name(BUCKET_NAME) is not a valid dns name, which will negatively impact performance.

Fog does not like dash in the the BUCKET_NAME. It's best to use BUCKETNAME.

Gravatar

Good find! Thanks for sharing this.


Gravatar
Anthony Candaele on

Thanks Chris for this very useful guide. I just implemented the Backup gem and the cron job. And it is successfully storing a backup of the database to Amazon S3.

At the end of the guide you say:

"Always be sure to test your backups and make sure you can safely restore from them!"

So how do you restore your database with a backup on Amazon S3?

greetings,

Anthony

Gravatar

Depends on what you're backing up, but for example, you can restore a mysql database with a sql file like this: http://stackoverflow.com/a/...

Gravatar
Anthony Candaele on

I'm backing up a PostgreSQL database. I believe the pg_dump utility has a feature for restoring databases from a remote location.

Gravatar
Anthony Candaele on

I tried to restore the database with:

sudo su - postgres
psql -1 posplus < production_backup

but nothing happens


Gravatar
Ismael Camara on

Hello Chris, thanks for this useful website! I am getting the following error with Amazon S3: AuthorizationHeaderMalformed<message>The authorization header is malformed; the authorization header requires three components: Credential, SignedHeaders, and Signature.</message>

I have tried to use GIYF without success. I have paperclip gem in my rails app working fine with the same S3 credentials and same bucket but backup gem is not! Can you help me please?

Gravatar

Interesting. I can't think of much except for maybe having a typo somewhere.


Gravatar

Code was broken


Gravatar
Andrew Shaydurov on

Just keep it more simple with https://backup.foundation


Gravatar

is there a way to use backup gem on heroku instance and setup? thx

Gravatar
Jordano Moscoso on

Use pg backups. Is easier on heroku.


Gravatar
Tyler Kessler on

Hey Chris, Backup gem link is broken, new link should go here: https://github.com/backup/b...


Gravatar

[fog][WARNING] fog: followed redirect to my-bucket.s3-eu-west-1.amaz..., connecting to the matching region will be more performant
[info] CloudIO::Error: Retry #1 of 10
[info] Operation: PUT 'path/production_backup.tar'
[info] --- Wrapped Exception ---
[info] Excon::Errors::BadRequest: Expected(200) <=> Actual(400 Bad Request)
[info] excon.error.response
[info] :body=> "\n<error>IncompleteBody<message>The request body terminated unexpectedly<

Any Idea?


Gravatar

Hi Chris, my application db size is 84 GB. If the backup runs every one hour will it not hamper the application. What should be the ideal way?

Gravatar

Lots of options. Depending on how long it takes you might want to do this every 4 hours or something instead. You might want to do a live replica database for realtime backups to another server and then archive a copy of it nightly.

Gravatar
Jordano Moscoso on

You should use database replicas and make partial and transaction backups. I did that on MS SQL server but i don't know how that would be achieved on Postgres or Mysql.


Gravatar
Ali Ahmed on

Hey Chris this is interesting. But I need the restore functionality. Do you think you have a way arounf?

Gravatar

You've just got to download the backup, extract it, and reimport your database and do the same for whatever else you may have backed up.


Gravatar

Hey Chris! Are you still using this method in production? I haven't been able to get the backup gem to work with ruby 2.4.x, have you gotten it to work with rails 5.1 and a recent ruby?


Login or create an account to join the conversation.