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

最近领导又吩咐活过来了，要求每周日查询 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