Migrate BOSH Azure MySQL DB to a new instance

These step describe the steps necessary to move a BOSH external MySQL 5.7 databases on Azure to a new instance.

Stop BOSH Director VM

Stop the BOSH Director VM in Azure. You can find the VM name in the Operations Manager BOSH Director tile under the Status tab. From the Azure CLI:

az vm stop --resource-group tas-resourcegroup --name 85a181e5-aa6b-4ea7-47b0-b2218b46002a

Backup the DB

Before attempting any migration we must first backup the DB. This can be done within the Azure console, however we're going to do so using mysqldump which works on any IaaS and doesn't rely on IaaS specific storage etc.

The command uses the following syntax:

mysqldump --host <DB FQDN> -u <DB User> -p<DB Password> <DB Name> > bosh.sql

For example:

mysqldump --host bosh-mysql.mysql.database.azure.com -u bosh -pSecret bosh > bosh.sql

This can be done from a jumpbox with mysql tools installed or from Operations Manager which already has mysql and mysqldump installed.

Restore DB Backup

With your BOSH DB backup in hand and the BOSH Director VM stopped, we can now safely restore the BOSH Director DB to our new Azure MySQL instance. First create a same named DB on the new MySQL Server instance, in this case bosh. With an empty bosh DB, we can restore the data using the mysql CLI.

The command uses the following syntax:

mysql -h <New DB FQDN> -P 3306 -u <DB User> -p<DB Password> <DB Name> < bosh.sql

mysql -h new-bosh-mysql.mysql.database.azure.com -P 3306 -u bosh -pSecret bosh < bosh.sql

Reconfigure Operations Manager Director Tile

With the data restored to our new Azure MySQL instance, we can reconfigure the Operations Manager BOSH Director tile to use the new connection details.

First we must put Operations Manager into Advanced Mode to unlock some of the protected fields we need to edit. If you haven't already, install the OM CLI. We need this tool to easily put Opsman into Advanced Mode. After installing OM, set the requisite connection details to your Operations Manager instance by setting the following environment variables.

• OM_TARGET
• OM_USERNAME
• OM_PASSWORD

For example:

export OM_TARGET='opsman.example.vmware.com'
export OM_USERNAME='admin'
export OM_PASSWORD='Secret'
export OM_SKIP_SSL_VALIDATION=true

With the required connection details you should now be able to execute om CLI commands, to validate the connection run:

om products

You should see one or more product tiles returned. Now that we know om is configured properly we can enable advanced mode by running:

om curl --path '/api/v0/staged/infrastructure/locked' --request PUT --data '{"locked" : "false"}'

In your browser login to Opsman, open the BOSH Director tile and open the Director Config tab. Under Database location edit the host, and then optionally the username, password, or any other changed connection details. Click Save.

Go back to the Installation Dashboard and click Review Pending Changes. Click See Changes on the BOSH Director and validate the host attribute and optionally any other attributes you modified show as changed. Go back to the Review Pending Changes screen. Unselect all tiles except for the BOSH Director tile and click Apply Changes.

This will take 10 to 20 minutes as a new BOSH Director VM is spin up and configured to use the new Azure MySQL instance. Once complete you can validate everything works properly by running a few bosh CLI commands like bosh vms.