"Too Many connections" causes MySQL to crash and start failing

Problem Description:

In the deployment of a new set of environments, the rancher-server on the 14 mirror package upgrade, mainly the micro-service write and query procedures, basically need to connect MySQL program. May be due to the large concurrent connection database, the last several services on the error, Docker container start failure, reported the MySQL error, printed a long string of SQL statements, and finally a "too many connections".
Then log in to MySQL to view the current maximum connections settings and find that MySQL is unable to log in:

[[email protected] ~]# mysql -u root -prootmysql: [Warning] Using a password on the command line interface can be insecure.ERROR 1040 (HY000): Too many connections

At this time also need to connect to this database Rancher-server interface is inaccessible, try to restart MySQL, restart failure:

[[email protected] ~]# systemctl restart mysqldJob for mysqld.service failed because the control process exited with error code. See "systemctl status mysqld.service" and "journalctl -xe" for details.

Perform systemctl status Mysqld.service see the error, found that the step to perform the stop was successful, but start failed, MySQL did not get up:

[[email protected] ~]# systemctl status Mysqld.servicea-mysqld.service-lsb:start and stop MySQL loaded:loaded   (/etc/rc.d/init.d/mysqld; bad; vendor preset:disabled) Active:failed (Result:exit-code) since Mon 2018-08-13 17:27:59 CST; 20s ago Docs:man:systemd-sysv-generator (8) process:30955 execstop=/etc/rc.d/init.d/mysqld Stop (code=exited, status =0/success) process:30992 execstart=/etc/rc.d/init.d/mysqld start (code=exited, status=1/failure) 13 17:27:58 Host12 systemd[1]: Starting Lsb:start and stop MySQL ... 17:27:59 host12 mysqld[30992]: Starting MySQL. error! The server quit without updating PID file (/data/mysqldata/host12.pid). Host12 Systemd[1]: Mysqld.service:control process exited, code=exited Status=1aug 17:27:59 17:27:59 host12 Syst EMD[1]: Failed to start Lsb:start and stop Mysql.aug 17:27:59 host12 systemd[1]: Unit Mysqld.service entered Failed St Ate. 17:27:59 host12 systemd[1]: Mysqld.service failed. [[Email protectEd] ~]# 

Resolution process

Systemctl status Mysqld.service See the information does not mention the number of connections too many problems, but the combination of rancher-server on the Docker container error, should be the current number of connections too many caused.
Since the default value of MySQL maximum connection number is usually 100, the maximum can reach 16384. The maximum number of connections is not manually modified after MySQL has been deployed, and is currently the default state.
Modify the configuration file, in the/etc/my.cnf file added max_connections=10000 this line (because not sure how many connections currently, so a one-time change to compare large test results).

[[email protected] mariadb]# cat /etc/my.cnf[mysqld]datadir=/data/mysqldatasocket=/var/lib/mysql/mysql.sock# Disabling symbolic-links is recommended to prevent assorted security riskssymbolic-links=0max_connections=10000     #加了这一行# Settings user and group are ignored when systemd is used.# If you need to run mysqld under a different user or group,# customize your systemd unit file for mariadb according to the# instructions in http://fedoraproject.org/wiki/Systemd[mysqld_safe]log-error=/var/log/mariadb/mariadb.logpid-file=/var/run/mariadb/mariadb.pid## include all files from the config directory#!includedir /etc/my.cnf.d[[email protected] mariadb]#

Start MySQL again, success.
Then rancher-server access is normal, the above services also start normally.
After startup, the maximum number of connections is changed to 2000 based on the configuration of the existing environment:

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

PostScript: MySQL How to view and modify the maximum number of connections:
1. View the maximum number of connections
Show variables like '%max_connections% ';

2. Modify the maximum number of connections
Method One: Modify the configuration file (for permanent entry).
Add max_connections=1000 to the/etc/my.conf file and restart the MySQL service.

Method Two: Command line modification (temporary effect)
Command line login after MySQL. Set the new MySQL maximum number of connections to 1000:
mysql> set global max_connections=200;
Method two The maximum number of connections set at the command line is only valid in the MySQL current service process, and once MySQL restarts, it will revert to its original state.
Because the initialization after MySQL starts is to read the data from its configuration file, this method does not make changes to its configuration file.

If you cannot restart MySQL immediately, use both methods. After the command line has been modified, the configuration file is modified, and the next time MySQL restarts, it will also take effect.

"Too Many connections" causes MySQL to crash and start failing