Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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?

share|improve this question
add comment

3 Answers

up vote 14 down vote accepted

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.

share|improve this answer
add comment

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.

share|improve this answer
add comment

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
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.