| Command-Line Format | --wait-timeout=# | ||
| System Variable | Name | wait_timeout | |
| Variable Scope | Global, Session | ||
| Dynamic Variable | Yes | ||
| Permitted Values (Windows) | Type | integer | |
| Default | 28800 | ||
| Min Value | 1 | ||
| Max Value | 2147483 | ||
| Permitted Values (Other) | Type | integer | |
| Default | 28800 | ||
| Min Value | 1 | ||
| Max Value | 31536000 | ||
这个参数指的是数据库系统在关闭它之前，服务器等待非交互式连接上的活动的秒数。
interactive_timeout
| Command-Line Format | --interactive-timeout=# | ||
| System Variable | Name | interactive_timeout | |
| Variable Scope | Global, Session | ||
| Dynamic Variable | Yes | ||
| Permitted Values | Type | integer | |
| Default | 28800 | ||
| Min Value | 1 | ||
这个参数指的是在关闭交互式连接之前，服务器等待活动的秒数
Warning:警告这两个参数建议一起调节，能够避免一些坑。
本文的两个参数值采用的是默认值
mysql> show global variables like '%timeout%'; +----------------------------+----------+ | Variable_name | Value | +----------------------------+----------+ | connect_timeout | 10 | | delayed_insert_timeout | 300 | | innodb_lock_wait_timeout | 50 | | innodb_rollback_on_timeout | OFF | |interactive_timeout | 28800 | | lock_wait_timeout | 31536000 | | net_read_timeout | 30 | | net_write_timeout | 60 | | slave_net_timeout | 3600 | |wait_timeout | 28800 | +----------------------------+----------+ 10 rows in set (0.01 sec)
另外在数据库中，我们重点关注下这两个参数，看看什么情况下Aborted_clients会提升，什么情况下Aborted_connects 会提升
mysql>show global status like 'aborted%'; +------------------+-------+ |Variable_name | Value | +------------------+-------+ |Aborted_clients | 19 | |Aborted_connects | 0 | +------------------+-------+ 2 rows inset (0.00 sec)
Part4:案例1
这里我故意输入错误的密码5次，来看下数据库的error log和Aborted的哪个参数记载了这一问题
[root@HE3~]# mysql -uroot -pwrongpass -h127.0.0.1 ERROR 1045 (28000): Access denied for user 'root'@'127.0.0.1' (using password: YES) [root@HE3~]# mysql -uroot -pwrongpass -h127.0.0.1 ERROR 1045 (28000): Access denied for user 'root'@'127.0.0.1' (using password: YES) [root@HE3~]# mysql -uroot -pwrongpass -h127.0.0.1 ERROR 1045 (28000): Access denied for user 'root'@'127.0.0.1' (using password: YES) [root@HE3~]# mysql -uroot -pwrongpass -h127.0.0.1 ERROR 1045 (28000): Access denied for user 'root'@'127.0.0.1' (using password: YES) [root@HE3~]# mysql -uroot -pwrongpass -h127.0.0.1 ERROR 1045 (28000): Access denied for user 'root'@'127.0.0.1' (using password: YES)
可以看出，这里的Aborted_connects 记录了密码错误的这一问题
mysql>show global status like 'aborted%'; +------------------+-------+ |Variable_name | Value | +------------------+-------+ |Aborted_clients | 19 | |Aborted_connects | 5 | +------------------+-------+ 2 rows inset (0.00 sec)
error log中，也记载了这类密码输错的信息
[Warning] Access denied for user'root'@'127.0.0.1' (using password: YES) [Warning] Access denied for user 'root'@'127.0.0.1' (using password:YES) [Warning] Access denied for user 'root'@'127.0.0.1' (using password:YES) [Warning] Access denied for user 'root'@'127.0.0.1' (using password:YES) [Warning] Access denied for user 'root'@'127.0.0.1' (using password:YES)
Part5:案例2
接下来我们看下文章第三节提到的两个重点参数对数据库连接的行为影响
这里我们将这两个参数均配置为10秒
mysql>set global wait_timeout=10; Query OK,0 rows affected (0.00 sec) mysql>set global interactive_timeout=10; Query OK,0 rows affected (0.00 sec) mysql>show processlist; ERROR 2006 (HY000): MySQL server has gone away No connection. Trying to reconnect... Connection id: 79 Current database: *** NONE *** +----+------+-----------------+------+---------+------+-------+------------------+ | Id |User | Host | db | Command | Time | State | Info | +----+------+-----------------+------+---------+------+-------+------------------+ | 79 |root | 127.0.0.1:42016 | NULL | Query | 0 | NULL | show processlist | +----+------+-----------------+------+---------+------+-------+------------------+ 1 row in set (0.00 sec)
这里三次操作，可以看到clients数上升，这是由于timeout参数控制的，已经连接上数据的连接被杀掉。
mysql>show global status like 'aborted%'; ERROR 2006 (HY000): MySQL server has gone away No connection. Trying to reconnect... Connection id: 81 Current database: *** NONE *** +------------------+-------+ |Variable_name | Value | +------------------+-------+ |Aborted_clients | 22 | |Aborted_connects | 5 | +------------------+-------+ 2 rows in set (0.01 sec)
error log中记载的是
[Warning] Aborted connection 81 to db: 'unconnected' user: 'root' host: '127.0.0.1' (Got timeout reading communication packets) [Warning] Aborted connection 78 to db: 'unconnected' user: 'root' host: '127.0.0.1' (Got timeout reading communication packets) [Warning] Aborted connection 79 to db: 'unconnected' user: 'root' host: '127.0.0.1' (Got timeout reading communication packets)
Part6:案例3
在这个案例中我们看下最大连接数对数据库连接的行为影响
mysql>show global variables like 'max_conn%'; +--------------------+-------+ |Variable_name | Value | +--------------------+-------+ |max_connect_errors | 1000 | |max_connections | 1024 | +--------------------+-------+ 2 rows in set (0.00 sec) mysql>set global max_connections=2; Query OK,0 rows affected (0.00 sec)
这里看到爆出了连接数过多的问题
[root@HE3~]# mysql -uroot -pMANAGER -h127.0.0.1 ERROR 1040 (HY000): Too many connections
而错误日志没有任何记录
Part7:案例4
第三方工具navicat select结果没有出来的时候选择停止则出现
clients上涨
mysql>show global status like 'aborted%'; +------------------+-------+ |Variable_name | Value | +------------------+-------+ |Aborted_clients | 28 | |Aborted_connects | 10 | +------------------+-------+ 2 rows in set (0.00 sec)
error log日志记录
170626 16:26:56 [Warning] Aborted connection 109 to db: 'helei1' user: 'sys_admin' host: '192.168.1.1' (Got an error writing communication packets)
Part8:原因总结
总结
通过这4个案例，我们能够了解到，Aborted_clients、和Aborted_connects的区别，以及什么情况下会爆出什么样的错误日志，文章第二节中的几个Aborted错误是常见的错误，这类错误出现的时候脑海里要有一个理论知识，知道什么情况下，会出现什么样的错误，以便快速定位问题。由于笔者的水平有限，编写时间也很仓促，文中难免会出现一些错误或者不准确的地方，不妥之处恳请读者批评指正。
好了，以上就是这篇文章的全部内容了，希望本文的内容对大家的学习或者工作能带来一定的帮助，如果有疑问大家可以留言交流，谢谢大家对脚本之家的支持。
