Mysql – rake task for backing up the data in your database

databaseMySQLrakerubyruby-on-rails

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.

Best Answer

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.