Enabling a full-privileged DB user for your MySQL instance provisioned by Cloud Director Data Solutions extension

Products

• VMware Cloud Director

Issue/Introduction

This article provides detail on how to enable a full-privileged MySQL DB user for your deployment.

Symptoms:

• When user creates a MySQL deployment through VMware Cloud Director for VMware Data Solutions extension, the MySQL instance gets a default DB user mysqlappuser.
• This mysqlappuser user's privilege is limited to the default DB instance.
• The mysqlappuser user cannot create additional DB instances.

Environment

VMware Cloud Director 10.x

Cause

This issue occurs when using VMware MySQL version 1.8 or below.
In the recent 1.8 release, VMware MySQL has made changes around the user account creation and this issue no longer occurs.

Please note, that the current version of Cloud Director Data Solutions extension, 1.1, does not support 1.8 but there are plans to add this support in future releases of the product.

Resolution

There is a built-in root user with administrator privilege in every MySQL deployment, but it doesn't support external access.
We can use this user to enable another full-privileged DB user.

With Cloud Director Data Solutions extension, the MySQL deployment is deployed into a TKGm Kubernetes cluster in Cloud Director tenant organization. 

Make sure you can run kubectl command to work with your TKGm Kubernetes cluster.

- For additional information on using kubectl, see the Kubectl Guide below.
- For additional information on managing your Kubernetes cluster, see the Kubernetes Container Clusters UI Plug-in for VMware Cloud Director below, specifically the "How do I Manage My clusters" section.

1. Find DB root user's password by command
   1. kubectl get secret -n vcd-ds-workloads.
      1. You are looking to verify the value for <MYSQL_INSTANCE_NAME>-credentials. 
2. Connect to the MySQL container
   1. kubectl exec -it <mysql_instance_name>-0 -n vcd-ds-workloads -c mysql -- bash.
      1. If unsure, you can locate the name of the Instance with the Cloud Director UI by going to the Instances section of the Data Solutions plugin.
3. Follow Accessing VMware SQL with MySQL for Kubernetes Instances to get the root access for your MySQL deployment.
   1. You can either grant admin privileges to the existing user mysqlappuser, or create another DB user with admin privileges.

Granting Admin Privileges
mysql> GRANT ALL PRIVILEGES ON * . * TO 'mysqlappuser'@'%';
mysql> FLUSH PRIVILEGES;
 
Creating another DB User with Admin Privileges
mysql> CREATE USER 'new_user'@'%' IDENTIFIED BY 'user_password';
mysql> GRANT ALL PRIVILEGES ON * . * TO 'new_user'@'%';
mysql> FLUSH PRIVILEGES;

Additional Information

Product documentation

• https://docs.vmware.com/en/VMware-Cloud-Director-extension-for-Data-Solutions/index.html 
• https://docs.vmware.com/en/VMware-SQL-with-MySQL-for-Kubernetes/index.html

Kubernetes Container Clusters UI Plug-in for VMware Cloud Director

• https://docs.vmware.com/en/VMware-Cloud-Director-Container-Service-Extension/4.0/VMware-Cloud-Director-Container-Service-Extension-Using-Tenant-4.0/GUID-4F6C444C-CCF5-4567-A74A-8307FCFBC732.html

Kubectl Guide

• https://kubernetes.io/docs/reference/kubectl/kubectl/