版權聲明：聲明：本文檔可以轉載，須署名原作者。 作者：無為 qq:490073687 周祥興 [email protected]
# Connection timeout variables
#interactive_timeout:
#參數含義：伺服器關閉互動式連接配接前等待活動的秒數。互動式用戶端定義為在mysql_real_connect()中使用CLIENT_INTERACTIVE選項的用戶端。
#參數預設值：28800秒（8小時）
#wait_timeout:
#參數含義：伺服器關閉非互動連接配接之前等待活動的秒數。
#線上程啟動時，根據全局wait_timeout值或全局interactive_timeout值初始化會話wait_timeout值，
#取決于用戶端類型(由mysql_real_connect()的連接配接選項CLIENT_INTERACTIVE定義)。
#參數預設值：28800秒（8小時）
繼續深入這兩個變量wait_timeout的取值範圍是1-2147483(Windows)，1-31536000(linux)。
驗證問題如下：
1.修改my.cnf檔案，分别設定wait_timeout和interactive_timeout不同的值，最終看誰覆寫誰？
2.interactive_timeout對wait_timeout的影響。
2.1 session.interactive_timeout<cnf.wait_timeout時，session.interactive_timeout是否會覆寫wait_timeout？
2.2 session.interactive_timeout>cnf.wait_timeout時，session.interactive_timeout是否會覆寫wait_timeout？
2.3 global.interactive_timeout<cnf.wait_timeout時，global.interactive_timeout是否會覆寫wait_timeout？
2.4 global.interactive_timeout>cnf.wait_timeout時，global.interactive_timeout是否會覆寫wait_timeout？
3.wait_timeout對interactive_timeout的影響。
3.1 session.wait_timeout<cnf.interactive_timeout時，session.wait_timeout是否會覆寫interactive_timeout？
3.2 session.wait_timeout>cnf.interactive_timeout時，session.wait_timeout是否會覆寫interactive_timeout？
3.3 global.wait_timeout<cnf.interactive_timeout時，global.wait_timeout是否會覆寫interactive_timeout？
3.4 global.wait_timeout>cnf.interactive_timeout時，global.wait_timeout是否會覆寫interactive_timeout？
4.什麼要同時設定wait_timeout和interactive_timeout？
5.set global wait_timeout=13;是否會影響到已經建立的會話？ 不會
先貼一下小結，後面實驗有點長。
4.什麼要同時設定wait_timeout和interactive_timeout？
對于應用來說，
如果用的是連接配接池，并且沒有F5（F5可能會設定一個逾時，把連接配接時間過長的session kill掉），那麼兩個timeout時間盡量設定長一些，避免應用連接配接池不斷的重新連接配接資料庫。
如果你是短連接配接，那麼就需要把兩個timeout時間設定得短一些，否則資料庫的最大連接配接數是有限的，很多連接配接一直不釋放掉的話，浪費資料庫資源，同時可能會有”MySQL: ERROR 1040: Too many connections”錯誤。
小結：
a.在2.1和2.2兩個試驗中，修改會話級的session.interactive_timeout，對目前會話的逾時時間wait_timeout沒有影響。逾時時間還是global.wait_timeout=cnf.interactive_timeout。
b.在2.3和2.4兩個實驗中，修改全局級的global.interactive_timeout，對目前會話的逾時時間wait_timeout不會馬上有影響。
當目前會話逾時之後，才會生效（相當于新會話了）。對于新建立的會議由影響，而且全局級的interactive_timeout修改會覆寫新會話的wait_timeout。
c.在3.1和3.2兩個實驗中，修改session.wait_timeout，
如果session.wait_timeout<global.interactive_timeout時，起作用的逾時時間是global.interactive_timeout。
如果session.wait_timeout>global.interactive_timeout時，起作用的逾時時間是session.wait_timeout。
d.在3.3和3.4兩個實驗中，修改global.wait_timeout，不管值如何修改，起作用的逾時時間是global.interactive_timeout。
e. 2.3和2.4 ， 3.3和3.4 這4個實驗也間接驗證明驗1中的情況，cnf.interactive_timeout覆寫wait_timeout，起作用的是cnf.interactive_timeout和global.interactive_timeout。
f.從這8個實驗可以看出：對于修改wait_timeout和interactive_timeout(包括session和global)，隻會修改show [global] variables like '%time%';中的參數值，
但是具體逾時時間，并不以這兩個值為準。---有點繞，需要了解。
g. set global wait_timeout和interactive_timeout，修改了目前會話全局參數值，并不修改目前會話級别的值。
隻會對建立會話生效（set global wait_timeout和interactive_timeout之後建立的會話），
對于已經建立的會話不生效（set global wait_timeout和interactive_timeout之前建立的會話）。
1.修改my.cnf檔案，分别設定wait_timeout和interactive_timeout不同的值，最終看誰覆寫誰？
答：interactive_timeout會覆寫wait_timeout。
修改參數檔案，重新開機mysql
# Connection timeout variables
wait_timeout=15
interactive_timeout=10
> show global variables like '%time%';
+---------------------------------+-------------------+
| Variable_name | Value |
+---------------------------------+-------------------+
| binlog_max_flush_queue_time | 0 |
| connect_timeout | 10 |
| datetime_format | %Y-%m-%d %H:%i:%s |
| delayed_insert_timeout | 300 |
| explicit_defaults_for_timestamp | OFF |
| flush_time | 0 |
| innodb_flush_log_at_timeout | 1 |
| innodb_lock_wait_timeout | 50 |
| innodb_old_blocks_time | 1000 |
| innodb_rollback_on_timeout | OFF |
| interactive_timeout | 10 |
| lc_time_names | en_US |
| lock_wait_timeout | 31536000 |
| long_query_time | 0.020000 |
| net_read_timeout | 30 |
| net_write_timeout | 60 |
| rpl_stop_slave_timeout | 31536000 |
| slave_net_timeout | 3600 |
| slow_launch_time | 2 |
| system_time_zone | CST |
| time_format | %H:%i:%s |
| time_zone | SYSTEM |
| timed_mutexes | OFF |
| wait_timeout | 15 |
+---------------------------------+-------------------+
24 rows in set (0.00 sec)
> show variables like '%time%';
+---------------------------------+-------------------+
| Variable_name | Value |
+---------------------------------+-------------------+
| binlog_max_flush_queue_time | 0 |
| connect_timeout | 10 |
| datetime_format | %Y-%m-%d %H:%i:%s |
| delayed_insert_timeout | 300 |
| explicit_defaults_for_timestamp | OFF |
| flush_time | 0 |
| innodb_flush_log_at_timeout | 1 |
| innodb_lock_wait_timeout | 50 |
| innodb_old_blocks_time | 1000 |
| innodb_rollback_on_timeout | OFF |
| interactive_timeout | 10 |
| lc_time_names | en_US |
| lock_wait_timeout | 31536000 |
| long_query_time | 0.020000 |
| net_read_timeout | 30 |
| net_write_timeout | 60 |
| rpl_stop_slave_timeout | 31536000 |
| slave_net_timeout | 3600 |
| slow_launch_time | 2 |
| system_time_zone | CST |
| time_format | %H:%i:%s |
| time_zone | SYSTEM |
| timed_mutexes | OFF |
| timestamp | 1442817811.601474 |
| wait_timeout | 10 |
+---------------------------------+-------------------+
25 rows in set (0.00 sec)
檢視會話逾時時間 --- 會話逾時時間是10s
> show full processlist;
+----+------+-----------+------+---------+------+-------+-----------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+------+---------+------+-------+-----------------------+
| 2 | root | localhost | NULL | Query | 0 | init | show full processlist |
| 3 | ca | localhost | NULL | Sleep | 10 | | NULL |
+----+------+-----------+------+---------+------+-------+-----------------------+
2 rows in set (0.00 sec)
(casystem)[email protected] [(none)]> show full processlist;
+----+------+-----------+------+---------+------+-------+-----------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+------+---------+------+-------+-----------------------+
| 2 | root | localhost | NULL | Query | 0 | init | show full processlist |
+----+------+-----------+------+---------+------+-------+-----------------------+
1 row in set (0.00 sec)
修改參數檔案，重新開機mysql
# Connection timeout variables
wait_timeout=10
interactive_timeout=15
> show global variables like '%time%';
+---------------------------------+-------------------+
| Variable_name | Value |
+---------------------------------+-------------------+
| binlog_max_flush_queue_time | 0 |
| connect_timeout | 10 |
| datetime_format | %Y-%m-%d %H:%i:%s |
| delayed_insert_timeout | 300 |
| explicit_defaults_for_timestamp | OFF |
| flush_time | 0 |
| innodb_flush_log_at_timeout | 1 |
| innodb_lock_wait_timeout | 50 |
| innodb_old_blocks_time | 1000 |
| innodb_rollback_on_timeout | OFF |
| interactive_timeout | 15 |
| lc_time_names | en_US |
| lock_wait_timeout | 31536000 |
| long_query_time | 0.020000 |
| net_read_timeout | 30 |
| net_write_timeout | 60 |
| rpl_stop_slave_timeout | 31536000 |
| slave_net_timeout | 3600 |
| slow_launch_time | 2 |
| system_time_zone | CST |
| time_format | %H:%i:%s |
| time_zone | SYSTEM |
| timed_mutexes | OFF |
| wait_timeout | 10 |
+---------------------------------+-------------------+
24 rows in set (0.00 sec)
(casystem)[email protected] [(none)]> show variables like '%time%';
+---------------------------------+-------------------+
| Variable_name | Value |
+---------------------------------+-------------------+
| binlog_max_flush_queue_time | 0 |
| connect_timeout | 10 |
| datetime_format | %Y-%m-%d %H:%i:%s |
| delayed_insert_timeout | 300 |
| explicit_defaults_for_timestamp | OFF |
| flush_time | 0 |
| innodb_flush_log_at_timeout | 1 |
| innodb_lock_wait_timeout | 50 |
| innodb_old_blocks_time | 1000 |
| innodb_rollback_on_timeout | OFF |
| interactive_timeout | 15 |
| lc_time_names | en_US |
| lock_wait_timeout | 31536000 |
| long_query_time | 0.020000 |
| net_read_timeout | 30 |
| net_write_timeout | 60 |
| rpl_stop_slave_timeout | 31536000 |
| slave_net_timeout | 3600 |
| slow_launch_time | 2 |
| system_time_zone | CST |
| time_format | %H:%i:%s |
| time_zone | SYSTEM |
| timed_mutexes | OFF |
| timestamp | 1442817650.922342 |
| wait_timeout | 15 |
+---------------------------------+-------------------+
25 rows in set (0.01 sec)
檢視會話逾時時間 --會話逾時時間是15秒
> show full processlist;
+----+------+-----------+------+---------+------+-------+-----------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+------+---------+------+-------+-----------------------+
| 2 | root | localhost | NULL | Query | 0 | init | show full processlist |
| 4 | ca | localhost | NULL | Sleep | 15 | | NULL |
+----+------+-----------+------+---------+------+-------+-----------------------+
2 rows in set (0.00 sec)
(casystem)[email protected] [(none)]> show full processlist;
+----+------+-----------+------+---------+------+-------+-----------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+------+---------+------+-------+-----------------------+
| 2 | root | localhost | NULL | Query | 0 | init | show full processlist |
+----+------+-----------+------+---------+------+-------+-----------------------+
1 row in set (0.00 sec)
2.interactive_timeout對wait_timeout的影響。--下面測試的參數檔案是
# Connection timeout variables
wait_timeout=15
interactive_timeout=10
> show variables like '%time%';
+---------------------------------+-------------------+
| Variable_name | Value |
+---------------------------------+-------------------+
| binlog_max_flush_queue_time | 0 |
| connect_timeout | 10 |
| datetime_format | %Y-%m-%d %H:%i:%s |
| delayed_insert_timeout | 300 |
| explicit_defaults_for_timestamp | OFF |
| flush_time | 0 |
| innodb_flush_log_at_timeout | 1 |
| innodb_lock_wait_timeout | 50 |
| innodb_old_blocks_time | 1000 |
| innodb_rollback_on_timeout | OFF |
| interactive_timeout | 10 |
| lc_time_names | en_US |
| lock_wait_timeout | 31536000 |
| long_query_time | 0.020000 |
| net_read_timeout | 30 |
| net_write_timeout | 60 |
| rpl_stop_slave_timeout | 31536000 |
| slave_net_timeout | 3600 |
| slow_launch_time | 2 |
| system_time_zone | CST |
| time_format | %H:%i:%s |
| time_zone | SYSTEM |
| timed_mutexes | OFF |
| timestamp | 1442818199.946527 |
| wait_timeout | 10 |
+---------------------------------+-------------------+
25 rows in set (0.01 sec)
> show global variables like '%time%';
+---------------------------------+-------------------+
| Variable_name | Value |
+---------------------------------+-------------------+
| binlog_max_flush_queue_time | 0 |
| connect_timeout | 10 |
| datetime_format | %Y-%m-%d %H:%i:%s |
| delayed_insert_timeout | 300 |
| explicit_defaults_for_timestamp | OFF |
| flush_time | 0 |
| innodb_flush_log_at_timeout | 1 |
| innodb_lock_wait_timeout | 50 |
| innodb_old_blocks_time | 1000 |
| innodb_rollback_on_timeout | OFF |
| interactive_timeout | 10 |
| lc_time_names | en_US |
| lock_wait_timeout | 31536000 |
| long_query_time | 0.020000 |
| net_read_timeout | 30 |
| net_write_timeout | 60 |
| rpl_stop_slave_timeout | 31536000 |
| slave_net_timeout | 3600 |
| slow_launch_time | 2 |
| system_time_zone | CST |
| time_format | %H:%i:%s |
| time_zone | SYSTEM |
| timed_mutexes | OFF |
| wait_timeout | 15 |
+---------------------------------+-------------------+
24 rows in set (0.00 sec)
2.1 session.interactive_timeout<cnf.interactive_timeout 時，session.interactive_timeout是否會覆寫wait_timeout？
set interactive_timeout=8; （8<10）
> set interactive_timeout=8;
Query OK, 0 rows affected (0.00 sec)
(casystem)[email protected] [(none)]> show global variables like '%time%';
+---------------------------------+-------------------+
| Variable_name | Value |
+---------------------------------+-------------------+
| binlog_max_flush_queue_time | 0 |
| connect_timeout | 10 |
| datetime_format | %Y-%m-%d %H:%i:%s |
| delayed_insert_timeout | 300 |
| explicit_defaults_for_timestamp | OFF |
| flush_time | 0 |
| innodb_flush_log_at_timeout | 1 |
| innodb_lock_wait_timeout | 50 |
| innodb_old_blocks_time | 1000 |
| innodb_rollback_on_timeout | OFF |
| interactive_timeout | 10 |
| lc_time_names | en_US |
| lock_wait_timeout | 31536000 |
| long_query_time | 0.020000 |
| net_read_timeout | 30 |
| net_write_timeout | 60 |
| rpl_stop_slave_timeout | 31536000 |
| slave_net_timeout | 3600 |
| slow_launch_time | 2 |
| system_time_zone | CST |
| time_format | %H:%i:%s |
| time_zone | SYSTEM |
| timed_mutexes | OFF |
| wait_timeout | 15 |
+---------------------------------+-------------------+
24 rows in set (0.00 sec)
(casystem)[email protected] [(none)]> show variables like '%time%';
+---------------------------------+-------------------+
| Variable_name | Value |
+---------------------------------+-------------------+
| binlog_max_flush_queue_time | 0 |
| connect_timeout | 10 |
| datetime_format | %Y-%m-%d %H:%i:%s |
| delayed_insert_timeout | 300 |
| explicit_defaults_for_timestamp | OFF |
| flush_time | 0 |
| innodb_flush_log_at_timeout | 1 |
| innodb_lock_wait_timeout | 50 |
| innodb_old_blocks_time | 1000 |
| innodb_rollback_on_timeout | OFF |
| interactive_timeout | 8 |
| lc_time_names | en_US |
| lock_wait_timeout | 31536000 |
| long_query_time | 0.020000 |
| net_read_timeout | 30 |
| net_write_timeout | 60 |
| rpl_stop_slave_timeout | 31536000 |
| slave_net_timeout | 3600 |
| slow_launch_time | 2 |
| system_time_zone | CST |
| time_format | %H:%i:%s |
| time_zone | SYSTEM |
| timed_mutexes | OFF |
| timestamp | 1442818460.657640 |
| wait_timeout | 10 |
+---------------------------------+-------------------+
25 rows in set (0.00 sec)
目前會話逾時時間----------- 逾時時間是10s（不是8s），修改會話級的interactive_timeout沒有覆寫wait_timeout。
(casystem)[email protected] [(none)]> show full processlist;
+----+------+-----------+------+---------+------+-------+-----------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+------+---------+------+-------+-----------------------+
| 5 | ca | localhost | NULL | Sleep | 10 | | NULL |
| 6 | root | localhost | NULL | Query | 0 | init | show full processlist |
+----+------+-----------+------+---------+------+-------+-----------------------+
2 rows in set (0.00 sec)
(casystem)[email protected] [(none)]> show full processlist;
+----+------+-----------+------+---------+------+-------+-----------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+------+---------+------+-------+-----------------------+
| 6 | root | localhost | NULL | Query | 0 | init | show full processlist |
+----+------+-----------+------+---------+------+-------+-----------------------+
1 row in set (0.00 sec)
2.2 session.interactive_timeout>cnf.interactive_timeout 時，session.interactive_timeout是否會覆寫wait_timeout？
> set interactive_timeout=12;
Query OK, 0 rows affected (0.00 sec)
> show variables like '%time%';
+---------------------------------+-------------------+
| Variable_name | Value |
+---------------------------------+-------------------+
| binlog_max_flush_queue_time | 0 |
| connect_timeout | 10 |
| datetime_format | %Y-%m-%d %H:%i:%s |
| delayed_insert_timeout | 300 |
| explicit_defaults_for_timestamp | OFF |
| flush_time | 0 |
| innodb_flush_log_at_timeout | 1 |
| innodb_lock_wait_timeout | 50 |
| innodb_old_blocks_time | 1000 |
| innodb_rollback_on_timeout | OFF |
| interactive_timeout | 12 |
| lc_time_names | en_US |
| lock_wait_timeout | 31536000 |
| long_query_time | 0.020000 |
| net_read_timeout | 30 |
| net_write_timeout | 60 |
| rpl_stop_slave_timeout | 31536000 |
| slave_net_timeout | 3600 |
| slow_launch_time | 2 |
| system_time_zone | CST |
| time_format | %H:%i:%s |
| time_zone | SYSTEM |
| timed_mutexes | OFF |
| timestamp | 1442818758.020616 |
| wait_timeout | 10 |
+---------------------------------+-------------------+
25 rows in set (0.01 sec)
> show global variables like '%time%';
+---------------------------------+-------------------+
| Variable_name | Value |
+---------------------------------+-------------------+
| binlog_max_flush_queue_time | 0 |
| connect_timeout | 10 |
| datetime_format | %Y-%m-%d %H:%i:%s |
| delayed_insert_timeout | 300 |
| explicit_defaults_for_timestamp | OFF |
| flush_time | 0 |
| innodb_flush_log_at_timeout | 1 |
| innodb_lock_wait_timeout | 50 |
| innodb_old_blocks_time | 1000 |
| innodb_rollback_on_timeout | OFF |
| interactive_timeout | 10 |
| lc_time_names | en_US |
| lock_wait_timeout | 31536000 |
| long_query_time | 0.020000 |
| net_read_timeout | 30 |
| net_write_timeout | 60 |
| rpl_stop_slave_timeout | 31536000 |
| slave_net_timeout | 3600 |
| slow_launch_time | 2 |
| system_time_zone | CST |
| time_format | %H:%i:%s |
| time_zone | SYSTEM |
| timed_mutexes | OFF |
| wait_timeout | 15 |
+---------------------------------+-------------------+
24 rows in set (0.00 sec)
目前會話逾時時間----------- 逾時時間是10s（不是12s），修改會話級的interactive_timeout沒有覆寫wait_timeout。
(casystem)[email protected] [(none)]> show full processlist;
+----+------+-----------+------+---------+------+-------+-----------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+------+---------+------+-------+-----------------------+
| 10 | ca | localhost | NULL | Sleep | 10 | | NULL |
| 11 | root | localhost | NULL | Query | 0 | init | show full processlist |
+----+------+-----------+------+---------+------+-------+-----------------------+
2 rows in set (0.00 sec)
(casystem)[email protected] [(none)]> show full processlist;
+----+------+-----------+------+---------+------+-------+-----------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+------+---------+------+-------+-----------------------+
| 11 | root | localhost | NULL | Query | 0 | init | show full processlist |
+----+------+-----------+------+---------+------+-------+-----------------------+
1 row in set (0.01 sec)
2.3 global.interactive_timeout<cnf.interactive_timeout 時，global.interactive_timeout是否會覆寫wait_timeout？
set global interactive_timeout=8;
目前會話 --- 目前會話的逾時時間還是10秒，并未馬上修改成8秒。等目前會話逾時之後，才重新修改成了8秒。
> set global interactive_timeout=8;
Query OK, 0 rows affected (0.00 sec)
(casystem)[email protected] [(none)]> show global variables like '%time%';
+---------------------------------+-------------------+
| Variable_name | Value |
+---------------------------------+-------------------+
| binlog_max_flush_queue_time | 0 |
| connect_timeout | 10 |
| datetime_format | %Y-%m-%d %H:%i:%s |
| delayed_insert_timeout | 300 |
| explicit_defaults_for_timestamp | OFF |
| flush_time | 0 |
| innodb_flush_log_at_timeout | 1 |
| innodb_lock_wait_timeout | 50 |
| innodb_old_blocks_time | 1000 |
| innodb_rollback_on_timeout | OFF |
| interactive_timeout | 8 |
| lc_time_names | en_US |
| lock_wait_timeout | 31536000 |
| long_query_time | 0.020000 |
| net_read_timeout | 30 |
| net_write_timeout | 60 |
| rpl_stop_slave_timeout | 31536000 |
| slave_net_timeout | 3600 |
| slow_launch_time | 2 |
| system_time_zone | CST |
| time_format | %H:%i:%s |
| time_zone | SYSTEM |
| timed_mutexes | OFF |
| wait_timeout | 15 |
+---------------------------------+-------------------+
24 rows in set (0.00 sec)
(casystem)[email protected] [(none)]> show variables like '%time%';
+---------------------------------+-------------------+
| Variable_name | Value |
+---------------------------------+-------------------+
| binlog_max_flush_queue_time | 0 |
| connect_timeout | 10 |
| datetime_format | %Y-%m-%d %H:%i:%s |
| delayed_insert_timeout | 300 |
| explicit_defaults_for_timestamp | OFF |
| flush_time | 0 |
| innodb_flush_log_at_timeout | 1 |
| innodb_lock_wait_timeout | 50 |
| innodb_old_blocks_time | 1000 |
| innodb_rollback_on_timeout | OFF |
| interactive_timeout | 10 |
| lc_time_names | en_US |
| lock_wait_timeout | 31536000 |
| long_query_time | 0.020000 |
| net_read_timeout | 30 |
| net_write_timeout | 60 |
| rpl_stop_slave_timeout | 31536000 |
| slave_net_timeout | 3600 |
| slow_launch_time | 2 |
| system_time_zone | CST |
| time_format | %H:%i:%s |
| time_zone | SYSTEM |
| timed_mutexes | OFF |
| timestamp | 1442819026.134650 |
| wait_timeout | 10 |
+---------------------------------+-------------------+
25 rows in set (0.00 sec)
> show variables like '%time%';
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 22
Current database: *** NONE ***
+---------------------------------+-------------------+
| Variable_name | Value |
+---------------------------------+-------------------+
| binlog_max_flush_queue_time | 0 |
| connect_timeout | 10 |
| datetime_format | %Y-%m-%d %H:%i:%s |
| delayed_insert_timeout | 300 |
| explicit_defaults_for_timestamp | OFF |
| flush_time | 0 |
| innodb_flush_log_at_timeout | 1 |
| innodb_lock_wait_timeout | 50 |
| innodb_old_blocks_time | 1000 |
| innodb_rollback_on_timeout | OFF |
| interactive_timeout | 8 |
| lc_time_names | en_US |
| lock_wait_timeout | 31536000 |
| long_query_time | 0.020000 |
| net_read_timeout | 30 |
| net_write_timeout | 60 |
| rpl_stop_slave_timeout | 31536000 |
| slave_net_timeout | 3600 |
| slow_launch_time | 2 |
| system_time_zone | CST |
| time_format | %H:%i:%s |
| time_zone | SYSTEM |
| timed_mutexes | OFF |
| timestamp | 1442819970.559386 |
| wait_timeout | 8 |
+---------------------------------+-------------------+
25 rows in set (0.00 sec)
建立的會話---新的會話修改成了8秒，而且新會話的interactive_timeout和wait_timeout都是8秒，全局的interactive_timeout=8秒，全局的wait_timeout=15保持不變。
> show global variables like '%time%';
+---------------------------------+-------------------+
| Variable_name | Value |
+---------------------------------+-------------------+
| binlog_max_flush_queue_time | 0 |
| connect_timeout | 10 |
| datetime_format | %Y-%m-%d %H:%i:%s |
| delayed_insert_timeout | 300 |
| explicit_defaults_for_timestamp | OFF |
| flush_time | 0 |
| innodb_flush_log_at_timeout | 1 |
| innodb_lock_wait_timeout | 50 |
| innodb_old_blocks_time | 1000 |
| innodb_rollback_on_timeout | OFF |
| interactive_timeout | 8 |
| lc_time_names | en_US |
| lock_wait_timeout | 31536000 |
| long_query_time | 0.020000 |
| net_read_timeout | 30 |
| net_write_timeout | 60 |
| rpl_stop_slave_timeout | 31536000 |
| slave_net_timeout | 3600 |
| slow_launch_time | 2 |
| system_time_zone | CST |
| time_format | %H:%i:%s |
| time_zone | SYSTEM |
| timed_mutexes | OFF |
| wait_timeout | 15 |
+---------------------------------+-------------------+
24 rows in set (0.00 sec)
(casystem)[email protected] [(none)]> show variables like '%time%';
+---------------------------------+-------------------+
| Variable_name | Value |
+---------------------------------+-------------------+
| binlog_max_flush_queue_time | 0 |
| connect_timeout | 10 |
| datetime_format | %Y-%m-%d %H:%i:%s |
| delayed_insert_timeout | 300 |
| explicit_defaults_for_timestamp | OFF |
| flush_time | 0 |
| innodb_flush_log_at_timeout | 1 |
| innodb_lock_wait_timeout | 50 |
| innodb_old_blocks_time | 1000 |
| innodb_rollback_on_timeout | OFF |
| interactive_timeout | 8 |
| lc_time_names | en_US |
| lock_wait_timeout | 31536000 |
| long_query_time | 0.020000 |
| net_read_timeout | 30 |
| net_write_timeout | 60 |
| rpl_stop_slave_timeout | 31536000 |
| slave_net_timeout | 3600 |
| slow_launch_time | 2 |
| system_time_zone | CST |
| time_format | %H:%i:%s |
| time_zone | SYSTEM |
| timed_mutexes | OFF |
| timestamp | 1442819080.061477 |
| wait_timeout | 8 |
+---------------------------------+-------------------+
25 rows in set (0.00 sec)
2.4 global.interactive_timeout>cnf.interactive_timeout 時，global.interactive_timeout是否會覆寫wait_timeout？
目前會話 --- 目前會話的逾時時間并未馬上修改成12秒。等目前會話逾時之後，才重新修改成了12秒。
> set global interactive_timeout=12;
Query OK, 0 rows affected (0.00 sec)
(casystem)[email protected] [(none)]> show global variables like '%time%';
+---------------------------------+-------------------+
| Variable_name | Value |
+---------------------------------+-------------------+
| binlog_max_flush_queue_time | 0 |
| connect_timeout | 10 |
| datetime_format | %Y-%m-%d %H:%i:%s |
| delayed_insert_timeout | 300 |
| explicit_defaults_for_timestamp | OFF |
| flush_time | 0 |
| innodb_flush_log_at_timeout | 1 |
| innodb_lock_wait_timeout | 50 |
| innodb_old_blocks_time | 1000 |
| innodb_rollback_on_timeout | OFF |
| interactive_timeout | 12 |
| lc_time_names | en_US |
| lock_wait_timeout | 31536000 |
| long_query_time | 0.020000 |
| net_read_timeout | 30 |
| net_write_timeout | 60 |
| rpl_stop_slave_timeout | 31536000 |
| slave_net_timeout | 3600 |
| slow_launch_time | 2 |
| system_time_zone | CST |
| time_format | %H:%i:%s |
| time_zone | SYSTEM |
| timed_mutexes | OFF |
| wait_timeout | 15 |
+---------------------------------+-------------------+
24 rows in set (0.00 sec)
> show variables like '%time%'; --目前會話沒有立馬生效，等了“一會兒”
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 17
Current database: *** NONE ***
+---------------------------------+-------------------+
| Variable_name | Value |
+---------------------------------+-------------------+
| binlog_max_flush_queue_time | 0 |
| connect_timeout | 10 |
| datetime_format | %Y-%m-%d %H:%i:%s |
| delayed_insert_timeout | 300 |
| explicit_defaults_for_timestamp | OFF |
| flush_time | 0 |
| innodb_flush_log_at_timeout | 1 |
| innodb_lock_wait_timeout | 50 |
| innodb_old_blocks_time | 1000 |
| innodb_rollback_on_timeout | OFF |
| interactive_timeout | 12 |
| lc_time_names | en_US |
| lock_wait_timeout | 31536000 |
| long_query_time | 0.020000 |
| net_read_timeout | 30 |
| net_write_timeout | 60 |
| rpl_stop_slave_timeout | 31536000 |
| slave_net_timeout | 3600 |
| slow_launch_time | 2 |
| system_time_zone | CST |
| time_format | %H:%i:%s |
| time_zone | SYSTEM |
| timed_mutexes | OFF |
| timestamp | 1442819701.923548 |
| wait_timeout | 12 |
+---------------------------------+-------------------+
25 rows in set (0.01 sec)
(casystem)[email protected] [(none)]> show global variables like '%time%';
+---------------------------------+-------------------+
| Variable_name | Value |
+---------------------------------+-------------------+
| binlog_max_flush_queue_time | 0 |
| connect_timeout | 10 |
| datetime_format | %Y-%m-%d %H:%i:%s |
| delayed_insert_timeout | 300 |
| explicit_defaults_for_timestamp | OFF |
| flush_time | 0 |
| innodb_flush_log_at_timeout | 1 |
| innodb_lock_wait_timeout | 50 |
| innodb_old_blocks_time | 1000 |
| innodb_rollback_on_timeout | OFF |
| interactive_timeout | 12 |
| lc_time_names | en_US |
| lock_wait_timeout | 31536000 |
| long_query_time | 0.020000 |
| net_read_timeout | 30 |
| net_write_timeout | 60 |
| rpl_stop_slave_timeout | 31536000 |
| slave_net_timeout | 3600 |
| slow_launch_time | 2 |
| system_time_zone | CST |
| time_format | %H:%i:%s |
| time_zone | SYSTEM |
| timed_mutexes | OFF |
| wait_timeout | 15 |
+---------------------------------+-------------------+
24 rows in set (0.01 sec)
目前會話的逾時時間
> show full processlist;
+----+------+-----------+------+---------+------+-------+-----------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+------+---------+------+-------+-----------------------+
| 18 | ca | localhost | NULL | Sleep | 12 | | NULL |
| 19 | root | localhost | NULL | Query | 0 | init | show full processlist |
+----+------+-----------+------+---------+------+-------+-----------------------+
2 rows in set (0.00 sec)
(casystem)[email protected] [(none)]> show full processlist;
+----+------+-----------+------+---------+------+-------+-----------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+------+---------+------+-------+-----------------------+
| 19 | root | localhost | NULL | Query | 0 | init | show full processlist |
+----+------+-----------+------+---------+------+-------+-----------------------+
1 row in set (0.00 sec)
新會話-------
> show variables like '%time%';
+---------------------------------+-------------------+
| Variable_name | Value |
+---------------------------------+-------------------+
| binlog_max_flush_queue_time | 0 |
| connect_timeout | 10 |
| datetime_format | %Y-%m-%d %H:%i:%s |
| delayed_insert_timeout | 300 |
| explicit_defaults_for_timestamp | OFF |
| flush_time | 0 |
| innodb_flush_log_at_timeout | 1 |
| innodb_lock_wait_timeout | 50 |
| innodb_old_blocks_time | 1000 |
| innodb_rollback_on_timeout | OFF |
| interactive_timeout | 12 |
| lc_time_names | en_US |
| lock_wait_timeout | 31536000 |
| long_query_time | 0.020000 |
| net_read_timeout | 30 |
| net_write_timeout | 60 |
| rpl_stop_slave_timeout | 31536000 |
| slave_net_timeout | 3600 |
| slow_launch_time | 2 |
| system_time_zone | CST |
| time_format | %H:%i:%s |
| time_zone | SYSTEM |
| timed_mutexes | OFF |
| timestamp | 1442819629.180842 |
| wait_timeout | 12 |
+---------------------------------+-------------------+
25 rows in set (0.00 sec)
(casystem)[email protected] [(none)]> show global variables like '%time%';
+---------------------------------+-------------------+
| Variable_name | Value |
+---------------------------------+-------------------+
| binlog_max_flush_queue_time | 0 |
| connect_timeout | 10 |
| datetime_format | %Y-%m-%d %H:%i:%s |
| delayed_insert_timeout | 300 |
| explicit_defaults_for_timestamp | OFF |
| flush_time | 0 |
| innodb_flush_log_at_timeout | 1 |
| innodb_lock_wait_timeout | 50 |
| innodb_old_blocks_time | 1000 |
| innodb_rollback_on_timeout | OFF |
| interactive_timeout | 12 |
| lc_time_names | en_US |
| lock_wait_timeout | 31536000 |
| long_query_time | 0.020000 |
| net_read_timeout | 30 |
| net_write_timeout | 60 |
| rpl_stop_slave_timeout | 31536000 |
| slave_net_timeout | 3600 |
| slow_launch_time | 2 |
| system_time_zone | CST |
| time_format | %H:%i:%s |
| time_zone | SYSTEM |
| timed_mutexes | OFF |
| wait_timeout | 15 |
+---------------------------------+-------------------+
24 rows in set (0.00 sec)
新會話的逾時時間，不測了。
3.wait_timeout對interactive_timeout的影響。--下面測試的參數檔案是
# Connection timeout variables
wait_timeout=15
interactive_timeout=10
> show variables like '%time%';
+---------------------------------+-------------------+
| Variable_name | Value |
+---------------------------------+-------------------+
| binlog_max_flush_queue_time | 0 |
| connect_timeout | 10 |
| datetime_format | %Y-%m-%d %H:%i:%s |
| delayed_insert_timeout | 300 |
| explicit_defaults_for_timestamp | OFF |
| flush_time | 0 |
| innodb_flush_log_at_timeout | 1 |
| innodb_lock_wait_timeout | 50 |
| innodb_old_blocks_time | 1000 |
| innodb_rollback_on_timeout | OFF |
| interactive_timeout | 10 |
| lc_time_names | en_US |
| lock_wait_timeout | 31536000 |
| long_query_time | 0.020000 |
| net_read_timeout | 30 |
| net_write_timeout | 60 |
| rpl_stop_slave_timeout | 31536000 |
| slave_net_timeout | 3600 |
| slow_launch_time | 2 |
| system_time_zone | CST |
| time_format | %H:%i:%s |
| time_zone | SYSTEM |
| timed_mutexes | OFF |
| timestamp | 1442818199.946527 |
| wait_timeout | 10 |
+---------------------------------+-------------------+
25 rows in set (0.01 sec)
> show global variables like '%time%';
+---------------------------------+-------------------+
| Variable_name | Value |
+---------------------------------+-------------------+
| binlog_max_flush_queue_time | 0 |
| connect_timeout | 10 |
| datetime_format | %Y-%m-%d %H:%i:%s |
| delayed_insert_timeout | 300 |
| explicit_defaults_for_timestamp | OFF |
| flush_time | 0 |
| innodb_flush_log_at_timeout | 1 |
| innodb_lock_wait_timeout | 50 |
| innodb_old_blocks_time | 1000 |
| innodb_rollback_on_timeout | OFF |
| interactive_timeout | 10 |
| lc_time_names | en_US |
| lock_wait_timeout | 31536000 |
| long_query_time | 0.020000 |
| net_read_timeout | 30 |
| net_write_timeout | 60 |
| rpl_stop_slave_timeout | 31536000 |
| slave_net_timeout | 3600 |
| slow_launch_time | 2 |
| system_time_zone | CST |
| time_format | %H:%i:%s |
| time_zone | SYSTEM |
| timed_mutexes | OFF |
| wait_timeout | 15 |
+---------------------------------+-------------------+
24 rows in set (0.00 sec)
3.1 session.wait_timeout<cnf.interactive_timeout時，session.wait_timeout是否會覆寫interactive_timeout？
set wait_timeout=8;
> set wait_timeout=8;
Query OK, 0 rows affected (0.00 sec)
(casystem)[email protected] [(none)]> show variables like '%time%';
+---------------------------------+-------------------+
| Variable_name | Value |
+---------------------------------+-------------------+
| binlog_max_flush_queue_time | 0 |
| connect_timeout | 10 |
| datetime_format | %Y-%m-%d %H:%i:%s |
| delayed_insert_timeout | 300 |
| explicit_defaults_for_timestamp | OFF |
| flush_time | 0 |
| innodb_flush_log_at_timeout | 1 |
| innodb_lock_wait_timeout | 50 |
| innodb_old_blocks_time | 1000 |
| innodb_rollback_on_timeout | OFF |
| interactive_timeout | 10 |
| lc_time_names | en_US |
| lock_wait_timeout | 31536000 |
| long_query_time | 0.020000 |
| net_read_timeout | 30 |
| net_write_timeout | 60 |
| rpl_stop_slave_timeout | 31536000 |
| slave_net_timeout | 3600 |
| slow_launch_time | 2 |
| system_time_zone | CST |
| time_format | %H:%i:%s |
| time_zone | SYSTEM |
| timed_mutexes | OFF |
| timestamp | 1442820543.624578 |
| wait_timeout | 8 |
+---------------------------------+-------------------+
25 rows in set (0.00 sec)
(casystem)[email protected] [(none)]> show global variables like '%time%';
+---------------------------------+-------------------+
| Variable_name | Value |
+---------------------------------+-------------------+
| binlog_max_flush_queue_time | 0 |
| connect_timeout | 10 |
| datetime_format | %Y-%m-%d %H:%i:%s |
| delayed_insert_timeout | 300 |
| explicit_defaults_for_timestamp | OFF |
| flush_time | 0 |
| innodb_flush_log_at_timeout | 1 |
| innodb_lock_wait_timeout | 50 |
| innodb_old_blocks_time | 1000 |
| innodb_rollback_on_timeout | OFF |
| interactive_timeout | 10 |
| lc_time_names | en_US |
| lock_wait_timeout | 31536000 |
| long_query_time | 0.020000 |
| net_read_timeout | 30 |
| net_write_timeout | 60 |
| rpl_stop_slave_timeout | 31536000 |
| slave_net_timeout | 3600 |
| slow_launch_time | 2 |
| system_time_zone | CST |
| time_format | %H:%i:%s |
| time_zone | SYSTEM |
| timed_mutexes | OFF |
| wait_timeout | 15 |
+---------------------------------+-------------------+
24 rows in set (0.00 sec)
目前會話逾時時間 -- 逾時時間是interactive_timeout=10秒，不是8秒，修改目前會話的wait_timeout，沒有覆寫interactive_timeout，起作用的是interactive_timeout
> show full processlist;
+----+------+-----------+------+---------+------+-------+-----------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+------+---------+------+-------+-----------------------+
| 6 | ca | localhost | NULL | Sleep | 10 | | NULL |
| 7 | root | localhost | NULL | Query | 0 | init | show full processlist |
+----+------+-----------+------+---------+------+-------+-----------------------+
2 rows in set (0.00 sec)
(casystem)[email protected] [(none)]> show full processlist;
+----+------+-----------+------+---------+------+-------+-----------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+------+---------+------+-------+-----------------------+
| 7 | root | localhost | NULL | Query | 0 | init | show full processlist |
+----+------+-----------+------+---------+------+-------+-----------------------+
1 row in set (0.01 sec)
3.2 session.wait_timeout>cnf.interactive_timeout時，session.wait_timeout是否會覆寫interactive_timeout？
set wait_timeout=12;
> set wait_timeout=12;
Query OK, 0 rows affected (0.00 sec)
(casystem)[email protected] [(none)]> show variables like '%time%';
+---------------------------------+-------------------+
| Variable_name | Value |
+---------------------------------+-------------------+
| binlog_max_flush_queue_time | 0 |
| connect_timeout | 10 |
| datetime_format | %Y-%m-%d %H:%i:%s |
| delayed_insert_timeout | 300 |
| explicit_defaults_for_timestamp | OFF |
| flush_time | 0 |
| innodb_flush_log_at_timeout | 1 |
| innodb_lock_wait_timeout | 50 |
| innodb_old_blocks_time | 1000 |
| innodb_rollback_on_timeout | OFF |
| interactive_timeout | 10 |
| lc_time_names | en_US |
| lock_wait_timeout | 31536000 |
| long_query_time | 0.020000 |
| net_read_timeout | 30 |
| net_write_timeout | 60 |
| rpl_stop_slave_timeout | 31536000 |
| slave_net_timeout | 3600 |
| slow_launch_time | 2 |
| system_time_zone | CST |
| time_format | %H:%i:%s |
| time_zone | SYSTEM |
| timed_mutexes | OFF |
| timestamp | 1442820744.921596 |
| wait_timeout | 12 |
+---------------------------------+-------------------+
25 rows in set (0.00 sec)
(casystem)[email protected] [(none)]> show global variables like '%time%';
+---------------------------------+-------------------+
| Variable_name | Value |
+---------------------------------+-------------------+
| binlog_max_flush_queue_time | 0 |
| connect_timeout | 10 |
| datetime_format | %Y-%m-%d %H:%i:%s |
| delayed_insert_timeout | 300 |
| explicit_defaults_for_timestamp | OFF |
| flush_time | 0 |
| innodb_flush_log_at_timeout | 1 |
| innodb_lock_wait_timeout | 50 |
| innodb_old_blocks_time | 1000 |
| innodb_rollback_on_timeout | OFF |
| interactive_timeout | 10 |
| lc_time_names | en_US |
| lock_wait_timeout | 31536000 |
| long_query_time | 0.020000 |
| net_read_timeout | 30 |
| net_write_timeout | 60 |
| rpl_stop_slave_timeout | 31536000 |
| slave_net_timeout | 3600 |
| slow_launch_time | 2 |
| system_time_zone | CST |
| time_format | %H:%i:%s |
| time_zone | SYSTEM |
| timed_mutexes | OFF |
| wait_timeout | 15 |
+---------------------------------+-------------------+
24 rows in set (0.00 sec)
目前會話逾時時間---逾時時間是wait_timeout=12秒，不是10秒，修改目前會話的wait_timeout，沒有覆寫interactive_timeout，但是起作用的是wait_timeout
> show full processlist;
+----+------+-----------+------+---------+------+-------+-----------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+------+---------+------+-------+-----------------------+
| 8 | ca | localhost | NULL | Sleep | 12 | | NULL |
| 9 | root | localhost | NULL | Query | 0 | init | show full processlist |
+----+------+-----------+------+---------+------+-------+-----------------------+
2 rows in set (0.00 sec)
(casystem)[email protected] [(none)]> show full processlist;
+----+------+-----------+------+---------+------+-------+-----------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+------+---------+------+-------+-----------------------+
| 9 | root | localhost | NULL | Query | 0 | init | show full processlist |
+----+------+-----------+------+---------+------+-------+-----------------------+
1 row in set (0.00 sec)
3.3 global.wait_timeout<cnf.interactive_timeout時，global.wait_timeout是否會覆寫interactive_timeout？
set global wait_timeout=8;
目前會話---目前會話的逾時時間是10，并未修改成8，目前會話逾時之後，也沒有修改成8.（原因，實驗1，以interactive_timeout為準）
> set global wait_timeout=8;
Query OK, 0 rows affected (0.00 sec)
(casystem)[email protected] [(none)]> show variables like '%time%';
+---------------------------------+-------------------+
| Variable_name | Value |
+---------------------------------+-------------------+
| binlog_max_flush_queue_time | 0 |
| connect_timeout | 10 |
| datetime_format | %Y-%m-%d %H:%i:%s |
| delayed_insert_timeout | 300 |
| explicit_defaults_for_timestamp | OFF |
| flush_time | 0 |
| innodb_flush_log_at_timeout | 1 |
| innodb_lock_wait_timeout | 50 |
| innodb_old_blocks_time | 1000 |
| innodb_rollback_on_timeout | OFF |
| interactive_timeout | 10 |
| lc_time_names | en_US |
| lock_wait_timeout | 31536000 |
| long_query_time | 0.020000 |
| net_read_timeout | 30 |
| net_write_timeout | 60 |
| rpl_stop_slave_timeout | 31536000 |
| slave_net_timeout | 3600 |
| slow_launch_time | 2 |
| system_time_zone | CST |
| time_format | %H:%i:%s |
| time_zone | SYSTEM |
| timed_mutexes | OFF |
| timestamp | 1442821194.483634 |
| wait_timeout | 10 |
+---------------------------------+-------------------+
25 rows in set (0.00 sec)
(casystem)[email protected] [(none)]> show global variables like '%time%';
+---------------------------------+-------------------+
| Variable_name | Value |
+---------------------------------+-------------------+
| binlog_max_flush_queue_time | 0 |
| connect_timeout | 10 |
| datetime_format | %Y-%m-%d %H:%i:%s |
| delayed_insert_timeout | 300 |
| explicit_defaults_for_timestamp | OFF |
| flush_time | 0 |
| innodb_flush_log_at_timeout | 1 |
| innodb_lock_wait_timeout | 50 |
| innodb_old_blocks_time | 1000 |
| innodb_rollback_on_timeout | OFF |
| interactive_timeout | 10 |
| lc_time_names | en_US |
| lock_wait_timeout | 31536000 |
| long_query_time | 0.020000 |
| net_read_timeout | 30 |
| net_write_timeout | 60 |
| rpl_stop_slave_timeout | 31536000 |
| slave_net_timeout | 3600 |
| slow_launch_time | 2 |
| system_time_zone | CST |
| time_format | %H:%i:%s |
| time_zone | SYSTEM |
| timed_mutexes | OFF |
| wait_timeout | 8 |
+---------------------------------+-------------------+
24 rows in set (0.00 sec)
(casystem)[email protected] [(none)]> show variables like '%time%';
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 12
Current database: *** NONE ***
+---------------------------------+-------------------+
| Variable_name | Value |
+---------------------------------+-------------------+
| binlog_max_flush_queue_time | 0 |
| connect_timeout | 10 |
| datetime_format | %Y-%m-%d %H:%i:%s |
| delayed_insert_timeout | 300 |
| explicit_defaults_for_timestamp | OFF |
| flush_time | 0 |
| innodb_flush_log_at_timeout | 1 |
| innodb_lock_wait_timeout | 50 |
| innodb_old_blocks_time | 1000 |
| innodb_rollback_on_timeout | OFF |
| interactive_timeout | 10 |
| lc_time_names | en_US |
| lock_wait_timeout | 31536000 |
| long_query_time | 0.020000 |
| net_read_timeout | 30 |
| net_write_timeout | 60 |
| rpl_stop_slave_timeout | 31536000 |
| slave_net_timeout | 3600 |
| slow_launch_time | 2 |
| system_time_zone | CST |
| time_format | %H:%i:%s |
| time_zone | SYSTEM |
| timed_mutexes | OFF |
| timestamp | 1442821244.086780 |
| wait_timeout | 10 |
+---------------------------------+-------------------+
25 rows in set (0.00 sec)
新會話--新會話的逾時時間，未修改8秒。 （原因，實驗1，以interactive_timeout為準）
> show variables like '%time%';
+---------------------------------+-------------------+
| Variable_name | Value |
+---------------------------------+-------------------+
| binlog_max_flush_queue_time | 0 |
| connect_timeout | 10 |
| datetime_format | %Y-%m-%d %H:%i:%s |
| delayed_insert_timeout | 300 |
| explicit_defaults_for_timestamp | OFF |
| flush_time | 0 |
| innodb_flush_log_at_timeout | 1 |
| innodb_lock_wait_timeout | 50 |
| innodb_old_blocks_time | 1000 |
| innodb_rollback_on_timeout | OFF |
| interactive_timeout | 10 |
| lc_time_names | en_US |
| lock_wait_timeout | 31536000 |
| long_query_time | 0.020000 |
| net_read_timeout | 30 |
| net_write_timeout | 60 |
| rpl_stop_slave_timeout | 31536000 |
| slave_net_timeout | 3600 |
| slow_launch_time | 2 |
| system_time_zone | CST |
| time_format | %H:%i:%s |
| time_zone | SYSTEM |
| timed_mutexes | OFF |
| timestamp | 1442821214.497556 |
| wait_timeout | 10 |
+---------------------------------+-------------------+
25 rows in set (0.00 sec)
(casystem)[email protected] [(none)]> show global variables like '%time%';
+---------------------------------+-------------------+
| Variable_name | Value |
+---------------------------------+-------------------+
| binlog_max_flush_queue_time | 0 |
| connect_timeout | 10 |
| datetime_format | %Y-%m-%d %H:%i:%s |
| delayed_insert_timeout | 300 |
| explicit_defaults_for_timestamp | OFF |
| flush_time | 0 |
| innodb_flush_log_at_timeout | 1 |
| innodb_lock_wait_timeout | 50 |
| innodb_old_blocks_time | 1000 |
| innodb_rollback_on_timeout | OFF |
| interactive_timeout | 10 |
| lc_time_names | en_US |
| lock_wait_timeout | 31536000 |
| long_query_time | 0.020000 |
| net_read_timeout | 30 |
| net_write_timeout | 60 |
| rpl_stop_slave_timeout | 31536000 |
| slave_net_timeout | 3600 |
| slow_launch_time | 2 |
| system_time_zone | CST |
| time_format | %H:%i:%s |
| time_zone | SYSTEM |
| timed_mutexes | OFF |
| wait_timeout | 8 |
+---------------------------------+-------------------+
24 rows in set (0.00 sec)
3.4 global.wait_timeout>cnf.interactive_timeout時，global.wait_timeout是否會覆寫interactive_timeout？
set global wait_timeout=12;
目前會話---目前會話逾時時間并未修改成12秒，而且逾時之後也沒有修改成12秒 （原因，實驗1，以interactive_timeout為準）
> set global wait_timeout=12;
Query OK, 0 rows affected (0.00 sec)
(casystem)[email protected] [(none)]> show variables like '%time%';
+---------------------------------+-------------------+
| Variable_name | Value |
+---------------------------------+-------------------+
| binlog_max_flush_queue_time | 0 |
| connect_timeout | 10 |
| datetime_format | %Y-%m-%d %H:%i:%s |
| delayed_insert_timeout | 300 |
| explicit_defaults_for_timestamp | OFF |
| flush_time | 0 |
| innodb_flush_log_at_timeout | 1 |
| innodb_lock_wait_timeout | 50 |
| innodb_old_blocks_time | 1000 |
| innodb_rollback_on_timeout | OFF |
| interactive_timeout | 10 |
| lc_time_names | en_US |
| lock_wait_timeout | 31536000 |
| long_query_time | 0.020000 |
| net_read_timeout | 30 |
| net_write_timeout | 60 |
| rpl_stop_slave_timeout | 31536000 |
| slave_net_timeout | 3600 |
| slow_launch_time | 2 |
| system_time_zone | CST |
| time_format | %H:%i:%s |
| time_zone | SYSTEM |
| timed_mutexes | OFF |
| timestamp | 1442821797.229564 |
| wait_timeout | 10 |
+---------------------------------+-------------------+
25 rows in set (0.00 sec)
(casystem)[email protected] [(none)]> show global variables like '%time%';
+---------------------------------+-------------------+
| Variable_name | Value |
+---------------------------------+-------------------+
| binlog_max_flush_queue_time | 0 |
| connect_timeout | 10 |
| datetime_format | %Y-%m-%d %H:%i:%s |
| delayed_insert_timeout | 300 |
| explicit_defaults_for_timestamp | OFF |
| flush_time | 0 |
| innodb_flush_log_at_timeout | 1 |
| innodb_lock_wait_timeout | 50 |
| innodb_old_blocks_time | 1000 |
| innodb_rollback_on_timeout | OFF |
| interactive_timeout | 10 |
| lc_time_names | en_US |
| lock_wait_timeout | 31536000 |
| long_query_time | 0.020000 |
| net_read_timeout | 30 |
| net_write_timeout | 60 |
| rpl_stop_slave_timeout | 31536000 |
| slave_net_timeout | 3600 |
| slow_launch_time | 2 |
| system_time_zone | CST |
| time_format | %H:%i:%s |
| time_zone | SYSTEM |
| timed_mutexes | OFF |
| wait_timeout | 12 |
+---------------------------------+-------------------+
24 rows in set (0.00 sec)
(casystem)[email protected] [(none)]> show variables like '%time%';
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 19
Current database: *** NONE ***
+---------------------------------+-------------------+
| Variable_name | Value |
+---------------------------------+-------------------+
| binlog_max_flush_queue_time | 0 |
| connect_timeout | 10 |
| datetime_format | %Y-%m-%d %H:%i:%s |
| delayed_insert_timeout | 300 |
| explicit_defaults_for_timestamp | OFF |
| flush_time | 0 |
| innodb_flush_log_at_timeout | 1 |
| innodb_lock_wait_timeout | 50 |
| innodb_old_blocks_time | 1000 |
| innodb_rollback_on_timeout | OFF |
| interactive_timeout | 10 |
| lc_time_names | en_US |
| lock_wait_timeout | 31536000 |
| long_query_time | 0.020000 |
| net_read_timeout | 30 |
| net_write_timeout | 60 |
| rpl_stop_slave_timeout | 31536000 |
| slave_net_timeout | 3600 |
| slow_launch_time | 2 |
| system_time_zone | CST |
| time_format | %H:%i:%s |
| time_zone | SYSTEM |
| timed_mutexes | OFF |
| timestamp | 1442821862.560630 |
| wait_timeout | 10 |
+---------------------------------+-------------------+
25 rows in set (0.00 sec)
新會話 -- 新會話的逾時時間，未修改12秒。 （原因，實驗1，以interactive_timeout為準）
> show global variables like '%time%';
+---------------------------------+-------------------+
| Variable_name | Value |
+---------------------------------+-------------------+
| binlog_max_flush_queue_time | 0 |
| connect_timeout | 10 |
| datetime_format | %Y-%m-%d %H:%i:%s |
| delayed_insert_timeout | 300 |
| explicit_defaults_for_timestamp | OFF |
| flush_time | 0 |
| innodb_flush_log_at_timeout | 1 |
| innodb_lock_wait_timeout | 50 |
| innodb_old_blocks_time | 1000 |
| innodb_rollback_on_timeout | OFF |
| interactive_timeout | 10 |
| lc_time_names | en_US |
| lock_wait_timeout | 31536000 |
| long_query_time | 0.020000 |
| net_read_timeout | 30 |
| net_write_timeout | 60 |
| rpl_stop_slave_timeout | 31536000 |
| slave_net_timeout | 3600 |
| slow_launch_time | 2 |
| system_time_zone | CST |
| time_format | %H:%i:%s |
| time_zone | SYSTEM |
| timed_mutexes | OFF |
| wait_timeout | 12 |
+---------------------------------+-------------------+
24 rows in set (0.00 sec)
(casystem)[email protected] [(none)]> show variables like '%time%';
+---------------------------------+-------------------+
| Variable_name | Value |
+---------------------------------+-------------------+
| binlog_max_flush_queue_time | 0 |
| connect_timeout | 10 |
| datetime_format | %Y-%m-%d %H:%i:%s |
| delayed_insert_timeout | 300 |
| explicit_defaults_for_timestamp | OFF |
| flush_time | 0 |
| innodb_flush_log_at_timeout | 1 |
| innodb_lock_wait_timeout | 50 |
| innodb_old_blocks_time | 1000 |
| innodb_rollback_on_timeout | OFF |
| interactive_timeout | 10 |
| lc_time_names | en_US |
| lock_wait_timeout | 31536000 |
| long_query_time | 0.020000 |
| net_read_timeout | 30 |
| net_write_timeout | 60 |
| rpl_stop_slave_timeout | 31536000 |
| slave_net_timeout | 3600 |
| slow_launch_time | 2 |
| system_time_zone | CST |
| time_format | %H:%i:%s |
| time_zone | SYSTEM |
| timed_mutexes | OFF |
| timestamp | 1442821846.568606 |
| wait_timeout | 10 |
+---------------------------------+-------------------+
25 rows in set (0.00 sec)
(casystem)[email protected] [(none)]>
![資料遷移方法資料遷移原則資料遷移之雙寫方案資料遷移之級聯同步方案[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)