Cfrgtkky

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

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

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.

sudo dpkg-reconfigure mysql-server-5.5

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

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

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;
   
   

   
   

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