# mySQL Issue During Backups **URL:** \ **Category:** Virtualmin\ **Created:** [January 7, 2013, 7:57pm UTC](https://forum.virtualmin.com/t/mysql-issue-during-backups/44665 "2013-01-07T19:57:32Z")\ **Posts on this page:** 15\ **Page:** 1
**Author:** ![webwzrd](https://cdn.forum.virtualmin.com/user_avatar/forum.virtualmin.com/webwzrd/32/487_2.png) [@webwzrd](https://forum.virtualmin.com/u/webwzrd)\ **Post date:** [January 7, 2013, 7:57pm UTC](https://forum.virtualmin.com/t/mysql-issue-during-backups/44665/1 "2013-01-07T19:57:32Z")
CentOS 6.3 / VM Pro I just migrated to a new server on Saturday. On both nights since, mySQL stopped responding during the nightly backup and didn’t start again until I did a mySQL restart. The backup of a high percent of virtual servers fail with this error: ``` Dumping MySQL database fxxxxxxxxxr_joomla .. .. dump failed! mysqldump: Error: 'Out of resources when opening file '/tmp/#sql_1332_2.MYI' (Errcode: 24)' when trying to dump tablespaces ``` mysqldump: Couldn’t execute ‘SHOW FUNCTION STATUS WHERE Db = ‘fxxxxxxxxxr\_joomla’’: Out of resources when opening file ‘/tmp/#sql\_1332\_0.MYI’ (Errcode: 24) (23) Do I need to increase some resource to fix? Brian ---
**Author:** ![Eric](https://cdn.forum.virtualmin.com/letter_avatar_proxy/v4/letter/e/898d66/32.png) [@Eric](https://forum.virtualmin.com/u/Eric)\ **Post date:** [January 7, 2013, 8:30pm UTC](https://forum.virtualmin.com/t/mysql-issue-during-backups/44665/2 "2013-01-07T20:30:24Z")
Howdy – MySQL’s error code 24 means that it received a “Too many files open” error. What we would need to do is configure MySQL to allow more open files. One way to do that would be to 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: /etc/init,d/mysql restart That will allow a lot more open files than the default, and should prevent that error that you’re seeing. ---
**Author:** ![webwzrd](https://cdn.forum.virtualmin.com/user_avatar/forum.virtualmin.com/webwzrd/32/487_2.png) [@webwzrd](https://forum.virtualmin.com/u/webwzrd)\ **Post date:** [January 7, 2013, 8:44pm UTC](https://forum.virtualmin.com/t/mysql-issue-during-backups/44665/3 "2013-01-07T20:44:39Z")
Perfect! I knew it would be something like that. Actual the file is “mysqld” I’m going to do a manual backup now and will confirm this fixed it. Thank you, Brian ---
**Author:** ![webwzrd](https://cdn.forum.virtualmin.com/user_avatar/forum.virtualmin.com/webwzrd/32/487_2.png) [@webwzrd](https://forum.virtualmin.com/u/webwzrd)\ **Post date:** [January 7, 2013, 9:47pm UTC](https://forum.virtualmin.com/t/mysql-issue-during-backups/44665/4 "2013-01-07T21:47:16Z")
Bingo - That did it! 210 servers backed up successfully, 0 had errors. 4 Virtualmin configuration settings backed up successfully. Backup is complete. Final size was 19.24 GB. Thank you very much! ---
**Author:** ![webwzrd](https://cdn.forum.virtualmin.com/user_avatar/forum.virtualmin.com/webwzrd/32/487_2.png) [@webwzrd](https://forum.virtualmin.com/u/webwzrd)\ **Post date:** [February 4, 2013, 3:02pm UTC](https://forum.virtualmin.com/t/mysql-issue-during-backups/44665/5 "2013-02-04T15:02:58Z")
Looks like a recent update of mySQL replaced the mysqld file, wiping out my modification. Is there a better way to do this so it is permanent or do I just need to make a mental note to add the extra line anytime I see that mySQL gets updated? Brian ---
**Author:** ![mihha](https://cdn.forum.virtualmin.com/letter_avatar_proxy/v4/letter/m/e480ec/32.png) [@mihha](https://forum.virtualmin.com/u/mihha)\ **Post date:** [March 14, 2013, 11:45am UTC](https://forum.virtualmin.com/t/mysql-issue-during-backups/44665/6 "2013-03-14T11:45:57Z")
The reason why is this happening is because MySQL 5.5 provided an update to the schemas, which are not automatically applied to already existing databases. To provide that schema update, it’s important to run the MySQL upgrade command, as it also helps with missing tables or prefixed tables. Just run this **`mysql_upgrade`** If you’re getting an access denied error, pass along the username + password. **`mysql_upgrade -u root -p`** ---
**Author:** ![yngens](https://cdn.forum.virtualmin.com/letter_avatar_proxy/v4/letter/y/9de053/32.png) [@yngens](https://forum.virtualmin.com/u/yngens)\ **Post date:** [April 13, 2013, 10:12pm UTC](https://forum.virtualmin.com/t/mysql-issue-during-backups/44665/7 "2013-04-13T22:12:35Z")
I just wonder will applying the following line to /etc/my.cnf file will save us from modifications in /etc/init.d/mysql getting replaced with every MySQL update: `open-files-limit = 6144` because my.cnf doesn’t get changed with MySQL updates/upgrades. Is it the same thing or not? ---
**Author:** ![Eric](https://cdn.forum.virtualmin.com/letter_avatar_proxy/v4/letter/e/898d66/32.png) [@Eric](https://forum.virtualmin.com/u/Eric)\ **Post date:** [April 15, 2013, 2:57am UTC](https://forum.virtualmin.com/t/mysql-issue-during-backups/44665/8 "2013-04-15T02:57:44Z")
Well, there may very well be a better way of doing that… and I’m open to suggestions! I’m not thrilled about that method either 🙂 The issue I’ve run into is that in many cases, setting the value in MySQL’s my.cnf won’t help, as the open files limit is actually set elsewhere, and is lower than that. For example, notice the limits for your root user… run “ulimit -a”. Many systems, by default, have a limit of 4096 open files. And whenever your root user restarts MySQL, MySQL will inherit that 4096 limit. So, even if you set MySQL’s “open-files-limit” to a higher number, that won’t always solve the problem – as it can’t be higher than the ulimit value. Feel free to tinker around with that though, if you find a better way to set the open files limit, let us know! -Eric ---
**Author:** ![snlnz](https://cdn.forum.virtualmin.com/user_avatar/forum.virtualmin.com/snlnz/32/14125_2.png) [@snlnz](https://forum.virtualmin.com/u/snlnz)\ **Post date:** [July 4, 2013, 12:43am UTC](https://forum.virtualmin.com/t/mysql-issue-during-backups/44665/9 "2013-07-04T00:43:18Z")
I’m getting a slightly different issue which I thought might have been related: The backup dumps the two user generated databases then shows: Dumping MySQL database performance\_schema … … dump failed! mysqldump: Couldn’t execute ‘show events’: Access denied for user ‘root’@‘localhost’ to database ‘performance\_schema’ (1044) Any ideas as to what might cause this as I can’t see anything weird in the syslog? Thanks in advance. Karu ---
**Author:** ![Eric](https://cdn.forum.virtualmin.com/letter_avatar_proxy/v4/letter/e/898d66/32.png) [@Eric](https://forum.virtualmin.com/u/Eric)\ **Post date:** [July 4, 2013, 4:04am UTC](https://forum.virtualmin.com/t/mysql-issue-during-backups/44665/10 "2013-07-04T04:04:04Z")
We’ve seen that error after upgrading to a non-standard MySQL version… are you by chance using MySQL from a third party repository? -Eric ---
**Author:** ![snlnz](https://cdn.forum.virtualmin.com/user_avatar/forum.virtualmin.com/snlnz/32/14125_2.png) [@snlnz](https://forum.virtualmin.com/u/snlnz)\ **Post date:** [July 4, 2013, 4:41am UTC](https://forum.virtualmin.com/t/mysql-issue-during-backups/44665/11 "2013-07-04T04:41:25Z")
No it was using all the normal virtualmin install scripts for debian that we’ve done so many times in the past. You’ve got me interested to know how you identify if mysql is a standard version or not, can you tell on a running instance? Thanks, Karu ---
**Author:** ![Eric](https://cdn.forum.virtualmin.com/letter_avatar_proxy/v4/letter/e/898d66/32.png) [@Eric](https://forum.virtualmin.com/u/Eric)\ **Post date:** [July 4, 2013, 1:00pm UTC](https://forum.virtualmin.com/t/mysql-issue-during-backups/44665/12 "2013-07-04T13:00:27Z")
What I’d do is compare the package names/versions to what comes standard in Debian. For example, what output do you see if you run this command: `dpkg -l 'mysql*'` Then, if you go to [packages.debian.org](http://packages.debian.org), you can see what the package name and version if for that particular distro/version. -Eric ---
**Author:** ![snlnz](https://cdn.forum.virtualmin.com/user_avatar/forum.virtualmin.com/snlnz/32/14125_2.png) [@snlnz](https://forum.virtualmin.com/u/snlnz)\ **Post date:** [July 4, 2013, 1:06pm UTC](https://forum.virtualmin.com/t/mysql-issue-during-backups/44665/13 "2013-07-04T13:06:26Z")
Everything matches the [debian.org](http://debian.org) repository. 5.5.31+dfsg-0+wheezy1 Hope that helps? ---
**Author:** ![unborn](https://cdn.forum.virtualmin.com/user_avatar/forum.virtualmin.com/unborn/32/23353_2.png) [@unborn](https://forum.virtualmin.com/u/unborn)\ **Post date:** [March 4, 2017, 10:28am UTC](https://forum.virtualmin.com/t/mysql-issue-during-backups/44665/14 "2017-03-04T10:28:42Z")
Hi, thanks for this - that worked for me this morning… last night I’ve done huge update on one of my dev servers running debian where database is around 5gigs and had 24 issue after update. ---
**Author:** ![system](https://cdn.forum.virtualmin.com/uploads/default/original/2X/c/cd96d761e326db4b2bef94886a639109bd7a8e76.svg) [@system](https://forum.virtualmin.com/u/system)\ **Post date:** [April 30, 2020, 5:56am UTC](https://forum.virtualmin.com/t/mysql-issue-during-backups/44665/15 "2020-04-30T05:56:53Z")
Closed on April 30, 2020, 5:56am UTC