当前位置： 首页 > article >正文

MySQL数据库最大连接数查询及修改

article 2024/11/16 17:49:31

MySQL数据库最大连接数查询及修改

1. 客户端连接数超出异常案例

Navicat连接异常信息如下：

在这里插入图片描述

2. 查看MySQL最大客户端连接数

通过mysql client命令登录MySQL数据库（登录用户不受限制，既可以是 root管理员用户，也可以是常规用户），执行如下命令 show variables like 'max_connections' 查看当前MySQL数据库最大连接数。

查询示例

dbuser普通账户查询：

Microsoft Windows [版本 10.0.19045.5131]
(c) Microsoft Corporation。保留所有权利。

C:\Users\kalami>mysql -h127.0.0.1 -udbuser -pdbuser
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.1.70-community-log MySQL Community Server (GPL)

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show variables like 'max_connections'
    -> ;
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 1100  |
+-----------------+-------+
1 row in set (0.00 sec)

mysql>

root管理员账户查询：

C:\Users\kalami>mysql -h127.0.0.1 -uroot -proot123
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.1.70-community-log MySQL Community Server (GPL)

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>
mysql> show variables like 'max_connections';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 1100  |
+-----------------+-------+
1 row in set (0.00 sec)

mysql>

3. 查看MySQL当前客户端连接数

【1】. 查看当前连接总数

方案一

MySQL数据库服务为每个mysql客户端连接都会创建一个独立线程去处理相关CRUD操作。通过查看MySQL数据库当前的连接线程数就可以知道当前有多少个有效客户端连接到数据库。

show status like 'Threads_connected';

示例如下：

C:\Users\kalami>mysql -h127.0.0.1 -uroot -proot123
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.1.70-community-log MySQL Community Server (GPL)

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>
mysql> show status like 'Threads_connected';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Threads_connected | 2     |
+-------------------+-------+
1 row in set (0.00 sec)

mysql>

方案二

通过查询 information_schema 中的processlist 数量获取当前连接数。查询SQL 命令如下：

select count(*) from information_schema.processlist;

示例如下：

C:\Users\kalami>mysql -h127.0.0.1 -uroot -proot123
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 5.1.70-community-log MySQL Community Server (GPL)

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select count(*) from information_schema.processlist;
+----------+
| count(*) |
+----------+
|        1 |
+----------+
1 row in set (0.03 sec)

mysql>

【2】. 查看详细连接信息

通过 show processlist 命令查看当前所有客户端连接详细信息。

mysql> show processlist;
+----+--------+----------------------+--------+---------+------+-------+------------------+
| Id | User   | Host                 | db     | Command | Time | State | Info             |
+----+--------+----------------------+--------+---------+------+-------+------------------+
|  4 | dbuser | 192.168.58.145:35192 | NULL   | Sleep   |  595 |       | NULL             |
|  6 | root   | 127.0.0.1:60982      | NULL   | Query   |    0 | NULL  | show processlist |
|  7 | root   | 192.168.58.145:34854 | NULL   | Sleep   |   70 |       | NULL             |
|  8 | dbuser | 192.168.58.151:49687 | paradb | Sleep   |    8 |       | NULL             |
+----+--------+----------------------+--------+---------+------+-------+------------------+
4 rows in set (0.00 sec)

mysql>

示例如下：

C:\Users\kalami>mysql -h127.0.0.1 -uroot -proot123
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.1.70-community-log MySQL Community Server (GPL)

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>
mysql> show processlist;
+----+--------+----------------------+--------+---------+------+-------+------------------+
| Id | User   | Host                 | db     | Command | Time | State | Info             |
+----+--------+----------------------+--------+---------+------+-------+------------------+
|  4 | dbuser | 192.168.58.145:35192 | NULL   | Sleep   |  595 |       | NULL             |
|  6 | root   | 127.0.0.1:60982      | NULL   | Query   |    0 | NULL  | show processlist |
|  7 | root   | 192.168.58.145:34854 | NULL   | Sleep   |   70 |       | NULL             |
|  8 | dbuser | 192.168.58.151:49687 | paradb | Sleep   |    8 |       | NULL             |
+----+--------+----------------------+--------+---------+------+-------+------------------+
4 rows in set (0.00 sec)

