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.
But on CentOS 7.0 and MySQL 5.6.0 above suggestions not fix my problem finally.
I check open_files_limit and nothing change
mysql> show global variables like "%open_files_limit%" ; +------------------+-------+ | Variable_name | Value | +------------------+-------+ | open_files_limit | 1024 | +------------------+-------+ 1 row in set (0.00 sec) |
Edit /etc/security/limits.conf and add the following lines
mysql soft nofile 65535 mysql hard nofile 65535 |
Then reboot.
Then edit /usr/lib/systemd/system/mysqld.service or /usr/lib/systemd/system/mariadb.service and add
LimitNOFILE=infinity LimitMEMLOCK=infinity |
Then run
systemctl daemon-reload |
Then restart the db service:
systemctl reload mysql.service |
Already check open_files_limit
mysql> show global variables like "%open_files_limit%" ; +------------------+-------+ | Variable_name | Value | +------------------+-------+ | open_files_limit | 65536 | +------------------+-------+ 1 row in set (0.00 sec) |
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://unix.stackexchange.com/a/157910
http://dba.stackexchange.com/a/86988
http://dev.mysql.com/doc/refman/5.6/…en-files-limit
http://www.centos.org/modules/newbb/…topic_id=13144
http://duntuk.com/how-raise-ulimit-o…openfileslimit