Mysql error: Too many connections solution

MySQL database too many connections

This error obviously occurs when you forget to call mysql_close after mysql_connect.

After a large number of connections, there will be a Too many connections error. The default number of connections for MySQL is 100. Under what circumstances will this error occur?

After a normal mysql_connect, call mysql_close() to close the connection.

However, in case of a connection error, or when mysql_real_query() exits with an error, you may forget to call mysql_close();

Therefore, before the program returns, you must determine whether to close(). The safest way is to have only one exit when writing any function!

You can also increase the number of allowed connections by modifying the MySQL configuration file!

Sometimes your server often has such errors:

The error message is as follows:

Can not connect to MySQL server

Error: Too many connections

Errno.: 1040

Similar error report has been dispatched to administrator before.

From the official documentation, we know that the default number of connections for MySQL compiled and installed on Linux is 100.

Documentation: http://dev.mysql.com/doc/refman/5.0/en/too-many-connections.html

MySQL officially tells us that we need to modify the value of max_connections, so how do we modify it? There are two ways

1. Modify the configuration file

Modify the /etc/my.cnf file and add max_connections=N in [mysqld]. If you do not have this file, copy the *.cnf file you need from the support-files folder in the compiled source code to /etc/my.cnf. I am using my-medium.cnf, the medium server configuration. For example, the content of my [mysqld] is as follows

[mysqld]

port = 3306

socket = /tmp/mysql.sock

skip-locking

key_buffer = 160M

max_allowed_packet = 1M

table_cache = 64

sort_buffer_size = 512K

net_buffer_length = 8K

read_buffer_size = 256K

read_rnd_buffer_size = 512K

myisam_sort_buffer_size = 8M

max_connections=1000

Since I am not very familiar with MySQL, many parameters have not been modified. Ha ha. .

2. Users who do not use the mysqld script to automatically start.

Modify the MYSQLHOME/bin/mysqldsafe file. For example, grep −n'maxconnection' in this file /usr/local/mysql/bin/mysqldsafe.

MYSQLHOME/bin/mysqldsafe file For example: /usr/local/mysql/bin/mysqldsafe This file grep −n'maxconnection′MYSQL_HOME/bin/mysqld_safe

Modify the max_connections parameter value of the corresponding row number

3. Server login mysql: mysql -u root -p

Ninety percent of the time, you cannot enter. If you cannot enter, execute the restart command: /etc/init.d/mysql restart (centos system)

At this time, restart mysql to connect to mysql. If there is still time, you can proceed to the next step.

Open the configuration file and add the following configuration: vi /etc/my.cnf

wait_timeout = 600

interactive_timeout = 600

Restart mysql again

Principle answer

MySQL defaults to 100 connections. If it exceeds the limit, it will not be able to connect. The actual number of working connections is far less than 100. Most of them are in sleep mode.

So either increase the number of connections or kill useless connections. The latter is recommended.

Summarize

The above is the full content of this article. I hope that the content of this article will have certain reference learning value for your study or work. Thank you for your support of 123WORDPRESS.COM.