2

I am able to login with root - "No password", But i can't change password. I checked many links , everybody saying to run update query on user table , but i even can't find user table in phpmyadmin.

Error I am getting is : 

$ /usr/local/mysql/bin/mysqladmin -u root -p password
Enter password: 
New password: 
Confirm new password: 

/usr/local/mysql/bin/mysqladmin: Can't turn off logging; error: 'Access denied; you need (at least one of) the SUPER privilege(s) for this operation'

Any help will be highly useful

PHP My ADMIN (Screenshot): http://awesomescreenshot.com/021cxa0fb

Error: http://i47.tinypic.com/1j1b0m.png

Additional screenshot: http://i48.tinypic.com/w9vbtl.png

Additional screenshot 2: http://i48.tinypic.com/4rymps.png

favoretti
  • 29,299
  • 4
  • 48
  • 61
Senthil
  • 946
  • 1
  • 14
  • 34
  • 1
    have you seen this post? http://stackoverflow.com/questions/6474775/setting-the-mysql-root-user-password-on-mac – Marcel Hebing Aug 11 '12 at 21:24
  • Yes I checked , again they saying "update user set password=PASSWORD" , but i can't find mysql or user table , should i create one myself? – Senthil Aug 11 '12 at 21:33
  • I noticed from your `Error` screenshot that you entered `mysqladmin -uroot` without a space between the `-u` and `root`. Can you retry the command with a space? – HeatfanJohn Aug 11 '12 at 21:40
  • Sorry john , its not working , i tried without gap . – Senthil Aug 11 '12 at 21:43
  • I just saw your newest screenshot showing that you can log into mysql as root, but then can't use database mysql. Very strange. Is this a new installation? After you log into the command line, what do you get when you enter the `status` command? – HeatfanJohn Aug 11 '12 at 21:45
  • This is what i am getting john ( http://i48.tinypic.com/4rymps.png ) . I already had working mysql , suddenly its stops working, so i reinstalled mysql , yes this is new installation . – Senthil Aug 11 '12 at 21:54
  • Check rights to the database files, they must be owned by your user. – favoretti Aug 11 '12 at 22:43
  • And apparently your 'root' user has lost its 'SUPER' privilege. What you also van do is have a look at this post: http://stackoverflow.com/questions/11819047/access-denied-for-user-userlocalhost-on-mysql/11819165#11819165 start mysql without grant tables and try to alter 'root' user to have 'SUPER' privilege. – favoretti Aug 11 '12 at 22:47

4 Answers4

2

it seems to me, that you use phpmyadmin to look for the table. if so, you won't see all tables. you have to log in to mysql as root with the "mysql" command in the terminal:

$ mysql -u root

then you can run the "update user..."-command (you don't have to see the table containing the user informations)

another idea: when you use the -p in the command line, you are not allowed to write the password next to it (it will ask you later on). if you write something after -p it will think that this is the database name...

Marcel Hebing
  • 3,072
  • 1
  • 19
  • 22
2

From the additional information you have posted in comments it appears that your MySQL root user no longer has root privileges on your system. After performing a quick search looking for mysql reset root privileges I found this blog posting that appears to give detailed instructions on how to restore root privileges to your root account.

BEWARE, I have not tried these steps and they are from 2009 and it's possible that MySQL may have changed internally from when these instructions were created. However, that being said, the comments on the posting are positive (of course, the comments could be fake).

My recommendation is to backup the directory (or directories) containing all of your MySQL data and then trying these steps EXACTLY as they are laid out.

Looking more closely at these instructions they appear valid to me. I noticed that they involve taking down the MySQL daemon, restarting MySQL with an option that turns off all table security and then executing updates to add rights back to the root user. I now recommend giving these steps a try.

HeatfanJohn
  • 7,143
  • 2
  • 35
  • 41
0

To set the root's password:

/usr/bin/mysqladmin -root password 'new-password'

try to know if mysql daemon is up:

/usr/bin/mysqladmin -u root -p ping

if not alive try :

/etc/init.d/mysql start

& verify that mcrypt package is installed.

user1587368
  • 314
  • 2
  • 6
0

I solved the Problem , Thanks for all.I reinstalled the mysql server as per following guide , the problem was I updated password directly to the mysql.user table with update query , its wrong , since the password should be stored as encrypted in mysql.user table , if we updating it directly through query then it will be a string.

Excellant guide : http://blog.mclaughlinsoftware.com/2011/02/10/mac-os-x-mysql-install/

Thanks again for everybody :)

Senthil
  • 946
  • 1
  • 14
  • 34