mysql>

4. 修改MySQL最大客户端连接数

如果想修改MySQL数据库最大客户端连接数，则需要修改mysql数据库配置文件my.ini或者 my.cnf；通过设置[mysqld]主题下的max_connections进行设置；设置完后，需要重启下mysql数据库服务。

【1】. Windows操作系统

步骤一

my.ini 配置文件中设置最大连接数。

在这里插入图片描述

步骤二

重启MySQL服务，以管理员运行CMD，并执行如下命令。

#停止MySQL服务
net stop mysql
#启动MySQL服务
net start mysql

示例如下：

Microsoft Windows [版本 10.0.19045.5131]
(c) Microsoft Corporation。保留所有权利。

C:\Windows\system32>net stop mysql
MySQL 服务正在停止..
MySQL 服务已成功停止。


C:\Windows\system32>net start mysql
MySQL 服务正在启动 .
MySQL 服务已经启动成功。


C:\Windows\system32>

步骤三

查看修改结果。

在这里插入图片描述

【2】. RedHat操作系统

查询默认（未设置）的最大连接数。

在这里插入图片描述

步骤一

设置 /etc/my.cnf配置文件，增加 max_connections=2048参数。

在这里插入图片描述

步骤二

重启MySQL服务。

service mysqld stop
service mysqld start

在这里插入图片描述

步骤三

再次查询当前的最大连接数。

在这里插入图片描述

【3】. Solaris操作系统

步骤一

solaris重启服务命令

在Solaris操作系统中，重新启动服务通常涉及使用svcadm命令。以下是几个常用的svcadm命令，用于管理服务。

  • svcadm命令
