bin/metrics-mysql-graphite.rb
#!/usr/bin/env ruby
# frozen_string_literal: false
#
# Push mysql stats into graphite
# ===
#
# NOTE: This plugin will attempt to get replication stats but the user
# must have SUPER or REPLICATION CLIENT privileges to run 'SHOW SLAVE
# STATUS'. It will silently ignore and continue if 'SHOW SLAVE STATUS'
# fails for any reason. The key 'slaveLag' will not be present in the
# output.
#
# Copyright 2012 Pete Shima <me@peteshima.com>
# Additional hacks by Joe Miller - https://github.com/joemiller
# Updated by Oluwaseun Obajobi 2014 to accept ini argument
#
# Released under the same terms as Sensu (the MIT license); see LICENSE
# for details.
#
# USING INI ARGUMENT
# This was implemented to load mysql credentials without parsing the username/password.
# The ini file should be readable by the sensu user/group.
# Ref: http://eric.lubow.org/2009/ruby/parsing-ini-files-with-ruby/
#
# EXAMPLE
# mysql-alive.rb -h db01 --ini '/etc/sensu/my.cnf'
# mysql-alive.rb -h db01 --ini '/etc/sensu/my.cnf' --ini-section customsection
#
# MY.CNF INI FORMAT
# [client]
# user=sensu
# password="abcd1234"
#
# [customsection]
# user=user
# password="password"
#
require 'sensu-plugin/metric/cli'
require 'mysql'
require 'socket'
require 'inifile'
class MysqlGraphite < Sensu::Plugin::Metric::CLI::Graphite
option :host,
short: '-h HOST',
long: '--host HOST',
description: 'Mysql Host to connect to',
required: true
option :port,
short: '-P PORT',
long: '--port PORT',
description: 'Mysql Port to connect to',
proc: proc(&:to_i),
default: 3306
option :username,
short: '-u USERNAME',
long: '--user USERNAME',
description: 'Mysql Username'
option :password,
short: '-p PASSWORD',
long: '--pass PASSWORD',
description: 'Mysql password',
default: ''
option :ini,
short: '-i',
long: '--ini VALUE',
description: 'My.cnf ini file'
option :ini_section,
description: 'Section in my.cnf ini file',
long: '--ini-section VALUE',
default: 'client'
option :scheme,
description: 'Metric naming scheme, text to prepend to metric',
short: '-s SCHEME',
long: '--scheme SCHEME',
default: "#{Socket.gethostname}.mysql"
option :socket,
short: '-S SOCKET',
long: '--socket SOCKET'
option :verbose,
short: '-v',
long: '--verbose',
boolean: true
def metrics_hash
{
'general' => {
'Bytes_received' => 'rxBytes',
'Bytes_sent' => 'txBytes',
'Key_read_requests' => 'keyRead_requests',
'Key_reads' => 'keyReads',
'Key_write_requests' => 'keyWrite_requests',
'Key_writes' => 'keyWrites',
'Binlog_cache_use' => 'binlogCacheUse',
'Binlog_cache_disk_use' => 'binlogCacheDiskUse',
'Max_used_connections' => 'maxUsedConnections',
'Aborted_clients' => 'abortedClients',
'Aborted_connects' => 'abortedConnects',
'Threads_connected' => 'threadsConnected',
'Open_files' => 'openFiles',
'Open_tables' => 'openTables',
'Opened_tables' => 'openedTables',
'Prepared_stmt_count' => 'preparedStmtCount',
'Seconds_Behind_Master' => 'slaveLag',
'Select_full_join' => 'fullJoins',
'Select_full_range_join' => 'fullRangeJoins',
'Select_range' => 'selectRange',
'Select_range_check' => 'selectRange_check',
'Select_scan' => 'selectScan',
'Slow_queries' => 'slowQueries'
},
'querycache' => {
'Qcache_queries_in_cache' => 'queriesInCache',
'Qcache_hits' => 'cacheHits',
'Qcache_inserts' => 'inserts',
'Qcache_not_cached' => 'notCached',
'Qcache_lowmem_prunes' => 'lowMemPrunes'
},
'commands' => {
'Com_admin_commands' => 'admin_commands',
'Com_begin' => 'begin',
'Com_change_db' => 'change_db',
'Com_commit' => 'commit',
'Com_create_table' => 'create_table',
'Com_drop_table' => 'drop_table',
'Com_show_keys' => 'show_keys',
'Com_delete' => 'delete',
'Com_create_db' => 'create_db',
'Com_grant' => 'grant',
'Com_show_processlist' => 'show_processlist',
'Com_flush' => 'flush',
'Com_insert' => 'insert',
'Com_purge' => 'purge',
'Com_replace' => 'replace',
'Com_rollback' => 'rollback',
'Com_select' => 'select',
'Com_set_option' => 'set_option',
'Com_show_binlogs' => 'show_binlogs',
'Com_show_databases' => 'show_databases',
'Com_show_fields' => 'show_fields',
'Com_show_status' => 'show_status',
'Com_show_tables' => 'show_tables',
'Com_show_variables' => 'show_variables',
'Com_update' => 'update',
'Com_drop_db' => 'drop_db',
'Com_revoke' => 'revoke',
'Com_drop_user' => 'drop_user',
'Com_show_grants' => 'show_grants',
'Com_lock_tables' => 'lock_tables',
'Com_show_create_table' => 'show_create_table',
'Com_unlock_tables' => 'unlock_tables',
'Com_alter_table' => 'alter_table'
},
'counters' => {
'Handler_write' => 'handlerWrite',
'Handler_update' => 'handlerUpdate',
'Handler_delete' => 'handlerDelete',
'Handler_read_first' => 'handlerRead_first',
'Handler_read_key' => 'handlerRead_key',
'Handler_read_next' => 'handlerRead_next',
'Handler_read_prev' => 'handlerRead_prev',
'Handler_read_rnd' => 'handlerRead_rnd',
'Handler_read_rnd_next' => 'handlerRead_rnd_next',
'Handler_commit' => 'handlerCommit',
'Handler_rollback' => 'handlerRollback',
'Handler_savepoint' => 'handlerSavepoint',
'Handler_savepoint_rollback' => 'handlerSavepointRollback'
},
'innodb' => {
'Innodb_buffer_pool_pages_total' => 'bufferTotal_pages',
'Innodb_buffer_pool_pages_free' => 'bufferFree_pages',
'Innodb_buffer_pool_pages_dirty' => 'bufferDirty_pages',
'Innodb_buffer_pool_pages_data' => 'bufferUsed_pages',
'Innodb_page_size' => 'pageSize',
'Innodb_pages_created' => 'pagesCreated',
'Innodb_pages_read' => 'pagesRead',
'Innodb_pages_written' => 'pagesWritten',
'Innodb_row_lock_current_waits' => 'currentLockWaits',
'Innodb_row_lock_waits' => 'lockWaitTimes',
'Innodb_row_lock_time' => 'rowLockTime',
'Innodb_data_reads' => 'fileReads',
'Innodb_data_writes' => 'fileWrites',
'Innodb_data_fsyncs' => 'fileFsyncs',
'Innodb_log_writes' => 'logWrites',
'Innodb_rows_updated' => 'rowsUpdated',
'Innodb_rows_read' => 'rowsRead',
'Innodb_rows_deleted' => 'rowsDeleted',
'Innodb_rows_inserted' => 'rowsInserted'
},
'configuration' => {
'max_connections' => 'MaxConnections',
'Max_prepared_stmt_count' => 'MaxPreparedStmtCount'
}
}
end
def run
# props to https://github.com/coredump/hoardd/blob/master/scripts-available/mysql.coffee
metrics = metrics_hash
# FIXME: break this up
config[:host].split(' ').each do |mysql_host| # rubocop:disable Metrics/BlockLength
mysql_shorthostname = mysql_host.split('.')[0]
if config[:ini]
ini = IniFile.load(config[:ini])
section = ini[config[:ini_section]]
db_user = section['user']
db_pass = section['password']
else
db_user = config[:username]
db_pass = config[:password]
end
begin
mysql = Mysql.new(mysql_host, db_user, db_pass, nil, config[:port], config[:socket])
results = mysql.query('SHOW GLOBAL STATUS')
rescue StandardError => e
puts e.message
end
results.each_hash do |row|
metrics.each do |category, var_mapping|
if var_mapping.key?(row['Variable_name'])
output "#{config[:scheme]}.#{mysql_shorthostname}.#{category}.#{var_mapping[row['Variable_name']]}", row['Value']
end
end
end
begin
slave_results = mysql.query('SHOW SLAVE STATUS')
# should return a single element array containing one hash
# #YELLOW
slave_results.fetch_hash.each_pair do |key, value|
if metrics['general'].include?(key)
# Replication lag being null is bad, very bad, so negativate it here
value = -1 if key == 'Seconds_Behind_Master' && value.nil?
output "#{config[:scheme]}.#{mysql_shorthostname}.general.#{metrics['general'][key]}", value
end
end
rescue StandardError => e
puts "Error querying slave status: #{e}" if config[:verbose]
end
begin
variables_results = mysql.query('SHOW GLOBAL VARIABLES')
category = 'configuration'
variables_results.each_hash do |row|
metrics[category].each do |metric, desc|
if metric.casecmp(row['Variable_name']).zero?
output "#{config[:scheme]}.#{mysql_shorthostname}.#{category}.#{desc}", row['Value']
end
end
end
rescue StandardError => e
puts e.message
end
mysql&.close
end
ok
end
end