2

My database server has only a private IP address. I am able to ssh into the database server from my application server. I have created an user with the app server's IP address.

Can anyone tell me how to access mysql databases from the app server using the db server's private IP address on ubuntu 16.04?

TrickyExplorer
  • 89
  • 1
  • 3
  • 11

2 Answers2

4

It's likely that the mysql server is bound to the loopback address, either 127.0.0.1 or the ipv6 equivalent, and so you would have to open that up to the LAN network. You can check that with the following command on the mysql server;

# netstat -lntp | grep 3306
tcp        0      0 127.0.0.1:3306     0.0.0.0:*    LISTEN   20080/mysqld

That option for binding to an ip is configured in my.cnf, or where ever the mysql config file is on your system, and can be changed to bind to all ips;

[mysqld]
bind-address                   = 127.0.0.1

change to 

[mysqld]
bind-address                   = 0.0.0.0

and restart the mysql server. Obviously it's important to ensure permissions have been set correctly on the mysql server.

Where the config file lives depends on which version of mysql you are using, I see it at; /etc/mysql/mysql.conf.d/mysqld.cnf on Ubuntu 16.04.3 running mysql-server-core-5.7 but it's at /etc/my.cnf on another CentOS box... so you might have to be more specific about that environment you are in...

You can test whether it's working by connecting to the private ip of the db server from the app server with the mysql-client

# echo "show databases;"  | mysql -uroot -psomepass -h localhost

Database
information_schema
wp_is_annoying_db
mysql
performance_schema
sys

if that's not working you should start with the basics

ping the db server private ip address;

$ ping 192.168.x.x
64 bytes from 127.0.0.1: icmp_seq=1 ttl=64 time=0.051 ms

use telnet or netcat to test the port

$ telnet localhost 3306
Trying 127.0.0.1...
Connected to localhost.

or use netcat, if telnet is not installed;

nc -v localhost 3306
Connection to localhost 3306 port [tcp/mysql] succeeded!
Tom
  • 10,886
  • 5
  • 39
  • 62
  • Thank you for your prompt reply! But, I have set my bind address to the private IP of my database server so that it listens to all connections within the network. It is not only that I want to access the instance from the app server but also that my php application should access the database as well. So SSH wouldn't be the right option. Any other solution to this issue? – TrickyExplorer Feb 06 '18 at 09:33
  • The port forwarding is an alternative. If you have set the bind address, and checked `netstat -lntp | grep 3306` you should be able to connect with php – Tom Feb 06 '18 at 10:00
  • are you getting an error? – Tom Feb 06 '18 at 10:14
  • Can port forwarding be used from the application? netstat on the server give's the IP of the server. I get this error ERROR 2003 (HY000): Can't connect to MySQL server on '10.0.3.41' (110) – TrickyExplorer Feb 06 '18 at 10:37
  • 100% packet loss while pinging, telnet and nc does keeps trying but does not connect(done from app server with db server's IP)! – TrickyExplorer Feb 06 '18 at 10:47
  • so you can ssh to the server, i.e. `ssh user@10.0.3.41`, but pinging it, and trying to telnet fails? (from the same console?) – Tom Feb 06 '18 at 10:48
  • If so, that suggests a firewall problem. try `iptables --list -v` on the dbserver and paste that out – Tom Feb 06 '18 at 10:49
  • I am able to ssh to the server! I am not able to paste my iptables here. So, these are my ufw rules To Action From -- ------ ---- 22 ALLOW Anywhere 80 ALLOW Anywhere 443 ALLOW Anywhere 3306 ALLOW Anywhere 22 (v6) ALLOW Anywhere (v6) 80 (v6) ALLOW Anywhere (v6) 443 (v6) ALLOW Anywhere (v6) 3306 (v6) ALLOW Anywhere (v6) – TrickyExplorer Feb 06 '18 at 11:10
0

Try to ping/telnet/nc to the db server from the app server. If this does not connect you to the db server. Disable the firewall(should be a firewall block and it was for me!)

TrickyExplorer
  • 89
  • 1
  • 3
  • 11