# svcadm
用法：svcadm [-v] [命令 [参数 ...]]

        svcadm enable [-rst] <服务> ...  - 启用服务并使服务联机
        svcadm disable [-st] <服务> ...  - 禁用服务并使服务脱机
        svcadm restart <服务> ...                - 重新启动指定的服务
        svcadm refresh <服务> ...                - 重新读取服务配置
        svcadm mark [-It] <状态> <服务> ...       - 设置维护状态
        svcadm clear <服务> ...          - 清除维护状态
        svcadm milestone [-d] <里程碑>  - 进入服务里程碑

        可以使用 FMRI、缩写、或 fnmatch(5) 模式指定
        服务，svc:/network/smtp:sendmail 的示例如下所示：

        svcadm <命令> svc:/network/smtp:sendmail
        svcadm <命令> network/smtp:sendmail
        svcadm <命令> network/*mail
        svcadm <命令> network/smtp
        svcadm <命令> smtp:sendmail
        svcadm <命令> smtp
        svcadm <命令> sendmail
#
  • 查看服务
svcs <service_name>
  • 停止服务
svcadm disable <service_name>
  • 启动服务
svcadm enable <service_name>
  • 重启服务
svcadm restart <service_name>

步骤二

修改 my.cnf配置文件，我的Solaris上的MySQL安装在/usr/local/mysql下，所以其配置文件在 /usr/local/mysql/my.cnf位置。

# Example MySQL config file for very large systems.
#
# This is for a large system with memory of 1G-2G where the system runs mainly
# MySQL.
#
# MySQL programs look for option files in a set of
# locations which depend on the deployment platform.
# You can copy this option file to one of those
# locations. For information about these locations, see:
# http://dev.mysql.com/doc/mysql/en/option-files.html
#
# In this file, you can use all long options that a program supports.
# If you want to know which options a program supports, run the program
# with the "--help" option.

# The following options will be passed to all MySQL clients
[client]
#password       = your_password
port            = 3306
socket          = /tmp/mysql.sock

# Here follows entries for some specific programs

# The MySQL server
[mysqld]
port            = 3306
socket          = /tmp/mysql.sock
skip-locking
key_buffer_size = 384M
max_allowed_packet = 1M
max_connections=2000
table_open_cache = 512
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size = 32M
# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 8

# Don't listen on a TCP/IP port at all. This can be a security enhancement,
# if all processes that need to connect to mysqld run on the same host.
# All interaction with mysqld must be made via Unix sockets or named pipes.
# Note that using this option without enabling named pipes on Windows
# (via the "enable-named-pipe" option) will render mysqld useless!
#
#skip-networking

# Replication Master Server (default)
# binary logging is required for replication
log-bin=mysql-bin

# required unique id between 1 and 2^32 - 1
# defaults to 1 if master-host is not set
"/usr/local/mysql/my.cnf" 147 行，4703 字符

在这里插入图片描述

启动MySQL数据库：

# pwd
/home/mysql/mysql5.1/bin
# ./mysqld_safe
241115 09:34:34 mysqld_safe Logging to '/usr/local/mysql/data/zone18.err'.
241115 09:34:34 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data

步骤三

查看最大连接数

# ./mysql -uroot -proot123
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.70-log MySQL Community Server (GPL)

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>
mysql>
mysql> show variables like "max_connections";
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 886   |
+-----------------+-------+
1 row in set (0.00 sec)

mysql> show variables like "max_connections";
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    1
Current database: *** NONE ***

+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 2000  |
+-----------------+-------+
1 row in set (0.00 sec)

mysql>

【4】. 麒麟操作系统

步骤一

在麒麟操作系统下，MySQL数据库最大连接数为 151， 且没在 /etc/my.cnf 配置文件中进行明文配置，未在 my.cnf配置文件中进行设置时，默认最大连接数为 151。

查询效果如下：

在这里插入图片描述

步骤二

设置最大连接数，修改 /etc/my.cnf配置文件，设置 max_connections参数。

在这里插入图片描述

步骤三

重启MySQL服务，重启命令如下:

systemctl restart mysqld

在这里插入图片描述

重启完MySQL服务后，再次查询最大连接数是否更新成功。

在这里插入图片描述


http://www.kler.cn/a/396139.html

相关文章：

  • Python读写Excel的全面教程
  • 关于在Reverse函数中不能使用L=s而是*L=*s的原因分析
  • Android Osmdroid + 天地图 （二）
  • 博物馆实景复刻：开启沉浸式文化体验的新篇章
  • SQL面试题——蚂蚁SQL面试题 连续3天减少碳排放量不低于100的用户
  • StarRocks Summit Asia 2024 全部议程公布！
  • R语言贝叶斯分析：INLA 、MCMC混合模型、生存分析肿瘤临床试验、间歇泉喷发时间数据应用|附数据代码...
  • Python实现PSO粒子群优化算法优化CNN-Transformer回归模型(优化权重和阈值)项目实战
  • 开源的说话人分离项目 | 可以对指定的音频分离不同的说话人 | 通话录音中分离不同的说话人
  • 开发中SQL积累
  • 量子奇异值阈值算法
  • vue3: ref, reactive, readonly, shallowReactive
  • Django Form
  • 【计算机网络】TCP网络特点2
  • 理解Go中的append函数及其返回值
  • MFC中Picture Control控件显示照片的几种方式
  • python基础 基本数据类型 执行顺序 条件判断 常用字符串操作 常用工具类
  • 英伟达 Isaac ROS产品体验
  • Java—— 正则表达式
  • 【系统架构设计师】真题论文: 论基于 DSSA 的软件架构设计与应用（包括解题思路和素材）
  • Nacos黑马笔记
  • 前端知识点---this的用法 , this动态绑定(Javascript)
  • 大数据如何助力干部选拔的公正性
  • 宝塔面板从 Nginx 切换到 Caddy：详细教程
  • 麒麟时间同步搭建chrony服务器
  • Python学习从0到1 day26 第三阶段 Spark ④ 数据输出