Create Docker Image for MySQL Database

Now I have a Docker imaging that runs Spring based web application. The next step for me is to create a Docker image with database on it, then be able to connect to this DB server using at least a command line based DB client and a GUI based DB client. Once I get this completed, I can start connecting the two running images so that they can interact.

Creating a Docker image that runs a MySQL database in it is a little harder than I thought. I ran into multiple issues. The Docker specific issue is that how I can upload a script and use it to setup the database once the server is up and running. This turned out to be pretty easy. But the research is tough. Mainly this is due to the fact that I had no idea what I am doing. And the search for solutions was not returning anything useful (on Google). The other issues are partly related to Docker, such as how to publish the port for DB access, and how to connect using DB client to test. All of these will be addressed in this tutorial.

The first step is to create a Docker file which I can use to create an image. This is the final Dockerfile:

FROM mysql:latest
ENV MYSQL_ROOT_PASSWORD='{{{MySQL Root Password Here}}}'
COPY testdb.sql /docker-entrypoint-initdb.d/

For security, I will not share my test password here. What I found is that for Linux, better not use $ in the password string. $ is used to denote variable in the shell scripts and using this as part of the password string could complicate the creation of shell scripts as the password would look wrong. Instead I used ! in the password string.

The docker file is really simple:

• The first line defines what Docker image template I will be using to create my image. The template is an image that contains latest MySQL server and DB client.
• The second one will set the root password for MySQL. This is set as a environment variable. Without this line, the image will boot then shutdown. And use a secure password.
• The last line is the punch line. It copies the database setup into a predefined folder, when the image boots up for the first time, it will start MySQL server, then run the script to setup the database. The setup only occur once. Hence, you don't have to worry about the script to be run over and over, which erases the new data.

I run the following command to create the new image:

docker build -t 'hanmysql1' .

Once the image is created successfully, I run the following command to start the image:

docker run -dit -p 3306:3306 'hanmysql1'

These two commands are the same ones I use in my previous tutorial. The second command uses -p 3306:3306 to publish the port 3306 from the image to the host. With that, I can access the database from localhost using port 3306. It also uses the option -dit to start up the image in detached/interact-able mode. The start up time for the first time will take a while because it will setup the database. It took about 5 minutes on my try. Subsequently, the script was not run again. And the new data are accessible after restart.

This is the SQL script I used for setup the database:

/* testdb.sql */
DROP USER IF EXISTS 'testdbuser'@'localhost';
DROP USER IF EXISTS 'testdbuser'@'%';
DROP DATABASE IF EXISTS `hantestdb`;

CREATE DATABASE `hantestdb`;

ALTER DATABASE `hantestdb` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

CREATE USER 'testdbuser'@'localhost' IDENTIFIED BY '{{{my local test user password}}}';
CREATE USER 'testdbuser'@'%' IDENTIFIED BY '{{{my user from any machine password}}}';
GRANT ALL ON `hantestdb`.* TO 'testdbuser'@'localhost';
GRANT ALL ON `hantestdb`.* TO 'testdbuser'@'%';
FLUSH PRIVILEGES;

USE `hantestdb`;

CREATE TABLE `HelloWorld` (
    `id` INT NOT NULL UNIQUE AUTO_INCREMENT,
    `msg` VARCHAR(512) NULL,
    `addedTS` DATETIME NOT NULL DEFAULT NOW(),
    `updatedTS` DATETIME NOT NULL DEFAULT NOW(),
    PRIMARY KEY (`id`)
);

INSERT INTO `HelloWorld` (`msg`) VALUES (
    'Greeting from my MySQL docker instance.'
);

The SQL script creats the database, the users, the password for the users, and the one table plus one row in the table. If this script can run one time successfully, it will create the databse, the users, the table and the one row for the table.

When the image is start running, I can test it by using the mysql client to connect to it. I can do this at the host machine, using the following command:

mysql -h 127.0.0.1 -P 3306 -u testdbuser -p

When prompt for user's password, enter it. Then click Enter.

Note: the command line uses the host address 127.0.0.1. If you use localhost, it will fail to connect.

If there is any problem connect to the database from host PC, you can try connect to the database via Docker Desktop's console. The command to connect is:

mysql -h localhost -u testdbuser -p

If both connections work as expected, we can also connect to the database using a GUI based DB client on the host PC, again the address would be 127.0.0.1.

Once I got both connections working, I am half way through the learning of getting two Docker images running at the same time and communicate with each other. The next tutorial I will discuss how this is done. Stay tuned!