db.rake

require 'yaml'
require 'fileutils'

namespace :db do

 desc "create the db/backup directory if it doesn't exist"
  task :create_backup_dir do
    FileUtils.mkdir_p backup_dir
  end

  desc 'Backup database by mysqldump'
  task :dump => [ :environment, :create_backup_dir ] do
    dump_file = backup_dir.join( "#{Rails.env}_#{DateTime.now.to_s}.sql" )
    puts "Connecting to #{Rails.env} environment..."
    system %Q[
      mysqldump #{mysql_credentials} --skip-triggers --compact
      --ignore-table=#{@database}.schema_migrations --no-create-info > #{dump_file}
    ].squeeze(" ").delete("\n")

    puts "Wrote #{dump_file}"
  end

  desc "Restore most recent backup via mysql into development db; FILE || {FROM}*.sql || most recent"
  task :restore => [:environment] do
    unless Rails.env.development?
      puts "Are you sure you want to import into #{Rails.env}?! [y/N]"
      return unless STDIN.gets =~ /^y/i
    end

    puts "truncating tables..."
    ActiveRecord::Base.connection.execute('show tables').to_a.flatten.each do |table|
      next if table.to_s == 'schema_migrations'
      puts "  - #{table}"
      ActiveRecord::Base.connection.execute("truncate table #{table}")
    end

    wildcard  = backup_dir.join( ENV['FILE'] || "#{ENV['FROM']}*.sql" )
    puts file = `ls -t #{wildcard} | head -1`.chomp  # default to file, or most recent ENV['FROM'] or just plain most recent
    puts "please wait, this could take a minute or two..."
    system "mysql #{mysql_credentials} < #{file}"
  end

  private

  def params_from_db_yaml env=Rails.env
    YAML.load( Rails.root.join( 'config', 'database.yml').open )[env.to_s].with_indifferent_access
  end

  def mysql_credentials env=Rails.env
    @database, user, password, host = params_from_db_yaml( env ).values_at(:database, :username, :password, :host)
    "-u#{user}#{" -p#{password}" if password.present?} #{@database}#{" -h#{host}" if host}"
  end

  def backup_dir
    Rails.root.join('db','backup')
  end

end

rake tasks for ralls app to backup and restore mysqldumps