#!/bin/sh

# ----------------------------------------------------
# Description : mysql_monitor.sh
#             : show status for zabbix monitor
# ----------------------------------------------------

#define
SHELLDIR=/usr/local/shell

#log file is MySQL show status
NEWFILE="${SHELLDIR}/showstatus.log"
OLDFILE="${SHELLDIR}/showstatus.log.1"

#csv file is zabbix monitoring
RESULTFILE="${SHELLDIR}/result.csv"
ROTATEFILE="${SHELLDIR}/result.csv.1"

#database account
ACCOUNT=zabbix

#database password
PASSWD=zabbix!

#cron interval
CRONINTERVAL=300


# current dir set
cd ${SHELLDIR}

# ----------------------------------------------------
#get the newdata from mysql
if [ -e ${NEWFILE} ]; then
  if [ -e ${OLDFILE} ]; then
    RUNSTATUS=2
  else
    RUNSTATUS=1
  fi
else
  RUNSTATUS=0
fi

# ----------------------------------------------------
#set data from mysql
if [ -e ${NEWFILE} ]; then
  if [ -e ${OLDFILE} ]; then
    rm -f ${OLDFILE}
  fi
  mv -f ${NEWFILE} ${OLDFILE}
fi

echo "show status;"| mysql -u ${ACCOUNT} -p${PASSWD}| awk '{print $1 "," $2}' > ${NEWFILE}

# ----------------------------------------------------
#csvfile set of data
if [ -e ${RESULTFILE} ]; then
  if [ -e ${ROTATEFILE} ]; then
    rm -f ${ROTATEFILE}
  fi
  mv -f ${RESULTFILE} ${ROTATEFILE}
fi
#initialize
touch ${RESULTFILE}

if [ ${RUNSTATUS} -ne 2  ]; then
#dmy out
  exit
fi

# ----------------------------------------------------
#get newdata from mysql
if [ ${RUNSTATUS} -gt 0  ]; then
  Qcache_free_blocks=`grep 'Qcache_free_blocks,' ${NEWFILE}|cut -d"," -f2`
  Qcache_free_memory=`grep 'Qcache_free_memory,' ${NEWFILE}|cut -d"," -f2`
  Qcache_hits=`grep 'Qcache_hits,' ${NEWFILE}|cut -d"," -f2`
  Qcache_inserts=`grep 'Qcache_inserts,' ${NEWFILE}|cut -d"," -f2`
  Qcache_lowmem_prunes=`grep 'Qcache_lowmem_prunes,' ${NEWFILE}|cut -d"," -f2`
  Qcache_queries_in_cache=`grep 'Qcache_queries_in_cache,' ${NEWFILE}|cut -d"," -f2`

  Open_tables=`grep 'Open_tables,' ${NEWFILE}|cut -d"," -f2`
  Opened_tables=`grep 'Opened_tables,' ${NEWFILE}|cut -d"," -f2`

  Threads_connected=`grep 'Threads_connected,' ${NEWFILE}|cut -d"," -f2`
  Threads_cached=`grep 'Threads_cached,' ${NEWFILE}|cut -d"," -f2`
  Threads_running=`grep 'Threads_running,' ${NEWFILE}|cut -d"," -f2`
  Threads_created=`grep 'Threads_created,' ${NEWFILE}|cut -d"," -f2`

  Key_read_requests=`grep 'Key_read_requests,' ${NEWFILE}|cut -d"," -f2`
  Key_reads=`grep 'Key_reads,' ${NEWFILE}|cut -d"," -f2`

  Created_tmp_disk_tables=`grep 'Created_tmp_disk_tables,' ${NEWFILE}|cut -d"," -f2`
  Created_tmp_files=`grep 'Created_tmp_files,' ${NEWFILE}|cut -d"," -f2`
  Created_tmp_tables=`grep 'Created_tmp_tables,' ${NEWFILE}|cut -d"," -f2`

  Sort_merge_passes=`grep 'Sort_merge_passes,' ${NEWFILE}|cut -d"," -f2`
  Sort_range=`grep 'Sort_range,' ${NEWFILE}|cut -d"," -f2`
  Sort_rows=`grep 'Sort_rows,' ${NEWFILE}|cut -d"," -f2`
  Sort_scan=`grep 'Sort_scan,' ${NEWFILE}|cut -d"," -f2`

  Handler_read_rnd_next=`grep 'Handler_read_rnd_next,' ${NEWFILE}|cut -d"," -f2`
  Com_select=`grep 'Com_select,' ${NEWFILE}|cut -d"," -f2`
