connecting to mysql remotely

Submitted by responsiveny on Fri, 11/04/2011 - 20:25

hi what is the exact procedure to connect to mysql remotely? I searched the forums but some of the threads are old and it seems the Virtualmin team has added some capabilites in later versions to achieve this.

My VM server (with a private address) is behind a NAT firewall. I want a Navicat client to be able to attach from outside the firewall from a fixed IP.

Thank you

Status: 
Active

Comments

Submitted by andreychek on Fri, 11/04/2011 - 20:35

Howdy -- you could do that with 3 steps:

  1. You'll need to forward port 3306 from your router to your Virtualmin server

  2. Go into Webmin -> Servers -> MySQL Database Server -> MySQL Server Configuration, and set "MySQL server listening address" to "0.0.0.0" to get it listening on all interfaces.

  3. For any Virtual Server that should have remotely accessible databases, go into Edit Databases -> Remote Hosts, and add the IP address from the remote system there.

Submitted by responsiveny on Fri, 11/04/2011 - 20:57

Thanks for the quick response as usual.

I can't seem to connect remotely.

To test, I tried telnet to 3306 and I'm getting connection refused.

When I check the firewall logs, I do see the traffic passed to the correct port (see below)

Nov 4 21:50:58 WAN :50019 192.168.1.101:3306 TCP:S

where 192.168.1.101 is the local internal IP of the virtualmin server

I followed your steps and in the remote hosts I entered the

Submitted by andreychek on Fri, 11/04/2011 - 21:02

After making the changes to the MySQL config, you may need to restart the MySQL service in order for those settings to be active.

Submitted by responsiveny on Fri, 11/04/2011 - 21:14

yes, did that. still no good.

Submitted by responsiveny on Fri, 11/04/2011 - 21:19

if I check linux firewall under webmin i do not see a rule allowing 3306. should i? i have not made any changes to linux firewall since the install of virtualmin, its all default.

Submitted by andreychek on Fri, 11/04/2011 - 22:04

Well, there wouldn't be a firewall setup on your system by default.

What output do you get if you run these two commands:

iptables -L -n
netstat -an | grep 3306

Submitted by responsiveny on Fri, 11/04/2011 - 22:17

iptables -L -n

bash: iptables: command not found

netstat -an | grep 3306 tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN

what could be blocking?

Submitted by andreychek on Fri, 11/04/2011 - 22:54

Yeah, MySQL is definitely listening... however, that iptables command should be installed.

Try running it will the full path, like this:

/sbin/iptables -L -n

Submitted by responsiveny on Fri, 11/04/2011 - 22:55

it turns out my client, Navicat, has an option to connect to MySQL via an SSH tunnel. So I am no longer in need to expose the MySQL port 3306 to the Internet. This is good.

I have reset MySQL to only listen on localhost and I am connecting remotely by SSH tunnel.

This does not, however, explain why I was unable to connect when MySQL was listening on 0.0.0.0

Thank you

Submitted by responsiveny on Sat, 11/05/2011 - 16:26

Here's the full iptables:

# /sbin/iptables -L -n
Chain INPUT (policy ACCEPT)
target     prot opt source               destination        
ACCEPT     udp  --  0.0.0.0/0            0.0.0.0/0           udp dpt:20
ACCEPT     udp  --  0.0.0.0/0            0.0.0.0/0           udp dpt:21
ACCEPT     udp  --  0.0.0.0/0            0.0.0.0/0           udp dpt:53
ACCEPT     tcp  --  0.0.0.0/0            0.0.0.0/0           tcp dpt:20000
ACCEPT     tcp  --  0.0.0.0/0            0.0.0.0/0           tcp dpt:10000
ACCEPT     tcp  --  0.0.0.0/0            0.0.0.0/0           tcp dpt:443
ACCEPT     tcp  --  0.0.0.0/0            0.0.0.0/0           tcp dpt:80
ACCEPT     tcp  --  0.0.0.0/0            0.0.0.0/0           tcp dpt:993
ACCEPT     tcp  --  0.0.0.0/0            0.0.0.0/0           tcp dpt:143
ACCEPT     tcp  --  0.0.0.0/0            0.0.0.0/0           tcp dpt:995
ACCEPT     tcp  --  0.0.0.0/0            0.0.0.0/0           tcp dpt:110
ACCEPT     tcp  --  0.0.0.0/0            0.0.0.0/0           tcp dpt:20
ACCEPT     tcp  --  0.0.0.0/0            0.0.0.0/0           tcp dpt:21
ACCEPT     tcp  --  0.0.0.0/0            0.0.0.0/0           tcp dpt:53
ACCEPT     tcp  --  0.0.0.0/0            0.0.0.0/0           tcp dpt:25
ACCEPT     tcp  --  0.0.0.0/0            0.0.0.0/0           tcp dpt:22
RH-Firewall-1-INPUT  all  --  0.0.0.0/0            0.0.0.0/0          

Chain FORWARD (policy ACCEPT)
target     prot opt source               destination        
RH-Firewall-1-INPUT  all  --  0.0.0.0/0            0.0.0.0/0          

Chain OUTPUT (policy ACCEPT)
target     prot opt source               destination        

Chain RH-Firewall-1-INPUT (2 references)
target     prot opt source               destination        
ACCEPT     all  --  0.0.0.0/0            0.0.0.0/0          
ACCEPT     icmp --  0.0.0.0/0            0.0.0.0/0           icmp type 255
ACCEPT     esp  --  0.0.0.0/0            0.0.0.0/0          
ACCEPT     ah   --  0.0.0.0/0            0.0.0.0/0          
ACCEPT     udp  --  0.0.0.0/0            224.0.0.251         udp dpt:5353
ACCEPT     udp  --  0.0.0.0/0            0.0.0.0/0           udp dpt:631
ACCEPT     tcp  --  0.0.0.0/0            0.0.0.0/0           tcp dpt:631
ACCEPT     all  --  0.0.0.0/0            0.0.0.0/0           state RELATED,ESTABLISHED
ACCEPT     tcp  --  0.0.0.0/0            0.0.0.0/0           state NEW tcp dpt:22
REJECT     all  --  0.0.0.0/0            0.0.0.0/0           reject-with icmp-host-prohibited

Submitted by andreychek on Sat, 11/05/2011 - 17:52

it does appear that a firewall has been setup on your server -- and anything not explicitly allowed would be denied.

So, if you'd like to do something like that in the future -- what you could do is go into Webmin -> Networking -> Linux Firewall, and add a firewall rule to allow port 3306.

nelsondcosta's picture
Submitted by nelsondcosta on Fri, 12/28/2018 - 10:13

Thanks andreychek, worked on me :)