Koozali.org: home of the SME Server

Contribs.org Forums => General Discussion => Topic started by: JJSH on March 17, 2009, 11:48:42 AM

Title: Recommendations for exporting a mysql querty to csv then sftp'ing to host
Post by: JJSH on March 17, 2009, 11:48:42 AM

I think this is a more general Linux question rather than particularly SME specific, but feel free to move this if it is felt it would be better in a 7.x forum, as I will be using SME 7.3.

I have an bespoke application here that I need to get some data from, save it as a csv file, then sftp it to a customer. I have just migrated the database part of it to MySQL and that runs on my SME box. I would like to automate this process, and have installed the crontab contrib for this purpose. I have written the mySQL query, and validated it as producing the required data.

So, I would like to poll people on which technologies would be best to call from crontab, and any advantages / disadvantages. I guess I'm looking at either calling a php, perl or shell script. Other than familiarity with the languages involved (and I'm not brilliant at either, but confident I could work it out), are there any underlying reasons to choose, say, php over a shell script, or whatever?

All advice gratefully received.  :smile:

Title: Re: Recommendations for exporting a mysql querty to csv then sftp'ing to host
Post by: cactus on March 17, 2009, 12:15:50 PM

Quote from: JJSH on March 17, 2009, 11:48:42 AM

are there any underlying reasons to choose, say, php over a shell script, or whatever?

No, other than experience in the choosen language AFAIK, but I would keep it as simple as possible, so if you do not need php or any other scripting language go with plain bash.

You can use a method for exporting like this: http://forums.mysql.com/read.php?79,11324,13062#msg-13062 , which you might be able to run from the command line and hence from a bash script using

Code: [Select]

mysql --user=username --password=password -e "SELECT ...."
After that you would have to rsync or sftp it to the other host if you need it there. I suggest to use rsync if possible and try and read the man rsync pages (or google as a lot of information on rsync can be easily found on the net).

Title: Re: Recommendations for exporting a mysql querty to csv then sftp'ing to host
Post by: JJSH on March 17, 2009, 12:51:12 PM

Thanks Cactus, I'll take that on board regarding keeping things simple, and have a look at that post. I'm stuck with sftp, I'm afraid, as it is what the customer wants.

Thanks again.