19 Rake db:backup 0 comments
I apologise for the quietness around ThinkRefresh recently, but I have been busy on a few projects, however some of them are coming to a close (or rather, an open...), so I have had little chance to post. Anyway, enough excuses, one of the good things to come from my work on these projects is the many things I have developed or discovered a long the way, many of which I hope to post here, so here is the first.
It is always a pain manually backing up your database, and even if your web host does backup for you, they may not keep copies long enough for your liking, so with a helping hand from S3 you can simply install the below rake task as a file in your lib/tasks directory and drop in your AWS keys. I cannot take full credit for the script, so I have quoted those involved within it, all I did was drop in SQLite support, so this was more of a discovery than a development.
One final note before you dig in, if you add support for another database engine or make any changes, please feel free to update the code on gist.github.com/739.
# S3 Backup Task for MySQL & SQLite
# Assumes InnoDB tables
# If using MySQL 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
# http://info-architects.net/2007/08/25/rake-task-for-mysql-backup-to-amazon-s3/
#
# Support for SQLite added by Ryan Townsend of http://thinkrefresh.com
namespace :db do
require "aws/s3"
require "ftools"
desc "Backup database to Amazon S3"
task :backup => :environment do
BUCKET = "my.bucket.example"
begin
AWS::S3::Base.establish_connection!(
:access_key_id => "ABC",
:secret_access_key => "XYZ"
)
db_config = ActiveRecord::Base.configurations[RAILS_ENV]
backup_path = "db/backup"
File.makedirs(backup_path)
case db_config['adapter']
when 'mysql'
stored_file = backup_file(backup_path, "#{db_config['database']}.sql.gz")
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 > #{stored_file}"
when 'sqlite3'
stored_file = backup_file(backup_path, File.basename(db_config['database']))
File.copy "#{RAILS_ROOT}/#{db_config['database']}", stored_file
else
puts "Unable to backup databases of type: #{db_config['adapter']}"
end
if stored_file && File.exists?(stored_file)
puts "Created backup: #{stored_file}"
puts "Storing file in S3: #{BUCKET}"
AWS::S3::S3Object.store(stored_file, open(stored_file), BUCKET)
puts "Backup Complete"
else
puts "Unable to create backup"
end
rescue AWS::S3::ResponseError => error
puts error.response.code.to_s + ": " + error.message
end
end
def backup_file(path, file)
File.join(path, "db_#{Time.now.strftime("%Y%m%d%H%M%S")}_#{file}")
end
end