综合实践(1#3)一个shell收集性能数据存入mysql并由grafana展示
自动化实践系列一1/3：一个自编监控服务器基础性能的实例
面向对象: 学习过LINUX基本命令、mysql
实验环境: centos7/mysql/
目的： 综合检验和提高学生对之前学习过的LINUX基础命令、SHELL、MYSQL等知识点的掌握情况，完成一个小功能；
步骤:
1) 使用shell提取服务器基础性能数据;
2) 存入mysql数据库;
3) 使用crontab定时运行;
4) 使用grafana来图形显示;
一、使用shell提取服务器基础性能数据;
包括： CPU使用率、内存使用率、网络带宽使用情况、磁盘IO使用率、根分区的使用率、开放的端口及其运行的程序；
此外，用DATE命令获得时间戳，以便用来画时序图；
#cpu剩余率% idle
top -bn1 |grep Cpu|awk '{print $8}'
#内存：total, used 单位KB
top -bn1 |grep 'Mem :'|awk '{print $4,$8}'
#开放的端口: 协议、IP、Port、PID、procname
netstat -nlpt4|awk 'NR>2{print $1"," $4","$7}'|sed -e 's/\//,/g' -e 's/:/,/g'
#网络进出带宽, RX, TX, 单位字节B
eth=eth0
RXpre=$(ifconfig $eth| grep bytes |grep RX | awk '{print $5}')
TXpre=$(ifconfig $eth| grep bytes |grep TX | awk '{print $5}')
sleep 1
RXnext=$(ifconfig $eth| grep bytes |grep RX | awk '{print $5}')
TXnext=$(ifconfig $eth| grep bytes |grep TX | awk '{print $5}')
RX=$((${RXnext}-${RXpre}))
TX=$((${TXnext}-${TXpre}))
echo "$RX $TX"
#根分区的使用率 used
df -h|awk 'NR==2{print $5}'|sed 's/%//'
#磁盘IO：sda硬盘的等待时间和利用率：await, util%
iostat -x|grep sda|awk '{print $10,$14}'
#DATE时间戳timestamp
date +"%Y-%m-%d %H:%M:%S"
二、 存入mysql数据库
#建数据库和表后，将数据插入数据库
sql1="LOAD DATA LOCAL INFILE '/tmp/netports.csv' INTO TABLE ports CHARACTER SET utf8 FIELDS TERMINATED BY ',' (timestamp, ip,protocol, ipl, port, pid, procname);"
sql2="insert into basemon(timestamp, ip, cpuidle,memtotal,memused,rx,tx,diskrootrate,ioawait,ioutil) values(\"$TIMESTAMP\",\"$IP\",$CPUIDLE, $MEMTOTAL, $MEMUSED, $RX, $TX, $DISKROOTRATE, $IOAWAIT, $IOUTIL)"
sql3="delete from ports"
echo $sql3|mysql -uroot -D monitor
echo $sql1|mysql -uroot -D monitor
echo $sql2|mysql -uroot -D monitor
三、使用crontab定时运行
echo "*/5 * * * * /usr/bin/bash /root/collect.sh >/dev/null 2>&1" >>/var/spool/cron/root
四、使用grafana来图形显示;
#安装grafana
wget https://s3-us-west-2.amazonaws.com/grafana-releases/release/grafana-5.0.0-1.x86_64.rpm
sudo yum localinstall grafana-5.0.0-1.x86_64.rpm
grafana-cli plugins install grafana-piechart-panel
#启动
systemctl start grafana-server
#界面：
http://192.168.31.222:3000/login
默认帐号/密码：admin/admin
增加data source/dashboard/panel/, 使用时序图、饼图、表格来做仪表盘；
metrics设置SQL示例：
#sql: cpu util%
SELECT
UNIX_TIMESTAMP(timestamp) DIV 10 * 10 as time_sec,
round(100-cpuidle,1) as value,
ip as metric
FROM basemon
where ip=$myip
group by UNIX_TIMESTAMP(timestamp) DIV 300
ORDER BY timestamp ASC
#sql: cpu util%
SELECT
UNIX_TIMESTAMP(timestamp) DIV 10 * 10 as time_sec,
round(100-cpuidle,1) as value,
ip as metric
FROM basemon
where ip=$myip
group by UNIX_TIMESTAMP(timestamp) DIV 300
ORDER BY timestamp ASC
#total memory
SELECT
UNIX_TIMESTAMP(timestamp) DIV 10 * 10 as time_sec,
memtotal as value,
'total' as metric
FROM basemon
where ip=$myip
group by UNIX_TIMESTAMP(timestamp) DIV 300
ORDER BY timestamp ASC
#used memory
SELECT
UNIX_TIMESTAMP(timestamp) DIV 10 * 10 as time_sec,
memused as value,
'used' as metric
FROM basemon
where ip=$myip
group by UNIX_TIMESTAMP(timestamp) DIV 300
ORDER BY timestamp ASC
附录：
#!/usr/bin/bash
#collect.sh
##收集服务器基础性能数据
#DATE时间戳
TIMESTAMP=`date '+%Y-%m-%d %H:%M:%S'`
#IP
IP=`/usr/sbin/ifconfig eth0 |grep "inet "|awk '{print $2}'`
#CPUidle%
CPUIDLE=`top -bn1 |grep Cpu|awk '{print $8}'`
#内存：total, used 单位KB
memory=(`top -bn1 |grep 'Mem :'|awk '{print $4,$8}'`)
MEMTOTAL=${memory[0]}
MEMUSED=${memory[1]}
#网络进出带宽, RX, TX, 单位字节B
eth=eth0
netpre=(`/usr/sbin/ifconfig eth0| grep bytes|awk '{print $5}'`)
sleep 1
netnext=(`/usr/sbin/ifconfig eth0| grep bytes|awk '{print $5}'`)
RX=$((${netnext[0]}-${netpre[0]}))
TX=$((${netnext[1]}-${netpre[1]}))
#根分区的使用率 used
DISKROOTRATE=`df -h|awk 'NR==2{print $5}'|sed 's/%//'`
#磁盘IO：sda硬盘的等待时间和利用率：await, util%
io=(`iostat -x|grep sda|awk '{print $10,$14}'`)
IOAWAIT=${io[0]}
IOUTIL=${io[1]}
#开放的端口: 协议、IP、Port、PID、procname
NETPORTS=(`netstat -nlpt4|awk 'NR>2{print $1"," $4","$7}'|sed -e 's/\//,/g' -e 's/:/,/g'`)
#echo "$TIMESTAMP $IP $CPUIDLE $MEMTOTAL $MEMUSED $RX $TX $DISKROOTRATE $IOAWAIT $IOUTIL"
echo -n>/tmp/netports.csv
rows=${#NETPORTS[@]}
for ((i=1;i<$rows;i++));
do
echo "$TIMESTAMP,$IP,${NETPORTS[$i]}" >>/tmp/netports.csv
done
##建数据库和表后，将数据插入数据库
sql1="LOAD DATA LOCAL INFILE '/tmp/netports.csv' INTO TABLE ports CHARACTER SET utf8 FIELDS TERMINATED BY ',' (timestamp, ip,protocol, ipl, port, pid, procname);"
sql2="insert into basemon(timestamp, ip, cpuidle,memtotal,memused,rx,tx,diskrootrate,ioawait,ioutil) values(\"$TIMESTAMP\",\"$IP\",$CPUIDLE, $MEMTOTAL, $MEMUSED, $RX, $TX, $DISKROOTRATE, $IOAWAIT, $IOUTIL)"
sql3="delete from ports"
echo $sql3|mysql -uroot -D monitor
echo $sql1|mysql -uroot -D monitor
echo $sql2|mysql -uroot -D monitor
参见：https://app.yinxiang.com/fx/c71ee206-f6e9-46d6-8d7b-23402e8253d0
