[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 304
  • Last Modified:

Bash script to email me data from MySQL on Linux server

I have a View that I created on my MySQL database that I would like emailed to me daily so I can monitor some data.  I just have no idea on how to write a script to do this.  Can anyone help?
0
kevandju
Asked:
kevandju
  • 3
2 Solutions
 
nabeelmoiduCommented:
echo " MYSQL_STATEMENT" | mysql -u username -p  password   >> /path/to/file
mail -s 'Daily DB Check - MySQL View'  username@mail.domain.tld < /path/to/file
0
 
arober11Commented:
If you want a daily report you will probably want to schedule the extract, this can be done via a crontab entry.

To do this,

1) log on to the server, as some user:
2) place the necessary commands in a shell script file called "mail_mysql_csv_extract.sh" in your home directory.
(see below)
3) Make the script executable, via a "chmod u+x mail_mysql_csv_extract.sh"
4) Add a crontab entry, the following commands will add an entry that will run the extract at 07:00 each morning:
crontab -l > my_users.crontab
echo "00 07 * * *  $HOME/email_mysql_csv_extract.sh > $HOME/email_mysql_csv_extract.sh.log 2>&1" >> my_users.crontab
crontab my_users.crontab

#!/bin/sh
#
TMP_DIR="/tmp/my_mysql_dir"
CSV_FL_NAME="file-name.csv"
DB='you_db_name'
DBuser='mysql_username'
DBpass='mysql_password'
DBtable='the_table_name'
#
MAILX_REP="-R"
REPLY_TO="do-not-replay@yourdomain.com"
SUBJECT_LINE="Daily DB Check - MySQL View"
MAIL_TO="your@yourdomain.com"
#
SQL="SELECT * INTO OUTFILE '$TMP_DIR/$CSV_FL_NAME'  \
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\\\"' \
  LINES TERMINATED BY '\\n' \
  FROM $DBtable;"

rm -rf $TMP_DIR
mkdir $TMP_DIR || (echo "Error: Cant create a temporary directory";exit 2)
chmod 777 $TMP_DIR
mysql -u $DBuser -p$DBpass $DB -e "$SQL"

#Attach the extracted file to an email, then delete it.
uuencode $TMP_FIR/$CSV_FL_NAME $CSV_FL_NAME | mailx $MAILX_REP $REPLY_TO -s "$SUBJECT_LINE" $MAIL_TO
rm -rf $TMP_DIR

Open in new window

0
 
kevandjuAuthor Commented:
Sorry for the delay on responding.  Arober is there a way that I can just have the results placed in the body of the email rather than an attachment?
0
 
nabeelmoiduCommented:
#Attach the extracted file to an email, then delete it.
uuencode $TMP_FIR/$CSV_FL_NAME $CSV_FL_NAME |

Can be changed to

uuencode $TMP_FIR/$CSV_FL_NAME $CSV_FL_NAME;
mailx $MAILX_REP $REPLY_TO -s "$SUBJECT_LINE" $MAIL_TO < $CSV_FL_NAME;
0
 
nabeelmoiduCommented:
sorry

uuencode $TMP_FIR/$CSV_FL_NAME $CSV_FL_NAME;
mail $MAILX_REP $REPLY_TO  -s "$SUBJECT_LINE" $MAIL_TO < $CSV_FL_NAME;
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now