How to Setup Mysql Service with Kubernetes

In this article we are going to follow our series of article about kubernetes (k8s) ecosystem. In the current article we are going to look at the persistent part with k8s mainly, how to setup and get mysql up and running in your k8s cluster. As like in my previews kubernetes secrets article, I’ll be using minikube to demonstrate all my steps.

What we will learn

Creating pods
Exposing pods via services
Connecting to mysql image based pod
Wiring pods together

This article is organized as follows

Introduction
Creating mysql pod and exposing as service
Creating a nodejs api to hit mysql, pod and service
Getting data from mysql database using nodejs api
Conclusion

Note: I’ll be using the below alias in my terminal and kubernetes-mysql will be my working folder:

alias ctl='kubectl'

alias ccre='ctl create -f '

alias cdels='ctl delete service '

alias cdelp='ctl delete pod '

alias clog='ctl logs '

alias cdess='ctl describe service '

alias cdesp='ctl describe pod '

alias cgets='ctl get service'

alias cgetp='ctl get pods'

alias cgete='ctl get endpoints'

Introduction

We all know the big importance of data persistence and almost, all our applications rely hugely on some sort of database management systems (aka. DBMS). Therefore, setup a such DBMS in K8s is very important for a devops team, as I have mentioned in current article we are going to look close how to get mysql up and running in k8s from container, pod to service, and importantly I will go even further to illustrate how I can use my database mainly consume it through a simple API running in a separate pod. So, let’s get started!

I should mention that the current tutorial requires that you should have a basic understanding of the k8s' concepts mainly pods and services.

Creating mysql pod

First, I’m going to create a mysql based pod from the official latest mysql image, please note that I’m passing mysql_root_password as environment variable.

➜  kubernetes-mysql cat mysql-pod.yaml

apiVersion: v1

kind: Pod

metadata:

name: "k8s-mysql"

labels:

name: "lbl-k8s-mysql"

spec:

containers:

-

image: mysql:latest

name:  "mysql"

env:

-

name: "MYSQL_ROOT_PASSWORD"

value: "test"

ports:

-

containerPort: 3306

name:  "mysql"

volumeMounts:

-

name: "k8s-mysql-storage"

mountPath: "/var/lib/mysql"

volumes:

- name: "k8s-mysql-storage"

emptyDir: {}

➜  kubernetes-mysql ccre -f mysql-pod.yaml

pod "k8s-mysql" created

Check if our pod is running:

➜  kubernetes-mysql cgetp

NAME        READY     STATUS    RESTARTS   AGE

k8s-mysql   1/1       Running   0          5s

Check our pod’s logs:

➜  kubernetes-mysql clog k8s-mysql

Initializing database

2016-12-10T11:23:36.414301Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please...

MySQL init process done. Ready for start up.

…

2016-12-10T11:23:45.245972Z 0 [Note] mysqld: ready for connections.

Version: '5.7.16'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  MySQL Community Server (GPL)

Now we can connect to our pod to connect to our mysql server run exit twice to quit the mysql console and the bash:

➜  msg-api ctl exec -it k8s-mysql bash

[email protected]:/# echo $MYSQL_ROOT_PASSWORD

test

[email protected]:/# mysql --user=root --password=$MYSQL_ROOT_PASSWORD

mysql: [Warning] Using a password on the command line interface can be insecure.

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 2

Server version: 5.7.16 MySQL Community Server (GPL)

 

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

 

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

 

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

 

mysql> show databases;

+--------------------+

| Database           |

+--------------------+

| information_schema |

| mysql              |

| performance_schema |

| sys                |

+--------------------+

4 rows in set (0.00 sec)

 

mysql>exit

Bye

K8s comes with the service concept to expose pods to other pods or external systems, so that our pods will be reachable, more precise, with a service spec we can not only expose a pod but even define a set of policies/rules to access our pods. So, let’s create a service for our k8s-mysql pod created above:

➜  kubernetes-mysql ccre mysql-service.yaml

service "k8s-srv-mysql" created

Creating a nodejs api to hit mysql

In order to be able to connect to mysql from an other pod we need to have the ip address of our pod which can be done using:

➜  msg-api ctl get -o template pod k8s-mysql --template={{.status.podIP}}

172.17.0.4

Alright, now I’m going to create a sample node js app, to store a set of messages in database MESSAGES table, the app will have two endpoints:

‘/ping’ : to check the server health
‘/msg-api/all’ : to get all the stored messages

To keep things simple... the table will have only one column called TEXT.

First thing first, node app:

// api.js -> endpoints goes here

var express = require('express')
var mysql = require('mysql')

var Router = express.Router();
var ConnPool = mysql.createPool({
host: '172.17.0.4',
user: 'root',
password: 'test',
database: 'k8smysqldb'
})

// create database and MESSAGE table if not exist
ConnPool.query('CREATE DATABASE IF NOT EXISTS k8smysqldb', function (err) {
if (err) throw Error('\n\t **** error creating database **** ' + err)

console.log('\n\t ==== database k8smysqldb created !! ====')

ConnPool.query('USE k8smysqldb', function (err) {
if (err) throw Error('\n\t **** error using database **** ' + err);

console.log('\n\t ==== database k8smysqldb switched !! ====')

ConnPool.query('CREATE TABLE IF NOT EXISTS messages('
+ 'id INT NOT NULL AUTO_INCREMENT,'
+ 'PRIMARY KEY(id),'
+ 'text VARCHAR(100)'
+ ')', function (err) {
if (err) throw Error('\n\t **** error creating table **** ' + err);
})
})
})

