InnoDB: Error number 24 means 'Too many open files'.

来源：互联网发布：微信附近人定位软件编辑：程序博客网时间：2024/04/29 19:41

一.问题的描述

备份程序执行前滚的时候报错。(-apply-log)

InnoDB: Errornumber 24 means 'Too many open files'.

InnoDB: Some operatingsystem error numbers are described at

InnoDB: http://dev.mysql.com/doc/mysql/en/Operating_System_error_codes.html

InnoDB: File name/home/nice/backup/mysql/var.20121211021656/user_db_20121123/tuser_like_core.ibd

InnoDB: File operationcall: 'open'.

InnoDB: Cannot continueoperation.

innobackup:Error:

二.问题分析

openfiles 开的小了。

mysql的open_file_limit受制于以下几个参数

max_connections、 table_open_cache 、open_files_limit 以及 /etc/security/limits.conf中的配置。

max_connections和 table_open_cache 与open_files_limit 的关系：

max_1 = 10 + max_connections + table_cache * 2;

max_2 = max_connections * 5;

max_3 = max_os_open_files;//操作系统单个进程最大允许打开文件句柄（文件描述符）。

open_files_limit= max( max_1, max_2 ) > max_3 ? max_3 : max ( max_1, max_2);

综合分析发现，open_files_limit | 10240

而目前ibd表已经10800个了。

三.解决问题

1.my.cnf 配置 open_files_limit = 20480

重启mysql 发现没有变化,open_files_limit | 10240。

2.ulimit -a

core file size (blocks, -c) unlimited
data seg size (kbytes, -d) unlimited
file size (blocks, -f) unlimited
pending signals (-i) 1024
max locked memory (kbytes, -l) 32
max memory size (kbytes, -m) unlimited
open files (-n) 1024
pipe size (512 bytes, -p) 8
POSIX message queues (bytes, -q) 819200
stack size (kbytes, -s) 10240
cpu time (seconds, -t) unlimited
max user processes (-u) 137216
virtual memory (kbytes, -v) unlimited
file locks (-x) unlimited

3.vi /etc/security/limits.conf,添加

mysql soft nofile 8192
mysql hard nofile 20480

4.经过前三步，重启mysql。

重启mysql 发现,open_files_limit | 20480。

5.修改OS用户配置文件

经过前四步，虽然在mysql中看open_files_limit | 20480,但ibbackup还是在前滚时报错。报，open_file不够。怀疑还是没有生效。

ulimit -a

于是，修改OS用户配置文件

分别查看了.bash_profile 、.bashrc 、/etc/bashrc；

发现，.bash_profile调用 .bashrc ，.bashrc调用 /etc/bashrc

发现，/etc/bashrc 中有

ulimit -S -c unlimited > /dev/null 2>&1
在这一行之后添加

ulimit -n 20480 >/dev/null 2>&1

6.重启mysql ,执行备份程序，在--apply-log，还是报错，报，open_file不够

InnoDB: Errornumber 24 means 'Too many open files'.

InnoDB: Some operatingsystem error numbers are described at

InnoDB: http://dev.mysql.com/doc/mysql/en/Operating_System_error_codes.html

7.修改innodb_open_files 参数

之前这个参数，设置成500.这次修改到1000.

重启mysql 解决问题

四.关于Operating_System_error_codes.html

Linux System Error Codes

The following tableprovides a list of some common Linux system error codes. For a more completelist, see Linux source code.

Number

Macro

Description

EPERM

Operation not permitted

ENOENT

No such file or directory

ESRCH

No such process

EINTR

Interrupted system call

EIO

I/O error

ENXIO

No such device or address

E2BIG

Arg list too long

ENOEXEC

Exec format error

EBADF

Bad file number

ECHILD

No child processes

EAGAIN

Try again

ENOMEM

Out of memory

EACCES

Permission denied

EFAULT

Bad address

ENOTBLK

Block device required

EBUSY

Device or resource busy

EEXIST

File exists

EXDEV

Cross-device link

ENODEV

No such device

ENOTDIR

Not a directory

EISDIR

Is a directory

EINVAL

Invalid argument

ENFILE

File table overflow

EMFILE

Too many open files

ENOTTY

Inappropriate ioctl for device

ETXTBSY

Text file busy

EFBIG

File too large

ENOSPC

No space left on device

ESPIPE

Illegal seek

EROFS

Read-only file system

EMLINK

Too many links

声明：本文档可以随意更改，但必须署名原作者

作者：凤凰舞者 qq:578989855