Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I just installed MySQL 5.5 and right after install I was asked to set root password by executing those two commands:

/usr/bin/mysqladmin -u root password 'new-password'
/usr/bin/mysqladmin -u root -h linux-opxd password 'new-password'

or

/usr/bin/mysql_secure_installation

I executed first command -- OK. I executed second command this time with error:

error: 'Access denied for user 'root'@'localhost' (using password: NO)'

I re-executed the first one (for checking) -- error as above.

So, I executed mysql_secure_installation. Everything went fine and my password was recognized. So I executed the first two commands again -- and error again.

As another check:

mysql --user=root

The same error as above.

I tried resetting the password with mysql authentication, also I tried /usr/bin/mysql_upgrade -u root -p -- still the error while trying to launch mysql console as root.

So finally I turned authentication off, connected to console and executed this:

SELECT user();

The output -- root@. If I connect like this:

mysql --user='root@localhost'

I will get root@localhost@.

So for now it seems it is not the problem with privileges, or lack of password or anything like this, but no recognizing the user which was given at command line -- mysql appends @ at the end and this seems to be the problem (at least for my eye).

The question is: how to make MySQL recognize the given user?

share|improve this question

2 Answers 2

up vote 3 down vote accepted

The first error you have received:

error: 'Access denied for user 'root'@'localhost' (using password: NO)'

indicates that you probably did not use -p option in mysql command.

Once you have created mysql root password, you should be able to access MySql using:

mysql -u root -p

Where you will be prompted to enter MySql root password.

If you want to enable access from some other location (different from the localhost) you can grant privileges using SQL GRANT commands similar to this:

mysql> CREATE USER 'username'@'localhost' IDENTIFIED BY 'some_pass';
mysql> GRANT ALL PRIVILEGES ON databaseName.* TO 'username'@'localhost' WITH GRANT OPTION;
mysql> FLUSH PRIVILEGES;

On a similar fashion, you can enable MySql DB access from some other hostname/IP address or any location by changing 'username'@'localhost' to: 'username'@'someFQDN', 'username'@'IP_address', 'username'@'%'.

For additional information I'd recommend: http://dev.mysql.com/doc/refman/5.1/en/adding-users.html.

Cheers!

share|improve this answer
    
Thank you very much! I though I will be prompted for password, I was wrong. –  greenoldman Jan 20 '13 at 11:55

The reason you get the error message on the second mysqladmin command is because you don't authenticate with the server using the password you set in the first command. Simply include the '-p' on the second command, and input the password when asked for it. Or easier yet, just use the mysql_secure_installation command, as it takes care of it plus some other useful stuff.

share|improve this answer
    
Thanks you pointed out the problem correctly. In the regard to mysql_secure_installation I wrote I executed it as well. –  greenoldman Jan 20 '13 at 11:54

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.