Upgraded to MySQL 5.6.13 can't backup databases

[ditto]

After upgrading my servers from MySQL 5.5.x to MySQL 5.6.13, on one of my servers with many databases, I am not able to use "Admin Backup/Transfer" to backup/dump all databases anymore.

After dumping around 30% of the databases, it stops with with errors like this:

Error while backing up database USER_NAME

mysqldump error output: mysqldump: Error: 'Out of resources when opening file '/tmp/#sql_390d_2.MYD' (Errcode: 24 - Too many open files)' when trying to dump tablespaces

mysqldump: Couldn't execute 'show fields from `NAME_accesslog`': Out of resources when opening file '/tmp/#sql_390d_0.MYD' (Errcode: 24 - Too many open files) (23)

I have tried to increase open_files_limit from 2046 to be 4096 in my.cnf, like this:

open_files_limit=4096

But it did not help. I did not have this problem in MySQL 5.5.x. Does anybody knows what I can do?

 

[ditto]

I am trying to search for solutions. I found this old forum post: http://forums.mysql.com/read.php?35,7639,262412

There’s two ways to fix the problem. First, if you find that you only hit the limit during mysqldumps and never during normal database operation, just
add --single-transaction to your mysqldump command line options. This will cause mysql to keep only one table open at a time.

So maybe DirectAdmin should add "add --single-transaction to your mysqldump command line options" to the admin/backup transfer for datbases?

But it is strange that I only get this problem i MySQL 5.6.x. Maybe there is some other value I should add and change in my.cnf?

 

[ditto]

Also please see my reply number 3 here: http://forum.directadmin.com/showthread.php?t=47070&p=241798#post241798 - as you can see I got similar error during upgrade to mysql 5.6.13 in custombuild. Could it be that I need to run some upgrade commands again, maybe my upgrade of existing datbases is incomplete and the reason for the problems?

 

[ditto]

I see that on the server with the problem, I have 1024 open files limit:

[root@server1 ~]# 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) 256585
max locked memory       (kbytes, -l) 64
max memory size         (kbytes, -m) unlimited
[B]open files                      (-n) 1024[/B]
pipe size            (512 bytes, -p) 8
POSIX message queues     (bytes, -q) 819200
real-time priority              (-r) 0
stack size              (kbytes, -s) 10240
cpu time               (seconds, -t) unlimited
max user processes              (-u) 256585
virtual memory          (kbytes, -v) unlimited
file locks                      (-x) unlimited
[root@server1 ~]#

But on another server I have 65535 open files limit:

[root@server2 ~]# 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) 256323
max locked memory       (kbytes, -l) 64
max memory size         (kbytes, -m) unlimited
[B]open files                      (-n) 65535[/B]
pipe size            (512 bytes, -p) 8
POSIX message queues     (bytes, -q) 819200
real-time priority              (-r) 0
stack size              (kbytes, -s) 10240
cpu time               (seconds, -t) unlimited
max user processes              (-u) 256323
virtual memory          (kbytes, -v) unlimited
file locks                      (-x) unlimited
[root@server2 ~]#

Is it possible to increase open files to 65535 on "Server1" also? Is it possible to do without doing reboot? I am searching a lot, but have not found a good answer yet. Both servers is running CentOS 6.4 64bit, so I am confused why there is this difference?

 

[ditto]

Regarding possible change to use single-transaction, I now understand I can set this in my.cnf, but after reading this http://www.mysqlperformanceblog.com/2012/03/23/best-kept-mysqldump-secret/ I don't want to do it. So I am back to changing system level "open files" to higer value, as asked in previous reply. I hope someone knows how to do this?

 

[ditto]

L

Ok. I solved the problem. This is how I did it:

I edited /etc/security/limits.conf and added this at the bottom of the file:

*                soft    nofile          65535
*                hard    nofile          65535

Then I edited /etc/my.cnf and added/changed this line:

open_files_limit=65535

Then I restarted MySQL. And now my backup/dump of databases runs and complete without errors. I wonder if 65535 in my.cnf is to extreme high? Even this is a very powerfull server.

So, it seems MySQL 5.6.x needs more open limits files then MySQL 5.5.x, because I never had this problem with MySQL 5.5.x

Also I am confused why one of my CentOS 6.4 64bit server has open files limit set to 65535, but the other server has default to 1024. The server that has 65535 limit, does not have any mention of limit 65535 in /etc/security/limits.conf - maybe this is a default that is changed in recent CentOS 6.x versions, because the server with the higer limit, is a newer server.

Some useful links:

http://dev.mysql.com/doc/refman/5.6/en/server-options.html#option_mysqld_open-files-limit

http://www.centos.org/modules/newbb/viewtopic.php?topic_id=13144

http://duntuk.com/how-raise-ulimit-open-files-and-mysql-openfileslimit