Deploy MySQL and phpMyAdmin with Docker

Databases are a key component of your docker containers. Without the ability to access data, you might find those containers to be a bit less-than-capable. To that end, you might deploy MySQL via a Docker container and then connect another container to the database for data. However, what about populating that database with data? Or what about managing the database? You can’t just pull down a docker image, deploy a container with it, and assume it will magically populate with the necessary data for your containerized application.

Right?

Now that we’re on the same page, you see the problem. How to make it easier to manage a containerized database. If you happen to be a database administrator, you’re probably perfectly at home on the command line. But if your database skills aren’t quite up to that task, how do you make all of this work?

One way of simplifying the management of your containerized MySQL database deployments is with the help of phpMyadmin. If you’ve never heard of this tool, it’s a web-based GUI that happens to be the de facto standard UI for MySQL and MariaDB database administration.

I want to walk you through the process of deploying a MySQL container and then connecting a phpMyAdmin container to that database, for easier management. With this process, you’ll find working with MySQL database container deployments so much easier.

I’ll be demonstrating this on Ubuntu Server 20.04, but the process will be the same on any platform that supports Docker.

Install Docker

I tend to like to start from scratch, so let’s get Docker up and running first. To do this, log into your Linux server and install the necessary dependencies with:

sudo apt-get install ca-certificates curl gnupg lsb-release -y

Next, add the official Docker GPG key with:

 curl -fsSL https://download.docker.com/linux/ubuntu/gpg | sudo gpg --dearmor -o /usr/share/keyrings/docker-archive-keyring.gpg

Add the stable Docker repository with the command:

echo "deb [arch=$(dpkg --print-architecture) signed-by=/usr/share/keyrings/docker-archive-keyring.gpg] https://download.docker.com/linux/ubuntu $(lsb_release -cs) stable" | sudo tee /etc/apt/sources.list.d/docker.list > /dev/null

Update apt and install Docker with:

sudo apt-get update
sudo apt-get install docker-ce docker-ce-cli containerd.io -y

Add your user to the docker group with:

sudo usermod -aG docker $USER

Log out and log back in, so the changes take effect.

You’re now ready to deploy the containers.

Deploy the MySQL Database

The first thing we’re going to do is create a volume for MySQL, so our data will remain persistent, in case the container fails. To create a volume named mysql-volume, issue the command:

docker volume create mysql-volume

With our volume ready, we’ll now deploy the MySQL container (named tns_mysql) and connect it to the volume. Our command will look like this:

docker run --name=tns_mysql -p3306:3306 -v mysql-volume:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=PWORD -d mysql/mysql-server

Where PWORD is a strong/unique password.

Here’s an explanation of the above command:

We’re running the tns_mysql container on the standard MySQL port 3306 (both internal and external), connecting our mysql-volume volume to the /var/lib/mysql directory within the container, creating a strong password for the admin account, and running the container (based on the mysql-server image pulled from DockerHub) in daemon mode (with the -d option).

The command should come back with a container ID (a long string of random characters), meaning your MySQL container has successfully deployed.

How to Deploy the phpMyAdmin Container

Now, we can deploy the phpMyAdmin container. Remember, we’re connecting it to our MySQL container (named tns_mysql). The first thing we need to do is create a volume for phpMyAdmin with:

docker volume create phpmyadmin-volume

With our volume ready, let’s deploy the phpMyAdmin container with the command:

docker run --name tns-phpmyadmin -v phpmyadmin-volume:/etc/phpmyadmin/config.usr.inc.php --link tns_mysl:db -p 82:80 -d phpmyadmin/phpmyadmin

The explanation of the above command is:

We’re deploying a container, named tns-phpmyadmin, using the phpmyadmin-volume volume, connecting it to the /etc/phpmyadmin/config.usr.inc.php phpMyAdmin file, linking it with the tns_mysql database on external port 82 and internal port 80, running the container in daemon mode (with the -d option) and basing it on the latest official phpmyadmin image.

How to Access phpMyAdmin

Alright, we’re now ready to access our containerized, web-based MySQL GUI. Make sure you know the IP address of the hosting server (which can be found with the command ip a). Open a web browser and point it to http://SERVER:82 (Where SERVER is the IP address of the hosting server).

You will be presented with the phpMyAdmin login (Figure 1).

Figure 1: The phpMyAdmin container is up and running and ready to go.

Log in with the username root and the password you used when you deployed the MySQL container. You should then find yourself at the main phpMyAdmin page (Figure 2).

Figure 2: The phpMyAdmin main page is ready for action.

Here’s the thing, you probably don’t want to be using the root account from your containers because that would be a security nightmare. Let’s create a new database and a new user with rights to that database (to be used by your containerized applications).

From the Databases tab, type the name of a new database and click Create (Figure 3).

Figure 3: Creating a new database from within the phpMyAdmin GUI.

After the database is created, click the Privileges tab and click Add user account. In the resulting window (Figure 4), fill out a username, type and verify a strong/unique password, and click Go at the bottom.

Figure 4: Adding a new user to our newly-created database.

There you go, you’ve created a new database with an associated user that has full privileges to that database. You can then use that user/database combination for your containerized application, without worrying about a security issue caused by using the MySQL root account. To make this even better, you can more easily manage your containerized databases, with the help of a world-class web-based GUI.