I have forgot the password of MySQL’s admin user – how to recover?

If you have accidentaly locked out yourself, or just forgot your password for the admin (root) user of the MySQL, here is a quick-and-dirty solution.

First you have to shut down the MySQL server, next you need to start it in safe mode with no priviliges table loaded, update the password to a new one, and restart the database server just as before.

Step by step

1. Stop the database server:
$ sudo /etc/init.d/mysql stop
Stopping MySQL database server: mysqld.

2. Start in safe mode in the background (remember the PID! (29789 in my case as you can see)):
$ sudo mysqld_safe --skip-grant-tables &
[1] 29752
Starting mysqld daemon with databases from /var/lib/mysql
mysqld_safe[29789]: started

3. Now you can log in with the root user without password.
   $ mysql -u root
   Welcome to the MySQL monitor. Commands end with ; or g.
   Your MySQL connection id is 39
   Server version: 5.0.51a-6 (Debian)

   Type 'help;' or 'h' for help. Type 'c' to clear the buffer.


4. Set a new password, flush the privileges, and quit:
   mysql> UPDATE mysql.user SET password=PASSWORD('your-new-password') WHERE User='root';
   Query OK, 0 rows affected (0.00 sec)
   Rows matched: 3 Changed: 3 Warnings: 0

   mysql> FLUSH PRIVILEGES;
   Query OK, 0 rows affected (0.00 sec)

   mysql> QUIT
Bye


5. Now shut down the mysqld_safe what you have used (kill its PID):
$ sudo kill 29752
STOPPING server from pid file /var/run/mysqld/mysqld.pid
mysqld_safe[29869]: ended
[1]+ Done sudo mysqld_safe --skip-grant-tables

6. And you can start your mysql server normally again:
$ sudo /etc/init.d/mysql start
Starting MySQL database server: mysqld.


Original source is the Locked Out of MySQL topic on CentOS.org.