Exclude some database tables from backups

I know Eric said there is no such option on https://www.virtualmin.com/node/25989, but it was two years ago, so maybe we can think this through and implement as a new feature.

The problem is that Drupal websites that we host always have huge cache tables, which make backups very slow and often cause errors. If only Virtualmin backups could give end users a possbility to exclude huge cache tables in the website's databases, then it would be so much relief to lot's of Virtualmin servers.

I understand that when backing up multiple domains with different database structures this could be a challenging task. On the other hand it seems so easy to feed an exclusion filed in form "wildcard-anyword-wildcard" to the backup script to address this issue.

EDIT: At least running OPTIMIZE for each database table prior backing up would help to improve performance of all Virtualmin servers.

What do you think, Jamie?

Status: 
Active

Comments

Sure, this could be done - I will add it to my TODO list.

This has been implemented, for inclusion in the 5.0 Virtualmin release.

Thanks Jamie. Could you please elaborate what exactly will be implemented in 5.0 - exclusion option or just OPTIMIZE command?

Exclusion of selected DBs and tables.

Exclusion of selected DBs and tables.

Great! Any ETA for 5.0?

And it is so inconvenient to have an issue fixed when we can't really see/test it. It would be very nice if issues would get marked only after their fixes are publicly released like its done on many other GPL projects.

We're unfortunately not sure on the next Virtualmin release... we had honestly hoped it would have been earlier this month, but some other things are holding it up.

It should be out within a few weeks, though maybe sooner :-)

Any updates on this? How exactly we can exclude certain database tables from backups?

There is very good suggestion to this problem on http://www.vmdoh.com/blog/drupal-cacheform-table-eating-disk-space-heres... involving Percona Toolkit command:

pt-online-schema-change --host 127.0.0.1 --port 3306 --user bender --alter "ENGINE=InnoDB" D=database,t=cache_form --execute

Maybe Webmin could have integrated Percona Toolkit module and then Virtualmin could run the above command for each individual website before starting backup.

Another possibility to expand the functionality of the "Command to run before backup" command under (for some reason) the "Schedule and reporting" section to be able to run commands for individual virtual servers. In that case we could trigger some MySQL command like, for example, DELETE FROM cache_form before backing up the website.

This feature was in the 5.0 release of VIrtualmin. You can enter databases and tables to exclude on the "Excluded Directories and DBs" page.

I was looking at the "Edit Scheduled Backup" page. So where is the "Excluded Directories and DBs" page?

On the left menu, under "Administration Options"

Excellent!

It is clear with "Databases and tables to skip", because there is an explanation there: On each line, enter a database name or database.table name., but it is not for "Directories under home to skip" option. What should be the format for this field? Does it allow individual subdirectories like, for example:

logs
public_html/directory-to-exclude

What the "home" in "Directories under home to skip" stand for - the /home directory or the home directory for a user /home/username? I mean should we include the full path like:

/home/username/logs
/home/username/public_html/directory-to-exclude

or should we omit the /home/username part?

All the paths are relative to the domain's home dir, so you can enter things like public_html/somedir