6

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?

migrated from stackoverflow.com Mar 26 '14 at 0:16

This question came from our site for professional and enthusiast programmers.

4

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.

  • When I net stop mysql I don't see anything listening on :3306. When I net start mysql and quickly netstat -an I see both listen entries. – ebyrob Mar 20 '14 at 14:04
  • @ebyrob That covers one of the two possibilities I mentioned. – Michael Hampton Mar 20 '14 at 14:10
  • 1
    MySQL bug submitted: bugs.mysql.com/bug.php?id=72087 – ebyrob Mar 20 '14 at 14:25
  • 1
    @ebyrob What happened to your bug report? It says "You do not have access to bug #72087." – RandomSeed Mar 23 '14 at 10:24
  • @RandomSeed You have to have a user account with Oracle and log in to see it. Doesn't appear to have gone through triage yet. – ebyrob Mar 24 '14 at 14:50
1

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.

  • Oh, so they intended to do this. That means it's not a bug, it's a design flaw... – Michael Hampton Mar 24 '14 at 20:59
  • @MichaelHampton It looks to me like they intended this option to fix this bug: bugs.mysql.com/bug.php?id=67119 But didn't realize what would happen on win7 if they also enabled IPV6_V6ONLY for addresses other than wildcard. – ebyrob Mar 25 '14 at 13:21
-1

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.

  • Again, I'd like to bind to ::1 unless I'm planning to disable IPv6 (Microsoft recommends against disabling IPv6). I can connect to 127.0.0.1 by specifying it explicitly, but then any tool or user selecting localhost won't work. (I actually noticed the problem when I had performance issues binding to 127.0.0.1 and connecting to localhost on windows 7, it was about 30 seconds per connection) – ebyrob Mar 20 '14 at 13:59
  • Sorry, I should have been more specific; put both: bind-address = 127.0.0.1 bind-address = ::1 – klugerama Mar 20 '14 at 17:44
  • It looks like if I put in multiple bind-address entries, only the bottom value gets used. I get listen on 127.0.0.1:3306 or ::1:3306 and 0.0.0.0:3306. – ebyrob Mar 21 '14 at 15:02
  • Hmm, you're right. I had that in one of my installations and never checked the first address. Have you checked your hosts file? See victor-ratajczyk.com/post/2012/02/25/… Also, it may be helpful to post your mysql log file from right after it starts. – klugerama Mar 21 '14 at 16:08
  • I could change my hosts file but I didn't want to break some other windows process that might expect localhost to resolve to the new default of ::1. Considering every installation of MySQL 5.6 on win7 I've seen exhibits the same behavior, I don't think posting my particular log file will be useful here. – ebyrob Mar 21 '14 at 19:34
-1

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.

  • on 64-bit Windows 7 - I don't have Unix NTP, /etc or / for that matter. Binding to 127.0.0.1 works, if I don't use localhost in my connection string and if I'm sure I don't ever want to use IPv6 to connect locally. (I've got localhost all over my client-side config files and scripts for some reason, and other devs tend to type it in if they checkout/build and use their own client.) PS - For now, our solution is "remove bind-address specification and listen on 0.0.0.0:3306 with fire-walling". It works, but it's a bit like driving on a metal plate. – ebyrob Mar 24 '14 at 14:56
  • check my edit now . – echo_Me Mar 24 '14 at 15:22
  • Network Time still seems irrelevant. Are you suggesting I write a build script to modify other developer's host files with an obsolete host file configuration? – ebyrob Mar 24 '14 at 18:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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