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

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

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

First check in /root/.my.cnf

below is permission

-rw-------.  1 root root       39 May 22 17:09 .my.cnf
cat /root/.my.cnf

[client]
password="p@ssw0rD&"
user=root

 

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> use mysql;  ##note##optional

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!