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 tablespaces

    When 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:


    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 TABLES

    Checking for fixes