Standalone Database Instance via BOSH

Goal: deploy and manage instances of Pivotal MySQL or Postgres without needing a PCF/PAS installation

This approach uses BOSH Bootloader, aka bbl, to deploy/configure a BOSH Director in a cloud.
Once that Director is deployed, the BOSH steps should run similarly, regardless of which cloud you've deployed to.

Here's the procedure (assuming you're running a Mac / OSX)

1. Install Dependencies.

2. Install bosh-bootloader. The IaaS-specific guides aren't consistent about the state directory, so do this now (name it whatever you prefer):

   $ mkdir some-state-dir
   $ cd some-state-dir
   

3. Follow the appropriate IaaS-specific guide. For example here are the exports we used for our GCP project:

   export BBL_IAAS=gcp
   export BBL_GCP_REGION=us-central1
   export BBL_GCP_SERVICE_ACCOUNT_KEY="</path/to/key.json>"

4. Once that part is finished, target the BOSH Director, so that you can move on to deploying your BOSH release. This entails the following, from within some-state-dir:

   $ eval "$(bbl print-env)"
   

5. Go up one directory level: $ cd ..

Follow the steps below for a standalone MySQL service instance

1. Clone the pxc-release repo: git clone https://github.com/cloudfoundry-incubator/pxc-release.git
2. We'll follow the instructions for deploying a standalone MySQL, with additional "aspects", such as creating a database, with a user and password.
3. There are a couple of prerequisites which aren't specifically called out in that procedure. The first of which is to upload the stemcell (takes about 5 minutes):

   $ bosh upload-stemcell \
     https://s3.amazonaws.com/bosh-aws-light-stemcells/250.29/light-bosh-stemcell-250.29-aws-xen-hvm-ubuntu-xenial-go_agent.tgz \
     --sha1 3e1dd5c8580208cb07cec5df490fbed6145ad907
   

4. The second prerequisite is to upload the BOSH release for bpm, a dependency:

   $ bosh upload-release --sha1 41df19697d6a69d2552bc2c132928157fa91abe0 \
     https://bosh.io/d/github.com/cloudfoundry-incubator/bpm-release?v=1.0.4
   

5. Now, upload the BOSH release for pxc-release (the MySQL release):

   $ bosh upload-release --sha1 eb53d366af2d6e49e8c2ac834191547b2ba44d30 \
     https://bosh.io/d/github.com/cloudfoundry-incubator/pxc-release?v=0.16.0
   

6. Create the ops file instructing BOSH to create a database and a user, using your own values for name, username, and password. Here's an example:

   $ cat  pxc-release/operations/seeded-databases.yml
   ---
   
   - type: replace
     path: /instance_groups/name=mysql/jobs/name=pxc-mysql/properties/seeded_databases?/name=demo_db?
     value:
       name: demo_db
       username: demo_user
       password: changeme
   

7. Deploy the MySQL release, which took 23 minutes in my case:

   pxc="pxc-release"
   ops="$pxc/operations"
   
   bosh -d pxc deploy $pxc/pxc-deployment.yml -o $ops/seeded-databases.yml
   

8. Run bosh vms to get the deployment name and instance ID needed to SSH into the VM:

   $ bosh vms
   Using environment 'https://10.0.0.6:25555' as client 'admin'
   
   Task 66. Done
   
   Deployment 'pxc'
   
   Instance                                    Process State  AZ  IPs        VM CID               VM Type  Active
   mysql/2e09b422-cab0-4d2f-846d-3ea8f3a7bf94  running        z1  10.0.16.4  i-05066433338553a1d  default  true
   
   1 vms
   
   Succeeded
   

9. SSH into the VM so that you can connect to MySQL:

   $ bosh ssh -d pxc mysql/2e09b422-cab0-4d2f-846d-3ea8f3a7bf94
   Using environment 'https://10.0.0.6:25555' as client 'admin'
   
   Using deployment 'pxc'
   
   Task 67. Done
   Unauthorized use is strictly prohibited. All access and activity
   is subject to logging and monitoring.
   Welcome to Ubuntu 16.04.6 LTS (GNU/Linux 4.15.0-47-generic x86_64)
   
   [...]
   
   mysql/2e09b422-cab0-4d2f-846d-3ea8f3a7bf94:~$
   
   

10. Use bosh ssh ... to create an SSH tunnel so that clients can connect to the DB

    $ bosh ssh -d pxc mysql/2e09b422-cab0-4d2f-846d-3ea8f3a7bf94 --opts="-L 0.0.0.0:13306:127.0.0.1:3306"
    

11. Now your MySQL client can connect to port 13306 on localhost

    $ mysql -h 127.0.0.1 -u demo_user -pchangeme demo_db -P 13306
    

Where to, next?

Consider trying out this Little MySQL Exercise, with Data

Follow the steps below for a standalone PostgreSQL service instance

1. Clone the postgres-release repo: git clone https://github.com/cloudfoundry/postgres-release.git

2. We will follow the instructions for deploying a standalone postgreSQL service instance. Since we already have a BOSH director, we will start with uploading a stemcell directly.

3. Upload the desired IAAS stemcell directly to bosh. bosh.io provides an easy interface to find and download stemcells.

   # Example for GCP
   $ bosh upload-stemcell https://bosh.io/d/stemcells/bosh-google-kvm-ubuntu-xenial-go_agent

4. Upload the latest release from bosh.io

   $ bosh upload-release https://bosh.io/d/github.com/cloudfoundry/postgres-release

5. Create the ops file instructing BOSH to create a database and a user, using your own values for port, database, role and password. Here's an example:

   $ cat  postgres-release/templates/operations/set_properties.yml
   ---
   
    - type: replace
      path: /instance_groups/name=postgres/jobs/name=postgres/properties?/databases/port
      value: 5524
   
    - type: replace
      path: /instance_groups/name=postgres/jobs/name=postgres/properties?/databases/databases/name=sandbox?
      value:
        name: sandbox
        citext: true
   
    - type: replace
      path: /instance_groups/name=postgres/jobs/name=postgres/properties?/databases/roles/name=pgadmin?
      value:
        name: pgadmin
        password: ((pgadmin_database_password))
        permissions:
        - "CONNECTION LIMIT 50"
   
    - type: replace
      path: /variables?/name=pgadmin_database_password?
      value:
        name: pgadmin_database_password
        type: password

6. Generate a manifest file using the command below:

   postgres-release/scripts/generate-deployment-manifest \
   -o templates/operations/set_properties.yml > postgres-deployment.yml
   

7. Deploy the postgres release using the generated manifest and inject a password:

   $ postgres="postgres-release"
   $ ops="$postgres/templates/operations"
   
   $ bosh -d postgres deploy postgres-deployment.yml -v pgadmin_database_password=foobarbaz

8. To ssh into the vm:

   $ bosh -d postgres ssh postgres

9. In order to use psql from your local machine, we need to setup port forwarding via a ssh connection as below:

   $ bosh -d postgres ssh postgres --opts="-L 0.0.0.0:5432:127.0.0.1:5524"
   

   In another shell, you can use psql to connect to port 5432 on your local machine to get to the postgres instance as below:

   $ psql -h 127.0.0.1 -U pgadmin -d sandbox

References not already linked above

Example ops file

Example of "seeded databases"

Postgres release

CF MySQL Deployment

Example of replacing values in YAML files

MySQL Backup Release