#!/usr/bin/env ruby -w

require "mysql"
require "fileutils"
require "rubygems"
require_gem "actionmailer"

# --------------------------------------------------------------------------------
# mysql backup script
# platform: linux/ mac os x
# author: Marcus Wendt (www.marcuswendt.com)
# created: Jan 10, 2007
# version: 0.1
# --------------------------------------------------------------------------------
module MW
  module Utils
    class MySQLBackup
      # configuration
      @@name = 'backup-name'
      @@baseurl = 'https://www.your-host.com/backup_dir'

      @@db_server = 'localhost'
      @@db_user = 'XXXX'
      @@db_pass = 'XXX'

      # further options
      @@verbose = true
      @@verbose_level = 1 # set this to 0 to see all messages
      @@mysqLdump = '/usr/local/mysql/bin/mysqldump'
      @@file_opts = {}
      @@tar_opts = 'cjf'
      @@tar_suffix = 'tar.bz2'

      # do it!
      def initialize
        @@file_opts = {:verbose => @@verbose} if @@verbose_level == 0

        @time_start = Time.now
        nl
        line
        log "mysql backup script started #{format_time(@time_start)} ..."
        line

        # setup directories
        nl
        log "> checking directory structure"
        FileUtils.mkdir_p(archive_dir, @@file_opts)
        remove_tmp
        FileUtils.mkdir_p(tmp_dir, @@file_opts)

        # dump data
        nl
        log "> dumping databases"
        for db in databases
          log  "  #{db} ..."
          exec "    #{@@mysqLdump} --user=#{@@db_user} --password=#{@@db_pass} #{db} > #{tmp_dir}/#{db}.sql"
        end

        # compress data
        nl
        log "> creating archive"
        FileUtils.cd(tmp_dir, @@file_opts)
        exec "  tar #{@@tar_opts} #{filename} *.sql"
        FileUtils.mv("#{tmp_dir}/#{filename}", "#{archive_dir}/#{filename}", @@file_opts)

        # clean up
        nl
        log "> cleaning up"
        remove_tmp

        # show final message
        @time_end = Time.now
        duration = round(@time_end - @time_start,2)
        nl
        log "finished! (script executed in #{duration} seconds)"

        # send notifications
      end

      # name of the current archive file
      def filename
         "#{name}.#{@@tar_suffix}"
      end

      # name of the current backup
      def name
        "#{timestamp}_#{@@name}"
      end

      def wd
        Dir.getwd    
      end

      def format_time(time)
        time.strftime("%H:%M")    
      end

      def timestamp
        @timestamp = Time.now.strftime("%Y.%m.%d_%H_%M") if @timestamp.nil?
        return @timestamp
      end

      def archive_dir
        @archive_dir = "#{wd}/archives" if @archive_dir.nil?
        return @archive_dir
      end

      def tmp_dir
        "#{archive_dir}/tmp"
      end

      def remove_tmp
        FileUtils.rm_rf(tmp_dir, @@file_opts) if File.exists?(tmp_dir)
      end

      def databases
        database_list=[]
        db = MySQLHelper.new 
        db.connect(@@db_server, @@db_user, @@db_pass)
        dbr = db.query('SHOW DATABASES')
        while row = dbr.fetch_row
          database_list << row.to_s
        end
        dbr.free
        db.close
        return database_list
      end

      def exec(command)
        log(command.gsub(@@db_pass, '****').gsub(@@db_user, '****'), 0)
        return `#{command}`
      end

      def log(str, level=1)
        puts str if @@verbose && level >= @@verbose_level
      end

      def line
        log "----------------------------------------------------"
      end

      def nl 
        log ""
      end

      def round(numeric, precision)
        pow=10.0**precision.to_f
        (numeric*pow).round / pow
      end
    end

    # helper classes
    # ----------------------------------------------------------------------
    class MySQLHelper
      def connect(server, user, pass)
        begin
          @dbh = Mysql.real_connect(server, user, pass)
        rescue Mysql::Error => e
          puts "Error code: #{e.errno}"
          puts "Error message: #{e.error}"
          puts "Error SQLSTATE: #{e.sqlstate}" if e.respond_to?("sqlstate")
        end
      end

      def query(sql)
        @dbr = @dbh.query(sql) if @dbh
      end

      def free
        @dbr.free if @dbr
      end

      def close
        @dbh.close if @dbh
      end
    end
  end
end

# go!
MW::Utils::MySQLBackup.new