root can’t connect remotely to MySQL 5.5

This drove me absolutely nuts. I recently installed CentOS 7 with the typical LAMP configuration.

Also, I know. I shouldn’t be using root. I am because it doesn’t matter in this case – it’s not a production machine.

I installed MySQL 5.5 and ran the secure installation (mysql_secure_installation).

I made sure selinux and firewalld were temporarily turned off just to make it easy to get connected, then turn them back on.

root is getting denied access. I go and check permissions. root has everything it needs, so what is wrong?

I finally figured it out.

+------+--------------------------+-------------------------------------------+
| user | host | password |
+------+--------------------------+-------------------------------------------+
| root | localhost | *ABC123 |
| root | ::1 | *ABC123 |
| root | % | *DEF345 |
+------+--------------------------+-------------------------------------------+

The root password for % access was not the same as everything else.

So:

mysql> select user, host, password from mysql.user;

Review the output and check that all the passwords are the same.

mysql> use mysql;
mysql> update user set password=PASSWORD("ADD-NEW-PASSWORD-HERE") where User='root';

Check passwords:

mysql> select user, host, password from mysql.user;

Then:

mysql> flush privileges;

Voila!

Argh!

 

Add a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.