MySQL on W2012R2 backup with VAW2.0

[greg1276]

Hi,

A customer has MySQL 5.6.21 installed on a W2012R2 server.

I am under the impression that a backup with VAW2.0 of a running MySQL instance can result in a corrupt database at recovery.
There seems to be some confusion about this topic.

Is the above statement true or false?
An answer from a Veeam representative would be very much appreciated.

-----

If true, how would you recommend MySQL DB backup with VAW2.0?
(A master/slave configuration is not an option (yet))

For example, Pre-Freeze and Post-Thaw scripts:
- Hot backup: FLUSH TABLES WITH READ LOCK (read only), backup, UNLOCK.
- Hot backup: mysqldump.exe (DB dump), target folder selected in VAW2.0 backup.
- Cold backup: Stop, backup, and start MySQL

Best regards,

Ron

[Dima P.]

Hi Ron,

Cold backup should work for sure. Regarding hot backup, I’ve read that stopping service or flushing tables might work. I’ll check with the team.

[greg1276]

Hi Dmitry,

Much appreciated.

I know I'm being a little pushy here, but is it possible to answer the first question?
Regarding the possibility of an corrupt database at recovery.

Veeam (and of course many other vendors) uses VSS writers for backup.
According to the links below, MySQL does not have it's own writer (like MS SQL or Oracle DB).

https://dev.mysql.com/worklog/task/?id=2881
https://bugs.mysql.com/bug.php?id=35668

Best regards,

Ron

[Mike Resseler]

Hi Ron,

Maybe Dima will give you the official statement.

But look at it from this point of view. When your server suddenly crashes or lost power, what is the possibility of a corrupt database? It is exactly the same.
MySQL indeed does not have a VSS writer so you have a few options to make sure it doesn't get corrupt or that it can recover at boot time.

The cold backup is already said, that will work for sure.
The option with FLUSH Tables works also. A pre-script with FLUSH and POST with UNLOCK. There is a small danger here:
If there is a long running SQL statement in progress, FLUSH... will not finish until that long running SQL statement does. So the VM snapshot will be delayed long time and even possibly time out.
And obviously, the dump will work also which means that in case you recover the entire VM, you can use the dump to restore to that point in case there is corruption.

My personal preference still goes to the dump as an additional protection. Doing this just before the backup should do the trick

[greg1276]

Hi Mike,

Thank you very much for this explanation. It makes sense

I did a restore of a MySQL server last week, and there seems to be no (or very little) corruption of the DB. A DB administrator is using mysqlcheck.exe to make sure.
The backup of the online MySQL server took place after office hours, so not much going on in memory I guess.

I advised the hot backup, DB dump, as additional protection.

Best regards,

Ron