8

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

1
  • 2
    second link look like the way to go – Panther Dec 23 '11 at 5:25
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

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.

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.

1

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...

0
  1. Stop the MySQL Server.

    sudo /etc/init.d/mysql stop
    
  2. Start the mysqld configuration.

    sudo mysqld --skip-grant-tables &
    
  3. Login to MySQL as root.

    mysql -u root mysql
    
  4. Replace YOURNEWPASSWORD with your new password!

    UPDATE user SET Password=PASSWORD('YOURNEWPASSWORD') WHERE User='root'; FLUSH PRIVILEGES; exit;
    
0
  1. sudo /etc/init.d/mysql stop
  2. sudo mkdir /var/run/mysqld/
  3. sudo chown mysql /var/run/mysqld/
  4. sudo mysqld_safe --skip-grant-tables &
  5. sudo mysql -u root
  6. use mysql;
  7. update user set authentication_string=PASSWORD("New_Passwore_Here") where User='root';
  8. flush privileges;
  9. exit;
  10. sudo /etc/init.d/mysql stop
  11. sudo /etc/init.d/mysql start
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

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.