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 have a script called /etc/cron.daily/99loganalyzer_expire, which is as so:

#!/bin/bash
SQL="DELETE FROM SystemEvents WHERE ReceivedAt < DATE_SUB(CURDATE(),INTERVAL 30 DAY)"   
MYSQL_USER="loganalyzer"
MYSQL_PASS="loganalyzerpassword"
MYSQL_DB="Syslog"    
echo $SQL | /usr/bin/mysql --user=$MYSQL_USER --password=$MYSQL_PASS $MYSQL_DB

When it runs overnight, I get an email saying it failed:

/etc/cron.daily/99loganalyzer-expire:

ERROR 1045 (28000): Access denied for user 'loganalyzer'@'localhost' (using password: YES)

...however, when I run it as root from the console, it works:

# time /etc/cron.daily/99loganalyzer-expire

real    1m16.391s
user    0m0.012s
sys     0m0.008s

How can I pass in the credentials for this mysql user so they will work in a cronjob?

share|improve this question
    
append > /tmp/loganalyzer.log 2>&1 to the end of line to see what happens. –  quanta Sep 6 '12 at 12:13

2 Answers 2

up vote 3 down vote accepted

Putting login credentials into a command line like this is risky, as anyone who can view the process list could view those credentials. A better option is to put those credentials into an option file such as ~/.my.cnf and then reference that file in your command.

[client]
user="loganalyzer"
password="loganalyzerpassword"

Make sure to chmod the permissions to 0600 on that options file to prevent it from being viewable by anyone except the owner. Then your mysql command would be:

echo $SQL | /usr/bin/mysql --defaults-file=/root/.my.cnf Syslog

More details on option files can be found at http://dev.mysql.com/doc/refman/5.6/en/option-files.html.

share|improve this answer
    
This is, indeed, a better way to do it (I'm already using it for my Mysql backup process). Don't know why I didn't do it myself. It works through regular cron, I'll mark this as correct if it runs out of cron.daily correctly. –  David Mackintosh Sep 6 '12 at 14:15
1  
Another reason to use ~/.my.cnf : When password changes, you just have to update 1 file, not a bunch of crontab/scripts (Murphy said your coworkers will always forget to update one entry : The script that performs backups). –  coincoin Sep 6 '12 at 15:23

You need to use a different command.

Something like this should work:

#!/bin/bash
mysql -uUSERNAME -pPASSWORD -e "DELETE FROM SystemEvents WHERE ReceivedAt < DATE_SUB(CURDATE(),INTERVAL 30 DAY);" syslog

Might need to be adapted for your needs, and I am not 100% sure how the DB is selected.

share|improve this answer
    
The problem appears to be the password getting recognized, not the command getting executed. How does changing the invocation (from --password=$foo to -p$foo change that? –  David Mackintosh Sep 6 '12 at 14:09
    
Hard to tell - but I know that a command like that works in a cron. Might have something to do with you echoing it through a pipe - I don't know. –  Frederik Nielsen Sep 6 '12 at 14:47

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.