MysqlHow-ToBeginner · 3 min read

How to Set Max Connections in MySQL: Simple Guide

To set the maximum number of connections in MySQL, use the max_connections system variable. You can set it temporarily with SET GLOBAL max_connections = value; or permanently by adding max_connections=value in the MySQL configuration file my.cnf under the [mysqld] section.

📐

Syntax

The max_connections variable controls the maximum number of simultaneous client connections MySQL allows.

You can set it temporarily at runtime or permanently in the configuration file.

Temporary (runtime): SET GLOBAL max_connections = value;
Permanent (config file): Add max_connections=value under the [mysqld] section in my.cnf or my.ini

sql

SET GLOBAL max_connections = 200;

💻

Example

This example shows how to increase the max connections to 150 temporarily and check the current setting.

sql

SHOW VARIABLES LIKE 'max_connections';
SET GLOBAL max_connections = 150;
SHOW VARIABLES LIKE 'max_connections';

Output

+-----------------+-------+ | Variable_name | Value | +-----------------+-------+ | max_connections | 151 | +-----------------+-------+ Query OK, 0 rows affected (0.00 sec) +-----------------+-------+ | Variable_name | Value | +-----------------+-------+ | max_connections | 150 | +-----------------+-------+

⚠️

Common Pitfalls

Common mistakes when setting max_connections include:

Setting it too high without enough server resources, causing performance issues.
Changing the variable only at runtime without updating the config file, so the change is lost after restart.
Not having proper privileges to set global variables.

Always check current usage and server capacity before increasing this value.

sql

/* Wrong: Setting max_connections only temporarily and expecting it to persist after restart */
SET GLOBAL max_connections = 300;

/* Right: Also update my.cnf file for permanent change */
# In my.cnf under [mysqld]
max_connections=300

📊

Quick Reference

Action	Command or Location	Notes
Check current max connections	SHOW VARIABLES LIKE 'max_connections';	Shows current max connections value
Set max connections temporarily	SET GLOBAL max_connections = value;	Effective until MySQL restarts
Set max connections permanently	Add max_connections=value in my.cnf under [mysqld]	Persists after restart
Restart MySQL server	sudo systemctl restart mysql	Required after config file change

✅

Key Takeaways

Use SET GLOBAL max_connections to change max connections temporarily.

Edit my.cnf under [mysqld] to set max connections permanently.

Restart MySQL after changing the config file for changes to apply.

Avoid setting max_connections too high without enough server resources.

Check current max_connections with SHOW VARIABLES LIKE 'max_connections';