Situation
You want to export data from a MYSQL table and email it in .CSV format on a regular schedule. This was on Darwin on Mac OS X server, but works on any similarly configured *Nix of course.
Solution
Build Query
Here I am exporting a CSV formatted file suitable for import into Excel.
SELECT * INTO OUTFILE '/tmp/weeklyupdates.csv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' from profiletable
WHERE (entereddt > DATE_SUB( curdate( ) , INTERVAL 7 DAY ) );
Save this sql command file as updateseloutfile.sql
Bash script to run the query:
#!/bin/bash
rm /tmp/updates.csv
DBS=`mysql -uUsername -pPassword mysqldbnamehere < /scripts/updateseloutfile.sql`
save the script as updatescript.sh and test everything by executing script:
./updatescript.sh
Email Results
Add to the shell script a line to email the results:
(cat /scripts/updatesplaintextemailmessage.txt; uuencode /tmp/updates.csv /tmp/updates.csv) | mail -b samplebcc@antioch--college.edu -s "Weekly Web Updates" toaddress@antioch--college.edu
This line also adds some email text stored in updatesplaintextemailmessage.txt. It encodes the updates.csv file and sends it as an attachment to toaddress@, bcc�ing samplebcc@.
So you end up with 3 small text files:
updateseloutfile.sql – the sql file that exports data
updatesplaintextemailmessage.txt – the plain text message for email
updatescript.sh – the shell script that does the work
Schedule Export
Now set up the cron scheduler to run the shell script weekly:
check crontab listing: crontab -l
edit crontab: crontab -e
and add line to run your script weekly: 00 03 * * 6 /scripts/updatescript.sh
That�s It! You now have weekly export of MYSQL data being emailed in .CSV format to designated email address. If this is of help to anyone else, I�d appreciate a quick note.
Learn more about crontab by typing: man crontab -s5 The regular man crontab does not have any good examples.