MySQL study--"Too many connections" error solution

Source: Internet
Author: User
Tags percona percona server

MySQL study--"Too many connections" error solution

The error message is as follows :
Can not connect to MySQL server

Error:too Many connections
Errno.: 1040

Similar Error Report have beed dispatched to administrator before.

The following is a description of the mysql.com website:
If You get a Too many connections error when you try to connect to the MYSQLD server, this means that all avail Able connections is in use by and other clients.
The number of connections allowed is controlled by the max_connections system variable. Its default value is. If you need to support more connections, you should restart mysqld with a larger value for this variable.
mysqld actually allows max_connections+1 clients to connect. The extra connection is reserved for use by accounts and that has the SUPER privilege. By granting the SUPER privilege to administrators and not to normal users (who should not need it), an administrator can C Onnect to the server and useSHOW processlist to diagnose problems even if the maximum number of unprivileged Clie NTS is connected. See section 13.5.4.19, "SHOW processlist Syntax".
The maximum number of connections MySQL can support depends on the quality of the thread library on a given plat Form. Linux or Solaris should is able to the support 500-1000 simultaneous connections, depending on how much RAM are you having and what Your clients is doing. Static Linux binaries provided by MySQL AB can support up to 4000connections.

Workaround:


1. Modify the MY.CNF configuration file

[Email protected] ~]# CAT/ETC/MY.CNF
[Mysqld]
Datadir=/var/lib/mysql
Socket=/var/lib/mysql/mysql.sock
User=mysql
# Disabling Symbolic-links is recommended to prevent assorted security risks
Symbolic-links=0
Innodb_data_file_path=ibdata1:12m;ibdata2:10m:autoextend
Sql_mode=strict_trans_tables, No_engine_substitution
Slow_query_log=true
Slow_query_log_file = "/var/lib/mysql/rh64-slow.log"
Long_query_time=1
Log-queries-not-using-indexes=true
max_connections=1000

2. Restart MySQL server

[[Email protected] ~]# service MySQL restart
Shutting down MySQL (Percona Server): [OK]
Starting MySQL (Percona Server). [OK]
[Email protected] ~]# mysql-u root-p
Enter Password:
Welcome to the MySQL Monitor. Commands End With; or \g.
Your MySQL Connection ID is 1
Server Version:5.6.25-73.1-log Percona Server (GPL), Release 73.1, Revision 07b797f
Copyright (c) 2009-2015 Percona LLC and/or its affiliates
Copyright (c), Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of the Oracle Corporation and/or its
Affiliates. Other names trademarks of their respective
Owners.
Type ' help ', ' or ' \h ' for help. Type ' \c ' to clear the current input statement.

mysql> Show variables like '%connect% ';

+-----------------------------------------------+-----------------+| variable_name                                   | Value            |+-----------------------------------------------+-----------------+ | character_set_connection                       | utf8             | |  collation_connection                           | utf8_general_ci | |  connect_timeout              &nbsP;                | 10               | |  disconnect_on_expired_password                 | ON               | |  extra_max_connections                          | 1                | |  init_connect                                    |                 | |  max_connect_errors                             | 100              | |  max_connections                                |  1000            | |  max_user_connections                           | 0                | |  performance_schema_session_connect_attrs_size | 512               |+-----------------------------------------------+-----------------+10 rows  in set  (0.02 SEC)


mysql> show processlist;
+----+------+-----------+------+---------+------+-------+------------------+-----------+---------------+
| Id | User | Host      | DB   | Command | Time | State | Info             | rows_sent | rows_examined |
+----+------+-----------+------+---------+------+-------+------------------+-----------+---------------+
|  1 | Root | localhost | NULL | Query   |    0 | Init  | Show Processlist |         0 |             0 |
+----+------+-----------+------+---------+------+-------+------------------+-----------+---------------+
1 row in Set (0.00 sec)

Mysql> Kill ID//query out the process ID

The final way to solve the problem is to summarize:

1. Modify the configuration file/etc/my.cnf, adjust the connection parameters

2. Check the program code and close the link without closing it in time


This article is from the "Tianya blog," Please make sure to keep this source http://tiany.blog.51cto.com/513694/1725955

MySQL study--"Too many connections" error solution

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.