mysqldump connects as different user from cron and from command line

Question

I had a bug in a backup script running as a cron job which I found and corrected, but I'm still puzzled by the fact that this bug did not show up when the script was started from the command line. The script itself is started as a root and at some moment it starts another script as another user (actually as 'mysql')

su another_user -c "/some/path/another_script"

the another_script in turn does the db dump at some point:

mysqldump db_name > file.sql

This instance of mysql has no root password for localhost, so running this command as a root always works. What I don't understand is that it still works if I start this script from the command line, although this command is called not by 'root' but by 'mysql' user. The db logs show that the connecting user is still 'root'. Whereas when the script is started as a cron job it fails as expected with mysql logs showing that the connecting user was 'mysql'.

The problem actually can be reproduced as simple as that:

su mysql -c "mysqldump db_name > file.sql"

unexpectedly connects as a root db user if started from command line as a root and expectedly connects as mysql db user if started as a root cron job.

Answer 1

No mater who call the command if the user is on local machine.

If you allow root access to %@localhost you can connect to the mysql server with whatever user you have.

Example: If you're logged with user test1 you can still connect to the local mysql with user root.

user1@machine~$ mysql -h localhost -u root

or

user1@machine~$ mysqldump -h localhost -u root mydatabase > backup.sql

Check your backup script that you're using. There is the important things.

If you need to run the script with different user you may use this example:

su - user1 -c "/path/scripts/backup.sh"

Answer 2

The reason you are experiencing two different behaviors is cron does not run in a shell so all the environmental variables that you have access to in a shell are missing from cron. If you want cron to behave as it does in your shell you need to invoke it in a shell env. For bash that would be:

/bin/bash -l -c 'your command'