4

A couple of years back i tried to install apache, php and mysql on my mac. I got it all working but in the end i got fed up of doing everything on the command line so switched to mamp

Now I'm fed up with the sluggishness of mamp and want to get better at using the command line. I'm trying to set it up my self (well, with mac ports) but i forgot the password for the mysql root user. No big problem, no data on there I need, I'll just reinstall.

I uninstall of mysql, re-installed mysql and ran:

sudo -u _mysql mysql_install_db5 /opt/local/share/mysql5/mysql/mysql.server start

All worked fine but doing mysqladmin5 -u root -p ping asks me for a password and mysqladmin5 -u root ping doesn't work.

5

5 Answers 5

3

The problem was I didn't have a root user to reset the password of to fix do: Shell:

/opt/local/share/mysql5/mysql/mysql.server stop
/opt/local/share/mysql5/mysql/mysql.server start --skip-grant-tables

mysql:

user mysql;
insert into user (Host, User, Password) values ('localhost','root','');
update user set 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' where user='root';
exit;

shell:

/opt/local/share/mysql5/mysql/mysql.server stop
/opt/local/share/mysql5/mysql/mysql.server start
mysql -u root

mysql:

grant all privileges on *.* to 'root'@'localhost' with grant option;

More information here: http://helpfromfriend.com/database/mysql/how-to-recreate-root-account-in-mysql/

2

From dev.mysql.com/doc/refman/5.1/en/default-privileges.html :

The mysql.user grant table defines the initial MySQL user accounts and their access privileges:

Some accounts have the user name root. These are superuser accounts that have all privileges and can do anything. The initial root account passwords are empty, so anyone can connect to the MySQL server as root without a password and be granted all privileges.

Instructions for resetting the root password can be found here.

1
  • Instructions provided via link bomb out with error: auth socket not provided. May 8, 2018 at 15:04
0

If you stop mysqld, and start it from the command line with the --skip-grant-tables option, you'll be able to log in as root with no password. Then you can set the root password to something you'll remember.

USE mysql;
UPDATE user SET `password` = PASSWORD('your new password') WHERE user = 'root';
FLUSH PRIVILEGES; 

Be sure to stop mysqld and restart it normally after you've done this, though, so clients get their passwords checked again.

7
  • this are the commands I have run in order
    – msaspence
    Sep 15, 2010 at 20:33
  • shell> /opt/local/share/mysql5/mysql/mysql.server stop shell> /opt/local/share/mysql5/mysql/mysql.server start --skip-grant-tables shell> /opt/local/lib/mysql5/bin/mysql -u root mysql> mysql> use mysql; mysql> UPDATE user SET password = PASSWORD('password') WHERE user = 'root'; mysql> FLUSH PRIVILEGES; mysql> exit shell> /opt/local/share/mysql5/mysql/mysql.server stop
    – msaspence
    Sep 15, 2010 at 20:37
  • shell> /opt/local/share/mysql5/mysql/mysql.server start shell> /opt/local/lib/mysql5/bin/mysql -u root -p Enter password: <type password enter> ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
    – msaspence
    Sep 15, 2010 at 20:39
  • When you did the update, how many rows were affected?
    – cHao
    Sep 16, 2010 at 2:07
  • More to the point, can you SELECT user, host FROM user WHERE user = 'root' (while running mysqld with --skip-grant-tables, of course) and see a row for root@localhost?
    – cHao
    Sep 16, 2010 at 2:20
0

I do not know mac very well, however i had the very same problem with windows. Fresh MySQL installation asking for my current root password. The answer to how you get rid of this problem is very simple at least for windows.

First of all make sure to delete all MySQL folders on your computer. In windows this includes installation directory, but also under / All Users / Application Data on XP this directory is located under C:\Documents and Settings. Then, and actually I never thought of this simple solution to begin with but windows adds all your services in it's registry. You can get rid of a service in your registry by typing

sc delete "ServiceName" in the cmd prompt, in this case

sc delete MySQL

Maybe the same solution applies in some way on your MAC.

1
  • I find it confusing to give specific windows directions, when a more generic "remove old installation completely" might be more helpfull (however I dont think it would be in that case). Thanks for giving answers in this side, try to not confuse the askers :)
    – eckes
    Dec 13, 2012 at 2:24
-1

This no longer works for more recent installations of MySQL with this problem, it seems. See Dave's 2018 comment on http://www.helpfromfriend.com/database/mysql/how-to-recreate-root-account-in-mysql/ said: "this doesn’t work on mysql 5.7.17 very frustrating time with this and many others to skip grant tables". This refers to a similar procedure to the one shown here.

1
  • Hi, please improve your answer by quoting here the comment, as link only answer tend to become invalid with time
    – yagmoth555
    Mar 28, 2020 at 13:12

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .