How to modify the maximum number of connections limit in MySQL

1. Prerequisites

Modifying the database is a high-risk behavior. Before making modifications, it is recommended to use the development and test database for demonstration, and Back up relevant data. To prevent database misoperation, data loss, etc.

Prerequisites for modifying MySQL:

1. If you have an account with administrator rights of a Linux user, you need to modify the configuration questionnaire for MySQL installation;

2. The management account of the MySQL database.

3. If there is operation and maintenance, discuss modifications with the operation and maintenance and development personnel. Data is a major matter to prevent data loss and make a backup of the data.

2. Modification steps

1. Use the root user to log in and connect to the database

# mysql -uroot -p

After entering the command, you are prompted to enter the root user password. After entering the password, it is successful. Log in to the database.

Log in to the MySQL database

2. View the maximum number of connections to the current database

mysql> show variables like 'max_connections';

As shown below, the maximum number of connections to MySQL is only 151 , if you open a few more microservices, you will basically get an exception that there are too many database connections.

Check the maximum number of connections to the database

3. Modify the MySQL configuration file

Find the MySQL configuration file and modify it: /etc/mysql/mysql.conf.d/mysqld.cnf

# view /etc/mysql/mysql.conf.d/mysqld.cnf

Modify the maximum number of connections in the configuration file, max_connections = 2000, save the configuration and exit the editing state .

[mysql]
max_connections = 2000

Modify the maximum number of connections in the configuration file

4. Check the system limit limit

Use the command to query the system file limit

# 查询文件限制
$ ulimit -n
1024

If the system file limit you view is 65535, it means that the system has been modified and does not need to be modified.

5. Modify system file limits

5.1. Add the following to the end of /etc/security/limits.conf Two lines of records, save the configuration after editing

# 编辑系统文件配置文件
$ view /etc/security/limits.conf
 
# 在/etc/security/limits.conf最后增加如下两行记录
* hard nofile 65535
* soft nofile 65535

Finally add the following two lines of records

5.2. Edit /etc/pam. d/common-session, add a line session required pam_limits.so

# 编辑/etc/pam.d/common-session，
# 加入一行 session required pam_limits.so
 
$ view /etc/pam.d/common-session

##Edit configuration file

5.3. Edit /etc/profile, add ulimit -SHn 65535, after saving, Execute the scource command to make the configuration effective, and check the system file restrictions again

# 编辑/etc/profile，加入ulimit -SHn 65535
# 编辑完保存
$ view /etc/profile
 
# 执行scource 命令使配置生效，确认修改是否成功
$ source /etc/profile
 
# 再次查看系统limit限制，看配置文件是否生效
$ ulimit -n
65535

The system configuration file has been Modification successful

6. Modify mysql configuration

Find

mysql.service Start the file

$ systemctl status mysql.service
● mysql.service - MySQL Community Server
 Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)
 Active: active (running) since Wed 2018-12-19 23:53:28 CST; 16min ago
 Process: 25857 ExecStartPost=/usr/share/mysql/mysql-systemd-start post (code=exited, status=0/SUCCESS)
Process: 25843 ExecStartPre=/usr/share/mysql/mysql-systemd-start pre (code=exited, status=0/SUCCESS)
 Main PID: 25856 (mysqld)
  Tasks: 28 (limit: 4915)
 Memory: 185.9M
    CPU: 1.577s
 CGroup: /system.slice/mysql.service
         └─25856 /usr/sbin/mysqld

Enter the directory

/etc/systemd/system/ multi-user.target.wants, find the file mysql.service;

sudo vim mysql.service, add at the end of [Service]:

LimitNOFILE=65535
LimitNPROC=65535

Modify the startup configuration file of mysql

7. Execute the following command to make the modification effective

The configuration file just modified takes effect and restart MySQL. Serve.

$ systemctl daemon-reload
$ systemctl restart mysql.service

8. Log in to mysql, use show variables like "%max_connections%"; to check the maximum number of connections, and then find that it has been changed to 2000.

The above is the detailed content of How to modify the maximum number of connections limit in MySQL. For more information, please follow other related articles on the PHP Chinese website!