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.
The MySQL 8.0 client installed

(Optional) Verify MySQL Instance Settings

To see all MySQL instance settings configured:

Access the MySQL container.
```
kubectl -n DEVELOPMENT-NAMESPACE exec -it pod/INSTANCE-NAME-0 -c mysql -- 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 -it pod/mysql-sample-0 -c mysql -- bash
```
Review the configuration files /etc/mysql/conf.d/base.cnf and /etc/mysql/conf.d/autotune.cnf.

Get Admin Access to the MySQL Server

The mysql-admin user has all privileges except SHUTDOWN, SUPER, REPLICATION SLAVE, and REPLICATION CLIENT. This user can connect from outside the container, so it can be used by external applications such as phpMyAdmin.

Common uses for this user are: - managing additional users and databases beyond those created by default - accessing tables in other schemas like performance_schema

For example, to connect to a single-node instance named mysql-sample with the user mysql-admin, open a connection to the database by port forwarding over 3306 in one session:

kubectl port-forward service/mysql-sample 3306

In another session, fetch the credentials for the mysql-admin user and connect over 127.0.0.1:

ADMIN_PASSWORD=$(kubectl get secret mysql-sample-admin-user-db-secret -o go-template='{{.data.password | base64decode }}')
mysql -u mysql-admin -p$ADMIN_PASSWORD -h 127.0.0.1 -P 3306

Note
The secret name will be <INSTANCE-NAME>-admin-user-db-secret.

Get Root Access to the MySQL Server

The root user has all privileges, including privileges omitted from the mysql-admin user.

Common uses for this user: - enabling system variable changes at runtime - flushing binary logs - managing client connections

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 pod/INSTANCE-NAME-0 -c mysql-sidecar -- \
         mysqlsh --credential-store-helper=tanzumysql \
         innodb-cluster-admin@localhost --sql \
         --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 pod/mysql-sample-0 -c mysql-sidecar -- \
     mysqlsh --credential-store-helper=tanzumysql \
     innodb-cluster-admin@localhost --sql \
     --execute="select MEMBER_HOST from performance_schema.replication_group_members \
     WHERE (MEMBER_ROLE=\"PRIMARY\")"

MEMBER_HOST
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 -it pod/POD-NAME -c mysql -- 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 -it pod/mysql-sample-0 -c mysql -- bash
mysql@mysql-sample-0:/$
```

mysql -u root -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', 'hostname']}"
```
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.