Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

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.

share|improve this question

2 Answers 2

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.

share|improve this answer
    
What is the reason for cat and >result? –  Nick Jul 26 '11 at 0:58
1  
cat send file to stdout. after that pipe send all that to mysql clietn like u do when type that using keyboard. >result mean send output to file "result". better use full path. see bash-shell articles for farther understanding. –  arheops Jul 26 '11 at 1:17
    
The SQL query that I'm running exports to a .csv file and takes about 5 minutes to complete. When I used your command, it finished immediately and the result file had the man mysql page in it. Something is not working properly. –  Nick Jul 26 '11 at 2:12
    
@arheops: Wouldn't mysql database -u user -ppass </dir/query.sql >result work as well? :) –  Janne Pikkarainen Jul 26 '11 at 6:50
    
sure. but i see no real difference in it. it is just other notation ;) –  arheops Jul 26 '11 at 12:15
up vote 0 down vote accepted

The solution I ended up using was to put the query in a "here document". This bash script was then implemented with cron. For more information, check out this link: http://snippets.dzone.com/posts/show/3078

And just in case that link goes dead, here's a snippet of the sample code:

#! /usr/bin/env bash
# execute some bash scripting commands here

mysql mydatabase <<SQL_ENTRY_TAG_1
SELECT * 
  FROM mytable 
  WHERE somecondition='somevalue';
SQL_ENTRY_TAG_1

# execute other bash scripting commands here

mysql mydatabase <<SQL_ENTRY_TAG_2
SELECT *
  FROM myothertable
  WHERE someothercondition='someothervalue';
SQL_ENTRY_TAG_2
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.