Mysql提取数据每日自动邮件通知

2018年12月21日11:29:13 2

最近领导又吩咐活过来了,要求每周日查询 sql,统计一周的数据,并生成 excel 文件,然后还要邮件发给他。想一想,这些都是重复性的劳动,除了时间是变,其他都是不变的。所以还是得搞个自动化脚本处理得了!
本文参考mysql 查询导出 csv 文件并自动发送附件给领导一篇文章 ,最近发现一个小小的 bug,然后也是优化了一下。

一、需求拆分

1,首先我们要写出sql语句,可以和开发一起完成。
2,然后将其变成模板文件,比如 data1_muban.sql,data2_muban.sql,data3_muban.sql,data4_muban.sql;
3,将sql 中的日期时间用变量名代替,比如我这边的时间是 BETWEEN ‘LAST_SUN’ AND ‘THIS_SAT’ 其中 LAST_SUN 和 THIS_SAT 是指上一个周日和这个周六,毕竟是一周的数据,可以随意命名,根据自己需求来,
4,考虑到这个变量始终都是要变成日期格式的,所以模板文件是不能动的,要生存临时 sql 文件,然后将临时 sql 文件中的变量名称替换成需要日期。以下是脚本内容,我会详细讲解一下

二、整体的脚本内容如下:

#!/bin/bash
PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin
export PATH
cp data_muban.sql data1.sql
today=`date -d "1 days ago" +%Y-%m-%d`
last_sun=`date -d "7 days ago" +%Y-%m-%d`
sed -ir 's/THIS_SAT/'"$today"'/g' data1.sql
sed -ir 's/LAST_SUN/'"$last_sun"'/g' data1.sql
passwd='yourdbpasswd'
dbname='yourdbname'
dbuser='yourdbuser'
/usr/bin/mysql -h xxx.xxx.xxx.xxx -u $dbuser -p$passwd >1.log << EOF
use $dbname;
source data1.sql
EOF
sed -i 's/\t/,/g' 1.log
cat 1.log | while read line
do
echo $line 
done > data1.csv
iconv -c -f utf-8 -t gb2312 data1.csv > data2.csv 
mv data2.csv YHLS_$today.csv
rm -rf data1.*
rm -rf 1.log

sleep 5

cp data1_muban.sql data2.sql
sed -ir 's/THIS_SAT/'"$today"'/g' data2.sql
sed -ir 's/LAST_SUN/'"$last_sun"'/g' data2.sql
/usr/bin/mysql -h xxx.xxx.xxx.xxx -u $dbuser -p$passwd >2.log << EOF
use $dbname;
source data2.sql
EOF
sed -i 's/\t/,/g' 2.log
cat 2.log | while read line
do
echo $line 
done > data2.csv
iconv -c -f utf-8 -t gb2312 data2.csv > data.csv 
mv data.csv FYHY_$today.csv
rm -rf data2.*
rm -rf 2.log

sleep 5

cp data2_muban.sql data3.sql
sed -ir 's/THIS_SAT/'"$today"'/g' data3.sql
sed -ir 's/LAST_SUN/'"$last_sun"'/g' data3.sql
/usr/bin/mysql -h xxx.xxx.xxx.xxx -u $dbuser -p$passwd >3.log << EOF
use $dbname;
source data3.sql
EOF
sed -i 's/\t/,/g' 3.log
cat 3.log | while read line
do
echo $line 
done > data3.csv
iconv -c -f utf-8 -t gb2312 data3.csv > data.csv 
mv data.csv YZZCYH_$today.csv
rm -rf data3.*
rm -rf 3.log

sleep 5

cp data3_muban.sql data4.sql
/usr/bin/mysql -h xxx.xxx.xxx.xxx -u $dbuser -p$passwd >4.log << EOF
use $dbname;
source data4.sql
EOF
sed -i 's/\t/,/g' 4.log
cat 4.log | while read line
do
echo $line 
done > data4.csv
iconv -c -f utf-8 -t gb2312 data4.csv > data.csv
mv data.csv YQQK_$today.csv
rm -rf data4.*
rm -rf 4.log

脚本说明

