Tag Archives: DBaaS

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

Featured

Posted on by
Reply

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. Discover how this powerful combination can transform CSP and customers’ data management experience.

VMware Cloud Director Extension for Data Solutions: An Introduction

The VMware Cloud Director Extension for Data Solutions is a game-changing plug-in for the VMware Cloud Director. By incorporating data and messaging services into the VMware Cloud Director 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, 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 Container Service Extension 4.0 or later. This powerful combination simplifies the process of deploying and managing these services, while also offering data analytics and monitoring through Grafana and Prometheus.

How the VMware Cloud Director Extension for Data Solutions Functions

The VMware Cloud Director Extension for Data Solutions works hand-in-hand with the Container Service Extension 4.x 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 the Container Service Extension 4.0 or later. 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.

Use Cases for the VMware Cloud Director Extension for Data Solutions

Tenants can utilize the VMware Cloud Director Extension for Data Solutions for various purposes, such as creating database 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 UI for the lifecycle management of VMware SQL with MySQL

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 include both data solution operators like 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.

  • Log in to VMware Cloud Director extension for Data Solutions from VMware Cloud Director.
  • Click Settings > Kubernetes Clusters.
  • Select the Kubernetes cluster where you want to run VMware Cloud Director extension for Data Solutions, and click Install Operator.
  • It takes a few minutes for the Operator Status of the cluster to change to Active.

Step:2 – Installing VMware SQL with MySQL

  • Log in to VMware Cloud Director extension for Data Solutions from VMware Cloud Director.
  • Click Instances > New Instance.
  • Enter the necessary details.
    • Enter the instance name.
    • Select the solution, for which you want to create an instance.
    • Select the Kubernetes cluster for this instance. 
    • you must enter only a default passwordSelect a sizing template for this instance.
  • To customize more details click Show Advanced Settings
  • 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”
  • Click Create.
  • You can also track the progress of the deployment by running:
#kubectl get pods -n vcd-ds-workloads
  • After a few minutes, you should see MySQL status is “Running” in vCD GUI as well
  • 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
  • Kubernetes will request and Cloud Director 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
  • 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:

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

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, 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. “mysqlappuser” user does not have enough permission to create a database, we need to create a new user which has enough permissions to create a database.

Step:4 – Enable a full-privileged DB user for your MySQL instance provisioned by the VMware Cloud Director Extension for Data Solutions

MySQL by default has a built-in “root” user with administrator privilege in every MySQL deployment, but it doesn’t support external access. We will use this user to create another full-privileged DB user.

  • Find DB root user’s password by using below commands:
# kubectl get secret -n vcd-ds-workloads
#kubectl get secret mysqldb01-credentials -n  vcd-ds-workloads -o jsonpath='{.data}'
  • 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
# echo "RE1nem90TmltZ2laQWdsZC1oqM0VGRw==" | base64 –decode
  • 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 Writable Pod )
# kubectl exec -it mysqldb01-2 -n vcd-ds-workloads -c mysql – bash
  • Now run the below command to connect to the MySQL instance:
1 - Login to SQL 
      #mysql -u root -p$ADMIN_PASSWORD -h 127.0.0.1 -P 3306
2 - Enter decoded password, once you successfully connected to the MySQL
3 - Create a local MySQL user using:
      #CREATE USER 'user01'@'%' IDENTIFIED BY 'password';
      #GRANT ALL PRIVILEGES ON * . * TO 'user01'@'%';
  • That’s it, now we can connect to this SQL Instance and can create a new database as you can see below screenshot

NOTE: Thanks to the writer of this blog, https://realpars.com/mysql/, I am using this database for this blog article.

In Summary, by leveraging VMware Cloud Director Extension for Data Solutions, CSPs can unlock the power of DBaaS using VMware SQL with MySQL, offering customers 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 customers with streamlined data management capabilities in the cloud.