Scott Haneda wrote:
> on 02/04/2004 02:23 AM, Bernd Tannenbaum at tannenbaum@stripped
>>2 possible ways to go:
>>Feed a Textfile with your statement in the db:
>>PATH/mysql db < /sql_scripts/sql_statements_as_textfile --password=pass
>>Give command directly In the shell-script:
>>PATH/mysql --skip-column-names -e "SELECT table SET blabla" db
> I need to select into outfile on this one, which poses a problem to me, I
> can not write the file to any directory as I get a permissions error since
> mysql is the owner of this file, how would I do this ?
Well, mysql can write into its data directory (the default location for
SELECT INTO OUTFILE), and mysql can write to whichever directory has
it's socket file (often /tmp). Assuming you don't want your outfile in
either of those places, you could just give mysql permission to write to
the directory where the outfile should go. You may even want to create
a directory for this purpose.
Note that mysql will not overwrite an existing file using SELECT INTO
OUTFILE, so you will need to arrange it so your cron job gives a unique
name for the outfile on each run, or have some process that removes your
chosen outfile between runs.
Also, you need the FILE privilege to SELECT INTO OUTFILE.
Alternatively, you could simply redirect output to a file.
mysql dbname -e "SELECT serial FROM serials" >/path/to/outfile
In this case, the file is written by the user running the cron job, so
that user, not mysql, needs permission to write.
Finally, I think it's important to point out that putting -ppass or
--password=pass on the command line is insecure. Any user who runs ps
at the right moment can see the password. The preferred way to do this
is to put the password in the client section of the user's .my.cnf file.
See <http://www.mysql.com/doc/en/Option_files.html> for more.