脚本.把mysql中的数据读出写到文本中

把mysql数据库中的数据读出，写到文本中，多行数据写成一行记录

 

 

脚本

#!/bin/sh
TxIp=192.168.0.103
bindir=/usr/local/mysql/bin/mysql
datadir=/data/txdata/test
yestoday=`date -I --date='-1 days'`
for num in $(seq 10 132)
do
IP=192.168.0.$num
$bindir -uwaptx -p"(YDtx405)" -h $TxIp -P12341 --default_character_set=GBK txbackend -e "select date_format(b.createTime,'%Y-%m-%d') as date,a.ip,b.alertId,count(a.ip) as count from alert_config as a,alert_mobile_history_log as b where a.id=b.alertId and a.description like 'load%' and a.ip='$IP' and date_format(b.createTime,'%Y-%m-%d')='$yestoday'" | sed 1d >load_chen.txt
exist=`cat load_chen.txt|awk '{print $4}'`
if [ $exist -eq 0 ]
then
        rm -f load_chen.txt
else
$bindir -uwaptx -p"(YDtx405)" -h $TxIp -P12341 --default_character_set=GBK txbackend -e "select b.value,date_format(b.createTime,'%T') as time from alert_config as a,alert_mobile_history_log as b where a.id=b.alertId and a.description like 'load%' and a.ip='$IP' and date_format(b.createTime,'%Y-%m-%d')='$yestoday'" | sed 1d >>load_chen.txt
Details=`cat load_chen.txt`
echo $Details >> chenlei.txt
fi
done
unix2dos chenlei.txt
sz chenlei.txt

 

可能有IP在当天没有记录，但是去count了。所以它会读出一个0条记录出来，文本中的记录可能是null null null 0 ，所以用awk找0，找到了就把文本删除。没找到0说明记录不为空，可以继续把详细的记录写到文本中。

 

结果

[root@waptx126 chen]# cat chenlei.txt
2009-08-11 192.168.0.38 213 4 8.54 04:10:03 8.15 04:15:03 8.96 04:20:03 8.16 04:35:04
2009-08-11 192.168.0.42 225 8 8.15 03:05:03 8.14 03:10:04 9.88 03:15:03 8.70 03:20:03 8.97 03:25:03 8.46 03:30:03 9.22 03:35:03 8.35 03:40:03
2009-08-11 192.168.0.60 279 7 5.14 21:10:03 6.30 21:20:03 5.58 23:05:04 5.47 23:10:03 5.12 23:15:03 5.26 23:20:03 5.32 23:30:03
2009-08-11 192.168.0.63 288 1 11.25 03:30:04
2009-08-11 192.168.0.65 294 5 9.45 01:45:08 19.38 01:50:08 13.33 01:55:08 9.05 02:00:06 7.06 02:05:07
2009-08-11 192.168.0.76 327 1 5.25 22:25:03
2009-08-11 192.168.0.77 330 2 5.09 19:00:03 5.24 23:25:04
2009-08-11 192.168.0.101 12 3 5.17 16:30:03 5.40 16:40:03 5.12 16:50:03
2009-08-11 192.168.0.103 18 2 13.18 00:05:07 10.40 01:25:04
2009-08-11 192.168.0.115 57 3 8.54 09:00:03 8.49 16:00:03 8.32 17:00:04
2009-08-11 192.168.0.116 60 2 6.20 09:00:03 9.76 13:00:03