Now that we have Kubernetes up and running, we will get our MySQL container up and running. We will need storage for the SQL database so that it isn't lost the pod is deleted or recreated. We also want the MySQL container/pod to run on a specific mode which has more resources.

References:

• https://hub.docker.com/_/mysql
• https://www.tutorialspoint.com/deploying-mysql-on-kubernetes-guide
• https://ubuntu.com/server/docs/databases-mysql
• https://stackoverflow.com/questions/15663001/remote-connections-mysql-ubuntu
• https://blog.devart.com/how-to-restore-mysql-database-from-backup.html
• https://medium.com/@shubhangi.thakur4532/how-to-deploy-mysql-and-wordpress-on-kubernetes-8ea1260c27dd

To Do:

• We have the secret creation set up, but we need to actually use it instead of the environment variables

To deploy MySQL on Kubernetes, we will use a Deployment object, which is a higher-level abstraction that manages a set of replicas of a pod. The pod contains the MySQL container along with any necessary configuration.

At the time of writing the latest mysql image being pulled is version 8.3.0 and runs on Oracle Linux Server 8.9. We will demonstrate using the base image, and mention an alternative image which enables the modern PDO driver.

There are three parts we will use

• deployment
• PersistentVolume (stored on the host aka Node)
• PersistentVolumeClaim

Finally, we will use Ansible to do the work.

Create the deployment file for the MySQL server.

k8s-deployment-sql.yml

apiVersion: apps/v1
kind: Deployment
metadata:
  name: mysql-deployment
spec:
  replicas: 1
  selector:
    matchLabels:
      app: mysql
  template:
    metadata:
      labels:
        app: mysql
    spec:
      nodeSelector:
        my-role: sql # restrict scheduling to the node with the label my-role: sql
      containers:
      - name: mysql
        image: mysql
        env:
        - name: MYSQL_ROOT_PASSWORD
          value: yourpassword
        ports:
        - containerPort: 3306
        lifecycle:
          postStart:
            exec:
              command: ["/bin/bash", "-c", "cp /docker-entrypoint-initdb.d/mysql.sql / && sleep 10 && /bin/mysql -uroot -pyourpassword -e \"SOURCE /mysql.sql\""]
        volumeMounts:
        - name: mysql-persistent-storage
          mountPath: /var/lib/mysql
        - name: mysql-script
          mountPath: /docker-entrypoint-initdb.d
        - name: mysql-bind
          mountPath: /etc/mysql/conf.d/mysqld.cnf
          subPath: mysqld.cnf
      volumes:
      - name: mysql-persistent-storage
        persistentVolumeClaim:
          claimName: mysql-pvc
      - name: mysql-script
        configMap:
          name: mysql-setup-script
      - name: mysql-bind
        configMap:
          name: mysql-bind

We can launch the deployment manually using kubectl:

kubectl apply -f k8s-deployment-sql.yml

What happened and what didn't happen? Examine the output of the following commands

• kubectl get pods
• kubectl get deployments
• kubectl describe deployments

Our manifest refers to something that doesn't exist yet!

To remove it, run:

kubectl delete -f k8s-deployment-sql.yml

