windows2016 mysql连接慢,localhost被解析为ipv6的解决办法

admin 2019-07-29 PM 442℃ 0条

今天遇到个奇怪的问题,mysql用localhost连接很慢,影响查询效率,服务器是windows2016,改用127.0.0.1就正常。网上查找资料,找到了一篇英文文章(原贴地址:http://superuser.com/questions/436574/ipv4-vs-ipv6-priority-in-windows-7/436944#436944)。大概意思是ipv6优先级高,localhost默认走ipv6地址。所以mysql用localhost时windows把localhost 解析为 ipv6 地址 ::1 而不是 127.0.0.1,发现连不上失败再走ipv4,这样就影响了数据库查询效率,目前的解决办法是修改用127.0.0.1连接mysql。另外还有一个解决方案,修改my.ini,添加bind-address = :: 监听ipv6,还可以按下面说的修改注册表。

SOLUTION #1: ADD A PREFIX POLICY TO PREFER IPV4 ADDRESSES OVER IPV6
Prefix policy table is similar a routing table, it determines which IP addresses are preferred when making a connection. Note that higher precedence in prefix policies is represented by a lager "precedence" value, exactly opposite to routing table "cost" value.

Default Windows prefix policy table:

C:\>netsh interface ipv6 show prefixpolicies
Querying active state...

Precedence Label Prefix
---------- ----- --------------------------------
50 0 ::1/128
40 1 ::/0
30 2 2002::/16
20 3 ::/96
10 4 ::ffff:0:0/96
5 5 2001::/32
Note that IPv6 addresses (::/0) are preferred over IPv4 addresses (::/96, ::ffff:0:0/96).

We can create a policy that will make Hurricane Electric IPv6 tunnel less favourable than any IPv4 address:

netsh interface ipv6 add prefixpolicy 2001:470::/32 3 6
2001:470::/32 is Hurricane Electric's prefix, 3 is a Precedence (very low) and 6 is a Label.

I could have used a more generic prefix, but I wanted to make sure than if and when I get a direct IPv6 connectivity from an ISP, it will take precedence over IPv4.

If you adapt this solution, you need to substitute an appropriate IPv6 prefix instead of my Hurricane Electric one.

SOLUTION #2: TWEAK REGISTRY TO MAKE WINDOWS ALWAYS PREFER IPV4 OVER IPV6
This solution is more generic, but more invasive and less standards-compliant. In the end, Windows will still modify the prefix policy table for you.

Open RegEdit, navigate to HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\tcpip6\Parameters
Create DisabledComponents DWORD registry value, set its value to 20 (Hexadecimal). SeeMicrosoft KB 929852 for more info about this registry key, especially if DisabledComponentsalready exists on your system.
Reboot.

解决办法:

这篇文章解决办法是修改注册表。在命令行里面输入regedit打开注册表,找到键 HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\tcpip6\Parameters ,添加类型为DWORD 名字为 DisabledComponents 的项(已经有了的不用添加直接改值)。然后修改值为 20,值类型为16进制 。修改之后保存重启电脑。

标签: none

非特殊说明,本博所有文章均为博主原创。