Standalone backup agent for Microsoft Windows servers and workstations (formerly Veeam Endpoint Backup FREE)
Post Reply
greg1276
Novice
Posts: 3
Liked: never
Joined: May 02, 2017 11:00 am
Full Name: Ron Bos
Contact:

MySQL on W2012R2 backup with VAW2.0

Post by 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.
Product Manager
Posts: 14396
Liked: 1568 times
Joined: Feb 04, 2013 2:07 pm
Full Name: Dmitry Popov
Location: Prague
Contact:

Re: MySQL on W2012R2 backup with VAW2.0

Post by Dima P. » 1 person likes this post

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
Novice
Posts: 3
Liked: never
Joined: May 02, 2017 11:00 am
Full Name: Ron Bos
Contact:

Re: MySQL on W2012R2 backup with VAW2.0

Post by 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
Product Manager
Posts: 8044
Liked: 1263 times
Joined: Feb 08, 2013 3:08 pm
Full Name: Mike Resseler
Location: Belgium
Contact:

Re: MySQL on W2012R2 backup with VAW2.0

Post by 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
Novice
Posts: 3
Liked: never
Joined: May 02, 2017 11:00 am
Full Name: Ron Bos
Contact:

Re: MySQL on W2012R2 backup with VAW2.0

Post by 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
Post Reply

Who is online

Users browsing this forum: Bing [Bot] and 40 guests