Diagnosing and Increasing MySQL Max Connections

When a colleague encountered a Jira error caused by MySQL connection limits, the issue was traced to the max_connections setting. The default value varies by MySQL version, typically 151 for 5.5‑5.7 and 100 for 5.0, with upper limits ranging from 10,000 to 100,000.

Step 1: Check the 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)

If the limit is lower than needed, it can be increased.

Step 2: Modify the limit in the MySQL configuration file

Edit /etc/my.cnf and add or change the line in the [mysqld] section:

max_connections=1000

Restart the MySQL service for the change to take effect.

Step 3: Change the limit at runtime via SQL

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)

Step 4: Modify the MySQL source code (advanced)

{"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},
// Change the default value (e.g., to 1500) and recompile MySQL.

Step 5: Adjust the limit via 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 "... -O max_connections=1500 >> $err_log 2>&1"

After editing, save the file and restart the MySQL service. These methods allow you to raise the max connections limit to suit application demands.