How to Setup and Use MariaDB on Docker Container

I’m following my series of articles about the rock stars of the devops ecosystem. In today's article I will show you how to setup and use MariaDB on docker container and as bonus we will be able to reuse the simple nodejs api used in my previews article to show you how we can interact with MariaDB from another container.

What we will learn

• Installing, Creating and running MariaDB container
• Hitting MariaDB server from another container

The current article is organized as follows

How to install docker on ubuntu AWS...

To view this video please enable JavaScript, and consider upgrading to a web browser that supports HTML5 video

How to install docker on ubuntu AWS Server

• Introduction
• Starting MariaDB instance
• Connecting to MariaDB from outside
• Stopping and deleting MariaDB container
• Conclusion

Introduction

By way of introduction, I would like to mention some interesting points regarding MariaDB. The first point is that MariaDB has been created with same guy that has created Mysql, in fact, MariaDB starts as a fork of Mysql's repo. In 2008 has been acquired by Oracle, after a year, seems like Mr. Widenius the founder of Mysql does not find a common ground with the firm, thus, he left Oracle to create MariaDB. Second point, MariaDB ensure that the product will still 100% open source and free therefore, all the patches, updates and fixes, for security and others are open and accessible for everyone. The Third point is that the community of MariaDB ensure that this latter will still fully compatible with the Mysql's APIs. In the last point, I would mention that several benchmarks show that MariaDB out perform Mysql. I’ll stop here and start our main aim which is MariaDB under docker.

Starting mariadb instance

In order to use mariadb with a container, we have two options, which are valid for any software not only mariadb, the first option is to find an image in which mariadb is installed with all the needed dependencies and all we have to do is to fire a container from this image; the second option which a little bit complicated is to build an image for ourselves on an operating system for instance Ubuntu, Debian, CentOs etc, therefore, we must install MariaDB and the needed dependencies too. As you may know, the most used and convenient way is the first one, almost all the software providers now provide their official docker image; so, l’ll go with the first option too. First thing, I have to find and install MariaDB image, to do so, just connect to the docker hub (hub.docker.io) and search using the word ‘mariadb’, the official images are always tagged official as shown in the screenshot below:

Let’s create a Dockerfile to create a mariadb container instance:

➜  docker-mariadb git:(master) ✗ cat Dockerfile

FROM mariadb:latest

ENV MYSQL_ROOT_PASSWORD test

Our docker file will use mariadb latest image and we also fill an env variable the MYSQL_ROOT_PASSWORD via the ENV KEY VALUE command more detaill is given below:

Let’s build the MariaDB image using docker build command

➜  docker-mariadb git:(master) ✗ docker build -t linoxide/mariadb:v1 .

Sending build context to Docker daemon 34.82 kB

Step 1 : FROM mariadb:latest

latest: Pulling from library/mariadb

…

Successfully built a33014d19bcb

Once the image is built, we can use it to run our container, like so:

➜  ~ docker run -d linoxide/mariadb:v1

35d67d1643b44f1063500e38805e5c54545540c37d1c0179acb5cc953076ee09

Note:

If you don’t want to use Dockerfile, you can run a container just using a command line, like below, but Dockerfile is preferred as it allows us to save our building config, for instance in our case it was just a simple env variable MYSQL_ROOT_PASSWORD but you can imagine a situation with a bunch of settings.

Let’s list our containers:

➜  ~ Docker ps

Container id        image                 command                  created             status              ports               names

35d67d1643b4        linoxide/mariadb:v1   "docker-entrypoint.sh"   5 minutes ago       up 5 minutes        3306/tcp            silly_davinci

Connecting to our container bash using the container ID (look at the result of the last command above). Once connected, we can start the database server using mysql --user=root --password=$MYSQL_ROOT_PASSWORD; at the time of writing mysql still the entry point to start mariadb server:

➜  ~ docker exec -it 35d67d1643b4 bash

root@35d67d1643b4:/# mysql --user=root --password=test

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

Your MariaDB connection id is 2

Server version: 10.1.19-MariaDB-1~jessie mariadb.org binary distribution

 

Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.

 

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

 

MariaDB [(none)]> show databases;

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

| Database           |

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

| information_schema |

| mysql              |

