Need help on mysql backup- Automysqlbackup
-
Hi all,
I am trying to configure automysqlbackup on one of my linux servers. I configured it and executed, after running and taking backup for a while (I've selected to take all db backup), it started throwing errors:
mysqldump: Error: 'Out of resources when opening file '/tmp/#sql_3098_2.MYD' (Errcode: 24)' when trying to dump tablespacesWhen checked on different forums they mentioned to change the value table_open_cache to something like 200 which by default 1024. I checked mine and found that its 400 now. This is a cpanel installed CentOS server with multiple accounts configured.
Could you please help me to solve this?
-
Have never used anything like cPanel. Is there definitely enough space in /tmp for the backup?
-
yes, it does have 2GB free space. This seems like a generic mysqldump issue related to the table_open_cache as per my search results but wanted to get the exact details rather than a temp fix.
-
What should be the optimal value for table_open_cache?
-
On a different forum, saw this solution:
edit /etc/init.d/mysql, and a few lines down, you'll see a line that looks like this:exec="/usr/bin/mysqld_safe"
Right above that line, add a line that looks like this:
ulimit -n 16384
And then restart MySQL:
I couldn't find the entry exec
-
I tried adding open-files-limit=4096 in /etc/my.cnf and restart mysql. Then executed automysqlbackup again, this time got something else.
Errors reported during AutoMySQLBackup execution.. Backup failed
Error log below..
-- Warning: Skipping the data of table mysql.event. Specify the --events option explicitly.
mysqldump: Got error: 1142: SELECT,LOCK TABL command denied to user 'root'@'localhost' for table 'cond_instances' when using LOCK TABLESChecking for fixes
