Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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.

share|improve this question

migrated from stackoverflow.com Sep 15 '10 at 18:43

This question came from our site for professional and enthusiast programmers.

1  
Have you tried "root" as the password? –  Jergason Sep 15 '10 at 18:37
    
Or "" (blank) ? –  Jordan Sep 15 '10 at 18:38
    
yes and yes still no luck –  msaspence Sep 15 '10 at 21:18
add comment

4 Answers

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.

share|improve this answer
add comment
up vote 2 down vote accepted

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/

share|improve this answer
add comment

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.

share|improve this answer
    
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: <type password enter> 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 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 '10 at 2:20
show 2 more comments

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.

share|improve this answer
    
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 2:24
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.