Issues connecting to mySql database on target machine

Question

I'm using hostgator for hosting my website and I have added my IP address as remote access host. And then I connected with mySql workbench and tested the connection and I get the message that all parameters are correct. On hostgator, I added myself as a the admin user granting myself all privileges. Now I want to manipulate the database via a PHP script using PDO but this is the error I'm getting in my browser when I try to run the PHP file:

Connection failed: SQLSTATE[28000] [1045] Access denied for user 'user'@'host' (using password: YES)

On mySQL workbench Users and Privileges tab, I get this:

"The account you are currently using does not have sufficient privileges to make changes to MySQL users and privileges."

I'm not sure whats going wrong here and I'm quiet new to this. Can somebody please help?

edit:

php code below:

<?php

    ini_set('display_errors',1);
    error_reporting(E_ALL);

    //this block tries to connect to the database
    //if there's an error connecting, the code under catch will
    //and the program will end


    $host="host";
    $port="3306";
    $user="xxx";
    $password="xxx";
    $dbname="database";

    try {
        $conn = new PDO("mysql:host=$host;dbname=$dbname;", $user, $password);
        // set the PDO error mode to exception
        $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        echo "Connected successfully";
        }
    catch(PDOException $e)
        {
        echo "Connection failed: " . $e->getMessage();
        }
       //$con->close();
    ?>

can you successfully login via shell with the username & password? — Simon., Jan 08 '15 at 16:55
I looked over this post [link](http://stackoverflow.com/questions/21177562/installing-mysql-workbench-but-file-etc-my-cnf-doesnt-exist) and what's weird is that I don't even have a usr directory that would house this file. So unfortunately the SSH connection failed. @sanojlawrence I edited my main thread to include php code — rads89, Jan 08 '15 at 17:07
@sanojlawrence I'm not trying to connect to a localhost. I am actually trying to connect to my hostgator server. I just removed the hostgator server IP address for the purpose of this post. — rads89, Jan 08 '15 at 17:23
@sanojlawrence Connection failed: SQLSTATE[28000] [1045] Access denied for user 'user'@'host' (using password: YES) — rads89, Jan 08 '15 at 18:04
@rads89 there is problem with your `SQL` `username and password` try checking your `password user name table name host everthing is right` — sanoj lawrence, Jan 08 '15 at 18:08

score 0 · Answer 1 · answered Jan 09 '15 at 08:57

The user you are using to connect seems to have very restricted access rights. In order to view/change user privileges you'd need a user with more rights (e.g. the standard root user). The connection probably fails because the machine you connect from is not allowed for that specific user. Note that in MySQL you can specify the hosts a user is allowed to connect from.

It's rather unusual that you need a different server for your MySQL installation than that your script is running on. That's why it is usually enough to use localhost or the loop-back address (note here also differences between IPv4 and IPv6 as I have outline in my troubleshooting video: https://www.youtube.com/watch?v=DCgRF4KOYIY). However, if the servers are really different make sure your user is allowed to connect from the server your php script is running on. In the simpelst case you could add an anyhost entry (e.g. "myuser@%"), however the more restrictive the access rule is the better. Also make sure this user has access to the schema(s) and tables you need for your work. You can set all that in MySQL Workbench if you have a user that is allowed to change other user's privileges.

score 0 · Answer 2 · answered Jan 09 '15 at 20:34

SQLSTATE[28000] [1045] Access denied for user 'user'@'host' (using password: YES)

The reason for the above error is your password is wrong .

And before you check in your my.cnf (for Linux) or my.ini (for windows) bind-address=0.0.0.0

if bind address is disabled enable it ..

Issues connecting to mySql database on target machine

2 Answers2