Magento Backup Tool killed the database

Question

If I click on System > Tools > Backups create a database backup and restore it the shop breaks. Looks like the backup is canceled in the middle.

Could this be a PHP timeout problem or is this done by a cronjob? Is this know not to work probably?

Update

Looks like this is a MySQL problem. Any idea what config value might cause this?

MySQL config

connect_timeout=120
delayed_insert_timeout=3000
innodb_lock_wait_timeout=600
interactive_timeout=28800
net_read_timeout=600
net_write_timeout=600
slave_net_timeout=3600
table_lock_wait_timeout=600
wait_timeout=18000

From what I can see in backup source code a lot of queries are running to create the dumpfile instead of a simple mysqldump...

Update 2

I increased wat_timeout with success by adding into app/code/core/Mage/Backup/Model/Resource/Db.php

$this->_write->query('SET session wait_timeout = 115200;');

But I noticed I get a 500 Internal Server Error

/index.php/admin/system_backup/create/key/a74b4...7978/?isAjax=true

with post data: type=db&maintenance_mode=0&backup_name=test&exclude_media=0&form_key=uPK54mu...

Answer 1

Most likely a timeout of some type was encountered, be it either a MySQL or PHP timeout.

You are correct with the assuming the backup is triggered via cron:

I would double check MySQL's timeout and logs as PHP's timeout is set to 0 to avoid such issues when the cron job is executed:

The best and safest route is doing manual backups via CLI, with mysqldump, and recommended over the backup tool within the admin.

n98-magerun has some nifty backup utilities as well via CLI worth checing out:

• https://github.com/netz98/n98-magerun

Also for those running Enterprise 1.13, the backup tool does NOT include TRIGGERS (MySQL dump will however).

• https://github.com/molotovbliss/Envalo_Backup
• https://gist.github.com/molotovbliss/c176f2140232a0ba556f

Answer 2

I finally managed to find the correct php error log on this stupid server:

mod_fcgid: read data timeout in 61 seconds

Solution is to increase FcgidIOTimeout and FcgidBusyTimeout massively. As this process runs very slow (multiple hours in this case) and the store is not loading during this time this is not a good option for me. If you have a small store it might be OK for you but I go with B00MERs answer and trigger a mysqldump from my own script.