MySQL is completely blocked!

Question

I somehow managed to completely block myself from MySQL to WAMP. It seems that all privileges are denied to all users. The only way I can get into MySQL is to execute a command line, but without user input. From there I can literally do nothing ... all privileges are denied. I tried updating the root password to no avail.

I also tried to completely remove WAMP as well as MySQL. After deleting MySQL, I deleted the data directories inside them to get rid of all the information. After reinstalling WAMP, the problem persists.

Attempts to access PHPMyAdmin result in:

#1045 - Access denied for user 'root'@'localhost' (using password: NO) 

Can anyone help?

EDIT Why do people vote to close this post ...?

EDIT2 A wild secondary problem will appear! What do you want to do? Answer or navigate?

Answer 1

You will need to reset the root password using mysqladmin from the command line.

 mysqladmin password your-new-root-password 

Perhaps you need to explicitly specify the root user:

 mysqladmin -u root password your-new-root-password 

May also help reset privileges from mysqladmin :

 mysqladmin flush-privileges 

You may then need to return all root privileges. Not sure if this is necessary.

 GRANT ALL PRIVILEGES ON *.* TO root@localhost IDENTIFIED BY 'your-new-password'; 

Answer 2

http://dev.mysql.com/doc/refman/5.0/en/resetting-permissions.html

Make sure the mysqld process is killed before trying to reset. Try logging into the mysql client, as you say, maybe in your question.

Answer 3

If a complete cleanup and reinstallation of the results leads to the same problem, it is likely that the problem in your environment is related to the problem.

You do not indicate which version of Windows you are using, but I assume that it is Vista or higher with these inconvenient user privilege issues. I would check that the mysql service user account has read / write access to the data directories and mysql files.

REQUESTED DEVELOPMENT:

To check the account on which the service runs, open services.msc, scroll down the corresponding service and look at the "Login" column (or double-click on this service, and then go to the "Login" tab).

To check the permissions of the files and folders of the MySQL data, right-click on the parent folder and select "Properties", then the "Security" tab (I'm not sure that you would have a "Security" tab with "Home", although - you would not in XP).

If the account launched by the service does not have full control over the folder and files in it, you will need to edit the permissions to add this user (or change the user who runs the service).

If everything doesn’t point you in the right direction, you should find detailed instructions for installing WAMP on Win7 Home and make sure that you follow them in the letter, as permissions, etc. rather are PITA on Win7. Perhaps even Win7 Home will not allow you to run the webserver / sql server as a service, since home versions of Windows are often deliberately crippled in this way (fr'instance, you cannot run IIS on WinXP Home and folder permissions are hidden).

Answer 4

I was able to re-access by simply running mysql_secure_installation from the command line. He started me up on some y / n issues and let me (re) install the pw root.

Answer 5

Just spent forever on it .. so many solutions didn't work. Finally it worked for me.

Restart MySQL with skip-grant-tables , which bypasses security, and now you can log in and run:

 USE mysql; CHECK TABLE user; REPAIR TABLE user; 

Obviously, I messed up users or something like that ... and that solved the problem. After that, I found that I no longer have root user. So I had to add the following command (being with skip-grant-tables ):

 INSERT INTO mysql.user SET user = 'root', host = 'localhost', password = Password('yournewrootpw'), 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'; 

Hope this saves someone else a ton of time ..