收集mysql负载shell脚本

#!/bin/sh
INTERVAL=5
PREFIX=$INTERVAL-sec-status
RUNFILE=/root/1.txt
mysql -uroot -p123456 -e 'SHOW GLOBAL VARIABLES' >> mysql-variables
while test -e $RUNFILE; do
 file=$(date +%F_%I)
 sleep=$(date +%s.%N | awk "{print $INTERVAL - (\$1 % $INTERVAL)}")
 sleep $sleep
 ts="$(date +"TS %s.$N %F %T")"
 loadavg="$(uptime)"
 echo "$ts $loadavg" >> $PREFIX-${file}-status
 mysql -uroot -p123456 -e 'SHOW GLOBAL STATUS' >> $PREFIX-${file}-status &
 echo "$ts $loadavg" >> $PREFIX-${file}-innodbstatus
 mysql -uroot -p123456 -e 'SHOW ENGINE INNODB STATUS\G' >> $PREFIX-${file}-innodbstatus &
 echo "$ts $loadavg" >> $PREFIX-${file}-processlist
 mysql -uroot -p123456 -e 'SHOW FULL PROCESSLIST\G' >> $PREFIX-${file}-processlist &
 echo $ts
done
echo Exiting because $RUNFILE does not exist.

查看负载脚本：

#!/bin/sh
awk '
 BEGIN{
        printf "#ts date time load QPS";
        fmt = " %.2f";
        }
 /^TS/{# The timestamp lines begin with TS.
  ts    =substr($2, 1, index($2,".")-1);
  load  =NF -2;
  diff  =ts - prev_ts;
  prev_ts = ts;
  printf "\n%s %s %s %s",ts,$3,$4,substr($load, 1, length($load)-1);
}
/Queries/{
        printf fmt,($2-Queries)/diff;
        Queries=$2
}
' "$@"

如果想看画图，可以使用linux上的绘图工具yum -y install gnuploat*

gnuplot> plot "1.txt" using 5 w lines title "QPS"