2

For some reason when I installed MySQL on my machine (a Mac running OS X 10.9) the 'root' MySQL account got messed up and I don't have access to it, but I do have access to the standard MySQL account 'sean@localhost' which I use to log into phpMyAdmin.

I am trying to reset the 'root' password by starting the mysqld daemon using the command mysqld --skip-grant-tables and then running the following lines in the mysql> shell.

mysql> UPDATE mysql.user SET Password=PASSWORD('MyNewPass')

-> WHERE User='root';

mysql> FLUSH PRIVILEGES;

Problem is when I try to run that MySQL string the daemon spits back a ERROR 1142 (42000): UPDATE command denied to user ''@'localhost' for table 'user' as if I didn't use the -u argument when I started the mysql shell, either though I did.

Any help is muchly appreciated as I am lost at this point. :/

Uncle Nerdicus
  • 123
  • 1
  • 2
  • 6

1 Answers1

0

Well, you say you have access via:

sean@localhost

But then you say you are denied access via:

''@'localhost'

So you are clearly have MySQL running with user grants. Which won’t help in a case like this. So the key should be too circumvent the user grants by stopping MySQL & re-launcing via (this is what I do on Ubuntu):

sudo mysqld --skip-grant-tables &

On a Mac it could be as simple as doing it without sudo:

mysqld --skip-grant-tables &

Then when that is running, login as root; you can do that now since the use grants are skipped:

mysql -u root mysql

Then run a query like this replacing with your own settings:

UPDATE user SET Password=PASSWORD('YOURNEWPASSWORD') WHERE user='root'; FLUSH PRIVILEGES; exit;
Giacomo1968
  • 3,542
  • 27
  • 38
  • Upon running the last query I got the error `ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO) ` back, it does say 'root' now, so I think we're getting somewhere. When I run `mysql` without parameters I get a `ERROR 1046 (3D000): No database selected ERROR 1227 (42000): Access denied; you need (at least one of) the RELOAD privilege(s) for this operation` back. Thoughts @JakeGould ? – Uncle Nerdicus Nov 10 '13 at 15:31
  • Don’t know. Perhaps the best & most realistic solution at this point is to just reinstall MySQL? – Giacomo1968 Nov 10 '13 at 16:52
  • How do you reinstall it @JakeGould ? – Uncle Nerdicus Nov 10 '13 at 17:01
  • Do exactly what you did when you state, “…when I installed MySQL on my machine…” but now just do it again. – Giacomo1968 Nov 10 '13 at 17:12
  • Alright, will try this again.. Do you have an install Tutorial that you know works for Mac OS X 10.8/10.9? Obviously the one I followed before didn't work. – Uncle Nerdicus Nov 10 '13 at 17:15
  • It’s not obvious what you did the first time did not work. Also, I have no idea why this is complex to you. Go to the MySQL site. Download a binary installer (64 bit should work; 10.7 will work with 10.8 & 10.9) and reinstall. http://dev.mysql.com/downloads/mysql/ – Giacomo1968 Nov 10 '13 at 17:24
  • 1
    I got it working now using your advice and this http://akrabat.com/computing/setting-up-php-mysql-on-os-x-mavericks , thank you for your patience kind sir. :-) – Uncle Nerdicus Nov 10 '13 at 18:01