如何解决MySQL max_user_connections错误

I'm getting following error when I try to log onto phpMyAdmin.

我尝试登录phpMyAdmin时遇到以下错误。

User ** already has more than 'max_user_connections' active connections

用户**已经超过'max_user_connections'活动连接

Could anyone let me know how to close these DB connections from MySQL server end?

谁能让我知道如何从MySQL服务器端关闭这些数据库连接？

Thank you for your time!

感谢您的时间！

4 个解决方案

#1

Here i think you can solve your problem max_connections

在这里，我认为你可以解决你的问题max_connections

Also read this http://dev.mysql.com/doc/refman/5.5/en/too-many-connections.html

另请阅读http://dev.mysql.com/doc/refman/5.5/en/too-many-connections.html

#2

First, this is a hack, but works, especially on a shared host.
We all have bad "neighbors" sometimes, right?

首先，这是一个黑客，但有效，特别是在共享主机上。我们有时候都有坏邻居，对吧？

If you have access to your /etc/ increase the limit from 30 to 50, in your my.cnf or through the information schema.

如果您有权访问/ etc /，可以在my.cnf中或通过信息架构将限制从30增加到50。

1. To ignore the error message the visitor might see, use @mysql_connect().
   
2. 要忽略访问者可能看到的错误消息，请使用@mysql_connect（）。
3. If there are more than 30 MUCs, use the "or die()" statement to stop the query.
   
4. 如果有超过30个MUC，请使用“或die（）”语句来停止查询。
5. Replace the "or die" message with die(header(location: THIS PAGE)) and be sure to mysql_close();
6. 用die（header（location：THIS PAGE））替换“或die”消息，并确保mysql_close（）;

Yes, it will cause a delay in page loading. But better to load than a white screen of death -or worse error messages that visitors have no understanding of.

是的，它会导致页面加载延迟。但是加载比白屏死机更好 - 或者是访问者不了解的更糟糕的错误信息。

#3

Your best bet is to increase max_connections. For a MySQL instance serving multiple different web apps (raw php, WordPress, phpBB), you probably want a value of at least 60 for this.

最好的办法是增加max_connections。对于服务于多个不同Web应用程序（原始php，WordPress，phpBB）的MySQL实例，您可能需要至少60的值。

Issue this command and you'll find out how many global connections you have available:

发出此命令，您将找到可用的全局连接数：

show global variables like '%connections%'

You can find out how many connections are in use at any given moment like this:

您可以在任何给定时刻找出正在使用的连接数，如下所示：

show status like '%connected%'

You can find out what each connection is doing like this:

您可以找到每个连接正在执行的操作：

show full processlist

I would try for a global value of at least 100 connections if I were you. Your service provider ought to be able to help you if you don't have access to do this. It needs to be done in the my.cnf file configuration for MySQL. Don't set it too high or you run the risk of your MySQL server process gobbling up all your RAM.

如果我是你，我会尝试至少100个连接的全球价值。如果您无权访问，您的服务提供商应该能够为您提供帮助。它需要在MySQL的my.cnf文件配置中完成。不要设置得太高，否则你冒着MySQL服务器进程吞噬所有内存的风险。

A second approach allows you to allocate those overall connections to your different MySQL users. If you have different MySQL usernames for each of your web apps, this approach will work for you. This approach is written up here. https://www.percona.com/blog/2014/07/29/prevent-mysql-downtime-set-max_user_connections/

第二种方法允许您将这些总体连接分配给不同的MySQL用户。如果您的每个Web应用程序都有不同的MySQL用户名，则此方法适合您。这种方法写在这里。 https://www.percona.com/blog/2014/07/29/prevent-mysql-downtime-set-max_user_connections/

The final approach to controlling this problem is more subtle. You're probably using the Apache web server as underlying tech. You can reduce the number of Apache tasks running at the same time to, paradoxically, increase throughput. That's because Apache queues up requests. If it has a few tasks efficiently banging through the queue, that is often faster than lots of tasks because there's less contention. It also requires fewer MySQL connections, which will solve your immediate problem. That's explained here: Restart Mysql automatically when ubuntu on EC2 micro instance kills it when running out of memory

控制这个问题的最终方法更加微妙。您可能正在使用Apache Web服务器作为底层技术。您可以同时减少运行的Apache任务的数量，矛盾的是，增加吞吐量。那是因为Apache排队请求。如果它有一些任务有效地敲击队列，那通常比许多任务更快，因为争用较少。它还需要更少的MySQL连接，这将解决您的直接问题。这是在这里解释的：当EC2微实例上的ubuntu在内存不足时杀死它时，自动重启Mysql

By the way, web apps like WordPress use a persistent connection pool. That is, they establish connections to the MySQL data base, hold them open, and reuse them. If your apps are busy, each connection's lifetime ought to be several minutes.

顺便说一下，像WordPress这样的网络应用程序使用持久连接池。也就是说，它们建立与MySQL数据库的连接，保持它们打开，并重用它们。如果你的应用程序很忙，每个连接的生命周期应该是几分钟。

#4

This happens due to limit specified in the mysql configuration, the system variable max_user_connections.

这是由于mysql配置中指定的限制，即系统变量max_user_connections。

Solutions

Killing the queries which are stuck at the backend is only a solution I would suggest if it is a SELECT query. Queries that change data, like UPDATE/DELETE/INSERT, are not to be killed.

杀死滞留在后端的查询只是我建议的解决方案，如果它是SELECT查询。更改数据的查询（如UPDATE / DELETE / INSERT）不会被杀死。

Secondly, you can use the command mysqladmin processlist to check what is going on inside mysql.

其次，您可以使用命令mysqladmin processlist来检查mysql内部的内容。

If locking is causing your problem, you can check which engine you are using and change it to another. IBM's SolidDB documentation on table locks might help you. Though there may be another reason for this. (For example, perhaps your queries are taking too long because of an unoptimized query, or the table size is too big, or you have a spammed database).

如果锁定导致您的问题，您可以检查您正在使用的引擎并将其更改为另一个引擎。 IBM关于表锁的SolidDB文档可能对您有所帮助。虽然可能还有其他原因。 （例如，由于未经优化的查询，或者表大小太大，或者您有垃圾邮件数据库，可能您的查询花费的时间太长）。