MySQL server not accessible from remote machine

Question

I have installed MySQL server in my local Ubuntu server (11.10). I can't connect to the server from a remote machine.

When I tried:- nmap localhost , It shows the following

PORT     STATE SERVICE
22/tcp   open  ssh
80/tcp   open  http
139/tcp  open  netbios-ssn
445/tcp  open  microsoft-ds
631/tcp  open  ipp
3306/tcp open  mysql

It means that 3306, the MySQL port is open, right? But when I tried nmap 192.168.0.50, which is the server IP, I get the following:-

PORT    STATE SERVICE
22/tcp  open  ssh
80/tcp  open  http
139/tcp open  netbios-ssn
445/tcp open  microsoft-ds

Does this mean the port is not open when accessing using IP? If so, how do I open the port?

I had tried the following code, but looks like it didn't work.

sudo iptables -A INPUT -p tcp --dport 3306 -j ACCEPT

What is wrong here?

Solution

Your MySQL service is bound to serve localhost only (interface binding). This is the default for security reasons. If you really need to access it directly from other hosts, there is a nice How to enable remote access to MySQL on Ubuntu which you could follow:

1. as root, open your /etc/mysql/my.cnf or /etc/mysql/mysql.conf.d/mysqld.cnf with your favorite editor, as on different systems it is found to be different.
2. look for the [mysqld] section, and in there for the bind-address keyword. This usually is set to 127.0.0.1 -- change that to match your "normal" IP-address
3. save the file, and reload the service (e.g. using service mysql restart)

Remember you must enable your remote users to access their database(s) from remote, by setting the appropriate GRANTs -- e.g.

GRANT ALL ON mydb.* TO remoteuser@'%' IDENTIFIED BY 'SomePASSWORD';

Note the @'%', which means "from any host".

Comments

[David Tod]

Glad to read -- and you're welcome!

[Russkiy]

link is broken :(

[David Tod]

@dino No longer. I fixed that and also included an excerpt, so if it dies again the steps required are still here. My bad I didn't do so from the beginning -- but as you can see from the timestamp, that was one of my early answers. We all learn :)

[Eric G]

The bind-address field was under /etc/mysql/mysql.conf.d/mysqld.cnf for my system.

[David Tod]

@EricG Thanks for the pointer. My answer was 5 years ago, so configs have changed a little (and probably made more modular).