2

I accidentally removed all the privileges from my MySQL root user,

Is there some way I can restore this user to its original state (with all privileges)?

i m using my sql work bench 6.0

please let me know soultion step by step as i m new in my sql.

Manquer
  • 7,390
  • 8
  • 42
  • 69
user3705192
  • 41
  • 1
  • 5

1 Answers1

7

First try 

GRANT ALL ON *.* TO 'root'@'localhost';

if that does not work then do the following

  1. Stop mysqld service with this ( on UNix-like systems)

    sudo mysqld stop

  2. Restart it with the --skip-grant-tables option.

  3. Connect to the mysql with just: mysql (i.e. no -p option, and username may not be required).

  4. Use the following in the mysql client:

    UPDATE mysql.user SET Grant_priv='Y', Super_priv='Y' WHERE User='root';

  5. Flush the privileges by running

    FLUSH PRIVILEGES;

  6. Remove --skip-grant-tables option. and restart mysqld

  7. Finally run

    GRANT ALL ON *.* TO 'root'@'localhost';

Manquer
  • 7,390
  • 8
  • 42
  • 69
  • I am using window, do I need to run all these commands in work bench6.0 or command prompt – user3705192 Jun 04 '14 at 01:57
  • 1
    if you are using windows see this on how to start mysqld with skip grant table option http://stackoverflow.com/questions/1708409/how-to-start-mysql-with-skip-grant-tables.. for the rest yes via command line is best – Manquer Jun 04 '14 at 02:12
  • hi, I ahve followed the following prcess and still getting the same issue when i run the query. – user3705192 Jun 04 '14 at 05:45
  • hi, I have followed the following process http://stackoverflow.com/questions/1708409/how-to-start-mysql-with-skip-grant-tables and still getting the same issue when i run the query. "GRANT ALL ON *.* TO 'root'@'localhost';" error: UPDATE command denied to user 'root'@'localhost' for table 'user' – user3705192 Jun 04 '14 at 06:08
  • hey forgot to add you need to remove --skip-grant-tables option before granting. – Manquer Jun 04 '14 at 06:16
  • still it is showing the same error, I go to go to C:\ProgramData\MySQL\MySQL Server 5.6\my.default as i did not found 'my.ini', I add the [mysqld] skip-grant-tables then login to my sql and run the command "GRANT ALL ON *.* TO 'root'@'localhost';" it is showing the error "23:41:13 GRANT ALL ON *.* TO 'root'@'localhost' Error Code: 1045. Access denied for user 'root'@'localhost' (using password: YES) 0.000 sec " Plesae let me know how I can solve this issue. – user3705192 Jun 04 '14 at 06:42