Overview:
Backing up a MySQL database on a VMware virtual machine (VM) involves using VMware Tools to run pre-and post-backup scripts. These scripts ensure the database is in a consistent state during the backup, preventing data corruption. This method works for both standalone and clustered MySQL databases on both Linux and Windows VMs.
Prerequisites
VMware Tools must be installed on the MySQL Server VM.
You need administrative privileges on the VM to create and manage the necessary scripts and directories.
A backup solution, such as Enterprise Workloads, is required to perform the actual VM-level backup.
Procedure for Linux VMs
1. Set Up Script Directory
Create the backupScripts.d directory where VMware Tools expects to find pre-and post-backup scripts.
sudo mkdir -p /etc/vmware-tools/backupScripts.d
2. Create and Configure Scripts
Create two scripts: a pre-freeze script to prepare the database for backup and a post-thaw script to resume normal operations. Name them using a numerical prefix (e.g., 10-pre-freeze-script.sh and 20-post-thaw-script.sh) to control their execution order.
Script Name | Purpose | Example |
10-pre-freeze-script.sh | Locks the MySQL database tables to prevent writes. |
|
20-post-thaw-script.sh | Unlocks the MySQL tables, allowing writes to resume. |
|
Pre-freeze script (10-pre-freeze-script.sh): This script flushes and locks all MySQL tables. It then creates a lock file to signal the successful lock. A background process (nohup) holds the lock, ensuring the database remains in a consistent state during the VM snapshot.
#!/bin/bash
# Pre-freeze script to lock MySQL tables for a consistent backup snapshot
#
# Use this script to ensure MySQL tables are flushed and read-locked before a
# VMware snapshot is taken. This is essential for a consistent backup.
timeout=60
lock_file=/tmp/mysql_tables_read_lock
sleep_time=$((timeout+10))
rm -f $lock_file
echo"Attempting to FLUSH TABLES WITH READ LOCK..." | logger -t $(basename $0)
# Run the lock command in the background
mysql -uroot -p'your_password' -e "FLUSH TABLES WITH READ LOCK; SYSTEM touch $lock_file; SYSTEM nohup sleep $sleep_time &" > /dev/null &
mysql_freeze_pid=$!
echo"MySQL freeze process PID: $mysql_freeze_pid" | logger -t $(basename $0)
c=0
# Wait for the lock file to be created, signaling a successful lock
while [ ! -f "$lock_file" ]; do
if ! ps -p "$mysql_freeze_pid" 1>/dev/null; then
echo"MySQL command failed (bad credentials?)" | logger -t $(basename $0
exit 1
fi
sleep 1
c=$((c+1))
if [ $c -gt $timeout ]; then
echo"Timed out waiting for MySQL lock" | logger -t $(basename $0)
touch "$lock_file"
kill"$mysql_freeze_pid"
exit 1
fi
done
echo"$mysql_freeze_pid" > "$lock_file"
echo"MySQL tables successfully locked." | logger -t $(basename $0)
exit 0
Post-thaw script (20-post-thaw-script.sh): This script reads the process ID (PID) of the lock-holding process from the lock file and then kills it. This action releases the read lock, allowing normal database operations to resume.
#!/bin/bash
# Post-thaw script to unlock MySQL tables after a consistent backup snapshot
#
# This script releases the read lock on MySQL tables that was applied by the
# pre-freeze script.
lock_file=/tmp/mysql_tables_read_lock
if [ -f "$lock_file" ]; then
mysql_freeze_pid=$(cat "$lock_file")
echo"Releasing MySQL lock, sending SIGTERM to PID $mysql_freeze_pid" | logger -t $(basename $0)
pkill -9 -P "$mysql_freeze_pid"
rm -f "$lock_file"
echo"MySQL tables unlocked." | logger -t $(basename $0)
else
echo"Lock file not found, assuming no lock was applied." | logger -t $(basename $0)
fi
exit 0
3. Set Script Permissions
Ensure the scripts are executable and owned by the root user to be run by VMware Tools.
sudo chmod +x /etc/vmware-tools/backupScripts.d/*.sh
sudo chown root:root /etc/vmware-tools/backupScripts.d/*.sh
Procedure for Windows VMs
1. Set Up Script Directory
VMware Tools for Windows uses a specific folder for scripts, usually %ProgramFiles%\VMware\VMware Tools\Guest\backupScripts.d. If this folder doesn't exist, create it.
New-Item -Path "C:\Program Files\VMware\VMware Tools\Guest\backupScripts.d" -ItemType Directory
2. Create and Configure Scripts
Create two batch or PowerShell scripts. The pre-freeze script will lock the database, and the post-thaw script will unlock it.
Script Name | Purpose |
10-pre-freeze-script.bat | Locks the MySQL database. |
20-post-thaw-script.bat | Unlocks the MySQL database. |
Pre-freeze script (10-pre-freeze-script.bat): This script uses the mysql client to execute the FLUSH TABLES WITH READ LOCK command. It's crucial to ensure the command completes successfully.
@echo off
rem Pre-freeze script to lock MySQL tables for a consistent backup snapshot
rem
rem This script uses the mysql client to execute the lock command.
rem A temporary file is created to signal the success of the lock operation.
set "mysql_user=root"
set "mysql_password=your_password"
set "lock_file=C:\Temp\mysql_tables_read_lock.tmp"
echo FLUSH TABLES WITH READ LOCK; > NUL 2> NUL
mysql -u%mysql_user% -p%mysql_password% -e "FLUSH TABLES WITH READ LOCK;" > NUL 2> NUL
if %errorlevel% neq 0 (
echo ERROR: Failed to lock MySQL tables. Check credentials.
exit /b 1
)
echo Lock successful, creating signal file.
echo Lock active > %lock_file%
exit /b 0
Note: A simple FLUSH TABLES WITH READ LOCK command executed via a batch file may not be sufficient on its own. It should be wrapped in a script that holds the connection open, similar to the Linux example, to maintain the lock until the snapshot is complete. A more robust solution might involve a PowerShell script that uses a loop to hold the connection.
Post-thaw script (20-post-thaw-script.bat): This script needs to release the read lock. Since the pre-freeze script only created a read lock and then exited, the lock is already released. This script is primarily for cleanup and confirmation.
@echo off
rem Post-thaw script to unlock MySQL tables after a consistent backup snapshot
rem
rem Since the pre-freeze script's connection is closed, the lock should be released.
rem This script's primary purpose is to confirm the unlock and clean up any temp files.
set "lock_file=C:\Temp\mysql_tables_read_lock.tmp"
if exist "%lock_file%" (
del "%lock_file%"
echo Lock file deleted. MySQL tables are now unlocked.
) else (
echo Lock file not found, no action needed.
)
exit /b 0
Note: The FLUSH TABLES WITH READ LOCK command is session-based. The lock is released when the connection is closed. The Linux example holds the connection open in a background process. For Windows, a more advanced script using PowerShell or a similar tool to maintain the connection during the snapshot would be a more reliable approach.
Configure Your Druva Backup Solution
After setting up the scripts on the VM, you must configure your backup solution to use them.
Deploy a Backup Proxy: Deploy a backup proxy, which is the component that interacts with the VMware environment to perform backups.
Create a Backup Policy: In your backup solution's management console (e.g., Enterprise Workloads), create a backup policy for the VM.
Enable Quiescing: In the policy settings, enable VMware Tools quiescing and Changed Block Tracking (CBT). Quiescing tells VMware to use the pre-and post-backup scripts.
Run a Test Backup: Perform a manual backup of the VM to validate that the scripts are running correctly. You can check the VM's event logs or the VMware Tools logs to confirm.