1、问题描述:连接数据库出现Too Many Connections 错误,
2、问题定位步骤:
1、mysql -u root -p 进入不了,同样出现上述错误。
2、修改/etc/mysql/my.cnf(ubuntu系统,其他系统在/etc/my.cnf
[mysqld]
port=3306
#socket=MySQL
skip-locking
set-variable = key_buffer=16K
set-variable = max_allowed_packet=1M
set-variable = thread_stack=64K
set-variable = table_cache=4
set-variable = sort_buffer=64K
set-variable = net_buffer_length=2K
set-variable = max_connections=1000
3、重启mysql /etc/init.d/mysql restart
续:
2、问题定位步骤:
1、mysql -u root -p 进入不了,同样出现上述错误。
2、修改/etc/mysql/my.cnf(ubuntu系统,其他系统在/etc/my.cnf
[mysqld]
port=3306
#socket=MySQL
skip-locking
set-variable = key_buffer=16K
set-variable = max_allowed_packet=1M
set-variable = thread_stack=64K
set-variable = table_cache=4
set-variable = sort_buffer=64K
set-variable = net_buffer_length=2K
set-variable = max_connections=1000
3、重启mysql /etc/init.d/mysql restart
搞定了。
4、还有一个方法就是直接 /etc/init.d/mysql restart 在做连接操作,呵呵,这个不好。
续:
再次mysql -u root -p 进入系统 ,如下,大部分的连接其实已经Sleep了,那么如何控制释放这些空闲连接,
mysql的默认连接上只有100,也就是说连接数据超过100 就会有可能出现 Too Many Connections
修改my.cnf配置文件添加并需要重启:
[mysqld]
wait_timeout = 600
interactive_timeout = 600
上面的意思是设置10分钟内该连接没有请求就断开,当然你也可以通过kill掉下面的process的。命令是 kill ${id} 比如下面的19684 进程 kill 19684。
mysql> show full processlist;
+-------+----------------------+-----------------------------+------+---------+------+-------+-----------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-------+----------------------+-----------------------------+------+---------+------+-------+-----------------------+
| 19684 | root | AY130121082434a.local:59618 | fo | Sleep | 7028 | | NULL |
| 19691 | root | AY130121082434a.local:59888 | mo | Sleep | 6121 | | NULL |
| 19709 | root | AY130121082434a.local:60339 | fo | Sleep | 7272 | | NULL |
| 19708 | root | AY130121082434a.local:60338 | fo | Sleep | 6672 | | NULL |
| 19710 | root | 222.95.56.226:4883 | fo | Sleep | 7263 | | NULL |
| 19711 | root | 222.95.56.226:4884 | fo | Sleep | 7263 | | NULL |
| 19712 | root | 222.95.56.226:4885 | fo | Sleep | 7264 | | NULL |
| 19713 | root | 222.95.56.226:4886 | fo | Sleep | 7264 | | NULL |
| 19714 | root | 222.95.56.226:4887 | fo | Sleep | 7265 | | NULL |
| 19715 | root | 222.95.56.226:4888 | fo | Sleep | 7261 | | NULL |
| 19716 | root | 222.95.56.226:4889 | fo | Sleep | 7256 | | NULL |
| 19717 | root | 222.95.56.226:4891 | fo | Sleep | 7250 | | NULL |
| 19718 | root | 222.95.56.226:4899 | fo | Sleep | 7246 | | NULL |
| 19719 | root | 222.95.56.226:4900 | fo | Sleep | 3669 | | NULL |
| 19720 | root | 222.95.56.226:4901 | fo | Sleep | 3035 | | NULL |
| 19721 | root | 222.95.56.226:4904 | fo | Sleep | 7231 | | NULL |
| 19722 | root | 222.95.56.226:4909 | fo | Sleep | 7226 | | NULL |
| 19723 | root | 222.95.56.226:4910 | fo | Sleep | 7221 | | NULL |
| 19724 | root | 222.95.56.226:4912 | fo | Sleep | 7216 | | NULL |
| 19725 | root | 222.95.56.226:4913 | fo | Sleep | 7211 | | NULL |
| 19726 | root | 222.95.56.226:4914 | fo | Sleep | 7206 | | NULL |
| 19727 | root | 222.95.56.226:4915 | fo | Sleep | 7200 | | NULL |
| 19728 | root | 222.95.56.226:4916 | fo | Sleep | 3034 | | NULL |
| 19729 | root | 222.95.56.226:4918 | fo | Sleep | 3115 | | NULL |
| 19730 | root | AY130121082434a.local:60622 | mo | Sleep | 6121 | | NULL |
| 19731 | root | AY130121082434a.local:60623 | fo | Sleep | 6672 | | NULL |
| 19732 | root | AY130121082434a.local:60692 | fo | Sleep | 6547 | | NULL |
| 19733 | root | AY130121082434a.local:60693 | fo | Sleep | 6547 | | NULL |
| 19734 | root | AY130121082434a.local:60694 | fo | Sleep | 6305 | | NULL |
| 19735 | root | AY130121082434a.local:60695 | fo | Sleep | 6547 | | NULL |
| 19736 | root | AY130121082434a.local:60791 | mo | Sleep | 5713 | | NULL |
| 19737 | root | AY130121082434a.local:60792 | mo | Sleep | 5713 | | NULL |
| 19738 | root | AY130121082434a.local:60793 | fo | Sleep | 5823 | | NULL |
| 19739 | root | AY130121082434a.local:60794 | fo | Sleep | 5823 | | NULL |
| 19756 | root | AY130121082434a.local:60875 | mo | Sleep | 5249 | | NULL |
| 19755 | root | AY130121082434a.local:60874 | mo | Sleep | 5249 | | NULL |
| 19758 | root | AY130121082434a.local:60877 | fo | Sleep | 5342 | | NULL |
| 19757 | root | AY130121082434a.local:60876 | fo | Sleep | 5647 | | NULL |
| 19759 | root | AY130121082434a.local:60878 | fo | Sleep | 5342 | | NULL |
| 19771 | root | AY130121082434a.local:60923 | mo | Sleep | 4767 | | NULL |
| 19772 | root | AY130121082434a.local:60924 | mo | Sleep | 4767 | | NULL |
| 19773 | root | AY130121082434a.local:60926 | fo | Sleep | 3897 | | NULL |
| 19774 | root | AY130121082434a.local:60927 | fo | Sleep | 3175 | | NULL |
| 19775 | root | AY130121082434a.local:60998 | mo | Sleep | 4454 | | NULL |
| 19776 | root | AY130121082434a.local:60999 | mo | Sleep | 4454 | | NULL |
| 19777 | root | AY130121082434a.local:32913 | mo | Sleep | 4307 | | NULL