DB Backups

Every application in production needs some sort of database backup scheme. There are plenty of methodologies and several Rails scripts or plugins to do just that. Here’s a brief summary of some of the popular ones:

• ar-backup is a Rails plugin which saves data as fixtures. The data of course becomes database independent. However, to store it safely you will have to push them to your SCM or manage backups separately.
• A comprehensive backup system of DB data, code and SCM to Amazon S3 is on Ruby Inside
• here is a simple rake task to take a mysqldump and push it to S3. There is no scheme for rolling backups, but if you want a simple solution, this may be enough.
• For a large database where full backups may be expensive, see this. There’s also a Rails plugin using this, however at the time of writing it seems incomplete.
• The awesome guys at Collective Idea have a polished Rails plugin: for backing up the database as fixtures. It also has some nifty features like ability to pull data back into development server. Read about it here
• Finally, a novel approach of backing up the database in a Git repository which seems will work for databases which are not growing very rapidly.

However, surprisingly, none of them fitted exactly what I needed and so I decided to roll my own. Also, it is quite straightforward as you will see. This is what I wanted from a DB backup system:

Take periodic dumps of the MySQL database and push them to S3 for safe storage. However, keep the old backups so that if the database gets corrupted or I want to look at the history, I have old ones available. But, I don’t want all the old ones (to cut down on storage charges), perhaps just last couple of weeks’ ones.

Courtesy of this solution, which provides rolling backups but saves to disk and some help from Amazon, I wrote this rake task. If you want to use it, copy this in lib/tasks/db_backup.rake of your Rails project

require 'find'
require 'ftools'
require 'aws/s3'

namespace :db do
  desc "Backup the database to a file. Options: RAILS_ENV=production MAX=28" 
  task :backup => [:environment] do
    AWS::S3::Base.establish_connection!(:access_key_id => 'your-S3-access-key', :secret_access_key => 'your-S3-secret-access-key')
    BUCKET = 'A-S3-bucket-name-you-created'

    datestamp = Time.now.strftime("%Y-%m-%d_%H-%M-%S")
    base_path = ENV["RAILS_ROOT"] || "." 
    file_name = "#{RAILS_ENV}_dump-#{datestamp}.sql.gz" 
    backup_file = File.join(base_path, "tmp", file_name)
    db_config = ActiveRecord::Base.configurations[RAILS_ENV]
    sh "mysqldump -u #{db_config['username']} -p#{db_config['password']} -Q --add-drop-table -O add-locks=FALSE -O lock-tables=FALSE #{db_config['database']} | gzip -c > #{backup_file}" 
    AWS::S3::S3Object.store(file_name, open(backup_file), BUCKET)
    puts "Created backup: #{file_name}" 
    FileUtils.rm_rf(backup_file)

    bucket = AWS::S3::Bucket.find(BUCKET)
    all_backups = bucket.objects.select { |f| f.key.match(/dump/) }.sort { |a,b| a.key <=> b.key }.reverse
    max_backups = ENV["MAX"].to_i || 28
    unwanted_backups = all_backups[max_backups..-1] || []
    for unwanted_backup in unwanted_backups
      unwanted_backup.delete
      puts "deleted #{unwanted_backup.key}" 
    end
    puts "Deleted #{unwanted_backups.length} backups, #{all_backups.length - unwanted_backups.length} backups available" 
  end
end

rake db:backup RAILS_ENV=production MAX=8 # take a backup of production DB, push the file to S3, keep past 8 copies

You can use S3Fox to keep a watch on backup files as they get pushed to S3. If you ever need to import back the database into local desktop – use S3Fox to download the file and do this on your development machine:

gzip -dc production_dump-YYYY-MM-DD_HH-MM-SS.sql.gz | mysql -u root -p <development database>