Interviewer: What is the default maximum number of connections for MySQL? How to modify?

Just about 10 minutes after work in the morning, my colleague said that there was a problem when using jira. The specific screenshot is as follows:

Through the error message in the figure above: locate the problem of the number of mysql database connections.

Solution

1. Log in to mysql to view

Mysql –uroot –p123456
mysql> show variables like'%max_connections%';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 151  |
+-----------------+-------+
1 row in set (0.00 sec)

It's very strange, why is the maximum number of connections 151? Isn't the default maximum number of connections for mysql 100?

Later, I thought about the problem of different versions, and the default number of connections is also different. In order to confirm that the default maximum number of connections for mysql5.5.3 is 151, I went to the mysql official website to check: the default maximum number of connections for mysql is 151, and the upper limit is 1000. In addition, I have sorted out the MySQL series of interview questions and answers. Pay attention to the technical road of the public account migrant worker, and you can check it in the enterprise interview question column.

2. Modify mysql's default maximum number of connections to 1000

In the /etc/my.cnf file (requires restarting the mysql service)

[mysqld]部分增加
max_connections=1000

The default maximum number of connections for other versions of mysql

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

Mysql 5.1 differs according to its minor version, the default maximum number of connections and the upper limit of the number of connections that can be modified are also different.

Modify the default maximum number of connections for the mysql database

method one

Modify mysql's main configuration file /etc/my.cnf

[mysqld]部分添加
“max_connections=1000（这个根据实际的需要来进行设置即可）”

Restart MySQL service

Method Two

mysql client login, modify the global variables through the command line to modify

mysql -uroot -p123456
mysql> set global_max_connections = 200;
mysql> show processlist;
mysql> show status;

Check after the modification, the maximum number of mysql connections

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

Method Three

Unzip the mysql source code, enter the SQL directory inside and modify mysqld.cc to find the following line:

{"max_connections", OPT_MAX_CONNECTIONS,
  "The number of simultaneous clients allowed.", (gptr*) &max_connections,
  (gptr*) &max_connections, 0, GET_ULONG, REQUIRED_ARG, 100, 1, 16384, 0, 1,
  0},
  把它改为：
  {"max_connections", OPT_MAX_CONNECTIONS,
  "The number of simultaneous clients allowed.", (gptr*) &max_connections,
  (gptr*) &max_connections, 0, GET_ULONG, REQUIRED_ARG, 1500, 1, 16384, 0, 1,
  0},

Save and exit, and then ./configure ;make;make install can get the same effect

Method Four

Modify the number of mysql connections by modifying mysqld_safe

Edit the mysqld_safe configuration file and 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"

Save and exit and restart the mysql service.

Author: mohan87821000
Source: https://blog.51cto.com/5250070/1672803