Mysql root not working after running mysql_secure_installation

Keywords: LAMP/MAMP/WAMP - AWS - Technical issue - Services (Apache, MariaDB, MySQL…)

bnsupport ID: aa8dc0dc-f583-5c19-5eb8-b21e6fdb222d

bndiagnostic output:

? Resources: Found possible issues

bndiagnostic failure reason: The issue it found has nothing to do with my problem.

Description:
Ran mysql_secure_installation and it made my root login not work. (see Screenshot)

This was a completely fresh LAMP instance.


Hello @jporozynski,

It seems using that tool provoked issues in the past. Because of that, we created a guide to run the same steps the tool does so that we can secure MariaDB. Could you please check out the following guide:

https://docs.bitnami.com/aws/infrastructure/lamp/administration/secure-server-mariadb/

Is there anyway to undo the chaos that tool made?

Hello @jporozynski,

I’m not sure what other changes the tool could have made but to recover your access, you can try modifying the password for the root user as explained in this guide:

https://docs.bitnami.com/aws/infrastructure/lamp/administration/change-reset-password-mariadb/

Regards,
Francisco de Paz

I ended up making a new LAMP instance then only deleting the test database. Anything else i do in that article locks the root out.

Hello @jporozynski,

Thanks for reporting this. I have been able to reproduce this and it seems there is an issue disallowing root login remotely. It seems that the root user present is configured to be accessible from anywhere:

MariaDB [(none)]> SELECT host, user FROM mysql.user;
+------+------+
| host | user |
+------+------+
| %    | root |
+------+------+
1 row in set (0.000 sec)

In this case, we can just alter the host from which root can connect:

MariaDB [(none)]> UPDATE mysql.user SET Host='localhost' WHERE Host='%' AND User='root';
MariaDB [(none)]> UPDATE mysql.db SET Host='localhost' WHERE Host='%' AND User='root';
MariaDB [(none)]> FLUSH PRIVILEGES;
MariaDB [(none)]> SELECT host, user FROM mysql.user;
+-----------+------+
| host      | user |
+-----------+------+
| localhost | root |
+-----------+------+

That should solve the issues and disable remote login for root.

As a side note. There is no blank '' user by default, so it’s not necessary to run the user deletion command:

MariaDB [(none)]> SELECT User FROM mysql.user;
+------+
| User |
+------+
| root |
+------+

Regards,
Francisco de Paz

Maybe running that command for ’ ’ is what made it fritz out?

Hello @jporozynski,

It should have only run the command without any changes to the database but yes, that could have been the issue.

Regards,
Francisco de Paz