dump_to_s3.rb

#! /usr/bin/env ruby
#
# Dump a MySQL database and upload to S3, splitting the dump
# if needed as S3 has a 5GB file size limit.
#
# Simply run "./dump_to_s3.rb database_name" to dump and
# upload to S3 all in one go.
#

require "rubygems"
require "aws/s3"

# Amazon S3 configuration
AWS::S3::Base.establish_connection!(
  :access_key_id     => 'YOUR_ACCESS_KEY',
  :secret_access_key => 'YOUR_SECRET_KEY'
)

# MySQL configuration
$mysql_user = "root"
$mysql_pass = ""

# Local temporary folder to store MySQL dumps in
$local = "/mnt"

# Bucket (and folder) to store backups in on S3
$remote = "MY_BUCKET/FOLDER"

# Split the dump files at 2GB
$split_size = 2*1000*1000*1000

class DbStore < AWS::S3::S3Object
  set_current_bucket_to $remote.to_s
end

if !ARGV[0].nil?
  time = Time.new
  year  = time.year.to_s.rjust(4, '0')
  month = time.month.to_s.rjust(2, '0')
  day   = time.day.to_s.rjust(2, '0')
  hour  = time.hour.to_s.rjust(2, '0')
  min   = time.min.to_s.rjust(2, '0')
  sec   = time.sec.to_s.rjust(2, '0')
  file = "#{ARGV[0]}-#{year}-#{month}-#{day}_#{hour}-#{min}-#{sec}.sql"
  puts "dumping #{ARGV[0]} database..."
  `mysqldump -u#{$mysql_user} -p#{$mysql_pass} #{ARGV[0]} | split -b #{$split_size} - #{$local}/#{file}_`
  files = Dir.glob("#{$local}/#{file}_*")
  split_count = files.size
  files.each do |partial|
    target = (split_count > 1) ? File.basename(partial) : file
    puts "uploading #{target} to S3."
    if File.exist?(partial)
      DbStore.store(target, open(partial))
      File.delete(partial)
    end
  end
end

EXAMPLE

# Dump database "hello_world"
$ ./dump_to_s3.rb hello_world
dumping hello_world database...
uploading /mnt/hello_world-2009-10-30_12-11-44.sql_aa to S3.
uploading /mnt/hello_world-2009-10-30_12-11-44.sql_ab to S3.
uploading /mnt/hello_world-2009-10-30_12-11-44.sql_ac to S3.