Mysql – Cronjob script won’t pass in thesql password

cronMySQL

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?

Best Answer

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.

Related Topic