fi

#get olddata from mysql
if [ ${RUNSTATUS} -eq 2  ]; then
  Qcache_lowmem_prunes_old=`grep 'Qcache_lowmem_prunes,' ${OLDFILE}|cut -d"," -f2`

  Opened_tables_old=`grep 'Opened_tables,' ${OLDFILE}|cut -d"," -f2`

  Threads_created_old=`grep 'Threads_created,' ${OLDFILE}|cut -d"," -f2`
fi

# ----------------------------------------------------
#Qcache infomation
echo ${Qcache_free_blocks} | awk '{print "Qcache_free_blocks," $1}' >> ${RESULTFILE}
echo ${Qcache_free_memory} | awk '{print "Qcache_free_memory," $1}' >> ${RESULTFILE}
echo ${Qcache_hits} | awk '{print "Qcache_hits," $1}' >> ${RESULTFILE}
echo ${Qcache_inserts} | awk '{print "Qcache_inserts," $1}' >> ${RESULTFILE}
echo ${Qcache_not_cached} | awk '{print "Qcache_not_cached," $1}' >> ${RESULTFILE}
echo ${Qcache_lowmem_prunes} | awk '{print "Qcache_lowmem_prunes," $1}' >> ${RESULTFILE}
echo ${Qcache_queries_in_cache} | awk '{print "Qcache_queries_in_cache," $1}' >> ${RESULTFILE}


Qcache=`echo ${Qcache_hits} ${Qcache_inserts} ${Qcache_not_cached}|awk '{print $1 + $2 + $3}'`
if [ ${Qcache} -eq 0 ]; then
  echo "CAL_Qcache_hit_rate,0" >> ${RESULTFILE}
else
  echo ${Qcache_hits} ${Qcache} | awk '{printf("CAL_Qcache_hit_rate,%.2f\n",$1 / $2 * 100)}' >> ${RESULTFILE}
fi
echo ${Qcache_lowmem_prunes} ${Qcache_lowmem_prunes_old} | awk '{print "CAL_Qcache_lowmem_prunes," $1 - $2}' >> ${RESULTFILE}

# ----------------------------------------------------
#buffer & cache
echo ${Open_tables} | awk '{print "Open_tables," $1}' >> ${RESULTFILE}
echo ${Opened_tables} ${Opened_tables_old} | awk '{print "CAL_Opened_tables," $1 - $2}' >> ${RESULTFILE}

echo ${Threads_connected} | awk '{print "Threads_connected," $1}' >> ${RESULTFILE}
echo ${Threads_cached} | awk '{print "Threads_cached," $1}' >> ${RESULTFILE}
echo ${Threads_running} | awk '{print "Threads_running," $1}' >> ${RESULTFILE}
echo ${Threads_created} ${Threads_created_old} | awk '{print "CAL_Threads_created," $1 - $2}' >> ${RESULTFILE}

if [ ${Key_read_requests} -eq 0 ]; then
  echo "CAL_Key_Efficiency,0" >> ${RESULTFILE}
else
  echo ${Key_reads} ${Key_read_requests} | awk '{printf("CAL_Key_Efficiency,%.2f\n",100 - $1 / $2 * 100)}' >> ${RESULTFILE}
fi

echo ${Created_tmp_disk_tables} | awk '{print "Created_tmp_disk_tables," $1}' >> ${RESULTFILE}
echo ${Created_tmp_files} | awk '{print "Created_tmp_files," $1}' >> ${RESULTFILE}
echo ${Created_tmp_tables} | awk '{print "Created_tmp_tables," $1}' >> ${RESULTFILE}

echo ${Sort_merge_passes} | awk '{print "Sort_merge_passes," $1}' >> ${RESULTFILE}
echo ${Sort_range} | awk '{print "Sort_range," $1}' >> ${RESULTFILE}
echo ${Sort_rows} | awk '{print "Sort_rows," $1}' >> ${RESULTFILE}
echo ${Sort_scan} | awk '{print "Sort_scan," $1}' >> ${RESULTFILE}

if [ ${Com_select} -eq 0 ]; then
  echo "CAL_Table_Scan,0" >> ${RESULTFILE}
