SOLVED lost root access to MySQL - DFU error...

[PCZero]

I was attempting to added authorization for root to access MySWL remotely from my IP using MySWL Query Browser 1.2.17. I was successful by chaning the Host field form localhost to my current IP, then I was successful using the wildcard "%". I wantd to make it so that only localhost and my current IP could have access and per a Google saeach I was supposedly able to do that by changing the host to "localhost/mycurrentIP". Well that must have been bad info because now I cannot access MySQL at all. The MyPHPAdmin in WHM will not connect nor will the MySQL Query Browser program. How in the heck do I get back into the MySQL user table to edit the host field at this point?

(I hate when I do DFU things)

 

[PCZero]

Woo Hoo I got it figured out...

 

[andrew.n]

What was it? Good job!

 

[PCZero]

To get into MySQL if locked out as root...

1) At shell go to /etc
2) nano my.cnf
--> Under [mysqld] add the line skip-grant-tables
3) In WHM restart MySQL.
4) At shell log into mysql
--> mysql -u root -p
--> use mysql
--> UPDATE user SET HOST = 'localhost' WHERE user = 'root'
5) In WHM restart MySQL.

Now you should be abel to use phpMyAdmin in the usual manner. You might want to go back and edit the my.cnf file and delete or comment out the line added above then restart MySQL one last time.

 

[andrew.n]

Well done though this could also be done from WHM directly

The root MySQL user password can be updated in the following location in WHM: "Home » SQL Services » MySQL Root Password"

 

[PCZero]

The issue was not the password. The issue was that the host filed for the root user got scrogged and there was no way to get into any of MySQL. The above procedures allows the you to get in, use the MySQL database and edit the host field for the root user in the user table.