Nowadays, most engineering structures are microservices , Each service generally has its own DB library , Yes MySQL The number of connections required is also increasing . If locally installed test data , No, right MySQL Modify the default number of connections , When testing , Open more micro Services , You may encounter the problem of too many database connections . Yes MySQl It is necessary to modify the default maximum number of connections .

One 、 Prerequisite

Modifying the database is a dangerous action , Before modification, take the database developed and tested as an example , Backup the data . In case of misoperation of the database , Lost data, etc .

modify MySQL The premise of ：

1、 Yes Linux User's administrator privilege account , Need to be right MySQL Modify the installed configuration questionnaire ;

2、MySQL Database management account .

3、 If there is a problem , Discuss with the person in charge of operation and maintenance and development to modify , Data is a big deal , In case of data loss , Backup the data .

Two 、 Modification steps

1、 Use root Users log in and connect to the database

# mysql -uroot -p

After typing the command , prompt root User password , After entering the password , Successfully logged in to the database .

Sign in MySQL database

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

mysql> show variables like 'max_connections';

  Here's the picture ,MySQL The maximum number of connections is 151, Open a few more micro Services , The exception of too many database connections will be reported .

View the maximum number of connections to the database

3、 modify MySQL The configuration file

find MySQL And modify ：/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, After modifying, 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 file limit of the system

#  Query file limit 
$ ulimit -n
1024

If you view The system file limit is 65535, It indicates that the system has been modified , No need to modify .

5、 Modify the system file limit

5.1、 stay /etc/security/limits.conf Finally, add the following two lines of records , Save the configuration after editing

#  Edit system profile 
$ view /etc/security/limits.conf

#  stay /etc/security/limits.conf Finally, add the following two lines of records 
* hard nofile 65535
* soft nofile 65535

Finally, add the following two lines of records

5.2、 edit /etc/pam.d/common-session, Join a row  session required pam_limits.so

#  edit /etc/pam.d/common-session,
#  Join a row  session required pam_limits.so

$ view /etc/pam.d/common-session

Edit profile

5.3、 edit /etc/profile, Join in ulimit -SHn 65535, After the save , perform scource The command enables the configuration to take effect , Check the system file limit again

#  edit /etc/profile, Join in ulimit -SHn 65535
#  Save after editing 
$ view /etc/profile

#  perform scource  The command enables the configuration to take effect , Confirm whether the modification is successful 
$ source /etc/profile

#  Check the system again limit Limit , See if the configuration file is effective 
$ ulimit -n
65535

The system configuration file has been modified successfully
 

6、 modify mysql To configure

lookup mysql.service Startup 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

Entry directory  /etc/systemd/system/multi-user.target.wants, file found mysql.service;

sudo vim mysql.service, stay [Service] Finally add ：

LimitNOFILE=65535
LimitNPROC=65535

modify mysql The startup profile for

7、 Execute the following command to make the modification effective

Take effect of the configuration file just modified , Restart MySQL service .

$ systemctl daemon-reload
$ systemctl restart mysql.service

8、 Sign in mysql, Use  show variables like "%max_connections%";  See the maximum number of connections , At this time, it was found that it had been changed to 2000.

The maximum number of connections has been modified successfully

3、 ... and 、 Conclusion

thus MySQL The maximum number of connections has been modified , In the development process , Try to make rational use of the number of connections to the database .