MySQL 连接数过多的处理方法合集 - Too many connections - 卡拉云

HiJiangChuan · · 1526 次点击 · · 开始浏览    
这是一个创建于 的文章,其中的信息可能已经有所发展或是发生改变。

![MySQL 连接数过多的处理方法合集](https://kalacloud.com/static/c976e8f8a5cea3670bd67260ac6c3b1c/ef245/head.jpg) 本文首发:[MySQL 连接数过多的处理方法合集 - Too many connections - 卡拉云](https://kalacloud.com/blog/mysql-error-too-many-connections/) 碰到`Can not connect to MySQL server. Too many connections”-mysql`错误着实令人抓狂。这基本等于失去了对 MySQL 的控制权。本教程将详细讲解多种处理此错误的方法。 ``` sudo mysql -uroot -p ERROR 1040 (00000): Too many connections ``` 本教程将分这几个来讲解此类错误的原因。 - 如何查看 MySQL 连接状态? - 如何查看当前 MySQL 连接池是否已满? - 限制超时时间的方法,缩短 sleep 时间,使系统更快回收连接。 - 修改配置文件中最大连接数的方法,保证连接畅通。 - 火线救援法,不用重启,不用登录 MySQL,即可修改最大连接数。 - 提前布局,给 root 预留好连接通道。 ## 一. 错误原因 出现 MySQL 连接数过多有多种情况,多数是因为`mysql_connect` ,没有 `mysql_close`; 当`sleep`连接占满最大连接数`max_connections`时,会导致 `Too many connections` 错误。 MySQL 默认最大连接数`max_connections`为 151,其实 MySQL 还给 root 留了多一个通道,真正的最大连接数为`max_connections + 1` 。但实际工作中因为各种原因,这个 1 也有可能被占用。这时,我们无法通过登录 MySQL 调整参数的方法来处理这个错误。 ## 二. 查看当前 MySQL 连接情况 我们可以使用 `SHOW PROCESSLIST;` 查看前 100 条连接。 ``` SHOW PROCESSLIST; ``` 也可以使用 `SHOW full PROCESSLIST;` 查看所有连接。 ``` SHOW full PROCESSLIST; ``` ![查看 MySQL 中所有连接](https://kalacloud.com/static/48d5709f4b174f8987d6c3f9a994a2f3/b8471/01-PROCESSLIST.png) 上图中 ID 15 的连接 我们可以看到它已经 11388s 扩展阅读:《[如何使用 MySQL 慢查询日志进行性能优化](https://kalacloud.com/blog/how-to-use-mysql-slow-query-log-profiling-mysqldumpslow/)》 ## 三. 如何查看当前 MySQL 连接池是否已满? 使用 `mysqladmin -u kalacloud -p status` 查看当前连接数情况 ![使用 mysqladmin status 查看当前连接数](https://kalacloud.com/static/451216c4ac760a26ef6725c06ddf81f2/a878e/02-mysqladmin-status.png) 将 `kalacloud` 替换为你的 MySQL 账号名称,在返回的结果中,`Threads` 的值为当前连接数,如果当前连接数接近或等于最大连接数,那么就说明 MySQL 连接数已经满了或接近满了。 扩展阅读:《[MySQL 触发器的创建、使用、查看、删除教程及应用场景实战案例](https://kalacloud.com/blog/how-to-manage-and-use-mysql-database-triggers/)》 ## 四. 合理设置超时时间 之所以会出现大量 `sleep` 占满连接,除了业务量的原因外,也有可以从超时时间着手调整,可根据实际情况适当缩短超时时间,让 MySQL 可在短时间自动清理超时连接,以达到保证连接通常的目的。 `mysqld` 连接超时参数有以下两个: - [interactive_timeout](https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_interactive_timeout) - [wait_timeout](https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_wait_timeout) 默认情况下,两者都是 28800 秒(8 小时),我们可以在 MySQL 配置文件中修改这两个参数。 如果你使用的是`mysql_pconnect` 这种持久连接的话,可以将超时时间降到更合适的值,比如 600 (10 分钟)甚至 60(1 分钟)。这个超时时间并没有一个明确的时间,主要还是要看你的应用场景中的实际需求。 ### **1.在配置文件中修改超时时间(需重启 MySQL 生效):** 首先打开 `mysqld.cnf` 配置文件。 ``` sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf ``` 然后在配置文件中找到这两行,并修改对应的参数: ``` [mysqld] interactive_timeout=60 wait_timeout=60 ``` 扩展阅读:《[MySQL 配置文件详解](https://kalacloud.com/blog/how-to-edit-mysql-configuration-file-my-cnf-ini/)》 ### 2.临时变更连接超时时间(无需重启): ``` SET GLOBAL interactive_timeout = 60; SET GLOBAL wait_timeout = 60; ``` 注意: - 这是临时变更配置的方法,在重启 MySQL 后会恢复配置文件中的设置值。 - 对于已经打开的连接,是不会被关闭的。只有新建立的连接才会在 60 秒后关闭。 扩展阅读:《[如何远程连接 MySQL 数据库,阿里云腾讯云允许远程连接教程](https://kalacloud.com/blog/how-to-allow-remote-access-to-mysql/)》 ## 五. 查看及修改最大连接数 在 MySQL 中,默认连接数为 151,我们可以通过修改 MySQL 配置文件永久调整连接数参数,也可以通过 SQL 命令临时调整。 ### 1.查看当前 MySQL 连接数 ``` mysql> show variables like '%max_connections%'; +-----------------+-------+ | Variable_name | Value | +-----------------+-------+ | max_connections | 151 | +-----------------+-------+ 1 row in set (0.01 sec) mysql> ``` ### 2.临时调整当前 MySQL 连接数 ``` set GLOBAL max_connections = 300; ``` ![MySQL 配置文件调整最大连接数](https://kalacloud.com/static/f395ea423d14c3231482cd357c373819/fe9e8/03-max-connections.png) ### 3.通过修改 MySQL 配置文件调整最大连接数 首先打开 MySQL 配置文件: ``` sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf ``` 在 [mysqld] 下面找到 `max_connections` ,如果没有可直接添加。 ``` [mysqld] ... max_connections = 300 ... ``` 修改后重启 MySQL,使配置文件生效: ``` sudo systemctl restart mysql ``` 重启后,进入 MySQL ,我们可以看到最大连接数配置已经生效。 ``` mysql> show variables like '%max_connections%'; +------------------------+-------+ | Variable_name | Value | +------------------------+-------+ | max_connections | 300 | | mysqlx_max_connections | 100 | +------------------------+-------+ 2 rows in set (0.02 sec) mysql> ``` 扩展阅读:《[MySQL 配置文件 my.cnf / my.ini 逐行详解](https://kalacloud.com/blog/how-to-edit-mysql-configuration-file-my-cnf-ini/)》 ## 六. 无法登录 MySQL 时,如何修改最大连接数 在无法登录又无法重启 MySQL 时,我们可以使用以下方法进行操作,以增大连接数。 ### 1.修改 pid 增大连接数 我们可以使用 gdb 工具,在不进入数据库的情况下,修改最大连接数。 ``` gdb -p $(cat data/kalacloud.pid) -ex "set max_connections=5000" -batch ``` - `data/kalacloud.pid`:将这里修改为你服务器中 `pid` 的文件路径及文件名。 此方法仅适用于特殊、紧急情况,在生产环境使用,有一定风险,慎用。 ## 七. 提前布局,防患于未然 在 MySQL 配置文件中,有两个有关连接数的参数 - `max_connections`:控制最大连接数。 - `max_user_connections`:控制单个用户的最大连接数。当此参数为 100 时,那么任意用户(含 root 用户)最多可创建 100 个连接。 **制定连接策略:** ``` max_connections = 2000 max_user_connections= 300 ``` 当 MySQL 有 6 个用户时(不含 root ),单个用户最大连接数为 300,那么 6 个用户最多有 1800 连接。那么系统总会剩下 200 个连接留给 root 使用。 ## 六. 总结 有关 MySQL 连接数过多的错误,我们要在平时的工作中多实践,这里的很多关键参数都需要我们对手中的工作有更宏观的认识,才能更好的设定这些参数。 ![无需懂前端,快速搭建企业内部工具](https://kalacloud.com/5400a60956e16d655e0297c5d6e5a8d2/98-kalacloud-gif.gif) 最后推荐以下卡拉云,卡拉云是一套低代码开发工具,可一键接入包括 MySQL 在内的常见数据库及 API,无需懂任何前端,只需要简单拖拽,即可快速搭建企业内部工具。数月的开发工作量,使用卡拉云后可缩减至数天,免费试用[卡拉云](https://kalacloud.com/)。 ![卡拉云可一键接入市面上常见的数据库及 API](https://kalacloud.com/static/18822b2a23183deb7d11dd484a7f65aa/71c1d/97-kalacloud-sql.png) 卡拉云可一键接入市面上常见的数据库及 API 有关 MySQL 教程,可继续拓展学习: - [MySQL 时间戳用什么类型 - MySQL 时间函数详解](https://kalacloud.com/blog/difference-between-mysql-datetime-and-timestamp-datatypes/) - [MySQL 中如何实现 BLOB 数据类型的存取,BLOB 有哪些应用场景?](https://kalacloud.com/blog/how-to-use-the-mysql-blob-data-type-to-store-images-with-php-or-kalacloud/) - [如何在 MySQL / MariaDB 中跳过多张表导出或指定多张表导出备份](https://kalacloud.com/blog/how-to-dump-database-and-ignore-or-specific-some-tables-with-mysqldump-in-mysql/) - [如何将 MySQL / MariaDB 的查询结果保存到文件](https://kalacloud.com/blog/how-to-save-mysql-mariadb-query-output-to-a-file/) - [MySQL 触发器使用教程 - 创建、删除、查看](https://kalacloud.com/blog/how-to-manage-and-use-mysql-database-triggers/)

有疑问加站长微信联系(非本文作者))

入群交流(和以上内容无关):加入Go大咖交流群,或添加微信:liuxiaoyan-s 备注:入群;或加QQ群:692541889

1526 次点击  
加入收藏 微博
1 回复  |  直到 2021-12-07 21:46:29
暂无回复
添加一条新回复 (您需要 登录 后才能回复 没有账号 ?)
  • 请尽量让自己的回复能够对别人有帮助
  • 支持 Markdown 格式, **粗体**、~~删除线~~、`单行代码`
  • 支持 @ 本站用户;支持表情(输入 : 提示),见 Emoji cheat sheet
  • 图片支持拖拽、截图粘贴等方式上传