How to backup Oracle / MySQL

Availability for the Always-On Enterprise

How to backup Oracle / MySQL

Veeam Logoby remko.de.koning » Tue Mar 03, 2015 3:34 pm

Hi guys,

I am looking for some help on how to safely backup an Oracle Database and MySQL database in our Server 2012 R2 HyperV environment.
I would like to start with saying that I am not a database expert! These databases have been installed by the Vendor of an application and till now we have simply been taking regular Veaam backups of these servers.
Today I learned that special treatment is required for non VSS aware applications like Oracle 9i or 10g and MySQL databases.
For the MySQL database I managed to created "Dumps" via a scheduled task periodically so that I have at least something.
Bits and pieces about this subject can be found via Google but for me it is almost impossible to get a complete picture.
There is for example a great whitepaper on how to backup MySQL on a Linux box running on a VMWare host. However, how does one backup a MySQL server running on Windows on a Hyper-V infrastructure?
I am really looking for a step-by-step plan on how to accomplish these two tasks. Keep in mind that I am a system administrator and not a DBA.
I have found the option to trigger Pre-Freeze and Post-thaw scripts in the Application-Aware Processing Options of the Veeam Backup Job. Are there any example scripts on how to do this.
What do I need to put where?

Goals:
[*]Backup MySQL on a Windows Machine (2008 R2) on a Windows Server 2012 Hyper-V Host
[*]Backup Oracle 10g on a Windows Machine (2008 R2) on a Windows Server 2012 Hyper-V Host

Any help will be greatly appreciated.

As a side note:
I have tested a restore of a MySQL database on a windows server via SureBackup and it seems to be okay but from what I am reading I guess I am just lucky.

Thanks for any thoughts you might have and your patience with me :wink:
remko.de.koning
Enthusiast
 
Posts: 72
Liked: 13 times
Joined: Wed May 21, 2014 12:15 pm
Full Name: Remko de Koning

Re: How to backup Oracle / MySQL

Veeam Logoby v.Eremin » Wed Mar 04, 2015 1:14 pm

There is for example a great whitepaper on how to backup MySQL on a Linux box running on a VMWare host. However, how does one backup a MySQL server running on Windows on a Hyper-V infrastructure?

You can use similar approach with the one exception - instead of VMware scripts you can put pre-freeze and post scripts that can be found in the advanced application processing settings.

What do I need to put where?

The script examples have been provided on this forum several times, so, feel free to use advanced search in order to find them.

Thanks.
v.Eremin
Veeam Software
 
Posts: 13266
Liked: 968 times
Joined: Fri Oct 26, 2012 3:28 pm
Full Name: Vladimir Eremin

Re: How to backup Oracle / MySQL

Veeam Logoby jlester » Wed Mar 04, 2015 7:36 pm

For MySQL, we just do a dump before the Veeam job starts. For Oracle, we do a data pump backup before the Veeam job starts. So, our actual backup is of the file that the dump and data pump wrote out.
jlester
Enthusiast
 
Posts: 43
Liked: 4 times
Joined: Tue Mar 23, 2010 1:59 pm
Full Name: Jason Lester

Re: How to backup Oracle / MySQL

Veeam Logoby remko.de.koning » Thu Mar 05, 2015 7:58 am

Yesterday I was able to successfully start & stop the MySQL service with pre and post scripts. The whole shutdown of the service, snapshots of the machine and restart of the service took 50 seconds.
I guess this is acceptable downtime for this specific webapplication with the MySQL backend.
Will try to focus now on Oracle. I have less expertise with this database.
What is recommended for Oracle? Create a dump file prior to the backup or put the database in a different mode? I understood that there is a hot-backup mode. What are pro's and con's? Would this be a good idea?
remko.de.koning
Enthusiast
 
Posts: 72
Liked: 13 times
Joined: Wed May 21, 2014 12:15 pm
Full Name: Remko de Koning

Re: How to backup Oracle / MySQL

Veeam Logoby v.Eremin » Thu Mar 05, 2015 8:52 am

What is recommended for Oracle?

Stop db service prior to taking snapshot and release it afterwards. This topic might be of some help.
v.Eremin
Veeam Software
 
Posts: 13266
Liked: 968 times
Joined: Fri Oct 26, 2012 3:28 pm
Full Name: Vladimir Eremin

Re: How to backup Oracle / MySQL

Veeam Logoby remko.de.koning » Thu Mar 05, 2015 1:31 pm

Hi guys, thanks all for your feedback. After reading many posts and watching this webinar (https://www.youtube.com/watch?v=j49MfsnkVWQ) am I correct by saying that there are three ways to backup Oracle
1) Shutdown with pre script, snapshots and restart database
2) Putting Oracle in hot-backup mode. With VM backups it only stays in this mode for a couple of seconds so there is not much data replay via de redo logs.
3) Do Nothing. Simply take a crash-consistant backup. As if you would pull the power plug. From what I understand this works fairly well.

Solution 1 is the most safe I guess. I need to check if we can simply shutdown the database for a short period. Solution 3 sounds a bit scary but from what I understand the change of recovery is pretty good. (is there a change of failure?)
If solution 1 is not possible I guess I need to explore option 2.

At least I have some food for thought now. Again. Thanks all for your thoughts on this.
remko.de.koning
Enthusiast
 
