error in instructions to set mysql root password?

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):
>>>
[root@sls-ce12p12 ~]# /usr/bin/mysqladmin -u root password 'america'
[root@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=gcdmg-mysql-2@m.gmane.org

Re: error in instructions to set mysql root password?

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):
> >>>
>[root@sls-ce12p12 ~]# /usr/bin/mysqladmin -u root password 'america'
>[root@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=bennett@peacefire.org
>


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org