Martin Probst's weblog

MySQL backup/restore task for Capistrano

Tuesday, November 20, 2007, 10:59 — 0 comments Edit

This is a simple backup task for Capistrano (which could really use a lot more documentation…).

The tool reads database configuration from the local database.yml. This Works For Me ™ as I keep the local and remote database configuration identical - YMMV.

While the script doesn’t require you to type the database user’s password, it will echo it to the console for the restore task. Avoiding that seems to be quite tricky - I tried sending the backup directly over the stream and piping in the password before, but that gives an obscure error.

So the following will have to do for now, but I’m quite pleased with it. I should probably include a warning/confirmation before restoring, but hey, command lines are for experts ;-)

$config = YAML.load_file(File.join('config', 'database.yml'))

desc "Backup the database to db/" +"backup_#{$config['production']['database']}_%Y-%m-%e.sql")
task :backup, :roles => :db, :only => { :primary => true } do 
  backup_path = File.join('db',"backup_#{$config['production']['database']}_%Y-%m-%e.sql"))
  on_rollback { delete backup_path, :recursive => false }
  backup_file =, 'w+')
  run "mysqldump --default-character-set=utf8 " +
    "--user=#{$config['production']['username']} " +
    "--password " +
    "-B #{$config['production']['database']}" do |channel,stream,data|
    if stream == :out
      if data =~ /^Enter password:/
        raise Capistrano::Error, "unexpected output from mysqldump: " + data
  end "Database dumped to #{backup_path} successfully."

desc "Restore the database from backup"
task :restore, :roles => :db do
  backups = Dir[File.join('db', "backup_#{$config['production']['database']}_*.sql")]
  raise Capistrano::Error, "no backup found!" if backups.size == 0
  last_backup = backups.sort[-1]
  put(, "#{current_path}/db/restore.sql") "Restoring from #{last_backup}"
  run "mysql --default-character-set=utf8 " +
    "--user=#{$config['production']['username']} " +
    "--password=#{$config['production']['password']} " do |channel, stream, data|
    raise Capistrano::Error, "unexpected output from mysql: " + data
  end "Restored successfully."

No comments.