1

I have a CentOS VM on my laptop running a Host-only and a NAT adapters.

I'm looking to connect to an external MySQL database on a Bluehost server. According to Bluehost the port 3306 is open.

When I run

nmap -v -sV localhost -p 3306

on my VM, it discovers open port 3306/tcp on 127.0.0.1.

I'm not sure if I want the port on 127.0.0.1 since my IP is 192.168.56.101? Not sure how to configure this further. Thanks in advance.


In iptables, I added these two lines:

-A INPUT -p tcp -m state --state NEW -m tcp --dport 3306 -j ACCEPT
-A INPUT -p tcp -m state --state NEW -m tcp --sport 3306 -j ACCEPT

Not sure whether this was a destination or a source or both?! Is this even INPUT?

4
  • Sorry but I don't understand what you mean. Are you running that nmap command on the Bluehost server? Or your VM? What are you actually asking? Can your VM not connect correctly to the MySQL DB on the Bluehost server. As an aside, make sure the DB is locked down on the server so that only your VM (or other authenticated users) can access the database. And that data is going over the wire encrypted.
    – webtoe
    Mar 5, 2012 at 17:44
  • I'm running nmap on the VM. Correct, my VM cannot connect to the MySQL DB on Bluehost. Not sure how to "lock down" the connection, although I'm pretty sure Bluehost handles that by allowing certain IPs.
    – AlxVallejo
    Mar 5, 2012 at 17:45
  • I would strongly consider reading the MySQL documentation especially regarding administering it and security. The loopback device (127.0.0.1) is a special address and not accessible from outside. It is there to enable you to connect to MySQL using TCP from the same machine as MySQL is running on.
    – webtoe
    Mar 5, 2012 at 17:51
  • Yeah, I figured that 127.0.0.1 is not what I'm looking for. I tried opening the port on iptables. I'll edit the question and show you.
    – AlxVallejo
    Mar 5, 2012 at 17:59

2 Answers 2

3

You will need to grant privileges to allow a user to connect remotely as well as changing the bind-address in your /etc/mysql/my.cnf:

bind-address            = 127.0.0.1

Change it to:

bind-address            = 0.0.0.0

restart your mysql-server

/etc/init.d/mysql restart

0.0.0.0 means that you will listen on all interfaces.

Now to grant a user its rights to connect remotely:

GRANT ALL PRIVILEGES ON DATABASE.* TO 'user'@'%' IDENTIFIED BY "pass";

% means from any ip, if you only want to allow this for a certain IP, replace % with the ip.

13
  • I can run a mysqldump via SSH so I wouldn't think that this is an issue with Bluehost's configuration and Bluehost is telling me that the bind-address cannot be changed from 127.0.0.1. Is my iptables setup correctly?
    – AlxVallejo
    Mar 5, 2012 at 18:27
  • 2
    Trust me, you need to change the bind address if you want remote connection. Unless you use a tunnel to your server, you will need this . I doubt that there are any rules blocking internal traffic present in your firewall. Please give me the output of iptables --list. If you run mysqldump through ssh you are basically running it on your server so there is no problem since you are listening on the local host. Mar 5, 2012 at 18:31
  • iptables --list output: screencast.com/t/2YFpoP2BVl
    – AlxVallejo
    Mar 5, 2012 at 18:46
  • 1
    If you provider does not allow you to listen on anything but your loop-back, then you will need to tunnel in as Lucas says... ssh -L3307:localhost:3306 username@serveraddress You can then mysql -u mysqluser -p 3307 or point your sql tools to open localhost port 3307. This assume you are able to ssh into the remote server and that you have a locally running mysql server using port 3306 Mar 5, 2012 at 18:54
  • Your table defaults to accepting everything, so your firewall is not dropping anything. So you need to fix your firewall as well I'm affraid. If Blue Oyster don't want to change the policy then I'm affraid you are out of options. However tell me how you can run iptables on your VPS, but not be allowed to change the my.cnf on your own VPS... Mar 5, 2012 at 18:57
0

If you are able to see the remote server port 3306 is open from your virtual machine, then this sounds like a MySQL configuration issue. The MySQL instance on your other server is likely refusing your connection since it isn't a local connection. This is the out of the box behavior with MySQL; it won't accept remote connections.

If you want to enable remote connections, try issuing the following from your MySQL instance:

mysql> GRANT ALL PRIVILEGES ON *.* TO '[email protected]' IDENTIFIED BY "pass";

This will allow the user, yourUsername to connect to the MySQL server from the IP address xxx.yyy.zzz.aaa.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .