How to set the root user password?

[ivytony]

First of all, I'm not sure if I've set up root password for MySQL server or not. I was following this tutorial http://www.howtoforge.com/centos-5.1...p-ispconfig-p4 to install mysql on my CentOS 5 server.

When I run 'mysqladmin -u root password yourrootsqlpassword', I got this error message:

mysqladmin: connect to server at 'localhost' failed
error: 'Access denied for user 'root'@'localhost' (using password: NO)'

Then I go to MySQL prompt running ' mysql -uroot -pPassword' (Password is replaced by actual password)

When I am on MySQL prompt, I run

mysql> select user, host, password from user where user='root'; and I saw there are three root users in the user table:

+------+-----------+-------------------------------------------+
| user | host      | password                                  |
+------+-----------+-------------------------------------------+
| root | localhost | *FSDFSAFEFSD124521FSDFSFZFD14584FZZFE22F |
| root | li27-127  | *FSDFSAFEFSD124521FSDFSFZFD14584FZZFE22F |
| root | 127.0.0.1 | *FSDFSAFEFSD124521FSDFSFZFD14584FZZFE22F |
+------+-----------+-------------------------------------------+

From this output, can I say that my root user(s) already has password?

Should I have only one root user for 'localhost' and delete the other two from the user table?

Thanks!

[coolsti]

When you first install MySql on Centos (at least for Centos 4.5 and lower) I think your root does not initially have a password.

Anyway, the way I log on to mysql in a console window is as follows:

mysql -u root -p [databasename]

where the database name in the brackets is optional.

When you use the above, you are then prompted for your password. This is easier if your password has special symbols in it which would maybe confuse putting it after the -p on the mysql command line.

Your three rows for the root user are for root "coming from" three different places. The first and last are probably equivalent, as 127.0.0.1 should be the same on your machine as "localhost". Be careful in deleting any of them, if you don't really know which one should be deleted! I am not sure where mysql thinks you are "coming from" when you log on via a console window.

And yes, the fact that there are entries in the password field does indicate that there is a password set up for the root user in all three cases. This is not the actual password that you would type in, but an encrypted version that you get when you use the mysql password() command.

I cannot remember the command, but you can set or reset your password from within mysql in the console window. Take a look at the documentation. Something like set password etc.

[Atli]

I cannot remember the command, but you can set or reset your password from within mysql in the console window. Take a look at the documentation. Something like set password etc.

SET PASSWORD FROM 'user'@'host' = PASSWORD('newPassword');
 

Just for the record :)

P.S.
I changed the thread title to make it a little clearer.
Please avoid using phrases like "please help me" in thread titles.
Check out the Posting Guidelines for tips on how to make good thread titles.

Thanks.
MODERATOR