Learning MYSQL, following steps from a book and have run into an error

[Juddy58]

Hello im trying to learn mysql, im currently following some instructions from a mysql book I have.
I originally had MySql installed the other day and was playing around with the password, since then i have reinstalled MySQL. Now im trying to set a root user password. To do so I have done the following steps.

Firstly, I went into command prompt and went into the mysql\bin directory
I then entered the command:
mysqld
to start the server and received no error messages it just went to the next line of the prompt:
C:\mysql\bin>

Next I changed the password to “password” for the root user, using:
mysqladmin -u root password "password"

Just like before I received no error message, it just went to the next line of the prompt:
C:\mysql\bin>

To ensure MySQL registered this change, I entered:
mysqladmin -u root reload

Just like before I received no error message, it just went to the next line of the prompt:
C:\mysql\bin>

Where im having a problem is when I try to test my password by asking mysql for its current status:
mysqladmin -u root -p status

I am given the following prompt:
Enter Password:
If I enter the password I have previously set: password

I get the following error message:
Mysqladmin: connect to server at ‘localhost’ failed
Error: ‘Access denied for user: ‘root@127.0.0.1’ (Using password: YES)’

from the error message i assume it is thinking im trying to log in with the wrong password.
Just wondering why this is occuring and if anyone has any solutions, suggestions!
Any help appreciated
Thanks!
Justin

 

[likelylad]

When you at the Enter Password Stage, if you just hit the return button (ie don't enter any password), what happens????

 

[abombss]

Try logging into the server, either with a password or without. Then type the following commands

\u mysql
select user, host, password from user;

If your root user is setup properly you will see something like. The below example allows my root user to log in from only to hosts, the local host and my workstation einstein. The password is encrypted for security.

+---------+------------------+-----------+
| user    | password         | host      |
+---------+------------------+-----------+
| root    | 3a57e0d265a632cc | localhost |
| root    | 3a57e0d265a632cc | einstein  |

However, if there is no password for your root user it may look like this.

+---------+------------------+-----------+
| user    | password         | host      |
+---------+------------------+-----------+
| root    |                  | localhost |

This means that trying

mysql -u root -p

will throw an error because there is no password for root.

I know have probably read this already, but check out this page and re-read it. Also check out the other pages for adding users.

http://www.mysql.com/doc/en/Default_privileges.html

BTW --

If you see a line like this when you run the above query,

+---------+------------------+-----------+
| user    | password         | host      |
+---------+------------------+-----------+
|         |                  | localhost |
|         |                  | %         |

Get rid of this, this means that anyone without a user name or password can get into your system. Try

delete from mysql.user where user='';

Hope this helps.

abombss

 

[toomee]

try this
mysql -u root -pstatus
or
mysql -u root --password=status

 

[Juddy58]

Thanks for the replies,
if i get to the password prompt and just press enter it works fine. Thus telling me i havent set the password.
however if i run the query:
select user, host, password from user; // i end up with
+-----------+--------------+-------------------+
| root | localhost | |
| root | % | 878789789679d878 |
| | localhost | |
| | % | 4382382382383289 |
+-----------+--------------+-------------------+
i am guessing that this is telling me that i have created 4 different users while i have been stuffing around. I will have a look at the suggested link and have a play around and see how i go.
Thanks for the help everyone!

 

[liuwt]

Hi,

i see that there is no password for the root if you connect from localhost. Maybe you should change it.

Also, accounts with blank usernames are risky for system security, I would delete them.

Greetings

--
Smash your head on keyboard to continue...

 

[Juddy58]

thanks every one, i have run a delete query to delete the users with no user names it seems to be fine.
One last thing: when i run the query "select User, Host, password from user;
i end with two records:

User Host password
+------------+-------------+------------------------+
| root | localhost | 00850603543344c3 |
| root | % | 03030404494494949 |
_____________________________________________________
im just wondering what the second record means with the host name of: % Can i just delete this user as well or is it some kind of default user i should leave.
Once again thanks every one.

 

[abombss]

The % is used as wild card chacter for a fuzzy match just as its used in the

LIKE

statement. This means that root can connect from any host.

If your domain was foobar.com and you only wanted connections from that domain you could say %foobar.com.

abombss

 

[Juddy58]

Thanks mate, i ran a delete query to delete this user, so i only have my root user on my localhost server with a password.
Cheers!