Host not Allowed Connectivity to MySQL Server

Question

I currently have a website and it references mySQL database. The website uses HTML, CSS and PHP.

PHP code embedded inside HTML website, fetches a value and stores it on the SQL database. Then the website reflects the value stored in the database. Basically, the website is a page counter, for instance every time a user views my webpage it counts it as a numerical value and displays the value on the webpage.

Within my PHP code, I'm supplying correct credentials, such as Host, User, and Password. But, when I try to access the website I receive error code "SQLSTATE[HY000] Host'x.x.x.x' is not allowed to connect to this MySQL server"

I think the issue is mySQL isn't allowing host to access SQL database. The reason I know this, is because I performed 3 steps to log into SQL without a password. when I log into mySQL with no password, I can see the website page counter and it successfully works.

Steps I use to log into SQL without a password are: 1) /etc/init.d/mysqld stop 2) mysqld_safe --skip-grant-tables & 3) mysql -uroot.

When I perform the above steps I can see the page counter working on the website.

What modifications do I need to perform on mySQL to allow my host to access the SQL database?

I've already tried GRANT ALL PRIVILEGES ON .* To root@localhost and it did not work. (MySQL database is installed on the same host accessing the website)

My web server is Apache 2.2.15 (CentOS). The database server is running mysql Ver 14.14 Distribution 5.5.49. On Linux environment.

Answer 1

Use a SQL commands like this:

mysql -u user -p -e 'Your SQL Query Here' database-name

to figure out what users are accessing your db.

In the results, did you only see localhost and 127.0.0.1 for the host? This would mean that only these two hosts are allowed for access. you can try:

GRANT ALL PRIVILEGES ON *.* TO 'root'@'myIPaddress'

and don't forget to follow that with

FLUSH PRIVILEGES