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.
1 Answer
I have discussed situations like this before in these posts:
May 10, 2013
: In place upgrade from MySQL 5.5 to 5.6.11 removes all users from user tableSep 22, 2013
: How to create root user after running mysql_install_db?
In your case do this
STEP #1
Add these lines just under the [mysqld]
group header in my.ini
[mysqld]
skip-grant-tables
skip-networking
STEP 2
Open Windows Command Line as Administrator, and reboot MySQL Service
C:\> net stop mysql
C:\> net start mysql
STEP 3
You can login to MySQL
C:\> mysql <Hit Enter>
STEP 4
From the mysql prompt, run this
SET @PasswordHash = PASSWORD('whateverpasswordiwant');
REPLACE INTO mysql.user SET
Host = 'root',
User = 'localhost',
Password = @PasswordHash,
Select_priv = 'Y',
Insert_priv = 'Y',
Update_priv = 'Y',
Delete_priv = 'Y',
Create_priv = 'Y',
Drop_priv = 'Y',
Reload_priv = 'Y',
Shutdown_priv = 'Y',
Process_priv = 'Y',
File_priv = 'Y',
Grant_priv = 'Y',
References_priv = 'Y',
Index_priv = 'Y',
Alter_priv = 'Y',
Show_db_priv = 'Y',
Super_priv = 'Y',
Create_tmp_table_priv = 'Y',
Lock_tables_priv = 'Y',
Execute_priv = 'Y',
Repl_slave_priv = 'Y',
Repl_client_priv = 'Y',
Create_view_priv = 'Y',
Show_view_priv = 'Y',
Create_routine_priv = 'Y',
Alter_routine_priv = 'Y',
Create_user_priv = 'Y',
Event_priv = 'Y',
Trigger_priv = 'Y',
Create_tablespace_priv = 'Y',
ssl_type = '',
ssl_cipher = '',
x509_issuer = '',
x509_subject = '',
max_questions = 0,
max_updates = 0,
max_connections = 0,
max_user_connections = 0
;
REPLACE INTO mysql.user SET
Host = 'root',
User = '127.0.0.1',
Password = @PasswordHash,
Select_priv = 'Y',
Insert_priv = 'Y',
Update_priv = 'Y',
Delete_priv = 'Y',
Create_priv = 'Y',
Drop_priv = 'Y',
Reload_priv = 'Y',
Shutdown_priv = 'Y',
Process_priv = 'Y',
File_priv = 'Y',
Grant_priv = 'Y',
References_priv = 'Y',
Index_priv = 'Y',
Alter_priv = 'Y',
Show_db_priv = 'Y',
Super_priv = 'Y',
Create_tmp_table_priv = 'Y',
Lock_tables_priv = 'Y',
Execute_priv = 'Y',
Repl_slave_priv = 'Y',
Repl_client_priv = 'Y',
Create_view_priv = 'Y',
Show_view_priv = 'Y',
Create_routine_priv = 'Y',
Alter_routine_priv = 'Y',
Create_user_priv = 'Y',
Event_priv = 'Y',
Trigger_priv = 'Y',
Create_tablespace_priv = 'Y',
ssl_type = '',
ssl_cipher = '',
x509_issuer = '',
x509_subject = '',
max_questions = 0,
max_updates = 0,
max_connections = 0,
max_user_connections = 0
;
exit
STEP 5
Remove skip-grant-tables
and skip-networking
from my.ini
STEP 6
Restart MySQL
C:\> net stop mysql
C:\> net start mysql
STEP 7
Try logging into MySQL
my.ini
? What version of MySQL are you running (5.1, 5.5, 5.6) ?