MySQL default maximum number of connections and upper limit for different versions

The morning just work about ten minutes, colleagues said in the use of Jira problems, as follows:

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M00/6F/88/wKiom1WfLDXD_ag0AAX0GXUN_xc840.jpg "title=" The number of database connections is too large. png "alt=" wkiom1wfldxd_ag0aax0gxun_xc840.jpg "/> Error message: Location as MySQL database connection number problem

Workaround:

1. Log in to MySQL to view mysql–uroot–p123456mysql> show variables like '%max_connections% '; +-----------------+-------+| variable_name | Value |+-----------------+-------+| max_connections | 151 |+-----------------+-------+1 row in Set (0.00 sec) It's strange that the maximum number of connections is 151, is MySQL the default maximum number of connections is not 100? Think about it later. This may be a different version of the problem, the default number of connections is also different. To confirm that the mysql5.5.3 default maximum number of connections is 151, go to the MySQL official website to check: MySQL default maximum number of connections is 151, the upper limit is 10002. Modify MySQL By default the maximum number of connections is 1000 in the/etc/my.cnf file [mysqld ] section to add max_connections=1000, restart MySQL service, problem solving.

Supplement 1:MySQL other version default maximum number of connections

Mysql5.5 mysql5.6 mysql5.7: The default maximum number of connections is 151, the upper limit is:100000

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M02/6F/85/wKioL1WfL-mQ12woAACbi5DxTZU099.jpg "title=" Mysql5.5_5.7.png "alt=" Wkiol1wfl-mq12woaacbi5dxtzu099.jpg "/>

Mysql5.1 the default maximum number of connections and the maximum number of modifiable connections varies depending on the iteration.

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M02/6F/85/wKioL1WfMCPTjvf2AAEkEu6cg1k964.jpg "title=" mysql5.1 "alt=" Wkiol1wfmcptjvf2aaekeu6cg1k964.jpg "/>

Mysql5.0 version: The default maximum number of connections is 16384

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M00/6F/85/wKioL1WfMFWhrBbHAACjwjpPK14276.jpg "title=" mysql5.0 "alt=" Wkiol1wfmfwhrbbhaacjwjppk14276.jpg "/>

Add 2: Modify the default maximum number of connections for MySQL database

Method One: Modify the main configuration file of MySQL/etc/my.cnf,[mysqld] section add "max_connections=1000 (this according to the actual need to set up)", restart the MySQL service.

Method Two: MySQL client login, modify the global variables through the command line to modify

mysql-uroot-p123456mysql> Set global_max_connections = 200;mysql> show processlist;mysql> show status; After the modification is completed, the maximum number of connections MySQL mysql> show variables like '%max_connections% '; +-----------------+-------+| variable_name | Value |+-----------------+-------+| max_connections | |+-----------------+-------+1 row in Set (0.00 sec)

Method Three: untie the MySQL source code, enter the inside of the SQL directory modification mysqld.cc found the following line:

　　

{"Max_connections", Opt_max_connections, "the number of simultaneous clients allowed.", (gptr*) &max_connections, (g ptr*) &max_connections, 0, Get_ulong, Required_arg, 100, 1, 16384, 0, 1, 0}, change it to: {"Max_connections", Opt_max_conn Ections, "the number of simultaneous clients allowed.", (gptr*) &max_connections, (gptr*) &max_connections, 0, G Et_ulong, Required_arg, 1500, 1, 16384, 0, 1, 0}, save exit, and then./configure; make;make install can get the same effect

Method Four: Modify the MySQL connection number by modifying the Mysqld_safe

Edit the Mysqld_safe configuration file to find the following: then $NOHUP _niceness $ledir/$MYSQLD $defaults--basedir= $MY _basedir_version--datadir=$ DataDir $USER _option--pid-file= $pid _file--skip-external-locking-o max_connections=1500 >> $err _log 2>& 1 Else eval "$NOHUP _niceness $ledir/$MYSQLD $defaults--basedir= $MY _basedir_version--datadir= $DATADIR $USER _option-- pid-file= $pid _file--skip-external-locking $args-o max_connections=1500 >> $err _log 2>&1 "The Red line represents the field to be added, Save to quit and restart the MySQL service.

Reference article: http://blog.chinaunix.net/uid-20592013-id-94956.html

http://blog.csdn.net/tongle_deng/article/details/6932733

This article is from the Nagios failure blog, so be sure to keep this source http://5250070.blog.51cto.com/5240070/1672803

MySQL default maximum number of connections and upper limit for different versions