Passwordless mysqldump via shell script in /etc/cron.daily

I'm aware that there are dozens of questions similar to this, but it seems none has a definitive answer to my problem, so that's why I'm posting this... I hope in the right place.

The problem:

I have a script placed in /etc/cron.daily that performs a daily database backup among the other things. It works fine as long as there is a password hardcoded into the script for the mysqldump command.

#!/bin/sh
$ mysqldump -u [uname] -p[pass] db_name > db_backup.sql

However, not wanting to have the password in the script, I've set up ~/.my.cnf file (chmod 600) with my user's password stored there so the mysqldump command in the script would be passwordless.

~/.my.cnf    
[mysqldump]
password="pass"

#!/bin/sh    
$ mysqldump -u [uname] db_name > db_backup.sql

When I run this new script manually from the command line as root it works like a charm.

sudo sh /etc/cron.daily/daily-backup-script

But when cron wants to run it it's unable to dump the database giving the following error:

mysqldump: Got error: 1045: Access denied for user 'user'@'localhost' (using password: NO) when trying to connect.

So, I assume cron doesn't have appropriate privilege to perform the passwordless mysqldymp command in the script, with password placed in ~/.my.cnf, however the script and the passwordless mysqldump command IN IT are working flawlessly from the command line with sudo.

Effort so far:

• I've tried sudo in front of the mysqldump command in the script.

• I've tried sudo -u user in front of the mysqldump command in the script.

• I've chown-ed the ~/.my.cnf file as root:root.

Solved with the help of this answer.

cron doesn't know the path to ~/.my.cnf, so as per the MYSQL documentation you need to specify the path to the .my.cnf file.

The proper passwordless mysqldump command in the shell script triggered via cron should be:

mysqldump --defaults-extra-file=/path/to/.my.cnf -u [uname] db_name > db_backup.sql

Works like a charm.

StackExchange Database Administrators Q#183920, answer score: 7