如何解决 MySQL max_user_connections 错误
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11183727/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to solve MySQL max_user_connections error
提问by Yasiru G
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!
感谢您的时间！
采纳答案by osyan
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
回答by O. Jones
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.cnffile 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 个连接的全局值。如果您无权这样做，您的服务提供商应该能够帮助您。它需要在my.cnfMySQL的文件配置中完成。不要将它设置得太高，否则你的 MySQL 服务器进程可能会吞噬你所有的 RAM。
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 reducethe number of Apache tasks running at the same time to, paradoxically, increasethroughput. 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 这样的 Web 应用程序使用持久连接池。也就是说，它们建立到 MySQL 数据库的连接，保持它们打开状态，然后重用它们。如果你的应用很忙，每个连接的生命周期应该是几分钟。
回答by charless
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。
- To ignore the error message the visitor might see, use
@mysql_connect(). - If there are more than 30 MUCs, use the "
or die()" statement to stop the query. - Replace the "or die" message with
die(header(location: THIS PAGE))and be sure tomysql_close();
- 要忽略访问者可能会看到的错误消息，请使用
@mysql_connect()。 - 如果超过 30 个 MUC，请使用“
or die()”语句停止查询。 - 将“或死”消息替换为
die(header(location: THIS PAGE))并确保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.
是的，它会导致页面加载延迟。但比白屏死机更好加载 - 或者访问者不了解的更糟糕的错误消息。
回答by rawatsandeep1989
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 processlistto 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 locksmight 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文档可能对您有所帮助。虽然这可能还有另一个原因。（例如，您的查询可能因为未优化的查询而花费太长时间，或者表大小太大，或者您有垃圾邮件数据库）。
回答by Murad Ali
It looks like queries stuck on the server. Restart and everything will be ok. If you are on shared hosting, just contact your hosting provider they will fix it. I'm namecheap user and they solved it.
看起来查询卡在服务器上。重新启动，一切都会好的。如果您使用的是共享主机，只需联系您的主机提供商，他们会修复它。我是 namecheap 用户，他们解决了它。
