Schedule nightly backups to Amazon S3 with Ruby and Whenever

1_README.md

Install gems

gem install whenever mysql2 pony ruby-s3cmd

Configure S3 & schedule.rb

Go ahead and create your backup bucket on S3 now. Configure whenvever to do your backups at a time when server load will be low (I picked 2:00 am).

Update mysql_backup.rb configuration

You'll need to add connection information for the mysql host you want to backup and S3 configuration as well as email configuration if you want to receive a success email.

Recommended structure

mkdir -p ~/.schedule/config
touch ~/.schedule/config/schedule.rb
touch ~/.schedule/mysql_backup.rb
chomod +x ~/.schedule/mysql_backup.rb

That's it!

2_schedule.rb

# Use this file to easily define all of your cron jobs.
#
# It's helpful, but not entirely necessary to understand cron before proceeding.
# http://en.wikipedia.org/wiki/Cron

# Example:
#
# set :output, "/path/to/my/cron_log.log"
#
# every 2.hours do
#   command "/usr/bin/some_great_command"
#   runner "MyModel.some_method"
#   rake "some:great:rake:task"
# end
#
# every 4.days do
#   runner "AnotherModel.prune_old_records"
# end

# Learn more: http://github.com/javan/whenever

every 1.day, :at => '2:00 am' do
  command '/home/USER/.schedule/mysql_backup.rb'
end

3_mysql_backup.rb

#!/usr/bin/env ruby
require 'mysql2'
require 'pony'
require 'ruby-s3cmd'

#
# CONFIGURATION
#

mysql_config = {
  :host     => 'localhost', # or the hostname of your database server
  :username => '',
  :password => '',
  :database => 'mysql'
}
target_bucket = ""
send_mail_on_success = false
mail_config = {
  :to         => '',
  :from       => '',
  :sender     => "Servers: #{`hostname`}",
  :subject    => "#{mysql_config[:host]} database backups complete",
  :html_body  => "<b>Sent #{databases.size} databases to S3 in #{total_time}.</b><br />
   You can view them in the <a href=\"s3://#{target_bucket}\">#{target_bucket}</a> bucket."
}
#
# END CONFIGURATION
#

client = Mysql2::Client.new(mysql_config)
databases = client.query('SHOW DATABASES;').reject {|c| c['Database'] == 'mysql' || c['Database'] == 'information_schema' }
databases.collect! {|r| r['Database'] }

s3cmd = RubyS3Cmd::S3Cmd.new

date = Time.now.strftime('%Y-%m-%d')

`logger "Starting backups at #{start_time = Time.now}"`
databases.each do |db|
  `logger "Backing up #{db}"`

  file = "#{db}-#{date}.sql.gz"
  `mysqldump --host=#{mysql_config[:host]} -u #{mysql_config[:username]} -p#{mysql_config[:password]} #{db} | gzip > /tmp/#{file}`

  `logger "Sending #{db} to S3: s3://#{target_bucket}/#{mysql_config[:host]}/#{date}/#{file}"`
  s3cmd.put("/tmp/#{file}", "s3://#{target_bucket}/#{mysql_config[:host]}/#{date}/#{file}")

  `rm /tmp/#{file}`
end

end_time = Time.now
total_time = Time.at(end_time-start_time).gmtime.strftime('%R:%S')
`logger "Finished backups in #{total_time}"`

if send_mail_on_success
  Pony.mail(mail_config)