14

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?

6 Answers 6

12

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.

Update (2016-06-29) If you are running mysql 5.6.6 or greater, you should look at the mysql_config_editor tool that allows you to store credentials in an encrypted file. Thanks to Giovanni for mentioning this to me.

5
  • 1
    That method still requires an unencrypted password in plain-text to be on the system. That is what i'm trying to avoid.
    – Mech
    Feb 8, 2010 at 17:51
  • >you either don't have a root password set, or that you have a configuration file that is not being found by your script. Author clearly states that there is a mysql password for root, and that he is trying to access the database without supplying it to the mysqldump command: "(using password: NO)". Hence access denied error.
    – monomyth
    Feb 8, 2010 at 17:53
  • 1
    @monomyth, the author also states that he can login without a password while interactively logged in as root. This tells me something is not right.
    – Zoredache
    Feb 8, 2010 at 18:01
  • 2
    @Mech Software, There isn't much point of trying to protect yourself from the root account. If someone has the root account they could simply restart the mysql and bypass the permission system completely.
    – Zoredache
    Feb 8, 2010 at 18:24
  • 1
    The reason I was able to login was the root account had a .my.cnf specified with 600 permissions, so thus that is how the shell was getting access. cron however, must be ignoring the file so I used the parameter in this post to point to it.
    – Mech
    Feb 8, 2010 at 18:37
3

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.

3
  • 1
    I guess what I still don't understand is that If I can type (from the root prompt) mysqldump with no password input, why can't I run it through the cron job the same way. What piece is missing that requires the -p parameter. I'm not trying to "obscure" the password, I'd just rather never have it entered. Something particular to the root login itself is allowing the access, so why cant this be replicated with cron?
    – Mech
    Feb 8, 2010 at 17:56
  • If you see that you can login without a password and with a password using the same "root" user, it is possible that there are multiple root users in your mysql database. Some of them might not have password set. You can remove password from the 'root'@'localhost' but then not only cron will be able to connect to your database, but anyone with a local account. This is not different (or worse) than having a password in a clear text script.
    – monomyth
    Feb 8, 2010 at 18:04
  • I think the point is that MechSoftware was making is that being root essentially gives you read access to database files, and they are not encrypted. Therefore, requiring root MySQL password from the root user only to pretty print the data they essentially already can access is "security through obscurity". Besides, permissions are very easy to mess up, e.g. if someone is setting them recursively for a directory, if there's a symbolic link to the file etc.
    – Septagram
    May 14, 2014 at 10:27
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
1

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.

2
  • 1
    I found this was the case of HOW I was able to us mysqldump without the password. So this solved the mystery of how the SHELL is doing it, so why is cron not reading it?
    – Mech
    Feb 8, 2010 at 18:32
  • 1
    mysqldump when run from cron may not be able to locate the .my.cnf file. The remedy is to add a mysqldump parameter to explicitly read the options in the.my.cnf file, i.e. --defaults-extra-file=/root/.my.cnf I learned this from two other answers on Stack Overflow. See stackoverflow.com/a/602054/854680 and stackoverflow.com/a/890554/854680
    – MikeOnline
    Dec 16, 2015 at 19:50
1

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.

0

Although several of these responses are helpful several are confusing because the unix root user and the mysql root user are not the same and basically have no relationship other than they both use the login name 'root'. Maybe that's obvious but it seems some responses conflate the two.

What might be a useful option (maybe it exists?) to mysqld would be to allow client programs such as mysql or mysqldump etc running as unix root to access mysqld's root@localhost without a password without having to store root@localhost's (mysql) password in a my.cnf file or similar.

I know that makes some nervous but the reasoning is anyone running as local (to the mysqld server) unix root can by-pass mysqld's security anyhow, quite easily. And having a my.cnf with the mysqld root password 7x24 or even creating/deleting a my.cnf with the mysql root password (where does that password come from?) on the fly (e.g., to do a mysqldump) makes me nervous.

It would require some infrastructure and thinking because one would have to trust the mysql/mysqldump/etc to transmit to mysqld that it really believes it's being run by a local unix root account.

But for example limiting to mysqld's unix socket only, no TCP, could help, at least as a strongly recommended option of this option. That could establish that the client is running locally tho that's probably not quite enough. But it could be a start of an idea. Perhaps sending a file descriptor over a unix socket could be another piece (google it if that sounds like crazy talk.)

P.S. No I'm not going to try to brainstorm right here how any of that might work on a non-unix operating system tho the idea probably translates to other OS's.

4
  • 1
    Putting the credentials in /root/.my.cnf with proper permissions neatly solves the problem. Oct 2, 2016 at 23:54
  • I disagree, now anyone who can get access to that file, including via some other system flaw, has the mysql root pw. And it's there 24x7 to be attacked, and in a fixed file location (tho admittedly it wouldn't have to be in /root.) But for example whoever runs file system dumps, or can get access to one, can pull it out of a file system dump file. In that case it sounds like a temptation for a disgruntled employee. I think it's a reasonable goal that no cleartext passwords exist on a system, anywhere. Oct 4, 2016 at 19:36
  • Maybe so, but the proposal you've made is much worse, since it allows any local user to trivially pretend to be root. Oct 4, 2016 at 21:00
  • No, my proposal was they already have to be unix root on the local server in which case the local mysqld trusts the mysql or mysqldump etc (client) being run as unix root and allows them to proceed as if they'd authenticated as mysql root. As I said if they're already unix root local to the server they can by-pass the mysql root password anyhow with a few well documented commands ("mysql root password recovery"). Oct 5, 2016 at 5:25

You must log in to answer this question.

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