mysql connection最大连接_修改mysql最大连接数

1.在/etc/my.conf配置文件添加内容max_connections=1000

```shell

# For advice on how to change settings please see

# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html

[mysqld]

#

# Remove leading # and set to the amount of RAM for the most important data

# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.

# innodb_buffer_pool_size = 128M

#

# Remove leading # to turn on a very important data integrity option: logging

# changes to the binary log between backups.

# log_bin

#

# Remove leading # to set options mainly useful for reporting servers.

# The server defaults are faster for transactions and fast SELECTs.

# Adjust sizes as needed, experiment to find the optimal values.

# join_buffer_size = 128M

# sort_buffer_size = 2M

# read_rnd_buffer_size = 2M

datadir=/var/lib/mysql

socket=/var/lib/mysql/mysql.sock

# Disabling symbolic-links is recommended to prevent assorted security risks

symbolic-links=0

##设置最大连接数

max_connections=1000

# Recommended in standard MySQL setup

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

[mysqld_safe]

log-error=/var/log/mysqld.log

pid-file=/var/run/mysqld/mysqld.pid

```

2.重启mysql服务,发现并没有修改成功

```shell

[root@iZbp1je77jss3x67qq75nuZ ~]# systemctl daemon-reload

[root@iZbp1je77jss3x67qq75nuZ ~]# systemctl restart mysqld

[root@iZbp1je77jss3x67qq75nuZ ~]# mysql -uroot -p123456

Warning: Using a password on the command line interface can be insecure.

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 2

Server version: 5.6.48 MySQL Community Server (GPL)

Copyright (c) 2000, 2020, 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 | 214 |

+-----------------+-------+

1 row in set (0.00 sec)

```

3.更改 MySQL 在 Linux 的最大文件描述符限制，最后两行即为添加的内容

```shell

[root@iZbp1je77jss3x67qq75nuZ etc]# cat /usr/lib/systemd/system/mysqld.service

#

# Simple MySQL systemd service file

#

# systemd supports lots of fancy features, look here (and linked docs) for a full list:

# http://www.freedesktop.org/software/systemd/man/systemd.exec.html

#

# Note: this file ( /usr/lib/systemd/system/mysql.service )

# will be overwritten on package upgrade, please copy the file to

#

# /etc/systemd/system/mysql.service

#

# to make needed changes.

#

# systemd-delta can be used to check differences between the two mysql.service files.

#

[Unit]

Description=MySQL Community Server

After=network.target

After=syslog.target

[Install]

WantedBy=multi-user.target

Alias=mysql.service

[Service]

User=mysql

Group=mysql

# Execute pre and post scripts as root

PermissionsStartOnly=true

# Needed to create system tables etc.

ExecStartPre=/usr/bin/mysql-systemd-start pre

# Start main service

ExecStart=/usr/bin/mysqld_safe --basedir=/usr

# Don't signal startup success before a ping works

ExecStartPost=/usr/bin/mysql-systemd-start post

# Give up if ping don't get an answer

TimeoutSec=600

Restart=always

PrivateTmp=false

##下面两行是添加的内容

LimitNOFILE=65535

LimitNPROC=65535

```

4.重新加载配置，重启mysql

```shell

[root@iZbp1je77jss3x67qq75nuZ etc]# systemctl daemon-reload

[root@iZbp1je77jss3x67qq75nuZ etc]# systemctl restart mysqld

[root@iZbp1je77jss3x67qq75nuZ etc]# mysql -uroot -p123456

Warning: Using a password on the command line interface can be insecure.

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 92

Server version: 5.6.48 MySQL Community Server (GPL)

Copyright (c) 2000, 2020, 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 | 1000 |

+-----------------+-------+

1 row in set (0.00 sec)

mysql>

```

修改成功!!!