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

Answer 1

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

Answer 2

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.

Answer 3

sudo dpkg-reconfigure mysql-server-5.5

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

Answer 4

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

Answer 5

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;
   

Answer 6

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