Get a proof of concept up in 15 minutes using Docker, MySQL and Grafana Visualization

I Sun

Follow

Dec 2, 2020 · 7 min read

Motivation

Almost as certain as the sun rising from the east and setting in the west is that eventually you’ll get tapped by management for a report on some pressing issue. How much time will you have to prepare it? Probably half the time you need to do a decent job of it.

Throughout my engineering career as a systems engineer (i.e. business analyst), my handy dandy go-to has always been Excel spreadsheets. With VBA and pivot tables, there wasn’t anything I couldn’t tackle. So why not keep using spreadsheets? Well, it was time to get out of my comfort zone and embrace/practice all the DevOps training my organization has been pushing.

The best way to bring about change is to show quick results and minimize the amount of maintenance to keep it going. If you tell management, you’re coming up with some automated process they worry you’ll take forever building some complicated system that will be impossible to maintain…and it’s true, I’ve seen many times when people make it way more complicated than it needs to be.

Not today though. Take my hand, join me on this journey to get a prototype running in 15 minutes or less while learning a couple of technologies:

• Docker — spinning up containers to get apps up and running quickly
• MySQL — creating a simple test database from scratch
• Grafana — leverage open source visualization to come up with fancy charts

Install Docker

Docker is an essential technology to learn as part of the DevOps engineer toolbox. The key terms to be familiar with:

• Docker image — represents an application and its virtual environment. There are libraries of ready-to-go Docker images you can grab such as from Docker Hub.
• Docker container — a virtualized run-time environment where users can isolate applications from the underlying system. These contain the running images which start quickly and easily.

Follow the Docker installation info for your platform of choice. After you’re done you should be able to type the following in a terminal to test that Docker is ready to go:

$ docker --version

Get Running with MySQL

What’s neat about Docker is how fast you can get a working MySQL database on your machine without having to be a database guru. It does this through another key DevOps concept called Infrastructure as Code.

In a nutshell, all the configuration has already been specified by somebody who (hopefully) knows what they’re doing as part of the Docker image so all you have to do is grab that image and run it (in a Docker container).

Let’s get rolling. Run the following Docker command:

• mysql — this is the name you’re specifying for the Docker container, feel free to use something else
• my-secret-pw — this is the password for the root account to the database. Obviously not the most secure, so you’ll want to change it if things get serious.
• latest — this lets Docker know that you want the latest version. You can enter a specific version here if you’re particular.

$ docker run --name mysql -e MYSQL_ROOT_PASSWORD=my-secret-pw -d mysql:latest

After you run this command you’ll see a flurry of action as Docker goes to work trying to grab a Docker image for MySQL and spin up a container. What is this black magic? When it can’t find the image locally it goes off and pulls it from Docker Hub.

$ docker run --name mysql -e MYSQL_ROOT_PASSWORD=my-secret-pw -d mysql:latestUnable to find image 'mysql:latest' locallylatest: Pulling from library/mysqlbb79b6b2107f: Pull complete49e22f6fb9f7: Pull complete...Status: Downloaded newer image for mysql:latestcb1661a2c070c5e4326e470c5311d5194c16c7948e7a2fa7522c5921b0baa004

After the Docker run command has done its thing, check to see if MySQL is running by typing:

$ docker ps -a

With the Docker container running, we can connect to it using an interactive terminal with the following command:

$ docker exec -it mysql mysql -uroot -p

After entering in the root password that we defined when executing the Docker run comment (“my-secret-pw”), you’ll be welcomed into the database world with a message like this:

Welcome to the MySQL monitor.  Commands end with ; or \g.Server version: 8.0.22 MySQL Community Server - GPLCopyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql>

Congratulations! That wasn’t too difficult right? A MySQL database running in just 2 lines of script.

Create Source Data

Now that we’ve got somewhere to store data, let’s feed it with some data so we have something to report on. In this example, we’ll create a table to store some test results over time.

Obviously, depending on what you’re working on there may be existing software suites out there such as Atlassian Jira for task management or HP Application Lifecycle Management that tracks testing against requirements. If your company is paying for the licenses, by all means use them if they meet your business needs.

What this example shows is what you can accomplish with some open-source software in a short amount of time. With enough programming know-how, the flexibility is there to pipe in data from different sources and run some data transformation.

So let’s get back to business. When we ran the Docker run command at the beginning it created a database using the name given to the container which was “mysql”. Run this command to use that database:

mysql> use mysql;

Now we create a simple table to store pass/fail/skip data for our fictional test runs:

myqsl> create table test_results(    id INT NOT NULL AUTO_INCREMENT,    tester VARCHAR(100) NOT NULL,    test_name VARCHAR(100) NOT NULL,    test_date DATE,    steps_passed INT,    steps_failed INT,    steps_skipped INT,    PRIMARY KEY ( id ));

We don’t want our reporting software querying our database as the root user so we set up a read-only “report user”:

CREATE USER 'reportuser' IDENTIFIED BY 'password';
GRANT SELECT ON mysql.test_results TO 'reportuser';
FLUSH PRIVILEGES;

Insert some records into the table:

mysql> INSERT INTO test_results(tester, test_name, test_date, steps_passed, steps_failed, steps_skipped)VALUES(’Tester1’, ‘procedure1’, ’2020-11-15’, 8, 1, 1);mysql> INSERT INTO test_results(tester, test_name, test_date, steps_passed, steps_failed, steps_skipped)VALUES(’Tester2’, ‘procedure1’, ’2020-11-16’, 9, 1, 0);

That’s it for the data. Next we grab Grafana to create the visuals for us.

Setting up Grafana

Let’s use Docker run again to get Grafana. Either open up a new terminal or enter “exit” to leave the MySQL interactive shell and enter:

docker run -d -p 3000:3000 grafana/grafana

This grabs Grafana and runs a container through internal port 3000. We now have two Docker containers running if you want to run this command again:

$ docker ps -a

Navigate to http://localhost:3000. Login using admin/admin and you’ll be presented with the Grafana dashboard. No need for monkeying around with downloading and installing files for a particular OS…it just works.

Now we’ll need to connect Grafana to a data source. The data source in this case will be the table we set up earlier in MySQL that contains test results.

We first need to find the IP address of the Docker container that MySQL is running in. To do that, use the “docker ps -a” command to obtain the container id of the MySQL container.

$ docker ps -a

It should looks something like the bolded text below. Run the following command (substituting the container ID of your MySQL instance) to obtain the IP address.

docker inspect f1fb2f8887b4 | grep "IPAddress"

From the Grafana dashboard, click on the card that says “Add your first data source”. You’ll enter the following:

• Host: Use the IP address obtained for the MySQL container, it should be running on port 3306 by default
• Database: Name of the database
• User/Password: Login credentials for the read-only report user we created

Click Save and Test to continue. Voila! We’ve got Grafana running and a data source configured. Now we’re cooking.

Create a Report Dashboard

From the Grafana dashboard, click on the card that says “Create your first dashboard”.

There’s a report query builder that loads up by default but it was easier for me to just jump to straight SQL. Enter a query such as the one below and right away you’ll see a preview of the results:

There you have it. During your lunch break you’ve created a reporting dashboard that is reading from a MySQL database. A more robust solution than a shared Excel spreadsheet.

Note: After Docker containers are stopped, the data isn’t persisted unless you’ve set up storage on the host system, read more about Docker volumes.

Takeaway

It’s hard not to like Docker. It simplifies all the setup so developers can focus on being productive and creating new things like this snazzy dashboard. If you want to dive into it even more, learn about Docker Compose which is geared toward configuring and running multi-Docker applications such as the one we just built up manually.