1

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?

1
  • append > /tmp/loganalyzer.log 2>&1 to the end of line to see what happens.
    – quanta
    Sep 6, 2012 at 12:13

3 Answers 3

3

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.

2
  • 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. Sep 6, 2012 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).
    – user130370
    Sep 6, 2012 at 15:23
1

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.

2
  • 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? Sep 6, 2012 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
    Sep 6, 2012 at 14:47
1

The order is wrong, the last line, what is before the pipe should be at the end of the line.

I mean, the SQL command is the last bit.

1
  • What exactly is wrong? This doesn't address that the same file works when invoked from the shell.
    – RalfFriedl
    Apr 4, 2019 at 20:20

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .