mysql problem with lot of tables

written by Admin

Posted on 2015-04-22

Problem

mysqldump cannot dump a database with hundreds of tables. It returns: "Got error: 1016: Can't open file: '/***/***/***.frm' (errno: 24) when using LOCK TABLES"

Solution

Mysqldump opens ALL tables during LOCK TABLES. When the database contains a large number of tables it may reach the max open files limit.

For a one shot solution you can add --lock-tables=false in the mysqldump command.

# mysqldump --lock-tables=false --opt -u username -p databasename > dump.sql

For a definitive solution add this line in the [mysqld] or [mysqld_safe] section in your my.cnf config file.

[mysqld]

open_files_limit=16384

WARNING: on Windows the open_files_limit variable can be no more than 2048.
See MySQL limitations page for the windows server platform.

Feedback

VOTE THIS TRICK

Comments

Search

Welcome

We need collaboration and a real boost from our community: write us an email for every trick you find!

This website is a sysadmin swiss-knife for every-day Windows, Linux and Mac administration and troubleshooting

Our database grows if YOU send us your tips and trick as soon as you find them every day!