The database specifies the fields in the database table for backup, and sends mail to the specified mailbox ​ regularly in the form of a table.

Backup data is sent by regular mail mkdir-p / opt/module/shell/mkdir-p / opt/module/shell/sql/revenue.sqlmkdir-p / opt/module/shell/csv/revenue#MailTool.jar is placed in the specified directory to facilitate sending mail using the automatic generation form vim / opt/module/shell/daily_text.sh#! / bin/bash# database specification library. Back up the fields in the table And send mail in tabular form to the specified mailbox # definition variable dat= `date-d'1 days ago' +% Yashi% m`basepath = / opt/module/shellsql_revenue=$ {basepath} / sql/revenue.sqlcsv_revenue=$ {basepath} / csv/revenue/$ {dat} .csvfilename = `date +% Ymurf% m` # html_revenue=$ {basepath} / html/revenue/$ {dat} .html # current= `date-d last-day "+% Y-%m-%d" `date-d "$current" +% s` # convert current to timestamp Accurate to millisecond # currentTimeStamp=$ ((timeStamp*1000)) # current2= `date "+% Y-%m-%d" `# time2Stamp= `date-d "$current2" +% s` # convert current to timestamp, accurate to millisecond # current2TimeStamp=$ ((time2Stamp*1000)) # specific sql statement backup specified field sql_revenue_total= "select date,tb_card_no,goodsId,outItemSKu,money from itoyo_2018.o_jujibao_record Order by date" # database login ip in a database Port, user Password mysql=/usr/bin/mysqlhost= database ipport=3306username= database specify user name password= password # export the backup to a defined variable file $mysql-h$host-P$port-u$username-p$password-e "$sql_revenue_total" > $csv_revenuetotal= `$ mysql-h$host-P$port-u$username-p$password-e "$sql_revenue_total" | tail-n + 2`echo-e "$total" > > $csv_revenuesed-I's /\ t / " "/ g'$csv_revenuesed-I's / ^ /" / g'$csv_revenuesed-I's stop email / "/ g'$csv_revenuesed-I'2 mine3d' $csv_revenue# filter the information of the month in the backup file to the specified file cat $csv_revenue | grep-E" (date | $filename) "| sed'$d'> $csv_revenue.bak# edit email content and format confile=$ {basepath} / conf.propertiesrm-f $confile # here I use Tencent enterprise mailbox The exmail.qq.com mailbox port is 465echo "mailhost=smtp.exmail.qq.com" > $confileecho "mailport=465" > > $confileecho "username= email address" > > $confileecho "password= mailbox password" > $confileecho "from= Sender mailbox" > > $confileecho "to= recipient mailbox" > > $confileecho "# cc=" > > $confileecho "# bcc=" > $confileecho "subject= title $dat" > > $confileecho "content=

This month's data

# table_revenue#

"> > $confileecho" table_revenue=$csv_revenue.bak "> > $confileecho" file=$csv_revenue.bak "> > $confile# sends a message and-mail specifies that the content of the message must be written to the absolute path / data/tools/jdk1.7.0_67/bin/java-jar / opt/module/shell/MailTool.jar-mail $confile to execute the scheduled task.

Schedule tasks at regular intervals

# the daily_text.sh script must give execution permission crontab-e00 18 30 *. / opt/module/shell/daily_text.sh