Unable to Login as root after mysql service restart

14.04 permissions login mysql

11,351

May be you need to reset mysql root password:

Change my.cnf configuration file:

[mysqld]

skip-grant-tables

Restart service, and login with root without password, then change the root password:

SET PASSWORD FOR 'root'@'localhost' = PASSWORD('your_new_password');

Remove skip-grant-tables entry from my.cnf and restart mysql.

11,351

Andrew Atkinson

Updated on September 18, 2022

Comments

Andrew Atkinson over 1 year
I installed mysql on Ubuntu 14.04 LTS like:
```
sudo apt-get install mysql-server libapache2-mod-auth-mysql php5-mysql
```
after completely removing mysql with:
```
sudo apt-get remove --purge mysql-server mysql-client mysql-common
sudo apt-get autoremove
sudo apt-get autoclean
sudo deluser mysql
sudo rm -rf /var/lib/mysql
```
Everything works fine until I restart the mysql service with:
```
sudo service mysql restart
```
Then I can no longer login with the root user and password. I get the error access denied for root@localhost using password yes

My only guess: is it possible there are two instances of mysql installed?

edit

All of the below are set as installfrom the output of:
```
dpkg --get-selections | grep -v deinstall | grep "mysql"

libapache2-mod-auth-mysql
libdbd-mysql-perl
libmysqlclient18:amd64
libmysqlcppconn7
mysql-client-5.5
mysql-client-core-5.5
mysql-common
mysql-server
mysql-server-5.5
mysql-server-core-5.5
mysql-utilities
mysql-workbench
mysql-workbench-data
php5-mysql
python-mysql.connector
```
- steeldriver almost 10 years
  
  Did you follow the instructions to set a root password during the reinstall? if not, you can run sudo dpkg-reconfigure mysql-server-5.5 at any time to do so.
- Andrew Atkinson almost 10 years
  
  I was prompted to add a root password, which worked once it was installed before the restart.
- AzkerM almost 10 years
  
  Post the output for dpkg --get-selections | grep -v deinstall | grep "mysql". Only possible way to see whether any other instance matching mysql. Meanwhile try mysql -u username -p & then type the password when it prompts.
- Andrew Atkinson almost 10 years
  
  post updated @AzkerM
Bombe over 9 years

This results in ERROR 1290 (HY000): The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement with mysql 5.5.38.
Ivan Cachicatari over 9 years

If you try with: UPDATE mysql.user set password = PASSWORD('your_new_password') where user = 'root' and host = 'localhost'
Jimbali about 7 years

I had to be logged in as root and not ubuntu when starting mysql.
Jimbali about 7 years

Also I had to use: update mysql.user set authentication_string=password('MyNewPass') where user='root';
Ivan Cachicatari about 7 years

Change the ubuntu password using the same sql.