綜合實(shí)踐(1#3)一個(gè)shell收集性能數(shù)據(jù)存入mysql并由grafana展示
自動(dòng)化實(shí)踐系列一1/3：一個(gè)自編監(jiān)控服務(wù)器基礎(chǔ)性能的實(shí)例
面向?qū)ο??學(xué)習(xí)過(guò)LINUX基本命令、mysql
實(shí)驗(yàn)環(huán)境: centos7/mysql/
目的： 綜合檢驗(yàn)和提高學(xué)生對(duì)之前學(xué)習(xí)過(guò)的LINUX基礎(chǔ)命令、SHELL、MYSQL等知識(shí)點(diǎn)的掌握情況，完成一個(gè)小功能；
步驟:
1) 使用shell提取服務(wù)器基礎(chǔ)性能數(shù)據(jù);
2) 存入mysql數(shù)據(jù)庫(kù);
3) 使用crontab定時(shí)運(yùn)行;
4) 使用grafana來(lái)圖形顯示;
一、使用shell提取服務(wù)器基礎(chǔ)性能數(shù)據(jù);
包括： CPU使用率、內(nèi)存使用率、網(wǎng)絡(luò)帶寬使用情況、磁盤(pán)IO使用率、根分區(qū)的使用率、開(kāi)放的端口及其運(yùn)行的程序；
此外，用DATE命令獲得時(shí)間戳，以便用來(lái)畫(huà)時(shí)序圖；
#cpu剩余率% idle
top -bn1 |grep Cpu|awk?'{print $8}'
#內(nèi)存：total, used 單位KB
top -bn1 |grep 'Mem :'|awk '{print $4,$8}'
#開(kāi)放的端口: 協(xié)議、IP、Port、PID、procname
netstat -nlpt4|awk 'NR>2{print $1"," $4","$7}'|sed -e 's/\//,/g' -e 's/:/,/g'
#網(wǎng)絡(luò)進(jìn)出帶寬, RX, TX, 單位字節(jié)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"
#根分區(qū)的使用率 used
df -h|awk 'NR==2{print $5}'|sed 's/%//'
#磁盤(pán)IO：sda硬盤(pán)的等待時(shí)間和利用率：await, util%
iostat -x|grep sda|awk '{print $10,$14}'
#DATE時(shí)間戳timestamp
date +"%Y-%m-%d %H:%M:%S"
二、 存入mysql數(shù)據(jù)庫(kù)
#建數(shù)據(jù)庫(kù)和表后，將數(shù)據(jù)插入數(shù)據(jù)庫(kù)
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定時(shí)運(yùn)行
echo?"*/5 * * * * /usr/bin/bash /root/collect.sh??>/dev/null 2>&1"?>>/var/spool/cron/root
四、使用grafana來(lái)圖形顯示;
#安裝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
#啟動(dòng)
systemctl start grafana-server
#界面：
http://192.168.31.222:3000/login
默認(rèn)帳號(hào)/密碼：admin/admin
增加data source/dashboard/panel/, 使用時(shí)序圖、餅圖、表格來(lái)做儀表盤(pán)；
metrics設(shè)置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
##收集服務(wù)器基礎(chǔ)性能數(shù)據(jù)
#DATE時(shí)間戳
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}'`
#內(nèi)存：total, used 單位KB
memory=(`top -bn1 |grep 'Mem :'|awk '{print $4,$8}'`)
MEMTOTAL=${memory[0]}
MEMUSED=${memory[1]}
#網(wǎng)絡(luò)進(jìn)出帶寬, RX, TX, 單位字節(jié)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]}))
#根分區(qū)的使用率 used
DISKROOTRATE=`df -h|awk 'NR==2{print $5}'|sed 's/%//'`
#磁盤(pán)IO：sda硬盤(pán)的等待時(shí)間和利用率：await, util%
io=(`iostat -x|grep sda|awk '{print $10,$14}'`)
IOAWAIT=${io[0]}
IOUTIL=${io[1]}
#開(kāi)放的端口: 協(xié)議、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
##建數(shù)據(jù)庫(kù)和表后，將數(shù)據(jù)插入數(shù)據(jù)庫(kù)
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
參見(jiàn)：https://app.yinxiang.com/fx/c71ee206-f6e9-46d6-8d7b-23402e8253d0
