Capistrano task: Pull database for Magento to local machine

sync_magento_database.rb

# DD: pull the MySQL database down
# DD: base code: http://stackoverflow.com/questions/13638112/how-to-create-a-capistrano-task-to-download-latest-database-backup-tgz-and-impor
# DD: base code: https://gist.github.com/1726896
require 'nokogiri'
require 'colored'
namespace :db do
  desc 'Pull the DB from the server'
  task :pull, :roles => :db, :only => { :primary => true } do

    filename = "#{application}.dump.#{Time.now.to_f}.sql"
    filename_bz2 = "#{filename}.bz2"
    remote_file = "#{current_path}/#{filename_bz2}"

    # DD: grab remote data
    raw = capture "cat #{shared_path}/public/app/etc/local.xml"
    xml = Nokogiri::XML(raw)

    host = xml.xpath("//connection/host").children.first.text
    database = xml.xpath("//connection/dbname").children.first.text
    username = xml.xpath("//connection/username").children.first.text
    password = xml.xpath("//connection/password").children.first.text
    table_prefix1 = xml.xpath("//db/table_prefix").children.first.text
    admin_prefix1 = xml.xpath("//adminhtml//frontName").children.first.text

    run "mysqldump -h #{host} -u #{username} -p #{database} | bzip2 -c > #{remote_file}" do |ch, stream, out|
      ch.send_data "#{password}\n" if out =~ /^Enter password:/
    end

    # DD: transfer the file locally
    download remote_file, filename_bz2, :via => :scp
    run_locally("bzip2 -d #{filename_bz2}")

    # DD: dump to local data
    xml = Nokogiri::XML(File.open("public/app/etc/local.xml"))

    host = xml.xpath("//connection/host").children.first.text
    database = xml.xpath("//connection/dbname").children.first.text
    username = xml.xpath("//connection/username").children.first.text
    password = xml.xpath("//connection/password").children.first.text
    table_prefix2 = xml.xpath("//db/table_prefix").children.first.text
    admin_prefix2 = xml.xpath("//adminhtml//frontName").children.first.text

    if admin_prefix1 != admin_prefix2
      puts "WARNING: Your local admin prefix of '#{admin_prefix2}' is different from the remote admin prefix of '#{admin_prefix1}'. Just be cognizant of this.".red
    end

    if table_prefix1 != table_prefix2
      puts "!-----------------------------!".red
      puts "ERROR: Your local table prefix of '#{table_prefix2}' does not match the remote table prefix of '#{table_prefix1}'. Please use the same table prefix!".red
      puts "!-----------------------------!".red
    else
      pwd_command = password && password.length > 0 ? "-p #{password}" : ""

      run_locally "mysql -h #{host} -u #{username} #{pwd_command} #{database} < #{filename}"

      # DD: update the base url's
      run_locally "mysql -h #{host} -u #{username} #{pwd_command} #{database} -e \"update {table_prefix2}core_config_data set value = 'http://magento.test/' where path = 'web/unsecure/base_url'\""
      run_locally "mysql -h #{host} -u #{username} #{pwd_command} #{database} -e \"update {table_prefix2}core_config_data set value = 'http://magento.test/' where path = 'web/secure/base_url'\""
    end

    run_locally "rm #{filename}"
    run "rm #{remote_file}"
  end
end