1

By mistake, I changed all the privileges of user 'root' in my MySQL Database. Not even a single privilege is having for that user. Now if I log in with another user I am able to access only one database. But I have nearly 25 databases created with 'root' user. If I want to access all other databases I need to GRANT Privileges for the root user again but all other users are not permitted to execute GRANT statement. So is there any way to grant permissions for the root user again. The all other Databases contained in my DB Server are very much important. So Please help in resolving this issue.

Leandro Papasidero
  • 3,728
  • 1
  • 18
  • 33
a989898
  • 13
  • 1
  • 6

1 Answers1

1

This is for XAMPP

Log in as root, then run the following MySQL commands:

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

If you get an error please try this:

  1. Stop mysqld

enter image description here

  1. Restart mysql with the --skip-grant-tables option.

    • Add the two options in the mysqld section of my.ini:

my.ini

[mysqld]
skip-grant-tables
skip-networking

and then run Open command prompt windows >> Command prompt

net stop mysql (wait 10 seconds)
net start mysql

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

    • Open command prompt windows >> Command prompt
    • Type cd C:\xampp\mysql\bin
    • Type mysql

Issue the following commands in the mysql client:

insert into user (Host, User, Password, Select_priv, Insert_priv, Update_priv, Delete_priv, Create_priv, Drop_priv, Reload_priv, Shutdown_priv, Process_priv, File_priv, Grant_priv, References_priv, Index_priv, Alter_priv, Show_db_priv, Super_priv, Create_tmp_table_priv, Lock_tables_priv, Execute_priv, Repl_slave_priv, Repl_client_priv, Create_view_priv, Show_view_priv, Create_routine_priv, Alter_routine_priv, Create_user_priv, ssl_type, ssl_cipher, x509_issuer, x509_subject, max_questions, max_updates, max_connections, max_user_connections) 
values('localhost','root','','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','','','','','0','0','0','0');

Reference: How can I restore the MySQL root user’s full privileges?

Community
  • 1
  • 1
Leandro Papasidero
  • 3,728
  • 1
  • 18
  • 33
  • By mistake, I removed all the Privileges for root user. Event GRANT statement is permitted. If I Execute the above query, I am getting an error. Error SQL query: GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost'; MySQL said: Documentation #1045 - Access denied for user 'root'@'localhost' (using password: NO) – a989898 Feb 27 '15 at 20:19
  • Stop mysql service and restart it with `--skip-grant-tables` option. then connect to mysqld and type the query that i just edited. – Leandro Papasidero Feb 27 '15 at 20:31
  • Sir can you please mention step wise Please as I am a beginner to this DB. Please sir... – a989898 Feb 27 '15 at 20:48
  • No problem. First of all, are you working on your localhost, share hosting, VPS? Second, your "server" runs on Windows or Linux ? – Leandro Papasidero Feb 27 '15 at 20:55
  • I am working on localhost only and my server runs on Windows Sir... Thank you for your Reply Sir..... – a989898 Feb 27 '15 at 21:12
  • Are you using xampp or wamp? – Leandro Papasidero Feb 27 '15 at 21:13
  • Sir can you please explain the Step 1 once again in detail...That is where to stop mysqlId restart it with the --skip-grant-tables option. – a989898 Feb 27 '15 at 21:23
  • I just edited my answer step by step. Please let me know if you can follow it. – Leandro Papasidero Feb 27 '15 at 21:30
  • After adding `[mysqld] -grant-tables skip-networking` these lines I opened my command prompt and also taken the path to `C://xampp/mysql/bin/>`. But If I enter only mysql, I am getting **ERROR 2003 : Can't connect to MySQL server on 'localhost' <10061>** I am getting a doubt `net stop mysql (wait 10 seconds) net start mysql`. Where to run these statements... – a989898 Feb 28 '15 at 05:31
  • Open command prompt windows >> Command prompt. `net stop mysql` will stop the service and `net start mysql` will start the service – Leandro Papasidero Feb 28 '15 at 05:34