If you have forgotten your root password for MySQL, then don’t panic. Although, if you don’t have root shell access, then do panic!
Anyway, to reset a password, you need to stop MySQL and restart it with a special setting that allows you to login without passwords. Obviously this is a dangerous switch and you have to remember to stop and start MySQL after you’ve finished!
Login with superuser access (sudo su)
> /etc/init.d/mysql stop
This will stop the MySQL service on the server, and all connections will be cut off.
Then we need to start MySQL with the secret switch:
> mysqld_safe --skip-grant-tables &
I don’t get any messages after this, but you may get one saying the mysqld_safe has started.
Then connect as root:
> mysql -u root
You will be connected. And you can just assign the root user a new password:
mysql> USE mysql; mysql> UPDATE user SET password=PASSWORD(NEWPASSWORD) WHERE user = "root"; mysql> FLUSH PRIVILEGES; mysql> exit;
That’s it. So now, just stop and start mysql again (as superuser) and were done:
> /etc/init.d/mysql restart