9,177
2018-07-21 00:02:33
You can reset the root password by running the server with --skip-grant-tables and logging in without a password by running the following as root (or with sudo):
# service mysql stop
# mysqld_safe --skip-grant-tables &
$ mysql -u root
mysql> use mysql;
mysql> update user set authentication_string=PASSWORD("YOUR-NEW-ROOT-PASSWORD") where User='root';
mysql> flush privileges;
mysql> quit
# service mysql stop
# service mysql start
$ mysql -u root -p
Now you should be able to login as root with your new password.
It is also possible to find the query that reset the password in /home/$USER/.mysql_history or /root/.mysql_history of the user who reset the password, but the above will always work.
Note: prior to MySQL 5.7 the column was called password instead of authentication_string. Replace the line above with
mysql> update user set password=PASSWORD("YOUR-NEW-ROOT-PASSWORD") where User='root';
@Benjamin I did all the steps in your answer but I still get the Access denied for user 'root'@'localhost' - any ideas why?
For MySql 5.7: UPDATE mysql.user SET authentication_string = PASSWORD('MyNewPass'), password_expired = 'N' WHERE User = 'root' AND Host = 'localhost';
After trying all of this many times, and the
dpkgapproach below, and getting super frustrated that nothing worked (btw, I'm using 16.04 withmysql-sever-5.7), I noticed that mysql would accept my new credentials if I used sudo to connect. i.e.sudo mysql -u root -pWithout sudo I getAccess denied for user 'root'@'localhost'. I've never had to use sudo before, and I don't see it mentioned here, but after a frustrating hour that seems to be the solution.@Matt same here. I've never had to use
sudobefore when using mysql and now I have to? I don't understand why I have to be the root linux user to access the root account in mysql. This sort of behaviour reminds me of postgresql a bit more.For anyone only able to login as shell root, look at this askubuntu.com/questions/766334/…