backup mysql databases over SSH

backup_mysql_databases.rb

#!/usr/bin/env ruby

# backup_mysql_databases.rb - pulls data from various database hosts and dbs,
#                             and stores these in a centrally mounted location
#                             for offsite backup
#
# Erich Smith
#
# This script is intended to be run via daily CRON task, and will generate a
# single set of zipped backup files per day, one file per defined host
# database, until the number of days defined below as @days_to_keep is reached.
# Once this limit is reached, the script will delete the oldest backup file, to
# keep the running total at the defined limit.
#
# To add or remove databases, or change any of the runtime parameters, edit the
# data in the initialize() method.
#
# To turn on verbose logging, invoke this
# script with a command-line argument, like so:
#
# $> ./backup_mysql_databases.rb -v

root_dir = '/mnt/backups/'
log_dir = root_dir + 'logs/'
logfile = log_dir + 'db_bkups.log'

abort "Must run as root!" unless Process.uid == 0
abort "no #{log_dir}, is the filesystem mounted?" unless File.directory? log_dir

require 'timerizer' # provides Rails-style relative time methods
require 'logger'

class App

  def initialize(root_dir, logger)

    @hosts = configuration_hash

    @hosts[:host1][:user] = 'mysqlbackup'
    @hosts[:host1][:pass] = 'passw0rd'
    @hosts[:host1][:backup_dir] = root_dir + 'host1/db_bkups/'
    @hosts[:host1][:databases] = ['finance',
                                  'mysql']

    @hosts[:host2][:user] = 'root'
    @hosts[:host2][:pass] = '123F5s\)'  # note: escaped shell char
    @hosts[:host2][:backup_dir] = root_dir + 'host2/db_bkups/'
    @hosts[:host2][:databases] = ['mediawiki',
                                  'accounting',
                                  'mysql',
                                  'webstats']

    @hosts[:host3][:user] = 'backup'
    @hosts[:host3][:pass] = 'b\@ckup\!'
    @hosts[:host3][:backup_dir] = root_dir + 'host3/db_bkups/'
    @hosts[:host3][:databases] = ['test',
                                  'dev',
                                  'mysql']

    @days_to_keep = 30
    @date_template = '_%Y-%m-%d' # used in dumpfile names
    @ssh_user = 'backup'

    @logger = logger
  end

  def go!
    log_with_elapsed_time("total script execution") do
      databases.each do |database|
        dump_database(database)
        remove_old_dump(@days_to_keep.days.ago, database)
      end
    end
  end

  private

  def backup_command(database, local_dumpfile)
      ssh = %Q[ssh -i /home/#{@ssh_user}/.ssh/id_rsa #{@ssh_user}@#{database.host} ]

      sqldump = %Q[mysqldump -u#{database.user} -p#{database.pass} --flush-logs #{database.name}]
      compress = %Q[gzip -c]
      database_pipeline = %Q["#{sqldump} | #{compress} "]

      output_redirection = %Q[ > #{local_dumpfile}]

      ssh + database_pipeline + output_redirection
  end

  def configuration_hash
    Hash.new { |hash, key| hash[key] = {} }
  end

  def databases
    databases = Array.new
    @hosts.each do |host, config|
      config[:databases].each do |name|
          databases.push Database.new(host, name, config)
      end
    end
    databases
  end

  def dump_database(database)
    local_dumpfile = dumpfile_name(Time.now, database)

    if File.exists? local_dumpfile
      @logger.debug "file #{local_dumpfile} already exists, I will not overwrite it"
      return
    end

    cmd = backup_command(database, local_dumpfile)

    log_with_elapsed_time("dump for #{database.host}->#{database.name}") do
      @logger.debug "executing shell command: #{cmd}"
      system(cmd)
    end
  end

  def dumpfile_name(someTime, database)
    database.backup_dir + database.name + someTime.strftime(@date_template) + '.sql.gz'
  end

  def log_with_elapsed_time(task_description)
    start = Time.now
    @logger.info "start of #{task_description}"

    yield

    elapsed_time = Time.since(start)
    elapsed_time = '0 seconds' if elapsed_time.to_s.empty?

    @logger.info "end of #{task_description}, took: #{elapsed_time}"
  end

  def remove_old_dump(olddate, database)
    file = dumpfile_name(olddate, database)

    begin
      @logger.debug "attempting to delete file #{file}"
      File.delete(file)
    rescue Errno::ENOENT
      # it's okay if there is no file there
      @logger.debug "#{file} not found"
    rescue StandardError => e
      @logger.error "error while deleting #{file}: #{e.message}"
      @logger.error "could not delete #{file}"
    end
  end

end

class Database
  attr_reader :host, :name, :user, :pass, :backup_dir

  def initialize(host, name, config)
    @host = host
    @name = name
    @user = config[:user]
    @pass = config[:pass]
    @backup_dir = config[:backup_dir]
  end
end

cmdarg = ARGF.argv[0]
is_verbose = (cmdarg == '-v' || cmdarg == '--verbose')

begin
  logger = Logger.new(logfile, rotate = 'weekly')
rescue StandardError => e
  abort "Couldn't open logfile: #{e.message}. QUITTING!"
end

logger.formatter = proc do |severity, datetime, progname, msg|
  datetime.strftime('%Y-%m-%d %H:%M:%S') + ": #{severity} - #{msg}\n"
end
logger.level = is_verbose ? Logger::DEBUG : Logger::INFO

App.new(root_dir, logger).go!

Potential improvements to this script could be:

• extract the configuration parameters into a separate YAML file
• allow the backup schedules to vary for each database
• allow the 'number of backups to keep' parameter to vary for each database