Azure VM Application Consistent MySQL DB Disk Snapshots

Backup of Database is the pillar of our system which is necessary and mandatory to provide us data incase of crash, new machine provisioning and many other scenarios listed here. 

As part of the backup process, a snapshot is taken, and the data is transferred to the Recovery Services vault with no impact on production workloads. The snapshot provides different levels of consistency, as described below:

1. Application-consistent: App-consistent backups capture memory content and pending I/O operations. App-consistent snapshots use a VSS writer (or pre/post scripts for Linux) to ensure the consistency of the app data before a backup occurs.

When you're recovering a VM with an app-consistent snapshot, the VM boots up. There's no data corruption or loss. The apps start in a consistent state.

2. File-system consistent: File-system consistent backups provide consistency by taking a snapshot of all files at the same time.

When you're recovering a VM with a file-system consistent snapshot, the VM boots up. There's no data corruption or loss. Apps need to implement their own "fix-up" mechanism to make sure that restored data is consistent.

3. Crash-consistent: Crash-consistent snapshots typically occur if an Azure VM shuts down at the time of backup. Only the data that already exists on the disk at the time of backup is captured and backed up.

Starts with the VM boot process followed by a disk check to fix corruption errors. Any in-memory data or write operations that weren't transferred to disk before the crash are lost. Apps implement their own data verification. For example, a database app can use its transaction log for verification. If the transaction log has entries that aren't in the database, the database software rolls transactions back until the data is consistent.

So now after reading snapshot consistency we should choose application consistent snapshot which doesn't let guarantees no data loss. So below are the steps for setting up application consistent snapshots for a MySQL VM.

Schedule backup on the VM using procedure listed here.

Setup pre and post backup hooks using below procedure:

Step 1: Create necessary dir and files:

mkdir -p /etc/azure/
cd  /etc/azure/
mkdir logs

Step 2: create a user in Mysql with super privilege to stop and start slave.

GRANT SUPER ON *.* TO 'bkpuser'@'localhost' IDENTIFIED BY 'secretpass';

Step 3: Create below files required to configure pre and post backup hooks:

vim VMSnapshotScriptPluginConfig.json
{
    "pluginName" : "ScriptRunner",
    "preScriptLocation" : "/etc/azure/pre-mysql-backup.sh",
    "postScriptLocation" : "/etc/azure/post-mysql-backup.sh",
    "preScriptParams" : ["", ""],
    "postScriptParams" : ["", ""],
    "preScriptNoOfRetries" : 0,
    "postScriptNoOfRetries" : 0,
    "timeoutInSeconds" : 30,
    "continueBackupOnFailure" : true,
    "fsFreezeEnabled" : true
}

vim pre-mysql-backup.sh
#!/bin/bash
# variable declaration
PROGNAME=$(basename $0)
host=`hostname`
IP=`hostname -I | xargs`
USER="bkpuser"
PASS="secretpass"
SENDTO='recipients@company.com'
# functions
function error_exit
{
       if [ $? -ne 0 ]; then
           echo "${PROGNAME}: Step $1:- Failed at `($date date +"%Y_%m_%d")`" | mail -s "${PROGNAME} failed for Host : $host" -r "dba-alerts <dba-alerts@comany.com>" $SENDTO
           exit 1
       fi
}
touch /etc/azure/logs/$(basename $0).log
mysql -u$USER -p$PASS -e "stop slave;" 2>&1 | tee /etc/azure/logs/$(basename $0).log
error_exit "stopping slave"

vim post-mysql-backup.sh
#!/bin/bash
# variable declaration
PROGNAME=$(basename $0)
host=`hostname`
IP=`hostname -I | xargs`
USER="bkpuser"
PASS="secretpass"
SENDTO='recipients@company.com'
# functions
function error_exit
{
       if [ $? -ne 0 ]; then
           echo "${PROGNAME}: Step $1:- Failed at `($date date +"%Y_%m_%d")`" | mail -s "${PROGNAME} failed for Host : $host" -r "dba-alerts <dba-alerts@company.com>" $SENDTO
           exit 1
       fi
}
touch /etc/azure/logs/$(basename $0).log
mysql -u$USER -p$PASS -e "start slave;" 2>&1 | tee /etc/azure/logs/$(basename $0).log
error_exit "starting slave"

Step 4: Configure required permission on the files by azure backup

chmod 700 *.sh
chmod 600 VMSnapshotScriptPluginConfig.json

Ref: 

[1]. https://docs.microsoft.com/en-us/azure/backup/backup-azure-vms-introduction#snapshot-creation