Restoring deleted 'root' user and password for MySQL

Question

I accidentally deleted the root user on my local dev setup of MAMP/MySQL running on OS X. There are no other users created to get back into MySQL.

This is a mild nightmare can't seem to do anything without root.

Found this: http://hack2live.blogspot.com/2009/04/restore-repair-reset-mysql-root.html which seems like exactly what I need.

I also don't think a reinstall of MAMP will do the trick, as a lot of my cursory searches yielded people who had tried without success.

Does anyone know of an OSX friendly way to recreate root @ localhost back into MAMP's MySQL? I basically just don't know where MySQL is living in MAMP or how to execute the correct commands in terminal to try and fix it.

Update

I tried several options below to restore root to no avail and decided to restore a backup of the entire MAMP application. So I've got root back, I can open phpmyadmin, etc.

Answer 1

I have a quick and dirty way

Get someone with SysAdmin rights and do the following:

1. Add 'skip-grant-tables' to my.cnf under the [mysqld] section

2. restart mysql

3. type mysql with no password and hit enter

4. Run This:

   DELETE FROM mysql.user 
   WHERE  user = 'root' 
          AND host = 'localhost'; 
   
   INSERT INTO mysql.user 
   SET user = 'root', 
       host = 'localhost', 
       password = Password('whatevernewpassword'), 
       Select_priv = 'y',
       Insert_priv = 'y',
       Update_priv = 'y',
       Delete_priv = 'y',
       Create_priv = 'y',
       Drop_priv = 'y',
       Reload_priv = 'y',
       Shutdown_priv = 'y',
       Process_priv = 'y',
       File_priv = 'y',
       Grant_priv = 'y',
       References_priv = 'y',
       Index_priv = 'y',
       Alter_priv = 'y',
       Show_db_priv = 'y',
       Super_priv = 'y',
       Create_tmp_table_priv = 'y',
       Lock_tables_priv = 'y',
       Execute_priv = 'y',
       Repl_slave_priv = 'y',
       Repl_client_priv = 'y',
       Create_view_priv = 'y',
       Show_view_priv = 'y',
       Create_routine_priv = 'y',
       Alter_routine_priv = 'y',
       Create_user_priv = 'y',
       Event_priv = 'y',
       Trigger_priv = 'y',
       Create_tablespace_priv = 'y';
   

5. exit from mysql

6. remove 'skip-grant-tables' from my.cnf under the [mysqld] section

7. restart mysql

That should be all!

Answer 2

The translated version of http://hack2live.blogspot.com/2009/04/restore-repair-reset-mysql-root.html - for OS X.

Open TextEdit.app and select in Format -> "Make plain text".
Cut and paste the following into TextEdit and save it into your HOME folder with name restore_root_privileges.sql

update mysql.user set Super_priv='y' where user='root';
update mysql.user set Select_priv='y' where user='root';
update mysql.user set Insert_priv='y' where user='root';
update mysql.user set Update_priv='y' where user='root';
update mysql.user set Delete_priv='y' where user='root';
update mysql.user set Create_priv='y' where user='root';
update mysql.user set Drop_priv='y' where user='root';
update mysql.user set Reload_priv='y' where user='root';
update mysql.user set Shutdown_priv='y' where user='root';
update mysql.user set Process_priv='y' where user='root';
update mysql.user set File_priv='y' where user='root';
update mysql.user set Grant_priv='y' where user='root';
update mysql.user set References_priv='y' where user='root';
update mysql.user set Index_priv='y' where user='root';
update mysql.user set Alter_priv='y' where user='root';
update mysql.user set Show_db_priv='y' where user='root';
update mysql.user set Super_priv='y' where user='root';
update mysql.user set Create_tmp_table_priv='y' where user='root';
update mysql.user set Lock_tables_priv='y' where user='root';
update mysql.user set Execute_priv='y' where user='root';
update mysql.user set Repl_slave_priv='y' where user='root';
update mysql.user set Repl_client_priv='y' where user='root';
update mysql.user set Create_view_priv='y' where user='root';
update mysql.user set Show_view_priv='y' where user='root';
update mysql.user set Create_routine_priv='y' where user='root';
update mysql.user set Alter_routine_priv='y' where user='root';
update mysql.user set Create_user_priv='y' where user='root';

Save and quit TextEdit.app.

Stop you mysqld server. How to do this, depends on what installation did you use for MySQL. You probably have an PreferencePane in your system preferences. If not, you must consult the docs for your MySQL installation.

Open Terminal.app (Applications/Utilities) Enter the following commands:

sudo mysqld --skip-grant-tables &  #start your MySQL server without access control
mysql -vv < ~/restore_root_privileges.sql
sudo mysqladmin -p shutdown

Start your MySQL server as usually, e.g. from PreferencePanes.
In the Terminal.app: enter the following:

mysql -u root -p
mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION;
mysql> quit;

