From time to time, customers ask Quest whether Rapid Recovery explicitly supports MySQL or PostgreSQL. The short answer is "no." However, it isn't hard for Rapid Recovery to capture application-consistent backups from any data storage technology that can be placed in an application-consistent state. This blog post shows how, using MySQL version 8 as an example. It should not be hard to extend this example to other data storage tools.
Most databases have commands to place them into and take them out of "backup mode." Rapid Recovery has integration points (or "hooks") that let users execute scripts before and after key Rapid Recovery Core jobs. Using these script hooks, you can perform system operations before and after a backup or replication transfer, or after virtual export.
In this blog post, we show an example of using these hooks to place MySQL into a consistent state and then putting the database back online. These hooks are only available with the Rapid Recovery Agent - agentless protection doesn't have these hooks.
Disclaimer: This post demonstrates one method in which Rapid Recovery can work with MySQL. We do not represent ourselves as MySQL experts, and the information in his post is not to be interpreted as advice. We tested the approach described here, and it works. However, this approach may not be the best approach for your MySQL instance or your environment. Please review the examples to decide if it is suitable for your needs. If you decide to use the hooks, as best practices dictate, Quest suggests you test your backup and restore process before moving them into production.
Windows Example
On Windows, the hooks that we are need are two PowerShell scripts called PreSnapshotScript.ps1 and PostSnapshotScript.ps1. They have to be placed in the C:\ProgramData\AppRecovery\Scripts folder on the machine that hosts MySQL.
PreSnapshotScript.ps1
param([object]$SnapshotScriptParameter)
$ErrorActionPreference = "SilentlyContinue"
& 'C:\Program Files\MySQL\MySQL Server 8.0\bin\mysql.exe' -u root -ppassword -e "LOCK INSTANCE FOR BACKUP"
PostSnapshotScript.ps1
param([object]$SnapshotScriptParameter)
$ErrorActionPreference = "SilentlyContinue"
$outFile = "C:\preTransfOutput.txt"
& 'C:\Program Files\MySQL\MySQL Server 8.0\bin\mysql.exe' -u root -ppassword -e "UNLOCK INSTANCE"
These examples contain the path where MySQL is installed and the user name & password to connect with. Of course, these will vary from environment to environment.
Linux Example
Since MySQL is an open source database, many instances run on Linux. There are equivalent hooks in the Rapid Recovery Linux Agent, but they use BASH instead of PowerShell. They go into /opt/apprecovery/scripts on Ubuntu. Assuming MySQL is on the path, you could use the following example scripts.
PreSnapshotScript.sh
mysql -u root -ppassword -e "LOCK INSTANCE FOR BACKUP;"
PostSnapshotScript.sh
mysql -u root -ppassword -e "UNLOCK INSTANCE;"
Other Uses
The key commands for MySQL 8 are "LOCK INSTANCE FOR BACKUP;" and "UNLOCK INSTANCE;". With earlier versions of MySQL, the closest commands were "FLUSH TABLES WITH READ LOCK;" and "UNLOCK TABLES;".
For PostgreSQL, you might consider calling pg_start_backups() and pg_stop_backup() API. Another approach is to take a database dump in the PreTransferScript. Using this approach, the dump files should be located on a protected volume, while the database could be on unprotected storage.
A similar technique could be used to check backups with a PostTransferScript or to verify a VM after each export with a PostExportScript.
References
For definitions of application-consistent and crash-consistent, see the Rapid Recovery User Guide topic "Understanding crash-consistent and application-consistent backups."
For more detail on these and other hooks see the "Scripting" section of the Command Line and Scripting Reference Guide. The timing at which scripts are executed is described in "Using Bourne shell and Bash scripting with Rapid Recovery," specifically the subsection "Execution timing for pre- and post- scripts."
KB 126882 - Running a PowerShell script before or after a snapshot or backup job
