Forget MySQL Root Password

It is normal to forget password when you have to remember so many diff and type password..
For MySQL, there are few way you can reset the root password..
But the main condition is.. you must have access to the physical server..
It can't be reset through mysql connection..

Here I will use 1 simple way which I prefer the most.

1. Check which mysql daemon need to reset. At here.. I want to reset mysql port 3306 which using the /etc/my5.cnf config file. Use command "netstat -ntulp" to check the running process and port.


2. Check the mysql /etc/my5.cnf daemon PID. Use command "ps aux | grep mysqld".


3. After identify the pid. Kill the process with "kill -9 db_pid".


4. Double check the mysql port 3306 had been killed or not.


5. Start the mysql with additional parameter "--skip-grant-tables".. which the command will be "mysqld_safe --defaults-file=/etc/my5.cnf --skip-grant-tables --user=root &".


6. Enter the mysql port 3306. It can be access without any password.


7. Now update the root password with new password with command "UPDATE mysql.user SET Password=PASSWORD('new_password') WHERE User='root';".
Then flush the privileges with "flush privileges".

8. Shutdown the mysql with "mysqladmin -uroot -h127.0.0.1 -P3306 shutdown". At here.. since the grant-tables privileges not open yet, shutdown also does not require password.


9. Start back the mysql without the "--skip-grant-tables" parameter.



That all. The MySQL root password had been reset. Here is some reminder.. After reset, remember to restart the mysql without the skip-grant-tables.

Do not let anyone have access to the physical DB server, to avoid DB daemon is restart without knowledge.