[root@saas-pro-bg b]# cat test.sh
#!/bin/bash
MYSQL_HOST="1.1.1.1"
MYSQL_PORT="3306"
MYSQL_USER="psaas"
MYSQL_PASS="xxxxxxx"
SLEEP_TIME=5
JOB="saas"
LABEL="MYSQL集群"
INSTANCE="1.1.1.1"
CLOUD="alibabacloud"
ACCOUNT="account@gmail.com"
PUSHGATEWAY="http://47.57.142.166:9091"
# 定义日志文件
function Log()
{
msg=$1
#logType=$2
logFile="./MySQLStatusToMonitor.log"
echo "["`date +"%Y-%m-%d %X"`"] $msg" >> $logFile
}
Log "***********************************"
# 判断是否mysql执行命令
MYSQL_BIN=`which mysql`
if [[ $? != 0 ]];then
Log "Error: mysql client command not found,Please install mysqll client"
echo "Error: mysql client command not found,Please install mysqll client"
exit 1
fi
if ! which nc >/dev/null 2>&1 ;then
Log "Error: nc scan command not found, Please install nc command"
echo "Error: nc scan command not found, Please install nc command"
exit 1
fi
# 数据库是否允许
IS_ACTIVE_CMD=$(nc -w 3 -z $MYSQL_HOST $MYSQL_PORT)
IS_ACTIVE=1
if [[ $? != 0 ]];then
IS_ACTIVE=0
fi
# 连接数据库
MYSQL_CONN="mysql -h$MYSQL_HOST -P$MYSQL_PORT -u$MYSQL_USER -p$MYSQL_PASS"
# 采集
OLD_MYSQL_STATUS=`$MYSQL_CONN -e "show status;"|grep -Ew "Com_select|Com_insert|Com_update|Com_delete"`
echo $MYSQL_STATUS
#记录相关参数
#AbConnections=`echo "$NEEDEDMYSQLSTATUS"|awk '/Aborted_connects/{print $2}'`
#InnodbRLCW=`echo "$NEEDEDMYSQLSTATUS"|awk '/Innodb_row_lock_current_waits/{print $2}'`
#MYSQLUptime=`echo "$NEEDEDMYSQLSTATUS"|awk '/Uptime/{print $2}'`
#Log "uploading Aborted_connects:$AbConnections,Innodb_row_lock_current_waits:$InnodbRLCW,MySQL_uptime:$MYSQLUptime."
old_com_select=`echo "$OLD_MYSQL_STATUS" |awk '/Com_select/{print $2}'`
old_com_insert=`echo "$OLD_MYSQL_STATUS" |awk '/Com_insert/{print $2}'`
old_com_update=`echo "$OLD_MYSQL_STATUS" |awk '/Com_update/{print $2}'`
old_com_delete=`echo "$OLD_MYSQL_STATUS" |awk '/Com_delete/{print $2}'`
sleep $SLEEP_TIME
MYSQL_STATUS=`$MYSQL_CONN -e "show status;"|grep -Ew "Aborted_connects|Aborted_clients|Connection_errors_internal|Connection_errors_max_connections|Innodb_row_lock_current_waits|Uptime|Key_reads|Key_read_requests|Key_writes|Key_write_requests|Com_select|Com_insert|Com_update|Com_delete|Innodb_buffer_pool_reads|Innodb_buffer_pool_read_requests|Qcache_hits|Qcache_inserts"`
new_com_select=`echo "$MYSQL_STATUS" |awk '/Com_select/{print $2}'`
new_com_insert=`echo "$MYSQL_STATUS" |awk '/Com_insert/{print $2}'`
new_com_update=`echo "$MYSQL_STATUS" |awk '/Com_update/{print $2}'`
new_com_delete=`echo "$MYSQL_STATUS" |awk '/Com_delete/{print $2}'`
# 运行时间
mysql_global_uptime=`echo "$MYSQL_STATUS" |awk '/Uptime/{print $2}'`
# 获取数据库版本
mysql_global_version=`$MYSQL_CONN -e "select version();"|sed -n 2p`
# 最大链接数
mysql_global_max_connection=`$MYSQL_CONN -e "show variables like 'max_connections';" |awk '/max_connections/{print $2}'`
# 客户端连接数
processlist=`$MYSQL_CONN -e "show processlist;"|wc -l`
mysql_global_processlist=$((processlist-2))
# 客户端未正确关闭连接而死亡的连接数
mysql_global_aborted_clients=`echo "$MYSQL_STATUS" |awk '/Aborted_clients/{print $2}'`
# 连接MySQL服务器失败的次数
mysql_global_aborted_connects=`echo "$MYSQL_STATUS" |awk '/Aborted_connects/{print $2}'`
# 由于服务器内部错误（例如无法启动新线程或内存不足情况）而被拒绝的连接数
mysql_global_connection_errors_internal=`echo "$MYSQL_STATUS" |awk '/Connection_errors_internal/{print $2}'`
# 由于达到服务器max_connections限制而拒绝的连接数。
mysql_global_connection_errors_max_connections=`echo "$MYSQL_STATUS" |awk '/Connection_errors_max_connections/{print $2}'`
# QPS每秒Query量, 连续获取两次Com_select指标，拿新指标减去老指标后再除于间隔时长的出间隔时间内的每秒平均值
mysql_global_query_qps=`echo "($new_com_select-$old_com_select)/$SLEEP_TIME" | bc`
# TPS(每秒事务量) 指定间隔时间内取值，然后新指标减去老指标后三个指标相加再除以间隔时间得出间隔时间内每秒平均TPS
mysql_global_tps=`echo "((($new_com_insert-$old_com_select)+($new_com_update-$old_com_update)+($new_com_delete-$old_com_delete))/$SLEEP_TIME)"|bc`
# 计算 Key Buffer 命中率
key_reads=`echo "$MYSQL_STATUS" |awk '/Key_reads/{print $2}'`
key_read_requests=`echo "$MYSQL_STATUS" |awk '/Key_read_requests/{print $2}'`
key_writes=`echo "$MYSQL_STATUS" |awk '/Key_writes/{print $2}'`
key_write_requests=`echo "$MYSQL_STATUS" |awk '/Key_write_requests/{print $2}'`
mysql_global_key_buffer_read_hits=0
mysql_global_key_buffer_write_hits=0
if [[ $key_reads != 0 && $key_read_requests != 0 ]];then
mysql_global_key_buffer_read_hits=`echo "scale=4;(1-$key_reads/$key_read_requests)*100"|bc`
fi
if [[ $key_writes != 0 && $key_write_requests != 0 ]];then
mysql_global_key_buffer_write_hits=`echo "scale=4;(1-$key_writes/$key_write_requests)*100"|bc`
fi
# 计算 InnoDB Buffer命中率
innodb_buffer_pool_reads=`echo "$MYSQL_STATUS" |awk '/Innodb_buffer_pool_reads/{print $2}'`
innodb_buffer_pool_read_requests=`echo "$MYSQL_STATUS" |awk '/Innodb_buffer_pool_read_requests/{print $2}'`
mysql_global_Innodb_buffer_read_hits=0
if [[ $key_writes != 0 && $key_write_requests != 0 ]];then
mysql_global_Innodb_buffer_read_hits=`echo "scale=4;(1-$innodb_buffer_pool_reads/$innodb_buffer_pool_read_requests)*100"|bc`
fi
#
# 计算 Query Cache命中率
qcache_hits=`echo "$MYSQL_STATUS" |awk '/Qcache_hits/{print $2}'`
qcache_inserts=`echo "$MYSQL_STATUS" |awk '/Qcache_inserts/{print $2}'`
mysql_global_qcache_cache_hits=0
if [[ $qcache_hits != 0 && $qcache_inserts != 0 ]];then
mysql_global_qcache_cache_hits=`echo "scale=4;(($qcache_hits/($qcache_hits+$qcache_inserts))*100)"|bc`
fi
#echo "mysql_global_version: $mysql_global_version"
#echo "mysql_global_uptime: $mysql_global_uptime"
#echo "mysql_global_processlist: $mysql_global_processlist"
#echo "mysql_global_max_connection: $mysql_global_max_connection"
#echo "mysql_global_aborted_clients: $mysql_global_aborted_clients"
#echo "mysql_global_aborted_connects: $mysql_global_aborted_connects"
#echo "mysql_global_connection_errors_internal: $mysql_global_connection_errors_internal"
#echo "mysql_global_connection_errors_max_connections: $mysql_global_connection_errors_max_connections"
#echo "mysql_global_query_qps: $mysql_global_query_qps"
#echo "mysql_global_tps: $mysql_global_tps"
#echo "mysql_global_key_buffer_read_hits: $mysql_global_key_buffer_read_hits"
#echo "mysql_global_key_buffer_write_hits: $mysql_global_key_buffer_write_hits"
#echo "mysql_global_Innodb_buffer_read_hits: $mysql_global_Innodb_buffer_read_hits"
#echo "mysql_global_qcache_cache_hits: $mysql_global_qcache_cache_hits"
cat > metrics.txt << EOF
mysql_shell_push{job="$JOB",instance="$INSTANCE", label="$LABEL", name="mysql_global_active"} $IS_ACTIVE
mysql_shell_push{job="$JOB",instance="$INSTANCE", label="$LABEL", name="mysql_global_processlist"} $mysql_global_processlist
mysql_shell_push{job="$JOB",instance="$INSTANCE", label="$LABEL", name="mysql_global_max_connection"} $mysql_global_max_connection
mysql_shell_push{job="$JOB",instance="$INSTANCE", label="$LABEL", name="mysql_global_aborted_clients"} $mysql_global_aborted_clients
mysql_shell_push{job="$JOB",instance="$INSTANCE", label="$LABEL", name="mysql_global_connection_errors_internal"} $mysql_global_connection_errors_internal
mysql_shell_push{job="$JOB",instance="$INSTANCE", label="$LABEL", name="mysql_global_connection_errors_max_connections"} $mysql_global_connection_errors_max_connections
mysql_shell_push{job="$JOB",instance="$INSTANCE", label="$LABEL", name="mysql_global_query_qps"} $mysql_global_query_qps
mysql_shell_push{job="$JOB",instance="$INSTANCE", label="$LABEL", name="mysql_global_tps"} $mysql_global_tps
mysql_shell_push{job="$JOB",instance="$INSTANCE", label="$LABEL", name="mysql_global_key_buffer_read_hits"} $mysql_global_key_buffer_read_hits
mysql_shell_push{job="$JOB",instance="$INSTANCE", label="$LABEL", name="mysql_global_key_buffer_write_hits"} $mysql_global_key_buffer_write_hits
mysql_shell_push{job="$JOB",instance="$INSTANCE", label="$LABEL", name="mysql_global_Innodb_buffer_read_hits"} $mysql_global_Innodb_buffer_read_hits
mysql_shell_push{job="$JOB",instance="$INSTANCE", label="$LABEL", name="mysql_global_qcache_cache_hits"} $mysql_global_qcache_cache_hits
EOF
# /metrics/job/{/<LABEL_NAME>/<LABEL_VALUE>}，
curl -XPOST --data-binary @metrics.txt $PUSHGATEWAY/metrics/job/$JOB/instance/$INSTANCE/cloud/$CLOUD/account/$ACCOUNT
Log "***********************************\n"