Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

Ok I have searched google for two days trying to get this to work. Here are the steps I have taken so far:

  1. Clean install of Ubuntu 10.10
  2. Install mysql 5.1 as well as admin
  3. Comment out the bind address in the config file
  4. Create a new database
  5. Create a new user that is username@% to allow remote connections
  6. Grant all access to this user to the new database EXCEPT the grant option
  7. Login on the server is ok using this new user and database on the localhost
  8. Login on the server is ok using this new user and database on the server internal network ip
  9. Login from a remote computer is ok using this new user and database using the internal network ip
  10. Login is not working when logging in with this username and database using the external ip address from the server or the remote computer.
  11. I have port forwarding enabled for this port and it is viewable from outside as confirmed by canyouseeme.org
  12. I have nmap'd using the following command on the internal ip and get the below result:

    nmap -PN -p 3306 192.168.1.73

    Starting Nmap 5.21 ( http://nmap.org ) at 2011-02-19 13:41 PST Nmap scan report for computername-System-Name (192.168.1.73) Host is up (0.00064s latency). PORT STATE SERVICE 3306/tcp open mysql

    Nmap done: 1 IP address (1 host up) scanned in 0.23 seconds

  13. I have nmap'd using the following command on the internal ip and get the below result(I have hidden ip for obvious reasons):

    nmap -PN -p 3306 xxx.xxx.xx.xxx

    Starting Nmap 5.21 ( http://nmap.org ) at 2011-02-19 13:42 PST Nmap scan report for HOSTNAME (xxx.xxx.xx.xxx) Host is up (0.00056s latency). PORT STATE SERVICE 3306/tcp closed mysql

    Nmap done: 1 IP address (1 host up) scanned in 0.21 seconds

I am completely stuck here and need some help. I have tried everything under the moon and still can not connect from a remote external ip address. Any help is greatly appreciated and I need to do anything to help find the problem let me know and I will post the results here.

share|improve this question
    
What's the obvious reason? Without that IP I don't really understand your step 13 and can't confirm my suspicion. However it doesn't look like your router is forwarding port 3306 to the internal machine... –  James Green Feb 19 '11 at 22:56
    
I have it forwarded. Changed it to DMZ to the server ip address as well just in case. Still no luck. –  BobFranz Feb 20 '11 at 0:21
    
Can we get the SQL statements you used to grant user access? –  Rilindo Oct 9 '11 at 17:23

5 Answers 5

Have you tried editing the file

/etc/mysql/my.cnf

Make sure line skip-networking is commented (or remove line) and add following line

user            = mysql
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
port            = 3306
basedir         = /usr
datadir         = /var/lib/mysql
tmpdir          = /tmp
language        = /usr/share/mysql/English
bind-address    = EXTERNAL(!!) IP SERVER
# skip-networking

Also, make sure the user has rights to the databases

mysql> CREATE DATABASE test;
mysql> GRANT ALL ON test.* TO user@'IP' IDENTIFIED BY 'supersecretpassword';
share|improve this answer
    
I have tried to look for the skip-networking but it is not in the config. Also I have tried the external IP address and that still doesn not help. I re-commented that line out. I have run the network tools and ran a port scan on the internal IP address and it is showing the port open for 3306. However when I run the same scan on my external IP it is not open. Yet I have DMZ'd to the server so I have no idea what is going on. –  BobFranz Feb 20 '11 at 0:25
    
Are there any other services on the server that are available to the outside world? NAT? –  Bart De Vos Feb 20 '11 at 0:46

Regardless of what you say, "I have it forwarded", it really looks like you don't.

Can you watch connections using netstat maybe?

watch -n1 "netstat -an|grep 3306"

I don't know what else to suggest. All indications are that the port is closed.

share|improve this answer

Check the bind-address in /etc/mysql/my.cnf as it is set to 127.0.0.1 by default on Debian-based systems. If you want to be able to connect to it from another machine besides localhost you will need to change this and restart mysql to do so.

You can confirm this is the case by running netstat -plunta |grep :3306 as root and you will likely see it bound only to 127.0.0.1 and when you modify the my.cnf and restart this will not be the case any more.

share|improve this answer

funny thing is that what worked for me is this: i did this command that somebody suggested above: 'netstat -plunta |grep :3306' and then i noticed that apache and ssh were both running with a foreign and local address of 0.0.0.0, whereas mysql had a local address of 127.0.0.1 as you would expect. So i changed it to 0.0.0.0 like the rest and in combination with granting privileges within mysql to other hosts, it worked!

so in short 0.0.0.0 functioned as a sort of wildcard when set as the bind-address.

share|improve this answer

I wouldn't want to expose MySQL to the internet at all. I would expose port 22 and ssh in from the outside. Forward a local port over the SSH tunnel to port 3306 on the remote server as shown in the first command below. Then connect to the remote server on port 3306 by connecting to the port you bound it to locally as shown in the second command.

eg.:
ssh -L 3307:0:3306 mysql@remotehost
(other shell:)
mysql -P 3307 -h localhost -u user -p

Note that you will only be able to connect and work with the MySQL cli in that second shell as long as the first one is open. It may be necessary to investigate SSH keep-alives. Note that you can also utilize this type of SSH tunnel on a Windows computer using Putty.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.