Automatically backing up your remote database on deploy
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.
3 Comments
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.
Great idea brandon.
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
Post a Comment