Accessing VMware SQL with MySQL for Kubernetes Instances

This topic describes how to access an instance of VMware SQL with MySQL for Kubernetes.

Prerequisites

Before accessing a MySQL instance, you must have:

The Kubernetes Command Line Interface (kubectl) installed. For more information, see the Kubernetes documentation.

(Optional) Verify MySQL Instance Settings

To see all MySQL instance settings configured:

Access the MySQL container.

kubectl -n DEVELOPMENT-NAMESPACE exec --stdin --tty pod/INSTANCE-NAME-0 -c mysql -- /bin/bash

In this command, replace:

DEVELOPMENT-NAMESPACE with the namespace of the MySQL instance
INSTANCE-NAME with the name of the instance

For example:

kubectl -n my-namespace exec --stdin --tty pod/mysql-sample-0 -c mysql -- /bin/bash

Review the configuration files /etc/mysql/conf.d/base.cnf and /etc/mysql/conf.d/autotune.cnf.

Get Root Access to the MySQL Server

Database administrative operations, such as creating users and databases, require connecting to MySQL as the root database user. Root account connections are only allowed from within the container running the database. Off-container root connections are not permitted.

To connect to the MySQL instance as the MySQL root user:

If the MySQL instance is configured for high availability, identify the primary (writable) Pod of the MySQL instance by running:

kubectl exec -it INSTANCE-NAME-0 -c mysql -- bash -c \
      "mysql -B -s --user=root --password=\$(cat $MYSQL_ROOT_PASSWORD_FILE) \
      --execute 'SELECT MEMBER_HOST FROM performance_schema.replication_group_members \
      WHERE (MEMBER_ROLE=\"PRIMARY\")'"

Where INSTANCE-NAME is the name of the instance.

For example:

kubectl exec -it mysql-sample-0 -c mysql -- bash -c \
      "mysql -B -s --user=root --password=\$(cat $MYSQL_ROOT_PASSWORD_FILE) \
      --execute 'SELECT MEMBER_HOST FROM performance_schema.replication_group_members \
      WHERE (MEMBER_ROLE=\"PRIMARY\")'"

mysql: [Warning] Using a password on the command line interface can be insecure.
mysql-sample-1.mysql-sample-members.default.svc.cluster.local

This command returns the Kubernetes-internal domain name of the primary node. The primary Pod name is the first dot-separated component of the command output. In the example above, the primary Pod name is mysql-sample-1.

Access the MySQL container by running:
```
kubectl exec --stdin --tty pod/POD-NAME -c mysql -- /bin/bash
```
Where POD-NAME is the name of the Pod: the MySQL instance name appended with an index of 0–2. If the MySQL instance is single-node, the index is 0.

For example:
```
kubectl exec --stdin --tty pod/mysql-sample-0 -c mysql -- /bin/bash
mysql@mysql-sample-0:/$
```

mysql -uroot -p$(cat $MYSQL_ROOT_PASSWORD_FILE)

Access the MySQL Server from an External IP Address

Note: This procedure requires that your cloud provider supports external load balancers. For more information about the LoadBalancer service type, see the Kubernetes documentation .

To connect to a MySQL instance from outside of your Kubernetes cluster, you must configure the Kubernetes service for the instance to be of type LoadBalancer.

To access the MySQL server from an external IP address:

Create a database user to use for the external connection. For more information, see Create a Database and Privileged MySQL User for the App in Connecting Apps to MySQL Instances.
Determine if the ServiceType is LoadBalancer or the default ClusterIP:
```
kubectl get service INSTANCE-NAME -o jsonpath={.spec.type}
```
In this command, replace INSTANCE-NAME with the value of the metadata.name property for the MySQL instance.

For example:
```
kubectl get service mysql-sample -o jsonpath={.spec.type}
LoadBalancer
```
If the ServiceType is not LoadBalancer, change the value of the Spec.ServiceType property to LoadBalancer.
For information about updating an instance, see Updating MySQL Instances. For information about the property, see Property Reference for the MySQL Resource.
Find the external IP address allocated for the service.
```
kubectl get service INSTANCE-NAME -o jsonpath={.status.loadBalancer.ingress[].ip}
```
In this command, replace INSTANCE-NAME with the value of the metadata.name property for the MySQL instance.

For example:
```
kubectl get service mysql-sample -o jsonpath="{.status.loadBalancer.ingress[*]['ip', 'hostname']}"
192.168.64.200
```
This command retrieves either an IP address or a resolvable DNS hostname.

If the command returns a DNS hostname, use the hostname in place of the IP address in the following examples. For example, an AWS load balancer returns a domain name instead of an IP address.
```
kubectl get service mysql-sample -o jsonpath="{.status.loadBalancer.ingress[*]['ip', 'hostname']}"
a4dc8de1biefe13112-17761231.us-west-2.elb.amazonaws.com
```
Log in to the MySQL server.
```
mysql -u USERNAME -pUSER-PASSWORD -P 3306 -h EXTERNAL-IP
```
In this command, replace:
- USERNAME with the name of the MySQL user (created in Step 1)
- USER-PASSWORD with the password you assigned to the MySQL user (created in Step 1)
- EXTERNAL-IP with the external IP address allocated for the MySQL service (retrieved in Step 3)
For example:
```
mysql -u report_admin -phunter2 -P 3306 -h a4dc8de1biefe13112-17761231.us-west-2.elb.amazonaws.com
```

Turn Off External Access

To deactivate off-platform connections:

In the YAML file for the instance, change the value of the Spec.ServiceType property to ClusterIP and update the MySQL instance. For more information about how to change a property, see Change Other Configurations in Updating MySQL Instances.

Note: When you change the Spec.ServiceType from LoadBalancer to ClusterIP, your cloud provider might automatically delete the associated load balancer for the MySQL instance.

Access MySQL From Cluster-Hosted Applications

Each MySQL instance has a Kubernetes service named after the instance. The service is used to connect an app to its MySQL database.

If an app is deployed on the Kubernetes cluster, the app can access the MySQL instance using the DNS name of the MySQL service. This DNS name is only resolvable within the Kubernetes cluster.

An app should connect to the database using a narrowly-privileged database user created for the specific requirements of the app. For information about creating a user for an app and connecting the app to a database using specific user and configuration information, see Connecting Apps to MySQL Instances.