else
  echo ${Handler_read_rnd_next} ${Com_select} | awk '{printf("CAL_Table_Scan,%.2f\n",100 - $1 / $2 * 100)}' >> ${RESULTFILE}
fi

#debug
echo "saved time," `date '+%D %T'` >> ${RESULTFILE}
[root@pits104 shell]#
[root@pits104 shell]# cat /showstatus.log
cat: /showstatus.log: そのようなファイルやディレクトリはありません
[root@pits104 shell]# cat showstatus.log
Variable_name,Value
Aborted_clients,440
Aborted_connects,25
Binlog_cache_disk_use,0
Binlog_cache_use,0
Bytes_received,130
Bytes_sent,162
Com_admin_commands,0
Com_alter_db,0
Com_alter_table,0
Com_analyze,0
Com_backup_table,0
Com_begin,0
Com_call_procedure,0
Com_change_db,0
Com_change_master,0
Com_check,0
Com_checksum,0
Com_commit,0
Com_create_db,0
Com_create_function,0
Com_create_index,0
Com_create_table,0
Com_create_user,0
Com_dealloc_sql,0
Com_delete,0
Com_delete_multi,0
Com_do,0
Com_drop_db,0
Com_drop_function,0
Com_drop_index,0
Com_drop_table,0
Com_drop_user,0
Com_execute_sql,0
Com_flush,0
Com_grant,0
Com_ha_close,0
Com_ha_open,0
Com_ha_read,0
Com_help,0
Com_insert,0
Com_insert_select,0
Com_kill,0
Com_load,0
Com_load_master_data,0
Com_load_master_table,0
Com_lock_tables,0
Com_optimize,0
Com_preload_keys,0
Com_prepare_sql,0
Com_purge,0
Com_purge_before_date,0
Com_rename_table,0
Com_repair,0
Com_replace,0
Com_replace_select,0
Com_reset,0
Com_restore_table,0
Com_revoke,0
Com_revoke_all,0
Com_rollback,0
Com_savepoint,0
Com_select,1
Com_set_option,1
Com_show_binlog_events,0
Com_show_binlogs,0
Com_show_charsets,0
Com_show_collations,0
Com_show_column_types,0
Com_show_create_db,0
Com_show_create_table,0
Com_show_databases,0
Com_show_errors,0
Com_show_fields,0
Com_show_grants,0
Com_show_innodb_status,0
Com_show_keys,0
Com_show_logs,0
Com_show_master_status,0
Com_show_ndb_status,0
Com_show_new_master,0
Com_show_open_tables,0
Com_show_privileges,0
Com_show_processlist,0
Com_show_slave_hosts,0
Com_show_slave_status,0
Com_show_status,1
Com_show_storage_engines,0
Com_show_tables,0
Com_show_triggers,0
Com_show_variables,0
Com_show_warnings,0
Com_slave_start,0
Com_slave_stop,0
Com_stmt_close,0
Com_stmt_execute,0
Com_stmt_fetch,0
Com_stmt_prepare,0
Com_stmt_reset,0
Com_stmt_send_long_data,0
Com_truncate,0
Com_unlock_tables,0
Com_update,0
Com_update_multi,0
Com_xa_commit,0
Com_xa_end,0
Com_xa_prepare,0
Com_xa_recover,0
Com_xa_rollback,0
Com_xa_start,0
Compression,OFF
Connections,329677
Created_tmp_disk_tables,0
Created_tmp_files,5
Created_tmp_tables,1
Delayed_errors,0
Delayed_insert_threads,0
Delayed_writes,0
Flush_commands,1
Handler_commit,0
Handler_delete,0
Handler_discover,0
Handler_prepare,0
Handler_read_first,0
Handler_read_key,0
Handler_read_next,0
Handler_read_prev,0
Handler_read_rnd,0
Handler_read_rnd_next,0
Handler_rollback,0
Handler_savepoint,0
Handler_savepoint_rollback,0
Handler_update,0
Handler_write,132
Innodb_buffer_pool_pages_data,18865
Innodb_buffer_pool_pages_dirty,24
Innodb_buffer_pool_pages_flushed,3702157
Innodb_buffer_pool_pages_free,46146
Innodb_buffer_pool_pages_latched,0
Innodb_buffer_pool_pages_misc,525
Innodb_buffer_pool_pages_total,65536
Innodb_buffer_pool_read_ahead_rnd,15
Innodb_buffer_pool_read_ahead_seq,122
Innodb_buffer_pool_read_requests,855526183
Innodb_buffer_pool_reads,6494
Innodb_buffer_pool_wait_free,0
Innodb_buffer_pool_write_requests,42653033
Innodb_data_fsyncs,3183292
Innodb_data_pending_fsyncs,0
Innodb_data_pending_reads,0
Innodb_data_pending_writes,0
Innodb_data_read,223973376
Innodb_data_reads,7021
Innodb_data_writes,5112948
Innodb_data_written,3883636736
Innodb_dblwr_pages_written,3702157
Innodb_dblwr_writes,226588
Innodb_log_waits,0
Innodb_log_write_requests,3857366
Innodb_log_writes,1713905
Innodb_os_log_fsyncs,1892675
Innodb_os_log_pending_fsyncs,0
Innodb_os_log_pending_writes,0
Innodb_os_log_written,2739057664
Innodb_page_size,16384
Innodb_pages_created,5328
Innodb_pages_read,13537
Innodb_pages_written,3702157
Innodb_row_lock_current_waits,0
Innodb_row_lock_time,162
Innodb_row_lock_time_avg,2
Innodb_row_lock_time_max,72
Innodb_row_lock_waits,77
Innodb_rows_deleted,2187189
Innodb_rows_inserted,1699440
Innodb_rows_read,212388586
Innodb_rows_updated,4434665
Key_blocks_not_flushed,0
Key_blocks_unused,14492
Key_blocks_used,9
Key_read_requests,4837901
Key_reads,400
Key_write_requests,12291
Key_writes,0
Last_query_cost,0.000000
Max_used_connections,30
Not_flushed_delayed_rows,0
Open_files,46
Open_streams,0
Open_tables,246
Opened_tables,0
Prepared_stmt_count,0
Qcache_free_blocks,0
Qcache_free_memory,0
Qcache_hits,0
Qcache_inserts,0
Qcache_lowmem_prunes,0
Qcache_not_cached,0
Qcache_queries_in_cache,0
Qcache_total_blocks,0
Questions,15844323
Rpl_status,NULL
Select_full_join,0
Select_full_range_join,0
Select_range,0
Select_range_check,0
Select_scan,1
Slave_open_temp_tables,0
Slave_retried_transactions,0
Slave_running,OFF
Slow_launch_threads,0
Slow_queries,0
Sort_merge_passes,0
Sort_range,0
Sort_rows,0
Sort_scan,0
Ssl_accept_renegotiates,0
Ssl_accepts,0
Ssl_callback_cache_hits,0
Ssl_cipher,
Ssl_cipher_list,
Ssl_client_connects,0
Ssl_connect_renegotiates,0
Ssl_ctx_verify_depth,0
Ssl_ctx_verify_mode,0
Ssl_default_timeout,0
Ssl_finished_accepts,0
Ssl_finished_connects,0
Ssl_session_cache_hits,0
Ssl_session_cache_misses,0
Ssl_session_cache_mode,NONE
Ssl_session_cache_overflows,0
Ssl_session_cache_size,0
Ssl_session_cache_timeouts,0
Ssl_sessions_reused,0
Ssl_used_session_cache_entries,0
Ssl_verify_depth,0
Ssl_verify_mode,0
Ssl_version,
Table_locks_immediate,19856242
Table_locks_waited,0
Tc_log_max_pages_used,0
Tc_log_page_size,0
Tc_log_page_waits,0
Threads_cached,13
Threads_connected,17
Threads_created,30
Threads_running,1
Uptime,1915092
Uptime_since_flush_status,1915092
[root@pits104 shell]# cat result.csv
Qcache_free_blocks,0
Qcache_free_memory,0
Qcache_hits,0
Qcache_inserts,0
Qcache_not_cached,
Qcache_lowmem_prunes,0
Qcache_queries_in_cache,0
CAL_Qcache_hit_rate,0
CAL_Qcache_lowmem_prunes,0
Open_tables,246
CAL_Opened_tables,0
Threads_connected,17
Threads_cached,13
Threads_running,1
CAL_Threads_created,0
CAL_Key_Efficiency,99.99
Created_tmp_disk_tables,0
Created_tmp_files,5
Created_tmp_tables,1
Sort_merge_passes,0
Sort_range,0
Sort_rows,0
Sort_scan,0
CAL_Table_Scan,100.00
saved time, 03/04/14 19:15:01