[root@iZbp1fna7ky0qz2jbj7gfpZ ~]# cat mysql.sh
#!/bin/bash
database="openvpncert"
user="root"
password="123456"
table="certs"
mysql -u$user -p$password -D $database --skip-column-names -e "select id,name,ip,url from $table order by id asc" | while read id cert_name ip url;do
echo $id","$name","$ip","$url
done
https://stackoverflow.com/questions/5918436/bash-script-loop-through-mysql
https://stackoverflow.com/questions/3683424/processing-mysql-result-in-bash
[root@web_manager mailcsv]# cat generatecsv.sh
#!/bin/bash
yestoday=`date +"%Y%m%d" -d "-1 days"`
today=`date +"%Y%m%d"`
#echo $yestoday;
basepath=$(cd `dirname $0`; pwd)
database="filecheck_log"
user="root"
password="123456"
table="filecheck"$yestoday
/usr/bin/mysql -h10.10.10.20 -D $database -u$user -p$password -e "select * from $table order by id asc" > $basepath/$table.txt
if [ $? -eq 0 ];
then
echo "ok";
sed -i '1d' $basepath/$table.txt
echo "id,timeStamp,LocalTime,SoftId,Ver,ip,UId,QId,ComputerName,MD5Error,MD5,SignError,DubiousModule">$basepath/$table.csv
awk '{print $1","$2","$3" "$4","$5","$6","$7","$8","$9","$10","$11","$12","$13","$14}'>>$basepath/$table.csv $basepath/$table.txt
filewc=`cat $basepath/$table.csv|wc -l`
#echo $filewc
if [ $filewc -gt 10 ];
then
#echo "good";
/usr/bin/python ./mail.py $table.csv $table.csv
fi
else
echo "error";
fi
[root@web_manager mailcsv]# cat test.sh
#!/bin/bash
yestoday=`date +"%Y%m%d" -d "-1 days"`
today=`date +"%Y%m%d"`
database="filecheck_log"
user="root"
password="123456"
table="filecheck"$yestoday
basepath=$(cd `dirname $0`; pwd)
echo "id,timeStamp,LocalTime,SoftId,Ver,ip,UId,QId,ComputerName,MD5Error,MD5,SignError,DubiousModule">$basepath/$table.csv
mysql -h10.10.10.20 -u$user -p$password -D $database -e "select id,timeStamp,datetime,SoftId,Ver,ip,UId,QId,ComputerName,MD5Error,MD5,SignError,DubiousModule from $table order by id asc" | while read id timeStamp LocalTime SoftId Ver ip UId QId ComputerName MD5Error MD5 SignError DubiousModule; do
#echo $id","$timeStamp","$LocalTime" "$SoftId","$Ver","$ip","$UId","$QId","$ComputerName","$MD5Error","$MD5","$SignError","$DubiousModule >> $basepath/$table.csv
echo $id","$timeStamp","$LocalTime","$SoftId","$Ver","$ip","$UId","$QId","$ComputerName","$MD5Error","$MD5","$SignError","$DubiousModule
#echo $id","$timeStamp","$LocalTime","$SoftId","$Ver","$ip","$UId","$QId","$ComputerName
done
exit;
sed -i '2d' $basepath/$table.csv
filewc=`cat $basepath/$table.csv|wc -l`
#echo $filewc
if [ $filewc -gt 10 ];
then
echo "sendmail";
/usr/bin/python ./mail.py $table.csv $table.csv
fi
pi@bananapi ~/shell $ cat generate.sh
#!/bin/bash
yestoday=`date +"%Y%m%d" -d "-1 days"`
today=`date +"%Y%m%d"`
#echo $yestoday;
basepath=$(cd `dirname $0`; pwd)
database="filecheck_log"
user="root"
password="root"
table="filecheck"$yestoday
/usr/bin/mysql -hlocalhost -D $database -u$user -p$password -e "select id,timeStamp,\`LocalTime\`,SoftId,Ver,ip,UId,QId,ComputerName,case when MD5Error='' then '------' else MD5Error end,MD5,SignError,DubiousModule from $table order by id asc" > $basepath/$table.txt
if [ $? -eq 0 ];
then
echo "ok";
sed -i '1d' $basepath/$table.txt
echo "id,timeStamp,LocalTime,SoftId,Ver,ip,UId,QId,ComputerName,MD5Error,MD5,SignError,DubiousModule">$basepath/$table.csv
awk 'gsub(/\\\\/,"\\",$14){print $1","$2","$3" "$4","$5","$6","$7","$8","$9","$10","$11","$12","$13","$14}'>>$basepath/$table.csv $basepath/$table.txt
filewc=`cat $basepath/$table.csv|wc -l`
#echo $filewc
if [ $filewc -gt 10 ];
then
echo "good";
#/usr/bin/python ./mail.py $table.csv $table.csv
fi
else
echo "error";
fi
pi@bananapi ~/shell $
awk中使用替换
http://blog.sina.com.cn/s/blog_71459fbc0101eq0h.html
gsub（/123/,456,$3）将第三个域中的“123”替换成“456”
例子：
test文件内容：
0001|efskjfdj|EREADFASDLKJCV
0002|djfksdaa|JDKFJALSDJFsddf
0003|efskjfdj|EREADFASDLKJCV
0004|djfksdaa1234|JDKFJALSDJFsddf
将文本以“|”分割，将第二域中的字母“d”替换成“#”
awk -F '|' 'gsub(/d/,"#",$2) {print $0}' test
0001 efskjf#j EREADFASDLKJCV
0002 #jfks#aa JDKFJALSDJFsddf
0003 efskjf#j EREADFASDLKJCV
0004 #jfks#aa1234 JDKFJALSDJFsddf
