由于挖站否免费CN2空间用户增多，近期总是出现半夜Mysql进程挂掉的情况，查询了一下Mysql的错误日志，提示有：[ERROR] Error in accept: Too many open files，原来是因为open files不足导致数据库挂掉了，尤其是晚上DirectAdmin系统备份时更是如此。
Linux系统默认的opens file是1024，可以使用ulimit -a 查看,这个是系统级的限制，另外在mysql中默认的open files也是1024，很多时候这个数值无法满足我们的建站需要，尤其是一个大型的网站和巨大型的数据库中心，很容易就会出现Too many open files问题。
这篇文章就是来分享一下如何解决错误：[ERROR] Error in accept: Too many open files的问题，更多的关于服务器优化以及问题解决方法，参考：
一、数据库问题详情
查看数据库错误日志，如果用的是DirectAdmin面板，相关的日志路径如下：
#DirectAdmin: /var/log/directadmin/error.log /var/log/directadmin/errortaskq.log /var/log/directadmin/system.log /var/log/directadmin/security.log #Apache: /var/log/httpd/error_log /var/log/httpd/access_log /var/log/httpd/suexec_log /var/log/httpd/fpexec_log /var/log/httpd/domains/domain.com.error.log /var/log/httpd/domains/domain.com.log /var/log/messages (generic errors) #Proftpd: /var/log/proftpd/access.log /var/log/proftpd/auth.log /var/log/messages (generic errors) #PureFTPd: /var/log/pureftpd.log #Dovecot and vm-pop3d: /var/log/maillog /var/log/messages #named (bind): /var/log/messages #exim: /var/log/exim/mainlog /var/log/exim/paniclog /var/log/exim/processlog /var/log/exim/rejectlog #(on FreeBSD, they have "exim_" in front of the filenames) #mysqld: #RedHat: /var/lib/mysql/server.hostname.com.err #FreeBSD and Debian: /usr/local/mysql/data/server.hostname.com.err #crond: /var/log/cron #To view a log file, run: less /var/log/filename #Where /var/log/filename is the path of the log you wish to view. If the log is too large you can use the "tail" command: tail -n 30 /var/log/filename #Where 30 is the number of lines from the end you wish to view.
打开Mysql错误日志，一般会提示如下错误：
190902 3:16:52 [ERROR] Error in accept: Too many open files 190902 3:21:08 [ERROR] Error in accept: Too many open files 190902 3:25:24 [ERROR] Error in accept: Too many open files 190902 3:29:40 [ERROR] Error in accept: Too many open files 190902 3:33:56 [ERROR] Error in accept: Too many open files 190902 3:38:12 [ERROR] Error in accept: Too many open files 190902 3:42:28 [ERROR] Error in accept: Too many open files 190902 3:46:44 [ERROR] Error in accept: Too many open files 190902 3:51:00 [ERROR] Error in accept: Too many open files 190902 3:55:16 [ERROR] Error in accept: Too many open files 190902 3:59:32 [ERROR] Error in accept: Too many open files 190902 4:03:48 [ERROR] Error in accept: Too many open files 190902 4:08:04 [ERROR] Error in accept: Too many open files 190902 4:12:20 [ERROR] Error in accept: Too many open files 190902 4:16:37 [ERROR] Error in accept: Too many open files 190902 4:20:53 [ERROR] Error in accept: Too many open files 190902 4:25:09 [ERROR] Error in accept: Too many open files 190902 4:29:25 [ERROR] Error in accept: Too many open files 190902 4:33:41 [ERROR] Error in accept: Too many open files 190902 4:37:57 [ERROR] Error in accept: Too many open files 190902 4:42:13 [ERROR] Error in accept: Too many open files 190902 4:46:29 [ERROR] Error in accept: Too many open files 190902 4:50:45 [ERROR] Error in accept: Too many open files
二、数据库问题分析
2.1 查看限制
使用命令：ulimit -a可以查看到本机的open_files_limit：
[root@sc3 ~]# ulimit -a core file size (blocks, -c) 0 data seg size (kbytes, -d) unlimited scheduling priority (-e) 0 file size (blocks, -f) unlimited pending signals (-i) 15675 max locked memory (kbytes, -l) 64 max memory size (kbytes, -m) unlimited open files (-n) 1024 pipe size (512 bytes, -p) 8 POSIX message queues (bytes, -q) 819200 real-time priority (-r) 0 stack size (kbytes, -s) 8192 cpu time (seconds, -t) unlimited max user processes (-u) 15675 virtual memory (kbytes, -v) unlimited file locks (-x) unlimited
或者使用以下命令仅查看某一项进程的open_files_limit：
#直接查看 open files限制数字 ulimit -n #仅查看soft 和 hard open files limits ： ulimit -Hn ulimit -Sn #仅查看Mysql的open files limits: su mysql ulimit -a
2.2 修改limits.conf
方法一：
你可以使用以下命令来修改/etc/security/limits.conf文件，命令如下：
#To set the ceiling of the limits available (the max a soft or hard limit can be set by each user). #NOTE: this is set by a PAM during authentication, and NOT at boot. #编辑文件 vi /etc/security/limits.conf #Add the following to bottom of file to set for everything *: #添加以下谷贱伤农 * soft nofile 1024000 * hard nofile 1024000 * soft nproc 10240 * hard nproc 10240 #To set only a specific user, like mysql then put in: #或者指定Mysql mysql hard nofile 1024000 mysql soft nofile 1024000 #Repeat for /etc/security/limits.d/90-nproc.conf #同样对90-nproc.conf也是一样的操作 vi /etc/security/limits.d/90-nproc.conf #Add the following: * soft nofile 1024000 * hard nofile 1024000 * soft nproc 10240 * hard nproc 10240 #root soft nproc unlimited #I selected '1024000', which is fairly high; you can surely set this lower to something like '102400'，请根据自己的实际情况来设定数字
或者使用root账号登录，然后使用以下命令临时修改/etc/security/limits.conf：
ulimit -Hn 1024000 #Edit /etc/init.d/mysqld and add this to the top, after #!/bin/sh #或者编辑修改/etc/init.d/mysqld，加入以下代码： ulimit -HSn 1024 ulimit -HSn 32768 ulimit -HSn 1024000
方法二：
打开vi /etc/security/limits.conf文件，在文件里添加一行：* - nofile 32768，这样就把open files调整到了32768。
2.3 修改my.cnf文件
编辑：vi /etc/my.cnf，添加以下代码在 [mysqld]下方：
[mysqld] open_files_limit = 1024000
然后重启Mysql，如下：
/etc/init.d/mysqld restart #或者 service mysqld restart
2.4 查看是否生效
执行以下命令：
mysql -u root -p show global variables like 'open%'; #如果是DirectAdmin面板 mysql -u da_admin -p show global variables like 'open%';
可以查看到open_files_limit已经修改成功了：
三、重启后失效问题
如果你发现系统重启后open files又恢复到了1024，按照以下命令修改配置 :
#One fix: simply restart mysqld after the system boots up.
#最简单的方法再次重启Mysql
service mysqld restart
#另一种方法
#Another fix is to set these values at boot time before everything else (permanent):
vi /etc/init.d/mysqld
#Add the following:
#添加以下内容：
ulimit -S -n ${DAEMON_FILES_LIMIT:-102400} >/dev/null 2>&1
#然后在 /etc/my.cnf 中添加以下内容：
[myqld_safe]
open_files_limit = 102400
#或者是
[myqld]
open_files_limit = 102400四、总结
MySQL打开的文件描述符限制都是Linux操作系统的文件描述符限制，即默认为1024，和Mysql配置文件中open_files_limit的设置没有关系。所以要想修改open_files_limit，必须先修改操作系统的open_files_limit。
当然，如果你不想修改系统设置，也可以使用root帐号，运行mysqld_safe脚本启动MySQL（或者使用mysql.server启动），加上命令：–open-files-limit是可以成功设置的，因为mysqld_safe启动MySQL时，其实是在启动mysqld程序之前，调用了ulimit -n $open_files来实现文件描述符的限制。
./mysqld_safe --open-files-limit=25000 & root@(none) 02:50:54>show variables like "%open_files_limit%"; +------------------+-------+ | Variable_name | Value | +------------------+-------+ | open_files_limit | 25000 | +------------------+-------+