这个脚本内容是执行 sql 生成导出的 csv 文件,也就是领导需要的 excel 文件,这其中前期有一些坑存在,最开始,我准备用 into outfile “xxx.csv”的方式,后来发现文件都生成到数据库服务器上去了,我在本地找了半天没找到文件,原来 into outfile 是在数据库服务器生成的,这个坑有点大,主要是它能直接将查询结果导出为 csv 文件,但是还是放弃了这种方式,毕竟数据库服务器上拿文件还是不太安全。然后我就用了一种将查询结果进行转换的方式也就是以上中脚本的 for 循环的部分,本来直接转换成 csv 文件了,一下载到本地一看,打开是乱码的,后面查询版本,原来 excel 对 utf8 编码的方式打开是乱码显示的,这又坑了,巴拉巴拉又去查询了一番,发现 excel 是可以打开 gb2312 的中文格式的,那就好办了,就将生成的 csv 文件再转码一次,用 iconv -f utf-8 -t gb2312 data4.csv > data.csv 种方式可以将 utf8 格式的 data4.csv 文件转为 gb2312 格式的 data.csv 文件,不过你在 linux 系统上看 data.csv 文件以 utf8 方式查看会是乱码的,但是下载到 windows 上查询是正常的,毕竟我要的是这种效果。


好了,以上文件生成了,接下来是考虑如何自动发送给领导了。下面就是配置sendmail。

三、配置邮箱sendmail+定时任务

3.1 部署sendmail

yum install -y sendmail 
yum install -y sendmail-cf

3.2 添加定时任务

30 8 * * 7 source /etc/profile;cd /home/mysql/data1/;sh exec.sh
30 9 * * 7 source /etc/profile;sh /home/mysql/data1/sendmail.sh

3.3 邮件自动推送脚本

#!/bin/bash
today=`date -d "1 days ago" +%Y-%m-%d`
/usr/local/bin/sendEmail -f youemail@xxxx.com -t lingdao@xxxx.com  lindao2@xxxx.com \
    -s smtp.mxhichina.com -u "每周数据库查询" -o message-content-type=html \
    -o message-charset=utf-8 -a /home/mysql/data1/*_$today.csv -xu youremail@xxxx.com \
    -xp youremailpasswd -m "每周数据库查询-详见附件"
##  另外之余,如果发送的邮件有多位领导,可以多个收件人,sendmail 的方式如下所示,就是第一个收件人后,空格再增加一个收件人。

以上是将某日期的所有的 csv 文件发送给领导了。

四、问题总结

问题BUG:基于 mysql 查询出来的结果是 tab 分割字段的,而之前用的脚本处理,是用空格进行处理,以至于后来查询结果中有字段值里面有空格,而导致最后出来的报表中有错乱的现象,还有有些字段内容本身就是空的,也会出现栏目交错的现象!

之前的逻辑在这里:用 tr 命令将空行换成逗号,之后进行导入 csv 文件;

/usr/bin/mysql -h xxx.xxx.xxx.xxx -u $dbuser -p$passwd >1.log << EOF
use $dbname;
source data1.sql
EOF
cat 1.log | while read line
do
echo $line | tr " " ","
done > data1.csv

后来,将 1.log 拿到本地查看,发现每个字段之间是 tab 分割的,这就表示之前将空格分割的逻辑是错误的;按理来说,只要将空格替换成\t 的 tab 就可以了,后来发现还是不行,因为循环读 1.log 内容的时候,每一行将 tab 都换成空行了。所以只能先将所有的 tab 换成逗号,然后再进行转化成 csv 文件。处理后的逻辑如下所示

/usr/bin/mysql -h xxx.xxx.xxx.xxx -u $dbuser -p$passwd >1.log << EOF
use $dbname; 
source data1.sql 
EOF
sed -i 's/\t/,/g' 1.log
cat 1.log | while read line
do
echo $line 
done > data1.csv
  • QQ精品交流群
  • weinxin
  • 微信公众号
  • weinxin
admin

发表评论

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

目前评论:2   其中:访客  1   博主  1

    • avatar 哥哥的人 1

      学习了