Skip to content

Instantly share code, notes, and snippets.

@cscotta
Created May 11, 2009 03:12
Show Gist options
  • Save cscotta/109852 to your computer and use it in GitHub Desktop.
Save cscotta/109852 to your computer and use it in GitHub Desktop.
# 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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment