Skip to content

Instantly share code, notes, and snippets.

@pmaene

pmaene/db-mysqlstat.pl

Created April 12, 2013 22:25
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save pmaene/5375675 to your computer and use it in GitHub Desktop.
Save pmaene/5375675 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
db-mysqlstat.pl
#!/bin/env perl
# This program was designed to work with Zabbix to keep monitored MySQL databases.
# Copyright (C) 2012 Wilmer Jaramillo M. <wilmer@fedoraproject.org>
#
# This program is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation; either version 3 of the License, or
# (at your option) any later version.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program. If not, see <http://www.gnu.org/licenses/>
use strict;
use DBI;
use Getopt::Long;
my ($host, $database, $user, $password);
my %queries = (
'aborted_clients' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'aborted_clients'},
'aborted_connects' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'aborted_connects'},
'binlog_cache_disk_use' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'binlog_cache_disk_use'},
'binlog_cache_use' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'binlog_cache_use'},
'bytes_received' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'bytes_received'},
'bytes_sent' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'bytes_sent'},
'com_admin_commands' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'com_admin_commands'},
'com_alter_db' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'com_alter_db'},
'com_alter_table' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'com_alter_table'},
'com_analyze' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'com_analyze'},
'com_backup_table' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'com_backup_table'},
'com_begin' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'com_begin'},
'com_change_db' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'com_change_db'},
'com_change_master' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'com_change_master'},
'com_check' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'com_check'},
'com_checksum' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'com_checksum'},
'com_commit' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'com_commit'},
'com_create_db' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'com_create_db'},
'com_create_function' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'com_create_function'},
'com_create_index' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'com_create_index'},
'com_create_table' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'com_create_table'},
'com_create_user' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'com_create_user'},
'com_dealloc_sql' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'com_dealloc_sql'},
'com_delete' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'com_delete'},
'com_delete_multi' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'com_delete_multi'},
'com_do' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'com_do'},
'com_drop_db' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'com_drop_db'},
'com_drop_function' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'com_drop_function'},
'com_drop_index' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'com_drop_index'},
'com_drop_table' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'com_drop_table'},
'com_drop_user' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'com_drop_user'},
'com_execute_sql' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'com_execute_sql'},
'com_flush' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'com_flush'},
'com_grant' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'com_grant'},
'com_ha_close' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'com_ha_close'},
'com_ha_open' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'com_ha_open'},
'com_help' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'com_help'},
'com_insert' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'com_insert'},
'com_insert_select' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'com_insert_select'},
'com_kill' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'com_kill'},
'com_load' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'com_load'},
'com_load_master_data' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'com_load_master_data'},
'com_load_master_table' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'com_load_master_table'},
'com_lock_tables' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'com_lock_tables'},
'com_optimize' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'com_optimize'},
'com_preload_keys' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'com_preload_keys'},
'com_prepare_sql' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'com_prepare_sql'},
'com_purge' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'com_purge'},
'com_purge_before_date' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'com_purge_before_date'},
'com_rename_table' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'com_rename_table'},
'com_repair' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'com_repair'},
'com_replace' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'com_replace'},
'com_replace_select' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'com_replace_select'},
'com_reset' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'com_reset'},
'com_restore_table' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'com_restore_table'},
'com_revoke' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'com_revoke'},
'com_revoke_all' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'com_revoke_all'},
'com_rollback' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'com_rollback'},
'com_savepoint' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'com_savepoint'},
'com_select' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'com_select'},
'com_set_option' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'com_set_option'},
'com_show_binlog_events' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'com_show_binlog_events'},
'com_show_binlogs' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'com_show_binlogs'},
'com_show_charsets' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'com_show_charsets'},
'com_show_collations' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'com_show_collations'},
'com_show_column_types' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'com_show_column_types'},
'com_show_create_db' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'com_show_create_db'},
'com_show_create_table' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'com_show_create_table'},
'com_show_databases' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'com_show_databases'},
'com_show_errors' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'com_show_errors'},
'com_show_fields' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'com_show_fields'},
'com_show_grants' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'com_show_grants'},
'com_show_innodb_status' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'com_show_innodb_status'},
'com_show_keys' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'com_show_keys'},
'com_show_logs' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'com_show_logs'},
'com_show_master_status' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'com_show_master_status'},
'com_show_ndb_status' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'com_show_ndb_status'},
'com_show_new_master' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'com_show_new_master'},
'com_show_open_tables' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'com_show_open_tables'},
'com_show_privileges' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'com_show_privileges'},
'com_show_processlist' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'com_show_processlist'},
'com_show_slave_hosts' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'com_show_slave_hosts'},
'com_show_slave_status' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'com_show_slave_status'},
'com_show_status' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'com_show_status'},
'com_show_storage_engines' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'com_show_storage_engines'},
'com_show_tables' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'com_show_tables'},
'com_show_triggers' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'com_show_triggers'},
'com_show_variables' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'com_show_variables'},
'com_show_warnings' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'com_show_warnings'},
'com_slave_start' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'com_slave_start'},
'com_slave_stop' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'com_slave_stop'},
'com_stmt_close' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'com_stmt_close'},
'com_stmt_execute' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'com_stmt_execute'},
'com_stmt_fetch' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'com_stmt_fetch'},
'com_stmt_prepare' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'com_stmt_prepare'},
'com_stmt_reset' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'com_stmt_reset'},
'com_stmt_send_long_data' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'com_stmt_send_long_data'},
'com_truncate' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'com_truncate'},
'com_unlock_tables' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'com_unlock_tables'},
'com_update' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'com_update'},
'com_update_multi' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'com_update_multi'},
'com_xa_commit' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'com_xa_commit'},
'com_xa_end' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'com_xa_end'},
'com_xa_prepare' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'com_xa_prepare'},
'com_xa_recover' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'com_xa_recover'},
'com_xa_rollback' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'com_xa_rollback'},
'com_xa_start' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'com_xa_start'},
'compression' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'compression'},
'connections' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'connections'},
'created_tmp_disk_tables' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'created_tmp_disk_tables'},
'created_tmp_files' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'created_tmp_files'},
'created_tmp_tables' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'created_tmp_tables'},
'delayed_errors' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'delayed_errors'},
'delayed_insert_threads' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'delayed_insert_threads'},
'delayed_writes' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'delayed_writes'},
'flush_commands' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'flush_commands'},
'handler_commit' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'handler_commit'},
'handler_delete' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'handler_delete'},
'handler_discover' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'handler_discover'},
'handler_prepare' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'handler_prepare'},
'handler_read_first' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'handler_read_first'},
'handler_read_key' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'handler_read_key'},
'handler_read_next' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'handler_read_next'},
'handler_read_prev' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'handler_read_prev'},
'handler_read_rnd' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'handler_read_rnd'},
'handler_read_rnd_next' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'handler_read_rnd_next'},
'handler_rollback' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'handler_rollback'},
'handler_savepoint' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'handler_savepoint'},
'handler_savepoint_rollback' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'handler_savepoint_rollback'},
'handler_update' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'handler_update'},
'handler_write' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'handler_write'},
'innodb_buffer_pool_pages_data' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'innodb_buffer_pool_pages_data'},
'innodb_buffer_pool_pages_dirty' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'innodb_buffer_pool_pages_dirty'},
'innodb_buffer_pool_pages_flushed' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'innodb_buffer_pool_pages_flushed'},
'innodb_buffer_pool_pages_free' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'innodb_buffer_pool_pages_free'},
'innodb_buffer_pool_pages_misc' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'innodb_buffer_pool_pages_misc'},
'innodb_buffer_pool_pages_total' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'innodb_buffer_pool_pages_total'},
'innodb_buffer_pool_read_ahead_rnd' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'innodb_buffer_pool_read_ahead_rnd'},
'innodb_buffer_pool_read_ahead_seq' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'innodb_buffer_pool_read_ahead_seq'},
'innodb_buffer_pool_read_requests' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'innodb_buffer_pool_read_requests'},
'innodb_buffer_pool_reads' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'innodb_buffer_pool_reads'},
'innodb_buffer_pool_wait_free' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'innodb_buffer_pool_wait_free'},
'innodb_buffer_pool_write_requests' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'innodb_buffer_pool_write_requests'},
'innodb_data_fsyncs' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'innodb_data_fsyncs'},
'innodb_data_pending_fsyncs' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'innodb_data_pending_fsyncs'},
'innodb_data_pending_reads' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'innodb_data_pending_reads'},
'innodb_data_pending_writes' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'innodb_data_pending_writes'},
'innodb_data_read' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'innodb_data_read'},
'innodb_data_reads' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'innodb_data_reads'},
'innodb_data_writes' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'innodb_data_writes'},
'innodb_data_written' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'innodb_data_written'},
'innodb_dblwr_pages_written' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'innodb_dblwr_pages_written'},
'innodb_dblwr_writes' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'innodb_dblwr_writes'},
'innodb_log_waits' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'innodb_log_waits'},
'innodb_log_write_requests' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'innodb_log_write_requests'},
'innodb_log_writes' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'innodb_log_writes'},
'innodb_os_log_fsyncs' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'innodb_os_log_fsyncs'},
'innodb_os_log_pending_fsyncs' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'innodb_os_log_pending_fsyncs'},
'innodb_os_log_pending_writes' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'innodb_os_log_pending_writes'},
'innodb_os_log_written' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'innodb_os_log_written'},
'innodb_page_size' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'innodb_page_size'},
'innodb_pages_created' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'innodb_pages_created'},
'innodb_pages_read' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'innodb_pages_read'},
'innodb_pages_written' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'innodb_pages_written'},
'innodb_row_lock_current_waits' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'innodb_row_lock_current_waits'},
'innodb_row_lock_time' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'innodb_row_lock_time'},
'innodb_row_lock_time_avg' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'innodb_row_lock_time_avg'},
'innodb_row_lock_time_max' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'innodb_row_lock_time_max'},
'innodb_row_lock_waits' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'innodb_row_lock_waits'},
'innodb_rows_deleted' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'innodb_rows_deleted'},
'innodb_rows_inserted' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'innodb_rows_inserted'},
'innodb_rows_read' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'innodb_rows_read'},
'innodb_rows_updated' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'innodb_rows_updated'},
'key_blocks_not_flushed' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'key_blocks_not_flushed'},
'key_blocks_unused' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'key_blocks_unused'},
'key_blocks_used' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'key_blocks_used'},
'key_read_requests' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'key_read_requests'},
'key_reads' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'key_reads'},
'key_write_requests' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'key_write_requests'},
'key_writes' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'key_writes'},
'last_query_cost' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'last_query_cost'},
'max_used_connections' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'max_used_connections'},
'ndb_cluster_node_id' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'ndb_cluster_node_id'},
'ndb_config_from_host' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'ndb_config_from_host'},
'ndb_config_from_port' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'ndb_config_from_port'},
'ndb_number_of_data_nodes' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'ndb_number_of_data_nodes'},
'not_flushed_delayed_rows' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'not_flushed_delayed_rows'},
'open_files' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'open_files'},
'open_streams' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'open_streams'},
'open_tables' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'open_tables'},
'opened_tables' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'opened_tables'},
'prepared_stmt_count' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'prepared_stmt_count'},
'qcache_free_blocks' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'qcache_free_blocks'},
'qcache_free_memory' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'qcache_free_memory'},
'qcache_hits' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'qcache_hits'},
'qcache_inserts' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'qcache_inserts'},
'qcache_lowmem_prunes' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'qcache_lowmem_prunes'},
'qcache_not_cached' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'qcache_not_cached'},
'qcache_queries_in_cache' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'qcache_queries_in_cache'},
'qcache_total_blocks' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'qcache_total_blocks'},
'questions' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'questions'},
'rpl_status' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'rpl_status'},
'select_full_join' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'select_full_join'},
'select_full_range_join' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'select_full_range_join'},
'select_range' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'select_range'},
'select_range_check' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'select_range_check'},
'select_scan' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'select_scan'},
'slave_open_temp_tables' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'slave_open_temp_tables'},
'slave_retried_transactions' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'slave_retried_transactions'},
'slave_running' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'slave_running'},
'slow_launch_threads' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'slow_launch_threads'},
'slow_queries' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'slow_queries'},
'sort_merge_passes' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'sort_merge_passes'},
'sort_range' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'sort_range'},
'sort_rows' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'sort_rows'},
'sort_scan' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'sort_scan'},
'table_locks_immediate' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'table_locks_immediate'},
'table_locks_waited' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'table_locks_waited'},
'tc_log_max_pages_used' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'tc_log_max_pages_used'},
'tc_log_page_size' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'tc_log_page_size'},
'tc_log_page_waits' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'tc_log_page_waits'},
'threads_cached' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'threads_cached'},
'threads_connected' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'threads_connected'},
'threads_created' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'threads_created'},
'threads_running' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'threads_running'},
'uptime' => qq{SHOW GLOBAL STATUS WHERE variable_name = 'uptime'}
);
GetOptions(
'host=s' => \$host,
'user=s' => \$user,
'pass=s' => \$password,
'database=s' => \$database,
'aborted_clients' => sub { print query_database($queries{aborted_clients}) },
'aborted_connects' => sub { print query_database($queries{aborted_connects}) },
'binlog_cache_disk_use' => sub { print query_database($queries{binlog_cache_disk_use}) },
'binlog_cache_use' => sub { print query_database($queries{binlog_cache_use}) },
'bytes_received' => sub { print query_database($queries{bytes_received}) },
'bytes_sent' => sub { print query_database($queries{bytes_sent}) },
'com_admin_commands' => sub { print query_database($queries{com_admin_commands}) },
'com_alter_db' => sub { print query_database($queries{com_alter_db}) },
'com_alter_table' => sub { print query_database($queries{com_alter_table}) },
'com_analyze' => sub { print query_database($queries{com_analyze}) },
'com_backup_table' => sub { print query_database($queries{com_backup_table}) },
'com_begin' => sub { print query_database($queries{com_begin}) },
'com_change_db' => sub { print query_database($queries{com_change_db}) },
'com_change_master' => sub { print query_database($queries{com_change_master}) },
'com_check' => sub { print query_database($queries{com_check}) },
'com_checksum' => sub { print query_database($queries{com_checksum}) },
'com_commit' => sub { print query_database($queries{com_commit}) },
'com_create_db' => sub { print query_database($queries{com_create_db}) },
'com_create_function' => sub { print query_database($queries{com_create_function}) },
'com_create_index' => sub { print query_database($queries{com_create_index}) },
'com_create_table' => sub { print query_database($queries{com_create_table}) },
'com_create_user' => sub { print query_database($queries{com_create_user}) },
'com_dealloc_sql' => sub { print query_database($queries{com_dealloc_sql}) },
'com_delete' => sub { print query_database($queries{com_delete}) },
'com_delete_multi' => sub { print query_database($queries{com_delete_multi}) },
'com_do' => sub { print query_database($queries{com_do}) },
'com_drop_db' => sub { print query_database($queries{com_drop_db}) },
'com_drop_function' => sub { print query_database($queries{com_drop_function}) },
'com_drop_index' => sub { print query_database($queries{com_drop_index}) },
'com_drop_table' => sub { print query_database($queries{com_drop_table}) },
'com_drop_user' => sub { print query_database($queries{com_drop_user}) },
'com_execute_sql' => sub { print query_database($queries{com_execute_sql}) },
'com_flush' => sub { print query_database($queries{com_flush}) },
'com_grant' => sub { print query_database($queries{com_grant}) },
'com_ha_close' => sub { print query_database($queries{com_ha_close}) },
'com_ha_open' => sub { print query_database($queries{com_ha_open}) },
'com_help' => sub { print query_database($queries{com_help}) },
'com_insert' => sub { print query_database($queries{com_insert}) },
'com_insert_select' => sub { print query_database($queries{com_insert_select}) },
'com_kill' => sub { print query_database($queries{com_kill}) },
'com_load' => sub { print query_database($queries{com_load}) },
'com_load_master_data' => sub { print query_database($queries{com_load_master_data}) },
'com_load_master_table' => sub { print query_database($queries{com_load_master_table}) },
'com_lock_tables' => sub { print query_database($queries{com_lock_tables}) },
'com_optimize' => sub { print query_database($queries{com_optimize}) },
'com_preload_keys' => sub { print query_database($queries{com_preload_keys}) },
'com_prepare_sql' => sub { print query_database($queries{com_prepare_sql}) },
'com_purge' => sub { print query_database($queries{com_purge}) },
'com_purge_before_date' => sub { print query_database($queries{com_purge_before_date}) },
'com_rename_table' => sub { print query_database($queries{com_rename_table}) },
'com_repair' => sub { print query_database($queries{com_repair}) },
'com_replace' => sub { print query_database($queries{com_replace}) },
'com_replace_select' => sub { print query_database($queries{com_replace_select}) },
'com_reset' => sub { print query_database($queries{com_reset}) },
'com_restore_table' => sub { print query_database($queries{com_restore_table}) },
'com_revoke' => sub { print query_database($queries{com_revoke}) },
'com_revoke_all' => sub { print query_database($queries{com_revoke_all}) },
'com_rollback' => sub { print query_database($queries{com_rollback}) },
'com_savepoint' => sub { print query_database($queries{com_savepoint}) },
'com_select' => sub { print query_database($queries{com_select}) },
'com_set_option' => sub { print query_database($queries{com_set_option}) },
'com_show_binlog_events' => sub { print query_database($queries{com_show_binlog_events}) },
'com_show_binlogs' => sub { print query_database($queries{com_show_binlogs}) },
'com_show_charsets' => sub { print query_database($queries{com_show_charsets}) },
'com_show_collations' => sub { print query_database($queries{com_show_collations}) },
'com_show_column_types' => sub { print query_database($queries{com_show_column_types}) },
'com_show_create_db' => sub { print query_database($queries{com_show_create_db}) },
'com_show_create_table' => sub { print query_database($queries{com_show_create_table}) },
'com_show_databases' => sub { print query_database($queries{com_show_databases}) },
'com_show_errors' => sub { print query_database($queries{com_show_errors}) },
'com_show_fields' => sub { print query_database($queries{com_show_fields}) },
'com_show_grants' => sub { print query_database($queries{com_show_grants}) },
'com_show_innodb_status' => sub { print query_database($queries{com_show_innodb_status}) },
'com_show_keys' => sub { print query_database($queries{com_show_keys}) },
'com_show_logs' => sub { print query_database($queries{com_show_logs}) },
'com_show_master_status' => sub { print query_database($queries{com_show_master_status}) },
'com_show_ndb_status' => sub { print query_database($queries{com_show_ndb_status}) },
'com_show_new_master' => sub { print query_database($queries{com_show_new_master}) },
'com_show_open_tables' => sub { print query_database($queries{com_show_open_tables}) },
'com_show_privileges' => sub { print query_database($queries{com_show_privileges}) },
'com_show_processlist' => sub { print query_database($queries{com_show_processlist}) },
'com_show_slave_hosts' => sub { print query_database($queries{com_show_slave_hosts}) },
'com_show_slave_status' => sub { print query_database($queries{com_show_slave_status}) },
'com_show_status' => sub { print query_database($queries{com_show_status}) },
'com_show_storage_engines' => sub { print query_database($queries{com_show_storage_engines}) },
'com_show_tables' => sub { print query_database($queries{com_show_tables}) },
'com_show_triggers' => sub { print query_database($queries{com_show_triggers}) },
'com_show_variables' => sub { print query_database($queries{com_show_variables}) },
'com_show_warnings' => sub { print query_database($queries{com_show_warnings}) },
'com_slave_start' => sub { print query_database($queries{com_slave_start}) },
'com_slave_stop' => sub { print query_database($queries{com_slave_stop}) },
'com_stmt_close' => sub { print query_database($queries{com_stmt_close}) },
'com_stmt_execute' => sub { print query_database($queries{com_stmt_execute}) },
'com_stmt_fetch' => sub { print query_database($queries{com_stmt_fetch}) },
'com_stmt_prepare' => sub { print query_database($queries{com_stmt_prepare}) },
'com_stmt_reset' => sub { print query_database($queries{com_stmt_reset}) },
'com_stmt_send_long_data' => sub { print query_database($queries{com_stmt_send_long_data}) },
'com_truncate' => sub { print query_database($queries{com_truncate}) },
'com_unlock_tables' => sub { print query_database($queries{com_unlock_tables}) },
'com_update' => sub { print query_database($queries{com_update}) },
'com_update_multi' => sub { print query_database($queries{com_update_multi}) },
'com_xa_commit' => sub { print query_database($queries{com_xa_commit}) },
'com_xa_end' => sub { print query_database($queries{com_xa_end}) },
'com_xa_prepare' => sub { print query_database($queries{com_xa_prepare}) },
'com_xa_recover' => sub { print query_database($queries{com_xa_recover}) },
'com_xa_rollback' => sub { print query_database($queries{com_xa_rollback}) },
'com_xa_start' => sub { print query_database($queries{com_xa_start}) },
'compression' => sub { print query_database($queries{compression}) },
'connections' => sub { print query_database($queries{connections}) },
'created_tmp_disk_tables' => sub { print query_database($queries{created_tmp_disk_tables}) },
'created_tmp_files' => sub { print query_database($queries{created_tmp_files}) },
'created_tmp_tables' => sub { print query_database($queries{created_tmp_tables}) },
'delayed_errors' => sub { print query_database($queries{delayed_errors}) },
'delayed_insert_threads' => sub { print query_database($queries{delayed_insert_threads}) },
'delayed_writes' => sub { print query_database($queries{delayed_writes}) },
'flush_commands' => sub { print query_database($queries{flush_commands}) },
'handler_commit' => sub { print query_database($queries{handler_commit}) },
'handler_delete' => sub { print query_database($queries{handler_delete}) },
'handler_discover' => sub { print query_database($queries{handler_discover}) },
'handler_prepare' => sub { print query_database($queries{handler_prepare}) },
'handler_read_first' => sub { print query_database($queries{handler_read_first}) },
'handler_read_key' => sub { print query_database($queries{handler_read_key}) },
'handler_read_next' => sub { print query_database($queries{handler_read_next}) },
'handler_read_prev' => sub { print query_database($queries{handler_read_prev}) },
'handler_read_rnd' => sub { print query_database($queries{handler_read_rnd}) },
'handler_read_rnd_next' => sub { print query_database($queries{handler_read_rnd_next}) },
'handler_rollback' => sub { print query_database($queries{handler_rollback}) },
'handler_savepoint' => sub { print query_database($queries{handler_savepoint}) },
'handler_savepoint_rollback' => sub { print query_database($queries{handler_savepoint_rollback}) },
'handler_update' => sub { print query_database($queries{handler_update}) },
'handler_write' => sub { print query_database($queries{handler_write}) },
'innodb_buffer_pool_pages_data' => sub { print query_database($queries{innodb_buffer_pool_pages_data}) },
'innodb_buffer_pool_pages_dirty' => sub { print query_database($queries{innodb_buffer_pool_pages_dirty}) },
'innodb_buffer_pool_pages_flushed' => sub { print query_database($queries{innodb_buffer_pool_pages_flushed}) },
'innodb_buffer_pool_pages_free' => sub { print query_database($queries{innodb_buffer_pool_pages_free}) },
'innodb_buffer_pool_pages_misc' => sub { print query_database($queries{innodb_buffer_pool_pages_misc}) },
'innodb_buffer_pool_pages_total' => sub { print query_database($queries{innodb_buffer_pool_pages_total}) },
'innodb_buffer_pool_read_ahead_rnd' => sub { print query_database($queries{innodb_buffer_pool_read_ahead_rnd}) },
'innodb_buffer_pool_read_ahead_seq' => sub { print query_database($queries{innodb_buffer_pool_read_ahead_seq}) },
'innodb_buffer_pool_read_requests' => sub { print query_database($queries{innodb_buffer_pool_read_requests}) },
'innodb_buffer_pool_reads' => sub { print query_database($queries{innodb_buffer_pool_reads}) },
'innodb_buffer_pool_wait_free' => sub { print query_database($queries{innodb_buffer_pool_wait_free}) },
'innodb_buffer_pool_write_requests' => sub { print query_database($queries{innodb_buffer_pool_write_requests}) },
'innodb_data_fsyncs' => sub { print query_database($queries{innodb_data_fsyncs}) },
'innodb_data_pending_fsyncs' => sub { print query_database($queries{innodb_data_pending_fsyncs}) },
'innodb_data_pending_reads' => sub { print query_database($queries{innodb_data_pending_reads}) },
'innodb_data_pending_writes' => sub { print query_database($queries{innodb_data_pending_writes}) },
'innodb_data_read' => sub { print query_database($queries{innodb_data_read}) },
'innodb_data_reads' => sub { print query_database($queries{innodb_data_reads}) },
'innodb_data_writes' => sub { print query_database($queries{innodb_data_writes}) },
'innodb_data_written' => sub { print query_database($queries{innodb_data_written}) },
'innodb_dblwr_pages_written' => sub { print query_database($queries{innodb_dblwr_pages_written}) },
'innodb_dblwr_writes' => sub { print query_database($queries{innodb_dblwr_writes}) },
'innodb_log_waits' => sub { print query_database($queries{innodb_log_waits}) },
'innodb_log_write_requests' => sub { print query_database($queries{innodb_log_write_requests}) },
'innodb_log_writes' => sub { print query_database($queries{innodb_log_writes}) },
'innodb_os_log_fsyncs' => sub { print query_database($queries{innodb_os_log_fsyncs}) },
'innodb_os_log_pending_fsyncs' => sub { print query_database($queries{innodb_os_log_pending_fsyncs}) },
'innodb_os_log_pending_writes' => sub { print query_database($queries{innodb_os_log_pending_writes}) },
'innodb_os_log_written' => sub { print query_database($queries{innodb_os_log_written}) },
'innodb_page_size' => sub { print query_database($queries{innodb_page_size}) },
'innodb_pages_created' => sub { print query_database($queries{innodb_pages_created}) },
'innodb_pages_read' => sub { print query_database($queries{innodb_pages_read}) },
'innodb_pages_written' => sub { print query_database($queries{innodb_pages_written}) },
'innodb_row_lock_current_waits' => sub { print query_database($queries{innodb_row_lock_current_waits}) },
'innodb_row_lock_time' => sub { print query_database($queries{innodb_row_lock_time}) },
'innodb_row_lock_time_avg' => sub { print query_database($queries{innodb_row_lock_time_avg}) },
'innodb_row_lock_time_max' => sub { print query_database($queries{innodb_row_lock_time_max}) },
'innodb_row_lock_waits' => sub { print query_database($queries{innodb_row_lock_waits}) },
'innodb_rows_deleted' => sub { print query_database($queries{innodb_rows_deleted}) },
'innodb_rows_inserted' => sub { print query_database($queries{innodb_rows_inserted}) },
'innodb_rows_read' => sub { print query_database($queries{innodb_rows_read}) },
'innodb_rows_updated' => sub { print query_database($queries{innodb_rows_updated}) },
'key_blocks_not_flushed' => sub { print query_database($queries{key_blocks_not_flushed}) },
'key_blocks_unused' => sub { print query_database($queries{key_blocks_unused}) },
'key_blocks_used' => sub { print query_database($queries{key_blocks_used}) },
'key_read_requests' => sub { print query_database($queries{key_read_requests}) },
'key_reads' => sub { print query_database($queries{key_reads}) },
'key_write_requests' => sub { print query_database($queries{key_write_requests}) },
'key_writes' => sub { print query_database($queries{key_writes}) },
'last_query_cost' => sub { print query_database($queries{last_query_cost}) },
'max_used_connections' => sub { print query_database($queries{max_used_connections}) },
'ndb_cluster_node_id' => sub { print query_database($queries{ndb_cluster_node_id}) },
'ndb_config_from_host' => sub { print query_database($queries{ndb_config_from_host}) },
'ndb_config_from_port' => sub { print query_database($queries{ndb_config_from_port}) },
'ndb_number_of_data_nodes' => sub { print query_database($queries{ndb_number_of_data_nodes}) },
'not_flushed_delayed_rows' => sub { print query_database($queries{not_flushed_delayed_rows}) },
'open_files' => sub { print query_database($queries{open_files}) },
'open_streams' => sub { print query_database($queries{open_streams}) },
'open_tables' => sub { print query_database($queries{open_tables}) },
'opened_tables' => sub { print query_database($queries{opened_tables}) },
'prepared_stmt_count' => sub { print query_database($queries{prepared_stmt_count}) },
'qcache_free_blocks' => sub { print query_database($queries{qcache_free_blocks}) },
'qcache_free_memory' => sub { print query_database($queries{qcache_free_memory}) },
'qcache_hits' => sub { print query_database($queries{qcache_hits}) },
'qcache_inserts' => sub { print query_database($queries{qcache_inserts}) },
'qcache_lowmem_prunes' => sub { print query_database($queries{qcache_lowmem_prunes}) },
'qcache_not_cached' => sub { print query_database($queries{qcache_not_cached}) },
'qcache_queries_in_cache' => sub { print query_database($queries{qcache_queries_in_cache}) },
'qcache_total_blocks' => sub { print query_database($queries{qcache_total_blocks}) },
'questions' => sub { print query_database($queries{questions}) },
'rpl_status' => sub { print query_database($queries{rpl_status}) },
'select_full_join' => sub { print query_database($queries{select_full_join}) },
'select_full_range_join' => sub { print query_database($queries{select_full_range_join}) },
'select_range' => sub { print query_database($queries{select_range}) },
'select_range_check' => sub { print query_database($queries{select_range_check}) },
'select_scan' => sub { print query_database($queries{select_scan}) },
'slave_open_temp_tables' => sub { print query_database($queries{slave_open_temp_tables}) },
'slave_retried_transactions' => sub { print query_database($queries{slave_retried_transactions}) },
'slave_running' => sub { print query_database($queries{slave_running}) },
'slow_launch_threads' => sub { print query_database($queries{slow_launch_threads}) },
'slow_queries' => sub { print query_database($queries{slow_queries}) },
'sort_merge_passes' => sub { print query_database($queries{sort_merge_passes}) },
'sort_range' => sub { print query_database($queries{sort_range}) },
'sort_rows' => sub { print query_database($queries{sort_rows}) },
'sort_scan' => sub { print query_database($queries{sort_scan}) },
'table_locks_immediate' => sub { print query_database($queries{table_locks_immediate}) },
'table_locks_waited' => sub { print query_database($queries{table_locks_waited}) },
'tc_log_max_pages_used' => sub { print query_database($queries{tc_log_max_pages_used}) },
'tc_log_page_size' => sub { print query_database($queries{tc_log_page_size}) },
'tc_log_page_waits' => sub { print query_database($queries{tc_log_page_waits}) },
'threads_cached' => sub { print query_database($queries{threads_cached}) },
'threads_connected' => sub { print query_database($queries{threads_connected}) },
'threads_created' => sub { print query_database($queries{threads_created}) },
'threads_running' => sub { print query_database($queries{threads_running}) },
'uptime' => sub { print query_database($queries{uptime}) }
);
sub query_database {
my $query = shift(@_);
my $dbh = DBI->connect("dbi:mysql:dbname=$database; host=$host", $user, $password);
my $sth = $dbh->prepare($query) or die $|;
$sth->execute;
while (my @array = $sth->fetchrow_array) {
return @array[1];
}
$sth->disconnect
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment