backup_db.rb

# backup_db.rb

# Remotely back up and securely download a copy of a MySQL database.
# Can be used in conjunction with cron to run nightly with a crontab like this:
# 0 0 * * * ruby /home/user/scripts/backup_db.rb

# Written by C. Scott Andreas (http://twitter.com/cscotta)
# No warranties, use entirely at your own risk.  The author assumes no liability
# for any consequences of this script, including data loss, security breaches, and service unavailability.
# Released under an MIT-style License.  Use, modify, and distribute this script as you like.

require 'rubygems'
require 'net/ssh'
require 'net/sftp'

# SSH Configuration
SSH_HOST  = ""
SSH_USER  = ""
SSH_PASS  = ""
SSH_PORT  = 22

# Database Configuration
MYSQL_DB    = ""
MYSQL_USER  = ""
MYSQL_PASS  = ""

# Backup File Locations (no trailing slash)
REMOTE_PATH = ""
LOCAL_PATH  = ""


## - End Configuration - ##

date = Time.now.strftime("%m%e%g")
puts "Connecting to sever."
Net::SSH.start(SSH_HOST, SSH_USER, {:port => SSH_PORT, :password => SSH_PASS}) do |ssh|

  puts "Backing up database."
  ssh.exec!("mysqldump -u #{MYSQL_USER} -p#{MYSQL_PASS} #{MYSQL_DB} > #{REMOTE_PATH}/#{MYSQL_DB}_#{date}.sql")

  puts "Compressing database."
  ssh.exec!("tar -cvzf #{REMOTE_PATH}/#{MYSQL_DB}_#{date}.sql.tgz #{REMOTE_PATH}/#{MYSQL_DB}_#{date}.sql")

  puts "Downloading backup copy of database."
  ssh.sftp.download!("#{REMOTE_PATH}/#{MYSQL_DB}_#{date}.sql.tgz", "#{LOCAL_PATH}/#{MYSQL_DB}_#{date}.sql.tgz")
  
  puts "Cleaning up temporary files."
  ssh.exec!("rm #{LOCAL_PATH}/#{MYSQL_DB}_#{date}.sql.tgz")
  ssh.exec!("rm #{REMOTE_PATH}/#{MYSQL_DB}_#{date}.sql")
  
  puts "\nDone!\n"
end