wait_timeout:mysql服务器关闭非交互连接之前等待活动的秒数。
wait_timeout过大有弊端,MySQL里大量的SLEEP连接无法及时释放,拖累系统性能;
设置过小,可能会遇到“MySQL has gone away”之类的报错,如:
当php出现报错“PHP Error[2]: PDOStatement::execute(): MySQL server has gone away”,其实就是链接超时
Linux系统中,默认是28800秒,可设置最小值1,最大值31536000
mysql> show global variables LIKE '%wait_timeout%';
+--------------------------+----------+
| Variable_name | Value |
+--------------------------+----------+
| innodb_lock_wait_timeout | 50 |
| lock_wait_timeout | 31536000 |
| wait_timeout | 28800 |
+--------------------------+----------+
3 rows in set (0.04 sec)
修改方法:
1.全局:
修改配置文件my.cnf
[mysqld]
wait_timeout=10
或者:
mysql> set global wait_timeout=10;
Query OK, 0 rows affected (0.45 sec)
mysql> show global variables LIKE '%wait_timeout%';
+--------------------------+----------+
| Variable_name | Value |
+--------------------------+----------+
| innodb_lock_wait_timeout | 50 |
| lock_wait_timeout | 31536000 |
| wait_timeout | 10 |
+--------------------------+----------+
3 rows in set (0.14 sec)
2.当前会话:
mysql> set wait_timeout=10;
Query OK, 0 rows affected (0.45 sec)
mysql> show variables LIKE '%wait_timeout%';
+--------------------------+----------+
| Variable_name | Value |
+--------------------------+----------+
| innodb_lock_wait_timeout | 50 |
| lock_wait_timeout | 31536000 |
| wait_timeout | 28800 |
+--------------------------+----------+
3 rows in set (0.00 sec)
记得区分全局变量和当前变量修改的区别:
全局变量的修改对所有的链接都生效,show global variables LIKE ‘%wait_timeout%’;查看修改结果,一定是带上global
对其的变更会影响所有新session(变更时已经存在session不受影响)至下次mysql server重启动。注意它的变更影响不能跨重启,要想再mysql server重启时也使用新的值,那么就只有通过在命令行指定变量选项或者更改选项文件来指定,而通过SET变更是达不到跨重启的。
当前会话只对当前的这个链接生效,其他链接是无法看到效果的,使用show variables LIKE ‘%wait_timeout%’;查看修改结果