How to restore MySQL data folder using persistent disk snapshots (GCP)

• Create folders if they don't 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

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

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}

• Restore snapshot to disk

gcloud compute disks create ${NEW_DISK_NAME} \
 --source-snapshot ${SNAP_NAME} \
 --zone ${SNAP_ZONE} 
 

• Attach disk to the current VM

gcloud compute instances attach-disk ${VM_NAME} \
 --disk ${NEW_DISK_NAME} \
 --zone ${SNAP_ZONE}

• Mount the created disk

mount ${NEW_PART_PATH} /${NEW_PART_NAME}

• Ensure proper permissions in the new data folder

chown -R mysql:mysql /${NEW_PART_NAME}

• Stop MySQL

systemctl stop mysql

• Find the datadir variable in my.cnf

REPLACEMENT="datadir=/"${NEW_PART_NAME}"/mysql"
 sed -i "s|datadir=.*|${REPLACEMENT}|g" ${MY_CNF_FILE}

• Start MySQL service back again

systemctl start mysql

• Edit fstab to make the partition changes permanent

sed -i "s|${OLD_PART_PATH}|${NEW_PART_PATH}|g" /etc/fstab
 sed -i "s|${OLD_PART_NAME}|${NEW_PART_NAME}|g" /etc/fstab

• Umount unused partition

sleep 5
 umount -l ${OLD_PART_PATH}

• Detach disk

gcloud compute instances detach-disk ${VM_NAME} \
 --disk ${OLD_DISK_NAME} \
 --zone ${SNAP_ZONE} 

• Remove disk

gcloud compute disks delete ${OLD_DISK_NAME} \
 --zone ${SNAP_ZONE} \
 --quiet

• Remove the data dir

rm -rf /${OLD_PART_NAME}