How to backup Oracle / MySQL

[remko.de.koning]

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

[veremin]

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.

[jlester]

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.

[remko.de.koning]

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?

[veremin]

What is recommended for Oracle?

Stop db service prior to taking snapshot and release it afterwards. This topic might be of some help.

[remko.de.koning]

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.

[nielsengelen]

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.

[remko.de.koning]

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

[nielsengelen]

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?
Lost me

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.

[mithung]

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

[veremin]

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.

[HFX902]

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

[foggy]

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