Thor task to dump and load mysql databases in a Rails project by using the credentials in config/database.yml.

mysql.thor

require 'io/console'

class Mysql < Thor

  desc 'create DATABASE', 'create mysql database'
  method_option :debug, :type => :boolean, :default => false, :aliases => "-d", :desc => "Debug"
  method_option :verbose, :type => :boolean, :default => true, :aliases => "-v", :desc => "Verbose"
  method_option :env, :type => :string, :default => 'development', :aliases => "-e", :desc => "Environment must be either 'development' or 'production'"
  def create(db = nil)
    debug = options[:debug]
    verbose = (debug || options[:verbose])
    env = options[:env].to_s.downcase
    abort "Environment value not acceptable" unless %w(development production test).include?(env)
    if File.exist?('config/database.yml')
      database = load_database(env)
    else
      database = {}
      database['database'] = database['username'] = db
      if db.nil? || db.blank?
        puts "Enter database name:"
        database['database'] = STDIN.gets.chomp
      end
      case env
      when 'development'
        database['database'] += '_dev'
      when 'test'
        database['database'] += '_test'
      end
      puts "Enter database password:"
      database['password'] = STDIN.noecho(&:gets).chomp
    end
    cmd = "mysql -e \"CREATE DATABASE #{database['database']}; GRANT ALL ON #{database['database']}.* TO #{database['username']}@localhost IDENTIFIED BY '#{database['password']}'; \""
    puts cmd if verbose
    system(cmd) unless debug
  end

  desc 'dump', 'dump mysql database to tmp directory'
  method_option :debug, :type => :boolean, :default => false, :aliases => "-d", :desc => "Debug"
  method_option :verbose, :type => :boolean, :default => true, :aliases => "-v", :desc => "Verbose"
  method_option :timestamp, :type => :boolean, :default => false, :aliases => "-t", :desc => "Add timestamp to file name"
  method_option :env, :type => :string, :default => 'development', :aliases => "-e", :desc => "Environment must be either 'development' or 'production'"
  def dump
    debug = options[:debug]
    verbose = (debug || options[:verbose])
    env = options[:env].to_s.downcase
    abort "Environment value not acceptable" unless %w(development production).include?(env)
    database = load_database(env)
    FileUtils.cd('tmp', :verbose => verbose)
    ext = 'sql.gz'
    if options[:timestamp]
      timestamp = "-#{Time.now.strftime('%Y%m%d-%H%M')}"
    else
      timestamp = ''
    end
    dump_file = "#{database['database']}#{timestamp}.#{ext}"
    puts "Dumping sql file..." if verbose
    cmd = "mysqldump --user=#{database['username']} --password=#{database['password']} #{database['database']} | gzip -9 > #{dump_file}"
    puts cmd if verbose
    system(cmd) unless debug
    if File.exist?(dump_file)
      puts "File created: #{File.join('tmp', dump_file)}" if verbose
    else
      puts "! Error: File not created #{File.join('tmp', dump_file)}"
    end
    puts "Creating mysql database '#{database}'..." if verbose
    cmd = "mysql --user=#{database['username']} --password=#{database['password']} #{database['database']} < #{source}"
    puts cmd if verbose
    system(cmd) unless debug
  end

  desc 'load FILE', 'load sql file into local mysql database'
  method_option :debug, :type => :boolean, :default => false, :aliases => "-d", :desc => "Debug"
  method_option :verbose, :type => :boolean, :default => true, :aliases => "-v", :desc => "Verbose"
  method_option :force, :type => :boolean, :default => false, :aliases => "-f", :desc => "Force"
  method_option :env, :type => :string, :default => 'development', :aliases => "-e", :desc => "Environment must be either 'development' or 'production'"
  def load(file)
    debug = options[:debug]
    verbose = (debug || options[:verbose])
    force = options[:force]
    env = options[:env].to_s.downcase
    abort "Environment value not acceptable" unless %w(development production).include?(env)
    abort "File does not exist" unless File.exist?(file)
    source = File.expand_path(file)
    database = load_database(env)
    abort "File name '#{File.basename(file)}' does not match database name '#{database['database']}'" unless /#{database['database']}/.match(file) || options[:force]
    if File.extname(source) == '.gz'
      puts "Unzipping file..." if verbose
      cmd = "gunzip -f #{source}"
      puts cmd if verbose
      system(cmd) unless debug
      source.sub!(/\.gz$/, '')
    end
    unless File.extname(source) == '.sql'
      puts "File must have '.sql' extension"
      return
    end
    puts "Loading sql file..." if verbose
    cmd = "mysql --user=#{database['username']} --password=#{database['password']} #{database['database']} < #{source}"
    puts cmd if verbose
    system(cmd) unless debug
  end

  private

    def load_database(env)
      file = File.expand_path('config/database.yml')
      yml = YAML.load(File.read(file))
      yml[env]
    end

end