Mysql – How to enable remote access to wamp thesql

mysql-5.6mysql-workbenchwamp

I have wamp server which is installed in windows server. Since database is becoming large want to move code to other machine from there I want logon to wamp mysql.

I have disabled bind-to-address and created new user with all permission, but unfortunately not able to connect mysql remotely.

Steps I followed:

CREATE USER 'user'@'x.x.x.x' IDENTIFIED BY 'password';

GRANT ALL PRIVILEGES ON *.* TO 'user'@'x.x.x.x';

Please let me what is steps do I need to follow.

Best Answer

When you grant access to the MySQL instance to a user account you have to ensure you are allowing access from the remote host.

GRANT ALL PRIVILEGES ON *.* TO 'user'@'x.x.x.x';

In your example the x.x.x.x should be the IP address of the application server or if the server has DNS resolution it can be the hostname (A record or CName) of the application server.

E.g.

Given the following values:

APP Server ....: 192.168.10.12
APP Host Name .: myappserver.domain.com

DB Server .....: 192.168.10.25
DB Host Name ..: mydbserver.domain.com

...your statements should be:

GRANT ALL PRIVILEGES ON *.* TO 'user'@'192.168.10.12';

or:

GRANT ALL PRIVILEGES ON *.* TO 'user'@'myappserver.domain.com';

You are granting access to a user from a host/ip_address (myappserver) to the MySQL instance (mydbserver).

Then you have to ensure that you can TELNET into the MySQL server from the application server on port 3306 (default; or the port you have configured MySQL to listen on). On App Server: 

bash> telnet mydbserver 3306

You should be greeted by the following if the firewall is open:

Connected to mydbserver.
Escape character is '^]'.
[

If not you may have a firewall issue. Ensure that the firewall is open from myappserver to mydbserver and port 3306 (default).

Related Question