That's all. Without any warranty. You can loose all you data if do something wrong. Backup first your mysql files.

If you got something like:

-bash: mysql: command not found

thats mean, than your installation is incorrect and you should find where are your mysql binaries, and need enter the directory into you PATH variable.

Answer 3

I just got the same - problem. I am using Xammp on Windows 7. I accidently deleted a root user in phpmyadmin.

It appears I was able to fix the problem in easy route like this:

Stop the apache and mysql in xammp control

Go to .../xammp/mysql

You will see the file "resetroot". Run this file

After this application is finished, restart your mysql and apache

Go to phpmyadmin and you will see the root user restored

Now you can access all your databases again

Answer 4

MySQL 5.7 had replaced Password field with authentication_string (in mysql.user table); so this worked for me.

INSERT INTO mysql.user 
SET user = 'root', 
host = 'localhost', 
authentication_string =Password('Yours_own_password'), 
Select_priv = 'y',
Insert_priv = 'y',
Update_priv = 'y',
Delete_priv = 'y',
Create_priv = 'y',
Drop_priv = 'y',
Reload_priv = 'y',
Shutdown_priv = 'y',
Process_priv = 'y',
File_priv = 'y',
Grant_priv = 'y',
References_priv = 'y',
Index_priv = 'y',
Alter_priv = 'y',
Show_db_priv = 'y',
Super_priv = 'y',
Create_tmp_table_priv = 'y',
Lock_tables_priv = 'y',
Execute_priv = 'y',
Repl_slave_priv = 'y',
Repl_client_priv = 'y',
Create_view_priv = 'y',
Show_view_priv = 'y',
Create_routine_priv = 'y',
Alter_routine_priv = 'y',
Create_user_priv = 'y',
Event_priv = 'y',
Trigger_priv = 'y',
Create_tablespace_priv = 'y',
ssl_cipher = '',
x509_issuer = '',
x509_subject = '';

Answer 5

using mamp. I made the apparently common mistake of deleting the root user when creating another user, I was locked out!

so I found this post and tried to follow the directions but...

I was getting this errores: -bash: mysql: command not found when trying to execute the command mysql commands and later I was getting this error DELETE command denied to user ''@'localhost' for table 'user' when tring to set properly privileges to the root user and do anything with that user once mysql in shell.

so before I could use the commands eric and rolando posted (thank you guys for that) this is what I did

I need to start mysqld properly so I had to do the following

1. stop mysqld process found with

   ps aux | grep mysql

and stop the mamp interface application aswell..

1. and stopped any mysql process found with

   kill -9 [pid]

do make sure no mysql process are running before you go any further.

1. then I had to restart mysqld properly from the actual location where I had mysql binaries this commnad will start mysld without asking for a password (sort of like in safemode)

/Applications/MAMP/Library/bin/mysqld --skip-grant-tables --skip-networking &

I tried with my.cnf file but couldn't get it to work.

1. then I had to go into mysql in shell

/Applications/MAMP/Library/bin/mysql -u root -p

(if prompted for a password just hit enter)

1. then execute the commands eric and rolando posted, this time I didnt get any error "DELETE command denied to user ''@'localhost' for table 'user'" if you do, you didn't start mysqld properly

2. then stop the mysqld service:

/Applications/MAMP/Library/bin/mysqld stop

then restarted mamp as usual with the gui

and all started to work again as it was before... I was so relieved!!!

2 hours of panic and trial and error with the console... valuable lessons learned..

If I messed up any of the commands or the explanation (which could be the case as I was tracing my steps back as writing this post please let me know. I'd be happy to update the post.

lastly don't give. it can be done!!

Answer 6

I did exactly the same thing yesterday, being new to macs and the sql server. I accidentally deleted the root user because I pressed the wrong button in Privileges whilst trying to set up a new user and password.

I did not have any work of any use on this computer so was not worried about deleting my websites.

• I first uninstalled MAMP Pro (for which I had an icon in Launchpad).
• I then deleted MAMP directory from the Applications folder.
• I then reinstalled MAMP from the internet download.
• I then DELETED all my cookies, cache etc from safari.
• I shut down my computer and rebooted.

I now have access to phpMyAdmin through the link on the startup page once MAMP is booted.

Answer 7

Just wanted to contribute how I resolved this. If you accidentally deleted the root user in MAMP PRO and are using OSX with Time Machine backups. Simply go to this folder in finder:

/Library/Application Support/appsolute/

Then 'Enter Time Machine' from the top menu bar and restore a recent backup of the MAMP PRO folder. Saved a lot of hassle for me.

Answer 8

Ok, stop running your MySQL, and open up the installation files it came with. There should be a bat file named resetroot.bat. Run it and your problem will be gone.

Answer 9

Worked for me on CentOS 6. I had to take out Create_tablespace_priv = 'y' because my version squawked otherwise all good.