MYSQL max_user_connections back_log max_connections参数和Max_used_connections

原创请注明出处

1、max_user_connections
max_user_connections这个参数是单个用户允许连接的最大会话数量，在建立用户的时候也有类似的限制，这里仅仅说的是这个参数

下面是官方文档说明：
The maximum number of simultaneous connections permitted to any given MySQL user account. A
value of 0 (the default) means “no limit.”
This variable has a global value that can be set at server startup or runtime. It also has a read-only
session value that indicates the effective simultaneous-connection limit that applies to the account
associated with the current session. The session value is initialized as follows:
? If the user account has a nonzero MAX_USER_CONNECTIONS resource limit, the session
max_user_connections value is set to that limit.
? Otherwise, the session max_user_connections value is set to the global value.

报错如下：
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1203 (42000): User root already has more than 'max_user_connections' active connections

2、max_connections
max_connections:这个参数是MYSQL服务端允许的最大连接会话数量，没什么好说的
下面是官方文档说明:
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. If the required
number of descriptors are not available, the server reduces the value of max_connections. See
Section 9.4.3.1, “How MySQL Opens and Closes Tables”, for comments on file descriptor limits.

测试也非常简单报错如下:
[root@testmy ~]#  /mysqldata/mysql5.7/bin/mysql --socket=/mysqldata/mysql5.7/mysqld3307.sock -utestmy -pGelc123123
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1040 (HY000): Too many connections

3、Max_used_connections 、Max_used_connections_time 
这里还大概说一下

mysql> show global status like '%max%';
+-----------------------------------+---------------------+
| Variable_name                     | Value               |
+-----------------------------------+---------------------+
| Max_used_connections              | 7                   |
| Max_used_connections_time         | 2017-05-10 17:10:56 |

这两个状态说的是MYSQL SERVER自上次启动起来最大连接数量和发生的时间，和上面讲参数没任何关系。

? Max_used_connections
The maximum number of connections that have been in use simultaneously since the server started.
? Max_used_connections_time
The time at which Max_used_connections reached its current value. This variable was added in
MySQL 5.7.5

4、back_log
back_log:这个参数的解释相对于比较复杂和难以理解，难以理解是因为大多DBA对LINUX下C编程不熟悉，
这个参数说的就是sokcet编程中的listen调用的时候使用形参back_log
函数原型
int listen(int sockfd, int backlog);          
man page中描述:
DESCRIPTION
       listen() marks the socket referred to by sockfd as a passive socket, that is, as a socket that will be used to accept incoming connection requests using accept(2).
       The sockfd argument is a file descriptor that refers to a socket of type SOCK_STREAM or SOCK_SEQPACKET.
       The  backlog argument defines the maximum length to which the queue of pending connections for sockfd may grow.  If a connection request arrives when the queue is full, the client may receive an error with
       an indication of ECONNREFUSED or, if the underlying protocol supports retransmission, the request may be ignored so that a later reattempt at connection succeeds.
RETURN VALUE
       On success, zero is returned.  On error, -1 is returned, and errno is set appropriately.
sockfd没什么好说的create的时候返回的scoket文件描述符，这里的backlog实际上是一个未决连接的一个队列，如果超过可能值会返回ECONNREFUSED的一个错误，但是如果底层协议支持retransmission,
,这个错误将被忽略然后再次尝试知道成功。它取决于LINUX系统设置/proc/sys/net/core/somaxconn和函数backlog的小值当然LINUX系统默认这个值是128.
下面是MYSQL官方文档描述：
The number of outstanding connection requests MySQL can have. This comes into play when the
main MySQL thread gets very many connection requests in a very short time. It then takes some
time (although very little) for the main thread to check the connection and start a new thread. The
back_log value indicates how many requests can be stacked during this short time before MySQL
momentarily stops answering new requests. You need to increase this only if you expect a large number
of connections in a short period of time.
In other words, this value is the size of the listen queue for incoming TCP/IP connections. Your operating
system has its own limit on the size of this queue. The manual page for the Unix listen() system
call should have more details. Check your OS documentation for the maximum value for this variable.
back_log cannot be set higher than your operating system limit
实际上它说在有大量连接时候，可能出现这样的问题，这里使用了一个short time来描述，也明确告诉你这个
参数和 Unix listen() system调用有关。
MYSQL listen 函数调用栈为