Last night, I was running through the steps to install a LAMP stack following this Linode guide. Upon trying to secure the MySQL installation, I ran into the error I'm about to describe. Due to the deadline, I ended up re-installing MySQL from scratch and then creating a regular user to continue with WordPress installation.
Today, I attempted tu run sudo mysql_secure installation. When prompted for a new root password, I received the following error:
... Failed! Error: SET PASSWORD has no significance for user 'root'@'localhost' as the
authentication method used doesn't store authentication data in the MySQL server.
Please consider using ALTER USER instead if you want to change authentication parameters.
So I instead ran ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password by '<new_password>'; and, afterwards, tried running sudo mysql_secure_installation. This time, when prompted for Enter password for user root: and providing the password I just created, I receved the following error:
Error: Access denied for user 'root'@'localhost' (using password: YES) (which is exactly the same error I got last night).
Following similar questions, I've send that other users have been able to at least accessing their DBs through either sudo mysql -u root -p, sudo mysql -u root or sudo mysql. But regardless of the combination, I always get "ERROR 1045 (28000): Access denied for user 'root'@'localhost'". I'm essentially locked out MySQL and thus unable to run any kind of query, which is suggested by several answers around here to similar questions.
I'd prefer not having to reinstall it again, since I already configured a DB for WordPress: there's no data there yet, but its still a last resort. Besides, I still need to secure my MySQL installation
How can I recover access to MySQL, and what are the correct steps for securing my installation?
