未命名設(shè)計(1).jpg

我直接在標(biāo)題寫出這個錯誤原因，是因為我希望讓讀者搜索到一個“快速”的解決方法，

話不多說，我們直接來看怎么處理這個問題。

很多開發(fā)人員都會遇見”MySQL: ERROR 1040: Too many connections”的異常情況，造成這種情況的一種原因是訪問量過高，MySQL服務(wù)器抗不住，這個時候就要考慮增加從服務(wù)器分散讀壓力；另一種原因就是MySQL配置文件中max_connections值過小。

首先，我們來查看mysql的最大連接數(shù)：

mysql> show variables like '%max_connections%';
+-----------------+-------+
| Variable_name  | Value |
+-----------------+-------+
| max_connections | 151  |
+-----------------+-------+
1 row in set (0.00 sec)

其次，查看服務(wù)器響應(yīng)的最大連接數(shù):

mysql> show global status like 'Max_used_connections';
+----------------------+-------+
| Variable_name    | Value |
+----------------------+-------+
| Max_used_connections | 2   |
+----------------------+-------+
1 row in set (0.00 sec)

在這里我們可以看到服務(wù)器響應(yīng)的最大連接數(shù)為2，遠(yuǎn)遠(yuǎn)低于mysql服務(wù)器允許的最大連接數(shù)值。我們來算一下比例，2/150 ~=1.55%，這么小的比例，很明顯給服務(wù)器在面對大批量的請求連接時會出現(xiàn)崩潰狀態(tài)，失去響應(yīng)。

對于mysql服務(wù)器最大連接數(shù)值的設(shè)置范圍比較理想的是：服務(wù)器響應(yīng)的最大連接數(shù)值占服務(wù)器上限連接數(shù)值的比例值在10%以上，如果在10%以下，說明mysql服務(wù)器最大連接上限值設(shè)置過高。

Max_used_connections / max_connections * 100% = 2/151 *100% ≈ 1%

我們來修改一下

mysql> show variables like '%max_connections%';
+-----------------+-------+
| Variable_name  | Value |
+-----------------+-------+
| max_connections | 151  |
+-----------------+-------+
1 row in set (0.19 sec) 
mysql> show global status like 'Max_used_connections';
+----------------------+-------+
| Variable_name    | Value |
+----------------------+-------+
| Max_used_connections | 44  |
+----------------------+-------+
1 row in set (0.17 sec)

這里的最大連接數(shù)占上限連接數(shù)的30％左右。
上面我們知道怎么查看mysql服務(wù)器的最大連接數(shù)值，并且知道了如何判斷該值是否合理，下面我們就來介紹一下如何設(shè)置這個最大連接數(shù)值。

方法1：

mysql> set GLOBAL max_connections=256; 
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like '%max_connections%';
+-----------------+-------+
| Variable_name  | Value |
+-----------------+-------+
| max_connections | 256  |
+-----------------+-------+
1 row in set (0.00 sec)

方法2：

修改mysql配置文件my.cnf，在[mysqld]段中添加或修改max_connections值：

max_connections=128
重啟mysql服務(wù)即可。

附。docker啟動MySql的yml文件

version: '3'
services:
  mysql:
    image: mysql
    ports:
      - 3306:3306
    volumes:
      - /data/worker_data/db/:/var/lib/mysql
      - /data/apps/scheduler/deploy/prod/my.cnf:/etc/mysql/my.cnf
      - /data/apps/scheduler/deploy/prod/sql:/data/sql
    environment:
      - MYSQL_DATABASE=scheduler_test
      - MYSQL_ROOT_PASSWORD=root
    networks:
      - spiders_net
networks:
  spiders_net:
    external: true

下面看看cnf文件

# Copyright (c) 2017, Oracle and/or its affiliates. All rights reserved.
#
# This program is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation; version 2 of the License.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA  02110-1301 USA

#
# The MySQL  Server configuration file.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

[mysqld]
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
datadir         = /var/lib/mysql
secure-file-priv= NULL
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
skip-name-resolve
max_connections=1000
max_user_connections=500
wait_timeout=200

# Custom config should go here
!includedir /etc/mysql/conf.d/