mysql 超时断开_mysql数据库设置超时断开连接

mysql数据库长时间未操作自动断开连接由参数：interactive_timeout和wait_timeout控制，默认都是8小时(28800分钟)

mysql> show variables like '%timeout%';

+-----------------------------------+----------+

| Variable_name | Value |

+-----------------------------------+----------+

| connect_timeout | 10 |

| delayed_insert_timeout | 300 |

| have_statement_timeout | YES |

| innodb_flush_log_at_timeout | 1 |

| innodb_lock_wait_timeout | 50 |

| innodb_rollback_on_timeout | OFF |

| interactive_timeout | 28800 |

| lock_wait_timeout | 31536000 |

| mysqlx_connect_timeout | 30 |

| mysqlx_idle_worker_thread_timeout | 60 |

| mysqlx_interactive_timeout | 28800 |

| mysqlx_port_open_timeout | 0 |

| mysqlx_read_timeout | 30 |

| mysqlx_wait_timeout | 28800 |

| mysqlx_write_timeout | 60 |

| net_read_timeout | 30 |

| net_write_timeout | 60 |

| rpl_stop_slave_timeout | 31536000 |

| slave_net_timeout | 60 |

| wait_timeout | 28800 |

+-----------------------------------+----------+

20 rows in set (0.02 sec)

修改interactive_timeout和wait_timeout即可设置长时间未操作自动断开连接。

修改interactive_timeout和wait_timeout为15分钟(将参数添加到my.cnf参数文件)。

步骤：

1.停止当前数据库

[root@node01 ~]# /etc/init.d/mysqld stop

Shutting down MySQL... SUCCESS!

2.修改参数文件添加以下参数

interactive_timeout=15

wait_timeout=15

3.重启数据库

[root@node01 ~]# /etc/init.d/mysqld start

Starting MySQL.. SUCCESS!

4.查看修改后的参数

mysql> show variables like '%timeout%';

+-----------------------------------+----------+

| Variable_name | Value |

+-----------------------------------+----------+

| connect_timeout | 10 |

| delayed_insert_timeout | 300 |

| have_statement_timeout | YES |

| innodb_flush_log_at_timeout | 1 |

| innodb_lock_wait_timeout | 50 |

| innodb_rollback_on_timeout | OFF |

| interactive_timeout | 15 |

| lock_wait_timeout | 31536000 |

| mysqlx_connect_timeout | 30 |

| mysqlx_idle_worker_thread_timeout | 60 |

| mysqlx_interactive_timeout | 28800 |

| mysqlx_port_open_timeout | 0 |

| mysqlx_read_timeout | 30 |

| mysqlx_wait_timeout | 28800 |

| mysqlx_write_timeout | 60 |

| net_read_timeout | 30 |

| net_write_timeout | 60 |

| rpl_stop_slave_timeout | 31536000 |

| slave_net_timeout | 60 |

| wait_timeout | 15 |

+-----------------------------------+----------+

20 rows in set (0.00 sec)

5.验证

mysql> select user,host from mysql.user;

ERROR 2013 (HY000): Lost connection to MySQL server during query

未操作时间达到15分钟，连接自动断开。