mysql相关指标监控

#! /bin/bash
# *******作者：        payne                             
# *******创建时间：  2019/11/21                           
# *******功能：     用于监控mysql相关状态          
# *******版本：			v1.0						
# *******说明：仅在mysql5.7上测试通过，其他版本有的状态变量可能不存在
# 监控指标：
#   1、Query throughput
#   2、Query execution performance 此指标可通过慢查询日志进行监控
#   3、Connections
#   4、Buffer pool usage
# mysql状态变量说明(详见：https://dev.mysql.com/doc/refman/5.7/en/server-status-variables.html)：
# Queries：mysql系统接收的查询的次数，包括存储过程内部的查询
# Questions：mysql系统接收查询的次数，但不包括存储过程内部的查询，与Com_select基本相同，但Com_select表示select语句执行了多少次
# Com_commit：提交语句次数
# Com_rollback：回滚语句次数
# Handler_commit：The number of internal COMMIT statements.
# Com_insert：插入语句次数
# Com_update：更新语句次数
# Com_delete：删除语句次数
# Threads_connected：已连接的线程数
# Threads_running：正运行的线程数
# Aborted_connects：Count of failed connection attempts to the server
# Aborted_clients：The number of connections that were aborted because the client died without closing the connection properly.
# Max_used_connections：The maximum number of connections that have been in use simultaneously since the server started.
# Innodb_buffer_pool_read_requests：The number of logical read requests.
# Innodb_buffer_pool_reads：The number of logical reads that InnoDB could not satisfy from the buffer pool, and had to read directly from disk.
# Innodb_buffer_pool_pages_total：The total size of the InnoDB buffer pool, in pages.
# Innodb_buffer_pool_pages_free：The number of free pages in the InnoDB buffer pool.
# Innodb_buffer_pool_pages_data：The number of pages in the InnoDB buffer pool containing data. The number includes both dirty and clean pages. 

# innodb buffer pool size相关静态变量：
# innodb_buffer_pool_instances：
# innodb_buffer_pool_chunk_size：
# innodb_buffer_pool_size：

trap 'signal_handler' SIGINT

function human_readable()
{
   sec=$1
   dd=0;hh=0;mm=0;ss=0

   if [ $sec -lt 60 -a $sec -gt 0 ]; then
      ss=$sec
      remaining_sec=0
      echo -e "${ss} seconds\c"
   elif [ $sec -lt $((60*60)) ]; then
      mm=$(($sec/60))
      echo -e "${mm} minutes \c"
      remaining_sec=$(($sec%60))
   elif [ $sec -lt $((60*60*24)) ]; then
      hh=$(($sec/(60*60)))
      echo -e "${hh} hours \c"
      remaining_sec=$(($sec%(60*60)))
   else
      dd=$(($sec/(60*60*24)))
      echo -e "${dd} days \c"
      remaining_sec=$(($sec%(60*60*24)))
   fi

   if [ $remaining_sec -gt 0 ]; then
        human_readable ${remaining_sec}
   else
        echo ""
   fi
}

# awk调用shell函数的两种方式：
export -f human_readable
# 1、此方法不能获得字符串返回值，因为shell函数不支持返回字符串
# echo "he" | awk '{res = system("human_readable 60"); print res}' #若human_readable返回值(return)是字符串则报错
# 2、此方法支持获得shell函数的echo结果，相当于返回字符串
# echo "he" | awk '{"human_readable 60" | getline res; print res}'

function signal_handler()
{
   mysql_monitor "mysqladmin extended-status" "awk -v exit_flag=1" 0
}

function mysql_monitor()
{
mysqladmin_command=$1
awk_command=$2
is_print=$3
if [ $is_print -eq 1 ]; then
  echo -e "\033[32mMonitoring mysql status. Ctrl+C to exit...\033[0m"
fi

$mysqladmin_command | $awk_command '
BEGIN{
     count = 0;
     first_run = 1;
}
# Query相关
$2 ~ /Queries$/            {queries = $4 - last_queries; last_queries = $4;}
$2 ~ /Com_commit$/         {commits = $4 - last_commits; last_commits = $4;}
$2 ~ /Com_rollback$/       {rollbacks = $4 - last_rollbacks; last_rollbacks = $4;}
$2 ~ /Com_insert$/         {inserts = $4 - last_inserts; last_inserts = $4;}
$2 ~ /Com_update$/         {updates = $4 - last_updates; last_updates = $4;}
$2 ~ /Com_delete$/         {deletes = $4 - last_deletes; last_deletes = $4;}
# connection相关
$2 ~ /Aborted_connects$/		{aborted_connects = $4}
$2 ~ /Max_used_connections$/		{max_used_connections = $4}
$2 ~ /Threads_connected$/  {threads_connected = $4;}
$2 ~ /Threads_running$/    {threads_running = $4;}
# Innodb相关
$2 ~ /Innodb_buffer_pool_read_requests$/	{logical_reads = $4;}
$2 ~ /Innodb_buffer_pool_reads$/	{physical_reads = $4;}
$2 ~ /Innodb_buffer_pool_pages_total$/	{total_pages = $4;}
$2 ~ /Innodb_buffer_pool_pages_free$/	{free_pages = $4;}
$2 ~ /Uptime$/		{
  if(count > 10 || first_run == 1 || exit_flag == 1)
  {
    printf("\n");
    "human_readable "$4 | getline uptime;  # 调用shell的human_readable函数，并将结果存入至uptime变量
    printf("Uptime: %s\n", uptime);
    printf("Queries: %d Inserts: %d Updates: %d Commits: %d Rollbacks: %d\n", 
       last_queries, last_inserts, last_updates, last_deletes, last_commits, last_rollbacks);
    printf("Threads_connected: %d Threads_running: %d Max_used_connections: %d Aborted_connects: %d\n",
       threads_connected, threads_running, max_used_connections, aborted_connects);
    printf("Logical_reads: %d Physical_reads: %d Innodb_buffer_total_pages: %d Innodb_buffer_free_pages: %d\n",
       logical_reads, physical_reads, total_pages, free_pages);
    printf("\n");
    if(exit_flag == 1){exit;}
    
    if(count > 10 || first_run == 1) 
    {
        first_run = 0;
        count = 0;
        printf("%6s %12s %12s %12s %12s %12s %12s %6s %12s %12s\n", 
           "QPS", "Insert/s", "Updates/s", "Deletes/s", "Writes/s", "Commits/s", "Rollbacks/s", "TPS", "IBufUsedRate(%)", "IBufHitRate(%)");
        printf("--------------------------------------------------------------------------------------------------------------------------\n");
    }
		 				
  } else {
    count += 1;
    printf("%6d %12d %12d %12d %12d %12d %12d %6d %10.2f %12.2f\n", queries, inserts, updates, deletes, inserts + updates + deletes, 
      commits, rollbacks, commits + rollbacks, (total_pages - free_pages) * 100 / total_pages, (logical_reads - physical_reads) * 100 / logical_reads);
  }

}'
}

# 每秒监控输出
mysql_monitor "mysqladmin extended-status -i1" "awk" 1

结果：