July 21, 2023

Harness the Power of Cloud Director Data Solutions: Offer DBaaS using VMware SQL with MySQL

In the ever-evolving landscape of cloud technology, Cloud Service Providers (CSPs) play a crucial role in helping businesses unlock the potential of their data. Database as a Service (DBaaS) offerings have become essential tools for organizations looking to streamline their data management processes. This article will explore how our CSP is revolutionizing DBaaS with Cloud Director Data Solutions, powered by VMware SQL with MySQL. Continue reading to discover how this powerful combination can transform CSP and customers’ data management experience.

Introduction

The VMware Cloud Director Extension for Data Solutions is a game-changing plug-in for VMware Cloud Director (VCD). By incorporating data and messaging services into the VCD portfolio, it enables cloud providers and their tenants to access and manage services such as VMware SQL with MySQL, VMware SQL with PostgreSQL, and the efficient messaging system, RabbitMQ.

The extension operates in conjunction with Container Service Extension 4.0 or later (CSE 4.0+), facilitating the publication of popular data and messaging services to tenants. These services are deployed in a Tanzu Kubernetes Grid Cluster, which is managed by CSE 4.0+. This powerful combination simplifies the process of deploying and managing these services, while also offering data analytics and monitoring through Grafana and Prometheus.

 

How it works?

The VMware Cloud Director Extension for Data Solutions works hand-in-hand with CSE 4.0+ to provide cloud providers with the ability to publish data and messaging services to their tenants. In turn, tenants can utilize these services for building new applications or maintaining existing ones.

Services are deployed within a Tanzu Kubernetes Grid Cluster, which is managed by CSE 4.0+. This plays a crucial role in the deployment of services, with a service operator installed in a selected tenant Tanzu Kubernetes Cluster responsible for managing the entire lifecycle of a service, from inception to dissolution.

To better understand the architecture of the VMware Cloud Director Extension for Data Solutions, consider the high-level diagram provided below.

Data Solutions Extension Architecture
Figure 1: VMware Cloud Director Extension for Data Solutions Architecture

Use Cases

Tenants can utilize the VMware Cloud Director Extension for Data Solutions for various purposes, such as creating databases and messaging services at scale. Once these services are available in a tenant organization, authorized tenant users can create, upgrade, or delete PostgreSQL, MySQL, or RabbitMQ services. Advanced settings, such as enabling High Availability for VMware SQL with MySQL and VMware SQL with PostgreSQL, can be applied during the creation of a service.

In addition to creating database and messaging services, tenants can effortlessly manage their upgrades. When a newer version becomes available, the VMware Cloud Director Extension for Data Solutions interface will notify the user and prompt them to take action. Tenant administrators or users can then upgrade the chosen service with a single click, safeguarding the service against vulnerabilities and ensuring its stability.

Tenant self-service - Lifecycle Management

The Tenant self-service UI streamlines the lifecycle management of VMware SQL with MySQL, empowering Tenant users to efficiently provision, monitor, and scale their database instances with ease.

Step 1 - Installing VMware Cloud Director Data Solutions operator.

To run VMware SQL with MySQL in a Kubernetes cluster using the VMware Cloud Director extension for Data Solutions, you must install a VMware Cloud Director extension for the Data Solutions operator to a Kubernetes cluster. The VMware Cloud Director Data Solutions operator (DSO) is a backend service running within each tenant Kubernetes cluster. DSO manages the lifecycle of user resources in Kubernetes clusters upon user requests, sent through VMware Cloud Director Resource Defined Entities. The resources consist of both data solution operators, such as VMware RabbitMQ operators for Kubernetes, and data solution instances, like VMware RabbitMQ for Kubernetes. It deploys, upgrades, and updates various data solutions in Kubernetes clusters on behalf of the user.

  1. Log in to VMware Cloud Director extension for Data Solutions from VMware Cloud Director.
  2. Click Settings > Kubernetes Clusters (you should see all K8s Cluster here provisioned by CSE 4.x)
  3. Select the Kubernetes cluster where you want to run the  VMware Cloud Director extension for Data Solutions and click Install Operator.

A screenshot of a computer</p>
<p>Description automatically generated with low confidence
Figure 2: Install Operator

It takes a few minutes for the Operator Status of the cluster to change to Active.

A screenshot of a computer</p>
<p>Description automatically generated with medium confidence
Figure 3: Operator Installation Status

Step 2 - Installing VMware SQL with MySQL

  1. Log in to VMware Cloud Director extension for Data Solutions from VMware Cloud Director.
  2. Click Instances > New Instance.
  3. Enter the necessary details.
    1. Enter the instance name.
    2. Select the solution, for which you want to create an instance.
    3. Select the Kubernetes cluster for this instance.
    4.  you must enter only a default password
    5. Select a sizing template for this instance.
    6. To customize more details click Show Advanced Settings

A screenshot of a computer</p>
<p>Description automatically generated
Figure 4: Create Instance

  1. To connect to a MySQL instance from outside of your Kubernetes cluster, we must configure the Kubernetes service for the instance to be of type Load Balancer”. Select “Expose Service by Load Balancer”

 

A screenshot of a computer</p>
<p>Description automatically generated with medium confidence
Figure 5: Service Expose by Load Balancer

  1. Click Create.
  2. You can also track the progress of the deployment by running:
    • #kubectl get pods -n vcd-ds-workloads


