Overview
Backing up and restoring MySQL databases using a standard VMware VM backup requires leveraging VMware Tools Pre-freeze and Post-thaw scripts. These scripts are essential for achieving application consistency by executing FLUSH TABLES WITH READ LOCK before the VM snapshot begins and holding that lock until the snapshot completes. This ensures all transactional data is written to disk consistently. Because the resulting backup is a clean, consistent disk image, recovery is straightforward: restoring the entire VM and relying on MySQL's automatic crash recovery to bring the database online immediately.
Prerequisites
To successfully implement this backup method, the following conditions must be met:
VMware Tools Installation: The MySQL Server VM must have VMware Tools (or open-vm-tools for Linux) installed and running.
Administrative Access: You must possess administrative privileges (Root/Administrator) on the VM to create, configure, and manage the custom scripts.
Backup Solution: A VM-level backup solution (e.g., Enterprise Workloads or similar) is necessary to execute the actual snapshot and backup operations.
The Backup Process: Ensuring Data Consistency (Quiescing)
The key to a reliable MySQL backup is using the custom scripts to enforce a read lock on the database during the VM snapshot.
1. Procedure for Linux VMs
This method uses shell scripts to reliably hold the lock using a background process.
A. Set Up Script Directory
Create the standard directory where VMware Tools expects to find pre- and post-backup scripts.
sudo mkdir -p /etc/vmware-tools/backupScripts.d
B. Pre-freeze Script (10-pre-freeze-script.sh)
This script flushes all tables and applies a read lock, using a background nohup sleep process to maintain the connection and hold the lock during the snapshot.
#!/bin/bash
# Pre-freeze script to lock MySQL tables for a consistent backup snapshot
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, holding the connection open
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 confirm successful lock acquisition
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
C. Post-thaw Script (20-post-thaw-script.sh)
This script releases the lock by terminating the background process that was holding the connection.
#!/bin/bash
# Post-thaw script to unlock MySQL tables after a consistent backup snapshot
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)
# Terminate the background process
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
D. Set Script Permissions and Check Compatibility
Set executable permissions and ensure the scripts are owned by root.
sudo chmod +x /etc/vmware-tools/backupScripts.d/*.sh
sudo chown root:root/etc/vmware-tools/backupScripts.d/*.sh
⚠️ IMPORTANT: Windows Line Endings Fix
If you created these scripts on a Windows machine, the line endings (CRLF) must be converted to LF using dos2unix to prevent execution errors:
sudo dos2unix /etc/vmware-tools/backupScripts.d/*.sh
2. Procedure for Windows VMs
A. Set Up Script Directory
Create the designated directory:
New-Item -Path "C:\Program Files\VMware\VMware Tools\Guest\backupScripts.d" -ItemType Directory
B. Pre-freeze Script (10-pre-freeze-script.bat)
This batch script executes the lock. Note: This basic script does not hold the lock, as the connection closes immediately. For reliability, a PowerShell script designed to hold the connection is recommended.
@echo off
remPre-freezescripttolockMySQLtablesforaconsistentbackupsnapshot
set"mysql_user=root"
set"mysql_password=your_password"
set"lock_file=C:\Temp\mysql_tables_read_lock.tmp"
remExecuteFLUSHTABLESWITHREADLOCK;(Lockreleasesimmediatelyafterthisscriptexits)
mysql-u%mysql_user%-p%mysql_password%-e"FLUSH TABLES WITH READ LOCK;">NUL2>NUL
if%errorlevel%neq0(
echoERROR:FailedtolockMySQLtables.Checkcredentials.
exit /b 1
)
echoLocksuccessful,creatingsignalfile.
echoLockactive>%lock_file%
exit /b 0
C. Post-thaw Script (20-post-thaw-script.bat)
This script handles cleanup.
@echo off
remPost-thawscripttounlockMySQLtablesafteraconsistentbackupsnapshot
set"lock_file=C:\Temp\mysql_tables_read_lock.tmp"
ifexist"%lock_file%"(
del"%lock_file%"
echoLockfiledeleted.
) else(
echoLockfilenotfound,noactionneeded.
)
exit /b 0
Script Verification: Manual Test Procedure
Before running a production backup, manually test the scripts to ensure credentials and lock logic are correct. You will need two separate terminal sessions for this test.
Step | Terminal 1 (Test Execution) | Terminal 2 (Verification) | Command to Run | Expected Result |
1. Run Pre-freeze | Execute the script. | Monitor Lock File | Linux: sudo /etc/vmware-tools/backupScripts.d/10-pre-freeze-script.sh
Windows: "C:\Program Files\VMware\VMware Tools\Guest\backupScripts.d\10-pre-freeze-script.bat" | Terminal 1: MySQL tables successfully locked.
Terminal 2: Shows the PID. |
2. Verify Lock | Do nothing. | Attempt Write | mysql -e "UPDATE mydatabase.mytable SET column='test' WHERE id=1;" | The command should hang indefinitely (or timeout), confirming the database is locked. |
3. Run Post-thaw | Execute the script. | Monitor Write Status | Linux: sudo /etc/vmware-tools/backupScripts.d/20-post-thaw-script.sh
Windows: "C:\Program Files\VMware\VMware Tools\Guest\backupScripts.d\20-post-thaw-script.bat" | Terminal 1: MySQL tables unlocked.
The hanging write command in Terminal 2 should immediately complete. |
4. Clean Up |
| Verify Lock File is Gone | ls /tmp/mysql_tables_read_lock (Linux example) | Returns "No such file or directory". |
Configure and Run the Backup
After verification, configure your backup solution:
Deploy Proxy: Ensure the backup proxy component is deployed.
Create Policy: Create a backup policy targeting the MySQL VM.
Enable Quiescing: In the policy settings, ensure VMware Tools quiescing and Changed Block Tracking (CBT) are enabled.
Test Run: Perform a manual backup and check the VM logs to confirm the pre- and post-scripts ran successfully.
The Restore Process: VM-Level Recovery
Since the backup captures a full VM image with a consistent MySQL data state, the recovery is purely a VM-level operation.
1. Initiating the VM Restore
Recovery is managed entirely within the backup solution's console:
Select VM: Choose the MySQL Server VM.
Select Recovery Point: Choose a snapshot that was marked as successfully quiesced.
Restore: Initiate a standard Full VM Restore.
2. Database Behavior Upon Power-On
No manual database recovery steps are needed:
Consistent Data: The restored disks contain MySQL data files that were fully consistent due to the pre-freeze lock.
Automatic Recovery: MySQL's InnoDB storage engine automatically detects that the previous shutdown (the snapshot) was not graceful. It uses its transaction logs to perform an automatic crash recovery, ensuring all transactions are brought to a stable, consistent state.
Immediate Availability: Once the MySQL service starts, the database is immediately available for clients.