5

We are running a production MySQL v5.5 database server on a Windows 2008 R2 VM. We are currently just performing a daily mysqldump of the database, and then backing those up. Our backup solution (Veeam) supports Hyper-V and uses VSS to create a consistent snapshot of the VMs. Is this a good backup method? Is this the best backup method? If we had to restore this VM, would we experience any database corruption? Should we continue to perform daily mysqldumps and keep those backed up separately? Thanks!

Improve this question
2
  • Does veeam do a consistent file level backup or database backup? I think you would have to tell veeam to do a lock all tables before the snapshot to get a consistent backup.
    – Jure1873
    May 5, 2012 at 20:06
  • Same question, only a few years old serverfault.com/questions/206784/…
    – Bret Fisher
    May 6, 2012 at 2:09

2 Answers 2

Reset to default
2

A backup is not only a disaster recovery solution, but it can also be an archive.

For the disaster recovery part you are fine with snapshots of your VMs. But imagine that you'd just like to recover a single table or a single record from your database, or you would like to see how a record in the database has evolved over time. In such a case, you would probably be much better off with a directory full of dumps of your databases.

I can therefore recommend to not only rely on the snapshots, but to also keep the dumps somewhere.

Improve this answer
3

Hyper-V does VSS snapshots of VM's, and if those OS's have the integration services installed, it will notify the internal OS to also prepare for a snapshot. That VM OS then tells any apps that have VSS providers to flush to disk (known as Quiescing). So the cool thing is the VSS notification trickles down from Hyper-V->VM->Apps in VM. The bad thing is it only helps for apps that are VSS aware. VSS has in been in Windows Server since 2003, and is widely supported on Microsoft products but not so much in 3rd party.

So it seems MySQL doesn't have native VSS support but there is a third party tool according to this.

If it was me, I'd set MySQL to do DB and Log backups to local disk (frequently, maybe even every 15min depending on your Recovery Point Objectives) and still do the Hyper-V snapshots to capture those files. In a recovery scenario if you have any data loss you can recover from those rather then only have the online (open files) MySQL DB's and Logs. Note that I'm not a MySQL expert :)

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .