Over the years of being a DBA, I had to deal with all kinds of problems in the database. One of the most common problems I faced is the one related to the well-known error “ERROR 1040 (08004): Too many connections”. A lot has been written about this error. Still, the users keep falling into this trap, maybe because of a poorly configured database, a change in the application components, or just because of a sudden increase of connections in the application. At some point, we all face this issue in our careers, not only once but many times. The main objective of this blog post is to point out the new administrative connections allowed on MySQL 8, as these connections can save us from restarting the instance in case this happens.

Default behavior

We know that the amount of connections allowed in the database is defined by the parameter “max_connections.” The default value for this parameter is 151, and it can be changed dynamically, which means without a database restart. If the connections in the database are maxed out, we will hit the dreadful message “ERROR 1040 (08004): Too many connections”. It is important to remember that out of the box, MySQL allows one extra connection, this connection is reserved for the users with “SUPER” privilege (already deprecated here) or the CONNECTION_ADMIN privilege.

I’ll show an example of this feature; for this example, I have an instance with “max_connections=20”, and I have three users, user “monitor1” has only the PROCESS privilege, user “admin1” has the privileges PROCESS and CONNECTION_ADMIN, finally user “admin2” has the privilege SUPER (deprecated). We will see how MySQL treats these connections in the event of having an instance maxed out on user connections:

As you can see, one single connection with a user with “CONNECTION_ADMIN” or “SUPER” privilege is allowed, however, when user “monitor1” tried to connect, it was not possible because it did not have any of those privileges. Once we gain access to the database, we can easily increase the connections by changing the variable “max_connections” online and then checking the origin of the problem. It is important to remember that only one of these connections is allowed, so please don’t grant these privileges to any user, or you could still be locked out of your database.

Usually, when this problem occurs, and we cannot gain access to MySQL, the immediate solution is to restart the database and deal with all consequences that this causes, but hey… that is better than rejecting connections for several minutes during the business’s normal operating hours. There is another alternative to gain access to the database, which is by using GDB, but it is not always possible, and Too many connections? No problem! is an article we wrote about this tool in the past, the article is a bit old but still valid.

Side note for Percona Server for MySQL and MariaDB

Percona Server for MySQL, in versions before 8.0.14, had another way to access the database instance, similar to the new feature introduced in version 8.0.14. It was by enabling variables “extra_port” and “extra_max_connections,” and the usage of these variables is out of the scope of this blog post, but the objective of such variables was to allow connections to the database even when the database maximum connections have been reached. Remember that those variables were removed on version 8.0.14, and if found in the config file, the instance will not start, and an error will be shown. Like Percona Server for MySQL, MariaDB had a similar implementation for the same variables. Documentation for MariaDB can be found here.

New feature

Starting with MySQL 8.0.14, a new “Administrative Connections” or “Administrative Network Interface” feature was introduced. This feature allows connections to the database through an administrative port, there is no limit on the number of administrative connections. The difference between this feature and the single connection shown in the previous example is that this is a different port, and it does not limit the connections to only one but more than one connection if required. This should allow us to access the database when the user connections are maxed out and work from there to increase the connections or kill some of the application connections.

The easiest way to enable the “Administrative Connections” is to define the “admin_address” variable, this is the IP address that the administrative connections will listen to, for example, if you only want to allow local connections, you can define this variable as “127.0.0.1”, or if you want to connect through the network, you can define this variable as the server’s IP address. This variable is not dynamic, which means it will require a database restart. By default, this variable is empty, meaning the administrative interface is disabled. Another related variable is “admin_port”; this variable defines the port MySQL will listen to for the administrative connections, the default value for this variable is 33062. Once you define both variables and restart the database, you will see a message indicating the admin interface is ready for connections in the error log:

Now that the admin interface is configured, you need to define the users that can access this administrative connection. These users will require the “SERVICE_CONNECTION_ADMIN” privilege; otherwise, they won’t be able to connect to it. Following our initial example, I have granted the “SERVICE_CONNECTION_ADMIN” to the user “admin1” but not to user “admin2”

Testing connection to the admin interface, we see that only user “admin1” is allowed, while user “admin2” connection is rejected for lacking privilege “SERVICE_CONNECTION_ADMIN.” Also, we can confirm user “admin1” is connected to port 33062, which is the port used for the admin interface.

Conclusion

If you are using MySQL 8.0.14 or higher, you should enable the admin interface, as we have seen, enabling this feature is super easy and leverages a great feature by allowing access to the database to DBAs in case of an event of “ERROR 1040 (08004): Too many connections”. This new feature does not affect normal database performance and brings great power to DBAs.  Please consider adding the privilege “SERVICE_CONNECTION_ADMIN” only to administrative users, not application users, the idea is not to abuse this feature. If you are still using a lower version of Percona Server for MySQL, please remember you can configure variables  “extra_port” and “extra_max_connections” to access your database in case you face a max connections issue.

Percona Distribution for MySQL is the most complete, stable, scalable, and secure open-source MySQL solution available, delivering enterprise-grade database environments for your most critical business applications… and it’s free to use!

 

Try Percona Distribution for MySQL today!

Subscribe
Notify of
guest

1 Comment
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
Javier Pérez Escamilla

Very interesting. I need to run the script and see the effects. By the way, how can we reuse the connections in the connection pool? The cost of opening and closing the connection is very high. What Strategy can we use in those cases?

1
Reply