Search code examples
mysqlmutt

how to email the output of a mysql command

I want to email the output of a MySQL command, I wrote the following script but it doesn't work, where am I going wrong? 

mysql --user=me --password=00045344534 john_e56
SELECT table_schema,table_name,update_time
FROM information_schema.tables
WHERE update_time > (NOW() - INTERVAL 5 MINUTE);
INTO OUTFILE '/mysqlchanges.txt'
exit
mutt -s "mysql changes" me123@mail.com -a /mysqlchanges.txt < /mail.txt
Solution

  • What error are you getting?

    I'm not sure how INTO OUTFILE works, but 99% it requires write access to the file you're telling it to write to. In your query the output file is right in the root (/) partition. I doubt the user mysql is run from allows to write there. If you're on *nix box, create a file in your home

    touch /home/ubuntu/mysqlchanges.txt
sudo chown mysql:mysql /home/ubuntu/mysqlchanges.txt

    in your shell first. Then to test you can do

    su mysql
echo "" > /home/ubuntu/mysqlchanges.txt

    and if that works with no error, you can modify your query to output to this file and mutt to get that file as attachment:

    mysql --user=me --password=00045344534 john_e56
SELECT table_schema,table_name,update_time
FROM information_schema.tables
WHERE update_time > (NOW() - INTERVAL 5 MINUTE);
INTO OUTFILE '/home/ubuntu/mysqlchanges.txt'
exit
mutt -s "mysql changes" me123@mail.com -a /home/ubuntu/mysqlchanges.txt < /mail.txt

    Assuming ubuntu is your user and mysql runs on behalf of mysql user.

    UPDATE

    If I understood your request right, you just need to receive the output of this SQL query:

    SELECT table_schema,table_name,update_time
FROM information_schema.tables
WHERE update_time > (NOW() - INTERVAL 5 MINUTE);

    onto your mailbox me123@mail.com

    If it's not crucial to make it as attachment and just plain text in body is fine, then have a look at this bash command:

    mysql -u me -p00045344534 -e 'SELECT table_schema,table_name,update_time FROM information_schema.tables WHERE update_time > (NOW() - INTERVAL 5 MINUTE);' | mutt -s "mysql changes" me123@mail.com --

    first it launches that query with output to STDOUT, then pipes it to mutt