Prerequisites<\/h2>\n
To follow along with this tutorial, you need the following:<\/p>\n
- \n
- Minikube<\/a> and VirtualBox<\/a> installed locally \u2013 This tutorial uses Minikube v1.25.2 and VirtualBox 6.1.<\/li>\n
- Basic knowledge of Kubernetes<\/li>\n
- Basic knowledge of MySQL<\/li>\n<\/ul>\n
Setting up minikube<\/h2>\n
Minikube<\/a> is a popular tool used in the development of Kubernetes-based applications. It allows developers to create a local Kubernetes cluster for testing, debugging, and experimenting with Kubernetes features and applications. To use Minikube, a virtualization driver is needed. The driver creates and manages a virtual machine that hosts the Kubernetes cluster. This tutorial uses VirtualBox as the driver, which is free and open-source virtualization software that allows users to run multiple operating systems on a single machine.<\/p>\n
To create a Kubernetes cluster with Minikube and VirtualBox, you need to run the command:<\/p>\n
minikube start --driver=virtualbox<\/pre>\n
This command will initiate MiniKube with VirtualBox as its driver. At present, VirtualBox is the most stable driver available for Minikube.<\/p>\n
Set up MySQL on Kubernetes<\/h2>\n
Now we will set up MySQL \u2013 we will use a version of MySQL available on Docker Hub, authenticate it with credentials we\u2019ll help you set up, and then create a pod. We will also create a PersistentVolume<\/a> (PV) and PersistentVolumeClaim<\/a> (PVC) to work with storage inside your Kubernetes Cluster. PV sets aside storage resources in the cluster for MySQL, while PVC requests storage from the PV.<\/p>\n
To get started, you need to create a folder for all the Kubernetes YAML files. You will create the YAML file (my-storage.yaml) for storage first, which will include data for the PV and PVC, then paste the following code in it:<\/p>\n
apiVersion: v1\r\nkind: PersistentVolume\r\nmetadata:\r\n name: mysql-pv\r\nspec:\r\n capacity:\r\n storage: 250Mi\r\n accessModes:\r\n - ReadWriteOnce\r\n hostPath:\r\n path: \"\/var\/lib\/mysql\"\r\n---\r\napiVersion: v1\r\nkind: PersistentVolumeClaim\r\nmetadata:\r\n name: mysql-pv-claim\r\nspec:\r\n accessModes:\r\n - ReadWriteOnce\r\n resources:\r\n requests:\r\n storage: 250Mi<\/pre>\n
The
PersistentVolume<\/code> resource, namedmysql-pv<\/code>, defines a storage volume with a capacity of250Mi<\/code> (250 megabytes) and is using thehostPath<\/code> volume plugin, which allows the volume to be mounted from a file or directory on the host system. In this case, the volume is being mounted from the\/var\/lib\/mysql<\/code> directory on the host system, but make sure to change the path if you\u2019ve set a different directory for your MySQL data files.<\/p>\nThe
PersistentVolumeClaim<\/code> resource, namedmysql-pv-claim<\/code>, defines a request for a persistent volume with a capacity of250Mi<\/code> that can be mounted with read-write access by a single node at a time. This claim can then be used by other Kubernetes resources, such as a Deployment, to request storage resources.<\/p>\nNow send the above configuration to Kubernetes with the following command:<\/p>\n
kubectl apply -f my-storage.yaml<\/pre>\n
After you\u2019ve applied the configuration to Kubernetes, it\u2019s time to move on to the deployment and the service.<\/p>\n
Starting the Deployment and Service<\/h2>\n
Now, we\u2019ll create the deployment and the service for MySQL. Kubernetes will pull the Docker image
mysql:latest<\/code> from Docker Hub and use it to create a container that will run on Kubernetes. In Kubernetes, a Service provides a stable IP address for a set of Pods, allowing other parts of the application to easily access the Pods without needing to know their specific IP addresses or ports and since your MySQL image will require the values of the variables namedMYSQL_ROOT_PASSWORD<\/code>,MYSQL_DATABASE<\/code>,MYSQL_USER<\/code> andMYSQL_PASSWORD<\/code>, we will need to create those secret values before deployment.<\/p>\nYou can create these values by running the following commands (replace put-database-name, put-root-password, put-username, and put-user-password with values relevant to your database.) Also keep in mind that in Kubernetes, a secret may also contain more than one key\/value pair:<\/p>\n
kubectl create secret generic mysql-db --from-literal=database=put-database-name\r\n\r\nkubectl create secret generic mysql-root-pass --from-literal=password=put-root-password\r\n\r\nkubectl create secret generic mysql-user-pass --from-literal=username=putusername --from-literal=password=put-user-password<\/pre>\n
Here, the Kubernetes manifest creates a Service resource named “
mysql<\/code>” that exposes a port(3306)<\/code> on the nodes in the cluster via aNodePort<\/code>. The selector specified in the manifest ensures that the Service routes traffic to pods with the labelapp: mysql<\/code>.<\/p>\nTo get the deployment and Service going, create a new manifest file (mysql.yaml<\/em>) and paste in the following code.<\/p>\n
apiVersion: v1\r\nkind: Service\r\nmetadata:\r\n name: mysql\r\nspec:\r\n type: NodePort\r\n selector:\r\n app: mysql\r\n ports:\r\n - port: 3306\r\n targetPort: 3306\r\n nodePort: 30007\r\n---\r\napiVersion: apps\/v1\r\nkind: Deployment\r\nmetadata:\r\n name: mysql-deployment\r\n labels:\r\n app: mysql\r\nspec:\r\n replicas: 1\r\n selector:\r\n matchLabels:\r\n app: mysql\r\n template:\r\n metadata:\r\n labels:\r\n app: mysql\r\n spec:\r\n volumes:\r\n - name: mysql-pv\r\n persistentVolumeClaim:\r\n claimName: mysql-pv-claim\r\n containers:\r\n - name: mysql\r\n image: mysql:latest\r\n env:\r\n - name: MYSQL_ROOT_PASSWORD\r\n valueFrom:\r\n secretKeyRef:\r\n name: mysql-root-pass\r\n key: password\r\n - name: MYSQL_DATABASE\r\n valueFrom:\r\n secretKeyRef:\r\n name: mysql-db\r\n key: database\r\n - name: MYSQL_USER\r\n valueFrom:\r\n secretKeyRef:\r\n name: mysql-user-pass\r\n key: username\r\n - name: MYSQL_PASSWORD\r\n valueFrom:\r\n secretKeyRef:\r\n name: mysql-user-pass\r\n key: password\r\n volumeMounts:\r\n - name: mysql-pv\r\n mountPath: \/var\/lib\/mysql\r\n ports:\r\n - containerPort: 3306\r\n name: mysql<\/pre>\n
Explanation of the code above:<\/strong><\/p>\n
Here\u2019s what you need to know about the code block you\u2019ve just reviewed:<\/p>\n
- \n
spec<\/strong><\/code>: This section contains the specifications for the Kubernetes resource, such as the type of Service or the number of replicas for the Deployment.<\/li>\nselector<\/strong><\/code>: This section specifies the label selector for the Service or Deployment. In this case, the Service will match pods with theapp=mysql<\/code> label and the Deployment will select pods with theapp=mysql<\/code> label.<\/li>\ntype<\/strong><\/code>: This section specifies the type of Service being created. In this case, the type isNodePort<\/code>, which exposes the Service on a static port on each node in the cluster.<\/li>\nports<\/strong><\/code>: This section specifies the ports that the Service will listen on. In this case, the Service will listen on port 3306 and forward traffic to port 3306 on the pods.<\/li>\nvolumes<\/strong><\/code>: This section specifies the volumes that will be used by the container. In this case, there is one volume namedmysql-pv<\/code>, which is created by aPersistentVolume<\/code>.<\/li>\ncontainers<\/strong><\/code>: This section specifies the containers that will be run by the Deployment. In this case, there is one container namedmysql<\/code>, which is using themysql:latest<\/code> Docker image.<\/li>\nenv<\/strong><\/code>: This section specifies the environment variables that will be set for the container. In this case, there are four environment variables being set using secrets created from Kubernetes Secret resources. This was set earlier.<\/li>\nvolumeMounts<\/strong><\/code>: This section specifies the mount points for the volumes that will be used by the container. In this case, there is one volume namedmysql-pv<\/code>, which is mounted to the container’s\/var\/lib\/mysql <\/code>directory.<\/li>\ncontainerPort<\/strong><\/code>: This section specifies the port that the container will listen on. In this case, the container will listen on port 3306.<\/li>\n<\/ul>\nNow, apply your configuration by running the command below:<\/p>\n
kubectl apply -f mysql.yaml<\/pre>\n
This command applies a configuration to a resource from a given file. Let Kubernetes do its thing and after 5-10 minutes, run the following command to check if your pod is running the following command. Take note of the pod name \u2013 you will need it to interact with your database later on.<\/p>\n
kubectl get pods<\/pre>\n
If your pod is running, you should see something like the image below:<\/p>\n
![](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2023\/08\/word-image-97776-1.png\")
<\/p>\nIn this section, we have been able to create a pod where the MySQL server will run. We will need this is the next sections to work on the server.<\/p>\n
Interacting with the Database<\/h2>\n
Now, we will start an interactive shell to interact with our MySQL database. The interactive shell will enable us to interact with MySQL through the CLI. Start the interactive shell by running the following command (replace paste-pod-name with the name of the pod you\u2019ve just taken note of):<\/p>\n
kubectl exec --stdin --tty paste-pod-name -- \/bin\/bash<\/pre>\n
Within the interactive shell, run the following command to start the MySQL CLI as the root user (username you created earlier). You will be prompted to provide the password. Provide the password you used in this command:<\/p>\n
kubectl create secret generic mysql-root-pass --from-literal=password=put-root-password<\/pre>\n
This command was used to store the root password.<\/p>\n
Now, log in to your MySQL instance \u2013 provide a username and a password:<\/p>\n
mysql \u2013u [putusername] \u2013p<\/pre>\n
Your result should look like the image below.<\/p>\n
![](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2023\/08\/word-image-97776-2.png\")
<\/p>\nNow run the following command to create a new database:<\/p>\n
CREATE DATABASE catalog_database;<\/pre>\n
Now run the following MySQL command to list the databases available, and you should see all your databases including the one you just created.<\/p>\n
show databases;<\/pre>\n
![](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2023\/08\/word-image-97776-3.png\")
<\/p>\nNow navigate into any of your databases with the following command:<\/p>\n
use catalog_database;<\/pre>\n
Next, create a database table called Catalog with the following SQL statement.<\/p>\n
CREATE TABLE Catalog(\r\n CatalogId INTEGER PRIMARY KEY,\r\n Journal VARCHAR(25),\r\n Publisher VARCHAR(25),\r\n Edition VARCHAR(25),\r\n Title VARCHAR(45),\r\n Author VARCHAR(25)\r\n);<\/pre>\n
Add a row of data to the Catalog table with the following SQL statement.<\/p>\n
INSERT INTO Catalog \r\nVALUES('1','Muscle Magazine','Jude Mag', 'June 2013',\r\n 'How to grow muscles','Muhammed Ali');<\/pre>\nThe Catalog table gets created and a row of data gets added. You can checkout your data with the following statement:<\/p>\n
SELECT * \r\nFROM Catalog;<\/pre>\n
![](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2023\/08\/word-image-97776-4.png\")
<\/p>\nConclusion<\/h2>\n
In this article, we learned how to orchestrate the MySQL database using Kubernetes. We started with specifying the storage specification for the database, then moved to creating the deployment that runs the MySQL container. We also created a Kubernetes service so that the database can be accessed. Furthermore, we went further to interact with the database running in Kubernetes.<\/p>\n
Now, in the unlikely event of a database failure, a new instance will automatically be generated for you, ensuring that all your data remains intact and persistent.<\/p>\n
<\/p>\n","protected":false},"excerpt":{"rendered":"
If you\u2019ve been using MySQL for a while and want to learn how to orchestrate MySQL containers, you\u2019ve come to the right place. And while using Docker on its own to manage a single MySQL instance or multiple instances has certain drawbacks, such as lacking the ability to orchestrate multiple instances, scale, and provide services……<\/p>\n","protected":false},"author":342464,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143513,53,145792],"tags":[],"coauthors":[159021],"class_list":["post-97776","post","type-post","status-publish","format-standard","hentry","category-containers-and-virtualization","category-featured","category-mysql"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/97776","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/342464"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=97776"}],"version-history":[{"count":5,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/97776\/revisions"}],"predecessor-version":[{"id":97782,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/97776\/revisions\/97782"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=97776"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=97776"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=97776"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=97776"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}