This Capistrano task connects to your production database and dumps the contents to a file. The file is compressed and put in a directory specified with set :backup_dir, ”#{deploy_to}/backups”. This is a slight modification of http://pastie.caboo.se/42574. All credit to court3nay.

   1  task :backup, :roles => :db, :only => { :primary => true } do
   2    filename = "#{backup_dir}/#{application}.dump.#{Time.now.to_f}.sql.bz2"
   3    text = capture "cat #{deploy_to}/current/config/database.yml"
   4    yaml = YAML::load(text)
   5  
   6    on_rollback { run "rm #{filename}" }
   7    run "mysqldump -u #{yaml['production']['username']} -p #{yaml['production']['database']} | bzip2 -c > #{filename}" do |ch, stream, out|
   8      ch.send_data "#{yaml['production']['password']}\n" if out =~ /^Enter password:/
   9    end
  10  end

To automatically backup your data before you deploy a new version add this to config/deploy.rb:

   1  task :before_deploy do
   2      backup
   3    end

To restore the backup run the following command:

   1  mysql database_name -uroot < filename.sql