MySQL数据通过 mailx 定时发送的任务

Posted on

任务描述

每天定时对 Mysql 数据统计，然后将结果(.csv 文件)以附件形式邮件发送给指定人员

基本思路

bash 脚本将结果写到制定路径，然后 mail 附件发送，基于 crontab 定时执行

遇到问题

Mysql

列数据转为行数据

1
select ... case when then .... group by

保存为 csv 文件

1
select into outfile fields terminated by ',' lines terminated by '\r\n'

指定的输出文件路径, 但是报错

1
ERROR 1 (HY000) at line 1: Can't create/write to file '/mypath/outputfile.csv' (Errcode: 2) mv: cannot stat `/mypath/outputfile.csv': No such file or directory

查找原因，查看 Mysql Doc SELECT Syntax，

The file is created on the server host, so you must have the FILE privilege to use this syntax….If you want to create the resulting file on some other host than the server host, you normally cannot use SELECT … INTO OUTFILE since there is no way to write a path to the file relative to the server host’s file system.

所以，要在 mysql 所在服务器上进行。如果一定要在客户端服务器操作，通常做法是，

However, if the MySQL client software is installed on the remote machine, you can instead use a client command such as mysql -e “SELECT …” > file_name to generate the file on the client host.
By default, if you don’t specify absolute path for OUTFILE in select … into OUTFILE “…” It creates the file in “/var/lib/mysql/“

如果不带完整路径，文件将写到数据库默认路径下，如果指定路径，那么需要保证有当前路径的权限，且同名文件不存在。
输出文件路径需要 mysql 的写，执行权限，可以将当前用户加到 mysql 组，

1
usermod -a -G mysql user

包含列名

1
--column-names

完整语句如下

1
2
3
4
5
6
7
8
query_sql="use db; \
select ref_date,ref_hour, \
sum(case when eid = 1 and ispvtype = 1 then count else 0 end) as 'pv', \
sum(case when eid = 2 and ispvtype = 0 then count else 0 end) as 'uv' \
from t_data \
where utm = 0 and ref_date = \"$refDate\" group by ref_date, ref_hour  \
into outfile '$outputfile' fields terminated by ',' lines terminated by '\r\n' \
"

发邮件

uuencode

因为要添加附件，选择 uuencode + mail 的方式，机器上没有 uuencode，安装

1
$yum install sharutils

安装后，测试发送附件，

1
$ ( cat mailbody.txt; uuencode $filename $reportname ) | mail -s "mail subject" sendto@xxx.xxx

但接收到的为乱码。可能的原因是邮件提供商不支持。

乱码详细原因

出现此现象是因为 uuencode 邮件误标记 MIME 版本 1.0 标头。因此，该消息的格式不正确。

Exchange Server uuencode 邮件误标记 MIME 版本 1.0 标头，该邮件中的文本处理作为 MIME 文本。因此，附件的文本显示在邮件的正文，并且出现乱给邮件收件人。出现此现象是因为邮件通过组合中 uuencode 邮件的 MIME 版本 1.0 头结合两种不同的 Internet 正文格式。

解决方案

配置 Exchange SMTP 服务器，则将邮件发送到 Exchange Server，以便非 Exchange 服务器不向 uuencode 消息添加 MIME 版本标头。

mailx

发现 mailx -a 可以发送附件，测试，成功。

crontab

写 crontab 定时发送，Done.