Capistrano task to pull MySQL production data to develoment database

View as text

# Fetches the production database on the server contents into the development
# database 
# 
# Assumes you have dbuser, dbhost, dbpassword, and application defined somewhere in your 
# task. Modify as needed - database.yml is used for importing data, just not for exporting.
#
# Only supports MySQL.
desc "Load production data into development database"
task :fetch_remote_db, :roles => :db, :only => { :primary => true } do
  require 'yaml'

  database = YAML::load_file('config/database.yml')

  filename = "dump.#{Time.now.strftime '%Y-%m-%d_%H:%M:%S'}.sql"

  on_rollback do 
    delete "/tmp/#{filename}" 
    delete "/tmp/#{filename}.gz" 
  end

  cmd = "mysqldump -u #{dbuser} -h #{dbhost} --password=#{dbpassword} #{application}_production > /tmp/#{filename}"
  puts "Dumping remote database"
  run(cmd) do |channel, stream, data|
    puts data
  end
  
  # compress the file on the server
  puts "Compressing remote data"
  run "gzip -9 /tmp/#{filename}"
  puts "Fetching remote data"
  get "/tmp/#{filename}.gz", "dump.sql.gz"
  
  # build the import command
  # no --password= needed if password is nil. 
  if database['development']['password'].nil?
    cmd = "mysql -u #{database['development']['username']} #{database['development']['database']} < dump.sql"
  else
    cmd = "mysql -u #{database['development']['username']} --password=#{database['development']['password']} #{database['development']['database']} < dump.sql"
  end
  
  # unzip the file. Can't use exec() for some reason so backticks will do
  puts "Uncompressing dump"
  `gzip -d dump.sql.gz`
  puts "Executing : #{cmd}"
  `#{cmd}`
  puts "Cleaning up"
  `rm -f dump.sql`

  puts "Be sure to run rake db:migrate to ensure your database schema is up to date!"
end