Large database backup times out

[Jozsef]

Can I reduce the database size or break up the backup somehow?

The site's database grew over 100Mb having over 1M rows in total.
There are over 150k pages (high number of payments, orders and over 30k users).

I use the great CronjobDatabaseBackup module by kixe to trigger hourly updates it uses the core WireDatabaseBackup class.
My backup now triggers a PHP error every time, despite max_execution_time is set to 300 on the shared hosting. I think they enforce a maximum of 120 seconds on the database though.
Fatal Error: Maximum execution time of 120 seconds exceeded 
Line 854 of /wire/core/WireDatabaseBackup.php
or
Fatal Error: Maximum execution time of 120 seconds exceeded 
Line 961 of /wire/core/WireDatabasePDO.php

[Robin S]

5 hours ago, Jozsef said:

The site's database grew over 100Mb having over 1M rows in total.
There are over 150k pages (high number of payments, orders and over 30k users).

First, it's probably best not to try and host such a site on shared hosting. If the site is that popular the client should be able to afford better hosting.

You could try using mysqldump in a cron job and see if that works. Example:

mysqldump -u'YOUR_DATABASE_USERNAME' -p'YOUR_DATABASE_PASSWORD' YOUR_DATABASE_NAME > /home/YOUR_ACCOUNT/public_html/site/assets/backups/database/YOUR_DATABASE_NAME_`date +\%Y\%m\%d_\%H\%M`.sql

 

[flydev]

As @Robin S suggest, you can try mysqldump in a script, it might be available from the admin panel, or you can also try the module Duplicator, I just updated the master version - you can try the web-mode or the native-mode (which use mysqldump) to see if it works. In the past we got quite good results on limited shared hosts.

[Jozsef]

9 hours ago, Robin S said:

First, it's probably best not to try and host such a site on shared hosting. If the site is that popular the client should be able to afford better hosting.

You could try using mysqldump in a cron job and see if that works.

Thanks Robin, I didn't know I can use mysqldump directly in a cron job.
As for the site, they are debs (prom) organisers for high schools, hence the large number of customers and ticket sales on the site.
Otherwise the site is performant and works well within the shared hosting limits. As far as shared hosting goes it's a really good one with guaranteed resources.

23 minutes ago, flydev ?? said:

As @Robin S suggest, you can try mysqldump in a script, it might be available from the admin panel, or you can also try the module Duplicator, I just updated the master version - you can try the web-mode or the native-mode (which use mysqldump) to see if it works. In the past we got quite good results on limited shared hosts.

Thanks, I always wanted to check out your module, I'll give it a try.

[aagd]

found this, didn't test: