Send MySQL Innodb Status

capture_dbstatus.rb

#!/usr/bin/env ruby

require 'mail'

#======================
#  config
#======================

# db setting
mycnf_path="/usr/lib/zabbix/scripts/<your-database>.conf"

db_commands = [
  'SHOW FULL PROCESSLIST;',
  'SHOW GLOBAL STATUS;',
  'SHOW ENGINE INNODB MUTEX;',
  'SHOW ENGINE INNODB STATUS;',
]

# mail setting
mail = {}
mail[:from] = 'zabbix+database@example.com'
mail[:to] = ['your_name@example.com', '<your_name>@gmail.com']
mail[:subject] = 'Show database status'

result = {}

#======================
#  main
#======================

# don't use below, for trimming line
#result = `echo "#{db_commands.join}" | mysql  --defaults-extra-file="#{mycnf_path}"`

# get db status
db_commands.each do |cmd|
  result[cmd] = `mysql --defaults-extra-file="#{mycnf_path}" -e "#{cmd}"`
end

# trim results
body = ''
result.each do |key, value|
  body << "#====================================\n"
  body << key
  body << "\n#====================================\n"
  body << "\n"
  body << value.gsub("\\n", "\n")
  body << "\n\n\n"
end

# send mail report
mailer = Mail.new do
  from mail[:from]
  to mail[:to]
  subject mail[:subject]
  body body
end
mailer.deliver!

your_database.conf

[client]
host=127.0.0.1
user=root
password=foobar

Zabbixのリモートコマンドアクション用