remote mySQL user access

Submitted by securewebs on Fri, 01/17/2014 - 18:03 Pro Licensee

I have not been able to get a remote user setup for mySQL. Adding IP's to create a remote user does not work. I must be missing something. What could it be? I am using the IP of the virtual server (dedicated) and the database is on the same machine. I have done it before at the request of a client though I don't remember what I did to make it work. Not sure where to go from here. Any suggestions?

Scott Hirsch

Status: 
Active

Comments

Submitted by andreychek on Fri, 01/17/2014 - 18:07

Howdy -- what error do you receive when trying to remotely login to your database?

Submitted by securewebs on Fri, 01/17/2014 - 18:35 Pro Licensee

I am using the latest version of Navicat. The error is:

2003 - Can't connect to MySQL server on '64.85.13.102" (10038)

Submitted by andreychek on Fri, 01/17/2014 - 18:40

Okay, what output do you see if you run this command on your server:

netstat -an | grep :3306

Submitted by securewebs on Fri, 01/17/2014 - 19:25 Pro Licensee

tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN

Submitted by andreychek on Fri, 01/17/2014 - 19:28

Okay, that's good, it's listening for connections on all IP's.

Is your firewall set to allow connections to port 3306?

There isn't a firewall setup by default, but if one were enabled on your server, that could cause the problems you're seeing.

Similar if your server is behind a NAT router -- you may need to forward port 3306 in that case.

Submitted by securewebs on Fri, 01/17/2014 - 19:42 Pro Licensee

Just the default script with very little changes. No changes in IPTables. No changes to the firewall at all. No NAT router. Simple really. And I swear I have done it before. The remote user I understand has to have your IP in the username, which is what it does. Any chance a logfile would give a clue?

Submitted by Locutus on Sat, 01/18/2014 - 05:05

I can confirm that port 3306 times out on the IP 64.85.13.102. As a test, I tried ports 25 and 80, which both work.

So if you're not using a NAT setup which requires port forwardings, either the local firewall on your system is blocking access, or your hoster (you might want to ask them if they block that port) or some system along the way is doing so. What output do you get for iptables -L?

Submitted by andreychek on Sat, 01/18/2014 - 08:55

Could you paste in the output of the command iptables -L -n? We can use that to determine if there's a firewall issue.

Submitted by andreychek on Sat, 01/18/2014 - 09:15

Oops, and Locutus had actually asked for that iptables output as well, I had missed that :-)

Submitted by Locutus on Sat, 01/18/2014 - 09:18

@Eric: Yep, but you added the rather useful "-n" option, so that's fine. ;) (@securewebs: It turns off resolution of IP addresses to names, speeding up the process and circumventing potential DNS issues.)

Submitted by securewebs on Sat, 01/18/2014 - 15:31 Pro Licensee

[root@pivot etc]# iptables -L Chain INPUT (policy ACCEPT) target prot opt source destination ACCEPT udp -- anywhere anywhere udp dpt:ftp-data ACCEPT udp -- anywhere anywhere udp dpt:ftp ACCEPT udp -- anywhere anywhere udp dpt:domain ACCEPT tcp -- anywhere anywhere tcp dpt:dnp ACCEPT tcp -- anywhere anywhere tcp dpt:ndmp ACCEPT tcp -- anywhere anywhere tcp dpt:https ACCEPT tcp -- anywhere anywhere tcp dpt:http ACCEPT tcp -- anywhere anywhere tcp dpt:imaps ACCEPT tcp -- anywhere anywhere tcp dpt:imap ACCEPT tcp -- anywhere anywhere tcp dpt:pop3s ACCEPT tcp -- anywhere anywhere tcp dpt:pop3 ACCEPT tcp -- anywhere anywhere tcp dpt:ftp-data ACCEPT tcp -- anywhere anywhere tcp dpt:ftp ACCEPT tcp -- anywhere anywhere tcp dpt:domain ACCEPT tcp -- anywhere anywhere tcp dpt:submission ACCEPT tcp -- anywhere anywhere tcp dpt:smtp ACCEPT tcp -- anywhere anywhere tcp dpt:ssh ACCEPT all -- anywhere anywhere state RELATED,ESTABLISHED ACCEPT icmp -- anywhere anywhere ACCEPT all -- anywhere anywhere ACCEPT tcp -- anywhere anywhere state NEW tcp dpt:ssh REJECT all -- anywhere anywhere reject-with icmp-host-prohibited

Chain FORWARD (policy ACCEPT) target prot opt source destination REJECT all -- anywhere anywhere reject-with icmp-host-prohibited

Chain OUTPUT (policy ACCEPT) target prot opt source destination

I confess, I have never done anything in particular with IPTables. Not the most experience server admin but the buck stops with me. I own a 50 server hosting company with about 40 Windows servers and perhaps10 CentOS. About 5 or 6 Virtualmin servers with license to host up to 100 account. Most of the server average about 60 right now. This is the newest server and has only about 12 accounts. A strong solid-state Xeon machine with 16GB - overkill really.

I will try to determine what could be blocking port 3306 I am just wondering if the GUI is not successful when adding a remote user.

Submitted by Locutus on Sat, 01/18/2014 - 15:38

Okay, your iptables output suggests that it is indeed your local firewall that not blocking port 3306. While the policy (the default behavior) is set to "accept", there's a rule at the end that rejects everything that's not covered by a previous rule. And I don't see a rule that allows port 3306. You might want to add one, then accessing the server from the outside should work.

Be aware though that in general it's not good practice to open up MySQL to the whole Internet. There e.g. has been an incident with a serious security hole in MySQL, caused by programming bugs, that allowed an attacker to access the database with root rights without specifying a password, in one out of 256 attempts randomly.

It's strongly recommended to open it only to specific IP addresses, or maybe through a VPN connection (the latter is what I do on my systems).