/**
* /all
*/
Router.get('/all', function (req, res) {
ConnPool.getConnection(function (errConn, conn) {
if (errConn) throw Error('error get connection : ' + errConn)

conn.query('SELECT * FROM messages', function (errSelect, rows) {
if (errSelect) throw Error('error selecting messages : ' + errSelect)
res.writeHead(200, {
'Content-Type': 'application/json'
});
var result = {
success: true,
rows: rows.length,
}
res.write(JSON.stringify(rows));
res.end();
})
})
})

module.exports = Router

// server.js -> fire expressjs server

var express = require('express')
var msgApi = require('./api')

var app = express()

app.use('/msg-api', msgApi)

app.get('/ping', function (req, res) {
res.write("hello there! I m up and running!");
res.end();
})

app.listen(8080, function () {
console.log('\n\t ==== Message API listening on 8080! ====')
})

// Dockerfile -> bundle docker image for our app

FROM node:latest

RUN mkdir -p /usr/src/app
WORKDIR /usr/src/app

COPY package.json /usr/src/app/package.json
RUN npm i

COPY . /usr/src/app/

EXPOSE 8080
CMD [ "node", "server.js" ]

Now we can build our docker images from the Dockerfile

➜  msg-api docker build -t linoxide/msg-api:v0.0.3 . --no-cache=true

Sending build context to Docker daemon 9.728 kB

Step 1 : FROM node:latest

…

And push the built image to docker hub

➜  msg-api docker push linoxide/msg-api:v0.0.3

The push refers to a repository [docker.io/linoxide/msg-api]

c4477a160652: Pushed

32c1bac97782: Pushed

3d629e3d2e5a: Pushed

…

v1: digest: sha256:dba64e7ff64561f4af866fbbb657555cad7621688c7f312975943f5baf89efa2 size: 2628

Now we can create a pod of our nodejs app, the below spec file:

➜  msg-api cat msg-api-pod.yaml

apiVersion: v1

kind: Pod

metadata:

name: "k8s-msg-api"

labels:

name: "lbl-msg-api"

spec:

containers:

-

image: linoxide/msg-api:v0.0.1

name:  "msg-api"

ports:

-

containerPort: 8080

name:  "msg-api"

➜  msg-api ccre msg-api-pod.yaml

pod "k8s-msg-api" created

Make sure that the pod is running by checking the status:

➜  kubernetes-mysql cgetp

NAME          READY     STATUS    RESTARTS   AGE

k8s-msg-api   1/1       Running   0          22s

k8s-mysql     1/1       Running   0          5h

At this level we need to expose the created pod so that can access it from outside. This time I will do it using only command line rather a spec file:

➜  msg-api kubectl expose pod k8s-msg-api --port=8080 --name=k8s-srv-msg-api --type=NodePort

service "k8s-srv-msg-api" exposed

Getting data from mysql database using nodejs api

At this level, I need to point out some important staff, in order understand all the pieces, let’s first, summarize what we have done until now, so far, we have created a mysql pod and we have exposed it through a service to make it accessible for other pods, second, we have created a sample nodejs app, we called it a messaging api, so that we can use it to hit the mysql pod; similarly, to be able to access the messaging api we need to expose it through a service, I hope everything is clear until here!

Now the question is how can we call our messaging api from outside of our cluster mainly minikube? To do so, we need the IP address of our node, as I'm using minikube which create only one node so, the IP address is resolved, is the minikube ip address itself, just run:

➜  msg-api minikube ip

192.168.99.100

And what about the port? Well good question! let’s describe our messaging api service to check that out:

➜  msg-api cdess k8s-srv-msg-api

Name:           k8s-srv-msg-api

Namespace:      default

Labels:         name=lbl-msg-api

Selector:       name=lbl-msg-api

Type:           NodePort

IP:         10.0.0.170

Port:           <unset> 8080/TCP

NodePort:       <unset> 30887/TCP

Endpoints:      172.17.0.5:8080

Session Affinity:   None

No events.

So we have Port which is the port of our messaging api service. NodePort is the port on which the exposed service is available (accessible) i.e., the service is available on NodeIP:NodePort

Let’s try that out:

➜  msg-api curl 192.168.99.100:30887/ping

hello there! I m up and running!%

➜  msg-api curl 192.168.99.100:30887/msg-api/all

[]%

Very nice, so far we are able to hit our mysql database, let’s insert some data into our database using terminal.

➜  kubernetes-mysql kubectl exec -it k8s-mysql bash

[email protected]:/# mysql --user=root --password=$MYSQL_ROOT_PASSWORD

mysql: [Warning] Using a password on the command line interface can be insecure.

…

mysql> use k8smysqldb;

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

 

Database changed

mysql> show tables;

+----------------------+

| Tables_in_k8smysqldb |

+----------------------+

| messages             |

+----------------------+

1 row in set (0.01 sec)

 

mysql> insert into messages(text) values ('this is the first msg!');

Query OK, 1 row affected (0.01 sec)

mysql> insert into messages(text) values ('this is the second msg!');

Query OK, 1 row affected (0.01 sec)

Let’s get this data through our nodejs api using curl:

➜  msg-api curl 192.168.99.100:30887/msg-api/all

[{"id":1,"text":"this is the first msg!"},{"id":2,"text":"this is the second msg!"}]%

Conclusion

In this article we have seen the main step of working with a storage system, mainly mysql which still very used in our nowadays, we even built a small nodejs api to illustrate how a such mysql database can be consumed inside by another pod as well externally by hitting it from outside through the nodejs api. I guess the main steps shown in this article can be applied to build even a bigger system.