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 --defaults-file=/etc/mysql/debian.cnf mysql

Once you are logged in, do the following:

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

Answer 3

My root user didn't exist. On

CREATE USER root@localhost;

I got

ERROR 1290 (HY000): The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement

The solution was to

mysqld_safe  --skip-grant-tables &
mysql
INSERT INTO user (Host,User,Password) VALUES ('localhost', 'root', 'changethispassword');
UPDATE mysql.user SET Grant_priv='Y', Super_priv='Y' WHERE User='root';
FLUSH PRIVILEGES;

After that I added all privs one by one with a little help from this:

mysql mysql -e "SELECT * FROM user WHERE User='root'\G"|grep N

(and this one could and should be automated more)

Note: finding the correct number of 'Y's in the INSERT is a PITA.

Answer 4

As steps on MySQL Reference Manual are not very clear, I am trying to answer here in easier language. (this answer is relevant to MacBook OSX users):

1. As we don't know current password, MySQL server needs to run skipping/bypassing current login credentials. Instruct MySQL server to skip access grants table by:
   • Stop your MySQL server (if it is already running).
   • Find your MySQL configuration file, my.cnf. (For me it was placed @ /Applications/XAMPP/xamppfiles/etc. You can just search if you can't find it).
   • Open my.cnf file in any text editor.
   • Add "skip-grant-tables" (without quotes) at the end of [mysqld] section and save the file.
   • Now start your MySQL server. It'll start with skip-grant-tables option.
2. Open Terminal and run mysql command
3. Execute command mysql> UPDATE mysql.user SET Password=PASSWORD('MyNewPass') WHERE User='root';
4. Execute command mysql> FLUSH PRIVILEGES;
5. Execute command mysql> exit
6. Restart MySQL server.