Is this your first visit? You may want to subscribe to the feed.

Articles tagged with database

Awesomeness: database backups

At Collective Idea, we have a plugin called awesomeness that is…well, awesome. It’s a collection of things that we use in almost every project that aren’t generic enough to go into individual plugins (although some things may have evolved enough to be worthy of plugin status).

A while ago, I blogged a little snippet for backing up your remote database. Well, that snippet as evolved quite a bit, into it’s own set of rake and Capistrano tasks.

First, the rake tasks. You Can easily create a new local backup:

$ rake db:backup:create

This creates a backups directory in your project, with a subdirectory for each backup based on the timestamp. A backup consists of the schema.rb file and then a fixture for each table to hold the data. Why fixtures? Good question. Because we wanted the backups to be database independent.

You can easily restore your local database to the latest backup, or a specific version:

$ rake db:backup:restore VERSION=20080427214315

That’s nice, but what good are local backups? That’s where Capistrano comes in. Just add this to your config/deploy.rb:

load 'awesomeness/backup'

This adds some nifty remote backup support. Now, whenever cap deploy:migrations is run, a backup of your remote database will automagically be created and stored in the shared directory on the server. You can also have them transferred to your local machine by adding a callback in your deploy.rb:

after "backup:create", "backup:download"

Sometimes, you just want to take a snapshot of the server and plop it into your local database.

$ cap backup:mirror

How do I get this backup awesomeness?

Awesomeness now lives on Github (like the rest of the world). Fork it and let us know what you think.

Code: database Apr 27, 2008 ● updated Oct 14, 2008 ● 2 comments

Fixed in edge rails: table name quoting

One of more annoying ActiveRecord bugs has finally been fixed in edge rails after 2 years. Table names in SQL queries are now properly escaped.

On nearly every project it seems I end up having a table name that doesn’t work in either MySQL or sqlite. While they usually are SQL reserved words, most databases allow you to use reserved words if they’re properly quoted.

The following, which doesn’t work in MySQL: 
DELETE FROM values;

will now be:

DELETE FROM `values`;
Code: database Oct 16, 2007 ● updated Oct 16, 2007 ● 2 comments

Automatically backing up your remote database on deploy

Ever had a migration fail when you’re deploying a sparkling new release of your snazzy web app, leaving your database in an inconsistent state? I have. Fortunately, a faulty migration has never completely hosed my production database, but I have had to ssh in, comment some lines out of a migration, and re-run it.

Now, I can rest assured that I’ll never have the experience of hosing my production database without being able to recover it. Here are Capistrano recipes to backup your remote production database whenever you run migrate on your remote database.

require 'yaml'

desc "Backup the remote production database"
task :backup, :roles => :db, :only => { :primary => true } do
  filename = "#{application}.dump.#{Time.now.to_i}.sql.bz2"
  file = "/tmp/#{filename}"
  on_rollback { delete file }
  db = YAML::load(ERB.new(IO.read(File.join(File.dirname(__FILE__), 'database.yml'))).result)['production']
  run "mysqldump -u #{db['username']} --password=#{db['password']} #{db['database']} | bzip2 -c > #{file}"  do |ch, stream, data|
    puts data
  end
  `mkdir -p #{File.dirname(__FILE__)}/../backups/`
  get file, "backups/#{filename}"
  # capistrano < 1.4
  # `rsync #{user}@#{roles[:db][0].host}:#{filename} #{File.dirname(__FILE__)}/../backups/`
  delete file
end

desc "Backup the database before running migrations"
task :before_migrate do 
  backup
end

The backup recipe is adapted from ones presented by Caboo.se and Bojan Mihelac. I modified it to use my local database.yml. Thanks to Daniel Morrison, who came up with the idea of running the backup task before migrating.

Code: database Feb 09, 2007 ● updated Feb 15, 2007 ● 3 comments

Subscribe

Browse by Tag