How to reset or recover admin account password for MySql?

Question

I have a MySql database that I "inherited" and I was not given the admin credentials. I do however have access to the box that it runs on. Is there a way to either recover the admin credentials or create new ones?

Answer 1

Read and execute the chapter about resetting the root password in the MySQL Reference Manual.

This procedure starts the mysql daemon without authorization allowing you to connect without supplying credentials. In this mode you can connect normally, and reset passwords and grants. Afterwards do not forget to start mysql again with proper authorization in place.

Answer 2

If this is a Debian/Ubuntu box, there is a special root-equivalent account called debian-sys-maint. You can read the password in /etc/mysql/debian.cnf

Using that password you can log into mysql as debian-sys-maint using:

mysql -udebian-sys-maint -p

Once you are logged in, do the following:

use mysql;
update user set password=password('<new password>') where user='root';
flush privileges;
quit;

root should now be accessible using your new password:

mysql -uroot -p