Differences

This shows you the differences between two versions of the page.

--- lab:kubernetes_app:step_4_-_mysql_server [2024/02/12 23:28] – updated user
+++ lab:kubernetes_app:step_4_-_mysql_server [2024/05/13 18:16] (current) – removed user
@@ Line 1: / Line 1: @@
-====== Step 4 - MySQL Server ======
-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
-====== Create MySQL Deployment ======
-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.
-===== Deployment =====
-Create the deployment file for the MySQL server.
-<file yaml 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
-</file>
-We can launch the deployment manually using kubectl:
-<code bash>kubectl apply -f k8s-deployment-sql.yml</code>
-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: <code bash>kubectl delete -f k8s-deployment-sql.yml</code>
-===== PersistentVolume =====
-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).
-<file yaml k8s-pv.yml>
-apiVersion: v1
-kind: PersistentVolume
-metadata:
-  name: my-pv
-spec:
-  capacity:
-    storage: 5Gi
-  accessModes:
-    - ReadWriteOnce
-  hostPath:
-    path: /data/my-pv
-</file>
-We can create the persistent volume manually using ''kubectl'':
-<code bash>kubectl apply -f k8s-pv.yml</code>
-Examine the output of the following commands
-  * ''kubectl get pv''
-  * ''kubectl describe pv''
-To remove it, run:
-<code bash>kubectl delete-f k8s-pv.yml</code>
-===== PersistentVolumeClaim =====
-Now we will create a "reservation" for space on the persistent volume and give it a name.
-<file yaml k8s-pvc.yml>
-apiVersion: v1
-kind: PersistentVolumeClaim
-metadata:
-  name: mysql-pvc
-spec:
-  accessModes:
-   - ReadWriteOnce
-  resources:
-   requests:
-    storage: 5Gi
-</file>
-We can create the persistent volume claim manually using kubectl (but the persistent volume needs to exist):
-<code bash>
-kubectl apply -f k8s-pvc.yml
-</code>
-Examine the output of the following commands
-  * kubectl get pvc
-  * kubectl describe pvc
-To remove it, run:
-<code bash>
-kubectl delete-f k8s-pvc.yml
-</code>
-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.
-===== Exposing MySQL with a Service =====
-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''.
-<file k8s-service-sql.yml>
-apiVersion: v1
-kind: Service
-metadata:
-  name: sql-service
-spec:
-  selector:
-    app: mysql
-  ports:
-    - protocol: TCP
-      port: 3306
-      targetPort: 3306
-</file>
-Since we are building everything in the default namespace, the full link to our service is:
-<code>mysql://sql-service.default.svc.cluster.local:3306/database_name</code>
-===== Deploying MySQL with Persistent Storage using Ansible =====
-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)
-<file yaml 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
-</file>
-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''
-====== Testing MySQL server ======
-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.
-===== Connecting to the MySQL Server Pod =====
-First, identify the pod name using
-<code>kubectl get pods</code>
-Next, use the pod name to connect interactively.
-<code>kubectl exec -it mysql-pod-name -- bash</code>
-For example,
-<code>kubectl exec -it mysql-deployment-6fd4f7f895-hd8dk -- bash</code>
-===== Login Using mysql Command =====
-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.
-===== Demonstrating Persistence =====
-Log back in to the pod and re-launch ''mysql''.
-Here we will:
-  - create a database
-  - select the new database
-  - create a table in the database
-  - insert a row
-  - show the data we inserted
-<code>
-CREATE DATABASE test;
-USE test;
-CREATE TABLE messages (message VARCHAR(255));
-INSERT INTO messages (message) VALUES ('Hello, world!');
-SELECT * FROM messages;
-</code>
-Now we will create an ansible playbook the remove not only the deployment but also the persistent storage.
-<file yaml 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
-</file>
-See what exists before you destroy everything:
-<code>kubectl get pod,node,deployment,pv,pvc,cm,seret</code>
-Run the playbook ''ansible-playbook destroy-sql.yml''
-See what exists after you destroyed everything:
-<code>kubectl get pod,node,deployment,pv,pvc,cm,secret</code>
-Deploy again
-<code>ansible-playbook deploy-sql.yml</code>
-Watch as everything comes back up:
-<code>kubectl get pod,node,deployment,pv,pvc,cm,secret</code>
-Identify the new pod name using
-<code>kubectl get pods</code>
-Connect to the new pod interactively. (again, substitute the actual pod name)
-<code>kubectl exec -it mysql-pod-name -- bash</code>
-Reconnect to mysql
-<code>mysql -uroot -pyourpassword</code>
-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.
-====== Next Step ======
-Continue to [[Step 5 - Application Pods]]
-Or back to [[Step 3 - Set Up Kubernetes]] or [[Start]]
-====== Optional ======
-You can restore a MySQL database dump to play with. Here is the command.
-<code>
-mysql -u [user name] –p [target_database_name] < [dumpfilename.sql]
-</code>
-<code>$ docker exec some-mysql sh -c 'exec mysqldump --all-databases -uroot -p"$MYSQL_ROOT_PASSWORD"' > /some/path/on/your/host/all-databases.sql</code>
-<code>$ docker exec -i some-mysql sh -c 'exec mysql -uroot -p"$MYSQL_ROOT_PASSWORD"' < /some/path/on/your/host/all-databases.sql</code>
-===== Allowing Remote Access =====
-We created a  user ''meme'' and granted permissions to connect from any IP address.
-<code>
-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>
-<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.