1

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.

2
  • Could you post how you granted access to the database? Which statements did you issue? Do these users have access from www.xxx.yyy.zzz? Did you issue the correct GRANT ALL ON *.* TO '<user>'@'<ip_addresss>' ...?
    – John K. N.
    Sep 19 '17 at 11:22
  • @hot2use updated steps
    – niran
    Sep 19 '17 at 12:11
1

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).

3
  • Excellent explanation!!!! I have not tried, but there is lot of things to learn form your answer ........!!!!
    – niran
    Sep 19 '17 at 13:00
  • After changing configuration, I am keep on getting this error after sometime (2003, "Can't connect to MySQL server on '127.0.0.1' (10061)")
    – niran
    Sep 19 '17 at 14:45
  • Based on your feedback I guess you now possibly have two WAMP instances running. You din't need the M on your second WAMP (Windows Apache Mysql Php) application server. Make sure you enable the user on the DB server and that the MySQL instance on your App server is disabled (stop service).
    – John K. N.
    Sep 19 '17 at 14:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.