gistfile1.rb

# adapted from http://blog.caboo.se/articles/2006/12/28/a-better-capistrano-backup
# supports multistage deploy setup
# allows for restore and local database imports

namespace :db do
  desc "Copy the remote production database to the local development machine" 
  task :backup, :roles => :db, :only => { :primary => true } do
    filename = "#{application}_#{Time.now.strftime("%Y%m%d_%H%M%S")}.sql.bz2" 
    backupfile = "/tmp/#{filename}" 
    data = capture "cat #{shared_path}/config/database.yml"
    config = YAML::load(data)['production']
    on_rollback { run "rm #{filename}" }
    run "mysqldump -u #{config['username']} -p -h #{config['host']} #{config['database']} | bzip2 -c > #{backupfile}" do |ch, stream, out|
      ch.send_data "#{config['password']}\n" if out =~ /^Enter password:/
    end
    `mkdir -p #{File.dirname(__FILE__)}/../backups/`
    get backupfile, "backups/#{stage.to_s}/#{filename}" 
    run "rm #{backupfile}" 
  end
  
  desc "Restore the local backup to the production database"
  task :restore_backup, :roles => :db, :only => { :primary => true } do
    backupfile = `ls -tr backups/#{stage.to_s} | tail -n 1`.chomp
    restorefile = "/tmp/restore_#{application}_#{Time.now.strftime("%Y%m%d_%H%M%S")}.sql"
    if backupfile.empty?
      logger.important "No backups found" 
    else
      logger.debug "Loading #{backupfile} into remote database on #{stage.to_s.upcase}"
      upload("backups/#{stage.to_s}/#{backupfile}", "#{restorefile}.bz2")
      database_yml = ""
      data = capture "cat #{shared_path}/config/database.yml"
      config = YAML::load(data)['production']
      mysql_import = "bzip2 -d #{restorefile}.bz2 &amp;&amp; mysql -u #{config['username']} -p'#{config['password']}' -h #{config['host']} #{config['database']} < #{restorefile}"
      # surpress debug log output to hide the password
      current_logger_level = self.logger.level
      logger.debug %(executing "#{mysql_import.sub(/-p\S+/, '-p')}")
      self.logger.level = Capistrano::Logger::INFO
      run mysql_import do |channel, stream, data|
        puts data 
      end
      # restore logger level
      self.logger.level = current_logger_level
      run "rm #{restorefile}" 
      logger.debug "command finished" 
    end
  end

  desc "Import the latest backup to the local development database" 
  task :import_backup do
    backupfile = `ls -tr backups/#{stage.to_s} | tail -n 1`.chomp
    if backupfile.empty?
      logger.important "No backups found" 
    else
      config = YAML::load(ERB.new(IO.read(File.join(File.dirname(__FILE__), 'database.yml'))).result)['development']
      logger.debug "Loading backups/#{stage.to_s}/#{backupfile} into local development database" 
      `bzip2 -cd backups/#{stage.to_s}/#{backupfile} | mysql -u #{config['username']} --password=#{config['password']} #{config['database']}`
      logger.debug "command finished" 
    end
  end

  desc "Backup the remote production database and import it to the local development database" 
  task :backup_and_import do
    backup
    import_backup
  end
end

## NOTE: remove #{stage.to_s} from filename 
## if you don't have multistage deploy env