Is there a rake task for backing up the data in your database?

Question

Is there a rake task for backing up the data in your database?

I already have my schema backed up, but I want to make a backup of the data. It's a small MySQL database.

Answer 1

The below script is a simplified version taken from eycap, specifically from this file.

set :dbuser "user"
set :dbhost "host"
set :database "db"

namespace :db do
  desc "Get the database password from user"
  task :get_password do
    set(:dbpass) do
      Capistrano::CLI.ui.ask "Enter mysql password: "
    end
  end

  task :backup_name, :only => { :primary => true } do
    now = Time.now
    run "mkdir -p #{shared_path}/db_backups"
    backup_time = [now.year,now.month,now.day,now.hour,now.min,now.sec].join('-')
    set :backup_file, "#{shared_path}/db_backups/#{database}-snapshot-#{backup_time}.sql"
  end

  desc "Dump database to backup file"
  task :dump, :roles => :db, :only => {:primary => true} do
    backup_name
    run "mysqldump --add-drop-table -u #{dbuser} -h #{dbhost} -p#{dbpass} #{database} | bzip2 -c > #{backup_file}.bz2"
  end
end

Edit: Yeah, I guess I missed the point that you were looking for a rake task and not a capistrano task, but I don't have a rake one on hand, sorry.

Answer 2

Just in case people are still surfing for solutions, we currently use the ar_fixtures plugin to backup our db, well as part of the solution anyway.

It provides the rake db:fixtures:dump tasks. This spits out everythin in YAML into test/fixtures, so it can be loaded in again using db:fixtures:load.

We use this to backup before every feature push to production. We also used this when migrating from sqlite3 to Postgres - which is subtlety very useful as incompatibilities between SQL dialects are, for the most part, hidden.

All the best, D

Answer 3

I don't have a rake task for backing up my MySQL db, but I did write a script in Ruby to do just that for my WordPress DB:

filename = 'wp-config.php'
def get_db_info(file)
  username = nil
  password = nil
  db_name = nil

  file.each { |line|
    if line =~ /'DB_(USER|PASSWORD|NAME)', '([[:alnum:]]*)'/
      if $1 == "USER"
        username = $2
      elsif $1 == "PASSWORD"
        password = $2
      elsif $1 == "NAME"
        db_name = $2
      end
    end
  }

  if username.nil? || password.nil? || db_name.nil?
    puts "[backup_db][bad] couldn't get all needed info"
    exit
  end

  return username, password, db_name
end

begin
  config_file = open("#{filename}")
rescue Errno::ENOENT
  puts "[backup_db][bad] File '#{filename}' didn't exist"
  exit
else
  puts "[backup_db][good] File '#{filename}' existed"
end

username, password, db_name = get_db_info(config_file)
sql_dump_info = `mysqldump --user=#{username} --password=#{password} #{dbname}`
puts sql_dump_info

You should be able to take this and do some mild pruning of it to put in your username/password/dbname to get it up and working for you. I put it in my crontab to run everyday as well, and it shouldn't be too much work to convert this to run as a rake task since it's already Ruby code (might be a good learning exercise as well).

Tell us how it goes!