Accessing a Database as an Admin User

Note: In v2.9 and later, MySQL for VMware Tanzu is named VMware Tanzu SQL with MySQL for VMs.

This topic describes how to access a database as an admin user with either CredHub credentials or BOSH SSH.

Overview

You can access a database as an admin user to do actions that cannot be done as a normal binding user.

You can do the following actions as an admin user:

  • Add users
  • Create new schemas
  • View system schemas

To access your database service instance as an admin, choose one of the following ways:

Connect to MySQL with BOSH SSH

To connect to MySQL with BOSH SSH:

  1. BOSH SSH into your node by following the procedure in BOSH SSH in the Ops Manager documentation.

  2. Connect to your MySQL VM by running:

    mysql --defaults-file=/var/vcap/jobs/pxc-mysql/config/mylogin.cnf
    

Connect to MySQL with CredHub Credentials

To retrieve the admin credentials for a service instance from BOSH CredHub:

  1. Use the cf CLI to determine the GUID associated with the service instance for which you want to retrieve credentials by running:
    cf service SERVICE-INSTANCE-NAME --guid
    For example:
    $ cf service my-service-instance --guid
    
    12345678-90ab-cdef-1234-567890abcdef
    If you do not know the name of the service instance, you can list service instances in the space with cf services.
  2. Follow the steps in Gather Credential and IP Address Information and Log In to the Ops Manager VM with SSH of Advanced Troubleshooting with the BOSH CLI to SSH into the Ops Manager VM.
  3. From the Ops Manager VM, log in to your BOSH Director with the BOSH CLI. See Authenticate with the BOSH Director VM in Advanced Troubleshooting with the BOSH CLI.
  4. Find the values for BOSH_CLIENT and BOSH_CLIENT_SECRET:

    1. In the Ops Manager Installation Dashboard, click the BOSH Director tile.
    2. Click the Credentials tab.
    3. In the BOSH Director section, click the link to the BOSH Commandline Credentials .
    4. Record the values for BOSH_CLIENT and BOSH_CLIENT_SECRET.
  5. Set the API target of the CredHub CLI to your BOSH CredHub server by running:
    credhub api https://BOSH-DIRECTOR-IP:8844 \
          --ca-cert=/var/tempest/workspaces/default/root_ca_certificate
    Where BOSH-DIRECTOR-IP is the IP address of the BOSH Director VM.

    For example:
    $ credhub api https://10.0.0.5:8844 \
          --ca-cert=/var/tempest/workspaces/default/root_ca_certificate
  6. Log in to CredHub by running:
    credhub login \
        --client-name=BOSH-CLIENT \
        --client-secret=BOSH-CLIENT-SECRET

    For example:

    $ credhub login \
          --client-name=credhub \
          --client-secret=abcdefghijklm123456789
  7. Use the CredHub CLI to retrieve the credentials by doing one of following :

    • Retrieve the password for the admin user by running:
      credhub get -n /p-bosh/service-instance_GUID/admin_password
      In the output, the password appears under value. Record the password.
      For example:
      $ credhub get \
        -n /p-bosh/service-instance_70d30bb6-7f30-441a-a87c-05a5e4afff26/admin_password 
      id: d6e5bd10-3b60-4a1a-9e01-c76da688b847 name: /p-bosh/service-instance_70d30bb6-7f30-441a-a87c-05a5e4afff26/admin_password type: password value: UMF2DXsqNPPlCNWMdVMcNv7RC3Wi10 version_created_at: 2018-04-02T23:16:09Z
    • Retrieve the password for the read-only admin user by running:
      credhub get -n /p-bosh/service-instance_GUID/read_only_admin_password
      In the output, the password appears under value. Record the password.
  8. Record the IP of your service instance. See Connect Using an IP Address.
  9. Connect to your database by doing one of following:
    • Connect using a management tool. See Using Management Tools for Tanzu SQL for VMs.
    • Connect directly from your workstation using the MySQL client by running:
      mysql -h IP-ADDRESS -u admin -P 3306 -p
      When prompted for a password, enter the password you recorded.