| performance_schema |

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

3 rows in set (0.00 sec)

 

MariaDB [(none)]>

Use ‘exit’ to quit the mariadb console and a second 'exit' to quit the container bash.

To check the logs of the server, use docker logs docker_id command.

Notes:

Below are some env variables that we can use for instance in our Dockerfile:

• MYSQL_ROOT_PASSWORD [mandatory]: to specify a password of root user of the db server
• MYSQL_DATABASE [optional]: if we want to create a specific database by name at the startup of the db server
• MYSQL_USER, MYSQL_PASSWORD [optional]: if we want to create a new user at the startup, so we can specify the name and the password of the user
• MYSQL_ALLOW_EMPTY_PASSWORD [optional]: if equals to yes, the container will start allowing a blank password for the root user
• MYSQL_RANDOM_ROOT_PASSWORD [optional]: assign a random password to the root user at the startup, the password will be printed in the STDOUT

Connecting to mariadb from outside

Now that we have create a  database server we can hit it from outside, ideally, for us in this article we can use another container to do that. So, here I’ll reuse my sample nodejs app created in a previews article to fire a container. First of all, go ahead and check the IP address of our MariaDB container by looking at the 「IPAddress」 line in the output of the command below:

➜  msg-api git:(master) ✗ docker inspect 35d67d1643b4

"IPAddress": "172.17.0.2"

Please note I’ll change the IP address of my nodejs app and I’ll build a new image, for the details please refer to my previews article as mentioned above, otherwise you can follow the below steps as I have already pushed out the docker image to docker hub, docker will find and pull it for you from docker hub:

➜  msg-api git:(master) ✗ docker build -t linoxide/msg-api:v0.0.5 .

Sending build context to Docker daemon 9.728 kB

Step 1 : FROM node:latest

…

Successfully built e0f9596397a3

Now let’s run a docker container with port-forward to be able to hit it from outside:

➜  msg-api git:(master) ✗  docker run -d -p 8080:8080 --name msg-api linoxide/msg-api:v0.0.5

8f70e4261bc6676467fe4f59fc67fa7e4659860d20f2e55dd3dec48ed3ee0d70

let’s check our app health using the '/ping' endpoint:

➜  docker-mariadb git:(master) ✗ curl localhost:8080/ping

hello there! I m up and running!%

Now I’m going to connect to mariadb server and insert some data:

➜  msg-api git:(master) ✗ docker exec -it 35d67d1643b4 bash

root@35d67d1643b4:/# mysql --user=root --password=test

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

Your MariaDB connection id is 8

Server version: 10.1.19-MariaDB-1~jessie mariadb.org binary distribution

 

Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.

 

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

 

MariaDB [(none)]> show databases;

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

| Database           |

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

| information_schema |

| k8smysqldb         |

| mysql              |

| performance_schema |

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

4 rows in set (0.04 sec)

MariaDB [(none)]> 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

MariaDB [k8smysqldb]> insert into messages values 'first faked msg over here';

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ''first faked msg over here'' at line 1

MariaDB [k8smysqldb]> insert into messages(text) values('first faked msg over here');

Query OK, 1 row affected (0.02 sec)

MariaDB [k8smysqldb]> insert into messages(text) values('Second faked msg over here');

Query OK, 1 row affected (0.03 sec)

MariaDB [k8smysqldb]>

Now, I’m going to use our nodejs app to retrieve this data using msg-api/all endpoint:

➜  docker-mariadb git:(master) ✗ curl localhost:8080/msg-api/all

[{"id":1,"text":"first faked msg over here"},{"id":2,"text":"Second faked msg over here"}]%

So everything is working find.

Stopping and deleting mariadb container

Once you finished with your docker containers and you want to delete them just run:

• docker ps –a to list all containers,
• find containers’ ids you want to delete
• make sure to stop them first otherwise you will get an error, to do so, docker stop your_container_id1 your_container_id1 …
• then run docker rm your_container_id1 your_container_id1 … to delete them

Conclusion

To sum up, in this article we have seen how to get an up and running MariaDB server using docker container. We have seen also both, how to connect to the container itself and run the database server using mysql program (yeah as mariadb is fully compatible with mysql) and how to communicate with mariadb database from another container.