| 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 | ||
這個(gè)參數(shù)指的是數(shù)據(jù)庫(kù)系統(tǒng)在關(guān)閉它之前，服務(wù)器等待非交互式連接上的活動(dòng)的秒數(shù)。
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 | ||
這個(gè)參數(shù)指的是在關(guān)閉交互式連接之前，服務(wù)器等待活動(dòng)的秒數(shù)
Warning:警告這兩個(gè)參數(shù)建議一起調(diào)節(jié)，能夠避免一些坑。
本文的兩個(gè)參數(shù)值采用的是默認(rèn)值
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)
另外在數(shù)據(jù)庫(kù)中，我們重點(diǎn)關(guān)注下這兩個(gè)參數(shù)，看看什么情況下Aborted_clients會(huì)提升，什么情況下Aborted_connects 會(huì)提升
mysql>show global status like 'aborted%'; +------------------+-------+ |Variable_name | Value | +------------------+-------+ |Aborted_clients | 19 | |Aborted_connects | 0 | +------------------+-------+ 2 rows inset (0.00 sec)
Part4:案例1
這里我故意輸入錯(cuò)誤的密碼5次，來(lái)看下數(shù)據(jù)庫(kù)的error log和Aborted的哪個(gè)參數(shù)記載了這一問(wèn)題
[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 記錄了密碼錯(cuò)誤的這一問(wèn)題
mysql>show global status like 'aborted%'; +------------------+-------+ |Variable_name | Value | +------------------+-------+ |Aborted_clients | 19 | |Aborted_connects | 5 | +------------------+-------+ 2 rows inset (0.00 sec)
error log中，也記載了這類(lèi)密碼輸錯(cuò)的信息
[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
接下來(lái)我們看下文章第三節(jié)提到的兩個(gè)重點(diǎn)參數(shù)對(duì)數(shù)據(jù)庫(kù)連接的行為影響
這里我們將這兩個(gè)參數(shù)均配置為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數(shù)上升，這是由于timeout參數(shù)控制的，已經(jīng)連接上數(shù)據(jù)的連接被殺掉。
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
在這個(gè)案例中我們看下最大連接數(shù)對(duì)數(shù)據(jù)庫(kù)連接的行為影響
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)
這里看到爆出了連接數(shù)過(guò)多的問(wèn)題
[root@HE3~]# mysql -uroot -pMANAGER -h127.0.0.1 ERROR 1040 (HY000): Too many connections
而錯(cuò)誤日志沒(méi)有任何記錄
Part7:案例4
第三方工具navicat select結(jié)果沒(méi)有出來(lái)的時(shí)候選擇停止則出現(xiàn)
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:原因總結(jié)
總結(jié)
通過(guò)這4個(gè)案例，我們能夠了解到，Aborted_clients、和Aborted_connects的區(qū)別，以及什么情況下會(huì)爆出什么樣的錯(cuò)誤日志，文章第二節(jié)中的幾個(gè)Aborted錯(cuò)誤是常見(jiàn)的錯(cuò)誤，這類(lèi)錯(cuò)誤出現(xiàn)的時(shí)候腦海里要有一個(gè)理論知識(shí)，知道什么情況下，會(huì)出現(xiàn)什么樣的錯(cuò)誤，以便快速定位問(wèn)題。由于筆者的水平有限，編寫(xiě)時(shí)間也很倉(cāng)促，文中難免會(huì)出現(xiàn)一些錯(cuò)誤或者不準(zhǔn)確的地方，不妥之處懇請(qǐng)讀者批評(píng)指正。
好了，以上就是這篇文章的全部?jī)?nèi)容了，希望本文的內(nèi)容對(duì)大家的學(xué)習(xí)或者工作能帶來(lái)一定的幫助，如果有疑問(wèn)大家可以留言交流，謝謝大家對(duì)腳本之家的支持。
標(biāo)簽：三明 AXB 林芝 宿州 內(nèi)江 朔州 汕頭 咸寧
巨人網(wǎng)絡(luò)通訊聲明：本文標(biāo)題《通過(guò)案例分析MySQL中令人頭疼的Aborted告警》，本文關(guān)鍵詞 通過(guò),案例分析,MySQL,中,；如發(fā)現(xiàn)本文內(nèi)容存在版權(quán)問(wèn)題，煩請(qǐng)?zhí)峁┫嚓P(guān)信息告之我們，我們將及時(shí)溝通與處理。本站內(nèi)容系統(tǒng)采集于網(wǎng)絡(luò)，涉及言論、版權(quán)與本站無(wú)關(guān)。
