Linux – How to make thesql recognize root user after install

linuxMySQLuser-accounts

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?

Best Answer

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!

Related Topic