MySQL Backup

Backup Options

The following options are supported to backup MySQL databases:

• HotBackup Database Online Dump
  • Online Dump to the same server
  • Online Dump to Staging server
• HotBackup Database Freeze
• ColdBackup Database Shutdown

HotBackup Database Online Dump

There are multiple options available regarding Database Online Dump. One of the options is to use Veeam Pre Freeze & Post Thaw Scripts to dump the database during the backup operations, and another option would be to dump the database to another staging server and protect the staging server via Veeam.

Let’s go through each option one by one in the details.

Database Online Dump During Backup Operations

In this option, the pre-freeze script will dump all databases hosted on the guest to a single file under the /tmp directory. Before VM snapshot creation, the mysqldump native command will dump a copy of the database while service will remain available.

The dump will be deleted by post-thaw script after the guest snapshot has been successful.

Online Dump to Staging server

As described above, another option is to dump the MySQL database to a staging server and protect this staging server via backup job.

1. Create new server or use any existing server as NFS share.
2. Create script to dump the MySQL database to staging server
3. Use editor
4. Copy below sample code in the editor

#!/bin/bash
# Shell script to backup MySQL database

# Set these variables
MyUSER=""	# DB_USERNAME
MyPASS=""	# DB_PASSWORD
MyHOST=""	# DB_HOSTNAME

# Backup Dest directory
DEST="" # /home/username/backups/DB

# Email for notifications
EMAIL=""

# How many days old files must be to be removed
DAYS=3

# Linux bin paths
MYSQL="$(which mysql)"
MYSQLDUMP="$(which mysqldump)"
GZIP="$(which gzip)"

# Get date in dd-mm-yyyy format
NOW="$(date +"%d-%m-%Y_%s")"

# Create Backup sub-directories
MBD="$DEST/$NOW/mysql"
install -d $MBD

# DB skip list
SKIP="information_schema
another_one_db"

# Get all databases
DBS="$($MYSQL -h $MyHOST -u $MyUSER -p$MyPASS -Bse 'show databases')"

# Archive database dumps
for db in $DBS
do
    skipdb=-1
    if [ "$SKIP" != "" ];
    then
		for i in $SKIP
		do
			[ "$db" == "$i" ] && skipdb=1 || :
		done
    fi

    if [ "$skipdb" == "-1" ] ; then
    	FILE="$MBD/$db.sql"
	$MYSQLDUMP -h $MyHOST -u $MyUSER -p$MyPASS $db > $FILE
    fi
done

# Archive the directory, send mail and cleanup
cd $DEST
tar -cf $NOW.tar $NOW
$GZIP -9 $NOW.tar

echo "MySQL backup is completed! Backup name is $NOW.tar.gz" | mail -s "MySQL backup" $EMAIL
rm -rf $NOW

# Remove old files
find $DEST -mtime +$DAYS -exec rm -f {} \;

1. Save file as db_backup.sh
2. Use Linux scheduler to run the script on desired time for the backup
3. Configure the backup of staging VM

HotBackup Database Freeze

In this option, Veeam will freeze the database during pre-freeze script and release the database in post-thaw. MySQL databases will be flushed to disk and enter read-only state, then return to writable state once the VM snapshot has been created.

Tip

Adjust the timeout according to database size, in the sample script we have set 300 seconds for timeout

Cold Backup Database Shutdown

In this option, Veeam will use pre and post-thaw script to stop and start the MySQL service using init.d or systemctl commands, depending on the database packages during the snapshot operations.

References

• Veeam Helpcenter: Pre Freeze & Post Thaw Scripts
• MySQL pre/post Scripts on VeeamHub