There are already many tools for MySQL to perform backups and consistent restores, but this time I’d like to elaborate on how to take advantage of the kindness the cloud, which gives us Persistent Disk Snapshots (PSD) to restore our data set in a consistent way.
In this entry, I'll describe in detail how to restore a backup of our MySQL dataset in a Cloud Compute VM instance making use of persistent disk snapshots and using code examples that can be used as part of an automation strategy. This method will allow us to switch between datasets in less than a minute, as opposed to the several hours that would be required to perform a restore via xtrabackup, or even longer via mysqldump.
Considerations
I’ve been working in Google Cloud Platform (GCP) and chose a simple master/slave replication scenario consisting of two VM instances running Percona Server 5.7 with xtrabackup installed on the slave.
Prior to restoring from a snapshot, we need a backup PDS. I won’t explain it here and will assume a backup PDS has already been created
Regarding permissions, I created a service account for this and included it as part of the Compute Instance Admin (v1) role and gave it Service Account User permissions. You can find how to create this type of account
here
.
With the service account created, it’s time to add it to our gcloud tool in the VM instance helped by the
gcloud auth activate-service-account
command.
The idea
We want to restore a backup PDS from our collection and mount it in a folder in our VM. We’ll stop and point MySQL into that new data folder and start it again. Using this approach, the new data folder will be available in a few seconds (less than 1 minute) and MySQL will be up and running again with a stable and consistent set of data.
Steps
- Create folders if they don't exist
The first thing to do is to ensure we have 2 data folders with the right naming: one for the current data and other for the new one. For this example, we are going to assume that we already have an existing data directory in /data-a and that we are going to restore our PDS to /data-b. As such, it’s important to confirm that /data-a exists and create /data-b if it doesn’t already exist.
mkdir -p /data-a #This will do nothing since /data-a already exists
mkdir -p /data-b
- Select the target folder to mount the restored snapshot
Using file count, we can determine which directory contains the current active data set. We can use this to drive logic that decides where to mount the new data partition coming from the latest PDS.
For this example, I’m taking the most common partition paths:
FILE_COUNT=(`ls /data-a | wc -l`)
if [ $FILE_COUNT -eq 0 ]; then
NEW_PART_NAME='data-a'
NEW_PART_PATH='/dev/sdc1'
OLD_PART_NAME='data-b'
OLD_PART_PATH='/dev/sdb1'
else
NEW_PART_NAME='data-b'
NEW_PART_PATH='/dev/sdb1'
OLD_PART_NAME='data-a'
OLD_PART_PATH='/dev/sdc1'
Fi
- Gather info for snapshot restoration to disk
Lastly, we need to decide which snapshot we’ll take from our collection and the disk names. We’ll need these variables in the coming steps.
For this example, all the PDS taken in the past contains the
bkp
string in the name, so in the GREP command, I filter based on this string. Based on this criteria, we are going to use the last backup PDS taken.
VM_NAME=`hostname`
SNAP_NAME=(`gcloud compute snapshots list --sort-by ~NAME | grep bkp | head -2 | tail -1 | awk '{print $1}'`)
SNAP_ZONE=(`gcloud compute snapshots list --sort-by ~NAME | grep bkp | head -2 | tail -1 | awk '{print $3}' | cut -d'/' -f 1`)
NEW_DISK_NAME=${VM_NAME}-${NEW_PART_NAME}
OLD_DISK_NAME=${VM_NAME}-${OLD_PART_NAME}
Having all the information we need to perform a proper PDS restoration, the next step is to create a new disk from a given snapshot we got from the previous step.
gcloud compute disks create ${NEW_DISK_NAME} \
--source-snapshot ${SNAP_NAME} \
--zone ${SNAP_ZONE}
- Attach disk to the current VM
We have a new disk containing our latest backup but is not attached to our VM. It’s time to change that:
gcloud compute instances attach-disk ${VM_NAME} \
--disk ${NEW_DISK_NAME} \
--zone ${SNAP_ZONE}
Once the disk is attached to our VM instance, we mount it.
mount ${NEW_PART_PATH} /${NEW_PART_NAME}
- Ensure proper permissions in the new data folder
We have our data backup already on the VM. Ensure MySQL users will be able to access it:
chown -R mysql:mysql /${NEW_PART_NAME}
systemctl stop mysql
- Find the datadir variable in my.cnf
Now, with MySQL stopped, it’s time to change the datadir variable and point it to the new data folder.
REPLACEMENT="datadir=/"${NEW_PART_NAME}"/mysql"
sed -i "s|datadir=.*|${REPLACEMENT}|g" ${MY_CNF_FILE}
- Start MySQL service back again
Time to start MySQL again and ensure it starts properly. If not, a quick look into the error log will point us to the reason:
systemctl start mysql
- Edit fstab to make the partition changes permanent
We have our MySQL instance up and running using our new dataset but we have not made our changes OS persistent. We edit fstab for this matter:
sed -i "s|${OLD_PART_PATH}|${NEW_PART_PATH}|g" /etc/fstab
sed -i "s|${OLD_PART_NAME}|${NEW_PART_NAME}|g" /etc/fstab
We can now umount our old data partition without taking any risk. I like to wait 5 seconds when scripting this to ensure any process in the OS has enough time to finish any action on the partition
sleep 5
umount -l ${OLD_PART_PATH}
Considering the old disk is not being used anymore by our OS, we can detach it from the VM instance:
gcloud compute instances detach-disk ${VM_NAME} \
--disk ${OLD_DISK_NAME} \
--zone ${SNAP_ZONE}
And, of course, we don’t need it in our disk collection anymore:
gcloud compute disks delete ${OLD_DISK_NAME} \
--zone ${SNAP_ZONE} \
--quiet
The old data directory can be removed, so we’re sure the next time we run the process it will create the empty folder and will use that one:
rm -rf /${OLD_PART_NAME}
Conclusions
Working in the cloud brings many advantages and flexibility. It’s a good thing to extend that flexibility to our restoration process which gives us a huge cut in restoration time when it comes to serious data sets.
In addition, creating and keeping a decent amount of PDS in our GCP collection instead of compressed backup seems more friendly for management (GCP has a nice GUI, in my personal opinion) and easier to maintain (you can remove them, sort them by date or size, you name it).
.svg){kind=small}
.svg){kind=small}