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!