• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar

The Geek Diary

CONCEPTS | BASICS | HOWTO

  • OS
    • Linux
    • CentOS/RHEL
    • Solaris
    • Oracle Linux
    • Linux Services
    • VCS
  • Database
    • oracle
    • oracle 12c
    • ASM
    • mysql
    • MariaDB
    • Data Guard
  • DevOps
    • Docker
    • Shell Scripting
  • Interview Questions
  • Big Data
    • Hadoop
    • Cloudera
    • Hortonworks HDP

MySQL Error “Too many connections” and how to resolve it

By admin

Problem

When a client tries to log into MySQL it may sometimes be rejected and receive an error message saying that there are “too many connections“. This means that the maximum number of clients that may be connected to the server has been reached. Either the client will have to wait for another client to log off, or the administrator will have to increase the maximum number of connections allowed.

Information about connections to a server can be found using the SHOW STATUS statement:

$ mysql –u root –p
SHOW STATUS LIKE 'max_used_connections';

First, you should ensure that your applications are closing connections to the server when they are no longer needed. However, you can solve this error by increasing the value of the max_connections variable and possibly decreasing the value of wait_timeout if you expect that many of the connections to your server are not being actively used.

Solution

The maximum number of connections threads allowed for the server is contained in the system variable max_connections. The default value is 151. To see the value to which this variable is set, run the following SQL command:

$ mysql –u root –p
mysql> SHOW VARIABLES LIKE 'max_connections';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 151   |
+-----------------+-------+

Changing the max_connections parameter (Temporarily)

To change the value of the system variable max_connections, the –max_connections option can be used. To change this variable temporarily while the server is running, enter the following SQL statement:

$ mysql –u root –p
mysql> SET GLOBAL max_connections = 512;
Note: It’s not necessary to have a client which is currently connected to the server disconnect to run this SQL statement as root. The server permits one connection more than the value of the max_connections. One connection is reserved for the root user or any other accounts that has SUPER privilege.

When the MySQL server daemon (mysqld) is restarted the above value will set back to the default value of 151. To make changes permanent use the below method.

Changing the max_connections parameter (Permanently)

A better method to change max_connections parameter would be to add this option to the options file (my.cnf or my.ini, depending on your system) so that it takes effect next time the server is restarted. Assuming you are using /etc/my.cnf file, add the below line to it.

# vi /etc/my.cnf
max_connections = 512

Now restart the mysqld daemon for the changes to take effect.

For CentOS/RHEL 6:

# service mysqld restart

For CentOS/RHEL 7:

# systemctl restart mysqld

What is the Maximum Value for max_connections on Linux?

The upper limit for how large you can configure max_connections is largely determined by the operating system in addition to the limit of 100,000 that MySQL sets. Linux has a limit called max open files, this is defined “per login” and says the maximum number of files a process can open. The default is 1024 (which you can see using ulimit -n).

Filed Under: mysql

Some more articles you might also be interested in …

  1. How To Restore an NDBCluster Backup Using ndb_restore
  2. Backup and Restore Of Group Replication Node ( MySQL 8.0 )
  3. What is the MySQL Enterprise Monitor?
  4. How to Generate Unique IDs For MySQL Cluster Backups
  5. How to Rollback RPM Upgrade of the MySQL Server
  6. How to Restore a Specific Database or Table (MySQL)
  7. Understanding the REVOKE statement in MySQL
  8. Which Ports are Used by mysqld, ndb_mgmd, and ndbd/ndbmtd in a MySQL Cluster Installation
  9. Understanding MySQL Storage Engines – MyISAM, MEMORY, BLACKHOLE and ARCHIVE
  10. MySQL Table Maintenance – InnoDB, MyISAM, MEMORY, ARCHIVE

You May Also Like

Primary Sidebar

Recent Posts

  • SQL script to find tables that are fragmented
  • TRUNCATE TABLE not releasing space from tablespace
  • How to reclaim entire space of an oracle database table with “Truncate Table” statement
  • Oracle SQL Script to Report Tablespace Free and Fragmentation
  • Archives
  • Contact Us
  • Copyright

© 2021 · The Geek Diary