How to limit active connections in MySQL or MariaDB

Limiting active connections in a MySQL or MariaDB server is an important aspect of database management. Setting a maximum number of allowed connections helps prevent excessive load on the server and ensures stability. Without a connection limit, a surge in user connections or application errors could overload the database, leading to performance degradation or failure.

The max_connections setting allows database administrators to define the maximum number of concurrent connections to the database. When this limit is reached, any additional connection attempts are rejected until existing ones are closed. This is an essential safeguard, especially for high-traffic servers or when troubleshooting connection leaks in applications.

Administrators can monitor the current connection count and adjust the maximum allowed connections based on the server’s capacity and typical usage. Modifying the limit is straightforward and can be done dynamically or through the configuration file to persist after restarts.

1. Access the MySQL or MariaDB shell with administrative privileges.

   $ mysql -u root -p
   Enter password: ********

2. Check the current maximum number of allowed connections.

   SHOW VARIABLES LIKE 'max_connections';

   This displays the current value of the max_connections setting.

3. Set a new maximum limit for active connections.

   SET GLOBAL max_connections = 200;

   Replace “200” with the desired connection limit. This change takes effect immediately but will not persist after a server restart.

4. Edit the my.cnf or my.ini configuration file to make the change permanent.

   [mysqld]
   max_connections = 200

5. Restart the MySQL or MariaDB service to apply the persistent changes.

   $ sudo systemctl restart mysql

   Restarting the service ensures that the new max_connections value persists across server reboots.

6. Verify the current number of active connections.

   SHOW STATUS LIKE 'Threads_connected';

   This command shows the total number of active connections at the moment.