Top Secret Project

There are a couple of these around, but I rolled my own because I wanted something quite simple.

# S3 Backup Task for MySQL
# Assumes InnoDB tables
# Database User needs the "reload" permission on the database (for --flush-logs in mysqldump)
#
# Stores files in Amazon S3 using the excellent AWS Gem: http://amazon.rubyforge.org/
# For information about Amazon S3: http://aws.amazon.com/s3
#
# Installation
# 1) Install AWS Gem
# 2) Enter your S3 Bucket, access_key_id and secret_access_key in this file
# 3) Run (rake db:backup)
#
# Inspired by code from:
#   http://blog.craigambrose.com/articles/2007/03/01/a-rake-task-for-database-backups
#   http://www.rubyinside.com/advent2006/15-s3rake.html

namespace :db do
  require "aws/s3"

  desc "Backup database to Amazon S3"

  task :backup => :environment do
    BUCKET = "bucket"

    begin
      AWS::S3::Base.establish_connection!(
        :access_key_id     => "access_key_id ",
        :secret_access_key => "secret_access_key "
      )

      db_config = ActiveRecord::Base.configurations[RAILS_ENV]

      backup_path = "db/backup"
      File.makedirs(backup_path)

      datestamp = Time.now.strftime("%Y%m%d%H%M%S")
      file_name = "#{RAILS_ENV}_#{db_config['database']}_#{datestamp}.sql.gz"

      backup_file = File.join(backup_path, file_name)

      sh "mysqldump -u #{db_config['username']} -p#{db_config['password']} --single-transaction --flush-logs --add-drop-table --add-locks --create-options --disable-keys --extended-insert --quick #{db_config['database']} | gzip -c > #{backup_file}"
      puts "Created backup: #{file_name}"

      puts "Storing file in S3: #{BUCKET}"
      AWS::S3::S3Object.store(file_name, open(backup_file), BUCKET)

      puts "Backup Complete"
    rescue AWS::S3::ResponseError => error
      puts error.response.code.to_s + ": " + error.message
    end

  end

end

Share This

This entry was posted on Saturday, August 25th, 2007 at 3:54 pm and is filed under Amazon S3, Amazon Web Services, Development, MySQL, Programming, Rake, Ruby, Ruby on Rails, Snippets. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.