收集MySQL测试数据脚本

收集数据脚本

先在当前目录创建一个名为running的空文件，不然脚本无法启动，如果想停止脚本，删除running文件即可

#!/bin/bash
INTERVAL=5
PREFIX=$INTERVAL-sec-status
RUNFILE=running
mysql -u root -pwangxj.123 -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 -u root -pwangxj.123 -e 'show global status' >> $PREFIX-${file}-status &
	echo "$ts $loadavg" >> $PREFIX-${file}-innodbstatus
	mysql -u root -pwangxj.123 -e 'SHOW ENGINE INNODB STATUS\G' >> $PREFIX-${file}-innodbstatus &
	echo "$ts $loadavg" >> $PREFIX-${file}-processlist
	mysql -u root -pwangxj.123 -e 'SHOW FULL PROCESSLIST\G' >> $PREFIX-${file}-processlist &
	echo $ts
done
echo Exiting becuse $RUNFILE does not exist.

分析QPS脚本

# !/bin/bash
awk '
	BEGIN {
		printf "#ts date time load QPS";
		fmt = " %.2f";
	}
	/^TS/ { # The timvestamp 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", ts, $3, $4, substr($load, 1, length($load)-1)
	}
	/Queries/ {
		printf fmt, ($2-Queries)/diff;
		Queries=$2
	}
' "$@"

给该脚本可执行权限，

chmod a+x script.sh

执行该脚本分析上一脚本生成的文件

./script.sh 5-sec-status-2019-04-25_03-status