#!/usr/local/bin/ruby

require 'mysql'
require 'fileutils'
require 'shellwords'
require 'mail'
require 'socket'

GZIP_CMD = '/usr/bin/gzip'
MYSQLDUMP_CMD = '/usr/local/bin/mysqldump'
DEST_DIR = '/var/backups/db/'
DB_HOST = 'localhost'
DB_USER = 'root'
DB_PASSWD = ''
DAYS = 3
EXCLUDE_DATABASES = [
  'mysql',
  'test',
  'information_schema',
  'performance_schema',
  'nagios',
]
MAIL_FROM = ''
MAIL_TO = ''

puts nil
puts "MySQLサーバ #{DB_HOST} のバックアップ:"
errors = []
db = Mysql.new(DB_HOST, DB_USER, DB_PASSWD)
db.query('SHOW DATABASES').each do |row_db|
  name_db = row_db[0]
  next if EXCLUDE_DATABASES.member?(name_db)
  db.query("USE `#{name_db}`")

  puts "== #{name_db}をチェック"
  db.query('SHOW TABLES').each do |row_table|
    name_table = row_table[0]
    row = db.query("CHECK TABLE `#{name_table}`").fetch_row
    puts row.join(' | ')
    unless row[3] == 'OK'
      errors.push(row.join(' | '))
      row = db.query("REPAIR TABLE `#{name_table}`").fetch_row
      puts row.join(' | ')
      errors.push(row.join(' | '))
    end
  end

  puts "== #{name_db}をダンプ"
  unless Dir.exist?(DEST_DIR + name_db)
    FileUtils.mkdir_p(DEST_DIR + name_db)
  end
  path_dump = "#{DEST_DIR}#{name_db}/#{name_db}_#{Time.now.strftime('%Y-%m-%d')}.sql"

  cmd = [
    'MYSQL_PWD=' + DB_PASSWD,
    MYSQLDUMP_CMD,
    '-h' + DB_HOST,
    '-u' + DB_USER,
    name_db,
    '>' + Shellwords.escape(path_dump),
  ]
  system(cmd.join(' '))

  system(GZIP_CMD, '-f', path_dump)
  path_dump = path_dump + '.gz'
  puts path_dump
  File.chmod(0600, path_dump)
end
db.close

puts "== #{DAYS}日経過したダンプを削除"
expires_on = Time.now - (60 * 60 * 24 * DAYS)
Dir.glob("#{DEST_DIR}*/*.sql.gz").each do |f|
  if File.new(f).ctime < expires_on
    puts f
    File.unlink(f)
  end
end

unless errors.empty?
  mail = Mail.new(charset: 'UTF-8')
  mail.from = MAIL_FROM
  mail.to = MAIL_TO
  mail.subject = "#{Socket.gethostname}で管理しているデータベースに関するレポート"
  mail.body = errors.join("\n")
  mail['X-Priority'] = 1
  mail.delivery_method(:sendmail)
  mail.deliver!
end