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

4 Answers 4

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

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

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

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

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.