a better capistrano backup

Courtenay : December 28th, 2006

Update (03-2008): See here for a better version of this: push.cx/2007..

Say you have a remote server and you want to back up your remote DB to your home machine but you’re behind a firewall. Here’s my quick solution/hack of the default capistrano backup task.

Remotely, it will dump your database to /tmp and bzip2 it. Locally, it will scp the file to your RAILS_ROOT/backups. You need to have all your ssh keys set up for this to work. It probably will fail if you’re using a gateway.


role :db,  "caboo.se", :primary => true
set :user, "bananas" 

desc "Backup the database" 
task :backup, :roles => :db, :only => { :primary => true } do
  filename = "/tmp/#{application}.dump.#{Time.now.to_f}.sql.bz2" 

  on_rollback { delete filename }
  run "mysqldump -u root -p mephisto_production | bzip2 -c > #{filename}" do |ch, stream, out|
    ch.send_data "assword\n" if out =~ /^Enter password:/
    # set this to your db password.. yuk!
  end
  `rsync #{user}@#{roles[:db][0].host}:#{filename} #{File.dirname(__FILE__)}/../backups/`
  delete filename
end

Invoke with rake remote:exec ACTION=backup

Suggested improvements:
  • is it necessary to shell out to scp? (update: now uses rsync)
  • is there a better way of doing the db password?
  • any way to use the ‘backup gem’, a favorite of rails professionals everywhere?
Posted by Courtenay Filed in rails, ruby

6 Responses to “a better capistrano backup”

  1. Jason L. Says:
    December 28th, 2006 at 07:53 AM

    I use a cron job and a shell script to backup my db – but I’d like to do some kind of cap task like this to occasionally do a backup “on the fly”. In my shell script, instead of:

    mysqldump -u root -p my_app_production > file
(prompt for password)

    I create a user that has select (read) and lock table permissions and I use:

    mysqldump -u backupuser --password=secret my_app_production > file

    People used to shy away from this format because they don’t want their password to show up in the process list, but these days mysqldump replaces your password with xxxxxxxx (in the process list).

    (Also I use the unix date command to determine the actual file name, but it’s not as nice looking as using ruby…)

  2. imrankhan/ imransyed2005@yahoo.com Says:
    December 28th, 2006 at 07:37 PM

    i want muslim sexy grils in hyderabad india

  3. topfunky Says:
    December 29th, 2006 at 04:26 AM

    For the DB password, you could set an internal variable and pass it on the command line:

      cap backup -s db_pass=frisky

    Or use my “run with input” cap method (part of the deprec gem) that interactively prompts for info and passes it back to capistrano.

    Or, just do something like

      db_pass = get
  ch.send_data(db_pass)
  4. topfunky Says:
    December 29th, 2006 at 04:48 AM

    Actually, I meant

      db_pass = Capistrano::CLI.password_prompt "Enter your DB password:"
  5. Scott Becker Says:
    February 17th, 2007 at 04:34 PM

    Instead of prompting for the password, you could grab it from the database.yml file on the server.

    Just a thought…

  6. Claude Says:
    April 3rd, 2007 at 01:54 AM

    Thank you for your code it is helpfull here.

Leave a Reply

I am a human (check this)

Remember: escape your underscores \_ and indent code at least 4 spaces or incur the wrath of smartypants.