You are here

Regained MySQL root privileges

I use MAMP, not exclusively but it is a helpful tool. I changed the password on my root account and locked myself out. Most of the documentation I was looking at to solve this problem was for native installations of MySQL. I had a lot of trouble interpreting things into MAMP.
Short of it is I got back in. Learned alot about MySQL and MAMP along the way. Here is the key that Unlocked the door for me.

I was getting this error on phpmyadmin.
#1045 - Access denied for user 'root'@'localhost' (using password: YES)

Resolution:
http://stackoverflow.com/questions/6085455/restoring-deleted-root-user-a...
I recommend you follow this link and read for yourself as things and knowledge change. But below is what I read.

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 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.

Add new comment