Using mysqldump in cron job without root password

Question

If I login with the root password on my box I can simply type

mysqldump --all-databases and I will get th expected "Dump".

I setup a job in cron.daily to run and dump this to a backup drive. The problem I have is that although the user is running as root I get the following message

mysqldump: Got error: 1045: Access denied for user 'root'@'localhost' (using password: NO)

when trying to connect. I do not want to hard code the mysql database root password in the script (who would).

Considering that I can just type "mysqldump" at the command line in my bash shell there must be someway to get around using the -u parameter.I already have #!/bin/bash at the top of the script.

What am I missing here to get this to not ask for the root password to the database?

Answer 1

In order to connect to the mysql server you must provide credentials. You can specify them in a configuration file, pass them via the command line, or simply create account that doesn't require credentials.

Of course the no-password option should never be used, the pass-by command line isn't great because anyone who can run ps may be able to see the command line.

The recommended option is to create a mysql configuration file with the credentials in it and then protect that file with filesystem permissions so only your backup user can access it.

You being able to login to the mysql server while logged in interactively as root seems to suggest that you either don't have a root password set, or that you have a configuration file that is not being found by your script. If you have a .my.cnf you may need to manually point to it. If your root account doesn't have a password set then I would strongly encourage you to fix that.

Answer 2

Security should not be done through obscurity. If you afraid that someone has access to your root account, it doesn't matter if root's mysql password is stored in the script, since you have all your data available in mysql dumps, or database files. So, the real question is what are you trying to protect?

If you don't want others to get a password that will let them change data in your database, you would need to create a user with appropriate permissions.

If you don't want that mysql password to be seen by any local account except root set file permissions on that script to be 0700 and owner to root.

Answer 3

Your shell use can do it becuase you have a shell to run it from, ie when you logon, all your shell scripts in your profile get run.

Cron does not have such luxuries. When it logs on (as root) it'll log on with a default shell. This prevents anyone from logging on remotely, but it also means there is no auto-login scripts that are run.

You can set a shell for cron to run under, edit the crontab and add the SHELL and HOME variables, eg.

SHELL=/bin/bash
HOME=/root

if these are not set, then cron will run with the shell and home directory specified in /etc/passwd (which are probably nothing, possibly /bin/sh).

If you want to see the environment cron is running as, add a cron job that exports env to a file, eg:

$crontab -e
* * * * * env > /tmp/crontabenv.log
:wq

Answer 4

Cron can be very frustrating to debug. When the cron jobs execute, they don't have an environment set like you take for granted with a shell.

Tips for cron:

• use full paths to everything -- "ECHO=/bin/echo", etc: (define variables at the top to make it easier)
• set MAILTO, so you get an email from each job (or have the jobs redirect stderr/stdout to a file)

If your root user can do it from the shell, cron should be able to do it. Make sure you explicitly specify the config file to use on the command-line.

Answer 5

If the script is run by root you could create a file /root/.my.cnf with permissions 600 and the following contents:

[client]
user = DBUSERNAME
password = DBPASSWORD

(where you enter your MySQL username and password of course).

This file will automatically be read by any mysql command-line tool, if it is run as root. No more need to provide it on the command-linea. The 600 permissions protect it against prying eyes.