Page 1 of 1

Site crashes during mysqldump

Posted: Tue Aug 06, 2019 4:12 pm
by ilputto
Hello all,

I'm a DBA and I'm asking your help because I'm facing with crashing of a website during execution of mysqldump backup.

The mysql version of database is 5.1, joomla is 3.9.5 . the database engine are both myisam and innodb.
Mysqldump job uses a "--lock-tables" option because our developer asked for a consistent backup (Myisam and Innodb)

What I need to know are:

1)Why website keep crashing during mysqldump?
2)is there a timeout to be set either apache-side or phpside?

thank you in advance for any help

Re: Site crashes during mysqldump

Posted: Tue Aug 06, 2019 6:19 pm
by Per Yngve Berg
The site crashes because the session table is locked. If you use php 7.2/7.3, change session from database to php. You can also exclude this table from backup as it contains only data that will be rebuild when clients are connecting.

PS. Why don't you use Akeeba backup?

Re: Site crashes during mysqldump

Posted: Wed Aug 07, 2019 9:57 am
by ilputto
Hi Per Yngve,
thank you very much for your update.
it's very valuable.
Are there a list of tables I have to exclude from backup? Is there "best practice" contains a list of tables we have to exclude from backup?
Thank you very much
Regards

Re: Site crashes during mysqldump

Posted: Wed Aug 07, 2019 10:02 am
by ilputto
Hi Per Yngve,

Do you know what is the php setting to set to modify the timeout?

Regards

Re: Site crashes during mysqldump

Posted: Sun Aug 11, 2019 12:39 am
by AMurray
The php time out setting is "max_execution_time" - the integer value is the time-out value in seconds. It is set in the php.ini or perhaps in the .htaccess file.

No DB tables "have to be" excluded......most shouldn't be except "session" table as already mentioned.

A suggested folder exclusion would be the default /backup folder in Akeeba itself - which would contain the previous backup archives if you regularly use the component. Of course, you wouldn't leave your archives there, you'd store copies elsewhere....but including them in any subsequent backup would increase the backup file size.

The Akeeba Backup component, allows you to click-and-select the files, folders or database tables to exclude.