Backing Up MySQL Including Quiesce Snapshot

[jtmoree23]

Scripts for backing up mysql including quiesce snapshot on nimble storage array.   Code is also at mysql-archives | Free software downloads at SourceForge.net

[jtmoree23]

Since the quiesce and snap is done on the db server and not on the nimble array the snapshots are not on a schedule and must be cleaned up manually.   The best approach would be for nimble to integrate something like these scripts into the array so it would make a connection to the mysql server and hold it open while making the snap.  Scripts like mysqlbackups could be required as a dependency to make it work.  It would be the same model as vmware tools where the array calls into the tools in the guest vm to quiesce file systems. 

[#3]

JT Moree,

The Best Practices Guide for "Nimble Storage for MySQL 5.6 with InnoDB on Oracle Linux & RHEL 6" on page 13 explains that we recommend using the native Nimble snapshot with synchronization set to "none".

A snippet is below:

Nimble Storage recommends using the native Nimble Storage snapshot feature to protect MySQL databases.

Below are some business requirements that can be achieved quickly and efficiently using snapshot:

• Whole database backup

• Database refreshes for Test/Dev/QA

• Database replication

• Test MySQL and Operating System patches

Hope this helps.

Link:

http://www.nimblestorage.com/docs/downloads/bpg_nimble_storage_mysql_innodb_oracle_linux.pdf

Thanks,

[jtmoree23]

Thanks.  I used that document heavily when setting up our environment but it does not address the issue I posted about.  Setting sync to something other than 'none' is the issue I am addressing.  Since Nimble does not support mysql sync I am manually quiescing the database in my own backups and calling into the array to make the snap.   The snaps don't get cleaned up because they are not associated with a schedule.

My suggestion for nimble to implement a mysql sync similar to the MS VSS sync still stands. 

[#5]

I understand your concern about application level quiescing but Linux does not integrates with VSS or VMware tools like Microsoft does, especially for third party applications.  There's no need for application level quescing in the Linux OS because there is no VSS writer for MySQL and there is a general consensus that application developers won't need/use VSS because the application will be written to use kernel API calls appropriately when the File System needs to be quiesced.

After consulting with a RedHat expert I found that this has been a debate between kernel and application developers for quite some time where on the one hand kernel developers are against implementing what they believe is a messy VSS versus application developers missing some customization and snapshot cleanup features for example when a backup completes.  One of the links I found on google partially explains some of this.  So at the end of the day whether VSS will be implemented is more of a Linux kernel development question and then the integration part would be up to the application development.

To make things clear though you will have an application consistent snapshot when synchronization is set to "none" for Nimble volumes.

Link:

virtual machine - Does quiesce exist for Linux? - Unix & Linux Stack Exchange

Thanks,

[jtmoree23]

I agree that quiesce is not absolutely necessary for a MySQL database but if it were a completely superfluous concept no one would do it--even on windows.

Our debate seems to hinge on the concept of OS/filesystem quiesce vs application.  I am asking for an application level quiesce (MySQL) which it would seem goes above and beyond Microsoft's VSS although the end result is the same for database servers.  Given that I have posted code that would allow such a feature it's not unreasonable to make the request.

Having said that, the demand for this feature is probably not very high as evidenced by the many views of this page and the low # of posts.  Everyone can live without this feature (including me).

[#7]

I'll let the open source developers in the Linux world debate whether their OS will ever implement a VSS equivalent that applications can integrate with like Microsoft does.  That would allow for the possibility of VMware tools to simply make an API call like we do with Exchange or SQL that is supported and made from the application vendor versus integrating an script with our snapshots which is a possibility as a feature enhancement request.

Ultimately you are correct and thanks for opening up the script to the community and making it available.  It's a valuable resource for implementing application quiescence snapshots and i'm sure other customer's appreciate all the time saved with this savvy script

[chrisp125]

Hi Joshua

I'm still trying to wrap my head around snapshots for mysql. The PDF link does provide a simple way to manage the Mysql snapshots, the snaps will also be replicated to a DR site. If I need to start up or DR site and mount the snapshot and start up Mysql, will the database be in a usable state, or can I expect a corrupt databse?

MySQL Backups Using LVM Snapshots | Lullabot

Using LVM for MySQL Backup and Replication Setup - MySQL Performance Blog

When creating  LV snapshot, you need to run the mysql command FLUSH TABLES WITH READ LOCK then run the LV snapshot in order to create a consistent snap of your DB files.

If I need to follow Create Nimble snapshot for Oracle DB or the scripts provided in the mail to ensure the database integrity, how will it affect the replication of the snapshot to the second nimble device?

If I have a Mysql database of say size 500G, how long can I expect the snaphot to run and can I determine a snapshot interval, the requirement is no not lose more than 5 minutes in case of a DR scenario where we need to fail over.