Is there another, better forum to post this? If even just 10% of new
MySQL users are running into this problem, it would save a lot of
person-hours around the world to change the instructions that are
displayed to new users when starting MySQL for the first time.
-Bennett
At 12:51 AM 9/15/2009, Bennett Haselton wrote:
When I install and start the MySQL server on a new machine, it outputs:
>>>
PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
To do so, start the server, then issue the following commands:
/usr/bin/mysqladmin -u root password 'new-password'
/usr/bin/mysqladmin -u root -h sls-ce12p12.dca2.superbservers.com
password 'new-password'
>>>
However if I try executing both of these commands as the
instructions say, I get an error on the second command (in the text
below, I have replaced my sample password with 'america'; I wouldn't
really use that as my password):
>>>
[r...@sls-ce12p12 ~]# /usr/bin/mysqladmin -u root password 'america'
[r...@sls-ce12p12 ~]# /usr/bin/mysqladmin -u root -h
sls-ce12p12.dca2.superbservers.com password 'america'
/usr/bin/mysqladmin: connect to server at
'sls-ce12p12.dca2.superbservers.com' failed
error: 'Access denied for user 'root'@'localhost' (using password: NO)'
>>>
Knowing now what the syntax means, it seems clear why the second
command fails. The first command executes with no authentication
required because no password has been set for the root user
yet. But by the time you execute the second command, the root user
password has already been set, so if the MySQL server sees the
second command as an attempt to set the 'root'@'localhost' password,
it will fail because you're not authenticated.
Now, I experimented some more on different machines, and on some
machines, the second command gives an error and on other machines it
doesn't. On all new mysql installations, there are separate entries
created in the mysql 'user' table for 'root'@'localhost' and
'root'@'hostname', where "hostname" is the hostname of the
machine. The deciding factor that determines whether or not the
second mysqladmin command gives an error, appears to be, whether
"hostname" on that machine resolves to 127.0.0.1, or resolves to the
public IP address of the machine instead.
IF "hostname" RESOLVES TO 127.0.0.1:
then when you run the command:
/usr/bin/mysqladmin -u root -h sls-ce12p12.dca2.superbservers.com
password 'america'
the MySQL server sees you connecting to IP address 127.0.0.1. So,
rather than trying to update the
'root'@'sls-ce12p12.dca2.superbservers.com' password, it thinks
you're trying to update the 'root'@'localhost' password. Of course
this password has already been set by the previous command, so the
second command fails.
IF "hostname" RESOLVES TO THE PUBLIC IP ADDRESS INSTEAD:
then when you run the command:
/usr/bin/mysqladmin -u root -h sls-ce12p12.dca2.superbservers.com
password 'america'
the MySQL server sees you connecting to the public IP address,
resolves that to the public hostname, and thus thinks you are trying
to update the 'root'@'sls-ce12p12.dca2.superbservers.com' entry in
the user table. And it allows that, because that password is still blank.
So for all users who are in the former situation (where "hostname"
resolves to 127.0.0.1), it appears that trying to run the second
mysqladmin command will give them an error. It's not a catastrophe
because at that point the biggest security hole -- the blank root
password -- has been fixed. But it's still inadvertently training
the user that security instructions are sometimes unreliable, or
that sometimes in order to get things working, you have to skips
parts of the security instructions and cut corners (which could be a
bad habit if they ever end up skipping over something
important). Plus, if an unprivileged user ever did figure out how
to connect as 'root'@'hostname', they would be granted full MySQL
root privileges, since that account is still unprotected.
The upshot of all this is: Would it be a good idea for the MySQL
installation program, rather than always telling the user:
>>>
PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
To do so, start the server, then issue the following commands:
/usr/bin/mysqladmin -u root password 'new-password'
/usr/bin/mysqladmin -u root -h sls-ce12p12.dca2.superbservers.com
password 'new-password'
>>>
to instead test whether 'hostname' resolves to 127.0.0.1, and if it
does, to skip giving the user the second command? Or to give the
user some other command that would set the 'root'@'hostname'
password without giving an error?
-Bennett
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To
unsubscribe: http://lists.mysql.com/mysql?unsub=benn...@peacefire.org
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
