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.