Dockerize Your Databases: A Step-by-Step Guide to MySQL Containerization

Photo by Campaign Creator from Unsplash.

In my last article, we went through the concept of virtual machines and docker containers. Docker containers can run instances of programming languages and databases instead of installing the database applications on your local machines. In this article, we will be running an instance of Mysql database and we will be using the MySQL workbench as our client to communicate with the database server. After this article, we should have done the following:

1. Pull Mysql docker image from docker hub
2. Run Mysql docker container with MySQL image.
3. Download Mysql Workbench.
4. Run Mysql server inside the Container.
5. Run Mysql server outside the Container.
6. Establish a connection between Mysql container server and to Mysql Workbench.
7. Create a Database.
8. Create a Table in the Database.

1 . Pull the Mysql Docker Image from the Docker Hub.

Firstly, you need to have docker installed on your local machine. In caseIn case you do not have docker installed on your system, you can refer to this article to do so. So now to get the MySQL server image from docker hub, we would run the following command on your terminal:

docker pull mysql

to verify if the image has been been pull, you would run the following docker command on your terminal:

docker image ls

You should see the MySQL image in the list of the images outputted from the terminal

Image by Author.

If you are using VScode and you have the docker plugin connected to your IDE, you should also see the image under the images drop-down on the docker pane.

We can verify that we have our MySQL image present on our local machine, we can now go ahead and and run our MySQL docker container with our image.

2. Run Mysql Docker Container with MySQL Image.

To run our Mysql docker container instance, we need to run the following command on the terminal:

docker run --name my-sql-server -p 3307:3306 -e MY_SQL_ROOT_PASSWORD=root -d mysql

Command Explanation:

• docker run: This command is used to build docker containers from a docker image.
• — name my-sql-server: This command is used for naming our container, we have successfully given our container the name tag my-sql-server.
• -p 3307:3306: The -p command stands for port, it is used to map ports from the host system to the the container. 3307:3306 is in the format of, it simply means that port 3307 on local host is mapped to 3306 on container. So if I need to run my container on my local machine, I run localhost:3307.
• -e MY_SQL_ROOT_PASSWORD=root: The -e tag sets environment variable. We set up an environment variable with the name MY_SQL_ROOT_PASSWORD and assign a value of root to it.
• -d MySQL: The -d flag is used to run containers in detached mode, it simply means that docker would run the container on the background as a daemon, allowing you to use the terminal for another task. The mysql after the -d the flag is the docker image we already pulled from docker hub.

After running the command you can verify the container is already running by running the following command:

docker ps

Image by Author.

You can see that the container is already running, and you can also see information about the container.

3. Download Mysql Workbench.

Now, we can download the MySQL workbench which serves as our client for communicating with the MySQL database server. You can download the mysql workbench with this link.

4. Run Mysql Server inside the Container.

So, now that we have successfully set up the mysql server container on our local system, we can execute the mysql container interactively in a bash terminal. We would run the following command on the terminal:

docker exec -it my-sql-server /bin/bash

On running the command, we would simply enter a bash instance as shown below:

Image by Author.

To run our mysql instance inside the container, we would simply run the following command in terminal:

mysql -uroot -p -A

On running the command, you would be asked for the mysql password which was set as an environment variable earlier while creating our container. Mine was root, so I provided it to the terminal.

As you can see, we are currently in the mysql container. Let’s run the following command to see the users on the container.

select user, host from mysql.user;

Image by Author.

The command works, and you can see that the terminal outputs a table of our users and host.

5. Running Instance Outside Container.

We can also run our mysql server instance outside the container, so far the container is still running on docker daemon. We would run the following command to do so:

mysql -uroot -p -P3307 -h127.0.0.1

The -uroot tag specifies the name of the Mysql connection, in this case it is root, the -p -P3307 tag specifies the port number to use for the connection which is the local host port, and the -h127.0.0.1 specifies the IP address of the container, this can be gotten by running the following command:

docker inspect <container_name>

On running our command, we would be requested input our password just like before. After inputting the password, we automatically enter into the container instance as shown below:

Image by Author.

As you can see, we have successfully spanned the mysql instance outside the container.

6. Establish a Connection between the Mysql Container Server and the Mysql Workbench.

You might be wondering what the difference between the Mysql database server and the Mysql workbench is. Well, the Mysql database server is the engine that runs and stores the database, while the workbench is like a Graphical User Interface (GUI) for communicating with the server.

Image by Author.

Once we open the workbench, we need to setup a new connection with our mysql server. We will provide our connection name, Hostname which is the IP address of our container, and the localhost port. Then click on OK to establish the connection.

As you can see, we have successfully established a connection between our mysql server and workbench. We can run SQL commands right from the workbench now.

Let’s run an SQL command to show the available databases on our database server before proceeding to the next phase of the article.

SHOW DATABASES;

Image by Author.

We ran the command on the terminal and as expected, the command works fine. It outputs the databases available our server. Now, let’s run the same command on the workbench to see if it would give us the same outcome.

Image by Author.

As shown in the image, the workbench (client) give us the same outcome as the one on the terminal, this leaves us with the flexibility of either using the workbench or the terminal to run commands based on your preference.

7. Create a Database

Now, we would create a database on our server, to show the container in action. We would run the following SQL command to create a database with the name Containers:

CREATE DATABASE Containers;

Image by Author.

The query works properly on the terminal. Let’s verify the existence of the database we just created by running the following command in our workbench.

SHOW DATABASES;

Image by Author.

Our database has been created successfully according to the image above.

8. Create a Table in the Database.

Now, we would create a table named IronContainer inside our Container database. We would run the following commands to do so:

USE Containers;

CREATE TABLE IF NOT EXISTS IronContainer (
    ID INT PRIMARY KEY,
    Name VARCHAR(255)
);

Image by Author.

Let’s insert some values into the table and check the content of the table with the workbench.

INSERT INTO IronContainer (ID, Name)
VALUES (1, 'Item 1'),
       (2, 'Item 2'),
       (3, 'Item 3');

Image by Author.

Now, we would check the content of the table on the workbench.

Image by Author.

The content we added to the table IronContainer was properly stored to the table according to the image above. We have successfully setup a database server without installing any application for the server.

Conclusion

We have come to the end of this article, we were able to run a MySQL database server container with docker, we learnt how to pull docker images from docker hub, how to run containers in docker, and we explored the options of running the database inside and outside the container and also running SQL commands via the terminal or the workbench (GUI). This environment setup technique is very useful for different tasks that require the use of a database.

Thank you for reading!