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.

jscott
  • 24,484
  • 8
  • 79
  • 100
msaspence
  • 201
  • 2
  • 3
  • 8

5 Answers5

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/

msaspence
  • 201
  • 2
  • 3
  • 8
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.

Rob
  • 262
  • 3
  • 9
  • 23
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.

cHao
  • 473
  • 1
  • 3
  • 10
  • this are the commands I have run in order – msaspence Sep 15 '10 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 '10 at 20:37
  • shell> /opt/local/share/mysql5/mysql/mysql.server start shell> /opt/local/lib/mysql5/bin/mysql -u root -p Enter password: ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES) – msaspence Sep 15 '10 at 20:39
  • When you did the update, how many rows were affected? – cHao Sep 16 '10 at 02: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 '10 at 02:20
  • empty set
    eek how did that happen!     – msaspence Sep 16 '10 at 07:25
  • Dunno. Perhaps the setup asked you to pick an admin username (since "root" is a common target). Anyway, look in that table for a username with all (or almost all) 'Y' privileges. If you don't find one, create one with a username of your choosing, a host of 'localhost', and a password of PASSWORD('whatever you want your password to be'), and all privileges set to 'Y'. – cHao Sep 22 '10 at 16:28
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.

Wolominiarz
  • 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 '12 at 02: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.

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