I am trying to regain root access to the MySQL database running on an Ubuntu instance (LTS 16.04.2). Problem situation is: I can start and stop MySQL using
/etc/init.d/mysql but I cannot log in with neither of
mysql -u root mysql -u root -p
The latter with empty password. I have an empty record in my password store. I believe to remember that login was possible without password, but maybe also I just forgot to save my password store properly. Maybe also the system is compromised. However, since I have root access, it should be possible to reset the password.
I decided to start MySQL server using
sudo -u mysql /usr/sbin/mysqld --skip-grant-tables --skip-networking
command and can now connect with the client. As per tutorial, I have to issue the two commands
FLUSH PRIVILEGES; SET PASSWORD FOR 'root'@'localhost' = PASSWORD('MyNewPass');
to set a password. When issuing the second command, I get the following warning on the console where I started the server:
[Warning] 'user' entry 'root@localhost' has both a password and an authentication plugin specified. The password will be ignored.
I do not remember to have set up any plug-in ever. Is this Ubuntu default? Where is my MySQL password stored then, and how do I change it? Or how do I disable that plug-in I do not know about.