解决远程连接mysql很慢的方法(mysql_connect 打开连接慢)
[mysqld]
skip-name-resolve
在linux下配置文件是/etc/my.cnf,在windows下配置文件是mysql安装目录下的my.ini文件。注意该配置是加在 [mysqld]下面,在更改配置并保存后,然后重启mysql并远程连接测试,一切恢复如初。该参数的官方解释信息如下:
How MySQL uses DNS
When a new thread connects to mysqld, mysqld will spawn a new thread to handle the request. This thread will first check if the hostname is in the hostname cache. If not the thread will call gethostbyaddr_r() and gethostbyname_r() to resolve the hostname.
If the operating system doesn't support the above thread-safe calls, the thread will lock a mutex and call gethostbyaddr() and gethostbyname() instead. Note that in this case no other thread can resolve other hostnames that is not in the hostname cache until the first thread is ready.
You can disable DNS host lookup by starting mysqld with –skip-name-resolve. In this case you can however only use IP names in the MySQL privilege tables.
If you have a very slow DNS and many hosts, you can get more performance by either disabling DNS lookop with –skip-name-resolve or by increasing the HOST_CACHE_SIZE define (default: 128) and recompile mysqld.
You can disable the hostname cache with –skip-host-cache. You can clear the hostname cache with FLUSH HOSTS or mysqladmin flush-hosts.
If you don't want to allow connections over TCP/IP, you can do this by starting mysqld with –skip-networking.
根据文档说明,如果你的mysql主机查询DNS很慢或是有很多客户端主机时会导致连接很慢,由于我们的开发机器是不能够连接外网的,所以DNS解析是不可能完成的,从而也就明白了为什么连接那么慢了。同时,请注意在增加该配置参数后,mysql的授权表中的host字段就不能够使用域名而只能够使用 ip地址了,因为这是禁止了域名解析的结果。
相关文章
- MySQL连接技术简明教程(mysql连接教程)
- MySQL数据表变更小结(mysql更改数据表)
- MySQL 数据库:应对变化的挑战(mysql数据库变化)
- Mysql 自动编号:简便生成唯一ID(mysql自动编号)
- 学习MySQL数据库:免费学习体验!(mysql数据库免费)
- MySQL查询:如何查询指定年龄的人?(mysql查询年龄)
- MySQL连接密码:获取与更改(mysql连接密码)
- MySQL:深入了解查看数据库的方法(mysql中查看数据库)
- 通过 JavaScript 实现MySQL数据库连接(js连接mysql)
- 连接MySQL授权:查看连接信息(mysql授权查看)
- MySQL连接服务器:失败的挫折(mysql连接服务器失败)
- 表MySQL命令行快速查看表结构(mysql命令行显示)
- 比较:Hive与MySQL的优势比较(hive和mysql)
- MySQL使用详解:全面学习MYSQL技术(mysql大全)
- 大小Mysql最大数据表大小探究(mysql最大表)
- 解决CRT进入MySQL管理的方法(crt怎么进入mysql)
- MySQL中input函数的使用方法分享(mysql中input)
- 数据库从命令行连接MySQL数据库一步一步操作指南(cmd连接外部mysql)
- 表用CMD简单操作MySql新建表(cmd mysql 新建)
- 解决方法MySQL无法读取INI文件的问题(mysql不加载ini)
- MySQL限制远程安装,该怎么办(mysql不允许远程安装)