# 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