0

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.

1
SET PASSWORD FOR 'root'@'localhost' = PASSWORD('MyNewPass');

... is the wrong command. In 2015 that column was deprecated and replaced by a column "authentication_string".

It should be

UPDATE user SET authentication_string=PASSWORD('MyNewPass') where USER='root'; 
| improve this answer | |
1

What you need to do after you skip-grant-tables is connect to your database and use the mysql database.

So completly it would be:

sudo systemctl stop mysql.service
sudo mysqld_safe --skip-grant-tables --skip-networking &
mysql -u root
use mysql;
FLUSH PRIVILEGES;
UPDATE user SET authentication_string = PASSWORD('newpass') WHERE User = 'root' AND Host = 'localhost';
\q
sudo killall mysqld
sudo systemctl start mysql.service
| improve this answer | |
  • I tried this. It worked without errors, but afterwards Access denied for user 'root'@'localhost' when trying to log in with the password just set remains. – Matthias Ronge Jul 17 '17 at 10:09
0

You'll still need to use sudo mysql -u root -p to login under the new credentials (unless you change the plugin type for the root user...)

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.