gistfile1.rb

#!/usr/bin/ruby
require 'yaml'
require 'logger'
require 'rubygems'
require 'net/sftp'

APP        = '/path/to/your/rails/app'
LOG_FILE   = '/home/log/database.log'
TIMESTAMP  = '%Y%m%d%H%M%S'

log        = Logger.new(LOG_FILE, 5, 10*1024)
dump       = "backup_#{Time.now.strftime(TIMESTAMP)}.sql.gz" 
# get the off server sftp configuration settings
ftp_config = YAML::load(open('/home/bin/sftp.yml'))
# get the production database configuration
config     = YAML::load(open(APP + '/config/database.yml'))['production']
cmd        = "mysqldump -u #{config['username']} -p#{config['password']} -h localhost --add-drop-table --add-locks --extended-insert --lock-tables #{config['database']} | gzip -cf9 > #{dump}" 

log.info 'Getting ready to create a backup'
`#{cmd}`
log.info 'Backup created, starting the transfer offsite'
Net::SFTP.start(ftp_config['host'], ftp_config['username'], :password => ftp_config['password']) do |sftp|
  sftp.upload!("#{dump}", "#{ftp_config['dir']}/#{dump}")
end
log.info 'Finished transferring backup offsite'
log.info 'Removing local file'
cmd       = "rm -f #{dump}" 
log.debug "Executing: #{cmd}" 
`#{cmd}`
log.info 'Local file removed'