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


Leave a Comment:


Search

Categories

android x 1
apache x 1
apple x 4
backup x 2
cygwin x 1
dns x 1
dos x 1
drupal x 3
ftp x 1
iis x 1
imap x 1
linux x 22
mail x 4
mysql x 3
oracle x 1
pop3 x 1
print x 2
proxy x 1
rsync x 1
shell x 8
smtp x 1
squid x 1
ssh x 1
storage x 2
web x 3
windows x 15

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!