How to reset the MYSQL root password after error 1045 Cannot log in to the MySQL server

From DP

Jump to: navigation , search

How to reset the MYSQL root password.

The MYSQL root is a different account with a different password than the Linux root. This tripped us up when we recently installed Mantis bug tracker and got a #1045 Cannot log in to the MySQL server error from PHPMYADMIN and the command line MYSQL login.

Here's how we reset the password on a Debian Linux system from an SSH command prompt.

Procedure

sudo /etc/init.d/mysql stop  

First stop MYSQL. Any part of your site that uses the database will return an error, so be ready to proceded quickly.

sudo mysqld_safe --skip-grant-tables --skip-networking &

Start MYSQL in safe mode with with passwords and network access disabled.

  • --skip-grant-tables option disables password protection for the database
  • --skip-networking disables network access to prevent attacks while passwords are disabled
  • & runs the process in the background

You might have to hit space to return to the command prompt.

sudo mysql -u root

Login as root, a password is not required.

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

Run these SQL statements at the MYSQL> prompt. Don't type MYSQL>...

  1. First set the new password for the root user. replace new_pass with your password
  2. Flush the privileges to reset everything
  3. Quit the MYSQL prompt
sudo /etc/init.d/mysql stop

Now stop MYSQL again.

sudo /etc/init.d/mysql start

Start MYSQL in normal mode.

sudo mysql -u root -p

To test the new password login as root with the password (-p) option enabled.

That's it, you're done!