懒人听书,大主宰

新聞中心

這里有您想知道的互聯(lián)網(wǎng)營銷解決方案

MySQL為什么莫名其妙的斷開連接以及解決方案！

前言

最近遇到在將本地的項目部署到服務(wù)器上之后遇到的一個奇怪問題：

成都創(chuàng)新互聯(lián)公司是一家專業(yè)提供萬源企業(yè)網(wǎng)站建設(shè),專注與成都網(wǎng)站建設(shè)、網(wǎng)站設(shè)計、H5建站、小程序制作等業(yè)務(wù)。10年已為萬源眾多企業(yè)、政府機(jī)構(gòu)等服務(wù)。創(chuàng)新互聯(lián)專業(yè)網(wǎng)站設(shè)計公司優(yōu)惠進(jìn)行中。

在部署完成后，網(wǎng)站當(dāng)時可以正常工作，但是第二天訪問網(wǎng)站的時候卻會遇到一個500 Server Error。

從日志中可以看出是MySQL數(shù)據(jù)庫出現(xiàn)了異常：

翻譯如下:

最后一個數(shù)據(jù)包在 83827560 ms 之前被成功接收，最后一個數(shù)據(jù)包在83827560 ms 之前被成功發(fā)送。比服務(wù)的配置參數(shù)wait_timeout的值要長。

日志中給出的建議如下：

翻譯如下：

你應(yīng)考慮在程序中進(jìn)行數(shù)據(jù)庫操作之前檢驗數(shù)據(jù)庫連接的有效性或者將數(shù)據(jù)庫的autoReconnect屬性設(shè)置為true來避免這個問題

關(guān)于wait_timeout和autoReconnect下面我們會依次分析介紹！

原因分析

我們進(jìn)入mysql的命令行查詢超時時間：

28800單位是秒轉(zhuǎn)化成小時就是8小時，看出MySQL的默認(rèn)設(shè)置，當(dāng)一個連接的空閑時間超過8小時后，MySQL就會斷開該連接。

所以發(fā)現(xiàn)問題出在如果超過這個wait_timeout時間(默認(rèn)是8小時)對數(shù)據(jù)庫沒有任何操作，那么MySQL會自動關(guān)閉數(shù)據(jù)庫連接以節(jié)省資源。

數(shù)據(jù)庫連接自動斷開的問題確實是在第二天發(fā)生了，也就是在一個晚上沒有對數(shù)據(jù)庫進(jìn)行操作(顯然超過了8小時)的情況下發(fā)生的這個問題。

大家用命令show processlist; 可以查看Sleep狀態(tài)的進(jìn)程Sleep，同時可以看到每個進(jìn)程Sleep多久了：

下面介紹下解決和優(yōu)化辦法！

解決方法

1. autoReconnect

這個參數(shù)表示在mysql超時斷開連接后會自動重新連接，配置的話，只需要在連接mysql的語句寫上autoReconnect=true：

jdbc:mysql://127.0.0.1:3306/stock_tweet?autoReconnect=true

下面是MySQL官網(wǎng)對autoReconnect的解釋:

同時可以看到官網(wǎng)不推薦使用這個參數(shù)，因為它有一些副作用，具體介紹下：

原有連接上的事務(wù)將會被回滾，事務(wù)的提交模式將會丟失；
原有連接持有的表的鎖將會全部釋放；
原有連接關(guān)聯(lián)的會話Session將會丟失，重新恢復(fù)的連接關(guān)聯(lián)的將會是一個新的會話Session；
原有連接定義的用戶變量將會丟失；
原有連接定義的預(yù)編譯SQL將會丟失；
原有連接失效，新的連接恢復(fù)后，MySQL將會使用新的記錄行來存儲連接中的性能數(shù)據(jù)；

2. 修改配置

涉及到兩個配置參數(shù)interactive_timeout和wait_timeout：

wait_timeout 指的是mysql在關(guān)閉一個非交互的連接之前所要等待的秒數(shù)
interactive_time 指的是mysql在關(guān)閉一個交互的連接之前所要等待的秒數(shù)

對于交互和非交互連接，說得直白一點就是，通過mysql客戶端連接數(shù)據(jù)庫是交互式連接，通過jdbc連接數(shù)據(jù)庫是非交互式連接。

配置方法：

(1) 會話方式

msyql> set global wait_timeout=2880000;
msyql> set global interactive_timeout=2880000;

這種方式只對當(dāng)前會話生效.

(2) 修改配置文件方式

修改/etc/my.cnf文件，在 [mysqld] 節(jié)中設(shè)置：

之后再重啟下服務(wù)器就好了！

注意：

將wait_timeout這個值設(shè)置得大了，可能會導(dǎo)致空閑連接過多。
如果你的MySQL Server有大量的閑置連接，他們不僅會白白消耗內(nèi)存，而且如果連接一直在累加而不斷開，最終肯定會達(dá)到MySQL Server的連接上限數(shù)，這會報'too many connections'的錯誤。

3. 連接池配置

因為連接池的配置也會影響項目和MySQL的連接，所以也需要對數(shù)據(jù)庫連接池的一些配置做一定修改

我們以Spring Boot 2.0默認(rèn)的數(shù)據(jù)庫連接池HikariCP為例

主要是下面這幾個配置

(1) maximum-pool-size：

最大連接數(shù)，超過這個數(shù)，新的數(shù)據(jù)庫訪問線程會被阻，缺省值：10。

常見的錯誤是設(shè)置一個太大的值，連接數(shù)多反而性能下降。

參考計算公式是：

#core_count：CPU個數(shù)，effective_spindle_count:硬盤個數(shù)
connections = ((core_count * 2) + effective_spindle_count)

例如：一個4核，1塊硬盤的服務(wù)器，連接數(shù) = (4 * 2) + 1 = 9，湊個整數(shù)，10就可以了。

(2) minimum-idle：

最小的連接數(shù)目。

(3) max-lifetime:

最大的連接時間，用來設(shè)置一個connection在連接池中的存活時間。

缺?。?0分鐘。強(qiáng)烈建議設(shè)置比數(shù)據(jù)庫超時時長少一點(MySQL的wait_timeout參數(shù)一般為8小時)。

(4) idle-timeout:

一個連接idle狀態(tài)的最長時間，超時則被釋放。

網(wǎng)站題目：MySQL為什么莫名其妙的斷開連接以及解決方案！
URL地址：http://m.fisionsoft.com.cn/article/dpsghpo.html