Posts: 72
Liked: 13 times
Joined: Wed May 21, 2014 12:15 pm
Full Name: Remko de Koning

Re: How to backup Oracle / MySQL

Veeam Logoby vmniels » Thu Mar 05, 2015 1:43 pm

remko.de.koning wrote:Yesterday I was able to successfully start & stop the MySQL service with pre and post scripts. The whole shutdown of the service, snapshots of the machine and restart of the service took 50 seconds.
I guess this is acceptable downtime for this specific webapplication with the MySQL backend.
Will try to focus now on Oracle. I have less expertise with this database.
What is recommended for Oracle? Create a dump file prior to the backup or put the database in a different mode? I understood that there is a hot-backup mode. What are pro's and con's? Would this be a good idea?



You don't have to stop the database in case of MySQL. Mysqldump can be done while the database is running.

Also instead of using mysqldump you could also use FLUSH TABLES WITH READ LOCK but this only works with InnoDB Tables.
VCP-DCV
Veeam Certified Engineer
http://foonet.be
vmniels
Veeam Software
 
Posts: 1505
Liked: 325 times
Joined: Mon Jul 15, 2013 11:09 am
Full Name: Niels Engelen

Re: How to backup Oracle / MySQL

Veeam Logoby remko.de.koning » Thu Mar 05, 2015 2:37 pm

You don't have to stop the database in case of MySQL. Mysqldump can be done while the database is running.

So basically the real Veeam backup has a inconsistant database which can be corrected afterwards (if needed) by importing the dump. Do I understand this correctly?

Also instead of using mysqldump you could also use FLUSH TABLES WITH READ LOCK but this only works with InnoDB Tables.

Lost me :D :D :D :D
remko.de.koning
Enthusiast
 
Posts: 72
Liked: 13 times
Joined: Wed May 21, 2014 12:15 pm
Full Name: Remko de Koning

Re: How to backup Oracle / MySQL

Veeam Logoby vmniels » Thu Mar 05, 2015 3:56 pm

remko.de.koning wrote:
You don't have to stop the database in case of MySQL. Mysqldump can be done while the database is running.

So basically the real Veeam backup has a inconsistant database which can be corrected afterwards (if needed) by importing the dump. Do I understand this correctly?

Also instead of using mysqldump you could also use FLUSH TABLES WITH READ LOCK but this only works with InnoDB Tables.

Lost me :D :D :D :D


Mysqldump works in that way that with recent versions a mysqldump is more then enough without stopping the database. MySQL imports will correct stuff if needed (but from my experience this happens rarely). The idea of using the scripts and stopping the database is an extra security check and as you mentioned works fine for small databases (1 minute of being unavailable is acceptable depending on the application).

On big databases mysqldump on the other hand might cause (big) issues (and if you stop the database that also means it takes longer to restart it) as the process requires some time. Besides that u need to provide additional storage to store the dump and you need extra I/O to perform the operation.
An alternate solution is using the command "FLUSH TABLES WITH READ LOCK", with this the database gets in a readonly state for the duration of the snapshot meaning applications can still read, incoming writes are put into a "on hold list" and performed as soon as possible. :-)
VCP-DCV
Veeam Certified Engineer
http://foonet.be
vmniels
Veeam Software
 
Posts: 1505
Liked: 325 times
Joined: Mon Jul 15, 2013 11:09 am
Full Name: Niels Engelen

[MERGED] : MySQL Back up Strategy using VEEAM

Veeam Logoby mithung » Mon Apr 18, 2016 12:35 am

Hi All,
We are using VEEM to back up OS and application files. Can someone advise what is best way to back up the MySQL database, which can be restored reliably.

Thanks Mithun
mithung
Lurker
 
Posts: 1
Liked: never
Joined: Mon Apr 18, 2016 12:30 am
Full Name: Mithun Ganguly

Re: How to backup Oracle / MySQL

Veeam Logoby v.Eremin » Mon Apr 18, 2016 9:51 am

Your post has been merged into existing discussion talking about similar matter. Kindly, check the answers provided above and ask for additional clarification, if required. Thanks.
v.Eremin
Veeam Software
 
Posts: 13266
Liked: 968 times
Joined: Fri Oct 26, 2012 3:28 pm
Full Name: Vladimir Eremin

[MERGED] backup mysql databases

Veeam Logoby HFX902 » Fri Sep 30, 2016 3:11 pm

We are looking at being able to backup mysql databases with veeam... The data is stored on RDM storage devices.

This means that the databases would no longer be part of the VM itself. So no VMDK file but a iscsi LUN.

Does veeam offer any backup solution's for what we are trying to achieve?

Thanks
HFX902
Influencer
 
Posts: 13
Liked: never
Joined: Thu Oct 01, 2015 8:13 pm
Full Name: ksmith

Re: How to backup Oracle / MySQL

Veeam Logoby foggy » Fri Sep 30, 2016 4:10 pm

Please review this thread for the general approach. However, keep in mind, that physical RDM disks as well as disks connected via in-guest iSCSI are not supported.

A couple of topics more:

mysql consistent backup
Database backups - pre/post etc
foggy
Veeam Software
 
Posts: 14742
Liked: 1079 times
Joined: Mon Jul 11, 2011 10:22 am
Full Name: Alexander Fogelson


Return to Veeam Backup & Replication



Who is online

Users browsing this forum: Bing [Bot], Yahoo [Bot] and 21 guests