Cron – Run SQL query from cron

cronsql

I'd like to run a SQL query via cron. I figured I'd create a .sql file and call it from a script that I place in the cron folder. The query is relatively large and not written by me, so I'd prefer to not have to re-create it in PHP.

Can anyone provide some assistance? I am not exactly clear on how to run the SQL query from the script. Thanks in advance.

EDIT: Just for some background info, the query is intended to grab data from multiple tables and organize it in a csv file.

Best Answer

Each database has a command line client tool

For MySQL, see man mysql

comand to add in cron something like this

cat /dir/query.sql|mysql database -u user -ppass >result

For PHP you can use file IO to read query, after that use string variable instead of the query in your requests.

Related Solutions

Cron Job – How to Run a Cron Job Manually and Immediately

Here's what I did, and it seems to work in this situation. At least, it shows me an error, whereas running from the command line as the user doesn't show the error.

Step 1: I put this line temporarily in the user's crontab:

* * * * *   /usr/bin/env > /home/username/tmp/cron-env

then took it out once the file was written.

Step 2: Made myself a little run-as-cron bash script containing:

#!/bin/bash
/usr/bin/env -i $(cat /home/username/tmp/cron-env) "$@"

So then, as the user in question, I was able to

run-as-cron /the/problematic/script --with arguments --and parameters

This solution could obviously be expanded to make use of sudo or such for more flexibility.

Hope this helps others.

Ssh – RSync shell script failing to run as CRON, runs fine manually

You will need to specify the full path to the exclude file, as the working directory of the script will not be automatically the one from where you start the script, but instead the cron job owners home directory.

Alternatively, you can cd into the correct directory from inside your shell script.

Best Answer

Related Solutions

Cron Job – How to Run a Cron Job Manually and Immediately

Ssh – RSync shell script failing to run as CRON, runs fine manually

Related Topic