Figure 6: Pod Status check

  1. After a few minutes, you should see MySQL status is “Running” in VCD GUI as well

A screenshot of a computer</p>
<p>Description automatically generated with medium confidence
Figure 7: Instance deployment status

  1. You can continue to see progress in the vCD taskbar as well as the Monitor section of vCD GUI; it is automatically creating required persistent volumes as well as network services like Load Balancer and NAT rules

A screenshot of a computer</p>
<p>Description automatically generated
Figure 8: Tasks & Events

  1. Kubernetes will request and Cloud Director will then allocate an external IP address (load balancer IP) to the service, which we can use to connect to the MySQL service from outside the cluster. You can view the Load Balancer IP by running:
    • kubectl get svc -A

A screenshot of a computer</p>
<p>Description automatically generated with low confidence
Figure 9: Service Status in Kubernetes Cluster

  1. Take note of the External IP, which in this case is 172.16.2.6, we will use this IP to connect to the MySQL cluster from outside

Step 3 - Connecting to VMware SQL with MySQL

To connect to a MySQL service using Workbench, you can follow these steps:

  1. Download and install MySQL Workbench from the official MySQL website if you haven't already.
  2. Launch MySQL Workbench on your computer.
  3. In the Workbench home screen, click on the "+" icon next to "MySQL Connections" to create a new connection.
  4. In the "Setup New Connection" dialog, enter a connection name of your choice.
  5. Configure the following settings:
    • Connection Method: Standard TCP/IP
    • MySQL Hostname: Enter the External IP address of the MySQL server. which in this case is 172.16.2.6
    • MySQL Server Port: Enter the port number for the MySQL service (default is 3306).
    • Username: Enter the MySQL username as “mysqlappuser”
    • Password: Enter the MySQL password as you entered while deploying MySQL
  6. Click on the "Test Connection" button to check if the connection is successful. If the test is successful, you should see a success message.
  7. Click on the "OK" button to save the connection settings.

A screenshot of a computer</p>
<p>Description automatically generated
Figure 10: Connection to Instance

A screenshot of a computer</p>
<p>Description automatically generated
Figure 11: Connection to Instance

A screenshot of a computer</p>
<p>Description automatically generated
Figure 12: Connected to instance successfully.

After following these steps, you should be connected to your MySQL service using MySQL Workbench. You can then use the Workbench interface to view this newly deployed instance, run queries, and perform various read-only database-related tasks. Operations are read-only because:

  • When Tenant creates a MySQL deployment through VMware Cloud Director for Data Solutions extension, the MySQL instance gets a default DB user named “mysqlappuser”
  • This mysqlappuser” user's privilege is limited to the default DB instance.
  • Themysqlappuser” user does not have adequate permissions to create a database, so we need to create a new user which has database permission to do so.

Step 4 – Enable a full-privileged DB user for your MySQL instance.

By default, MySQL has a built-in rootuser with administrative privileges in every MySQL deployment, but it doesn’t support external access. We will use this user to create another full-privileged DB user.

  1. Find DB root user's password by command
    • kubectl get secret -n vcd-ds-workloads.

A screenshot of a computer</p>
<p>Description automatically generated
Figure 13: Get-Credential

  • kubectl get secret mysqldb01-credentials -n  vcd-ds-workloads -o jsonpath='{’data}'

A picture containing text, screenshot, document</p>
<p>Description automatically generated
Figure 14: Get Credential

  • Take note of “rootPassword” from the output of the above command and copy the full encrypted password, which in this case starts with “RE1n**********”
  • Decode the above “rootPassword” using below command
    1. echo "<Root Password Copied Above>” | base64 –decode
    2. The output of the above command will be the root password, use this password to log in.
  • Enter the primary (writable) pod of your MySQL deployment by command: (Refer to this Link to identify the writable pod )
    1. kubectl exec -it mysqldb01-2   -n vcd-ds-workloads -c mysql – bash
  • Now run the below command to connect to the MySQL instance:
    1. mysql -u root -p$ADMIN_PASSWORD -h 127.0.0.1 -P 3306
    2. Enter the decoded password, once you successfully connected to the MySQL instance,
    3. Create a local MySQL user using:
      • CREATE USER 'user01'@'%' IDENTIFIED BY 'VMware1!';
      • GRANT ALL PRIVILEGES ON * . * TO 'user01'@'%';
  • That’s it! Now we can connect to this MySQL Instance and can create a new database as you can see below screenshot

A screenshot of a computer</p>
<p>Description automatically generated with medium confidence
Figure 15: Database Creation

By leveraging VMware Cloud Director Extension for Data Solutions, CSPs can unlock the power of DBaaS using VMware SQL with MySQL, offering tenants a comprehensive and efficient platform for their data management needs. With simplified deployment and management, seamless scalability, enhanced performance optimization, high availability, and robust security and compliance features, CSPs can provide businesses with a reliable and scalable DBaaS solution. Embrace the potential of DBaaS with VMware Cloud Director Extension for Data Solutions and VMware SQL with MySQL and empower your tenants with streamlined data management capabilities in the cloud. Check out the links below for more details:

https://docs.vmware.com/en/VMware-SQL-with-MySQL-for-Kubernetes/index.html

https://docs.vmware.com/en/VMware-Cloud-Director-extension-for-Data-Solutions/index.html

Filter Tags

Cloud Director Kubernetes VMware Cloud Providers VMware Sovereign Cloud Blog Technical Walkthrough Intermediate