MySQL Virtual Machine Backup Options

[edawg]

Hello-

I am going to be creating a Windows 2003 64-bit vm in an ESX3.5 clustered environment and have some questions about best practices in regards to MySQL backups. I am being told by my vendor I will need to run a script to shut off the MySQL service prior to backing up the database with my current backup option IBM Tivoli. Of course I can only do this at night which means I will only be capturing one backup per day. My requirements for this application are to run backups twice per day.

As a work around I have been looking at using a VCB proxy server with VRangerPro (www.vizioncore.com) to run hot backups. I was told by VRangerPro I could integrate their product with Microsoft's Volume Shadow Service to perform hot backups that would capture all the MySQL data necessary for restores. (data in memory etc)

Does anyone have any thoughts on this type of VRanger workaround or suggestions on how I could put in place a better backup plan that will allow me to run hot backups? I dont' have the option of doing any SAN based snapshots, due to limitations with our current SAN.

Thank you in advance,

Erik

[Texiwill]

Hello,

Are we discussing MSSQL or MySQL? MySQL has a new backup tool that should work named Zmanda (www.zmanda.com).

Best regards,

Edward L. Haletky

VMware Communities User Moderator

====

Author of the book 'VMWare ESX Server in the Enterprise: Planning and Securing Virtualization Servers', Copyright 2008 Pearson Education.

CIO Virtualization Blog: http://www.cio.com/blog/index/topic/168354

As well as the Virtualization Wiki at http://www.astroarch.com/wiki/index.php/Virtualization

[polysulfide]

You have 3 basic options.

1) Purchase a MySQL backup agent for your preferred backup solution and exclude the MySQL database from you file-level backup sets.

2) Have a script stop the MySQL server service before you take a snapshot, then perform a file-level or snapshot backup, then restart the service

3) Export your database as a dump file, exclude your MySQL database from file-level backups and backup the dump files.

2 works well for a snapshot level backup because the database will be ready-to-roll in the event of a full system restore. The downside is that your database is offline during the snapshot or backup operation.

3 works best for system uptime but requires more space and extra steps for recovery.

To dump the database

C:\path\to\Mysql\binaries\mysqldump -h localhost -u rootuser -p"password" MyDatabase E:\Dump\MyDatabase\MyDatabase.mysql

(Recommend zipping this as part of the script)

To stop the service

net stop "Mysql Server" (double check the service name)

Its a little different on Linux but I think you said Windows

If it was useful, give me credit

http://communities.vmware.com/blogs/polysulfide

VI From Concept to Implementation

[edawg]

Thanks for the great info. Regarding option1 do you think VRanger Pro with VCB qualifies as a MySQL backup agent since they state it integrates with VSS to quiesce the database before taking a snapshot, or are you referring to something like www.zmanda.com?

Erik

[polysulfide]

I'm pretty sure that MySQL for Windows doesn't have a VSS writer.

Yes an actual backup agent for MySQL such as the one provided by Amanda would work.

I'm fond of the daily database dumps if you zip and rotate them post-dump, they're quite affordable on diskspace.

If it was useful, give me credit

http://communities.vmware.com/blogs/polysulfide

VI From Concept to Implementation