记一次MySQL出现too many connection

今天中午的时候突然接到报警,MySQL连接数暴增..本来设置的max_conntion=6000, 这个数值连接已经够用.接到报警速度连接服务器连接mysql总是报如下错误:

[mysql@localhost ~]$ mysql -u xxxxx -pxxxxxxxx
ERROR 1040 (HY000): Too many connections

本来打算kill杀掉重启搞定解决..但是生产库最好不要轻易重启.这里演示一种在线解决连接数满的方式..还记的神器gdb吗?没错就是它 ^_^

查看mysql后台进程
[mysql@localhost ~]$ ps -ef | grep mysql 
root      1549  1281  0 16:21 ?        00:00:00 sshd: mysql [priv]
mysql     1551  1549  0 16:21 ?        00:00:00 sshd: mysql@pts/0
mysql     1552  1551  0 16:21 pts/0    00:00:00 -bash
mysql     1571  1552  0 16:21 pts/0    00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe
mysql     1985  1571  0 16:21 pts/0    00:00:01 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/mysql/data --plugin-dir=/usr/local/mysql/lib/plugin --log-error=/mysql/mysqld.log --pid-file=/mysql/mysqld.pid --socket=/tmp/mysql.sock

使用gdb修改内存中的max_connections值
[mysql@localhost ~]$ gdb -p 1985 -ex "set max_connections=10240" -batch   
[New LWP 2190]
[New LWP 2094]
[New LWP 2070]
[New LWP 2039]
[New LWP 2009]
[New LWP 2007]
[New LWP 2006]
[New LWP 2005]
[New LWP 2004]
[New LWP 2003]
[New LWP 2002]
[New LWP 2001]
[New LWP 2000]
[New LWP 1999]
[New LWP 1998]
[New LWP 1996]
[New LWP 1995]
[New LWP 1994]
[New LWP 1993]
[New LWP 1992]
[New LWP 1991]
[New LWP 1990]
[New LWP 1989]
[New LWP 1988]
[New LWP 1987]
[Thread debugging using libthread_db enabled]
0x00007f5e26613343 in poll () from /lib64/libc.so.6
[mysql@localhost ~]$ 

现在尝试进mysql,查看max_connections值
[mysql@localhost ~]$ mysql -u xxxxx -pxxxxxxx
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 5.6.15-log Source distribution

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

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

OK.^_^终于能进去了.终于恢复正常了..迅速批量杀掉sleep进程..一般情况下报too many connection应该是程序那边出问题了.暂时解决方案扩大max_connections解决.原因等开发回来一起查吧.(坏的真不是时候,我的中午饭啊.......-_-可怜)