MySql database backups with strftime substitution

Hello Joe

would there be any way to have the webmin/mysql/ script do a strftime substitution on the name of the MySql database backups directory so one could have a rotating backup scheme, the same way you have it set up for the full Virtualmin server backup schedules?

The idea would be that since the databases change all the time and it would be useful to be able to roll them back, one could keep many more generations of database backups than of the full virtual server backups (which take up much more space).

Many thanks!

Hey Robert,

During the incremental backup, although the files are only backed up when they’re changed, the databases are backed up each time.

So if you ran an incremental backup every day, you’d have the databases for any day you need.

Would that do what you’re after?

Hello Eric

what I was hoping for is to be able to keep all the different backup incarnations to be able to roll back a database to any point in the recent past.

With the Virtualmin server backup mechanism, if one uses the weekday in a strftime substitution, one gets the last seven versions before it rolls over.
My backup storage wouldn’t suffice for more, since it backs up all the html files etc.

So I was hoping to find a way of doing the same just for the databases, that way one could, for example, keep the complete versions for each of the last 100 days.

Best regards

Post edited by: Joncas, at: 2008/11/22 22:29<br><br>Post edited by: Joncas, at: 2008/11/22 22:29

Hey Robert,

Just to verify – you’re referring to incremental backups being too large, and not just the full backups, right?

You can choose between either – generally, if your files don’t change much, the incremental would be fairly small.

If you’re saying you’d like another backup option, so that you can choose between full, incremental, and just database, you may want to pop a feature request into the Bugs/Issues tracker to get Jamie’s thoughts.

In the meantime, you could always try the mysqlhotcopy tool (which comes with MySQL these days), it’s a great way to dump MySQL databases if the Virtualmin backup isn’t doing exactly what you want.

Hi Eric

it wasn’t really anything to do with incremental backup, just the option to create a separate schedule for backing up only the databases.

In the Webmin sections -> Servers -> MySql there is a panel for database backups which calls a perl script in /etc/webmin/mysql/

It works fine, but this doesn’t allow one to do any strftime substitution in the backup directory path, so one only has the most recent backup at any time.

My hope would be to find a simple way of enabling this. That way I could do a complete database backup of all the databases every day and keep quite a large number of old versions.

In the meantime I’ll look at the mysqlhotcopy tool you suggest.

Otherwise, I suppose someone smarter than me could write a very simple script that would copy the database backup directory created by Webmin to a different ftp-directory on the backup server every day. I.e., let the Webmin backup script do the database dump and just transfer it to a different tar archive every day to collect all the incarnations.

Many thanks for your help!

Hello again,

I found the answer: in the MySql Server settings the option to turn on strftime substitutions in the backup destination is in a different panel under "Module Config".
With that enabled and, for example %j in the backup path, the backup does just what I wanted to: write a new backup in a different directory every day.

Now the only "feature request" would be to also have the option of sending the backups to an ftp server instead of a local directory!

Best regards