How to Resolve MySQL Too Many Connections Errors by Raising max_connections

Problem Overview

A colleague encountered a MySQL error indicating too many connections, as shown in the screenshot. The issue is traced to the MySQL server's max_connections setting.

Check Current max_connections

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

The result shows a default of 151 connections, which differs from the commonly assumed default of 100.

Default Values by MySQL Version

According to MySQL documentation:

MySQL 5.5, 5.6, 5.7: default max_connections = 151, maximum = 100000.

MySQL 5.1: default varies with sub‑version; maximum also varies.

MySQL 5.0: default = 100, maximum = 16384.

Method 1 – Edit my.cnf

Add or modify the line max_connections=1000 under the [mysqld] section of /etc/my.cnf, then restart the MySQL service.

Method 2 – Change the variable at runtime

mysql -uroot -p123456
mysql> set global max_connections = 200;
mysql> show processlist;
mysql> show status;
mysql> show variables like '%max_connections%';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 1000 |
+-----------------+-------+
1 row in set (0.00 sec)

Method 3 – Modify MySQL source code

{"max_connections", OPT_MAX_CONNECTIONS,
 "The number of simultaneous clients allowed.", (gptr*) &max_connections,
 (gptr*) &max_connections, 0, GET_ULONG, REQUIRED_ARG, 1500, 1, 16384, 0, 1,
 0},
/* Recompile and reinstall MySQL */

After editing mysqld.cc, rebuild MySQL with ./configure ; make ; make install.

Method 4 – Adjust mysqld_safe

then $NOHUP_NICENESS $ledir/$MYSQLD
  $defaults --basedir=$MY_BASEDIR_VERSION
  --datadir=$DATADIR $USER_OPTION
  --pid-file=$pid_file
  --skip-external-locking
  -O max_connections=1500 >> $err_log 2>&1
else eval "... $NOHUP_NICENESS $ledir/$MYSQLD ... -O max_connections=1500 >> $err_log 2>&1"

Save the changes, restart the MySQL service, and the new connection limit takes effect.