补充七：mysql服务器设置优之连接调优

MySQL服务器设置优化之一-数据库连接速度调优

---

如需转载请标明出处：http://blog.csdn.net/itas109
QQ技术交流群：129518033

环境：
MySQL版本：5.5.15
操作系统：windows

1.禁止域名解析

优化参数：skip-name-resolve
参数作用：禁止域名解析的（包括主机名）。仅适用IP地址进行连接。如果使用此选项，授予表中的所有主机列值必须是IP地址或本地主机(localhost)。
优化方法：
在mysql的配置文件my.ini中添加skip-name-resolve参数。

[mysqld]

# Skip reverse DNS lookup of clients
skip-name-resolve

• 1
• 2
• 3
• 4

注意：
配置文件中加入skip-name-resolve就表示禁用域名解析，删除该参数就表示开启域名解析。不需要设置skip-name-resolve=ON或者skip-name-resolve=OFF，这些都是不正确的

原文：
–skip-name-resolve

Do not resolve host names when checking client connections. Use only IP addresses. If you use this option, all Host column values in the grant tables must be IP addresses or localhost. See Section 8.12.5.2, “DNS Lookup Optimization and the Host Cache”.

Depending on the network configuration of your system and the Host values for your accounts, clients may need to connect using an explicit –host option, such as –host=localhost, –host=127.0.0.1, or –host=::1.

An attempt to connect to the host 127.0.0.1 normally resolves to the localhost account. However, this fails if the server is run with the –skip-name-resolve option, so make sure that an account exists that can accept a connection. For example, to be able to connect as root using –host=127.0.0.1 or –host=::1, create these accounts:

CREATE USER 'root'@'127.0.0.1' IDENTIFIED BY 'root-password';
CREATE USER 'root'@'::1' IDENTIFIED BY 'root-password';

• 1
• 2

2.禁止TCP/IP连接

优化参数：skip-networking
参数作用：禁止TCP/IP连接。所有与mysqld的交互都必须使用命名管道或共享内存（在Windows上）或Unix套接字文件（在Unix上）。 强烈建议仅允许本地客户端的系统使用此选项。
优化方法：
在mysql的配置文件my.ini中添加skip-networking参数。

[mysqld]

#Do not listen for TCP/IP connections at all
skip-networking

• 1
• 2
• 3
• 4

注意：
开启该选项后就不能远程访问MySQL
为安全考虑希望指定的IP访问MySQL，可以在配置文件中增加bind-address=IP，前提是关闭skip-networking
bind-address=192.168.1.100

原文：
–skip-networking

Do not listen for TCP/IP connections at all. All interaction with mysqld must be made using named pipes or shared memory (on Windows) or Unix socket files (on Unix). This option is highly recommended for systems where only local clients are permitted. See Section 8.12.5.2, “DNS Lookup Optimization and the Host Cache”.

 

MySQL服务器设置优化之二-连接数调优

---

如需转载请标明出处：http://blog.csdn.net/itas109
QQ技术交流群：129518033

环境：
MySQL版本：5.5.15
操作系统：windows

1.客户端连接的最大允许数量

优化参数：max_connections
参数作用：同时进行的客户端连接的最大允许数量
优化方法：
在mysql的配置文件my.ini中修改max_connections参数。

[mysqld]

# The maximum amount of concurrent sessions the MySQL server will
# allow. One of these connections will be reserved for a user with
# SUPER privileges to allow the administrator to login even if the
# connection limit has been reached.
max_connections=1000

• 1
• 2
• 3
• 4
• 5
• 6
• 7

注意：
该参数在服务器资源够用的情况下应该尽量设置大，以满足多个客户端同时连接的需求。否则将会出现类似”Too many connections”的错误。

原文：

max_connections

The maximum permitted number of simultaneous client connections. By default, this is 151. See Section B.5.2.7, “Too many connections”, for more information.

Increasing this value increases the number of file descriptors that mysqld requires. See Section 8.4.3.1, “How MySQL Opens and Closes Tables”, for comments on file descriptor limits.

mysqld actually permits max_connections+1 clients to connect. The extra connection is reserved for use by accounts that have the SUPER privilege. By granting the SUPER privilege to administrators and not to normal users (who should not need it), an administrator can connect to the server and use SHOW PROCESSLIST to diagnose problems even if the maximum number of unprivileged clients are connected. See Section 13.7.5.30, “SHOW PROCESSLIST Syntax”.

2.客户端连接错误最大数量

优化参数：max_connect_errors
参数作用：负责阻止过多尝试失败的客户端。max_connect_errors的值与性能并无太大关系。
优化方法：
默认情况下，my.ini文件中可能没有此行。
在mysql的配置文件my.ini中添加max_connect_errors参数。

[mysqld]

max_connect_errors=1000

• 1
• 2
• 3

注意：
如果某个客户端的连接达到了max_connect_errors的限制，将被禁止访问，并提示以下错误：
Host ‘xxx’ is blocked because of many connection errors.Unblock with ‘mysqladmin flush-hosts’
解决方式：
a.非锁定的mysql客户端中执行

flush hosts;

• 1

b..非锁定的mysql客户端bin目录的mysqladmin.exe

mysqladmin flush-hosts -h192.168.2.100 -P3306   -uroot  -p123456

• 1

c.max_connections、max_connect_errors参数调优

---

原文：
max_connect_errors

If more than this many successive connection requests from a host are interrupted without a successful connection, the server blocks that host from further connections. You can unblock blocked hosts by flushing the host cache. To do so, issue a FLUSH HOSTS statement or execute a mysqladmin flush-hosts command. If a connection is established successfully within fewer than max_connect_errors attempts after a previous connection was interrupted, the error count for the host is cleared to zero. However, once a host is blocked, flushing the host cache is the only way to unblock it.

--------------------- 作者：itas109 来源：CSDN 原文：https://blog.csdn.net/itas109/article/details/79045694?utm_source=copy 版权声明：本文为博主原创文章，转载请附上博文链接！