accessing mysql database from different server

Question

we have a server with LAMP setup and virtualmin installed. Now, we want to access the database on this server from some different server.

Can someone please help me with exact settings I need to do on both the servers ?

Also, how can I check the port mysql server is using ? It's a centos os based server.

Answer 1

First you need to check that your MySQL server is reachable from the network. If it's listening on 127.0.0.1 (like below), you need to modify your my.cnf file and add a "bind-address = 0.0.0.0" (for example).

$ netstat -tpan | grep :3306
tcp        0      0 127.0.0.1:3306          0.0.0.0:*               LISTEN      -               

Then, check you don't have any firewall rule (iptables) wich will block the 3306/tcp port from outside.

And at last, you need to create the user, allowed to connect from the remote IP. For example :

GRANT ALL ON `remoteuser`.* TO `mydatabase`@'10.1.2.3' IDENTIFIED BY 'thepasswordhere' ;

I hope it helps !