MySQL Migrator

mysql-migrator.sh

#/bin/bash
set -e

if [[ -n "$DEBUG" ]]
then
  echo "!!! Debug output enabled"
  set -x
fi

set -u

## CONFIGURE STUFF HERE!
# GUIDs of the MySQL service instances
OLD_SERVICE_GUID=8a070567-2d88-4aac-9bd4-75a3b3d28d76
NEW_SERVICE_GUID=b66d5c0d-e404-42fa-b91a-6af947bfd4fd

# Backup server information
SCP_HOST=ubuntu-457.haas-457.pez.vmware.com
SCP_PORT=22
SCP_USERNAME=ubuntu
SCP_KEY_PATH=/home/david/457.key
SCP_BACKUP_PATH=/home/ubuntu/backups/p.mysql

# OpsMan ENV files
OLD_OM_ENV=/home/david/work/mysql-migrator/old.yml
NEW_OM_ENV=/home/david/work/mysql-migrator/new.yml

# Where to locally store some files while the script executes
SCRATCHWORK_DIR=/tmp/mysql-migration-scratch

# CF API of the new foundation
NEW_FOUNDATION_API=https://api.run.haas-457.pez.vmware.com
OLD_FOUNDATION_API=https://api.run.haas-457.pez.vmware.com

## DON'T TOUCH BELOW!

mkdir -p ${SCRATCHWORK_DIR}/extracted

# Sign into old foundation to grab admin credentials
echo ">>> Setting targets to old foundation and signing in..."
old_bosh_env=$(om -e ${OLD_OM_ENV} bosh-env)
eval $old_bosh_env

# Get BOSH deployment names for MySQl and CF
mysql_broker_deployment_name=$(bosh deps --column=name | grep pivotal-mysql | xargs)
cf_deployment_name=$(bosh deps --column=name | grep cf- | xargs)

# Grab credentials to log into runtime CredHub and TAS admin
CREDHUB_ADMIN_CLIENT=credhub_admin_client
credhub_admin_secret=$(om -e ${OLD_OM_ENV} curl -s -p /api/v0/deployed/products/${cf_deployment_name}/credentials/.uaa.credhub_admin_client_client_credentials | jq .credential.value.password)

if [ -z "$credhub_admin_secret" ]
then
  echo "!!! Error trying to grab CredHub admin secret for the old foundation. Is everything OK with OpsMan?"
  exit 1
fi

TAS_ADMIN_USER=admin
tas_admin_password=$(om -e ${OLD_OM_ENV} curl -s -p /api/v0/deployed/products/${cf_deployment_name}/credentials/.uaa.admin_credentials | jq .credential.value.password)
if [ -z "$tas_admin_password" ]
then
  echo "!!! Error trying to grab TAS admin secret for the old foundation. Is everything OK with OpsMan?"
  exit 1
fi


cf login -a ${OLD_FOUNDATION_API} -u ${TAS_ADMIN_USER} -p ${tas_admin_password} --skip-ssl-validation -o system -s system

echo ">>> Figuring out some information about the old service instance."
# Figure out some info on the old SI
old_service_info=$(cf curl --fail /v2/service_instances/$OLD_SERVICE_GUID)
old_service_name=$(echo ${old_service_info} | jq -r .entity.name)
old_service_plan_url=$(echo ${old_service_info} | jq -r .entity.service_plan_url)
old_service_plan_name=$(cf curl --fail ${old_service_plan_url} | jq -r .entity.name)
echo "!!! This service instance used a ${old_service_plan_name} plan. We will update the new instance to match that later."
# Deduce latest backup path
echo ">>> Figuring out latest backup for ${old_service_name}"
latest_backup_path=$(ssh ${SCP_USERNAME}@${SCP_HOST} -i ${SCP_KEY_PATH} -q "cd ${SCP_BACKUP_PATH}/service-instance_${OLD_SERVICE_GUID} && cd \$(ls -1dt ./*/*/*/ | head -n 1) && echo \$PWD/\$(ls -t | head -n 1)")

