RETROSPECTIVE

March 27th, 2022

Running a MySQL Database Client on Kubernetes

MySQL

Kubernetes

Terraform

AWS

s

This is part of a series of articles on SaintsXCTF Version 2.0. The first article in the series provides an overview of the application. You DO NOT need to read prior articles in the series to fully understand this article.

In the first version of my SaintsXCTF application, one underdeveloped aspect of the technology stack was the MySQL database infrastructure. The only way to access the production database was to create a bastion host and interact with it via the command line. This bastion host was a server that was only accessible from my IP address and could only interact with my MySQL database. All other network ports were closed.

While this was an okay start, I really wanted a user interface to interact with the MySQL database with similar functionality to a local MySQL IDE, such as DataGrip. After researching different options, I decided to use phpMyAdmin, a MySQL administrative client that can run on a web server.

My approach is to run phpMyAdmin on a container within a Kubernetes deployment. This way, I can create and destroy the phpMyAdmin client quickly depending on my needs.

In this article, I'll give an overview of phpMyAdmin and share the infrastructure needed to create it.

phpMyAdmin is a web application that allows administrators to view MySQL databases, tables, and more. Administrators can also execute queries and other SQL statements in the web application UI. In many ways, phpMyAdmin operates the same as a desktop SQL IDE.

When loading the webpage for phpMyAdmin, users are presented with the following screen.

From here, database connection details are entered. If phpMyAdmin is successful in connecting to the database, the following webpage is displayed.

Multiple options are presented on this page, such as viewing schemas within the database or writing SQL statements. SQL statements can be entered into a text box and executed, with results shown in the UI.

All the infrastructure for the phpMyAdmin database client is written as code using Terraform. The phpMyAdmin server is a container running within a Kubernetes cluster. My Kubernetes cluster is hosted using AWS EKS, with the cluster infrastructure existing in an eks module within my global-aws-infrastructure repository.

My phpMyAdmin server infrastructure has its own Terraform module named database-client, which exists in my saints-xctf-infrastructure repository. This diagram shows all the infrastructure components.

Most of the infrastructure code exists within a main.tf file. The most important resource for the web server, the Kubernetes Deployment object, has the following setup.

resource "kubernetes_deployment" "deployment" { metadata { name = "saints-xctf-database-client-deployment" namespace = "saints-xctf" labels = { version = local.version environment = "all" application = "saints-xctf-database-client" } } spec { replicas = 1 min_ready_seconds = 10 strategy { type = "RollingUpdate" rolling_update { max_surge = "1" max_unavailable = "0" } } selector { match_labels = { version = local.version environment = "all" application = "saints-xctf-database-client" } } template { metadata { labels = { version = local.version environment = "all" application = "saints-xctf-database-client" } } spec { affinity { node_affinity { required_during_scheduling_ignored_during_execution { node_selector_term { match_expressions { key = "workload" operator = "In" values = ["development-tests"] } } } } } container { name = "saints-xctf-database-client" image = "phpmyadmin/phpmyadmin:latest" readiness_probe { period_seconds = 5 initial_delay_seconds = 20 http_get { path = "/" port = 80 } } env { name = "PMA_ARBITRARY" value = "1" } port { container_port = 80 protocol = "TCP" } } } } } }

The Kubernetes Deployment runs a phpmyadmin/phpmyadmin:latest container, which starts a phpMyAdmin web server. Importantly, a PMA_ARBITRARY environment variable on the container is given a value of 1. This determines that any arbitrary MySQL database can be accessed from phpMyAdmin1. The alternative is to make phpMyAdmin purpose-built for a single MySQL database.

The remainder of the infrastructure deals with networking and setting up a domain name for the phpMyAdmin server. You can view it in the main.tf file, and read more about how this setup works in my SaintsXCTF Kubernetes Infrastructure article.

Creating a phpMyAdmin server on Kubernetes is a quick way to administer a MySQL database. It can be spun up and down at a moments notice, and provides similar functionality to a desktop IDE application. All the code discussed in this article is available on GitHub.

[1] "Official phpMyAdmin Docker image", https://hub.docker.com/r/phpmyadmin/phpmyadmin/