Automatically backing up your remote database on deploy

backup | capistrano | database | migration | rails February 09 2007

Ever had a migration fail when you’re deploying a sparkling new release of your snazzy web app, leaving your database in an inconsistent state? I have. Fortunately, a faulty migration has never completely hosed my production database, but I have had to ssh in, comment some lines out of a migration, and re-run it.

Now, I can rest assured that I’ll never have the experience of hosing my production database without being able to recover it. Here are Capistrano recipes to backup your remote production database whenever you run migrate on your remote database.

require 'yaml'

desc "Backup the remote production database"
task :backup, :roles => :db, :only => { :primary => true } do
  filename = "#{application}.dump.#{Time.now.to_i}.sql.bz2"
  file = "/tmp/#{filename}"
  on_rollback { delete file }
  db = YAML::load(ERB.new(IO.read(File.join(File.dirname(__FILE__), 'database.yml'))).result)['production']
  run "mysqldump -u #{db['username']} --password=#{db['password']} #{db['database']} | bzip2 -c > #{file}"  do |ch, stream, data|
    puts data
  end
  `mkdir -p #{File.dirname(__FILE__)}/../backups/`
  get file, "backups/#{filename}"
  # capistrano < 1.4
  # `rsync #{user}@#{roles[:db][0].host}:#{filename} #{File.dirname(__FILE__)}/../backups/`
  delete file
end

desc "Backup the database before running migrations"
task :before_migrate do 
  backup
end

The backup recipe is adapted from ones presented by Caboo.se and Bojan Mihelac. I modified it to use my local database.yml. Thanks to Daniel Morrison, who came up with the idea of running the backup task before migrating.

posted by brandon | updated February 15th 02:35 PM
comments feed

3 comments

  1. Nice. I have had this happen myself a few times as well. Bonus points might be to try to catch the failure when it occurs and automatically restore the backup as part of a rollback process.

    Eric Anderson Eric Anderson
    February 09, 2007 at 10:57 AM
  2. Great idea brandon.

    John Nunemaker John Nunemaker
    February 09, 2007 at 11:46 AM
  3. Nice one, Brandon. Worked a treat. The only thing I had to change was the mysqldump line to:

    run "mysqldump -u #{db['username']} --password=#{db['password']} -h #{db['host']} #{db['database']} | bzip2 -c > #{file}"  do |ch, stream, data|

    To accomodate the fact that my database (at MediaTemple) isn’t on localhost.

    Thanks

    Ritchie Ritchie
    April 05, 2007 at 10:46 PM

Speak your mind:

(Required)

(Required)


(You may use textile in your comments.)

About

I'm Brandon Keepers, a web application developer that likes beautiful code, valid markup and adherence to standards. As a part of Collective Idea in Holland, Michigan, I practice Agile software development primarily using Ruby on Rails.

-86.103171 42.785037

Contact:

more ยป

Syndicate