MySQL - Access denied when connecting locally but able to connect remotely

Question

I've got a PHP application hosted both on a server and locally with XAMP, and I've got a DB hosted in that same server, too.

When trying to establish a connection with my DB I instance a new mysqli object like this:

$mysqli = new mysqli(HOST, USER, PASS, MY_DB);

The value for those constants are the same for my local and remote applications but for some reason they work only when used on XAMP; I get 'Access denied for user' when trying to connect from the server and I have no idea what the problem might be.

Sorry for my bad english, and thanks for the help :)

Answer 1

There's two options: Either you are binding to the remote IP and not the local one or you messed up the permissions.

1. Binding the IP adress

If you followed an online tutorial on how to allow remote access, you probably set the config option bind-adress to you axternal IP. This means, your server will bind only externally but not on the internal loopback (127.0.0.1/localhost).

All you have to do is:

1. Open your config file (usually /etc/mysql/my.cnf) with root permissions
2. Locate the setting bind-adress and set it to 0.0.0.0

---

2. Permissions

Try granting the permission through the terminal to '<your user>'@'%'. If you don't understand what this means, read the GRANT manual here.

Here's an example for giving superuser permissions to a user from anywhere for every database:

GRANT ALL PRIVILEGES ON *.* TO '<your user>'@'%' WITH GRANT OPTION;

I wouldn't recommend using the above on a production server since it is a big security risk.