MySQL on W2012R2 backup with VAW2.0

Backup agent for Microsoft Windows servers and workstations (formerly Veeam Endpoint Backup FREE)

MySQL on W2012R2 backup with VAW2.0

Veeam Logoby greg1276 » Wed Jul 12, 2017 11:19 am

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

Re: MySQL on W2012R2 backup with VAW2.0

Veeam Logoby Dima P. » Wed Jul 12, 2017 7:27 pm 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.
Dima P.
Veeam Software
 
Posts: 6229
Liked: 439 times
Joined: Mon Feb 04, 2013 2:07 pm
Location: SPb
Full Name: Dmitry Popov

Re: MySQL on W2012R2 backup with VAW2.0

Veeam Logoby greg1276 » Thu Jul 13, 2017 7:41 pm

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

Re: MySQL on W2012R2 backup with VAW2.0

Veeam Logoby Mike Resseler » Fri Jul 14, 2017 5:37 am

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
Mike Resseler
Veeam Software
 
Posts: 3150
Liked: 362 times
Joined: Fri Feb 08, 2013 3:08 pm
Location: Belgium, the land of the fries, the beer, the chocolate and the diamonds...
Full Name: Mike Resseler

Re: MySQL on W2012R2 backup with VAW2.0

Veeam Logoby greg1276 » Mon Jul 17, 2017 10:25 am

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


Return to Veeam Agent for Windows



Who is online

Users browsing this forum: dcheung and 4 guests