mysql fresh install asking for root password

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.

From serverfault msaspence

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

  msaspence : this are the commands I have run in order

  msaspence : 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 : 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)

  cHao : When you did the update, how many rows were affected?

  cHao : 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?

  msaspence : empty set
  eek how did that happen!

  cHao : 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'.
  From cHao

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

  From Rob

• 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/

  From msaspence