Capistrano 2 task for backing up your MySQL production database before each deployment

Tagged ruby, rails, mysql, backup, capistrano  Languages ruby

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 All credit to court3nay.

task :backup, :roles => :db, :only => { :primary => true } do
  filename = "#{backup_dir}/#{application}.dump.#{}.sql.bz2"
  text = capture "cat #{deploy_to}/current/config/database.yml"
  yaml = YAML::load(text)

  on_rollback { run "rm #{filename}" }
  run "mysqldump -u #{yaml['production']['username']} -p #{yaml['production']['database']} | bzip2 -c > #{filename}" do |ch, stream, out|
    ch.send_data "#{yaml['production']['password']}\n" if out =~ /^Enter password:/

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

task :before_deploy do

To restore the backup run the following command:

mysql database_name -uroot < filename.sql