By default, the MySQL pod does not have persistent storage, which means that any data stored in the pod will be lost if the pod is deleted or recreated. We are going to create a persistent volume that pods can mount. The actual storage is on the host (Node's file system).

k8s-pv.yml

apiVersion: v1
kind: PersistentVolume
metadata:
  name: my-pv
spec:
  capacity:
    storage: 5Gi
  accessModes:
    - ReadWriteOnce
  hostPath:
    path: /data/my-pv

We can create the persistent volume manually using kubectl:

kubectl apply -f k8s-pv.yml

Examine the output of the following commands

• kubectl get pv
• kubectl describe pv

To remove it, run:

kubectl delete-f k8s-pv.yml

Now we will create a “reservation” for space on the persistent volume and give it a name.

k8s-pvc.yml

apiVersion: v1
kind: PersistentVolumeClaim
metadata:
  name: mysql-pvc
spec:
  accessModes:
   - ReadWriteOnce
  resources:
   requests:
    storage: 5Gi

We can create the persistent volume claim manually using kubectl (but the persistent volume needs to exist):

kubectl apply -f k8s-pvc.yml

Examine the output of the following commands

• kubectl get pvc
• kubectl describe pvc

To remove it, run:

kubectl delete-f k8s-pvc.yml

If you create all three things, the pod will come up! But for now, we want to deploy all these with Ansible. And we want to run a script to grant create a user and grant permissions.

The MySQL server should be accessible from other deployments in Kubernetes, but secure from outside access. By creating a Service object, we create a name and port that can be used to connect to the MySQL server. The other pods will be able to use the DNS name sql-service.

apiVersion: v1
kind: Service
metadata:
  name: sql-service
spec:
  selector:
    app: mysql
  ports:
    - protocol: TCP
      port: 3306
      targetPort: 3306

Since we are building everything in the default namespace, the full link to our service is:

mysql://sql-service.default.svc.cluster.local:3306/database_name

This lab deploys everything in the “default” namespace.

• Leverages the .yml files created above
• Mounts the MySQL configuration script so it can be run during the deployment
• Mounts the persistent storage space for the MySQL server to use
• Mounts a custom mysql configuration file to enable connections from other pods (the bind-address statement)

deploy-sql.yml

---
- name: Deploy MySQL with persistent volume
  hosts: localhost
  connection: local
  tasks:
    - name: Create ConfigMap for database init
      kubernetes.core.k8s:
        state: present
        namespace: default
        definition:
          apiVersion: v1
          kind: ConfigMap
          metadata:
            name: mysql-setup-script
          data:
            mysql.sql: |
              CREATE DATABASE IF NOT EXISTS app_db;
              CREATE USER IF NOT EXISTS 'appuser'@'%' IDENTIFIED BY 'mypass';
              GRANT ALL ON *.* to 'appuser'@'%';
              CREATE USER IF NOT EXISTS 'appuser'@'localhost' IDENTIFIED BY 'mypass';
              GRANT ALL ON *.* to 'appuser'@'localhost';
              FLUSH PRIVILEGES;
              USE app_db;
              CREATE TABLE IF NOT EXISTS app_user (
              fname varchar(255),
              lname varchar(255),
              email varchar(255) Primary Key,
              password varchar(64),
              address varchar(255),
              gender varchar(5),
              contact varchar(15),
              dob date,
              login timestamp);
    - name: Create ConfigMap for binding
      kubernetes.core.k8s:
        state: present
        namespace: default
        definition:
          apiVersion: v1
          kind: ConfigMap
          metadata:
            name: mysql-bind
          data:
            mysqld.cnf: |
              [mysqld]
              bind-address = 0.0.0.0
    - name: Create Secret
      kubernetes.core.k8s:
        state: present
        definition: "{{ lookup('file', 'k8s-secret-sql.yml') }}"
        namespace: default
    - name: Create Deployment
      kubernetes.core.k8s:
        state: present
        definition: "{{ lookup('file', 'k8s-deployment-sql.yml') }}"
        namespace: default
    - name: Create PersistentVolume
      kubernetes.core.k8s:
        state: present
        definition: "{{ lookup('file', 'k8s-pv.yml') }}"
        namespace: default
    - name: Create PersistentVolumeClaim
      kubernetes.core.k8s:
        state: present
        definition: "{{ lookup('file', 'k8s-pvc.yml') }}"
        namespace: default
    - name: Create Service
      kubernetes.core.k8s:
        state: present
        definition: "{{ lookup('file', 'k8s-service-sql.yml') }}"
        namespace: default

Run: ansible-playbook deploy-sql.yml

Take a look at the results by running:

• kubectl get pod,node,deployment,pv,pvc,svc,cm,secret

Now confirm that the new MySQL pod is running on “node1”, where we want it.

• kubectl describe pod
• Look for the line similar to: Node: node1/192.168.99.202

Congratulations on your brand new MySQL server! In this step we are going do demonstrate that this MySQL server will retain its database across reboots, upgrades, and even deleting and re-creating the deployment.

First, identify the pod name using

kubectl get pods

Next, use the pod name to connect interactively.

kubectl exec -it mysql-pod-name -- bash

For example,

kubectl exec -it mysql-deployment-6fd4f7f895-hd8dk -- bash

After connecting interactively to the MySQL pod, test logging in from the command line

• mysql -uroot -pyourpassword
• mysql -uappuser -pmypass

Take a look at what is there by default: (don't forget the trailing semicolon;)

• show grants;
• show databases;
• select user,host from mysql.user;
• use database app_user;
• show tables;

Use exit or quit to exit.

Log back in to the pod and re-launch mysql.

Here we will:

1. create a database
2. select the new database
3. create a table in the database
4. insert a row
5. show the data we inserted

CREATE DATABASE test;
USE test;
CREATE TABLE messages (message VARCHAR(255));
INSERT INTO messages (message) VALUES ('Hello, world!');
SELECT * FROM messages;

Now we will create an ansible playbook the remove not only the deployment but also the persistent storage.

destroy-sql.yml

---
- name: Destroy MySQL with persistent volume
  hosts: localhost
  connection: local
  tasks:
    - name: Remove ConfigMap
      kubernetes.core.k8s:
        api_version: v1
        kind: ConfigMap
        name: mysql-setup-script
        state: absent
        namespace: default
    - name: Remove Secret
      kubernetes.core.k8s:
        state: absent
        definition: "{{ lookup('file', 'k8s-secret-sql.yml') }}"
        namespace: default
    - name: Remove Deployment
      kubernetes.core.k8s:
        state: absent
        definition: "{{ lookup('file', 'k8s-deployment-sql.yml') }}"
        namespace: default
    - name: Remove PersistentVolume
      kubernetes.core.k8s:
        state: absent
        definition: "{{ lookup('file', 'k8s-pv.yml') }}"
        namespace: default
    - name: Remove PersistentVolumeClaim
      kubernetes.core.k8s:
        state: absent
        definition: "{{ lookup('file', 'k8s-pvc.yml') }}"
        namespace: default
    - name: Remove Service
      kubernetes.core.k8s:
        state: absent
        definition: "{{ lookup('file', 'k8s-service-sql.yml') }}"
        namespace: default
- name: Clean hostPath directoy
  hosts: sql
  become: true
  tasks:
    - name: Clean hostPath directory
      file:
        path: /data/my-pv
        state: absent

See what exists before you destroy everything:

kubectl get pod,node,deployment,pv,pvc,cm,seret

Run the playbook ansible-playbook destroy-sql.yml

See what exists after you destroyed everything:

kubectl get pod,node,deployment,pv,pvc,cm,secret

Deploy again

ansible-playbook deploy-sql.yml

Watch as everything comes back up:

kubectl get pod,node,deployment,pv,pvc,cm,secret

Identify the new pod name using

kubectl get pods

Connect to the new pod interactively. (again, substitute the actual pod name)

kubectl exec -it mysql-pod-name -- bash

Reconnect to mysql

mysql -uroot -pyourpassword

Check the outputs of these commands:

• show databases;
• use test;
• show tables;
• SELECT * FROM messages;

Now let's clean up the test database and table:

• DROP TABLE messages;
• DROP DATABASE test;

Use exit or quit to exit mysql.

Continue to Step 5 - Application Pods

Or back to Step 3 - Set Up Kubernetes or Start

You can restore a MySQL database dump to play with. Here is the command.

mysql -u [user name] –p [target_database_name] < [dumpfilename.sql]

$ docker exec some-mysql sh -c 'exec mysqldump --all-databases -uroot -p"$MYSQL_ROOT_PASSWORD"' > /some/path/on/your/host/all-databases.sql

$ docker exec -i some-mysql sh -c 'exec mysql -uroot -p"$MYSQL_ROOT_PASSWORD"' < /some/path/on/your/host/all-databases.sql

We created a user meme and granted permissions to connect from any IP address.

CREATE USER 'meme'@'localhost' IDENTIFIED BY 'mypass';
CREATE USER 'meme'@'%' IDENTIFIED BY 'mypass';
GRANT ALL ON *.* TO 'meme'@'localhost';
GRANT ALL ON *.* TO 'meme'@'%';
FLUSH PRIVILEGES; 
EXIT;

<code>

What ports is MySQL listening on?

See the configuration files at:

• /etc/my.cnf
• /etc/my.cnf.d/ (none by default)
• /etc/mysql/conf.d/ (none by default)

Normally you would sudo vi /etc/mysql/mysql.conf.d/mysqld.cnf

• Locate and Modify the “bind-address” Option
  • Find the line that says bind-address = 127.0.0.1 (which restricts connections to localhost).
  • Either:
    • Comment it out: Add a # at the beginning of the line to disable it.
    • Change it to the server's IP address: If you prefer a specific IP, replace 127.0.0.1 with the IP of the MySQL server (e.g., bind-address = 192.168.99.17).
  • Save and Restart MySQL: sudo systemctl restart mysql

Since the bind-address isn't listed, how can we double-check? This image is Oracle Linux Server 8.9 and doesn't have netstat. Or ps.

It looks like MySQL is only using the Unix socket, not any IP address.