bind-address = ::1 # however, netstat shows 0.0.0.0:3306 LISTENING

Question

I'm running MySQL 5.6 on 64-bit Windows 7 with IPv6 and IPv4 enabled.

In my.ini:

port=3306
bind-address = ::1

I was hoping to use IPv6 and restrict to the loop-back adapter. I used ::1 instead of 127.0.0.1 because win7 with IPv6 defaults to ::1 for localhost.

With this configuration netstat reports the following:

C:\>netstat -an |findstr 3306
  TCP    0.0.0.0:3306           0.0.0.0:0              LISTENING
  TCP    [::1]:3306             [::]:0                 LISTENING

It seems like connections may be getting blocked on the IPv4 interface, but seeing netstat report that 0.0.0.0 port 3306 is open makes me uneasy.

The MySQL documentation specifies:

If the address is a “regular” IPv4 or IPv6 address (such as 127.0.0.1 or ::1), the server accepts TCP/IP connections only for that IPv4 or IPv6 address.

TCPView gave me the same info as netstat. I couldn't get TDIMon working on my system.

How can I prevent mysql from listening on 0.0.0.0?

Answer 1

I see two possibilities here:

1. You actually have two copies of MySQL running, one of which bound to IPv4, and the other which bound to IPv6. This is probably not very likely, but it's something you should check for anyway.

2. You've found a bug in the Windows port of MySQL. When I tried this on Linux, bind-address = ::1 caused MySQL to bind only to ::1 and not to any IPv4 addresses. In this case you should report it as a bug to MySQL.

Answer 2

I've finally had time to dig into the mysql 5.6 source. Line 2405 of mysqld.cc seems to hold some answers.

 /*
   For interoperability with older clients, IPv6 socket should
   listen on both IPv6 and IPv4 wildcard addresses.
   Turn off IPV6_V6ONLY option.

   NOTE: this will work starting from Windows Vista only.
   On Windows XP dual stack is not available, so it will not
   listen on the corresponding IPv4-address.
 */
if (a->ai_family == AF_INET6)
{
  arg= 0;

  if (mysql_socket_setsockopt(ip_sock, IPPROTO_IPV6, IPV6_V6ONLY,/* Line: 2405 */
                              (char *) &arg, sizeof (arg)))
  {
    sql_print_warning("Failed to reset IPV6_V6ONLY flag (error: %d). "
                      "The server will listen to IPv6 addresses only.",
                      (int) socket_errno);
  }
}

Setting this socket option causes the extra line 0.0.0.0:3306 to show up in netstat. However, I'm still not able to connect by specifying 127.0.0.1 to telnet.

The comments do make it sound like this option should only be used when wildcard is applied, but it seems the option is applied even when bind-address is specified.

Possibly related discussion here: http://serverdown.ttwait.com/que/486038

google cache: http://webcache.googleusercontent.com/search?q=cache:14aq4-3tRLsJ:serverdown.ttwait.com/que/486038+&cd=4&hl=en&ct=clnk&gl=us

EDIT: In the bug report (which is still in triage), I've proposed MySQL only clear IPV6_V6ONLY when binding to the wildcard address *. This seems to give the most flexibility of configuration in my.ini. If some admins are binding :: and then connecting to 127.0.0.1 they could be surprised. Binding * instead of :: would fix it.

Answer 3

0.0.0.0:3306 means that MySQL is listening on all adapters/addresses for IPv4. I would put the following lines in my.cnf:

bind-address = 127.0.0.1
bind-address = ::1

That should restrict IPv4 access to localhost; it shouldn't matter which version Windows defaults to, you'll still be able to connect to it. If that doesn't resolve it, then you likely have bind-address set in another file somewhere.

Also, make sure that mysql is actually stopped when you restart it; it could simply be that an older instance is still running and listening on 0.0.0.0.

EDIT

See MySQL Documentation section 4.2.3.3 Using Option Files for other files that the bind-address parameter may be specified in.

Answer 4

The solution is in conf file .Try to Remove all -I or --interface options from /etc/default/ntp and insert the following into your /etc/ntp.conf:

interface ignore wildcard
interface listen 127.0.0.1

• Without interface ignore wildcard NTP will also listen on 0.0.0.0

• Without interface listen NTP will only listen on 127.0.0.1 (of course)

If you already have that line:

0.0.0.0:3306

then just comment it to

# 0.0.0.0:3306

** Secondly try change following line in my.ini

 bind-address = ::1

to

 bind-address = 127.0.0.1

EDIT:

if you using windows7 64-bit,

you have to go to that file here:

 C:\WINDOWS\system32\drivers\etc\hosts

Localhost should be the first entry in that file.That hosts file may have ::1 for localhost. To fix it, add (or uncomment) the line:

   127.0.0.1       localhost

and comment that line:

   ::1             localhost 

to

   # ::1             localhost

and then you can connect via localhost.