mysqldump

mysqldump |(mysqldump | mysql yields 'too many open files' error. Why?)

我有一个RHEL 5系统，它有一个新的硬盘驱动器，我专门用于MySQL服务器。 为了让事情开始，我使用了“mysqldump --host otherhost -A | mysql”，尽管我注意到manpage从未明确建议尝试这样做（mysqldump到文件中是一个不行的，我们正在谈论500G的数据库）。

这个过程随机失败，抱怨太多的文件被打开（在这一点mysqld获得相关信号，并死亡和重生）。

我尝试了在sysctl和ulimit上使用它，但问题依然存在。 我该怎么做呢？

I have a RHEL 5 system with a fresh new hard drive I just dedicated to the MySQL server. To get things started, I used "mysqldump --host otherhost -A | mysql", even though I noticed the manpage never explicitly recommends trying this (mysqldump into a file is a no-go. We're talking 500G of database).

This process fails at random intervals, complaining that too many files are open (at which point mysqld gets the relevant signal, and dies and respawns).

I tried upping it at sysctl and ulimit, but the problem persists. What do I do about it?

最满意答案

默认情况下，mysqldump执行所有涉及表的每表锁定。 如果有许多表可能超过mysql服务器进程的文件描述符数量。 尝试使用--skip-lock-tables或者如果锁定是强制性的 - lock-all-tables。 http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html

--lock-all-tables, -x

Lock all tables across all databases. This is achieved by acquiring a global read lock for the duration of the whole dump. This option automatically turns off --single-transaction and --lock-tables.

mysqldump by default performs a per-table lock of all involved tables. If you have many tables that can exceed the amount of file descriptors of the mysql server process. Try --skip-lock-tables or if locking is imperative --lock-all-tables. http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html

--lock-all-tables, -x

Lock all tables across all databases. This is achieved by acquiring a global read lock for the duration of the whole dump. This option automatically turns off --single-transaction and --lock-tables.