GRANT access to ROOT

Question

I was using phpmyadmin to adjust the privileges of the users.

I removed all access for user 'root'. Now I am unable to see the tables.

I tried signing in under another user but its not allowing me.

What options do I have at this point?

Step 1.

//Stop mysql server

    zend
    opt 6 zendDBi
    opt 7 Stop ZendDBi

Step 2.

//Start mysql server

    cd /usr/local/mysql/bin  

    mysqld_safe --skip-grant-tables  
    mysql FLUSH PRIVILEGES;

After I do this I get this long bit of text:

 /usr/local/mysql-5.1.59-i5os-power-64bit/bin/mysql  Ver 14.14 Distrib 5.1.59, for ibm-i5os (power) using readline 5.1
 Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.                                         

  Oracle is a registered trademark of Oracle Corporation and/or its                                                    
  affiliates. Other names may be trademarks of their respective                                                        
  owners.                                                                                                              

   Usage: /usr/local/mysql-5.1.59-i5os-power-64bit/bin/mysql [OPTIONS]     [database]                                       
    -?, --help          Display this help and exit.          
    .............

After the flush I try to reconnect with mysql server I get this error:

 mysql -u root                                                                                   
 ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)

http://stackoverflow.com/questions/1708826/how-to-get-all-privileges-back-to-the-root-user-in-mysql — Viswanath Polaki, Mar 10 '15 at 12:33
You cannot see tables, but if you are able to login as 'root' then you can GRANT privileges in the same way you REVOKE. — mysqlrockstar, Mar 10 '15 at 13:07
mysql> grant all privileges on *.* to 'root'@'%' with grant option; ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO) — DJ Howarth, Mar 10 '15 at 13:22
Possible duplicate: https://stackoverflow.com/questions/4258124/how-to-reset-mysql-root-password — Isaac Bennetch, Mar 11 '15 at 19:44
If you lost a password, accidentally removed privileges, or completely deleted the root account, you still need to follow the same set of steps. You're right that the question sounds a bit different, but at the fundamental level both the linked question and yours deal with replacing lost privileges; so you're right that it's a different question and therefore not a duplicate and I'm (mostly likely) right that the solution is the same. Anyway, in the mean time jdog has added some steps and rather than start a whole new answer of my own, I'll just try to help out there. — Isaac Bennetch, Mar 12 '15 at 14:26

score 0 · Answer 1 · answered Mar 10 '15 at 17:59

0

You need to both start mysql with --skip-grant-tables and assign a password to root, in your case you seem to want the empty password:

1.

//Stop mysql server

zend
opt 6 zendDBi
opt 7 Stop ZendDBi

2.

//Start mysql server

cd /usr/local/mysql/bin  

mysqld_safe --skip-grant-tables

3.

grant all privileges on . to 'root'@'%' with grant option identified by password('');
flush privileges;

4.

exit

5.

mysql -uroot

answered Mar 10 '15 at 17:59

jdog

2,465
6
40
74

mysql grant all privileges on . to 'root'@'%' with grant option identified by password(''); qsh: 001-0050 Syntax error on line 6: token "(" not expected. ------------- mysql grant all privileges on . to 'root'@'%' with grant option; /usr/local/mysql-5.1.59-i5os-power-64bit/bin/mysql Ver 14.14 Distrib 5.1.59, for ibm-i5os (power) using readline 5.1 Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved. ---------------------- Mysql -u root ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2) – DJ Howarth Mar 10 '15 at 18:27
Try PASSWORD('') and make sure to use single tick – jdog Mar 10 '15 at 19:37
grant all privileges on . to 'root'@'%' with grant option identified by PASSWORD(''); qsh: 001-0050 Syntax error on line 3: token "(" not expected. – DJ Howarth Mar 10 '15 at 19:46
try: use mysql; grant all privileges on yourdatabase to 'root'@'%' identified by PASSWORD('') ; grant all privileges on yourdatabase to 'root'@'%' with grant option; – jdog Mar 10 '15 at 20:00
No connection. Trying to reconnect... Connection id: 4 Current database: *** NONE *** ERROR 1044 (42000): Access denied for user 'root'@'localhost' to database 'mysql' ERROR 1046 (3D000): No database selected ERROR 1046 (3D000): No database selected – DJ Howarth Mar 10 '15 at 20:12
Ca you check if your stopping of mysql actually works with task manager – jdog Mar 10 '15 at 20:28
I dont understand what you mean. – DJ Howarth Mar 10 '15 at 20:30
I suspect it's not stopped, so you are – jdog Mar 10 '15 at 20:31
Connecting to the original instance. After stopping, check in task manager that mysql is not running – jdog Mar 10 '15 at 20:32
mysql doesnt show up in my task manager, but I can check if the port is 'listening'. The server is shutting down. – DJ Howarth Mar 10 '15 at 20:46
'root'@'%' is for TCP connections; according to the error message you're connecting via socket which means you need to use the host 'localhost' instead of the wildcard '%' – Isaac Bennetch Mar 12 '15 at 14:22

GRANT access to ROOT

1 Answers1