MySQL admin@localhost access denied

[sieb@]

Access to the client instance of mysql as admin is denied. This just happened randomly. I had this problem once before and fixed it per Botsman's instructions in this thread (http://forum.plesk.com/showthread.php?s=&threadid=25369&highlight=mysql+access+denied) but its not working now.

Stop mysql, change my.ini to "skip_grant_tables", restart mysql, clear admin password, restart, access denied. Or, if I clear the admin password and set a new one, access is denied again. I check the 'misc' table in PSA's db and the password is correct, but no access. Even if I try to connect with Mysql Control Center, I get access denied. This is getting to be annoying because we cannot create user databases, nor can we run psadump.

 

[Gris@]

When you using "skip_grant_tables" you can access to mysql. Then you can see mysql database. This database has user table. Column host for admin user determines access rights of admin. In your case it should be localhost.

 

[sieb@]

Comparing the production server to a test server, there is no admin entry in the Mysql/user table. I add one with no password, and clear the password in the database credentials in PSA but, still doesn't work. I can't add an admin user to Mysql itself while skip-grant-tables is enabled, but if I disable it, I can't login to the db to fix it.

 

[sieb@]

Ok, I fixed this in a roundabout way. I did the skip_grant_tables, then logged in via MCC, modified one of the user accounts in the MySQL/user table and gave it full access (replaced all N's with Y's), restart mysql, then used that account to recreate the admin account with full access. Restart mysql and restore the correct access for the user account and sync the admin account credentials (make a password) via PSA. All is fine now.