So you have a Cloud Foundry deployed with the `cf-release` codebase
and you have noticed that in the newer release Cloud Foundry switched to MySQL.
And now you want to upgrade. but first you need to move from PostgreSQL so it will make your life easier for the future upgrades!

The good thing is...
It can be done pretty easy without much downtime.
please note! that you need to first test the migration in a sandbox/test enviorment.

LETS GET STARTED!!

Prerequisites:

go get --insecure github.com/pivotal-cf/pg2mysql/cmd/pg2mysql

Manifest changes:

First lets copy your current manifest (e.g. cf.yml) that you used to deploy your Cloud Foundry with to a file named cf-mysql.yml.

mv cf.yml to cf-mysql.yml`

Next you will create config files to hold connection information to the ccdb, uaa, and diego databases:

mkdir -p db_configs
touch db_configs/ccdb.yml
touch db_configs/uaadb.yml
touch db_configs/diegodb.yml

Now you will need to populate the contents of each of the files using your favorite editor. For each file you will find the connection information in the cf-mysql.yml file.

Note: You'll need to edit anything in ALLCAPS to match your environment

Edit the file db_configs/ccdb.yml:

mysql:
  database: ccdb
  username: ccadmin
  password: CHANGEME
  host: MYSQL_PROXY_IP
  port: 3306

postgresql:
  database: ccdb
  username: ccadmin
  password: CHANGEME
  host: POSTGRES_IP
  port: 5524
  ssl_mode: disable

Edit the file db_configs/uaadb.yml:

mysql:
  database: uaadb
  username: uaaadmin
  password: CHANGEME
  host: MYSQL_PROXY_IP
  port: 3306

postgresql:
  database: uaadb
  username: uaaadmin
  password: CHANGEME
  host: POSTGRES_IP
  port: 5524
  ssl_mode: disable

Edit the file db_configs/diegodb.yml:

mysql:
  database: diego
  username: diego
  password: CHANGEME
  host: MYSQL_PROXY_IP
  port: 3306

postgresql:
  database: diego
  username: diego
  password: CHANGEME
  host: POSTGRES_IP
  port: 5524
  ssl_mode: disable


Now back to your cf.yml file, find the postgres job in your manifest:

Change

  - name: postgres
    release: cf

To

  - name: postgres
    release: cf
    provides:
      postgres: {as: pgdb}

Also add the following below the postgres job (please note that the dbnames/passwords in the seeded_databases needs to reflect the ones that we just used in the previous config files)

- default_networks:
  - name: cf1
  instances: 1
  name: mysql_proxy_z1
  networks:
  - name: cf1
    static_ips:
    - YOURSTATICIP
  resource_pool: small
  properties:
    cf_mysql:
      proxy:
        api_password: CHANGEME
  templates:
  - consumes:
      consul_client: nil
      consul_common: nil
      consul_server: nil
    name: consul_agent
    release: cf
  - name: proxy
    release: cf-mysql
    provides:
      mysql-database: {as: db}
  - name: metron_agent
    release: cf
  update:
    serial: true

- default_networks:
  - name: cf1
  instances: 1
  name: mysql_z1
  networks:
  - name: cf1
  persistent_disk: 102400
  resource_pool: large
  properties:
    cf_mysql:
      mysql:
        admin_password: CHANGEME
        cluster_health:
          password: CHANGEME
        galera_healthcheck:
          endpoint_password: CHANGEME
          db_password: CHANGEME
        seeded_databases:
          - name: ccdb
            username: ccadmin
            password: CHANGEME
          - name: uaadb
            username: uaaadmin
            password: CHANGEME
          - name: diego
            username: diego
            password: CHANGEME
  templates:
  - consumes:
      consul_client: nil
      consul_common: nil
      consul_server: nil
    name: consul_agent
    release: cf
  - name: mysql
    release: cf-mysql
  - name: metron_agent
    release: cf
  update:
    serial: true

Now find 3 entries in the manifest that refer to db_scheme: postgres and change them from:

    address: PG_STATIC_IP
    db_scheme: postgres
    port: 5524

to:

    address: MYSQL_PROXY_STATIC_IP
    db_scheme: mysql
    port: 3306

For the uaa database the naming is a bit different, the schema name is postgresql, not postgres.

For the diego database they use the following properties, change these accordingly as well:

db_host: MYSQL_PROXY_STATIC_IP
db_driver: mysql
db_port: 3306

Deployment:

Now the fun/scary part... deploying.

deploy your bosh manifest

bosh -d YOURDEPLOYMENTNAME deploy cf-mysql.yml

The deployment may fail but that is often due to some drain scripts. In my case it was the api_worker jobs that failed during the redeploy but it could be any job.
this can be solved by skipping the drain scripts and redeploying:
so in this case we force the job to stop and skip the drain scripts

bosh -d YOURDEPLOYMENTNAME stop api_worker_z1 --force --skip-drain
bosh -d YOURDEPLOYMENTNAME stop api_worker_z2 --force --skip-drain
bosh -d YOURDEPLOYMENTNAME deploy cf-mysql.yml

After a successful redeploy we are going to recreate the workers with the new config:

bosh -d YOURDEPLOYMENTNAME recreate api_worker_z1
bosh -d YOURDEPLOYMENTNAME recreate api_worker_z2

At this point the api servers will be pointed to the new mysql databases instead of the older postgres databases. If you log into CF at this point it will appear to be a completely new/clean environment. This is because we still need to migrate the data from postgres to mysql.

To perform the data migration we need to stop every job that connects to the databases:

  • uaa
  • api
  • api_worker
  • clock
  • diego_database

Loop through and stop each on of these, for example:

bosh -d YOURDEPLOYMENTNAME stop uaa_z1

Data Migration:

Now for the actual migration:
run the pg2mysql with the config files we previously populated:

pg2mysql -c db_configs/uaadb.yml migrate --truncate
pg2mysql -c db_configs/ccdb.yml migrate --truncate
pg2mysql -c db_configs/diegodb.yml migrate --truncate

SideNote! the truncate option will remove all the data in the target database, this is needed because MySQL is case sensitive and PostgreSQL is not

You can also use the tool to verify that all the data was migrated:

pg2mysql -c db_configs/uaadb.yml verify
pg2mysql -c db_configs/ccdb.yml verify
pg2mysql -c db_configs/diegodb.yml verify

If the tool successfully validates the migration you are ready to fire up all the vms we stopped in Cloud Foundry again:

bosh -d YOURDEPLOYMENTNAME start

At this point you can check and verify your CF if all apps are up and running.

please note that it can take a while before all apps are up and running again, depending on how many apps/cells you have.