By default, a Cloud Server Snapshot may not properly recover a MySQL database as there is no guarantee as to the database write state at the time of snapshot. This article provides a suggested approach to address this limitation by using the "pre and post script" feature to place a MySQL "FLUSH TABLES WITH READ LOCK" on the database while the vSphere portion of the snapshot is implemented, a process that should last only a few seconds. For more details, see Introduction to Cloud Server Snapshots for an overview of the snapshot process and the pre and post script feature.
This Article Provides an OVERVIEW of Approach
This article provides an overview of the suggested approach and is designed to be adapted by UNIX engineers familiar with MySQL and the issues of MySQL backups as well as their specific application. Given the variety of potential configurations, some changes may be required for a user's specific scenario. We strongly recommend implementing and then testing any approach prior to relying on it for production purposes.
- Unix Server environment with Bash Shell
- Unix Server should meet all the pre-requisites needed to use the Pre and Post Scripts feature (VM Tools, OS, File system, etc.)
- The requirements are spelled out in How to Set Pre and Post Snapshot Scripts on a Cloud Server
Approach will LOCK database while vSphere Snapshot is in progress
This function uses the MySQL FLUSH TABLES WITH READ LOCK function. Once the pre-snapshot script has run, you cannot inject data into the database until the post-snapshot script has completed running. Ensure your application can support such an interruption before proceeding with this approach.
Create Scripts on Server to Lock and Unlock the Database
- Install a script on the server that will implement the MYSQL_INPUT FLUSH TABLES WITH READ LOCK function, thereby locking the database. See sample script below, which will need to be updated with the user/password which has access to the DB.
- Install a script on the server that will unlock the database. See sample script below, which will need to be updated with the user/password which has access to the DB.
Configure Pre and Post Script Feature to Use Lock and Unlock Scripts
- Follow the instructions in How to Set Pre and Post Snapshot Scripts on a Cloud Server, referencing the script you set above
- Pre-Snapshot Script:
File Path on the Server - The absolute path to the first script created above that will lock the server
Failure Handling - We recommend the CONTINUE option, in which case the system will take the snapshot even if database script to "lock" the database isn't successful - the theory being that some snapshot is better than none. However, note that the failure is not logged by the Snapshot service so you should employ local alerting if possible. Alternately, setting it to the ABORT option will create a "failed" snapshot record (though no actual snapshot) which may make it clearer you're having problems so long as you are reviewing snapshot success periodically.
Script Timeout - The system will wait this long for the lock script to finish. We'd recommend setting this to 15 minutes or less.
- Post-Snapshot Script:
- File Path on the Server - The absolute path to the second script created above that will unlock the server
- Guest OS Username - Username with permission to run both scripts
Password - Password for the Username
- Pre-Snapshot Script: