Build a custom MySQL Docker Container

Tired of going through endless documentation for setting up your database in any computer apart from your local computer? Setting up a database every time you want to do some API testing? Well, Docker is here to help you out and spin up a local database. This will make it super easy for you to test their code and write data without installing and configuring a lot of tools. In the beginning stages of development, of course, you wouldn't want to spend hours configuring the database so a custom MySQL container can change your life!

What is docker❓

Docker is an application build and deployment tool to help you create, run and deploy applications. It uses the concept of container i.e. you create a package of your code with dependencies that can be deployed as one single unit. Although the concept of containers has been around for a long time, docker makes the tasks of setting up and handling the containers very easy.

Let's start creating! 🔥

We will be creating and deploying a custom docker image. What can this docker image do? It can:

1. Create a database "marvel"
2. Create a table "superheroes"
3. Set the superhero_id to auto_increment
4. Insert 10 records into the table

Before starting make sure you have Docker installed. If not, you can install it from here. Create a project folder let's call it marvel_db. Here's what the project structure would look like. We'll go through each of the files one by one.

Create the SQL scripts 📜

Create a folder called scripts to store both your SQL files, one for creating the database and table and the second one for inserting the new records.

Pro tip: Be careful with what you name these files because docker will go through them in alphabetical order. So if the name of the file inserting the records is alphabetically before the file creating the table, you might face a lot of errors.

Let's name our first file create_table.sql

CREATE DATABASE IF NOT EXISTS `marvel` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
GO
USE `marvel`;
GO
CREATE TABLE `superheroes` (
  `superhero_id` int(11) NOT NULL,
  `name` varchar(255) NOT NULL DEFAULT '',
  `color` varchar(30) NOT NULL,
  `noOfMovies` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
GO
ALTER TABLE `superheroes`
  ADD PRIMARY KEY (`superhero_id`);
GO
ALTER TABLE `superheroes`
  MODIFY `superhero_id` int(11) NOT NULL AUTO_INCREMENT;

So here's what we have done in the above code. We create a database marvel if it already doesn't exist. Then we go inside that database and create a new table superheroes. Next, we set superhero_id as the primary key of the table and set it to auto_increment. The GO command used after every query is a batch separator in SQL that tells SQL that there are more statements to be executed in the SQL file.

Now we'll create the second file, let's call it insert_table.sql for inserting 10 new records.

INSERT INTO `superheroes` (`name`,`color`,`noOfMovies`)
VALUES
  ("Iron Man", "Gold", 11),
  ("Captain America","Blue", 11),
  ("Thor","Dark Grey", 7),
  ("Hulk","Green", 9),
  ("Black Widow","Red", 8),
  ("Hawkeye","Grey", 6),
  ("Wanda Maximoff","Dark Red", 5),
  ("Black Panther","Purple", 4),
  ("War Machine","Black", 7),
  ("Spider Man", "Blue", 7);

Create Dockerfile 🐳

Now that our sample database is complete, we will create a Dockerfile to actually run these scripts.

FROM mysql:latest 
ENV MYSQL_DATABASE marvel
COPY ./scripts/ /docker-entrypoint-initdb.d/

Confused about the above code? Here's what it means. In this tutorial, we're creating a custom MySQL image which means we are taking MySQL's own docker image as a starting point for ours. So the first line is calling the latest version of the MySQL docker image. Next, the second line means that we are creating a MySQL database called marvel as soon as the Dockerfile is run. Lastly, the third line copies all the scripts in the script folder to docker-entrypoint-initdb.d/ which will be automatically executed during container setup.

See it in action 🎬

Guess what? We have created our custom MySQL Docker container. Let's build it and check if it works.
Run the command in your terminal to build the docker image with the name marveldb.

docker build -t marveldb:1.0 .

Pro tip: Docker images built with an ARM64 based architecture like the Apple Silicon chip can create issues when deploying images to a Linux or Windows-based AMD64 environment like AWS EC2, ECS, etc. So, you need a way to build AMD64 based images on the ARM64 architecture which you can build by using the flag --platform linux/x86_64 as used above. Windows and Linux users don't need to use this flag.

You can check if your new image is running with the command docker images. Now run the docker container with the following command:

docker run -d -p 3306:3306 --name marvelDB \
-e MYSQL_ROOT_PASSWORD=12Marvel --platform linux/x86_64 marveldb:1.0

Again you can leave out the platform flag if you are using Windows or Linux. You check if your container is running with the command docker container ls.

Execute the container to run SQL queries:

docker exec -it marvelDB bash 

Go inside the MySQL terminal:

mysql -uroot -p

You'll be prompted to enter the password. Write the root password you used while running the container, in our case 12Marvel. Now you can run whatever SQL queries you want you will notice, you have the marvel database and superheroes table already created with 10 records in it.

Take it one step further 🪜

In industries or big organizations, there is usually not just one docker container. They have many services or containers that they have to manage together. So instead of running and building each container, we can use a docker-compose file. It stores all the essential information about all the services and now you can run countless containers with one single command. Let's start by creating a docker-compose.yml file.

version: "3.7"
services:
    marvelDB:
        image: "marveldb:1.0"
        platform: linux/x86_64
        environment:
            MYSQL_ROOT_PASSWORD: "12Marvel"
        ports:
            - "3306:3306"

That's it! Run the docker compose:

docker-compose up   

It will create a service called marvel_db(name of our project folder) inside which there is a container with the name marvel_db_marvelDB_1. You can confirm the name of your container using docker container ls.

Now you can again run the exec command and get the exact same result. This process might not seem that fruitful in this example, but when things get more complex, the docker compose file makes a huge difference.

Push to docker hub ☁️

Wow! We're in the endgame now! The last thing to do is to push your custom MySQL container to the cloud so that anyone from anywhere can access it.

First, create an account on Docker Hub. Create a repository and give it a name and description. Then, from the terminal, run docker login and enter your credentials.

Now, tag your image:

docker tag local-image:tagname username/new-repo:tagname

In my case:

docker tag marveldb:1.0 sumana2001/marvel:1.0

Next, push your image to the new repository

docker push username/new-repo:tagname

In my case:

docker push sumana2001/marvel:1.0

When the process is complete, you will be able to pull your image from anywhere using the command:

docker pull username/new-repo:tagname

In my case:

docker pull sumana2001/marvel

Outro 💚

We have successfully created a custom MySQL container. From here, the sky is the limit. We can add endless functionalities on top of different prebuilt images and that's the beauty of docker. If you got stuck anywhere you can check out the entire source code on Github and view the live repository on Docker Hub.
In case you have some questions regarding the article or want to discuss something under the sun feel free to connect with me on LinkedIn 💕

If you run an organisation and want me to write for you please do connect with me 🙈