Which is the easiest way to reset the mysql root password?

Question

In the past there has been sudo /etc/init.d/mysql reset-password, (1) is manually restarting and setting the password with an sql command again required? (2)

(1) http://www.ubuntugeek.com/reset-the-root-password-on-mysql.html (2) http://www.howtoforge.com/reset-forgotten-mysql-root-password

second link look like the way to go – Panther Dec 23 '11 at 05:25 — Panther, Dec 23 '11 at 05:25

score 6 · Answer 1 · answered Dec 23 '11 at 06:18

6

There's also an administrative user equivalent to root: debian-sys-maint. You can use this account to reset root's password. You can find its password in /etc/mysql/debian.cnf

answered Dec 23 '11 at 06:18

tumbleweed

8,026

score 5 · Answer 2 · edited Oct 05 '15 at 15:23

5

You could create a sql file say /root/mysql.reset.sql with the content:

UPDATE mysql.user SET Password=PASSWORD('yourpassword') WHERE User='root';
FLUSH PRIVILEGES;

And just call:

mysqld_safe --init-file=/root/mysql.reset.sql

Will be very helpful if you are in a habit of forgetting passwords often.

edited Oct 05 '15 at 15:23

snoop

4,040
9
40
58

answered Dec 23 '11 at 06:29

tamilsweet

151
3

score 1 · Answer 3 · answered Jun 10 '12 at 16:54

1

sudo dpkg-reconfigure mysql-server-5.5

you can use tab complete after mysql-server- if you're using a different version of mysql.

answered Jun 10 '12 at 16:54

Colin Pickard

195

score 1 · Answer 4 · answered Feb 09 '17 at 10:21

tumbleweed's answer was the only one that helped me. I was dealing with what could possibly have been a corrupted root user and none of the standard methods resolved the issue of not being able to log in as root.

Using the debian-sys-maint user, I was able to finally log into MySQL with escalated privileges and even then resetting the root password did not work.

However, I was able to recreate the root user:

DROP USER 'root'@'localhost';
CREATE USER 'root'@'localhost' IDENTIFIED BY '<newpassword>';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION;
FLUSH PRIVILEGES;

... and finally all was right with the world again!

I hope that helps a desperate soul out there...

score 0 · Answer 5 · edited May 14 '18 at 13:08

0

sudo /etc/init.d/mysql stop
sudo mkdir /var/run/mysqld/
sudo chown mysql /var/run/mysqld/
sudo mysqld_safe --skip-grant-tables &
sudo mysql -u root
use mysql;
update user set authentication_string=PASSWORD("New_Passwore_Here") where User='root';
flush privileges;
exit;
sudo /etc/init.d/mysql stop
sudo /etc/init.d/mysql start

edited May 14 '18 at 13:08

derHugo

3,356
5
31
51

answered May 14 '18 at 11:47

samson mwanzia

1

Hello and welcome to AU. Your answer might be correct but should be documented: it is not recommended to just give an instruction list. In this case, I do not see a problem but, in general, if a user mistypes something, it could lead to unwanted results. You also use 'sudo' even when it is not necessary (sudo mysql...) which is potentially dangerous. – Marc Vanhoomissen May 14 '18 at 12:02

score 0 · Answer 6 · edited Feb 09 '17 at 11:30

0

Stop the MySQL Server.
```
sudo /etc/init.d/mysql stop
```
Start the mysqld configuration.
```
sudo mysqld --skip-grant-tables &
```
Login to MySQL as root.
```
mysql -u root mysql
```

Replace YOURNEWPASSWORD with your new password!

UPDATE user SET Password=PASSWORD('YOURNEWPASSWORD') WHERE User='root'; FLUSH PRIVILEGES; exit;

edited Feb 09 '17 at 11:30

Zanna

70,465

answered Feb 09 '17 at 10:45

Chetan Rathod

1

Which is the easiest way to reset the mysql root password?

6 Answers6

Linked

Related