echo ">>> Downloading latest backup..."
# Grab backup from SCP server and download it locally
scp -P ${SCP_PORT} -i ${SCP_KEY_PATH} ${SCP_USERNAME}@${SCP_HOST}:${latest_backup_path} ${SCRATCHWORK_DIR}

# Extract backup and also grab the filename, which is conviently the backup ID
echo ">>> Extracting latest backup..."
backup_id=$(cd ${SCRATCHWORK_DIR} && ls -t *.tar | head -n 1| cut -f 1 -d '.')
extracted_file=$(tar -xvf $(ls -t ${SCRATCHWORK_DIR}/*.tar | head -n 1) -C ${SCRATCHWORK_DIR}/extracted/ | grep .tar.gpg)

# BOSH SSH onto the MySQL broker, target the runtime CredHub, and grab the encryption key for the backup we've got
echo ">>> Getting encryption key for latest backup..."
encryption_key="$(bosh -d ${mysql_broker_deployment_name} ssh dedicated-mysql-broker/0 -c "
    export PATH=/var/vcap/packages/credhub-cli/bin:\$PATH
    credhub api https://credhub.service.cf.internal:8844 --ca-cert /var/vcap/jobs/adbr-api/config/credhub_ca.pem
    credhub login --client-name ${CREDHUB_ADMIN_CLIENT} --client-secret ${credhub_admin_secret}
    echo VALGET: \$(credhub get -n /tanzu-mysql/backups/${backup_id} --quiet)" 2>&1 | awk '/VALGET:/ {$1="";print $5}')"
encryption_key=$(echo "$encryption_key" | tr -d '\r' | cut -c1-12)
# Now target the new foundation
echo ">>> Switching over targets to new foundation and signing in..."

# Grab BOSH and CredHub info for new foundation
new_bosh_env=$(om -e ${NEW_OM_ENV} bosh-env)
eval $new_bosh_env
new_cf_deployment_name=$(bosh deps --column=name | grep cf- | xargs)

# Sign into new CF
TAS_ADMIN_USER=admin
new_tas_admin_password=$(om -e ${NEW_OM_ENV} curl -s -p /api/v0/deployed/products/${new_cf_deployment_name}/credentials/.uaa.admin_credentials | jq .credential.value.password)
cf login -a ${NEW_FOUNDATION_API} -u ${TAS_ADMIN_USER} -p ${new_tas_admin_password} --skip-ssl-validation -o system -s system

# Grab some nice info about the new SI
new_service_info=$(cf curl --fail /v2/service_instances/$NEW_SERVICE_GUID)
new_service_name=$(echo ${new_service_info} | jq -r .entity.name)

# SCP extracted, encrypted backup to the MySQL SI
echo ">>> Uploading ${old_service_name} backup to ${new_service_name} VMs for restoring... (this may take awhile, there is no progress bar)"
mysql_si_bosh_name="service-instance_${NEW_SERVICE_GUID}"

bosh -d ${mysql_si_bosh_name} scp ${SCRATCHWORK_DIR}/extracted/${extracted_file} mysql:/tmp/

echo ">>> Restoring the MySQL database... (this may take awhile, there is no progress bar)"  
bosh -d ${mysql_si_bosh_name} ssh mysql/0 -c "mysql-restore --encryption-key ${encryption_key} --restore-file /tmp/${extracted_file}"

echo ">>> Upgrading the restored MySQL database to the original plan..."
new_plan_guid=$(cf curl --fail /v2/service_plans | jq -r --arg plan_name "${old_service_plan_name}" '.resources[] | select(.entity.name==$plan_name) | .metadata.guid')

cf curl --fail -X PUT /v2/service_instances/${NEW_SERVICE_GUID}?accepts_incomplete=true -d  "{\"service_plan_guid\":\"${new_plan_guid}\"}"

rm -rf ${SCRATCHWORK_DIR}/extracted
echo ">>> Service restore complete. The new database is currently upgrading, check its' status in the Apps Manager web UI."