ie.brokethe.net

I frequently need to copy my production database in to my local development environment so I can work with the same data as on the live site. I’ve written 3 capistrano tasks that make this a lot easier: backup, import_backup and backup_and_import.

‘backup’ runs mysqldump on the production server using the production username and password supplied in your database.yaml file, before zipping the file and downloading it to a local directory called ‘backups’.

‘import_backup’ imports the latest backup file in the ‘backups’ folder into your local development database, using the ‘development’ username and password in database.yml.

‘backup_and_import’ performs the above 2 commands one after the other. If you want to try it out, copy the code below into your deploy.rb file, then try ‘cap backup_and_import’ from your root rails folder.

require 'yaml'

desc "Copy the remote production database to the local development database" 
task :backup, :roles => :db, :only => { :primary => true } do
  filename = "#{application}.dump.#{Time.now.to_i}.sql.bz2" 
  file = "/tmp/#{filename}" 
  on_rollback { delete file }
  db = YAML::load(ERB.new(IO.read(File.join(File.dirname(__FILE__), 'database.yml'))).result)['production']
  run "mysqldump -u #{db['username']} --password=#{db['password']} #{db['database']} | bzip2 -c > #{file}"  do |ch, stream, out|
    puts out
  end
  `mkdir -p #{File.dirname(__FILE__)}/../backups/`
  get file, "backups/#{filename}" 
  run "rm #{file}" 
end

desc "Copy the latest backup to the local development database" 
task :import_backup do
  filename = `ls -tr backups | tail -n 1`.chomp
  if filename.empty?
    logger.important "No backups found" 
  else
    ddb = YAML::load(ERB.new(IO.read(File.join(File.dirname(__FILE__), 'database.yml'))).result)['development']
    logger.debug "Loading backups/#{filename} into local development database" 
    `bzip2 -cd backups/#{filename} | mysql -u #{ddb['username']} --password=#{ddb['password']} #{ddb['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