Skip to main content
How to back up MySQL database on VMware

Instructions to back up MySQL database on VMware

Updated over a week ago

This document guides you on how to back up a MySQL clustered and standalone databases hosted on VMware using the Enterprise Workloads data protection solution. You can leverage VMware Tools utility to run custom pre-post backup scripts, ensuring database consistent snapshots.


📝 Note

Ensure VMware Tools are installed on the MySQL Server VM.


Procedure

  1. Create the backupScripts.d folder under the /etc/vmware-tools on the MySQL Server VM. For more information, refer to the VMware KB article.

    1. SSH into the MySQL Server VM as a non-root sudo user.

    2. Run the following command:

      sudo mkdir /etc/vmware-tools/backupScripts.d​

  2. Create pre and post script files in the /etc/vmware-tools/backupScripts.d folder and you must name them starting with a number to ensure sequential execution of IO freeze and IO thaw tasks by VMware tools, where lower number has higher priority during script execution. In our example, the 10-pre-freeze-script.sh script will be executed prior to the 20-post-thaw-script.sh script.

    1. Pre-backup script: 10-pre-freeze-script.sh

    2. Post-backup script: 20-post-thaw-script.sh

  3. Ensure scripts are executable with appropriate privileges.

    1. SSH into the MySQL Server VM.

    2. Run the following commands:

      sudo chmod +x /etc/vmware-tools/backupScripts.d/10-pre-freeze-script.sh
      sudo chmod +x /etc/vmware-tools/backupScripts.d/20-post-thaw-script.sh
      sudo chown root:root /etc/vmware-tools/backupScripts.d/10-pre-freeze-script.sh
      sudo chown root:root /etc/vmware-tools/backupScripts.d/20-post-thaw-script.sh​

  4. Deploy a backup proxy to back up your VM.

    1. Download the VMware Proxy Deployer on your local system and deploy the backup proxy using steps listed in Deploy VMware proxy using VMware Proxy Deployer.

    2. Configure VM for backup by creating a backup policy on the Enterprise Workloads Management Console.

    3. Enable Changed Block Tracking (CBT) and VMware tools quiescing in the backup policy configuration.

  5. Perform a manual VM backup on the Enterprise Workloads Management Console for a quick validation.

Sample Pre-script: 10-pre-freeze-script.sh

#!/bin/bash

timeout=60
lock_file=/tmp/mysql_tables_read_lock
sleep_time=$((timeout+10))

rm -f $lock_file
echo "FLUSH TABLES WITH READ LOCK" | logger -t $(basename $0)
mysql -uroot -p'my_password' -e "FLUSH TABLES WITH READ LOCK; system touch $lock_file; system nohup sleep $sleep_time; system echo \"Lock released\"|logger -t $(basename $0); " > /dev/null &

mysql_freeze_pid=$!
echo "child pid $mysql_freeze_pid" | logger -t $(basename $0)
c=0

while [ ! -f $lock_file ]
do
if ! ps -p $mysql_freeze_pid 1>/dev/null ; then
echo "Mysql command has 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 lock" | logger -t $(basename $0)
touch $lock_file
kill $mysql_freeze_pid
fi
done
echo $mysql_freeze_pid > $lock_file
exit 0

Sample Post-script: 20-post-thaw-script.sh

#!/bin/bash

lock_file=/tmp/mysql_tables_read_lock
mysql_freeze_pid=$(cat $lock_file)

echo "Sending sigterm to $mysql_freeze_pid" | logger -t $(basename $0)
pkill -9 -P $mysql_freeze_pid
rm -f $lock_file

exit 0
Did this answer your question?